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

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)