MySQL优化之虚拟列以及group by 优化
5墨值下载
接到一个 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 也不
见了（排序在内存临时表中完成了）
of 4
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论