在生产环境中，索引的更新操作可能非常频繁。如果再每次索引发生更新操作时就对其进行Cardinality的统计，那么将会对数据库带来很大的负担。另外需要考虑的是，如果一张表的数据量非常大，比如一张表有50GB的数据，

那么统计一次Cardinality信息所需要的时间可能非常长。这在生产环境的应用中也是不能接受的。因此，数据库对于Cardinality的统计都是通过采样(sample)的方法来完成的。

在InnoDB存储引擎中，Cardinality统计信息的更新发生在两个操作中：INSERT和UPDATE。根据前面的叙述，不可能在每次发生INSERT和UPDATE时都去更新Cardinality的信息，

这会增加数据库系统的负荷，同事对大表进行统计时，时间上也不允许。

因此InnoDB存储引擎对于更新Cardinality信息的策略为：

表中1/16的数据已发生变化

stat_modified_counter > 2 000 000 000

ANALYZE TABLE语句分析并存储表中索引的分布情况。对于InnoDB、MyISAM存储引擎，在分析中，表会被加只读锁。这个语句适用于InnoDB、NDB和MyISAM表。对于MyISAM表，这个语句等同于myisamchk --analyze。这个语句对 视图无效。 在执行非constant类型的表连接时，MySQL使用存储的索引分布信息来决定表的关联顺序。另外，索引的分布情况也可以用来决定在查询中使用哪个表的特定索引。 执行这个语句需要对指定表的SELECT和INSERT权限。 对于分区表，需要执行ALTER TABLE ... ANALYZE PARTITION语句。 在MySQL 5.7.1，在执行ANALYZE TABLE语句之前，需要将gtid_next参数设为AUTOMATIC。这个限制在MySQL 5.7.2版本以后被修复。 可以使用SHOW INDEX语句来显示索引的分布情况。 默认，MySQL服务会将ANALYZE TABLE语句写到二进制日志中，并传输到复制环境中的slave节点。想关闭这个日志记录，可以在语句中加上NO_WRITE_TO_BINLOG或LOCAL。 --分析非分区表的索引分布情况 mysql> show index from dept; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | dept  |          0 | PRIMARY  |            1 | DEPTNO      | A         |           4 |     NULL | NULL   |      | BTREE      |         |               | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) mysql> show keys from dept; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | dept  |          0 | PRIMARY  |            1 | DEPTNO      | A         |           4 |     NULL | NULL   |      | BTREE      |         |               | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) mysql> analyze table  dept; +-----------+---------+----------+----------+ | Table     | Op      | Msg_type | Msg_text | +-----------+---------+----------+----------+ | fire.dept | analyze | status   | OK       | +-----------+---------+----------+----------+ 1 row in set (0.03 sec) mysql> show index from dept; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | dept  |          0 | PRIMARY  |            1 | DEPTNO      | A         |           4 |     NULL | NULL   |      | BTREE      |         |               | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) --分析分区表的索引分布情况 mysql> select table_schema,table_name,partition_name from  information_schema.partitions where table_name = 'trb1'; +--------------+------------+----------------+ | table_schema | table_name | partition_name | +--------------+------------+----------------+ | fire         | trb1       | p0             | | fire         | trb1       | p1             | | fire         | trb1       | p2             | | fire         | trb1       | p3             | +--------------+------------+----------------+ 4 rows in set (0.00 sec) mysql> show index from trb1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | trb1  |          0 | PRIMARY  |            1 | id          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) mysql> alter table trb1 analyze partition p0; +-----------+---------+----------+----------+ | Table     | Op      | Msg_type | Msg_text | +-----------+---------+----------+----------+ | fire.trb1 | analyze | status   | OK       | +-----------+---------+----------+----------+ 1 row in set (0.03 sec) mysql> alter table trb1 analyze partition p1; +-----------+---------+----------+----------+ | Table     | Op      | Msg_type | Msg_text | +-----------+---------+----------+----------+ | fire.trb1 | analyze | status   | OK       | +-----------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> alter table trb1 analyze partition p2; +-----------+---------+----------+----------+ | Table     | Op      | Msg_type | Msg_text | +-----------+---------+----------+----------+ | fire.trb1 | analyze | status   | OK       | +-----------+---------+----------+----------+ 1 row in set (0.01 sec) mysql> alter table trb1 analyze partition p3; +-----------+---------+----------+----------+ | Table     | Op      | Msg_type | Msg_text | +-----------+---------+----------+----------+ | fire.trb1 | analyze | status   | OK       | +-----------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> show index from trb1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | trb1  |          0 | PRIMARY  |            1 | id          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2115026/，如需转载，请注明出处，否则将追究法律责任。