[MySQL最適化事例]シリーズ--OPTIMIZEの威力

作/訳者:葉金栄(Email:)、出典:http://imysql.cn、転載は作/訳者と出典を明記してください.また、商業用途には使用できません.違反者は必ず追及します.
1、挿入操作を複数回削除したテーブルインデックスの様子を見てみる

mysql> SHOW INDEX FROM `tbl_name`;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tbl_name |          0 | PRIMARY    |            1 | StepID      | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | FlowID     |            1 | FlowID      | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerCount |            1 | WagerCount  | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_3  |            1 | WagerID     | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_3  |            2 | StepType    | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_3  |            3 | ParamResult | A         |           1 |      255 | NULL   |      | BTREE      |         |
| tbl_name |          1 | StepType_2 |            1 | StepType    | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | StepType_2 |            2 | ParamResult | A         |           1 |      255 | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_2  |            1 | WagerID     | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_2  |            2 | StepType    | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_2  |            3 | ParamResult | A         |           1 |      255 | NULL   |      | BTREE      |         |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
11 rows in set (0.01 sec)

2、最適化表

mysql> optimize table tbl_name;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| test.tbl_name | optimize | status   | OK       |
+---------------+----------+----------+----------+
1 row in set (40.60 sec)

3、最適化後の効果を見てみる

mysql> SHOW INDEX FROM `tbl_name`;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tbl_name |          0 | PRIMARY    |            1 | StepID      | A         |      172462 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | FlowID     |            1 | FlowID      | A         |       86231 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerCount |            1 | WagerCount  | A         |        4311 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_3  |            1 | WagerID     | A         |       86231 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_3  |            2 | StepType    | A         |      172462 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_3  |            3 | ParamResult | A         |      172462 |      255 | NULL   |      | BTREE      |         |
| tbl_name |          1 | StepType_2 |            1 | StepType    | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | StepType_2 |            2 | ParamResult | A         |       86231 |      255 | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_2  |            1 | WagerID     | A         |       86231 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_2  |            2 | StepType    | A         |      172462 |     NULL | NULL   |      | BTREE      |         |
| tbl_name |          1 | WagerID_2  |            3 | ParamResult | A         |      172462 |      255 | NULL   |      | BTREE      |         |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

最後に、OPTIMIZEについてのマニュアルの説明を見てみましょう.

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
              ，                 （  VARCHAR, BLOB TEXT   ）       ，    
OPTIMIZE TABLE。               ，   INSERT             。     OPTIMIZE TABLE   
        ，          。
       ，        OPTIMIZE TABLE。                  ，         ，         
  ，        。
OPTIMIZE TABLE  MyISAM, BDB InnoDB    。
  ， OPTIMIZE TABLE     ，MySQL    。
     “MySQL   ”   http://www.imysql.cn/
 

本文は“MySQLを愛します”のブログから出て、転載して作者と連絡してください!