优化案例 | CASE WHEN进行SQL改写优化
来源:互联网 发布:c4d软件安装 编辑:程序博客网 时间:2024/06/06 03:47
导读
今天给大家分享一个通过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 给独立出来成两列,查询时再合并成一个新的条件,简直无力吐槽。
吐槽归吐槽,该干活还得干活,谁让咱是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,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\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: p20170324,p20170325,....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 列加了函数,而且还是 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: p20170324 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.com[myDB]> ALTER TABLE t1 ADD INDEX idx_uid(uid);yejr@imysql.com[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 uid\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: if_date_hour_army_count partitions: p20170331,p20170401... 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
我们注意到,虽然加了 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 关注课程进展。
不再加原创
喜欢就转发
打赏可勾搭
靠谱好茶&在线培训,都在〖老叶茶馆〗http://yejinrong.com
- 优化案例 | CASE WHEN进行SQL改写优化
- SQL CASE WHEN语句性能优化
- 巧用case when做SQL优化
- 改写SQL优化SQL
- SQL改写优化技巧
- 【SQL优化】条件逻辑判断“case when then”
- 用sum case when做分类统计sql优化
- sql语句中多个union all的case when优化
- 用with改写优化sql
- 有关case when的两个sql案例
- 利用case when进行sql动态排序
- 改写SQL语句优化MySQL性能
- SQLServer性能优化之改写SQL语句
- 用with改写优化sql之二
- SQL优化-标量子查询的改写
- sql优化-典型案例
- SQL优化案例(1)
- SQL优化案例
- 衰!中关村鼎好卖场遭遇暴力骗局
- 师兄面试秒过,不靠颜值,竟然是靠这个!!!
- 美丽的厦门?呵呵哒
- 加入知数堂,成为专业MySQL DBA,就是这么简单!
- GROUP BY另类优化技巧
- 优化案例 | CASE WHEN进行SQL改写优化
- 5. Shell 脚本简明教程
- 好听到耳朵会怀孕的GROUP BY另类优化
- 优化案例 | 分区表场景下的SQL优化
- 优化案例 | 分区表场景下的SQL优化
- 由浅入深理解Paxos(2)
- 由浅入深理解Paxos协议(1)
- 基于MySQL 5.7多源复制及Keepalived搭建三节点高可用架构
- MVCC原理探究及MySQL源码实现分析