实战演练:通过伪列、虚拟列实现SQL优化

本文是技术同仁 蔡亮 在日常工作中通过试验,总结出的一些技巧方案,供大家参考学习。在此,感谢蔡亮的供稿分享,希望大家也可以后续将学习工作中遇到的问题,解决方法分享给大家。

一.通过伪列、虚拟列实现SQL优化

慢 SQL 文本如下:

SQL 执行时长达 38S,获取 361 条数据结果返回。

SQL 执行计划如下:

初步索引实现SQL优化

由执行计划可知,SQL 首先从 minute_time(minute_time 数据大概估算1440条)表中获取数据,然后嵌套驱动 month_show_data(month_show_data 数据大概估算333389条),两个表都是全表扫描!可以通过添加索引将 SQL 优化。

为两个表添加如下索引:

ALTER TABLE `minute_time` ADD INDEX `idx_minute`(`minute`); ALTER TABLE `month_show_data` ADD INDEX `idx_sz_type_ct`(`server_zone`, `type`, `create_time`);

添加索引后,SQL 执行情况如下:

SQL 执行 10s 左右返回 361 条结果集。

添加索引后的执行计划如下:

由执行计划可知,SQL 通过索引 idx_minute 先从 minute_time 中过滤出 361 条数据结果集,然后与通过索引 idx_sz_type_ct 从 month_show_data 中过滤出 931 条数据结果集相关联!

虽然 SQL 已经得到优化,但 SQL 长达 10s 的执行时间,对业务来说无法接受,随着数据量的增加,SQL 执行时间也会越来越慢。

虚拟列实现SQL优化

分析 SQL 可知,SQL 的性能瓶颈在于 a.minute = DATE_FORMAT(b.create_time, '%H:%i') 两表之间的关联关系,SQL 无法通过表之间的关联关系直接驱动 month_show_data 直接返回数据。并且 MySQL 不支持函数索引。无法通过创建函数索引来优化该 SQL。

这时候 SQL 如何在不改变业务的需求下继续深入优化呢?

MySQL 5.7 增加了虚拟列的新功能,可以类似的实现 Oracle 函数索引。由此思路,month_show_data 增加虚拟列 vr_time,并添加虚拟列索引 idx_vr_time。

ALTER TABLE `month_show_data` ADD vr_time VARCHAR(10) AS (DATE_FORMAT(create_time, '%H:%i')) STORED, ADD KEY idx_vr_time (vr_time);

并将 SQL 改写为:

再次执行,0.27s 左右即可返回所需的数据。

执行计划如下:

此 SQL 的优化是否就这样结束了呢?明显不是。

伪列实现SQL优化

由数据量、表之间的关联关系及返回的结果集推断可知,只有在 minute_time 和 month_show_data 分别过滤后,再对符合条件的结果集进行关联才是最优的执行计划,而由前面的分析可知,执行计划并没有按照我们想象中方式的实现关联。伪列的引入,可以强制 SQL 改变表之间的关联顺序,获得想要的执行计划。将 SQL 改写成如下方式:

SQL 在 0.004s 之后即返回查询结果。

执行计划如下:

分析执行计划可知,SQL 先取得 DERIVED3 结果集(即 month_show_data 表符合条件的结果集),然后取得 DERIVED2 结果集(即 minute_time 表符合条件的结果集),最后以 DERIVED2 嵌套关联 DERIVED3,执行计划达到最优。

此次 SQL 的优化难点在于,MySQL 并不支持函数索引,在表设计和业务设计的时候,尽量避免对关联谓词和过滤谓词进行函数运算。MySQL 5.7 虚拟列的引入可以在不改动业务实现的情况下,实现函数索引类似的需求。基于代价的优化器存在缺陷,并不能每次都会选出最优的执行计划。能准确分析表之间的关联关系,才是 SQL 优化的制胜关键!

二. SQL优化改写之美——MySQL 一条SELECT死锁引发SQL

出问题的数据库版本为 MySQL 5.1,表引擎为 MyISAM,在业务 SELECT 查询的时候,居然与一条 UPDATE 语句相作用,触发了数据库的死锁问题。

具体问题如下:

