真实场景sql优化持续更新（老司机必备）

概述

下述场景，均来自实际产品线上经验，出于保密考量，所有需求场景都是仿造的，模拟遇到过的真实场景。

场景一： 统计数据（Order by 不具备唯一性导致的分页数据混乱）

需求

在实际业务场景中，我们经常遇到统计分析，比如现在有一张学生表student，现统计姓名为xxx的总共有多少学生。

id	name
1	张三
2	张三
3	李四
4	武器
5	大炮
6	大炮
7	李四
8	无用
9	刘可
10	狐狸
11	无话
12	败给
13	事变
14	狐狸
15	何必
16	无话
17	无用
18	无话
19	李四

实现

常规思路一般用groub by ,然后再求和，再分页。

查第一页

SELECT
	t.name,
	COUNT(1) as num 
FROM
	test t 
WHERE
	1 = 1 
GROUP BY t.`name`
ORDER BY
	num DESC 
	LIMIT 0,
	5

查询结果是这样的：

name	num
李四	3
无话	3
张三	2
大炮	2
狐狸	2

查第二页

SELECT
	t.name,
	COUNT(1) as num 
FROM
	test t 
WHERE
	1 = 1 
GROUP BY t.`name`
ORDER BY
	num DESC 
	LIMIT 5,
	5

查询结果是这样的：

name	num
狐狸	2
武器	1
刘可	1
败给	1
事变	1

结果分析

显然第二页的'狐狸'不应该出现，他是第一页的最后一条数据。这个问题在mysql官方是给予了答案的，其实只要是order by 的排序字段在结果集中不唯一，排序字段一致的行他返回的结果都是无序的（可能无索引的话走堆排序），这一点不容易被重视，也不容易被测试所发现（单表一般需要较多重复数据和分页才容易被发现），算是一个小坑。

优化

方案一

网上一般提供的思路： 既然排序字段不是唯一的，我们一般期望唯一排序，只需要在order by 中跟上唯一标识的字段即可（或者加索引走索引排序），像下面这样：

SELECT
	t.name,
	COUNT(1) as num 
FROM
	test t 
WHERE
	1 = 1 
GROUP BY t.`name`
ORDER BY
	num DESC,t.id desc
	LIMIT 5,
	5

但是这种方式有个致命问题，ORDER BY 后面接了两个字段会让索引失效，大数据场景下是不推荐这种方式的。

方案二

使用 ROW_NUMBER() OVER ( ORDER BY t.id) AS serial_number让他按照指定方式排序，这基本也是万机油解决方案，对代码侵入程度很低。但是我们这个场景下两种方式效率一样，因为本来num字段就没有索引，但是当order by 存在一个字段可以用索引的话就不一样了。

SELECT
	t.name,
	COUNT(1) as num ,
	ROW_NUMBER() OVER ( ORDER BY t.name) AS serial_number
FROM
	test t 
WHERE
	1 = 1 
GROUP BY t.`name`
ORDER BY
	num DESC
	LIMIT 5,
	5

场景二： 大表查询优化问题（多租户情景下的连表查询规范）

需求

假设有这样一个场景，要求查某公司的商品出售情况的数据，数据库设计如下：

表名	备注
order	订单表
goods	商品表
logistics	物流表
order_goods_mapping	商品与订单关联表
order_logistics_mapping	物流与订单关联表

实现

先不考虑数据库设计是否合理，现在要分页查询商品销售情况，在不考虑数据量的情况下一般这样写sql(伪sql):

select g.*,o.*,l.* from goods g
join order_goods_mapping ogm on(ogm.goods_id= g.goods_id)
join order o on(o.order_id= ogm.order_id)
join order_logistics_mapping olg on(olg.order_id = o.order_id)
join logistics l on(l.logistics_id = olg.logistics_id)

where l.company_id = #{companyId} limit 0,10

这些xxxid字段索引都有，当数据库较小的时候看上去没有任务问题。但是假设商品有1亿种商品，这个sql可以预见性的剧卡。因为join操作匹配本来就是nnn这样的操作，由于只限制了logistics 的company_id，所以查询出来的数据量依旧是巨大的。（亲身经历的一次因为慢查询，导致上线失败的根本原因）

优化

要限制每张表的数据尽可能少，一般多租户场景下，每张表要有租户id, 这样就可以按租户维度进行数据隔离。由于很多时候我们没有遇到过大表的情况，所以基本租户隔离技术在sql联表查询没有体现出来，往往只是限制了联表的某一张表的租户id等于登录的租户id,这是不可取的（有意思的是：难怪现在流行的多租户方案要求每张表都要有租户id,除了分库分表有用，查询优化也体现出了数据隔离的优势，一个小小的字段竟然有这么大的作用）。优化后的sql如下：

select g.*,o.*,l.* from goods g
join order_goods_mapping ogm on(ogm.goods_id= g.goods_id)
join order o on(o.order_id= ogm.order_id)
join order_logistics_mapping olg on(olg.order_id = o.order_id)
join logistics l on(l.logistics_id = olg.logistics_id)

where l.company_id = #{companyId} and g.company_id = #{companyId} and ogm..company_id = #{companyId} and o.company_id = #{companyId} and olg.company_id = #{companyId}limit 0,10

场景三： 子查询导致的效率低下的问题（纵表转横表的查询，本质上是连表取交集问题的解决思路）

需求

mysql作为关系型数据库，他对行内关系的描述较弱，比如有这样2个表，主表interface记录接口表，子表itf_param记录接口参数表。
itf_param假设构造如下：

字段名	描述
id	主键
itf_id	接口id
param_name	参数名称
param_value	参数值

现在要查所有(参数名='code',参数值='12')和(参数名='route',参数值='gw')的interface记录。

实现

通常我们会用如下sql实现：

select it.* from interface it where 1=1 
and exists(
  select 1  from itf_param p where p.param_name= 'code' and p.param_value='12'
)
and exists(
  select 1  from itf_param p where p.param_name= 'route' and p.param_value='gw'
)
where 1=1 limit 0,10

在数据量少的情况下，这个sql是没有任何问题的，但是在大数据量场景下，此sql就难堪大任了，因为一般来讲子查询效率都会较低（这里即便分页了也是如此，具体原因要问DB工程师了，估摸着limit是最后被执行，所以逐条过滤大量数据导致效率较低）。

优化

通常连表查询效率高于子查询，这里采用纵表转横表的方式对sql进行优化，如下所示(伪sql)：

select it.* , 
MAX(CASE WHEN p.param_name= 'code' THEN p.param_value ELSE NULL END) AS codeParamValue,
MAX(CASE WHEN p.param_name= 'route' THEN p.param_value ELSE NULL END) AS routeParamValue,
from interface it join itf_param p on(it.itf_id = p.itf_id)
where 1=1 
group by it.*
having codeParamValue = '12' and routeParamValue='gw' 
limit 0,10 

场景四： mybatis二级缓存导致的问题

在同一个事务里面：
po = xxxMapper.select();
po.setFile(xxx);
po = xxxMapper.select();
其中po的field字段还是xxx。虽然是调用的mapper.select()从数据库查询的po对象，但是以为缓存原故，field字段还是xxx,并不是数据库中的字段。