分区表场景下的 SQL 优化
短视频,自媒体,达人种草一站服务 这篇文章主要介绍了分区表场景下的 SQL 优化,帮助大家更好的理解和学习SQL,感兴趣的朋友可以了解下 导读 有个表做了分区,每天一个分区。 该表上有个查询,经常只查询表中某一天数据,但每次都几乎要扫描整个分区的所有数据,有什么办法进行优化吗? 待优化场景 有一个大表,每天产生的数据量约100万,所以就采用表分区方案,每天一个分区。 下面是该表的DDL: CREATE TABLE `t1` ( 该表上经常发生下面的慢查询: SELECT ... FROM `t1` WHERE `date` = '2017-04-01' AND `icnt` > 300 AND `id` = '801301'; SQL优化之路 SQL优化思路 想要优化一个SQL,一般来说就是先看执行计划,观察是否尽可能用到索引,同时要关注预计扫描的行数,以及是否产生了临时表(Using temporary) 或者 是否需要进行排序(Using filesort),想办法消除这些情况。 更进一步的优化策略则可能需要调整程序代码逻辑,甚至技术架构或者业务需求,这个动作比较大,一般非核心系统上的核心问题,不会这么大动干戈,绝大多数情况,还是需要靠DBA尽可能发挥聪明才智来解决。 SQL性能瓶颈定位 yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE 现在,我们来看下这个SQL的执行计划: 这个执行计划看起来还好,有索引可用,也没临时表,也没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 事实上,如果不强制指定分区的话,也是可以达到优化效果的: yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE 后记 绝大多数的SQL通过添加索引、适当调整SQL代码(例如调整驱动表顺序)等简单手法来完成。 多说几句,遇到SQL优化性能瓶颈问题想要在技术群里请教时,麻烦先提供几个必要的信息: 表DDL 表常规统计信息,可执行 SHOW TABLE STATUS LIKE ‘t1' 查看 表索引分布信息,可执行 SHOW INDEX FROM t1 查看 有问题的SQL及相应的执行计划 没有这些信息的话,就别去麻烦别人了吧。 以上就是分区表场景下的 SQL 优化的详细内容,更多关于sql分区表优化的资料请关注脚本之家其它相关文章! (编辑:南平站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |