概述
下述场景,均来自实际产品线上经验,出于保密考量,所有需求场景都是仿造的,模拟遇到过的真实场景。
场景一: 统计数据(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):文章来源:https://www.toymoban.com/news/detail-424692.html
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,并不是数据库中的字段。文章来源地址https://www.toymoban.com/news/detail-424692.html
到了这里,关于真实场景sql优化持续更新(老司机必备)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!