|
副标题[/!--empirenews.page--]
《优化案例 | CASE WHEN进行SQL改写优化》要点：
本文介绍了优化案例 | CASE WHEN进行SQL改写优化，希望对您有用。如果有疑问，可以联系我们。
导读
今天给大家分享一个通过SQL改写而独辟蹊径的SQL优化案例
待优化场景
发现SLOW QUERY LOG中有下面这样一条记录：
...
# Query_time: 59.503827 ?Lock_time: 0.000198 ?Rows_sent: 641227 ?Rows_examined: 13442472 ?Rows_affected: 0
...
select uid,sum(power) powerup from t1 where
date>='2017-03-31' and
UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))>=1490965200 and
UNIX_TIMESTAMP(STR_TO_DATE(concat(date,'%Y-%m-%d %H'))<1492174801 ?and
aType in (1,6,9) group by uid;
实话说,看到这个SQL我也忍不住想骂人啊,究竟是哪个脑残的XX狗设计的?
竟然把日期时间中的 date 和 hour 给独立出来成两列,查询时再合并成一个新的条件,简直无力吐槽.
吐槽归吐槽,该干活还得干活,谁让咱是DBA呢,SQL优化是咱的拿手好戏不是嘛~
SQL优化之路
SQL优化思路
不厌其烦地再说一遍SQL优化思路.
想要优化一个SQL,一般来说就是先看执行计划,观察是否尽可能用到索引,
同时要关注预计扫描的行数,
以及是否产生了临时表(Using temporary) 或者?
是否需要进行排序(Using filesort),
想办法消除这些情况.
SQL性能瓶颈定位
毫无疑问,想要优化,先看表DDL以及执行计划：
CREATE TABLE `t1` (
?`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,?`date` date NOT NULL DEFAULT '0000-00-00',?`hour` char(2) NOT NULL DEFAULT '00',?`kid` int(4) NOT NULL DEFAULT '0',?`uid` int(11) NOT NULL DEFAULT '0',?`aType` tinyint(2) NOT NULL DEFAULT '0',?`src` tinyint(2) NOT NULL DEFAULT '1',?`aid` int(11) NOT NULL DEFAULT '1',?`acount` int(11) NOT NULL DEFAULT '1',?`power` decimal(20,2) DEFAULT '0.00',?PRIMARY KEY (`id`,`date`),?UNIQUE KEY `did` (`date`,`hour`,`kid`,`uid`,`aType`,`src`,`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=50486620 DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE ?COLUMNS(`date`)
(PARTITION p20170316 VALUES LESS THAN ('2017-03-17') ENGINE = InnoDB,PARTITION p20170317 VALUES LESS THAN ('2017-03-18') ENGINE = InnoDB
...
yejr@imysql.com[myDB]> EXPLAIN select uid,9) group by uidG
*************************** 1. row ***************************
? ? ? ? ? id: 1
?select_type: SIMPLE
? ? ? ?table: t1
? partitions: p20170324,p20170325,....all partition
? ? ? ? type: ALL
possible_keys: did
? ? ? ? ?key: NULL
? ? ?key_len: NULL
? ? ? ? ?ref: NULL
? ? ? ? rows: 25005577
? ? filtered: 15.00
? ? ? ?Extra: Using where; Using temporary; Using filesort
明显的,这个SQL效率非常低,全表扫描、没有索引、有临时表、需要额外排序,什么倒霉催的全赶上了.
优化思考
这个SQL是想统计符合条件的power列总和,虽然 date 列已有索引,但WHERE子句中却对 date 列加了函数,而且还是 date 和 hour 两列的组合条件,那就无法用到这个索引了.
还好,有个聪明伶俐的妹子,突发起想(事实上这位妹子本来就擅长做SQL优化的~),可以用 CASE WHEN 方法来改造下SQL,改成像下面这样的：
select uid,sum(powerup+powerup1) from
(
? select uid,? ? ? ? ?case when concat(date,hour) >='2017-03-24 13:00' then power else '0' end as powerup,hour) < '2017-03-25 13:00' then power else '0' end as powerup1
? from t1
? where date>='2017-03-24'
? and ? date <'2017-03-25'
? and ?aType in (1,9)
) a ?group by uid;
是不是很有才,直接把这个没办法用到索引的条件给用CASE WHEN来改造了.看看新的SQL执行计划：
*************************** 1. row ***************************
? ? ? ? ? id: 1
?select_type: SIMPLE
? ? ? ?table: t1
? partitions: p20170324
? ? ? ? type: range
possible_keys: did
? ? ? ? ?key: idx2_date_addRedType
? ? ?key_len: 4
? ? ? ? ?ref: NULL
? ? ? ? rows: 876375
? ? filtered: 30.00
? ? ? ?Extra: Using index condition; Using temporary; Using filesort
看看这个SQL的执行代价：
+----------------------------+---------+
| Variable_name ? ? ? ? ? ? ?| Value ? |
+----------------------------+---------+
| Handler_read_first ? ? ? ? | 1 ? ? ? |
| Handler_read_key ? ? ? ? ? | 1834590 |
| Handler_read_last ? ? ? ? ?| 0 ? ? ? |
| Handler_read_next ? ? ? ? ?| 1834589 |
| Handler_read_prev ? ? ? ? ?| 0 ? ? ? |
| Handler_read_rnd ? ? ? ? ? | 232276 ?|
| Handler_read_rnd_next ? ? ?| 232277 ?|
+----------------------------+---------+
及其SLOW QUERY LOG记录的信息：
# Query_time: 6.381254 ?Lock_time: 0.000166 ?Rows_sent: 232276 ?Rows_examined: 2299141 ?Rows_affected: 0
# Bytes_sent: 4237347 ?Tmp_tables: 1 ?Tmp_disk_tables: 0 ?Tmp_table_sizes: 4187168
# InnoDB_trx_id: 0
# QC_Hit: No ?Full_scan: No ?Full_join: No ?Tmp_table: Yes ?Tmp_table_on_disk: No
# Filesort: Yes ?Filesort_on_disk: No ?Merge_passes: 0
# ? InnoDB_IO_r_ops: 0 ?InnoDB_IO_r_bytes: 0 ?InnoDB_IO_r_wait: 0.000000
# ? InnoDB_rec_lock_wait: 0.000000 ?InnoDB_queue_wait: 0.000000
# ? InnoDB_pages_distinct: 9311
看起来还不是太理想啊,虽然不再扫描全表了,但毕竟还是?有临时表?和?额外排序,想办法消除后再对比看下.
有个变化不知道大家注意到没,新的SLOW QUERY LOG记录多了不少信息,这是因为用了Percona分支版本的插件才支持,这个功能确实不错,甚至还能记录Profiling的详细信息,强烈推荐.
（编辑：东莞站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