在常规情况下,SELECT 查询语句在 MyISAM 表引擎下是不会与 UPDATE 语句产生死锁,但数据库版本过旧,数据库存在未知且难以解决的 BUG,尝试升级数据库版本和更改表结构引擎,测试数据库升级方案中,业务中许多 SQL 中出现错误的结果返回和反应数据库整体缓慢。数据库升级方案无法通过,只能通过优化SQL,减少 SELECT 持有锁的等待时间,降低死锁出现的概率,最后在逐步升级业务、数据库和更改表引擎。

SQL 执行时长13S,SQL文 本如下:

SELECT .. .. FROM weituoanjian WA LEFT JOIN phoneinfo p ON wa.daoruId = p.drId AND wa.subId = p.subId LEFT JOIN employee e ON employeeId = beifenpeizhe LEFT JOIN mst_tuianleibie ON WA.TuiAnCode = mst_tuianleibie.TuiAnCode LEFT JOIN kehutel ON WA.keHuCode = kehutel.keHuId LEFT JOIN mst_anjianstatus ON WA.AnJianStatusCode = mst_anjianstatus.AnJianStatusCode LEFT OUTER JOIN mst_anjianstatus2 ON WA.taCode = mst_anjianstatus2.TAStatusCode LEFT JOIN anjianpici ON anjianpicicode = wa.pcCode WHERE IFNULL(historyFlag, 0) <> 1 AND (REPLACE(ChiKaRenXingMing, ' ', '') LIKE '%13335192949%' OR ckrpinyin LIKE '%13335192949%' OR ckrszm LIKE '%13335192949%' OR ZhengJianHaoMa LIKE '%13335192949%' OR KaHao LIKE '%13335192949%' OR ZhangHao LIKE '%13335192949%' OR email3 LIKE '%13335192949%' OR email2 LIKE '%13335192949%' OR REPLACE(bqContent, ' ', '') LIKE '%13335192949%' OR REPLACE(BeiZhu1, ' ', '') LIKE '%13335192949%' OR REPLACE(BeiZhu2, ' ', '') LIKE '%13335192949%' OR REPLACE(DanWeiMingCheng, ' ', '') LIKE '%13335192949%' OR pNo LIKE '%13335192949%' OR pName LIKE '%13335192949%' OR (wa.daoruId, wa.subId) IN (SELECT DISTINCT drId, subId FROM bgbinfo WHERE bgInfo LIKE '%13335192949%') OR ZhengJianHaoMa IN (SELECT DISTINCT ShenFenZhengID FROM inputlog WHERE REPLACE(inputText, ' ', '') LIKE '%13335192949%')) ORDER BY ZhengJianHaoMa, daoruId, subId LIMIT 0, 40;

仔细分析 SQL 文本,SQL 中以 LIKE ‘% 13335192949%’ 模糊查询,以及 IFNULL(historyFlag, 0) <> 1 等,并且没有很好的条件过滤字段,均无法通过对where后过滤谓词添加索引来优化 SQL。 执行计划如下:

分析、验证复杂SQL的性能瓶颈

通过对执行计划分析可知,SQL 先执行了 bgInfo,inputlog 相关子查询( DEPENDENT SUBQUERY ),然后再与其他表关联。

将 SQL拆分执行,验证 SQL 性能瓶颈:

