老叶茶馆 关注
手机版

MySQL5.7利用虚拟列优化

  1. 云栖社区>
  2. 老叶茶馆>
  3. 博客>
  4. 正文

MySQL5.7利用虚拟列优化

云栖直播~ 2018-12-06 16:12:58 浏览122 评论0

摘要: 导 读 知数堂只分享干货,各精品课程讲授的都是职场实用技能 今天线上发生CPU使用率超过95%的报警, 登上RDS以后发现一堆的sending data状态的SQL,大致有3个问题SQL,因为这3个SQL导致了其他原本很快的SQL也被“拖慢了”,以下是其中的一个SQL,拿出来记录一下 建表DDL: 首先,看下执行计划: 从执行计划里可以看到,如下展示的部分没有用到索引,并且在与表task log关联时也没有走索引,这里有点特殊的情况就是t4跟t1关联是3个字段关联。

导 读

知数堂只分享干货,各精品课程讲授的都是职场实用技能

今天线上发生CPU使用率超过95%的报警, 登上RDS以后发现一堆的sending data状态的SQL,大致有3个问题SQL,因为这3个SQL导致了其他原本很快的SQL也被“拖慢了”,以下是其中的一个SQL,拿出来记录一下

建表DDL:

首先,看下执行计划:

从执行计划里可以看到,如下展示的部分没有用到索引,并且在与表task log关联时也没有走索引,这里有点特殊的情况就是t4跟t1关联是3个字段关联。

建立以下索引:

再来看执行计划:

从执行计划看,问题就只剩下子查询的部分,如果这个部分能用到索引,那么这个SQL基本上就能达到要求了

先看下运行时间:

总共运行时间为90ms

再来分析下子查询的部分:

这个部分是根据user_id,task_id,date分组,而date又是把created_at字段格式化后取日期的部分,这个部分是没有办法直接用到索引的

查询了下生产环境的RDS版本为5.7.18,可以用5.7的虚拟列完美解决这个问题呀!

先来看下MySQL官方文档关于虚拟列咋写的:

fd63dee25a6e19f591b01d9f3221cd32c98259f1

虚拟列分为VIRTUAL和STORED两种,两者的区别是VIRTUAL只是在读行的时候计算结果,但在物理上是不存储的,并且InnoDB引擎支持在虚拟列上建二级索引;

而STORED则是当行数据进行插入或更新时计算并存储的,是需要占用物理空间的,并且也可以添加为索引列。

但虚拟列在实际使用过程中也需要注意如下:

1、虚拟列支持使用内置函数和运算符,但不能使用  CONNECTION_ID(), CURRENT_USER(), NOW()

2、子查询、参数、变量、 存储函数和自定义函数都无法使用虚拟列可以参照其他的虚拟列,但是参照的虚拟列必须定义在前面,假如参照的列为非虚拟列,那么该列可以定义在虚拟列的前后

3、自增属性无法使用在虚拟列并且自增列不能作为基础列

4、MySQL5.7.10版本,如果表达式计算造成截断或将错误数据传入函数,CREATE TABLE语句会造成错误并且DDL操作会被拒绝

OK,大致了解了虚拟列,我们看下DDL语法

回到正题上来,首先,增加虚拟列:

对虚拟列建立索引:

再看下执行计划:

从执行计划上看,子查询的部分也已经用上了刚刚对虚拟列建立的索引,但是可以注意到有两个warnings。

可以看到的是新增的date跟原先DATE_FORMAT(created_at, '%Y-%m-%d')是重名的,这个地方也是本次优化一个需要注意的地方,虽然这里报了warning,但是我们可以巧妙地解决无需修改线上代码就能达到优化的结果

看下执行结果:

可见,整个执行效果在21ms左右,性能提升4.3倍。

但是有点小小强迫症,让开发尽快修改代码,SQL修改如下:

最后生产环境上线以后,由原先的378ms提升到36ms,提升了10.5倍,效果还是很明显的。

d1cabe303dbb72b596fd2cb8b49e8398368a9a96

原文发布时间为:2018-12-5

本文作者:徐晨亮

本文来自云栖社区合作伙伴“  老叶茶馆”,了解相关信息可以关注“iMySQL_WX”微信公众号


【云栖快讯】阿里云栖开发者沙龙(Java技术专场)火热来袭!快来报名参与吧!  详情请点击

网友评论

云栖直播~
文章409篇 | 关注824
关注
主机市场占用率第一,20年专业品质保证 查看详情
云数据库PPAS版,是阿里云与EnterpriseDB公司合作基于PostgreSQL高度兼... 查看详情
PostgreSQL被业界誉为“最先进的开源数据库”,面向企业复杂SQL处理的OLTP在线事... 查看详情
为您提供简单高效、处理能力可弹性伸缩的计算服务,帮助您快速构建更稳定、安全的应用,提升运维效... 查看详情
双12

双12