有个表做了分区,每天一个分区。
该表上有个查询,经常只查询表中某一天数据,但每次都几乎要扫描整个分区的所有数据,有什么办法进行优化吗?
有一个大表,每天产生的数据量约 100 万,所以就采用表分区方案,每天一个分区。
下面是该表的 DDL:
- CREATE TABLE `t1` (
- `id` bigint(20) NOT NULL AUTO_INCREMENT,
- `date` date NOT NULL,
- `kid` int(11) DEFAULT '0',
- `uid` int(11) NOT NULL,
- `iid` int(11) DEFAULT '0',
- `icnt` int(8) DEFAULT '0',
- `tst` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- `countp` smallint(11) DEFAULT '1',
- `isr` int(2) NOT NULL DEFAULT '0',
- `clv` int(5) NOT NULL DEFAULT '1',
- PRIMARY KEY (`id`,`date`),
- UNIQUE KEY `date` (`date`,`uid`,`iid`),
- KEY `date_2` (`date`,`kid`)
- ) ENGINE=InnoDB AUTO_INCREMENT=3180686682 DEFAULT CHARSET=utf8mb4
- /*!50500 PARTITION BY RANGE COLUMNS(`date`)
- (PARTITION p20161201 VALUES LESS THAN ('2016-12-02') ENGINE = InnoDB,
- PARTITION p20161202 VALUES LESS THAN ('2016-12-03') ENGINE = InnoDB,
- PARTITION p20161203 VALUES LESS THAN ('2016-12-04') ENGINE = InnoDB,
- ...
该表上经常发生下面的慢查询:
- SELECT ... FROM `t1` WHERE `date` = '2017-04-01' AND `icnt` > 300 AND `id` = '801301';
想要优化一个 SQL,一般来说就是先看执行计划,观察 是否尽可能用到索引 ,同时要关注 预计扫描的行数 ,以及 是否产生了临时表(Using temporary) 或者 是否需要进行排序(Using filesort) ,想办法消除这些情况。
更进一步的优化策略则可能需要调整程序代码逻辑,甚至技术架构或者业务需求,这个动作比较大,一般非核心系统上的核心问题,不会这么大动干戈,绝大多数情况,还是需要靠 DBA 尽可能发挥聪明才智来解决。
现在,我们来看下这个 SQL 的执行计划:
- yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE
- `date` = '2017-03-02' AND `icnt` > 100 AND `iid` = '502302'\G
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: t1
- partitions: p20170302
- type: range
- possible_keys: date,date_2
- key: date
- key_len: 3
- ref: const
- rows: 9384602
- Extra: Using where
这个执行计划看起来还好,有索引可用,也没临时表,也没 filesort。不过,我们也注意到,预计要扫描的行数还是挺多的 rows: 9384602 ,而且要扫描 zheng 整个分区的所有数据,难怪效率不高,总是 SLOW QUERY。
我们注意到这个 SQL 总是要查询某一天的数据,这个表已经做了按天分区,那是不是可以忽略 WHERE 子句中的 时间条件 呢?
还有,既然去掉了 date 条件,反观表 DDL,剩下的条件貌似就没有合适的索引了吧?
所以,我们尝试新建一个索引:
- yejr@imysql.com[myDB]> ALTER TABLE t1 ADD INDEX iid (iid, icnt);
然后,把 SQL 改造成下面这样,再看下执行计划:
- yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` partition(p2017030) WHERE
- `icnt` > 100 AND `iid` = '502302'\G
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: t1
- partitions: p20170302
- type: ref
- possible_keys: date,date_2,iid
- key: iid
- key_len: 10
- ref: const
- rows: 7800
- Extra: Using where
这优化效果,杠杠滴。
事实上,如果不强制指定分区的话,也是可以达到优化效果的:
- yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE
- `date` = '2017-03-02' AND `icnt` > 100 AND `iid` = '502302'\G
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: t1
- partitions: p20170302
- type: ref
- possible_keys: date,date_2,iid
- key: iid
- key_len: 10
- ref: NULL
- rows: 7800
- Extra: Using where
绝大多数的 SQL 通过添加索引、适当调整 SQL 代码(例如调整驱动表顺序)等简单手法来完成。
多说几句,遇到 SQL 优化性能瓶颈问题想要在技术群里请教时,麻烦先提供几个必要的信息:
最后安利下,知数堂培训马上推出 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% 轻轻松松。此外,我们也会将优秀的学员直接推向各大一线互联网公司。
有兴趣的同学可以扫码加入知数堂 QQ 群 579036588 关注课程进展。
不再加原创
喜欢就转发
打赏可勾搭
靠谱好茶 & 在线培训,都在〖老叶茶馆〗 http://yejinrong.com
来源: http://www.tuicool.com/articles/2eEveq