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讲
登录|注册

17 | 如何正确地显示随机消息?

林晓斌 2018-12-21
我在上一篇文章,为你讲解完 order by 语句的几种执行模式后,就想到了之前一个做英语学习 App 的朋友碰到过的一个性能问题。今天这篇文章,我就从这个性能问题说起,和你说说 MySQL 中的另外一种排序需求,希望能够加深你对 MySQL 排序逻辑的理解。
这个英语学习 App 首页有一个随机显示单词的功能,也就是根据每个用户的级别有一个单词表,然后这个用户每次访问首页的时候,都会随机滚动显示三个单词。他们发现随着单词表变大,选单词这个逻辑变得越来越慢,甚至影响到了首页的打开速度。
现在,如果让你来设计这个 SQL 语句,你会怎么写呢?
为了便于理解,我对这个例子进行了简化:去掉每个级别的用户都有一个对应的单词表这个逻辑,直接就是从一个单词表中随机选出三个单词。这个表的建表语句和初始数据的命令如下:
mysql> CREATE TABLE `words` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`word` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=0;
while i<10000 do
insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
set i=i+1;
end while;
end;;
delimiter ;
call idata();
为了便于量化说明,我在这个表里面插入了 10000 行记录。接下来,我们就一起看看要随机选择 3 个单词,有什么方法实现,存在什么问题以及如何改进。

内存临时表

