MySQL优化器之Cardinality（Analyze table）

一、伏笔篇

（1）优化器索引选择影响因素?

选择索引是优化器的工作，而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

（2）SQL扫描行数判断依据？

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

（3）MySQL索引基数计算方法？

为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：
◆ 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
◆ 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。
由于是采样统计，所以不管 N 是 20 还是 8，这个基数都是很容易不准的。

MySQL官网介绍：https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html

二、案例演示

背景介绍：MySQL业务生产环境中，出现一起比较有意思的现象，即在主从同步结构中，主库和从库执行同样的SQL A耗时相差比较大。通过explain执行计划分析发现，主库执行SQL A未走索引，从库执行SQL A走了索引。是什么原因导致这种执行计划差异的出现呢？下面让我们一起分析一下。以下为生产业务真实案例，涉及表、库信息已做脱敏处理。

2.1 业务案例表结构

（1）查看异常业务表结构

mysql [blogs]> show create table table1\G
*************************** 1. row ***************************
       Table: table1
Create Table: CREATE TABLE `table1` (
  `ID_` varchar(64) COLLATE utf8_bin NOT NULL,
   ……
  `NAME_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `DP_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  ……
  PRIMARY KEY (`ID_`),
  KEY `key1` (`DP_ID_`),
  KEY `key2` (`NAME_`),
  CONSTRAINT `a` FOREIGN KEY (`DP_ID_`) REFERENCES `b` (`ID_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

（2）查看业务表SQL执行计划异常时索引

mysql>  show index from blogs.table1; 
+------------------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name               | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table1           |          0 | PRIMARY                |            1 | ID_            | A         |     5480647 |     NULL | NULL   |      | BTREE      |         |               |
| table1           |          1 | Key1                   |            1 | DP_ID_         | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| table1           |          1 | key2                   |            1 | NAME_          | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

（3）索引字段介绍

table                 # 表名称
non_unique      # 如果索引不能包括重复词，为0，如果可以，则为1
key_name         # 索引的名称
seq_in_index    # 索引中的列序号
column_name   # 列名称
collation            # 列以什么方式存储在索引中，在mysql中，有值'A'（升序）或者NULL（无分类）
cardinality         # 索引唯一值的数据的估值，通过运行analyze table xxx_table;或者 myisamchk -a 可以更新。myisam中，该值是准确的，INNODB中该值数据采样估算的，存在偏差
sub_part            # 如果列只是部分的编入索引 则为被编入索引的字符的数目，如果整列被编入索引，则为NULL
packed               # 指示关键词如何被压缩，如果没有被压缩，则为NULL
NULL                # 如果列含有NULL，则含有YES，如果没有，则该列为NO
index_type         # 用过的索引方法（BTREE,FULLTEXT,HASH,RTREE）
comment            # 备注
index_comment # 为索引创建时提供了一个注释属性的索引的任何评论

（4）查看业务表SQL执行计划正常时索引

mysql>  show index from blogs.table1; 
+------------------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name               | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table1           |          0 | PRIMARY                |            1 | ID_            | A         |     5480647 |     NULL | NULL   |      | BTREE      |         |               |
| table1           |          1 | Key1                   |            1 | DP_ID_         | A         |         200 |     NULL | NULL   | YES  | BTREE      |         |               |
| table1           |          1 | key2                   |            1 | NAME_          | A         |         200 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

2.2 统计两个key字段额不同值行数

（1）NAME_字段重复行数

mysql> select count(distinct(NAME_)) from blogs.table1;
+------------------------+
| count(distinct(NAME_)) |
+------------------------+
|                      6 |
+------------------------+
1 row in set (0.00 sec)

（2）DP_ID_字段重复行数

mysql> select count(distinct(DP_ID_)) from blogs.table1;
+-------------------------+
| count(distinct(DP_ID_)) |
+-------------------------+
|                      17 |
+-------------------------+
1 row in set (0.00 sec)

由此可见548w+行数据，NAME_和DP_ID_两个key字段不同值数分别是6和17，正常来说，这两个key字段不同值数过小时，说明他们并不是非常适合作为索引字段。通过对官网analyze table的阅读，不难发现，优化器判断一条SQL是否走索引的重要依据是key分布情况，即Cardinality（基数）值的大小。当Cardinality（基数）值过小时，优化器会认为SQL不走索引更优。比如，上述“背景分析”的现象。另外官网也对Cardinality（基数）值做了详细说明，其大小并不是一个准确值，而是一个采样预估值，通过analyze table操作可以更新Cardinality（基数）预估值的大小，从而影响优化器对索引的选择。

2.3 key分布影响参数

mysql> show variables like "%sample%";
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_stats_persistent_sample_pages | 20    |
| innodb_stats_sample_pages            | 8     |
| innodb_stats_transient_sample_pages  | 8     |
+--------------------------------------+-------+
3 rows in set (0.06 sec)

既然Cardinality值会影响索引的选择，即影响业务的并发性能，那么MySQL配置中是否有相关参数可以控制Cardinality值的大小呢？或者调整Cardinality值的准确性呢？从上面的分析我们了解到Cardinality值是通过随机采样获取的，那么是否能通过控制采样的数量来调整Cardinality值的大小或准确性呢？答案是肯定的。MySQL官方提供了innodb_stats_persistent_sample_pages参数来控制采样的页数，来实现对采样力度的控制。也就是说采样页数越大，Cardinality值就越接近真实值；采样页数越小，Cardinality值偏移量就越大。

三、案例总结

3.1 业务本身

首先我们从业务侧本身说起，前面我们讲过NAME_和DP_ID_两个key字段不同值数非常小，即绝大多数行这两个字段值都是一样的，唯一性非常差，并不适合做索引字段。所以业务侧在创建索引时要充分考虑到，索引字段的合理性，而不是索引越多越好；在编写业务SQL时，要充分考虑到SQL的执行效率，尽量优化至最优。

3.2 曲线救国方案

（1）执行analyze table，通过执行analyze table可以更新Cardinality值。

mysql> analyze table blogs.table1;
+------------------+---------+----------+-----------------------------+
| Table            | Op      | Msg_type | Msg_text                    |
+------------------+---------+----------+-----------------------------+
| table1           | analyze | status   | OK                          |
+------------------+---------+----------+-----------------------------+
1 row in set (0.00 sec)

（2）减少采样页数[当执行analyze table效果不明显时采用]

当然analyze table无效是个小概率事件，但是本案例中确实出现了，可能是因为客户索引字段唯一性差以及一些特殊情况的原因，无论执行多少次analyze table 主库Cardinality值始终是1，导致优化器没有选择索引，而进行了全表扫描。前面我们讲过，innodb_stats_persistent_sample_pages采样页数越小，Cardinality值偏移量就越大，即越失真。所以这里我们为了让优化器走非最优key字段创建的索引，可以通过减小innodb_stats_persistent_sample_pages采样页数，从而实现analyze table后，Cardinality值变大，进而影响MySQL优化器对索引的选择。

mysql>  set global innodb_stats_persistent_sample_pages=8;  # 默认20

mysql>  show index from blogs.table1; 
+------------------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name               | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table1           |          0 | PRIMARY                |            1 | ID_            | A         |     5480647 |     NULL | NULL   |      | BTREE      |         |               |
| table1           |          1 | Key1                   |            1 | DP_ID_         | A         |         666 |     NULL | NULL   | YES  | BTREE      |         |               |
| table1           |          1 | key2                   |            1 | NAME_          | A         |         458 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

# 调整后，可以看出Cardinality值明显变大

（3）其它方法

可以通过force index强制使用特定索引，或设置 max_seeks_for_key系统变量以确保 MySQL 更喜欢索引查找而不是表扫描。

set long_query_time=0;
select * from t force index(a) where a between 10000 and 20000;

作者：UStarGao
链接：https://www.starcto.com/mysql/239.html
来源：STARCTO
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

UCloud新用户专属注册连接

UCloud CDN超值特惠专场

UCloud全球云主机（UHost/VPS）大促页面

UCloud快杰云主机大促页面