优化案例CASEWHEN进⾏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,' ',hour),'%Y-%m-%d %H'))<1492174801 and aType in (1,6,9) group by uid;
实话说，看到这个SQL我也忍不住想骂⼈啊，究竟是哪个脑残的XX狗设计的？
竟然把⽇期时间中的 date 和 hour 给独⽴出来成两列，查询时再合并成⼀个新的条件，简直⽆⼒吐槽。
sql优化的几种方式吐槽归吐槽，该⼲活还得⼲活，谁让咱是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` t
inyint(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 p2******* VALUES LESS THAN ('2017-03-17') ENGINE = InnoDB, PARTITION p2******* VALUES LESS THAN ('2017-03-18') ENGINE = jr@imysql[myDB]> EXPLAIN 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,' ',hour),'%Y-%m-%d %H'))<1492174801 and aType in (1,6,9) group by uidG*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: p2*******,p2*******,....all partition type: ALLpossible_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 列加了函数，⽽且还
这个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, case when concat(date,' ',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,6,9)) a group by uid;
是不是很有才，直接把这个没办法⽤到索引的条件给⽤CASE WHEN来改造了。看看新的SQL执⾏计划：
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions:
p2******* type: rangepossible_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的详细信息，强烈推荐。
我们新建个 uid 列上的索引，看看能除临时表及排序后的代价如何，看看这个的开销会不会更低。
yejr@imysql[myDB]> ALTER TABLE t1 ADD INDEX idx_uid(uid);yejr@imysql[myDB]> EXPLAIN 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, case when concat(date,' ',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,6,9)) a group by uidG*************************** 1. row *************************** id: 1 select_type: SIMPLE table: if_date_hour_army_count partitions: p2*******,p2*******... type: indexpossible_keys: did,idx_uid key: idx_uid key_len: 4 ref: NULL rows: 12701520 filtered: 15.00 Extra: Using where
看看添加索引后SQL的执⾏代价：
+----------------------------+---------+| Variable_name | Value |+----------------------------+---------+| Handler_read_first | 1 || Handler_read_key | 1 || Handler_read_last | 0 || Handler_read_next | 1834589 || Handler_read_prev | 0 ||
Handler_read_rnd | 0 || Handler_read_rnd_next | 0 |+----------------------------+---------+
及其SLOW QUERY LOG记录的信息：
# Query_time: 5.772286 Lock_time: 0.000330 Rows_sent: 232276 Rows_examined: 1834589 Rows_affected: 0# Bytes_sent: 4215071 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0# InnoDB_trx_id: 0# QC_Hit: No
Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No# Filesort: No 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: 11470
0.000000 InnoDB_queue_wait: 0.000000# InnoDB_pages_distinct: 11470
我们注意到，虽然加了 uid 列索引后的SQL扫描的data page更多了，但执⾏效率其实是更⾼的，因为消除了临时表和额外排序，这从 Handlerread% 的结果中也能看出来，很显然它的顺序I/O更多，随机I/O更少，所以虽然需要扫描的data page 更多，实际上效率却是更快的。
后记
再想想这个SQL还有优化空间吗，显然是有的，那就是把数据表重新设计，将 date 和 hour 列整合到⼀起，这样就不⽤费劲的拼凑条件并且也能⽤到索引了。
最后安利下，知数堂培训马上推出 SQL开发优化课程，由业界资深SQL优化专家郑⽼师授课。
该课程关键字：MySQL、Oracle、SQL调优、EXPLAIN、DBMS_XPLAN、OPTIMIZER TRACE、SQL改写、NESTED LOOP、OUTER JOIN、HASH JOIN、ERD图、HINT、SORT MERGE、Materialized View、ROWNUM。
学完本课程，⽆论您是DBA⼯程师、运维⼯程师，还是开发⼯程师，抑或系统架构师、技术主管，都将⼤幅增强您的职场实⼒，加薪50%轻轻松松。此外，我们也会将优秀的学员直接推向各⼤⼀线互联⽹公司。
本周四晚上郑⽼师还会再进⾏⼀次公开课分享，讲讲GROUP BY的⽤法及堵门优化技巧。
有兴趣的同学可以扫码加⼊知数堂QQ 579036588关注课程进展。
不再加原创
喜欢就转发
打赏可勾搭
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论