首先,你会想到用 order by rand() 来实现这个逻辑。
mysql> select word from words order by rand() limit 3;
这个语句的意思很直白,随机排序取前 3 个。虽然这个 SQL 语句写法很简单,但执行流程却有点复杂的。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(99)

  • 老杨同志 置顶
    对应单词这种总量不是很多的数据,第一感觉应该装jdk缓存或者redis缓存。由于需要随机访问,数组比较好。假如一个单词平均10个字节,10*10000,不到1M就装下了。
    如果一定要用数据库来做,老师的方案1比较好,空洞的问题,如果单词库不变,可以在上线前整理数据,把空洞处理调。比如:原来单词存在A表,新建B表 ,执行 insert into B(word) select word from A. B的id是自增的,就会生成连续的主键。当然如果A表写比较频繁,且数据量较大,业务上禁用 这种写法,RR的隔离级别会锁A表

    作者回复: 重新整理表这个思路很赞👍🏿

    看得出你是业务经验很丰富啊,这几次问题,对底层实现和业务功能的平衡,考虑点很不错

    2018-12-21
    1
    74
  • 雪中鼠 置顶
    如果按照业务需求,随机取三个,数据库还在设计阶段,可以增加一个主键字段,用来记录每行记录的rowid,这样一万行,那就是连续的一万,然后随机,用该随机rowid回表查询该行记录

    作者回复: 这个也是个好方法,就是确保连续,可以快速的得到C和几个偏移量

    2018-12-21
    6
  • 吴宇晨
    我觉得可以按Y排个序,第一条取完,拿到对应id,然后有一条语句就是where id大于xxx,limit y2-y1,1

    作者回复: 抓住了关键点👍🏿

    2018-12-21
    3
    31
  • HuaMax
    假设Y1,Y2,Y3是由小到大的三个数,则可以优化成这样,这样扫描行数为Y3
    id1 = select * from t limit @Y1,1;
    id2= select * from t where id > id1 limit @Y2-@Y1,1;
    select * from t where id > id2 limit @Y3 - @Y2,1;

    作者回复: 👍🏿

    2018-12-21
    1
    24
  • 岁月安然
    为什么随机算法2比order by rand()的代价小很多?
    因为随机算法2进行limit获取数据的时候是根据主键排序获取的,主键天然索引排序。获取到第9999条的数据也远比order by rand()方法的组成临时表R字段排序再获取rowid代价小的多。

    作者回复: 对的,

    你是第一个回答正文中间问题的😄👍🏿

    2018-12-21
    24
  • 慧鑫coming
    又到周五了,开心😜
    2018-12-21
    13
  • 倪大人
    课后题可以在随机出Y1、Y2、Y3后,算出Ymax、Ymin
    再用 select id from t limit Ymin,(Ymax - Ymin);
    得到id集后算出Y1、Y2、Y3对应的三个id
    最后 select * from t where id in (id1, id2, id3)
    这样扫描的行数应该是C+Ymax+3

    作者回复: 漂亮

    2018-12-21
    2
    11
  • 梦康
    翻了下评论,没人问优先队列排序里的 row_size 和 rows_estimate 是如何计算的。想了半天没想明白。

    作者回复: 帮你贴下你自己的答案哈 https://mengkang.net/1338.html

    2019-02-13
    7
  • freesia
    从上一讲到这一讲,我发现老师在处理问题时,提出的方法就不再是单纯依靠MySQL解决,因为可能会耗费很多资源,而是把问题分担一部分到客户端,比如客户端拿到数据后再排序,或者客户端产生随机数再到MySQL中去查询。

    作者回复: 嗯嗯,MySQL 的代码和业务代码都是代码😄 配合起来用

    2018-12-23
    7
  • 李皮皮皮皮皮
    我经常在文中看到多个事务的执行时序。线下做实验的时候,是怎么保证能按这个时序执行呢?

    作者回复: 开两个窗口,按顺序执行命令哦

    2018-12-21
    7
  • 王飞洋
    归并排序,优先队列,算法无处不在。

    作者回复: 要说算法还是隔壁王老师讲的专业,这里咱们就只追求MySQL 里面用到的,能给大家讲明白就行了😄

    2018-12-21
    7
  • 无眠
    一直比较疑惑什么情况下会产生临时表Using temporary,希望老师指点下

    作者回复: 查询需要临时表,比如我们这个例子里,需要临时表来放rand()结果

    2018-12-21
    4
  • big-new
    您好,老师?请问全字段排序、rowid排序 与 临时文件算法(归并排序算法)、优先队列排序算法的 作用点分别在哪里?赶紧这两种概念分不清楚了。麻烦帮忙解答下疑惑~,谢谢?
    2019-03-25
    1
    3
  • 大神仙
    老师,limit n order by 非索引字段 进行分页查询。数据库符合条件的count=147000条,分页查询count也正确,但是分页查询出的147000条数据中存在重复数据。
    1,这个我看网上解释是因为堆排序算法不稳定导致的。这个说法是否正确。
    2,我查了很多资料,没找到,或者您能给我个指导,我去查查

    作者回复: 我的理解是说,
    你碰到了这种情况:

    limit n, a; 显示a条记录;
    然后 limit n+a, a显示第二组a条件记录;
    这两组a个记录出现了重复数据对吧,

    是的,是因为limit 有可能出现两种算法,比如直接排序和优先队列排序,就是不同的结果。
    而limit 后面的参数,是会影响算法的

    2019-02-20
    3
  • Sinyo
    你可能会问,这里也用到了 limit,为什么没用优先队列排序算法呢?原因是,这条 SQL 语句是 limit 1000,如果使用优先队列算法的话,需要维护的堆的大小就是 1000 行的 (name,rowid),超过了我设置的 sort_buffer_size 大小,所以只能使用归并排序算法。

    老师,上面的limit 1000 不是才14000么?14000小于32768的还是优先队列排序算法把?这里是不是10000少写了个0呢?

    作者回复: 没有少0哈

    好问题

    最小堆的维护代价比数组大,不只是14*1000哦

    2019-02-13
    3
  • Mr.Strive.Z.H.L
    老师你好,回顾这篇的时候突然有个疑惑。
    执行器只是调引擎接口获取结果,但是我认为order by的排序过程应该是在执行器执行的吧?内存临时表使用的memory引擎,应该也是在server端,而磁盘临时表应该是innodb内部。
    我这么理解对吗?还是说整个排序过程全部都在innodb内部执行?
    对此突然有点疑惑………

    作者回复: mysql的执行过程都是由执行器来调度的

    不论创建memory临时表还是innodb临时表,都是执行器调用引擎的创建表接口实现的

    写数据和读数据也是

    排序这个操作,是在server层做的

    2018-12-29
    3
  • 某、人
    今天这个问题我的理解转换成sql是:
    mysql> select count(*) into @C from t1;
    set @Y = floor(@C * rand());
    set @Y1 = floor(@C * rand());
    set @Y2 = floor(@C * rand());
    select LEAST(@Y,@Y1,@Y2) into @Y4;
    select GREATEST(@Y,@Y1,@Y2) into @Y6;
    select floor((@Y6+@Y4)/2) into @Y5;
    set @sql = concat("select id into @id from t1 limit ", @Y4, ",1");
    set @sql1 = concat("select id into @id1 from t1 where id>@id limit ", @Y5-@Y4, ",1");
    set @sql2 = concat("select id into @id2 from t1 where id>@id1 limit ", @Y6-@Y5, ",1");
    prepare stmt from @sql;
    prepare stmt1 from @sql1;
    prepare stmt2 from @sql2;
    execute stmt;
    execute stmt1;
    execute stmt2;
    DEALLOCATE prepare stmt;
    DEALLOCATE prepare stmt1;
    DEALLOCATE prepare stmt2;
    select * from t1 where id in (@id,@id1,@id2);
    感觉mysql不太适合处理随机数的问题,稍稍有点复杂。
    不过这两节课收获很多,对order by排序理解又深入不少,原来堆排序是放limit m,m行如果比sort_buffer占用空间小,则先把m行放进数据集里,然后在把表里的数据一行一行取出来做比较。得出的结果,在根据MRR回表取数据。
    老师,我有一个问题:
    堆排序,如果比较的值是相等的情况下,会不会替换在sort_buffer里?我感觉是不会,如果不会才能解释得通排序值相等,id不等的情况,不管是大顶堆还是小顶堆,得到的结果集都是id相对更小的
    2018-12-23
    3
  • daydaynobug
    老师,在sort_buffer中排序总是会使用快排吗,这个跟待排序的数据量有关系吗,会不会使用其他的排序算法啊

    作者回复: 都在内存的话就用快排

    需要用到文件的话,有用到合并排序

    2019-04-23
    2
  • 阿狸爱JAVA
    感觉老师的思路很宽广,就像一个大宝藏,方案一不行还有方案二,方案二不行还有方案三,并且每个方案都能给出具体的性能比较与证据,而自己自能顺着老师的思路还能明白,可是一旦扩展开来,便大脑一片空白

    作者回复: 加油慢慢来哈~~😆

    2019-01-31
    2
  • 胡楚坚
    老师,只要sort buffer 足够,就采用优先队列排序,而不用管到底是全字段排序还是rowid排序,对吗?

    作者回复: 前提是有limit 子句哈

    2019-01-20
    2
收起评论
99
返回
顶部