MySQL索引选择底层原理探究-从一个慢查询说起 | 技术创作特训营第一期

一. 背景与问题

在生产环境中收到一个接口耗时预警, 通过监控发现, 接口耗时达到了89s, 最终定位到了是因为触发了一个sql慢查询场景.

图1. 接口耗时告警

下面本文会通过慢查询分析, 引出mysql底层优化器在索引选择上的策略以及特性, 并给出索引的最佳实践.

二. 慢查询sql与数据规模

2.1 慢sql

上面告警对应的慢sql如下, 其目的是根据指定条件筛选并排序返回前10条数据.

select col_list
from dal_meta_table_par_info_d
where fstore_server='tdw_cft' and ftb_name='ods_evt_zxg_cft_mta_event_hh'
      and fdb_name='ods_base'
order by fpar_name desc
limit 10;

2.2 数据量以及索引配置

(1) 表dal_meta_table_par_info_d的数据总量为: 5110万

(2) 索引定义主要有如下五个 (其中本文重点讨论的是第三个和第四个):

• PRIMARY KEY (fid)
• UNIQUE KEY uk_uid (ftb_uid, fpar_name)
• UNIQUE KEY uk (ftb_name(255), fdb_name(100), fpar_name(50), fserver_id ) (表名+库名+分区名+serverId的联合唯一索引)
• KEY ix_par_name (fpar_name) (分区名字段的普通索引)
• KEY `ix_tb_ss_par` ( `ftb_name`(255), `fdb_name`(100), `fstore_server`, `fpar_name`(50))

三. 问题分析与探索

针对mysql慢查询问题, 离不开explain工具的使用.

3.1 尝试1 - force index

我们先看下问题sql的执行计划,

explain select * from dal_meta_table_par_info_d  
where fstore_server='tdw_cft' and ftb_name='ods_evt_zxg_cft_mta_event_hh'
        and fdb_name='ods_base'
order by fpar_name desc
limit 10;

图2

分析:

(1) 候选索引里有ftb_name的uk索引 , 但是最终mysql引擎选择的是分区字段名的这个排序字段索引( ix_par_name ), 其预估行数7379( 实际总记录数有31780, 这里体现了mysql引擎统计分析的局部性).

(2) mysql优化器认为在limit 10的情况下并结合内部预统计数据,认为走排序字段索引能更快的找到这批数据(走联合索引需扫描索引然后再排序并有file sort开销), 所以mysql综合考虑, 自动最终选了排序字段的索引, 导致实际检索时间很长--耗时89s, 出现了慢查询.

如果我们强制让优化器走uk索引, 结果会如何呢? 我们惊奇发现耗时只需要0.22s, 性能飞跃式提升( 从89s 降到了0.22s).

explain select * from dal_meta_table_par_info_d  force index(uk)
where fstore_server='tdw_cft' and ftb_name='ods_evt_zxg_cft_mta_event_hh'
        and fdb_name='ods_base'  
order by fpar_name desc 
limit 10;

图3

分析: 虽然有file sort, 但是通过走uk联合索引, 性能大幅提升. 因此基本可以断定确实就是mysql优化器在选择索引的时候出现和我们不符合预期的情况.

3.2 尝试2 - 改变条件值

将where中的三个查询条件值换成其他值, 看下会如何?

比如仅将ftb_name的筛选条件值从ods_evt_zxg_cft_mta_event_hh 改为dal_meta_table_collect_info_dd, fdb_name的值从ods_base改为dal_meta, 发现此时耗时只需0.01s.

explain select * from dal_meta_table_par_info_d 
where fstore_server='tdw_cft' and ftb_name='dal_meta_table_collect_info_dd' 
      and fdb_name='dal_meta' 
order by fpar_name desc
limit 10;

图4

分析:上述sql和问题sql一摸一样, 只是where条件值换了, 居然表现走向两个极端. 执行计划显示mysql此时自动选择的确是最佳的uk索引. 故可以断定mysql底层在选择索引的时候, 是一个动态调整的过程, 会基于数据分布情况进行动态选择(可能是最合适的也可能选择了很差性能的索引)

3.3 尝试3 - 避免排序

将排序字段去除, 也是可以避免慢查询, 如下

explain select * from dal_meta_table_par_info_d
where fstore_server='tdw_cft' and ftb_name='ods_evt_zxg_cft_mta_event_hh'
        and fdb_name='ods_base'  
limit 10;

图5

