五分钟,让你明白MySQL是怎么选择索引

数据库 MySQL
索引是为加速查询速度,创建的索引也符合所有规则,但MySQL就是不使用理想的索引,导致查询速度变慢并产生大量慢查询记录。

今天就从这个问题来聊聊MySQL选择索引时都做一些什么事情。

[[433710]]

一、如何选择索引

影响优化器的几大因素

一条查询SQL执行需要经过连接器、分析器、优化器、执行器,而选择索引的重任就交给了优化器。

优化器在多个索引中选择目的是为了找出执行代价最低的方案。

影响优化器选择无非就这几个因素,扫描行数、是否使用了临时表、是否使用文件排序。

临时表、文件排序这个两个点会在后期文章给大家慢慢引出,今天只聊扫描行数。

扫描行数越少则访问磁盘数据的次数就越少,消耗的CPU资源越少。

那么这个扫描行数是从哪里取的呢?

扫描行数从何而来?

创建索引一直提倡大家给区分度高的列建立索引,在一个索引上不同值的个数称之为基数(cardinality)。

使用show index from table_name可以查看每个索引的基数是多少。

 

五分钟,让你明白MySQL是怎么选择索引

 

索引基数

索引基数怎么计算

MySQL使用采样统计的方法,会选出N个数据页,每个数据页大小16kb,接着统计选出来的数据页上的不同值就会得到一个平均值,用平均值在乘以索引的页面数得到的结果就是这个索引的基数。

表数据是持续增加或删减的,统计的这个数据也不是时时变化的,当变更的数据超过1/M时会自动触发重新计算。

这个M是根据参数innodb_stats_persistent的值选则的,设置为on值为10,设置为off值为16。

索引基数通过这种方式计算不是精准的但也差不了多少

为什么优化器选择了扫描行数多的索引?

第一种情况

表增删十分频繁,导致扫描行数不准确

第二种情况

假设你主键索引扫描行数是10W行,而普通索引需要扫描5W行,这种情况就会遇到优化器选择了扫描行数多的。

在索引那一期文章中知道主键索引是不需要回表的,找到值直接就返回对应的数据了。

而普通索引是需要先拿到主键值,再根据主键值获取对应的数据,这个过程优化器选择索引时需要计算的一个成本。

如何解决这种情况

扫描行数不准确时可以执行analyze table table_name命令,重新统计索引信息,达到预期优化器选择的索引。

二、索引选择异常如何处理

方案一

在MySQL中提供了force index来强制优化器使用这个索引。

使用方法:select * from table_name force index (idx_a) where a = 100;

但别误解force index的使用方法,之前在代码中看到这样一个案例,给查询列使用了函数操作导致使用不上索引,然后这哥们就直接使用force index,肯定不行的哈!

当优化器没有正确选择索引时是可以使用这种方案来解决。

缺点

使用force index的缺点相信大家也知道就是太死板,一旦索引名字改动就会失效。

方案二

删掉误选的索引,简单粗暴,很多索引建立其实也是给优化器的一个误导,直接删掉即可。

方案三

修改SQL语句,主动引导MySQL使用期望的索引,一般情况这种做法使用的很少除非你对系统十分熟悉,否则尽量少操作。

三、总结

优化器选择索引首先会根据扫描行数再由执行成本决定。

当索引统计信息不准确时,使用analyze table 解决。

优化器选择了错误的索引,只用force index来快速矫正,再通过优化SQL语句来引导优化器选择正确的索引,最暴力的手法是直接删除误选的索引。

 

责任编辑:华轩 来源: 今日头条
相关推荐

2021-11-11 15:03:35

MySQLSQL索引

2017-11-20 10:25:20

数据库MySQL索引

2017-06-07 18:40:33

PromiseJavascript前端

2009-11-05 10:56:31

WCF通讯

2020-04-06 14:53:05

MySQL数据库字符串

2009-11-06 16:05:37

WCF回调契约

2023-01-03 08:36:34

MySQL索引

2009-10-29 11:39:52

ADO.NET连接池

2020-11-09 09:59:50

Ajax技术

2019-10-12 08:36:48

Java程序员数据结构

2019-04-01 14:01:13

B+树索引哈希索引算法

2022-03-08 08:39:22

gRPC协议云原生

2021-10-26 14:35:10

架构

2024-02-21 21:19:18

切片Python语言

2023-07-15 18:26:51

LinuxABI

2019-06-05 09:42:53

Kafka App 消息队列

2021-11-08 18:37:45

MySQL解码测试

2015-12-03 14:10:26

systemd容器Linux

2009-11-16 10:53:30

Oracle Hint

2019-05-08 14:02:52

MySQL索引查询优化数据库
点赞
收藏

51CTO技术栈公众号