分区表场景下的 SQL 优化
导读
有个表做了分区，每天一个分区。
该表上有个查询，经常只查询表中某一天数据，但每次都几乎要扫描整个分区的所有数据，有什么办法进行优化吗？
待优化场景
有一个大表，每天产生的数据量约100万，所以就采用表分区方案，每天一个分区。
下面是该表的DDL：
CREATETABLE`t1`(
`id`bigint(20)NOTNULLAUTO_INCREMENT,
`date`dateNOTNULL,
`kid`int(11)DEFAULT'0',
`uid`int(11)NOTNULL,
`iid`int(11)DEFAULT'0',
`icnt`int(8)DEFAULT'0',
`tst`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
`countp`smallint(11)DEFAULT'1',
`isr`int(2)NOTNULLDEFAULT'0',
`clv`int(5)NOTNULLDEFAULT'1'，
PRIMARYKEY(`id`,`date`),
UNIQUEKEY`date`(`date`,`uid`,`iid`),
KEY`date_2`(`date`,`kid`)
)ENGINE=InnoDBAUTO_INCREMENT=3180686682DEFAULTCHARSET=utf8mb4
/*!50500PARTITIONBYRANGECOLUMNS(`date`)
(PARTITIONp20161201VALUESLESSTHAN('2016-12-02')ENGINE=InnoDB,
PARTITIONp20161202VALUESLESSTHAN('2016-12-03')ENGINE=InnoDB,
PARTITIONp20161203VALUESLESSTHAN('2016-12-04')ENGINE=InnoDB,
该表上经常发生下面的慢查询：
SELECT...FROM`t1`WHERE`date`='2017-04-01'AND`icnt`>300AND`id`='801301';
SQL优化之路
SQL优化思路
想要优化一个SQL，一般来说就是先看执行计划，观察是否尽可能用到索引，同时要关注预计扫描的行数，以及是否产生了临时表（Usingtemporary）或者是否需要进行排序（Usingfilesort），想办法消除这些情况。
更进一步的优化策略则可能需要调整程序代码逻辑，甚至技术架构或者业务需求，这个动作比较大，一般非核心系统上的核心问题，不会这么大动干戈，绝大多数情况，还是需要靠DBA尽可能发挥聪明才智来解决。
SQL性能瓶颈定位
现在，我们来看下这个SQL的执行计划：
yejr@imysql.com[myDB]>EXPLAINPARTITIONSSELECT...FROM`t1`WHERE `date`='2017-03-02'AND`icnt`>100AND`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:Usingwhere
这个执行计划看起来还好，有索引可用，也没临时表，也没filesort。不过，我们也注意到，预计要扫描的行数还是挺多的rows:9384602，而且要扫描zheng整个分区的所有数据，难怪效率不高，总是SLOWQUERY。
优化思考
我们注意到这个SQL总是要查询某一天的数据，这个表已经做了按天分区，那是不是可以忽略WHERE子句中的时间条件呢？
还有，既然去掉了date条件，反观表DDL，剩下的条件貌似就没有合适的索引了吧？
所以，我们尝试新建一个索引：
yejr@imysql.com[myDB]>ALTERTABLEt1ADDINDEXiid(iid,icnt);
然后，把SQL改造成下面这样，再看下执行计划：
yejr@imysql.com[myDB]>EXPLAINPARTITIONSSELECT...FROM`t1`partition(p2017030)WHERE `icnt`>100AND`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:Usingwhere 这优化效果，杠杠滴。 事实上，如果不强制指定分区的话，也是可以达到优化效果的： yejr@imysql.com[myDB]>EXPLAINPARTITIONSSELECT...FROM`t1`WHERE `date`='2017-03-02'AND`icnt`>100AND`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:Usingwhere
后记
绝大多数的SQL通过添加索引、适当调整SQL代码（例如调整驱动表顺序）等简单手法来完成。
多说几句，遇到SQL优化性能瓶颈问题想要在技术群里请教时，麻烦先提供几个必要的信息：
- 表DDL
- 表常规统计信息，可执行SHOWTABLESTATUSLIKE‘t1'查看
- 表索引分布信息，可执行SHOWINDEXFROMt1查看
- 有问题的SQL及相应的执行计划没有这些信息的话，就别去麻烦别人了吧。
以上就是分区表场景下的SQL优化的详细内容，更多关于sql分区表优化的资料请关注毛票票其它相关文章！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。