mysql实战经典案例(高级篇)
前言
读本篇文章，需具备mysql基本优化认知(清楚执行计划各字段含义，清楚索引优化级别)
目录
索引降级 eq_ref vs ref
非强制要求group by极大丢失性能写法与eq_ref
临时表丢失索引
索引降级 eq_ref vs ref
常常情况，一条sql优化到ref级别，我们就认为是不错的sql，但实战情况下，eq_ref与ref性能还是存在较大差异，接下来将演示一段ref级别 索引如何优化到eq_ref
表结构
优化前sql
UPDATE s_opendate_charge_log a,
(SELECT serverid,PartnerID,IFNULL(SUM(Money),0) SumMoney
FROM b_charge_log WHERE serverid IN (
1057,2060,11032,11033,11034,11035,20427,20428,30308
) AND crdate='2021/1/27 0:00:00' AND Registerdate='2020/12/5 0:00:00' GROUP BY serverid,PartnerID
) b
SET a.ChargeMoney54=b.SumMoney WHERE a.serverid=b.serverid AND a.crdate='2020/12/5 0:00:00' AND a.PartnerID=b.PartnerID;
优化前执行计划
优化后sql
UPDATE s_opendate_charge_log a,
(SELECT '2020/12/5 0:00:00' crdate,serverid,PartnerID,IFNULL(SUM(Money),0) SumMoney
FROM b_charge_log WHERE serverid IN (
1057,2060,11032,11033,11034,11035,20427,20428,30308
) AND crdate='2021/1/27 0:00:00' AND Registerdate='2020/12/5 0:00:00' GROUP BY serverid,PartnerID
) b
SET a.ChargeMoney54=b.SumMoney WHERE a.serverid=b.serverid AND a.crdate=b.crdate AND a.PartnerID=b.PartnerID;
优化后执行计划
分析首先要清楚eq_ref与ref的差异与共同点,eq_ref与ref都命中了索引,不同的是eq_ref有且仅命中唯一行，而ref命中多行，具体分析即 eq_ref完全命中唯一索引与主键索引，我们常常手动创建的索引都不是唯一索引，再此案例中我们on条件差别就在于完全命中索引，与部分命中索引
非强制要求group by极大丢失性能写法与eq_ref
很多时候，mysql并没有开启严格约束group by，再这种情况下为了省事，写出来的group by可能存在性能问题
表结构
这里的player_info_log表，主键有两个(PlayerId,Crdate)
优化前sql与优化后sql对比图
临时表丢失索引
对于临时表的使用，再什么情况下使用临时表，再什么情况下不使用临时表，本案例讲为大家讲解这种情况
表结构
优化前sql
SELECT ta.partnerid , registerdate , crdate , COUNT(DISTINCT PlayerID) , SUM(ChargeMoney) FROM charge_log ta
INNER JOIN
(SELECT partnerid,id,registerdate FROM player_log WHERE registerdate>='2015/12/11' AND registerdate<='2015/12/20' GROUP BY id) tb
ON ta.partnerid = tb.partnerid AND ta.PlayerID = tb.id
WHERE crdate <= '2016/12/20'
GROUP BY ta.partnerid , registerdate , crdate
优化前执行计划
优化后sql
SELECT ta.partnerid , registerdate , crdate , PlayerID, Money FROM
player_log tb INNER JOIN
(SELECT Crdate,PlayerID,partnerid,SUM(ChargeMoney) Money FROM charge_log FORCE INDEX(IX_Crdate_PlayerID) WHERE crdate <= '2016/12/20' GROUP BY Crdate,PlayerID,partnerid) ta
ON
tb.id=ta.PlayerID
WHERE registerdate>='2015/12/11' AND registerdate<='2015/12/20'
优化后执行计划
对于临时表的使用，并不是使用了临时表，将原有数据变小后，再关联表就快，首先需要清楚的是表关联的条件也是就on尤为关键，上面这个例子，错误的将(player_log)玩家注册表，进行数据缩小做了临时表，然后再与(charge_log)充值表做关联，再这里我们可以看见的是，当玩家表采用临时表缩小之后，导致玩家表原本可以通过主键关联起来，但由于临时表，没法关联，造成索引丢失现象，使得连表数据集急剧增加
还有一些经典案例，由于本人没存就暂不做分享了，以上为最近总结的案例，最近本人项目使用了nf+autofac+efcore3.1,如果想看这个分享，就点个在看吧