sql 关联使用id还是code_一条sql优化之虚拟列、group by

接到一个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 的结果。