江苏龙网

  1. 首页 > 人文 > >

结合实际项目，谈谈如何进行SQL查询优化

问题背景
前段时间 ， 被紧急调到一个新项目 ， 支撑新项目的开发 。 跌跌撞撞之下 ， 项目也正常上线了 ， 期间收获颇多 ， 无论是业务上的 ， 还是业务之外的 。 业务上的就不多说了 ， 不具通用性 ， 意义不大 ， 有一点业务之外的东东给我的感触比较深 ， 特记录下来 ， 与大家分享下：查询优化 。
查询优化
完整示例工程：data-init ， 包括数据库表的ddl和dml ， 以及数据批量的生成
相关表
涉及的表不多 ， 一共三张：额度表、记录表、存款表
额度表t_custmor_credit
CREATETABLEt_customer_credit(idINT(11)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'自增主键',login_nameVARCHAR(50)NOTNULLCOMMENT'名称',credit_typeTINYINT(1)NOTNULLCOMMENT'额度类型 ， 1:自由资金,2:冻结资金,3:优惠',amountDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'额度值',create_byVARCHAR(50)NOTNULLCOMMENT'创建者',create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',update_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'创建时间',update_byVARCHAR(50)NOTNULLCOMMENT'修改者',PRIMARYKEY(id));记录每个顾客的当前额度 ， 额度一共分三种：自由资金、冻结资金和优惠 ， 也就是说每个顾客会有3条记录来表示他的各个额度 。 表中数据如下
结合实际项目，谈谈如何进行SQL查询优化
文章图片
额度记录t_custmor_credit_record
CREATETABLEt_customer_credit_record(idINT(11)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'自增主键',login_nameVARCHAR(50)NOTNULLCOMMENT'名称',credit_typeTINYINT(1)NOTNULLCOMMENT'额度类型 ， 参考t_custmor_credit的credit_type',bill_noVARCHAR(50)NOTNULLCOMMENT'订单号',amount_beforeDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'前额度值',amount_changeDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'额度变化值',amount_afterDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'后额度值',create_byVARCHAR(50)NOTNULLCOMMENT'创建者',create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',remarkVARCHAR(500)NOTNULLDEFAULT''COMMENT'备注',PRIMARYKEY(id));记录顾客额度的每一次变化 ， 只要有额度变化（不管是哪个额度进行了变化） ， 都会新增3条记录 ， 每个类型的额度都会新增一条记录 。 另外 ， 该表只会有数据的插入 ， 不会有数据的删、改 。 表中数据如下
结合实际项目，谈谈如何进行SQL查询优化
文章图片
存款表t_custmor_deposit
CREATETABLEt_customer_deposit(idINT(11)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'自增主键',login_nameVARCHAR(50)NOTNULLCOMMENT'名称',bill_noVARCHAR(50)NOTNULLCOMMENT'订单号',amountDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'存款金额',deposit_stateTINYINT(1)NOTNULLCOMMENT'存款状态:1成功,2失败,3未知',channalTINYINT(2)NOTNULLCOMMENT'存款渠道:1:银联,2支付宝,3微信',create_byVARCHAR(50)NOTNULLCOMMENT'创建者',create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',remarkVARCHAR(500)NOTNULLDEFAULT''COMMENT'备注',PRIMARYKEY(id));记录顾客的每一次存款 ， 该表只会有数据的插入 ， 不会有数据的删、改 。 表中数据如下
结合实际项目，谈谈如何进行SQL查询优化
文章图片
产品需求
分页展示如下列表（具体的过滤条件就不列了 ， 我们就当是初始状态 ， 还未输入过滤条件）
结合实际项目，谈谈如何进行SQL查询优化
文章图片
实现比较简单 ， t_custmor_credit_record左关联t_custmor_deposit就好 ， 但是我们的额度记录表与需求列表有些许的出入 ， 需要做一下简单的行转列 。
需求实现
我们先来看看最初的SQL查询 ， 这可能是很多人最容易想到的
SELECTMIN(tcd.channal)channal,MAX(tccr.id)mId,tccr.login_name,tccr.bill_no,tccr.create_time,IF(credit_type=1,amount_before,0)ASfreeBefore,IF(credit_type=1,amount_change,0)ASfreeChange,IF(credit_type=1,amount_after,0)ASfreeAfter,IF(credit_type=2,amount_before,0)ASfreezeBefore,IF(credit_type=2,amount_change,0)ASfreezeChange,IF(credit_type=2,amount_after,0)ASfreezeAfter,IF(credit_type=3,amount_before,0)ASpromotionBefore,IF(credit_type=3,amount_change,0)ASpromotionChange,IF(credit_type=3,amount_after,0)ASpromotionAfterFROMt_customer_credit_recordtccrLEFTJOINt_customer_deposittcdONtccr.bill_no=tcd.bill_noGROUPBYtccr.bill_no,tccr.login_name,tccr.create_timeORDERBYmIddescLIMIT0,10;数据量少的时候 ， 也许能在我们接受的时间内查出我们需要的结果 ， 一旦数据量多了 ， 这个SQL就跑不动了；我们先看下60w数据的情况下 ， 我们只进行t_custmor_credit_record单表查询
SELECTMAX(id)mId,login_name,bill_no,create_time,IF(credit_type=1,amount_before,0)ASfreeBefore,IF(credit_type=1,amount_change,0)ASfreeChange,IF(credit_type=1,amount_after,0)ASfreeAfter,IF(credit_type=2,amount_before,0)ASfreezeBefore,IF(credit_type=2,amount_change,0)ASfreezeChange,IF(credit_type=2,amount_after,0)ASfreezeAfter,IF(credit_type=3,amount_before,0)ASpromotionBefore,IF(credit_type=3,amount_change,0)ASpromotionChange,IF(credit_type=3,amount_after,0)ASpromotionAfterFROMt_customer_credit_recordGROUPBYbill_no,login_name,create_timeORDERBYmIddescLIMIT0,10;效果如下
结合实际项目，谈谈如何进行SQL查询优化
文章图片
花了近8秒 ， 这还只是单表 ， 如果执行上面的联表SQL ， 那时间又得增加不少（我试验的结果是直接卡住了 ， 看不到查询结果）；
优化处理
加索引
查询慢的时候 ， 我们最容易想到的优化方式往往就是加索引；上述SQL执行的时候 ， t_custmor_credit_record和t_custmor_deposit都没有建索引（主键索引除外） ， 那么我们就加索引呗 。 我的项目中加的是唯一索引 ， 做了唯一约束 ， 那我这里也加唯一索引
ALTERTABLEt_customer_credit_recordADDUNIQUEuk_unique(bill_no,login_name,create_time,credit_type);ALTERTABLEt_customer_depositADDUNIQUEuk_billno(bill_no);此时我们看下SQL执行效果
结合实际项目，谈谈如何进行SQL查询优化
文章图片
我们发现 ， t_custmor_credit_record单表查询的效率几乎没变 ， 将近8秒 ， 但t_custmor_credit_record与t_custmor_deposit联表的查询却在11秒内有结果了 。 加了索引为什么还这么慢了？难道没走索引？
结合实际项目，谈谈如何进行SQL查询优化
文章图片
我们是不是发现了什么？IF函数对联表查询是否走索引有影响 ， 也对单表的查询速度有影响 。 上图中的t_custmor_credit_record单表查询 ， 有IF函数 ， 查询时间近8秒 ， 没有IF函数 ， 查询时间2秒左右；t_custmor_credit_record与t_custmor_deposit联表查 ， 有IF函数 ， t_custmor_credit_record走的是全表查 ， 查询时间近11秒 ， 没有IF函数 ， t_custmor_credit_record走的是索引 ， 查询时间3秒不到 。 那么我们有没有什么办法拿掉这个IF函数呢？
使用CASE…WHEN….THEN代替IF
SELECTMAX(id)mId,login_name,bill_no,create_time,CASEcredit_typeWHEN1THENamount_beforeELSE0ENDASfreeBefore,CASEcredit_typeWHEN1THENamount_changeELSE0ENDASfreeChange,CASEcredit_typeWHEN1THENamount_afterELSE0ENDASfreeAfter,CASEcredit_typeWHEN2THENamount_beforeELSE0ENDASfreeChange,CASEcredit_typeWHEN2THENamount_changeELSE0ENDASfreeChange,CASEcredit_typeWHEN2THENamount_afterELSE0ENDASfreeChange,CASEcredit_typeWHEN3THENamount_beforeELSE0ENDASpromotionBefore,CASEcredit_typeWHEN3THENamount_changeELSE0ENDASpromotionChange,CASEcredit_typeWHEN3THENamount_afterELSE0ENDASpromotionAfterFROMt_customer_credit_recordGROUPBYbill_no,login_name,create_timeORDERBYmIddescLIMIT0,10;SELECTMIN(tcd.channal)channal,MAX(tccr.id)mId,tccr.login_name,tccr.bill_no,tccr.create_time,CASEcredit_typeWHEN1THENamount_beforeELSE0ENDASfreeBefore,CASEcredit_typeWHEN1THENamount_changeELSE0ENDASfreeChange,CASEcredit_typeWHEN1THENamount_afterELSE0ENDASfreeAfter,CASEcredit_typeWHEN2THENamount_beforeELSE0ENDASfreeChange,CASEcredit_typeWHEN2THENamount_changeELSE0ENDASfreeChange,CASEcredit_typeWHEN2THENamount_afterELSE0ENDASfreeChange,CASEcredit_typeWHEN3THENamount_beforeELSE0ENDASpromotionBefore,CASEcredit_typeWHEN3THENamount_changeELSE0ENDASpromotionChange,CASEcredit_typeWHEN3THENamount_afterELSE0ENDASpromotionAfterFROMt_customer_credit_recordtccrLEFTJOINt_customer_deposittcdONtccr.bill_no=tcd.bill_noGROUPBYtccr.bill_no,tccr.login_name,tccr.create_timeORDERBYmIddescLIMIT0,10;作者：youzhibing
cnblogs.com/youzhibing/p/11105897.html作者：youzhibing
cnblogs.com/youzhibing/p/11105897.html
结合实际项目，谈谈如何进行SQL查询优化
文章图片
我们可以看到 ， 执行时间与IF所差无几 ， 执行计划也是与IF的一致 ， 这也就反映出不是IF的问题 ， 应该是GROUPBY的问题 。 我们用GROUPBY结合IF（或CASE…WHEN….THEN） ， 就是为了将3条额度记录合并成一条、行转列之后输出我们想要的结果 ， 那有没有不用GROUPBY、又能实现我们需求的方式了？
自联代替GROUPBY
我们再仔细琢磨下这个需求 ， 咋一看 ， 确实需要行转列 ， 那么就需要用到GROUPBY ， 那么效率也就低了 ， 这似乎是无解了？
真的非要行转列吗 ， 假设我们将额度记录拆分成3张表：一张表只存自由资金的额度变化、一张表只存冻结资金的额度变化、一张表只存优惠的额度变化 ， 这样是不是只需要联表查而不要用GROUPBY来进行行转列了？有小伙伴有可能会问：t_custmor_credit_record表已经定了 ， 数据都跑了不少了 ， 再将其进行拆分 ， 既要改表（同时还要迁移数据） ， 还要改代码 ， 工程量会很大！
我们换个角度来看t_custmor_credit_record ， 目前它是3中额度记录的一个总和表 ， 我们能不能从它的身上做文章 ， 变化出我们想要的那3张表 ， 然后进行联表查询呢？肯定可以的 ， 类似如下
--自由资金额度记录表SELECT*FROMt_customer_credit_recordWHEREcredit_type=1;--冻结资金额度记录表SELECT*FROMt_customer_credit_recordWHEREcredit_type=2;--优惠额度记录表SELECT*FROMt_customer_credit_recordWHEREcredit_type=3;接下来的SQL怎么写 ， 我想大家都知道了吧 ， 自联就行了 ， 写法有很多种 ， 常见的写法有如下4种：
--不用groupby,做法1,个人比较推荐 ， 但此种方式不支持存款表的过滤条件SELECTd.channal,a.amount_beforeASfreeBefore,a.amount_changeASfreeChange,a.amount_afterfreeAfter,b.amount_beforeASfreezeBefore,b.amount_changeASfreezeChange,b.amount_afterfreezeAfter,c.amount_beforeASpromotionBefore,c.amount_changeASpromotionChange,c.amount_afterpromotionAfterFROM(SELECT*FROMt_customer_credit_recordWHEREcredit_type=1ORDERBYidDESCLIMIT0,10)aLEFTJOINt_customer_credit_recordbONa.bill_no=b.bill_noANDb.credit_type=2LEFTJOINt_customer_credit_recordcONa.bill_no=c.bill_noANDc.credit_type=3LEFTJOINt_customer_depositdONa.bill_no=d.bill_no;--不用groupby,做法2,此种方式支持存款表的过滤条件SELECTa.channal,a.amount_beforeASfreeBefore,a.amount_changeASfreeChange,a.amount_afterfreeAfter,b.amount_beforeASfreezeBefore,b.amount_changeASfreezeChange,b.amount_afterfreezeAfter,c.amount_beforeASpromotionBefore,c.amount_changeASpromotionChange,c.amount_afterpromotionAfterFROM(SELECTr.*,d.channalFROMt_customer_credit_recordrLEFTJOINt_customer_depositdONr.bill_no=d.bill_noWHEREr.credit_type=1ORDERBYr.idDESCLIMIT0,10)aLEFTJOINt_customer_credit_recordbONa.bill_no=b.bill_noANDb.credit_type=2LEFTJOINt_customer_credit_recordcONa.bill_no=c.bill_noANDc.credit_type=3;--不用groupby,做法3,这是最容易想到的方法SELECTd.channal,a.amount_beforeASfreeBefore,a.amount_changeASfreeChange,a.amount_afterfreeAfter,b.amount_beforeASfreezeBefore,b.amount_changeASfreezeChange,b.amount_afterfreezeAfter,c.amount_beforeASpromotionBefore,c.amount_changeASpromotionChange,c.amount_afterpromotionAfterFROMt_customer_credit_recordaLEFTJOINt_customer_credit_recordbONa.bill_no=b.bill_noLEFTJOINt_customer_credit_recordcONa.bill_no=c.bill_noLEFTJOINt_customer_depositdONa.bill_no=d.bill_noWHEREa.credit_type=1ANDb.credit_type=2ANDc.credit_type=3ORDERBYa.idDESCLIMIT0,10;--不用groupby,做法4SELECTd.channal,a.amount_beforeASfreeBefore,a.amount_changeASfreeChange,a.amount_afterfreeAfter,b.amount_beforeASfreezeBefore,b.amount_changeASfreezeChange,b.amount_afterfreezeAfter,c.amount_beforeASpromotionBefore,c.amount_changeASpromotionChange,c.amount_afterpromotionAfterFROMt_customer_credit_recordaLEFTJOINt_customer_credit_recordbONa.bill_no=b.bill_noANDb.credit_type=2LEFTJOINt_customer_credit_recordcONa.bill_no=c.bill_noANDc.credit_type=3LEFTJOINt_customer_depositdONa.bill_no=d.bill_noWHEREa.credit_type=1ORDERBYa.idDESCLIMIT0,10;执行结果如下
结合实际项目，谈谈如何进行SQL查询优化
文章图片
就目前的数据量而言 ， 4种写法的效率一样 ， 但是数据量再往上走 ， 它们之前还是有性能差别的 ， 大家可以仔细看看这4个SQL的执行计划 ， 它们之间还是有区别的 。 最终我的项目中采用的是第一种写法 。
表重新设计
我们回过头去看看t_customer_credit和t_custmor_credit_record ， 是否真的有必要用3条记录来存放顾客的3种额度 ， 一条记录将用户的3种额度都记录下来不是更好吗？如下所示
--自认为更好的表设计DROPTABLEIFEXISTSt_customer_credit_plus;CREATETABLEt_customer_credit_plus(idINT(11)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'自增主键',login_nameVARCHAR(50)NOTNULLCOMMENT'登录名',free_amountDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'自由资金额度',freeze_amountDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'冻结资金额度',promotion_amountDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'优惠资金额度',create_byVARCHAR(50)NOTNULLCOMMENT'创建者',create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',update_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'创建时间',update_byVARCHAR(50)NOTNULLCOMMENT'修改者',PRIMARYKEY(id),UNIQUEKEY`uk_login_name`(`login_name`));DROPTABLEIFEXISTSt_customer_credit_record_plus;CREATETABLEt_customer_credit_record_plus(idINT(11)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'自增主键',login_nameVARCHAR(50)NOTNULLCOMMENT'登录名',bill_noVARCHAR(50)NOTNULLCOMMENT'订单号',free_amount_beforeDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'自由资金前额度值',free_amount_changeDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'自由资金前额度变化值',free_amount_afterDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'自由资金前后额度值',freeze_amount_beforeDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'冻结资金前额度值',freeze_amount_changeDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'冻结资金额度变化值',freeze_amount_afterDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'冻结资金后额度值',promotion_amount_beforeDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'优惠前额度值',promotion_amount_changeDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'优惠额度变化值',promotion_amount_afterDECIMAL(22,6)NOTNULLDEFAULT'0.00000'COMMENT'优惠后额度值',create_byVARCHAR(50)NOTNULLCOMMENT'创建者',create_timeDATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',remarkVARCHAR(500)NOTNULLDEFAULT''COMMENT'备注',PRIMARYKEY(id),UNIQUEKEY`uk_unique`(`bill_no`,`login_name`,`create_time`));我想很多人都会有相同的感觉吧 ， 但是从拓展性出发 ， 分3条记录的做法更好 ， 为什么呢？如果后续新增1种或多种另外的额度类型 ， 上述的1条记录的表设计就需要新增字段来适配了 ， 但是3条记录的做法 ， 只需要拓展credit_type的值就好了 ， 表无需改动 。 各有利弊 ， 如何选择 ， 需要团队协商之后做出最好的选择 。
最终项目中采用的还是3条记录存放3个额度的方式 ， 没有采用我说的；原因是：大家都认为效率影响不大 ， 也容易理解 ， 关键是拓展性很好 ， 后续很方便就能加入新的额度类型 。
业务上的协调
最后我们再回到需求上来 ， 这个存款渠道真的有必要显示在额度记录吗？
1、对公司来说 ， 存款记录越多 ， 那肯定是越好 ， 但我们从实际出发 ， 存款记录在额度记录中占的比例大吗 ， 这个相信大家也都能想象得到 ， 比例非常低 ， 可能100条记录中会有1条；
2、本来就有单独的存款页面展示顾客的存款 ， 去专门的存款记录页面看岂不是更直观？
最后和产品讨论 ， 还真把这一列给拿掉了 ， 那么我们也就不需要关联存款表来查了 ， SQL更简单 ， 效率也更高了！
总结
1、SQL行转列 ， 往往是GROUPBY配合聚合函数（SUM、MAX、MIN等）来实现 ， 当然也包括IF和CASE…WHEN….THEN；
2、索引是提高查询效率的最有效的、也是最常用的方式 ， 我们对查询的优化都要往索引上靠 ， EXPLAIN可以查看SQL的执行计划 ， 我们可以从中获取SQL优化的提示；
3、一定要结合业务来写出高效的SQL
可能很多小伙伴会有这样的不满：上述的3个额度的例子有点特殊 ， 不具备通用性 ， 上述高效的SQL也只是在你（楼主）的项目中有效 。 你说的对 ， 但是我们要知道 ， 技术本身就是用来服务业务的 ， 脱离了业务 ， 技术有什么实际意义？但是我们回过头去细看 ， 我举的例子真的就特殊到独一无二？我想还算比较通用吧 ， 还是能套用很多场景的 。
4、要敢于质疑需求
虽然大多数时候产品都考虑比较周到 ， 但也不能完全保证他没有不犯迷糊的时候吧 。 有疑惑就向产品问清楚 ， 我们实现的也更快、更准确 。 一定不要对疑问藏着掖着 ， 以我亲身经历来讲 ， 很多时候开发认为的都是对的 ， 如果藏着掖着 ， 那你就准备返工改成你之前认为的那样吧！
作者：youzhibing
【结合实际项目，谈谈如何进行SQL查询优化】cnblogs.com/youzhibing/p/11105897.html


    推荐阅读


    上一篇：外媒爆出了iPhone 12的最新信息，iPhone8大米价无情让路

    下一篇：美国找我们弱点，从禁芯片再到禁工业软件，实施精准打击？