栏目头部广告

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新用户专属注册连接

UCloud CDN超值特惠专场

UCloud全球云主机(UHost/VPS)大促页面

UCloud快杰云主机大促页面

文章页广告

随便看看

栏目底部广告
`