MySQL 实战 45 讲
林晓斌
网名丁奇,前腾讯云数据库负责人
224873 人已学习
新⼈⾸单¥68
登录后,你可以任选4讲全文学习
课程目录
已完结/共 49 讲
实践篇 (37讲)
特别放送 (1讲)
结课测试 (1讲)
MySQL 实战 45 讲
15
15
1.0x
00:00/00:00
登录|注册

10 | MySQL为什么有时候会选错索引?

统计信息更新机制
统计方法
基数和区分度
解决优化器误判的问题
解决索引统计不准确的问题
新增或删除索引
修改语句引导优化器
使用force index
扫描行数估算
索引统计信息
扫描行数的判断
下期问题
小结
索引选择异常和处理
优化器的逻辑
MySQL索引选择问题

该思维导图由 AI 生成,仅供参考

前面我们介绍过索引,你已经知道了在 MySQL 中一张表其实是可以支持多个索引的。但是,你写 SQL 语句的时候,并没有主动指定使用哪个索引。也就是说,使用哪个索引是由 MySQL 来确定的。
不知道你有没有碰到过这种情况,一条本来可以执行得很快的语句,却由于 MySQL 选错了索引,而导致执行速度变得很慢?
我们一起来看一个例子吧。
我们先建一个简单的表,表里有 a、b 两个字段,并分别建上索引:
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
然后,我们往表 t 中插入 10 万行记录,取值按整数递增,即:(1,1,1),(2,2,2),(3,3,3) 直到 (100000,100000,100000)。
我是用存储过程来插入数据的,这里我贴出来方便你复现:
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
接下来,我们分析一条 SQL 语句:
mysql> select * from t where a between 10000 and 20000;
你一定会说,这个语句还用分析吗,很简单呀,a 上有索引,肯定是要使用索引 a 的。
你说得没错,图 1 显示的就是使用 explain 命令看到的这条语句的执行情况。
图 1 使用 explain 命令查看语句执行情况
从图 1 看上去,这条查询语句的执行也确实符合预期,key 这个字段值是’a’,表示优化器选择了索引 a。
不过别急,这个案例不会这么简单。在我们已经准备好的包含了 10 万行数据的表上,我们再做如下操作。
确认放弃笔记?
放弃后所记笔记将不保留。
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
公开
同步至部落
取消
完成
0/2000
荧光笔
直线
曲线
笔记
复制
AI
  • 深入了解
  • 翻译
    • 英语
    • 中文简体
    • 中文繁体
    • 法语
    • 德语
    • 日语
    • 韩语
    • 俄语
    • 西班牙语
    • 阿拉伯语
  • 解释
  • 总结

本文深入探讨了MySQL索引选择问题,通过一个案例展示了错误的索引选择可能导致查询性能下降的情况。作者首先介绍了一个简单的表结构和插入数据的存储过程,然后展示了一条查询语句的执行情况。通过对表进行数据操作后再次执行相同的查询语句,发现MySQL选择了错误的索引,导致了性能下降。文章讨论了MySQL索引选择机制的相关内容,包括优化器选择索引的逻辑、索引统计信息的采样统计方法以及优化器预估的扫描行数等。作者提出了解决方法,如使用analyze table命令重新统计索引信息。总的来说,本文为读者提供了有益的技术参考,帮助读者更好地理解和解决MySQL索引选择的问题。

仅可试看部分内容,如需阅读全部内容,请付费购买文章所属专栏
《MySQL 实战 45 讲》
新⼈⾸单¥68
立即购买
登录 后留言

