MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43177 人已学习
课程目录
已完结 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讲
登录|注册

18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?

林晓斌 2018-12-24
在 MySQL 中,有很多看上去逻辑相同,但性能却差异巨大的 SQL 语句。对这些语句使用不当的话,就会不经意间导致整个数据库的压力变大。
我今天挑选了三个这样的案例和你分享。希望再遇到相似的问题时,你可以做到举一反三、快速解决问题。

案例一:条件字段函数操作

假设你现在维护了一个交易系统,其中交易记录表 tradelog 包含交易流水号(tradeid)、交易员 id(operator)、交易时间(t_modified)等字段。为了便于描述,我们先忽略其他字段。这个表的建表语句如下:
mysql> CREATE TABLE `tradelog` (
`id` int(11) NOT NULL,
`tradeid` varchar(32) DEFAULT NULL,
`operator` int(11) DEFAULT NULL,
`t_modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `tradeid` (`tradeid`),
KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
假设,现在已经记录了从 2016 年初到 2018 年底的所有数据,运营部门有一个需求是,要统计发生在所有年份中 7 月份的交易记录总数。这个逻辑看上去并不复杂,你的 SQL 语句可能会这么写:
mysql> select count(*) from tradelog where month(t_modified)=7;
由于 t_modified 字段上有索引,于是你就很放心地在生产库中执行了这条语句,但却发现执行了特别久,才返回了结果。
如果你问 DBA 同事为什么会出现这样的情况,他大概会告诉你:如果对字段做了函数计算,就用不上索引了,这是 MySQL 的规定。
现在你已经学过了 InnoDB 的索引结构了,可以再追问一句为什么?为什么条件是 where t_modified='2018-7-1’的时候可以用上索引,而改成 where month(t_modified)=7 的时候就不行了?
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(92)

  • 老杨同志 置顶
    感谢老师鼓励,我本人工作时间比较长,有一定的基础,听老师的课还是收获很大。每次公司内部有技术分享,我都去听课,但是多数情况,一两个小时的分享,就只有一两句话受益。老师的每篇文章都能命中我的知识盲点,感觉太别爽。

    对应今天的隐式类型转换问题也踩过坑。
    我们有个任务表记录待执行任务,表结构简化后如下:
    CREATE TABLE `task` (
      `task_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
      `task_type` int(11) DEFAULT NULL COMMENT '任务类型id',
      `task_rfid` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '关联外键1',
      PRIMARY KEY (`task_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='任务表';

    task_rfid 是业务主键,当然都是数字,查询时使用sql:
    select * from task where task_rfid =123;
    其实这个语句也有隐式转换问题,但是待执行任务只有几千条记录,并没有什么感觉。
    这个表还有个对应的历史表,数据有几千万
    忽然有一天,想查一下历史记录,执行语句
    select * from task_history where task_rfid =99;
    直接就等待很长时间后超时报错了。
    如果仔细看,其实我的表没有task_rfid 索引,写成task_rfid =‘99’也一样是全表扫描。
    运维时的套路是,猜测主键task_id的范围,怎么猜,我原表有creat_time字段,我会先查
    select max(task_id) from task_history 然后再看看 select * from task_history where task_id = maxId - 10000的时间,估计出大概的id范围。然后语句变成
    select * from task_history where task_rfid =99 and id between ? and ?;

    作者回复: 你最后这个id预估,加上between ,
    有种神来之笔的感觉😄
    感觉隐约里面有二分法的思想
    👍🏿

    2018-12-24
    5
    43
  • 赖阿甘 置顶
    “mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;”
    图6上面那句sql是不是写错了。d.tradeid=l.tradeid是不是该写成l.tradeid = d.tradeid?不然函数会作用在索引字段上,就只能全表扫描了

    作者回复: 这个问题不是等号顺序决定的哈



    好问题

    2018-12-24
    2
    7
  • 可凡不凡 置顶
    1.老师好
    2.如果在用一个 MySQL 关键字做字段,并且字段上索引,当我用这个索引作为唯一查询条件的时候 ,会 造 成隐式的转换吗?
    例如:SELECT * FROM b_side_order WHERE CODE = 332924 ; (code 上有索引)
    3. mysql5.6 code 上有索引 intime 上没有索引
    语句一:
    SELECT * FROM b_side_order WHERE CODE = 332924 ;
    语句二;
      UPDATE b_side_order SET in_time = '2018-08-04 08:34:44' WHERE 1=2 or CODE = 332924;

    这两个语句 执行计划走 select 走了索引,update 没有走索引 是执行计划的bug 吗??

     


    作者回复: 1. 你好😄
    2. CODE不是关键字呀, 另外优化器选择跟关键字无关哈,关键字的话,要用 反‘ 括起来
    3. 不是bug, update如果把 or 改成 and , 就能走索引😄

    2018-12-25
    2
    3
  • 冠超 置顶
    非常感谢老师分享的内容,实打实地学到了。这里提个建议,希望老师能介绍一下设计表的时候要怎么考虑这方面的知识哈😊

    作者回复: 是这样的,其实我们整个专栏大部分的文章,最后都是为了说明 “怎么设计表”、“怎么考虑优化SQL语句”

    但是因为这个不是一成不变的,很多是需要考虑现实的情况,
    所以这个专栏就是想把对应的原理说一下,这样大家在应对不同场景的时候,可以组合来考虑。

    也就是说没有一段话可以把“怎么设计表”讲清楚(或者说硬写出来很可能就是一些general的没有什么针对性作用的描述)

    你可以把你的业务背景抽象说下,我们来具体讨论吧

    2019-01-28
    2
  • 700 置顶
    老师您好,有个问题恳请指教。背景如下,我长话短说:

    mysql>select @@version;
    5.6.30-log

     CREATE TABLE `t1` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `user_id` int(11) NOT NULL, `plan_id` int(11) NOT NULL DEFAULT '0' , PRIMARY KEY (`id`),
      KEY `userid` (`user_id`) USING BTREE, KEY `idx_planid` (`plan_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

     CREATE TABLE `t3` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `status` int(4) NOT NULL DEFAULT '0',
      `ootime` varchar(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_xxoo` (`status`,`ootime`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    t1 和 t3 表的字符集不一样

    sql 执行计划如下:
    explain
    SELECT t1.id, t1.user_id
      FROM t1, t3
     WHERE t1.plan_id = t3.id
       AND t3.ootime < UNIX_TIMESTAMP('2022-01-18')
    +----+-------------+-------+-------+---------------+--------------+---------+--------------+-------+----------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+-------+---------------+--------------+---------+--------------+-------+----------------------------------------+
    | 1 | SIMPLE | t3 | index | PRIMARY | idx_xxoo | 51 | NULL | 39106 | Using where; Using index |
    | 1 | SIMPLE | t1 | ref | idx_planid | idx_planid | 4 | t3.id | 401 | Using join buffer (Batched Key Access) |
    +----+-------------+-------+-------+---------------+--------------+---------+--------------+-------+----------------------------------------+

    我的疑惑是
    1)t3 的 status 没出现在 where 条件中,但执行计划为什么用到了 idex_xxoo 索引?
    2)为什么 t3.ootime 也用到索引了,从 key_len 看出。t3.ootime 是 varchar 类型的,而 UNIX_TIMESTAMP('2022-01-18') 是数值,不是发生了隐式转换吗?

    请老师指点。

    作者回复: 这个查询语句会对t3做全索引扫描,是使用了索引的,只是没有用上快速搜索功能

    2019-01-18
    1
  • 探索无止境
    老师,有道面试题困扰了很久,求指教!题目是这样的,a表有100条记录,b表有10000条记录,两张表做关联查询时,是将a表放前面效率高,还是b表放前面效率高?网上各种答案,但感觉都没有十分的说服力,期待老师的指点!

    作者回复: (这题目改成100万禾10000万比较好)
    如果是考察语句写法,这两个表谁放前面都一样,优化器会调整顺序选择合适的驱动表;

    如果是考察优化器怎么实现的,你可以这么想,每次在树搜索里面做一次查找都是log(n), 所以对比的是100*log(10000)和 10000*log(100)哪个小,显然是前者,所以结论应该是让小表驱动大表。

    2019-01-13
    1
    43
  • Leon📷
    索引字段不能进行函数操作,但是索引字段的参数可以玩函数,一言以蔽之

    作者回复: 精辟😄

    2018-12-24
    1
    33
  • 风轨
    刚试了文中穿插得思考题:当主键是整数类型条件是字符串时,会走索引。
    文中提到了当字符串和数字比较时会把字符串转化为数字,所以隐式转换不会应用到字段上,所以可以走索引。
    另外,select 'a' = 0 ; 的结果是1,说明无法转换成数字的字符串都被转换成0来处理了。

    作者回复: 👍🏿

    2018-12-24
    1
    20
  • 某、人
    SQL逻辑相同,性能差异较大的,通过老师所讲学习到的,和平时碰到的,大概有以下几类:
    一.字段发生了转换,导致本该使用索引而没有用到索引
    1.条件字段函数操作
    2.隐式类型转换
    3.隐式字符编码转换
    (如果驱动表的字符集比被驱动表得字符集小,关联列就能用到索引,如果更大,需要发生隐式编码转换,则不能用到索引,latin<gbk<utf8<utf8mb4)

    二.嵌套循环,驱动表与被驱动表选择错误
    1.连接列上没有索引,导致大表驱动小表,或者小表驱动大表(但是大表走的是全表扫描) --连接列上建立索引
    2.连接列上虽然有索引,但是驱动表任然选择错误。--通过straight_join强制选择关联表顺序
    3.子查询导致先执行外表在执行子查询,也是驱动表与被驱动表选择错误。
    --可以考虑把子查询改写为内连接,或者改写内联视图(子查询放在from后组成一个临时表,在于其他表进行关联)
    4.只需要内连接的语句,但是写成了左连接或者右连接。比如select * from t left join b on t.id=b.id where b.name='abc'驱动表被固定,大概率会扫描更多的行,导致效率降低.
     --根据业务情况或sql情况,把左连接或者右连接改写为内连接

    三.索引选择不同,造成性能差异较大
    1.select * from t where aid= and create_name>'' order by id limit 1;
    选择走id索引或者选择走(aid,create_time)索引,性能差异较大.结果集都有可能不一致
    --这个可以通过where条件过滤的值多少来大概判断,该走哪个索引

    四.其它一些因素
    1.比如之前学习到的是否有MDL X锁
    2.innodb_buffer_pool设置得太小,innodb_io_capacity设置得太小,刷脏速度跟不上
    3.是否是对表做了DML语句之后,马上做select,导致change buffer收益不高
    4.是否有数据空洞
    5.select选取的数据是否在buffer_pool中
    6.硬件原因,资源抢占
    原因多种多样,还需要慢慢补充。

    老师我问一个问题:
    连接列上一个是int一个是bigint或者一个是char一个varchar,为什么被驱动表上会出现(using index condition)?
    2018-12-24
    1
    13
  • 简海青
    执行explain 语句后,使用show warnings; 可以看到MySQL 实际执行的SQL,包括字符集的转换
    2019-05-04
    12
  • 探索无止境
    多表连接时,mysql是怎么选择驱动表和被驱动表的?这个很重要,希望老师可以讲讲
    2018-12-25
    8
  • 可凡不凡
    1.老师对于多表联合查询中,MySQL 对索引的选择 以后会详细介绍吗?

    作者回复: 额,你是第三个提这个问题的了,我得好好考虑下安排😄

    2018-12-24
    1
    6
  • Destroy、
    老师,对于最后回答上一课的问题:mysql> select * from t limit N, M-N+1;
    这个语句也不是取3条记录。 没理解。

    作者回复: 取其中三条…

    2018-12-27
    1
    5
  • Leon📷
    老师,经常面试被问到工作中做了什么优化,有没有好的业务表的设计,请问老师课程结束后能不能给我们一个提纲挈领的大纲套路,让我们有个脉络和思路来应付这种面试套路

    作者回复: 有没有好的业务表的设计,这类问题我第一次听到,能不能展开一下,这样说不要清楚面试官的考核点是啥…

    2018-12-25
    4
  • asdf100
    在这个例子里,放弃了树搜索功能,优化器可以选择遍历主键索引,也可以选择遍历索引 t_modified,优化器对比索引大小后发现,索引 t_modified 更小,遍历这个索引比遍历主键索引来得更快。

    优化器如何对比的,根据参与字段字段类型占用空间大小吗?

    作者回复: 优化器信息是引擎给的,
    引擎是这么判断的

    2018-12-24
    4
  • crazyone
    老师你好,我在执行explain的时候,发现extra 里面 有using where,using index ,using index condition 能具体讲下这几个的区别吗?

    作者回复: Using index是覆盖索引
    Using index condition 是索引下推

    2018-12-26
    3
  • 大坤
    之前遇到过按时间范围查询大表不走索引的情况,如果缩小时间范围,又会走索引,记得在一些文章中看到过结果数据超过全表的30%就会走全表扫描,但是前面说的时间范围查询大表,这个时间范围绝对是小于30%的情况,想请教下老师,这个优化器都是在什么情况下会放弃索引呢?

    作者回复: 总体来说就是判断哪种方式消耗更小,选哪种

    2018-12-25
    3
  • greatcl
    刚刚帮一个同事看SQL慢的问题,帮他找出来问题,并拿出这篇文章让他看,哈哈

    作者回复: 👍 这个感觉太赞啦

    2019-05-22
    2
  • 唯她命
    优化方案 CONVERT(l.tradeid USING utf8) 会丢精度吧?

    作者回复: 是的

    前提是l.tradeid 没有utf8mb4的哈

    2019-03-14
    2
  • 饭粒
    感觉要使用索引就不能“破坏”索引原有的顺序,这节的函数操作,隐式转换都“破坏”了原有的顺序。前一节的select * from t where city in in (“杭州”," 苏州 ") order by name limit 100; 同样是破坏了 (city,name) 联合索引的递增顺序,类似的还有使用联合索引,一个字段DESC,一个ASC。

    作者回复: 👍 总结得很好

    “顺势而查”才能用上索引😆

    2019-02-25
    2
收起评论
92
返回
顶部