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_message
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 19373842
 Avg_row_length: 107
    Data_length: 2079326208
Max_data_length: 0
   Index_length: 9689251840
      Data_free: 7956594688
 Auto_increment: 27527286
    Create_time: 2012-04-17 09:30:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 

mysql> show index from message_message \G
*************************** 1. row ***************************
       Table: message_message
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 19333139
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: message_message
  Non_unique: 1
    Key_name: idx_parent
Seq_in_index: 1
 Column_name: parent_id
   Collation: A
 Cardinality: 230



    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 

 

执行了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_message
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 19034974
 Avg_row_length: 94
    Data_length: 1799356416
Max_data_length: 0
   Index_length: 6529433600



      Data_free: 11394875392
 Auto_increment: 27530331
    Create_time: 2012-05-09 21:38:41
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment

 

 

ysql> show index from message_message \G  
*************************** 1. row ***************************
       Table: message_message
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 19064084



    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: message_message
  Non_unique: 1
    Key_name: idx_parent
Seq_in_index: 1
 Column_name: parent_id
   Collation: A
 Cardinality: 9532042



    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:

 

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