每日MySQL之023：使用ANALYZE TABLE命令分析表的key distribution

来源：互联网发布：苹果手机相机软件编辑：程序博客网时间：2024/06/06 18:16

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: t1Create Table: CREATE TABLE `t1` (  `id` char(10) NOT NULL,  `age` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `idx1` (`age`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 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)

阅读全文

0 0