mysql optimize innodb_mysql optimize innodb 优化 超、凢脫俗 2023-01-12 07:56 146阅读 0赞 如果对mysql表，进行大范围删除后，最好做一个表优化，这样会快一些。以前写过一篇类似的，针对myisam存储引擎的。请参考：实例说明optimize table在优化mysql时很重要 1，删除数据，并尝试优化 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。在官网找了一下，发现以下内空 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优化数据 mysql> alter table test engine='innodb'; Query OK, 384781 rows affected (19.88 sec) Records: 384781 Duplicates: 0 Warnings: 0 alter如果很慢，优化一下my.cnf，\[mysqld\]加上以下内容，并重新加载。 innodb\_buffer\_pool\_size=1G innodb\_file\_io\_threads=4 innodb\_file\_io\_threads常规配置，小于等CPU核数。innodb\_buffer\_pool\_size小于等于物理内存的1/2，原则上够用就好。 3，优化后的对比 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，第二列是索引
还没有评论，来说两句吧...