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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 本文是技术同仁 蔡亮 在日常工作中通过试验,总结出的一些技巧方案,供大家参考学习。在此,感谢蔡亮的供稿分享,希望大家也可以后续将学习工作中遇到的问题,解决方法分享给大家。

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

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

慢 SQL 文本如下:

image


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

SQL 执行计划如下:

image

初步索引实现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 执行情况如下:

image


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

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

image

由执行计划可知,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 改写为:

image

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

执行计划如下:

image


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

伪列实现SQL优化

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

image


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

执行计划如下:

image

分析执行计划可知,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 语句相作用,触发了数据库的死锁问题。

具体问题如下:

image

在常规情况下,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。

执行计划如下:

image

分析、验证复杂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 即可返回结果,执行计划如下:

image

由执行计划可知,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 子查询后执行计划如下:

image


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

image

由执行计划可知,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 层面,尽可能的避免改变业务方式实现。

原文发布时间为:2018-07-13
本文作者:蔡亮
本文来自云栖社区合作伙伴“数据和云”,了解相关信息可以关注“数据和云”。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4天前
|
SQL 关系型数据库 MySQL
【MySQL 数据库】7、SQL 优化
【MySQL 数据库】7、SQL 优化
24 0
|
1月前
|
SQL 存储 数据库
面试题19: 如何优化SQL查询?
面试题19: 如何优化SQL查询?
面试题19: 如何优化SQL查询?
|
6天前
|
SQL 关系型数据库 MySQL
|
15天前
|
SQL 数据库 索引
sql深度优化
sql深度优化
21 1
|
16天前
|
SQL 存储 关系型数据库
MySQL索引原理以及SQL优化
MySQL索引原理以及SQL优化
50 0
|
28天前
|
SQL 存储 关系型数据库
【MySQL】七种SQL优化方式 你知道几条
【MySQL】七种SQL优化方式 你知道几条
32 0
|
1月前
|
SQL 存储 关系型数据库
MySQL(终结篇二)- SQL 语句分析与优化
MySQL(终结篇二)- SQL 语句分析与优化
77 0
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据查询
【数据库SQL server】关系数据库标准语言SQL之数据查询
58 0
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之视图
【数据库SQL server】关系数据库标准语言SQL之视图
57 0
|
1月前
|
SQL 人工智能 算法
【数据库SQL server】传统运算符与专门运算符
【数据库SQL server】传统运算符与专门运算符
35 0