记一次MySQL取百分位数的优化
需求如下
从一个(即将变为)巨大的rank_table
表中取出百分位数,用来标记百分比排名。
rank_value
字段设置了BTree索引
排名字段 id, rank_value
内容(下表为按rank_value升序排列获取到的结果)
188, 10
6, 20
805, 30
…
671, 990
22, 1000
国际惯例,先上结论
- 获取数据总数
select count(*) from rank_table;
- 计算出百分位数序号列表
- 执行以下sql获取到预期内容
select rank_value from rank_table where (select @rank := ifNull(@rank+1, 0)) in (序号列表) order by rank_value asc;
思路1
实现过程
- 取出数据总量 count(*)
- 计算出对应的序号
- 使用下列sql语句,循环执行100次,得到预期结果
select rank_value from rank_table order by rank_value asc limlt 1 offset 序号;
问题
进行了101次数据库io
进行了100次数据库排序
服务响应时间过长
思路2
优化思路
针对思路1的问题,从另一个极端触发,直接获取全表的数据
实现过程
- 直接取出全表数据
- 在内存中进行排序,取出结果
问题
取出了过多的无效数据,浪费数据库带宽与io性能
需要额外维护自己编写的排序逻辑
思路3
针对思路2的问题,尝试将排序与获取结果的过程通过sql语句转移到数据库中
实现过程
- 取出数据总量count(*)
- 计算出对应的序号, 例如:(0, 10, 20, 30, … , 990)
- 执行下列子查询sql语句,得到预期结果
select rank_value
from (
select rank_value, @rank:=ifNull(@rank+1, 0) AS rank_no from rank_table order by rank_value asc) as a
where a.rank_no in (0, 10, 20, 30, ... , 990);
问题
虽然达成了我们的优化设想,但是通过explain查看性能发现,有derived表生成,没有充分利用索引
思路4
优化思路
针对思路3的问题,尝试将子查询去掉,直接在原表中进行排序筛选
实现过程
- 获取数据总数
- 计算出百分位数序号列表
- 执行以下sql获取到预期内容
select rank_value from rank_table where (select @rank := ifNull(@rank+1, 0)) in (序号列表) order by rank_value asc;
优化结果
explain结果显示,该查询语句使用了索引覆盖,性能已经优化到了比较高的水平,符合业务要求。
本次sql优化到此结束,各位大神如果有进一步优化的思路或方法,欢迎以各种形式讨论