mysql optimize innodb 优化

张映 发表于 2016-04-12

分类目录: mysql

标签:, ,

如果对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,第二列是索引



转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/mysql/1789.html