设为首页收藏本站

追梦Linux

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 300|回复: 0

mysql optimize innodb 优化

[复制链接]

482

主题

485

帖子

16万

积分

CEO

Rank: 9Rank: 9Rank: 9

积分
168140

最佳新人活跃会员热心会员推广达人宣传达人灌水之王突出贡献优秀版主荣誉管理论坛元老

QQ
发表于 2017-4-5 14:26:55 | 显示全部楼层 |阅读模式
如果对mysql表,进行大范围删除后,最好做一个表优化,这样会快一些。
1,删除数据,并尝试优化
[Bash shell] 纯文本查看 复制代码
mysql> delete FROM `test` WHERE d_id>397136;  
Query OK, 306356 rows affected (19.48 sec)  
 
mysql> optimize table test;  
+------------+----------+----------+-------------------------------------------------------------------+  
| Table      | Op       | Msg_type | Msg_text                                                          |  
+------------+----------+----------+-------------------------------------------------------------------+  
| test1.test | optimize | note     | Table does not support optimize, doing recreate + analyze instead |  
| test1.test | optimize | status   | OK                                                                |  
+------------+----------+----------+-------------------------------------------------------------------+  
2 rows in set (0.35 sec)
  
执行optimize的时候,非常慢,可能会卡死。msg_text信息的意思是innodb引擎,不支持optimize。在官网找了一下,发现以下内容
[Bash shell] 纯文本查看 复制代码
Table does not support optimize, doing recreate + analyze instead.
It is because the table that you are using is InnoDB.
You can optimize the InnoDB tables by using this.
ALTER TABLE table.name ENGINE='InnoDB';


一般情况下,由myisam转成innodb,会用alter table table.name engine='innodb'进行转换,优化也可以用这个

2,alter优化数据
[Bash shell] 纯文本查看 复制代码
mysql> alter table test engine='innodb';  
Query OK, 384781 rows affected (19.88 sec)  
Records: 384781  Duplicates: 0  Warnings: 0  

alter如果很慢,优化一下my.cnf,[mysqld]加上以下内容,并重新加载。
[Bash shell] 纯文本查看 复制代码
innodb_buffer_pool_size=1G  
innodb_file_io_threads=4  

innodb_file_io_threads常规配置,小于等CPU核数。innodb_buffer_pool_size小于等于物理内存的1/2,原则上够用就好。

3,优化后的对比
[Bash shell] 纯文本查看 复制代码
mysql> use information_schema 
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,  
 -> concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB  
 -> from tables where  
 -> table_schema='test1'  
 -> and table_name = 'test';  
+----------------+-----------------+  
| data_length_MB | index_length_MB |  
+----------------+-----------------+  
| 20.55MB        | 27.55MB         |       //优化前  
+----------------+-----------------+  
1 row in set (0.01 sec)  
 
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,  
 -> concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB  
 -> from tables where  
 -> table_schema='test1'  
 -> and table_name = 'test';  
+----------------+-----------------+  
| data_length_MB | index_length_MB |  
+----------------+-----------------+  
| 20.55MB        | 16.55MB         |    //优化后  
+----------------+-----------------+  
1 row in set (0.00 sec)  

data_length_MB,第一列是数据;index_length_MB,第二列是索引

参照:http://blog.51yip.com/mysql/1222.html


QQ|小黑屋|手机版|Archiver|追梦Linux ( 粤ICP备14096197号  点击这里给我发消息

GMT+8, 2019-3-25 17:52 , Processed in 0.308616 second(s), 30 queries .

Powered by 追梦Linux! X3.3 Licensed

© 2015-2017 追梦Linux!.

快速回复 返回顶部 返回列表