mysql optimize table

本文主要是介绍mysql optimize table，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

参考：

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

mysql性能优化-慢查询分析、优化索引和配置: http://www.oicto.com/mysql-explain-show/

http://duyongguang.blogbus.com/logs/181612876.html

 

执行前

 

    message table status:

           Name: message_messageEngine: InnoDBVersion: 10Row_format: CompactRows: 19373842Avg_row_length: 107Data_length: 2079326208
Max_data_length: 0Index_length: 9689251840Data_free: 7956594688Auto_increment: 27527286Create_time: 2012-04-17 09:30:48Update_time: NULLCheck_time: NULLCollation: utf8_general_ciChecksum: NULLCreate_options: Comment: 

mysql> show index from message_message \G
*************************** 1. row ***************************Table: message_messageNon_unique: 0Key_name: PRIMARY
Seq_in_index: 1Column_name: idCollation: ACardinality: 19333139Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
*************************** 2. row ***************************Table: message_messageNon_unique: 1Key_name: idx_parent
Seq_in_index: 1Column_name: parent_idCollation: ACardinality: 230Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 

 

执行了24分钟：

mysql> optimize table message_message;
^L+-----------------------+----------+----------+-------------------------------------------------------------------+
| Table                 | Op       | Msg_type | Msg_text                                                          |
+-----------------------+----------+----------+-------------------------------------------------------------------+
| zcwdb.message_message | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zcwdb.message_message | optimize | status   | OK                                                                |
+-----------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (24 min 16.26 sec)

 

执行后：

 

           Name: message_messageEngine: InnoDBVersion: 10Row_format: CompactRows: 19034974Avg_row_length: 94Data_length: 1799356416
Max_data_length: 0Index_length: 6529433600Data_free: 11394875392Auto_increment: 27530331Create_time: 2012-05-09 21:38:41Update_time: NULLCheck_time: NULLCollation: utf8_general_ciChecksum: NULLCreate_options: Comment

 

 

ysql> show index from message_message \G  
*************************** 1. row ***************************Table: message_messageNon_unique: 0Key_name: PRIMARY
Seq_in_index: 1Column_name: idCollation: ACardinality: 19064084Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
*************************** 2. row ***************************Table: message_messageNon_unique: 1Key_name: idx_parent
Seq_in_index: 1Column_name: parent_idCollation: ACardinality: 9532042Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:

 

结果还是比较明显：索引文件大小表小，Cardinality命中率上升.

 

 

这篇关于mysql optimize table的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---