分析: 走了uk索引,并且查询耗时也是毫秒级.

3.4 尝试4 - limit调整

只将最开始的慢sql中limit 10改为limit 20, 会如何呢? 结果耗时也是毫秒

explain select * from dal_meta_table_par_info_d  
where fstore_server='tdw_cft' and ftb_name='ods_evt_zxg_cft_mta_event_hh'
        and fdb_name='ods_base'
order by fpar_name desc
limit 20;

图6

分析: 预估rows翻了一倍(从limit 10时的7379 到了14758), mysql引擎此时认为还不如先走联合索引后再排序来的更高效, 因此就自动切到了高效的联合索引. (若改到limit 100或者limit 1000,那么rows就会更大) , 实现了高效的查询

图7

四. 上述慢查询优化方案

根据上述分析, 核心解决思路应该就是: sql中强制显式指定索引或者主动影响(干涉)mysql优化器的选择或者调整索引配置, 以达到解决慢sql效果. 具体如下

4.1 强制索引

在查询语句里增加force index (index_name)的指定,但是这种做法需侵入代码进行硬编码, 而且后续难以维护, 比如改了索引名后会出现sql异常. 此外有些ORM框架或者分库分表中间件封装了底层sql, 不支持直接修改

4.2 改造为子查询

子查询里面先按索引查询过滤,然后再排序

不过有时候这种方式不符合sql规范, 因为有些严格场景要避免子查询.不过我们这里是没有这种严格限制.

这也是一种利用索引覆盖机制提升查询效率方法--先通过辅助索引定位到主键或者唯一id,然后再根据主键查询

注:这也类似mysql ICP（Index Condition Pushdown,索引条件下推ICP）优化思想-在取出索引数据的同时将where条件过滤操作放在存储引擎层提前过滤掉不必要的数据,减少server层对存储引擎层的读取,降低扫描io开销,提升整体性能. 默认是开启的,show variables like 'optimizer_switch'; (index_condition_pushdown=on) / show variables like 'optimizer_%';

select * from dal_meta_table_par_info_d 
where fid in (select fid from dal_meta_table_par_info_d where fstore_server='tdw_cft' 
                and ftb_name='ods_evt_zxg_cft_mta_event_hh' and fdb_name='ods_base')
order by fpar_name desc
limit 10;

图8

4.3 索引定义优化或筛选条件改造

1. 增加包含排序字段在内的联合索引, 但是因为索引字段集合太长或者索引字段并非都是全字段索引, mysql会舍弃这种选择 同样无法命中, ,故这里不建议使用

-- alter table dal_meta_table_par_info_d add index ix_tb_ss_par( ftb_name(255), fdb_name(100), fstore_server, fpar_name(50) );

2. 去除fpar_name的单字段索引即删除误用的低效索引, 避免mysql引擎自动选择到它, 不给其机会, 并且该字段的索引效率低其实也没有必要加(fpar_name的区分度仅为0.0002), 因为索引字段应该区分度足够高才真正有效. 因为我们的业务场景中都是必须指定单表名查询, 所以这个方式最为有效且合理

原则: 选择区分度高(cardinality)的列作为索引，区分度计算:count(distinct col)/count(*),

可以通过show index from tb_name来看索引的区分度(基数), 它也是一个采样统计预估值.

 alter table dal_meta_table_par_info_d drop index ix_par_name

3. 如果改为按ftb_uid和fpar_name联合索引筛选,同意会命中高效索引, 仍然有效

explain select * from dal_meta_table_par_info_d
where ftb_uid='d1ee37cd9071a3a946b8832f77a3d34f' 
order by fpar_name desc limit 10;

图9

4.4 去除排序

sql不进行排序, 转移到代码层面, 这种方式不通用, 如果筛选后数据量大, 那么会对服务内存造成压力

explain select * from dal_meta_table_par_info_d
where fstore_server='tdw_cft' and ftb_name='ods_evt_zxg_cft_mta_event_hh' 
and fdb_name='ods_base'   limit 10;

图10

4.5 排序字段优化

因为fpar_date与 fpar_name的排序结果相关, 而fpar_date也未定义索引, 故可以order by fpar_name 改为order by fpar_date, 这种方法依赖于具体的业务特性场景,

图11

4.6 调大limit值

其核心思路是通过调大limit值, 让mysql优化器认为, 使用排序字段索引的代价很高了, 通过改变数据特征诱导mysql优化器进行索引的转移.