SELECT .. .. FROM weituoanjian WA LEFT JOIN phoneinfo p ON wa.daoruId = p.drId AND wa.subId = p.subId LEFT JOIN employee e ON employeeId = beifenpeizhe LEFT JOIN mst_tuianleibie ON WA.TuiAnCode = mst_tuianleibie.TuiAnCode LEFT JOIN kehutel ON WA.keHuCode = kehutel.keHuId LEFT JOIN mst_anjianstatus ON WA.AnJianStatusCode = mst_anjianstatus.AnJianStatusCode LEFT OUTER JOIN mst_anjianstatus2 ON WA.taCode = mst_anjianstatus2.TAStatusCode LEFT JOIN anjianpici ON anjianpicicode = wa.pcCode WHERE IFNULL(historyFlag, 0) <> 1 AND (REPLACE(ChiKaRenXingMing, ' ', '') LIKE '%13335192949%' OR ckrpinyin LIKE '%13335192949%' OR ckrszm LIKE '%13335192949%' OR ZhengJianHaoMa LIKE '%13335192949%' OR KaHao LIKE '%13335192949%' OR ZhangHao LIKE '%13335192949%' OR email3 LIKE '%13335192949%' OR email2 LIKE '%13335192949%' OR REPLACE(bqContent, ' ', '') LIKE '%13335192949%' OR REPLACE(BeiZhu1, ' ', '') LIKE '%13335192949%' OR REPLACE(BeiZhu2, ' ', '') LIKE '%13335192949%' OR REPLACE(DanWeiMingCheng, ' ', '') LIKE '%13335192949%' OR pNo LIKE '%13335192949%' OR pName LIKE '%13335192949%';

此部分的SQL在0.5S内即可返回结果,初步诊断SQL性能瓶颈不在于多表的LEFT JOIN 关联,而在于与bgInfo,inputlog的相关子查询部分。 将 SQL 中的 in 子查询等价改写为 INNER JOIN 关联: bgbinfo 表部分 SQL 改写如下:

SELECT .. . FROM weituoanjian WA LEFT JOIN phoneinfo p ON wa.daoruId = p.drId AND wa.subId = p.subId LEFT JOIN employee e ON employeeId = beifenpeizhe LEFT JOIN mst_tuianleibie ON WA.TuiAnCode = mst_tuianleibie.TuiAnCode LEFT JOIN kehutel ON WA.keHuCode = kehutel.keHuId LEFT JOIN mst_anjianstatus ON WA.AnJianStatusCode = mst_anjianstatus.AnJianStatusCode LEFT OUTER JOIN mst_anjianstatus2 ON WA.taCode = mst_anjianstatus2.TAStatusCode LEFT JOIN anjianpici ON anjianpicicode = wa.pcCode INNER JOIN (SELECT drId, subId FROM bgbinfo WHERE REPLACE(inputText, ' ', '') LIKE '%13335192949%' GROUP BY drId, subId) m ON m.drId = wa.daoruId AND m.subId = wa.subId WHERE (historyFlag > 1 or historyFlag < 1 or historyFlag is null)

此段 0.6s 即可返回结果,执行计划如下:

由执行计划可知,bgbinfo 先通过全索引扫描对drId, subId去重,获得结果集之后,成为驱动表,嵌套驱动WA 表。

inputlog 表部分SQL改写如下:

SELECT .. . FROM weituoanjian WA LEFT JOIN phoneinfo p ON wa.daoruId = p.drId AND wa.subId = p.subId LEFT JOIN employee e ON employeeId = beifenpeizhe LEFT JOIN mst_tuianleibie ON WA.TuiAnCode = mst_tuianleibie.TuiAnCode LEFT JOIN kehutel ON WA.keHuCode = kehutel.keHuId LEFT JOIN mst_anjianstatus ON WA.AnJianStatusCode = mst_anjianstatus.AnJianStatusCode LEFT OUTER JOIN mst_anjianstatus2 ON WA.taCode = mst_anjianstatus2.TAStatusCode LEFT JOIN anjianpici ON anjianpicicode = wa.pcCode INNER JOIN (SELECT ShenFenZhengID FROM inputlog WHERE REPLACE(inputText, ' ', '') LIKE '%13335192949%' GROUP BY ShenFenZhengID) n ON wa.ZhengJianHaoMa = n.ShenFenZhengID WHERE (historyFlag > 1 or historyFlag < 1 or historyFlag is null)

改写 in 子查询后执行计划如下:

对 WA 表 ZhengJianHaoMa 字段加索引,6s 即可返回结果集,添加索引后执行计划如下:

由执行计划可知,inputlog 先通过全索引扫描对 ShenFenZhengID 字段去重,取得结果集之后,嵌套驱动 WA表 。

整个SQL改写和索引优化已基本结束,SQL执行时间由原来的 13s 提升到 7s,SQL索引、等价改写效果不太明显。但最终确认了SQL的性能瓶颈源于对 inputlog(表数据量150W)整张表按 ShenFenZhengID 的去重,无法在进一步通过SQL等价改写层面优化SQL的性能。

优化业务实现

既然无法进一步优化SQL,不得已需通过改变业务的实现方式来优化,由于 inputlog 表字段ShenFenZhengID 和 inputText 是不会更新和删除数据,于是通过定时任务来定时统计 inputlog 表按 ShenFenZhengID 的当前时间零点之前的数据去重插入到新表 inputlog_day 中,再与当天表新增的的数据做合并。定时任务可以通过程序实现,也可以通过存储过程实现,最主要的是消除每次SQL调用 inputlog 需要index 全索引扫描的高额代价。

定时任务SQL代码如下:

INERT INTO inputlog_day SELECT ShenFenZhengID, '13335192949' FROM inputlog WHERE REPLACE(inputText, ' ', '') LIKE '%13335192949%' AND InputDate < DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d 00:00:00') GROUP BY ShenFenZhengID;

业务和SQL实现如下,SQL在0.2s左右即可获得查询结果。

SELECT .. . FROM weituoanjian WA LEFT JOIN phoneinfo p ON wa.daoruId = p.drId AND wa.subId = p.subId LEFT JOIN employee e ON employeeId = beifenpeizhe LEFT JOIN mst_tuianleibie ON WA.TuiAnCode = mst_tuianleibie.TuiAnCode LEFT JOIN kehutel ON WA.keHuCode = kehutel.keHuId LEFT JOIN mst_anjianstatus ON WA.AnJianStatusCode = mst_anjianstatus.AnJianStatusCode LEFT OUTER JOIN mst_anjianstatus2 ON WA.taCode = mst_anjianstatus2.TAStatusCode LEFT JOIN anjianpici ON anjianpicicode = wa.pcCode INNER JOIN (SELECT ShenFenZhengID from inputlog_day WHERE phoneNum = '13335192949' UNION SELECT ShenFenZhengID FROM inputlog WHERE REPLACE(inputText, ' ', '') LIKE '%13335192949%' AND InputDate >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d 00:00:00') GROUP BY ShenFenZhengID) n ON wa.ZhengJianHaoMa = n.ShenFenZhengID WHERE (historyFlag > 1 or historyFlag < 1 or historyFlag is null)

SQL的优化为1.1s,SQL最终改写后的代码实现如下:

SELECT * FROM (SELECT .. . FROM weituoanjian WA LEFT JOIN phoneinfo p ON wa.daoruId = p.drId AND wa.subId = p.subId LEFT JOIN employee e ON employeeId = beifenpeizhe LEFT JOIN mst_tuianleibie ON WA.TuiAnCode = mst_tuianleibie.TuiAnCode LEFT JOIN kehutel ON WA.keHuCode = kehutel.keHuId LEFT JOIN mst_anjianstatus ON WA.AnJianStatusCode = mst_anjianstatus.AnJianStatusCode LEFT OUTER JOIN mst_anjianstatus2 ON WA.taCode = mst_anjianstatus2.TAStatusCode LEFT JOIN anjianpici ON anjianpicicode = wa.pcCode WHERE (historyFlag > 1 or historyFlag < 1 or historyFlag is null) AND (REPLACE(ChiKaRenXingMing, ' ', '') LIKE '%13335192949%' OR ckrpinyin LIKE '%13335192949%' OR ckrszm LIKE '%13335192949%' OR ZhengJianHaoMa LIKE '%13335192949%' OR KaHao LIKE '%13335192949%' OR ZhangHao LIKE '%13335192949%' OR email3 LIKE '%13335192949%' OR email2 LIKE '%13335192949%' OR REPLACE(bqContent, ' ', '') LIKE '%13335192949%' OR REPLACE(BeiZhu1, ' ', '') LIKE '%13335192949%' OR REPLACE(BeiZhu2, ' ', '') LIKE '%13335192949%' OR REPLACE(DanWeiMingCheng, ' ', '') LIKE '%13335192949%' OR pNo LIKE '%13335192949%' OR pName LIKE '%13335192949%') UNION SELECT .. . FROM weituoanjian WA LEFT JOIN phoneinfo p ON wa.daoruId = p.drId AND wa.subId = p.subId LEFT JOIN employee e ON employeeId = beifenpeizhe LEFT JOIN mst_tuianleibie ON WA.TuiAnCode = mst_tuianleibie.TuiAnCode LEFT JOIN kehutel ON WA.keHuCode = kehutel.keHuId LEFT JOIN mst_anjianstatus ON WA.AnJianStatusCode = mst_anjianstatus.AnJianStatusCode LEFT OUTER JOIN mst_anjianstatus2 ON WA.taCode = mst_anjianstatus2.TAStatusCode LEFT JOIN anjianpici ON anjianpicicode = wa.pcCode INNER JOIN (SELECT drId, subId FROM bgbinfo WHERE REPLACE(inputText, ' ', '') LIKE '%13335192949%' GROUP BY drId, subId) m ON m.drId = wa.daoruId AND m.subId = wa.subId WHERE (historyFlag > 1 or historyFlag < 1 or historyFlag is null) UNION SELECT .. . FROM weituoanjian WA LEFT JOIN phoneinfo p ON wa.daoruId = p.drId AND wa.subId = p.subId LEFT JOIN employee e ON employeeId = beifenpeizhe LEFT JOIN mst_tuianleibie ON WA.TuiAnCode = mst_tuianleibie.TuiAnCode LEFT JOIN kehutel ON WA.keHuCode = kehutel.keHuId LEFT JOIN mst_anjianstatus ON WA.AnJianStatusCode = mst_anjianstatus.AnJianStatusCode LEFT OUTER JOIN mst_anjianstatus2 ON WA.taCode = mst_anjianstatus2.TAStatusCode LEFT JOIN anjianpici ON anjianpicicode = wa.pcCode INNER JOIN (SELECT ShenFenZhengID from inputlog_day WHERE phoneNum = '13335192949' UNION SELECT ShenFenZhengID FROM inputlog WHERE REPLACE(inputText, ' ', '') LIKE '%13335192949%' AND InputDate >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d 00:00:00') GROUP BY ShenFenZhengID) n ON wa.ZhengJianHaoMa = n.ShenFenZhengID WHERE (historyFlag > 1 or historyFlag < 1 or historyFlag is null)) t ORDER BY t.ZhengJianHaoMa, t.daoruId, t.subId LIMIT 0, 40;

总结此 SQL 的优化分析过程,SQL 的优化难点在于 LIKE ‘% 13335192949%’ 模糊查询和 IN 子查询及 OR 条件,无法添加有效的索引实现 SQL 优化,初步分析性能瓶颈由 in 相关子查询导致的,等价改写SQL改变表的驱动方式,也仅仅只将 SQL 的执行时间由 13s 优化为7s,SQL 真正的性能瓶颈在于对 150W 数据的 inputlog 表按列 ShenFenZhengID 去重。

已无法在索引、SQL 层面进一步对 SQL 优化,只能通过进一步优化业务的实现方式,通过以定时任务的方式,来优化 SQL。当然如果深入了解业务,可能会使 SQL 有更高的性能,就不在本次讨论里面,本次仅从 SQL 层面,尽可能的避免改变业务方式实现。

作者:蔡亮

转载:意向文章下方留言。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2018-07-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏idba

修改字符集的注意那些事儿

最近有开发同学遇到emoji显示问题,表结构是utf8mb4字符集,但是不支持emoji表情字符。我们在解决字符集问题的时候也重新认识了修改字符集操作的...

812
来自专栏Java Web

模仿天猫实战【SSM版】——项目起步

3654
来自专栏程序员宝库

电商系统设计之商品 (上)

电商大伙每天都在用,类似某猫,某狗等。电商系统设计看似复杂又很简单,看似简单又很复杂。本章适合初级工程师及中级工程师细看,大佬请随意。

2854
来自专栏我叫刘半仙

原 荐 MySQL的索引是什么?怎么优化?

      索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~8...

4146
来自专栏沃趣科技

SQL优化案例-正确的使用索引(二)

下面sql 30秒执行出结果,查看sql谓词中有like,我们知道谓词中有这样的语句是不走索引的(为了保护客户的隐私,表名和部分列已经重命名)。

1446
来自专栏Java后端生活

MySQL(八)DQL之连接查询

1798
来自专栏程序员宝库

电商系统设计之订单

用户交易将经历一段艰辛的历程,一般用户感觉不到,实际程序是经历了一段生死离别。具体付款流程如下:

4473
来自专栏杨建荣的学习笔记

生产环境大型sql语句调优实战第一篇(二) (r2笔记32天)

继续昨天的部分,上一篇的链接为: http://blog.itpub.net/23718752/viewspace-1217012/ 对这条大sql的性能瓶颈进...

2726
来自专栏数据和云

MySQL - 8种常见的SQL错误用法

前言:MySQL在2016年仍然保持强劲的数据库流行度增长趋势。越来越多的客户将自己的应用建立在MySQL数据库之上,甚至是从Oracle迁移到MySQL上来。...

4334
来自专栏黑泽君的专栏

day05_MySQL学习笔记_02

    注:每个表中至少要有一个主键。 主键用于唯一地标识表中的每一条记录,可以定义一列或多列为主键。 

952

扫码关注云+社区

领取腾讯云代金券