导读
有个表做了分区&＃xff0c;每天一个分区。
该表上有个查询&＃xff0c;经常只查询表中某一天数据&＃xff0c;但每次都几乎要扫描整个分区的所有数据&＃xff0c;有什么办法进行优化吗&＃xff1f;
一、待优化场景
有一个大表&＃xff0c;每天产生的数据量约100万&＃xff0c;所以就采用表分区方案&＃xff0c;每天一个分区。
下面是该表的DDL&＃xff1a;
CREATE TABLE &＃96;t1&＃96; (&＃96;id&＃96; bigint(20) NOT NULL AUTO_INCREMENT,&＃96;date&＃96; date NOT NULL,&＃96;kid&＃96; int(11) DEFAULT &＃39;0&＃39;,&＃96;uid&＃96; int(11) NOT NULL,&＃96;iid&＃96; int(11) DEFAULT &＃39;0&＃39;,&＃96;icnt&＃96; int(8) DEFAULT &＃39;0&＃39;,&＃96;tst&＃96; timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,&＃96;countp&＃96; smallint(11) DEFAULT &＃39;1&＃39;,&＃96;isr&＃96; int(2) NOT NULL DEFAULT &＃39;0&＃39;,&＃96;clv&＃96; int(5) NOT NULL DEFAULT &＃39;1&＃39;&＃xff0c;PRIMARY KEY (&＃96;id&＃96;,&＃96;date&＃96;),UNIQUE KEY &＃96;date&＃96; (&＃96;date&＃96;,&＃96;uid&＃96;,&＃96;iid&＃96;),KEY &＃96;date_2&＃96; (&＃96;date&＃96;,&＃96;kid&＃96;)
) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;3180686682 DEFAULT CHARSET&＃61;utf8mb4
/*!50500 PARTITION BY RANGE COLUMNS(&＃96;date&＃96;)
(PARTITION p20161201 VALUES LESS THAN (&＃39;2016-12-02&＃39;) ENGINE &＃61; InnoDB,PARTITION p20161202 VALUES LESS THAN (&＃39;2016-12-03&＃39;) ENGINE &＃61; InnoDB,PARTITION p20161203 VALUES LESS THAN (&＃39;2016-12-04&＃39;) ENGINE &＃61; InnoDB,
...
该表上经常发生下面的慢查询&＃xff1a;
SELECT ... FROM &＃96;t1&＃96; WHERE &＃96;date&＃96; &＃61; &＃39;2017-04-01&＃39; AND &＃96;icnt&＃96; > 300 AND &＃96;id&＃96; &＃61; &＃39;801301&＃39;;
二、SQL优化之路1、SQL优化思路
想要优化一个SQL&＃xff0c;一般来说就是先看执行计划&＃xff0c;观察是否尽可能用到索引&＃xff0c;同时要关注预计扫描的行数&＃xff0c;以及是否产生了临时表&＃xff08;Using temporary&＃xff09; 或者 是否需要进行排序&＃xff08;Using filesort&＃xff09;&＃xff0c;想办法消除这些情况。
更进一步的优化策略则可能需要调整程序代码逻辑&＃xff0c;甚至技术架构或者业务需求&＃xff0c;这个动作比较大&＃xff0c;一般非核心系统上的核心问题&＃xff0c;不会这么大动干戈&＃xff0c;绝大多数情况&＃xff0c;还是需要靠DBA尽可能发挥聪明才智来解决。
2、SQL性能瓶颈定位
现在&＃xff0c;我们来看下这个SQL的执行计划&＃xff1a;
yejr&＃64;imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM &＃96;t1&＃96; WHERE &＃96;date&＃96; &＃61; &＃39;2017-03-02&＃39; AND &＃96;icnt&＃96; > 100 AND &＃96;iid&＃96; &＃61; &＃39;502302&＃39;G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1partitions: p20170302type: range
possible_keys: date,date_2key: datekey_len: 3ref: constrows: 9384602Extra: Using where
这个执行计划看起来还好&＃xff0c;有索引可用&＃xff0c;也没临时表&＃xff0c;也没filesort。不过&＃xff0c;我们也注意到&＃xff0c;预计要扫描的行数还是挺多的 rows: 9384602&＃xff0c;而且要扫描zheng整个分区的所有数据&＃xff0c;难怪效率不高&＃xff0c;总是SLOW QUERY。
3、优化思考
我们注意到这个SQL总是要查询某一天的数据&＃xff0c;这个表已经做了按天分区&＃xff0c;那是不是可以忽略 WHERE 子句中的 时间条件呢&＃xff1f;
还有&＃xff0c;既然去掉了 date 条件&＃xff0c;反观表DDL&＃xff0c;剩下的条件貌似就没有合适的索引了吧&＃xff1f;
所以&＃xff0c;我们尝试新建一个索引&＃xff1a;
yejr&＃64;imysql.com[myDB]> ALTER TABLE t1 ADD INDEX iid (iid, icnt);
然后&＃xff0c;把SQL改造成下面这样&＃xff0c;再看下执行计划&＃xff1a;
yejr&＃64;imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM &＃96;t1&＃96; partition(p2017030) WHERE &＃96;icnt&＃96; > 100 AND &＃96;iid&＃96; &＃61; &＃39;502302&＃39;G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1partitions: p20170302type: ref
possible_keys: date,date_2,iidkey: iidkey_len: 10ref: constrows: 7800Extra: Using where
这优化效果&＃xff0c;杠杠滴。
事实上&＃xff0c;如果不强制指定分区的话&＃xff0c;也是可以达到优化效果的&＃xff1a;
yejr&＃64;imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM &＃96;t1&＃96; WHERE &＃96;date&＃96; &＃61; &＃39;2017-03-02&＃39; AND &＃96;icnt&＃96; > 100 AND &＃96;iid&＃96; &＃61; &＃39;502302&＃39;G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1partitions: p20170302type: ref
possible_keys: date,date_2,iidkey: iidkey_len: 10ref: NULLrows: 7800Extra: Using where
后记
绝大多数的SQL通过添加索引、适当调整SQL代码&＃xff08;例如调整驱动表顺序&＃xff09;等简单手法来完成。
多说几句&＃xff0c;遇到SQL优化性能瓶颈问题想要在技术群里请教时&＃xff0c;麻烦先提供几个必要的信息&＃xff1a;
- 表DDL
- 表常规统计信息&＃xff0c;可执行 SHOW TABLE STATUS LIKE ‘t1’ 查看
- 表索引分布信息&＃xff0c;可执行 SHOW INDEX FROM t1 查看
- 有问题的SQL及相应的执行计划 没有这些信息的话&＃xff0c;就别去麻烦别人了吧。
京公网安备 11010802041100号