但是这种方式是一种不可持续的临时手段, 不具备通用性. 因为具体limit值多大会命中mysql优化选择策略, 这个很难保证无法固定,, 而且随着数据的变化, 也无法长期有效.

explain select * from dal_meta_table_par_info_d where fstore_server='tdw_cft'
 and ftb_name='ods_evt_zxg_cft_mta_event_hh' and fdb_name='ods_base' 
 order by fpar_name desc limit 100 ;

图12

综上, 索引定义的时候需避免将区分度(cardinality)低的字段设计为索引, 并尽量利用覆盖索引特性减少回表IO次数, 提升查询效率.

五. 优化器选择索引原理

mysql底层的查询架构如下, 其中在查询优化器阶段进行最终索引的确定.

图13. 来自https://juejin.cn/post/7138688524567445534

选择索引是MySQL优化器的工作。而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行sql。扫描行数是影响执行代价的因素之一, 扫描的行数越少，说明访问磁盘数据的次数越少，CPU消耗越少. 不过扫描行数并不是唯一断标准，还会结合是否使用了临时表、是否排序等因素进行综合判断.

图14

mysql优化器选择有如下考虑因素：扫描行数、是否使用临时表、是否排序等等.若排序索引的预估行数row小并且没有filesort, 因此很有可能mysql会自动选择到它.

图15

需注意的是explain的rows是MySQL预估的行数，是根据查询条件、索引统计和limit综合考虑出来的预估行数, 它是基于innodb数据页平均值的采样统计而来(基于代价权衡), 并非完全准确 (mysql引擎会定时自动重新统计索引信息数据 或者 通过analyze table来主动触发)。

图16

MySQL的优化器主要是将SQL经过语法解析/词法解析后得到的语法树，通过MySQL的数据字典和统计信息的内容，经过一系列运算，从而得出一个执行计划树的构成。之后MySQL按照执行树的要求，计算得出结果。也就是说优化器的输入是一个语法树，输出是一个执行树（也称为执行计划）

查看mysql优化器配置: show variables like 'optimizer_switch'; show variables like 'optimizer_%';

开启优化器跟踪 : set session optimizer_trace='enabled=on';

在执行完查询语句后，在执行以下的select语句可以查看具体的优化器执行过程 : select * from information_schema.optimizer_trace;

六. 总结-索引分析最佳实践

1. 排序场景下特别注意索引的效率以及合理性, 避免在区分度低的字段上进行排序, 若必须这样做, 要慎重考虑sql效能, 比如优化具体业务逻辑或者索引选择优化技术
2. 杜绝在区分度低的字段上建立索引, 这样可以避免mysql引擎自动选择到它的可能性
3. 遇到慢查询, 结合explain分析sql执行性能以及索引命中情况
4. 利用好索引覆盖机制, 可以有效提升查询效率
5. order by 主键/普通索引 情况下, 在有limit值且到达某个范围时(无法提前预估), mysql优化器会认为, 为了避免排序消耗即先考虑避免走file_sort或者减少回表代价, 可能会命中非最佳的主键/普通索引(即排序字段的索引), 但是可能此时通过where条件中的索引却是更高效的.
6. 结合mysql优化器跟踪(optimizer_trace), 查看具体的优化器执行过程
7. 索引统计信息不准确, 可以用analyze table来触发mysql引擎重新统计预估索引数据.

【选题思路】

mysql目前是大部分公司都会使用的一个主流的OLTP数据库引擎, 而索引机制是其最为核心最为关键的能力之一. 如何准确、高效的运用mysql索引直接影响到在线场景的查询性能. 特别是很多慢查询场景都是因为开发者对索引原理、mysql优化器原理理解和掌握不深导致的问题. 本文通过线上生产环境遇到的一个实际问题, 引出本文重点-mysql索引选择原理探究, 并对问题进行详细的分析和探索, 然后给出了多种解决思路和方案, 助力开发者深度掌握mysql底层索引选择机制并付诸实践.

【创作提纲】

1. 背景与问题

2. 慢查询sql与数据规模情况

3. 问题分析与探索

3.1 尝试1 - force index

3.2 尝试2 - 改变条件值

3.3 尝试3 - 避免排序

3.4 尝试4 - limit调整

4. 满查询优化方案

4.1 强制索引

4.2 改造为子查询

4.3 索引定义优化或筛选条件改造

4.4 去除排序

4.5 排序字段优化

4.6 调大limit值

5. mysql优化器选择索引原理

6. 总结-索引分析最佳实践