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

这篇具有很好参考价值的文章主要介绍了真实场景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,并不是数据库中的字段。文章来源地址https://www.toymoban.com/news/detail-424692.html

到了这里,关于真实场景sql优化持续更新(老司机必备)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • unity移动端性能优化技术整理(持续更新)

    本文主要针对一些常见的性能优化的点进行总结。属于一个high level的overview。需要注意只有当这些模块确定成为制约帧时间的瓶颈时,才能够进行针对性地优化,否则可能会适得其反,事倍功半。 一般来说,移动端性能优化,如果同时支持ios和android,建议先从ios开始优化,

    2024年02月06日
    浏览(42)
  • 前端知识点、技巧、webpack、性能优化(持续更新~)

    可以把  图片转换成  base64  放在src里面   减少服务器请求  但是图片会稍微大一点点 以上的方法不需要一个一个自己转化 可以在webpack  进行 性能优化   (官网有详细描述)

    2024年03月10日
    浏览(46)
  • CTF Web SQL注入专项整理(持续更新中)

    SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严,攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句,在管理员不知情的情况下实现非法操作,以此来实现欺骗数据库服务器执行非授权的任意查询,从而进一步得到相应的数据

    2024年01月20日
    浏览(45)
  • Oracle/PL/SQL数据库基础操作(持续更新)

            PL/SQL不是一个独立的编程语言;它是Oracle编程环境中的工具。 SQL* Plus是一个互动的工具,它可以在命令提示符下键入SQL和PL/SQL语句。这些命令发送到数据库进行处理。语句处理之后将结果发回,并在屏幕上显示出来。 分类 命令 DDL create:创建;drop:删除;alter:

    2024年02月09日
    浏览(63)
  • 【SQL注入】Sqlmap使用指南(手把手保姆版)持续更新

    官网下载地址:https://github.com/sqlmapproject/sqlmap sqlmap 是一款开源的渗透测试工具,可以自动化进行SQL注入的检测、利用,并能接管数据库服务器。它具有功能强大的检测引擎,为渗透测试人员提供了许多专业的功能并且可以进行组合,其中包括数据库指纹识别、数据读取和访问

    2024年04月10日
    浏览(41)
  • 【vue2】vue2中的性能优化(持续更新中)

    ⭐ v-for 遍历避免同时使用 v-if ⭐ v-for 中的key绑定唯一的值 ⭐ v-show与v-if对性能的影响 ⭐ 妙用计算属性 ⭐ 使用防抖与节流控制发送频率 ⭐ 路由守卫处理请求避免重复发送请求 ⭐ 使用第三方UI库的引入方式 【前言】 该系列是博主在使用vue2开发项目中常用上的一些小Tips,学

    2024年01月16日
    浏览(45)
  • mmpose关键点(四):优化关键点模型(原理与代码讲解,持续更新)

    在工程中,模型的运行速度与精度是同样重要的,本文中,我会运用不同的方法去优化比较模型的性能,希望能给大家带来一些实用的trick与经验。 有关键点检测相关经验的同学应该知道,关键点主流方法分为Heatmap-based与Regression-based。 其主要区别在于监督信息的不同,Hea

    2024年02月08日
    浏览(66)
  • MySQL 数据存储和优化------MySQL索引原理和优化 ---- (架构---索引---事务---锁---集群---性能---分库分表---实战---运维)持续更新

    Mysql架构体系全系列文章主目录(进不去说明还没写完) https://blog.csdn.net/grd_java/article/details/123033016 本文只是整个系列笔记的第二章:MySQL索引原理和优化,只解释索引相关概念。 索引可以提高查询效率,影响where查询和order by排序,它可以从多方面进行分类,但是实际创建时

    2024年02月02日
    浏览(54)
  • 「ABAP」万字详解,一文带你入门SAT事务码【SQL优化必备】

    💂 作者简介: THUNDER王,一名热爱财税和SAP ABAP编程以及热爱分享的博主。目前于江西师范大学本科在读,同时任汉硕云(广东)科技有限公司ABAP开发顾问。在学习工作中,我通常使用偏后端的开发语言ABAP,SQL进行任务的完成,对SAP企业管理系统,SAP ABAP开发和数据库具有较

    2023年04月09日
    浏览(62)
  • Tonghttpserver6.0.1.0部署指引优化版+基本操作指引+部分问题收集持续更新(by lqw)

    输入lscpu,确认cpu架构: 根据cpu架构选择对应的安装包(圈起来的是x86的,另外两个是aarch64的): 2.安装jdk,检查并配置jdk环境变量(这个自行百度,一般麒麟v10和国创的系统已经自带openjdk了,可以不用安装,但是要配置jdk环境变量)。 3.关闭防火墙,或者放行8080(待会测

    2024年04月15日
    浏览(35)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包