MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43178 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

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

林晓斌 2018-12-05
前面我们介绍过索引,你已经知道了在 MySQL 中一张表其实是可以支持多个索引的。但是,你写 SQL 语句的时候,并没有主动指定使用哪个索引。也就是说,使用哪个索引是由 MySQL 来确定的。
不知道你有没有碰到过这种情况,一条本来可以执行得很快的语句,却由于 MySQL 选错了索引,而导致执行速度变得很慢?
我们一起来看一个例子吧。
我们先建一个简单的表,表里有 a、b 两个字段,并分别建上索引:
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`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/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(151)

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

    作者回复: 啊,误会了,确实是哪个索引的基数就是在哪个索引树上拿的。

    你的理解是对的,我文中也是这个意思哦😓

    2018-12-05
    9
  • 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
  • 某、人
    趁着答案公布之前的最后时间,再来尝试性答一下这个题
    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
    1
    89
  • Niko.
    我试了几遍 也是没有复现选错索引
    A会话
    mysql> select @@tx_isolation;
    +-----------------+
    | @@tx_isolation |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set, 1 warning (0.01 sec)

    mysql> select @@version
        -> ;
    +------------+
    | @@version |
    +------------+
    | 5.7.22-log |
    +------------+
    1 row in set (0.00 sec)
    mysql> start transaction with consistent snapshot;
    Query OK, 0 rows affected (0.00 sec)

    B会话
    mysql> delete from t;
    Query OK, 100000 rows affected (2.58 sec)

    mysql> call idata();
    Query OK, 1 row affected (24.32 sec)

    mysql> 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 |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)

    为啥呢?
    2018-12-12
    1
    30
  • Leon📷
    公司测试机器IO性能太差,插十万条要27分钟,做这个文章的实验要1个小时以上

    作者回复: ………

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

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

    作者回复: 👍🏿

    2018-12-05
    16
  • 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
    2
    15
  • WL
    把该讲内容总结为几个问题, 大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:

    1.
    mysql如何判断一个查询的扫描行数?
    2.
    索引基数如何计算? 通过哪个参数可以设置索引统计的存储方式?
    3.
    可以重新统计索引信息的命令是什么?
    4.
    如何定位索引选择异常这样的问题?
    5.
    索引选择异常的问题可以有哪几种处理方式?

    2018-12-09
    14
  • Shen
    1、sessionA:start transaction with consistent snapshot;
    2、sessionB:delete from t;
    3、sessionB:call idata();
    4、sessionB:explain select * from t where a between 10000 and 20000;
    +----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+
    | 1 | SIMPLE | t | range | a | a | 5 | NULL | 10000 | Using index condition |
    +----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+


    5.6.43没有复现 案例1的情况
    2019-03-16
    1
    9
  • 梁中华
    假如要查 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
    2
    8
  • 某、人
    谢谢老师的回答.
    1.确实有时候感觉虽然显示了扫描行数比较多,但是limit应该是起了作用的,从执行时间就能看出来。
    2.失误,确实是应该满足a条件id最大的。
    临时表排序之前了解过一点,1.常规排序。2.优化排序 3.优先队列排序(堆排序)
    由于选取的字段没超过max_length_for_sort_data,所以选取的是优化排序,只需要进行一次I/O。
    2018-12-05
    7
  • 沉浮
    图十下面第二段
    现在 limit b,a 这种写法,要求按照 b,a 排序,就意味着使用这两个索引都需要排序。
    应该是order by b,a吧
    另外有个问题请教林老师,根据经验大表增加索引的时候比较慢,这个是理解的,但是删除索引的时候能做到秒删,这个什么原理呢?

    作者回复: 是,已经修改了,谢谢。

    删除的时候是标记删除,所以很快。
    建索引是要扫描数据和真正生成索引树,是会慢些

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

    作者回复: 是的,你提到的这些,都在server层

    很早之前连过滤数据都在server层,后来有了index condition pushdown下推了一点到引擎层

    2019-02-26
    6
  • sky
    按照顺序执行,没能复现选错索引 版本5.6.33。
    Server version: 5.6.33 MySQL Community Server (GPL)
    # Time: 181224 11:32:26
    # User@Host: root[root] @ localhost [] Id: 20852
    # Query_time: 0.012308 Lock_time: 0.000124 Rows_sent: 10001 Rows_examined: 10001
    SET timestamp=1545622346;
    select * from t where a between 10000 and 20000;
    # Time: 181224 11:32:32
    # User@Host: root[root] @ localhost [] Id: 20852
    # Query_time: 0.351406 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 100000
    SET timestamp=1545622352;
    delete from t;
    # Time: 181224 11:32:37
    # User@Host: root[root] @ localhost [] Id: 20852
    # Query_time: 3.234390 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
    SET timestamp=1545622357;
    call idata();
    # Time: 181224 11:32:40
    # User@Host: root[root] @ localhost [] Id: 20852
    # Query_time: 0.014459 Lock_time: 0.000160 Rows_sent: 10001 Rows_examined: 10001
    SET timestamp=1545622360;
    select * from t where a between 10000 and 20000;
    2018-12-24
    6
  • William
    老师,你好。

    我按照文档中的例子。没有复现选择错索引呢?

    select @@global.tx_isolation,@@tx_isolation,version(),"session A";


    start transaction with consistent snapshot;

    select now() ;


    select @@global.tx_isolation,@@tx_isolation,version(),"session B";


    delete from tt;

    call idata();

    select now();

    explain select * from tt where a between 10000 and 20000;

    select @@global.tx_isolation,@@tx_isolation,version(),"session A";

    commit;

    2018-12-17
    5
  • 杰之7
    通过这一讲的学习,阅读完本文,其实核心点就是优化器的索引选择,从而牵引出索引统计的更新机制,以及通过Analyze tabe来进行分析索引。当然有时避免不了可以选择简单粗暴的用Force index来进行索引选择。
    下半部分老师通过实践上的问题给出了索引选择上的方案,一、通过Force index强行选择一个索引。二、通过修改SQL语句来修改索引,三、通过提供一个更合适的索引来给优化器做出选择。
    学习到这里,我想写通过认真阅读老师的每一篇文章,至少我现在开始思考老师每篇文章真正的出发点是什么,然后通过老师的每一步细节来搞懂技术上的问题,不敢说有多大进步,至少我相信我在不久之后可以做数据库技术这件事,感谢和老师一同进步成长。
    2018-12-11
    5
  • William
    使用navicate for mysql .

    开启了2个查询编辑器。

    在1 编辑器中 开启长事务:start TRANSACTION with CONSISTENT SNAPSHOT ;

    然后到2编辑器中, 进行了

    delete from t ;

    call idata();

    explain select *from t where a BETWEEN 1000 and 2000 ;

    依然用了 a索引。

    包括下一篇的例子,我也看了。
    也是按照那个方式模拟的,差别就是我用的navicat ,为什么就无法重现呢。。。。
    2018-12-17
    1
    4
  • Ryoma
    请问使用insert ... on duplicate key update对性能有什么影响呢

    作者回复: 比分成两句快😄

    2018-12-05
    4
  • greatcl
    老师您好,刚学习到这节内容,一直复现不了全表扫描的情况,看了下节课里面的视频,按照一样的步骤操作,Session B里最后还是走a索引,这个会是版本问题吗?在Windows上是5.7.21,Ubuntu上是5.7.23都不行。
    我也把在Ubuntu上的操作步骤录了个视频,如果老师有时间,希望能帮忙看一下哪里出问题了,谢谢!
    https://www.youtube.com/watch?v=LSS9GUUjEmA
    2019-01-31
    3
收起评论
99+
返回
顶部