mysql order rand 性能优化

往往是一些不起眼的东西更暗含玄机,记得以前有个这样的需求,在网站首页给用户随机推荐词库中的10个热门行业词,而且每一次都要不一样。我们使用了最简单的实现方式,MySQL里order by rand() 排序取出前10个。但是上线后发现这样写的性能尤其底下。

场景

表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14

create table words
(
id int unsigned auto_increment
primary key,
class_name varchar(255) not null comment '分类名称',
pid int null
)
comment '行业词库';

create index words_class_name_index
on words (class_name);


SQL 语句

1
explain  select pid,class_name from words order by rand() limit 3

rand

这里的 Extra Using temporary 使用临时表 Using filesort 使用排序

临时表

临时表分为内部临时表和外部临时表,内部临时表是属于一种轻量级保持在会话中的表结构,它一般是用于性能优化的。外部临时表用于一些特性的业务场景(我还没找到这个场景)。减少临时表的使用可以有效的提高SQL查询的效率

外部临时表

创建

1
2
3
4
5
6
7
8
create temporary table words_temp
(
id int unsigned auto_increment
primary key,
class_name varchar(255) not null comment '分类名称',
pid int null
)
comment '行业词库临时表';

修改

1
insert into words_temp values(1,'test',1);

查找

1
select * from words_temp

注意事项

  1. 账号有建立临时表的权限
  2. 不能多次关联临时表
  3. 临时表活在连接里,关闭就没了
  4. 临时表不能重命名,不能show tables查看

内部临时表

1
select pid,class_name from words order by rand() limit 3

上述语句的执行流程

  1. 创建一个内存临时表,表里会有2个字段,第一个是double类型,第二个是varchar类型,这个表没有建索引。
  2. 从数据表中,按主键顺序取出所有的值,调用rand() 生成一个 0-1的随机小数,把这个小数和按主键取出来的值放入上面的临时表。
  3. 在内存临时表中按照double类型的字段排序
  4. 初始化 sort_buffer 把临时表的数据放入 sort_buffer进行排序
  5. 返回前3条结果

磁盘临时表&&内存临时表

tmp_table_size这个配置限制内存临时表的大小,默认16MB。如果临时表大小操作这个值就会转换为磁盘临时表。临时表默认是InnoDB引擎

优化

1
explain  select pid,class_name from words order by rand() limit 3

rand

这里的 Extra Using temporary 使用临时表 Using filesort 使用排序,性能开销都很高我们要尽量避免这种情况。

一些花里胡哨的算法这边就没记录了,写一个在项目中最常用的方法。

1.count words 把总值记录内存缓存或者文件缓存。
2.代码里取随机数rand(1,count_value)
3.得到如下语句

1
explain  select pid,class_name from words limit  代码随机出来的值,1

这样做每次影响行数仅为1行,并且有随机性。弊端是取多个值的时候要请求多条语句

  • 版权声明: 本博客所有文章除特别声明外,著作权归作者所有。转载请注明出处!

请我喝杯咖啡吧~