调优实例

针对具体的实例调优的话，通过由一下几个步骤

1. 明确背景
2. 明确瓶颈、问题。
3. 对齐需求
4. 针对问题分析
5. 结合业务提出优化手段

offset无效回表

简单总结：

sql层和engine分工明确

• 由sql层负责条件判断、聚合以及offset条件等
• 由engine层负责具体的数据读写

所以，engine并不理解offset的概念，由sql层记录，所以每次都是sql层调用engine获取完整数据，sql判断不是需要的offset，那就查找下一条。

对齐sql

首先问面试官，目前表的结构大概是怎样，索引的建设，又是怎样的，假设通过沟通，我们得到如下简化过的表t_player：

字段名	类型	描述
id	bigint(20) unsigned	主键id
score	int(11) unsigned	分数
name	varchar(128)	姓名

只在score字段上建了二级索引，大小是从小到大。这里要找第k个，其实就是偏移k-1：

select * from t_player order by score desc offset k - 1 limit 1

复杂度分析

看起来有offset，可以走索引，实际上，由于不知道子节点元素个数，并不能走索引。

所以实际上是遍历。

offset慢问题

一方面遍历，使得慢。实际上，会特别慢，主要原因是对于前k个不需要到的数据，都需要回表查找，导致了很多次的随机IO。

而这个问题本质就是sql层和engine层的分工问题。

sql层和engine分工明确

• 由sql层负责条件判断、聚合以及offset条件等
• 由engine层负责具体的数据读写

所以，engine并不理解offset的概念，由sql层记录，所以每次都是sql层调用engine获取完整数据，sql判断不是需要的offset，那就查找下一条。

也就是说，每次查询，engine都需要回表查询到数据，然后再返回sql层做判断。

优化方案

1.业务上绕过

将limit、offset，改为next，也就是将第x页，改为下一页，这样就

可以通过树分支查找。

举个例子，百度的搜索界面，就是典型的分页面。

而现在移动互联网时代，用得更多的就是上一页、下一页这样的翻页逻辑，微博、抖音都是这样的逻辑。

-- 记录score为prev_score
select score from t_player order by score desc limit 20
-- 记录score为prev_score
select score from t_player where score < prev_score order by score desc limit 20

使用这种模式，可以利用树索引直接找到目标，也绕过了无效回表问题，在Offset超过一万的情况下，性能通常都能提高两个量级以上。

当然，这种适合给分页做优化，如果回到我们题目本身来说，那查找第k大的数，就需要循环“下一页”下去，损耗反而更大。

2. sql拆分优化

select * 
from t_player id 
in (
	select id 
	from t_player 
	order by score 
	offset 10000 
	limit 1 -- 这里虽然也是遍历，但由于只查了id，省略了回表的过程。
）

3.预判边界值

这其实也是根据业务场景的做法，能通过业务预判边界，这种方式并不是通用解决方案，但因为《高性能MySQL》中提到了，也一并列出来。

分层设计

为什么MySQL不直接丢掉无用数据，还要傻乎乎地回表？

也许你曾经听过一个词，叫索引下推，在MySQL5.6之后，MySQL通过索引下推提升了性能。

这个问题也类似，答案是Offset未曾下推！我们先review下查找流程：

1.存储引擎通过二级索引查找，获取主键值；

2.进行回表操作，将完整记录返回给上层；

3.上层判断是否需要该记录，需要则返回给客户端，不需要则跳过该记录；

4.存储引擎接着查找下一条；

5.重复第二步。

从流程其实我们能看出，存储引擎层是没有Offset信息的。

MySQL不做的原因，无非两点：

1.限制场景太多，给多个引擎做有点得不偿失；

2.更核心的，分层设计理念，这件事本身是Sql层的，本就不该存储引擎做。

但如果是自研的sql，就可以更考虑实用而非通用。