ANALYZE命令主要对表做 key distribution 分析,语法如下:
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...
ANALYZE TABLE 作用的对象是 InnoDB, NDB, 和 MyISAM 表,对视图不起作用。在分析期间,会对表加上read lock(InnoDB, MyISAM) , 如果自从上次分析之后,表没有发生变化,则不会被再次分析。
key distribution可以认为是表的“统计信息”,它决定表做join的时候的join顺序,以及在某个查询中是否会用到某个索引。要查看key distribution,可以使用 SHOW INDEX 或者查询 INFORMATION_SCHEMA.STATISTICS 表,两者是一样的。
下面的示例首先展示了两种查看key distribution的方法,看到 Cardinality 不准确,在做了一个ANALYZE TABLE的操作之后,变得准确。Cardinality 的意思是基数,也就是不重复的记录的数目,对于索引来讲,Cardinality越大越好,最好接近真实的记录数,如果Cardinality太小,则索引就失去意义了。
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` char(10) NOT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx1` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from t1;
+-------+------+
| id | age |
+-------+------+
| aaaaa | 12 |
| fff | 12 |
| ggg | 12 |
| hhh | 12 |
| bbb | 13 |
| iii | 13 |
| cc | 14 |
| dd | 15 |
| eee | 16 |
+-------+------+
9 rows in set (0.00 sec)
mysql> SHOW index from t1 \G
*************************** 1. row ***************************
Table: t1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 9
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: t1
Non_unique: 1
Key_name: idx1
Seq_in_index: 1
Column_name: age
Collation: A
Cardinality: 9
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
mysql> select * from INFORMATION_SCHEMA.STATISTICS where TABLE_NAME like 't1' \G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test1
TABLE_NAME: t1
NON_UNIQUE: 0
INDEX_SCHEMA: test1
INDEX_NAME: PRIMARY
SEQ_IN_INDEX: 1
COLUMN_NAME: id
COLLATION: A
CARDINALITY: 9
SUB_PART: NULL
PACKED: NULL
NULLABLE:
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test1
TABLE_NAME: t1
NON_UNIQUE: 1
INDEX_SCHEMA: test1
INDEX_NAME: idx1
SEQ_IN_INDEX: 1
COLUMN_NAME: age
COLLATION: A
CARDINALITY: 9 <----------不准
SUB_PART: NULL
PACKED: NULL
NULLABLE: YES
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
2 rows in set (0.00 sec)
mysql> ANALYZE TABLE t1;
+----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test1.t1 | analyze | status | OK |
+----------+---------+----------+----------+
1 row in set (0.02 sec)
mysql> SHOW index from t1 \G
*************************** 1. row ***************************
Table: t1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 9
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: t1
Non_unique: 1
Key_name: idx1
Seq_in_index: 1
Column_name: age
Collation: A
Cardinality: 5 <----------ANALYZE之后,获得了准确的数据
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)