全部留言(262)

  • 最新
  • 精选
  • 路过
    置顶
    老师,关于本章中的“基数”(cardinality)问题。既然已经为列a创建了索引,即有专门的数据页存放索引。遍历索引是很快的,从而得到“基数”的值应该很快呀。为何要到原始的数据页中,找N页,统计上面不同的值呢?有点多此一举啊。如果这样操作,会导致信息不准确,比如本来一个页中有50条数据,后来其中20条数据被删除了,空间没有被释放,这导致统计的信息就发生偏差。基数信息就更不准确了。 从原始页中计算“基数”,是不是考虑到索引页中的数据具有滞后性,即更新了表中数据,要过一会才更新索引页? 请老师指正,谢谢!

    作者回复: 啊,误会了,确实是哪个索引的基数就是在哪个索引树上拿的。 你的理解是对的,我文中也是这个意思哦😓

    2018-12-05
    7
    36
  • 某、人
    置顶
    今天这个问题不是特别明白为什么。session A开启了一致性读,session B delete或者insert,之前记录都已经放进了undo了。二级索引的记录也写进了redo和change buffer,应该说删除了索引页也不影响session A的重复读。估计是开启了一致性读之后,在这个事务执行期间,不能释放空间,导致统计信息变大。还是需要老师解释下具体的细节 今天有两个问题,想请教下老师 1.我的理解是由于B是查找(50000,100000),由于B+树有序,通过二分查找找到b=50000的值,从50000往右扫描,一条一条回表查数据,在执行器上做where a(1,1000)的筛选,然后做判断是否够不够limit的数,够就结束循环。由于这里b(50000,100000)必然不存在a(1,1000),所以需要扫描5W行左右.但是如果把a改为(50001,51000),扫描行数没有变。那么是因为优化器给的扫描行数有问题还是执行器没有结束循环?为什么不结束循环? (好像rows能直观展示limit起作用,必须在执行器上过滤数据,不能在索引上过滤数据,不知道为什么这样设计) 2.假设b上数据是会有很多重复的数据,b的最大值也存在多行重复 select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b desc limit 1; 这里倒序去扫描b索引树,选取的是b值最大,id值为一个固定值(既不最大也不最小) select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b desc limit 1; 由于这里选取的是a索引,排序不能用到索引,只能用优化排序.选取的是b值最大,id值最小那一行 这就是典型的两条相同的sql,但是索引选择的不同,出现的数据不一致。 所以如果是order by b,a就可以避免这种情况的引起的不一致,也可以避免堆排序造成的不一致 但是如果是asc没有出现这种情况。这里出现不一致,应该还不是由于堆排序造成的。这是什么原因造成的?

    作者回复: 1. 好问题,而且你做了个不错的对照实验。是的,加了limit 1 能减少扫描多少行,其实优化器也不确定,【得执行才知道】,所以显示的时候还是按照“最多可能扫多少行”来显示。 2. 你这个例子里,如果确实是按照b扫描了,应该肯定是ID最大值呀,除非ID最大的那个记录,a条件不满足。但是一定是“满足a条件里面最大的那个ID的”,你再验证下。 而如果是用了a, 那就有临时表排序,临时表排序有三种算法,还分内存还是磁盘临时表… 这里展开不了了,后面《order by是怎么工作的》这篇会讲。

    2018-12-05
    3
    72
  • bowenz
    置顶
    在5.7.21 percona 版本实验,未出现案例1的情况 。 dev02> select @@global.tx_isolation,@@tx_isolation,version(),"session A"; +-----------------------+-----------------+---------------+-----------+ | @@global.tx_isolation | @@tx_isolation | version() | session A | +-----------------------+-----------------+---------------+-----------+ | REPEATABLE-READ | REPEATABLE-READ | 5.7.21-20-log | session A | +-----------------------+-----------------+---------------+-----------+ dev02> start transaction with consistent snapshot; Query OK, 0 rows affected (0.00 sec) dev02> commit; Query OK, 0 rows affected (0.00 sec) dev02> select now() ; +---------------------+ | now() | +---------------------+ | 2018-12-04 22:03:48 | +---------------------+ 1 row in set (0.00 sec) dev02> select @@global.tx_isolation,@@tx_isolation,version(),"session B"; +-----------------------+-----------------+---------------+-----------+ | @@global.tx_isolation | @@tx_isolation | version() | session B | +-----------------------+-----------------+---------------+-----------+ | REPEATABLE-READ | REPEATABLE-READ | 5.7.21-20-log | session B | +-----------------------+-----------------+---------------+-----------+ 1 row in set, 2 warnings (0.00 sec) dev02> delete from t; Query OK, 100000 rows affected (0.51 sec) dev02> call idata(); Query OK, 1 row affected (2 min 38.34 sec) dev02> select now(); +---------------------+ | now() | +---------------------+ | 2018-12-04 22:03:58 | +---------------------+ 1 row in set (0.00 sec) dev02> explain select * from t where a between 10000 and 20000; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | 1 | SIMPLE | t | NULL | range | a | a | 5 | NULL | 10001 | 100.00 | Using index condition |

    作者回复: Session A提交早了… 从上到下按照时间顺序执行哈

    2018-12-05
    6
    15
  • 某、人
    趁着答案公布之前的最后时间,再来尝试性答一下这个题 1.为什么没有session A,session B扫描的行数是1W 由于mysql是使用标记删除来删除记录的,并不从索引和数据文件中真正的删除。 如果delete和insert中间的间隔相对较小,purge线程还没有来得及清理该记录。 如果主键相同的情况下,新插入的insert会沿用之前删除的delete的记录的空间。 由于相同的数据量以及表大小,所以导致了统计信息没有变化 2.为什么开启了session A,session B扫描行数变成3W 由于session A开启了一致性读,目的为了保证session A的可重复读,insert只能 另起炉灶,不能占用delete的空间。所以出现的情况就是delete虽然删除了,但是 未释放空间,insert又增加了空间。导致统计信息有误

    作者回复: 👍🏿

    2018-12-06
    20
    353
  • Ying
    现学现用 今天有个500万的表 分页查询特别慢。 select * from table where create_time and create_time>=时间戳 and create_time<=时间戳 and subtype='xx' and type='xx' and company_id =x order by create_time limited 90,30 ; 已经建立了组合索引 union_index包括字段 create_time subtype type company_id 但是 explain 发现竟然走了create_time 的索引 语句里加了一个use index(union_index) ,立马好了 真正的解决了客户的实际问题啊。 感谢老师

    作者回复: 👍🏿 而且发评论的时候还做了很细致地脱敏,赞

    2018-12-05
    13
    67
  • 梁中华
    假如要查 A in () AND B in (), 怎么建索引?

    作者回复: 好问题 where A in (a,b,c) AND B in (x,y,z) 会转成 (A=a and B=x) or (A=a and B=y) or (A=a and B=z) or (A=b and B=x) or (A=b and B=y) or (A=b and B=z) or (A=c and B=x) or (A=c and B=y) or (A=c and B=z)

    2019-02-01
    17
    50
  • 斜面镜子 Bill
    问题的思考: 我理解 session A 开启的事务对 session B的delete操作后的索引数据的统计时效产生了影响,因为需要保证事务A的重复读,在数据页没有实际删除,而索引的统计选择了N个数据页,这部分数据页不收到前台事务的影响,所以整体统计值会变大,直接影响了索引选择的准确性;

    作者回复: 👍🏿

    2018-12-05
    4
    48
  • geraltlaush
    公司测试机器IO性能太差,插十万条要27分钟,做这个文章的实验要1个小时以上

    作者回复: ……… 不会吧,插入10万条27分钟… 你把innodb_flush_log_at_trx_commit 和 sync_binlog都设置成0试试

    2018-12-05
    12
    41
  • 沉浮
    图十下面第二段 现在 limit b,a 这种写法,要求按照 b,a 排序,就意味着使用这两个索引都需要排序。 应该是order by b,a吧 另外有个问题请教林老师,根据经验大表增加索引的时候比较慢,这个是理解的,但是删除索引的时候能做到秒删,这个什么原理呢?

    作者回复: 是,已经修改了,谢谢。 删除的时候是标记删除,所以很快。 建索引是要扫描数据和真正生成索引树,是会慢些

    2018-12-05
    34
  • XD
    谢谢老师的解答,我之前一直以为这个操作也是在存储层进行的。 那执行器调用存储层的接口是不是只能获取到最原始的数据,后续的加工,比如order,join和group操作也都是在执行器里进行的吗?对应的buffer和内存临时表也都是server层的东西?

    作者回复: 是的,你提到的这些,都在server层 很早之前连过滤数据都在server层,后来有了index condition pushdown下推了一点到引擎层

    2019-02-26
    32
收起评论
显示
设置
留言
99+
收藏
沉浸
阅读
分享
手机端
快捷键
回顶部