优化案例 | CASE WHEN进行SQL改写优化(2)

我们新建个 uid 列上的索引,看看能除临时表及排序后的代价如何,看看这个的开销会不会更低.

yejr@imysql.com[myDB]> ALTER TABLE t1 ADD INDEX idx_uid(uid);
yejr@imysql.com[myDB]> EXPLAIN select uid,9)
) a ?group by uid\G

*************************** 1. row ***************************
 ? ? ? ? ? id: 1
 ?select_type: SIMPLE
 ? ? ? ?table: if_date_hour_army_count
 ? partitions: p20170331,p20170401...
 ? ? ? ? type: index
possible_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 列整合到一起,这样就不用费劲的拼凑条件并且也能用到索引了.

作者： 陈丽寒、叶金荣

文章来自微信公众号：老叶茶馆

（编辑：ASP站长网）