mysql optimize innodb 優化詳解

性能優化（Optimize） 簡而言之，就是在不影響系統運行正確性的前提下，使之運行地更快，完成特定功能所需的時間更短，下面我們就來看看關於mysql optimize innodb 優化教程，具體的細節如下所示。

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