MySQL中的analyze与optimize
Contents
analyze
作用： > 分析表主要作用是分析并保存索引的分布.
语法:
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
使用ANALYZE TABLE分析表的过程中，数据库系统会对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表。
例如：
mysql> show index from wb_status;
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wb_status | 0 | PRIMARY | 1 | id | A | 1195935 | NULL | NULL | | BTREE | | |
| wb_status | 0 | idx_sid | 1 | sid | A | 1196049 | NULL | NULL | | BTREE | | |
| wb_status | 1 | idx_user_id | 1 | user_id | A | 678760 | NULL | NULL | YES | BTREE | | |
| wb_status | 1 | ngram_idx | 1 | text | NULL | 1196049 | NULL | NULL | | FULLTEXT | | |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql> analyze table wb_status;
+----------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| test.wb_status | analyze | status | OK |
+----------------+---------+----------+----------+
1 row in set (0.03 sec)
mysql> show index from wb_status;
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wb_status | 0 | PRIMARY | 1 | id | A | 1279457 | NULL | NULL | | BTREE | | |
| wb_status | 0 | idx_sid | 1 | sid | A | 1279457 | NULL | NULL | | BTREE | | |
| wb_status | 1 | idx_user_id | 1 | user_id | A | 887945 | NULL | NULL | YES | BTREE | | |
| wb_status | 1 | ngram_idx | 1 | text | NULL | 1279457 | NULL | NULL | | FULLTEXT | | |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
optimize
作用: >
语法:
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
该语句对InnoDB和MyISAM类型的表都有效.重新组织表数据和索引数据的物理存储，以减少存储空间以及提高I/O的效率.具体的操作，还要依赖于表的存储引擎的类型. 无法用于视图，但可用于分区表.
如果用于视图的话，会报如下类似错误:
mysql> optimize table wsv;
+----------+----------+----------+------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------+----------+----------+------------------------------+
| test.wsv | optimize | Error | 'test.wsv' is not BASE TABLE |
| test.wsv | optimize | status | Operation failed |
+----------+----------+----------+------------------------------+
2 rows in set (0.00 sec)
mysql>
参考资料
注：
OPTIMIZE TABLE simply copies the table to remove unused space. If the table is MyISAM, ANALYZE TABLE is also performed to update index statistics for the sake of the Query Optimizer. If the table is InnoDB, ANALYZE TABLE is bypassed.
这里这个answer说，如果是InnoDB，则analyze table会忽略不处理？
但官方文档里说:
For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE … FORCE, which rebuilds the table to update index statistics and free unused space in the clustered index
这表明，它其实也已经自动更新了索引的统计信息了的。然后就不需要再执行analyze table的意思？ 我开始时，还以为对于InnoDB，它并不会更新索引的统计信息，而analyze table又被忽略…
[moremysql.weebly.com])(http://moremysql.weebly.com/blog/optimize-vs-analyze-table-in-mysql)