接到一个sql优化问题,开发同学给了我sql语句,需要帮忙优化一下。
带出两个sql优化的技巧。虚拟列和group by优化
看下sql语句:了解下下背景:
-- 按时间统计,最近1小时,每10分钟间距统计
- 按时间统计,最近7天,每天间距统计
-- 按时间统计,最近30天,每天间距统计
大概知道慢在哪里了。
对时间列进行函数运算,排序、分组等,对排序列运算,用不了索引。
优化:
MySQL虚拟列,将对列运算的函数单独抽取为一个列,并建立索引。
group by 优化:包括sort_buffer_size tmp_table_size等
SELECT
DATE_FORMAT(
concat( date( a.create_time ), ' ', HOUR ( a.create_time ), ':', floor( MINUTE ( a.create_time ) / 10 ) * 10 ),
'%Y-%m-%d %H:%i'
) time,
COUNT(id) timeCount
FROM
mq_receive_history a
WHERE
a.create_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
and a.code = 'heartbeat' and a.del_flag = 0
GROUP BY time
ORDER BY time;
表结构:
CREATE TABLE `mq_receive_history` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_time` timestamp(0) NULL DEFAULT NULL,
`update_time` timestamp(0) NULL DEFAULT NULL,
`del_flag` bit(1) NULL DEFAULT NULL,
`code` varchar(255) ,
`gid` varchar(255) ,
`uid` varchar(255) ,
`msg_id` varchar(255) ,
`receive_time` timestamp(0) NULL DEFAULT NULL,
`data` longtext,
`receive_message`,
`error_code` int(11) NULL DEFAULT NULL,
`error_msg` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`is_ui_run` bit(1) NULL DEFAULT NULL,
`is_system_login` bit(1) NULL DEFAULT NULL,
`code_run_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `p_code`(`code`) USING BTREE
) ENGINE = InnoDB;
查看sql查询计划
不出所料:Using where; Using temporary; Using filesort
虚拟列优化:
修改表结构,把对createtime列每10分钟运算固化为函数列,
并在上面建立索引,这下只需要对这个列分组即可,也可以用到索引。
使用这种优化也有优劣
1.当在表里读取记录的时候,virtual类型的会进行实时的计算。
2.当写入一条记录的时候,stored类型会通过计算,写入表中,
和常规的字段的一样的占用磁盘的空间。
3.这两种类型都可以有NOT NULL限制,但是能使用索引的一部分的功。
(不能使用主键索引和全文索引(fulltext index))
需要注意:虚拟列有两种类型:一种是VIRTUAL(MySQL默认) 一种是STORED
VIRTUAL:建议采用,数据不持久化到磁盘,数据实时计算
STORED:性能相比VIRTUAL稍差,数据持久化到磁盘,试用于需要持久化数据场景
添加虚拟列:
ALTER TABLE `demo`.`mq_receive_history`
ADD COLUMN `createtime_per_ten_min` varchar(20) AS
( concat( date( a.create_time ), ' ', HOUR ( a.create_time ), ':', floor( MINUTE ( a.create_time ) / 10 ) * 10 )) VIRTUAL NULL AFTER `code_run_id`;
添加索引:
ALTER TABLE `demo`.`mq_receive_history`
ADD INDEX `idx_mq_rec_createtime_per_min`(`createtime_per_ten_min`) USING BTREE;
修改后,在看sql语句查询计划:使用到了索引,查询效率也大幅度提高。using filesort也不见了(排序在内存临时表中完成了)
接下来看下group by优化点:
使用了group by 一般伴随着:
using temporary:使用了内存临时表
using filesort:使用了排序操作
group by 原理可以去查阅相关文档看下(可以看下丁奇老师的《MySQL实战45讲》)
group by 的语义逻辑,是统计不同的值出现的个数,因此需要一个临时表来统计。
优化时候也可以通过查看下面参数:
show global status like '%Created_tmp_tables%'
show global status like '%Created_tmp_disk_tables%'
分组排序数据量小的时候适当调大:tmp_table_size可以让临时表不使用磁盘,速度很快。
using filesort:
如果数据量很大的时候,临时表会不会占用很多内存空间,
到内存空间 sort_buffer不够用的时候,还是需要用到磁盘空间。
也可以使用SQL_BIG_RESULT 这个提示(hint),
就可以告诉优化器:这个语句涉及的数据量很大,请直接用磁盘临时表。
总结:
1、如果对 group by 语句的结果没有排序要求,要在语句后面加 order by null;
2、尽量让 group by 过程用上表的索引,确认方法是 explain 结果里没有
Using temporary 和 Using filesort;
3、如果 group by 需要统计的数据量不大,尽量只使用内存临时表;
也可以通过适当调大 tmp_table_size 参数,来避免用到磁盘临时表;
4、如果数据量实在太大,使用 SQL_BIG_RESULT 这个提示,
来告诉优化器直接使用排序算法得到 group by 的结果。