sql分区原理(分区表场景下的 SQL 优化)
sql分区原理
分区表场景下的 SQL 优化导读
有个表做了分区,每天一个分区。
该表上有个查询,经常只查询表中某一天数据,但每次都几乎要扫描整个分区的所有数据,有什么办法进行优化吗?
待优化场景
有一个大表,每天产生的数据量约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优化之路
SQL优化思路
想要优化一个SQL,一般来说就是先看执行计划,观察是否尽可能用到索引,同时要关注预计扫描的行数,以及是否产生了临时表(Using temporary) 或者 是否需要进行排序(Using filesort),想办法消除这些情况。
更进一步的优化策略则可能需要调整程序代码逻辑,甚至技术架构或者业务需求,这个动作比较大,一般非核心系统上的核心问题,不会这么大动干戈,绝大多数情况,还是需要靠DBA尽可能发挥聪明才智来解决。
SQL性能瓶颈定位
现在,我们来看下这个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优化性能瓶颈问题想要在技术群里请教时,麻烦先提供几个必要的信息:
- 表DDL
- 表常规统计信息,可执行 SHOW TABLE STATUS LIKE ‘t1' 查看
- 表索引分布信息,可执行 SHOW INDEX FROM t1 查看
- 有问题的SQL及相应的执行计划 没有这些信息的话,就别去麻烦别人了吧。
以上就是分区表场景下的 SQL 优化的详细内容,更多关于sql分区表优化的资料请关注开心学习网其它相关文章!
- SQL SERVER数据类型对应C#的数据类型
- mysql自增长语句(mysql实现自增序列的示例代码)
- 如何看idea连接mysql数据库(IDEA 链接Mysql数据库并执行查询操作的完整代码)
- dedecms的dedesql.class.php on line 489错误的解决方法(dedecms的dedesql.class.php on line 489错误的解决方法)
- mysql删除的delete怎么找回(MySQL Delete 删数据后磁盘空间未释放的原因)
- SQL如何操作Xml字段
- 常用的mysql查询语句(MySQL查询重写插件的使用)
- sqlserver实例全库备份(sql server通过脚本进行数据库压缩全备份的方法推荐)
- mysql双主状态不一致(MySQL GTID主备不一致的修复方案)
- 优化SQL语句,提高数据库的访问性能
- windows下mysql密码重置(Windows10系统下Mysql8.0.13忘记root密码的操作方法)
- mysql怎么查看表的字段(Mysql 获取表的comment 字段操作)
- mysql事务未提交会锁表吗(Mysql事务中Update是否会锁表?)
- mysql把重复数据删掉(mysql查找删除重复数据并只保留一条实例详解)
- python2.7连接sqlserver数据库(Python使用sqlalchemy模块连接数据库操作示例)
- mysqltruncate(MYSQL中Truncate的用法详解)
- 苹果正式发布自研芯片M1 5nm 32核心 彻底放弃Intel(苹果正式发布自研芯片M1)
- 苹果自研芯片跑分对比 A16芯片排名靠后,M1系列霸榜(苹果自研芯片跑分对比)
- X86处理器的梦魇 苹果M1自研芯片到底有多强(苹果M1自研芯片到底有多强)
- 泰剧《爱欲之神》Boom kitkong和Great合体杂志(泰剧爱欲之神Boomkitkong和Great合体杂志)
- 素人恋爱综艺火药味十足 男生为赢得芳心集体扯头花,真是出好戏(素人恋爱综艺火药味十足)
- 《囧妈》为何受抵制 春节七部影片撤档背后的责任与博弈(囧妈为何受抵制)
热门推荐
- MVC 使用FileResult导出文件
- nginx怎么配置静态资源(nginx实现发布静态资源的方法)
- vue项目步骤(Vue项目中常用的实用技巧汇总)
- python无法拒绝的表白代码教程(很酷的python表白工具 你喜欢我吗)
- react常用设计模式(提高React界面性能的十个技巧)
- linux启动失败原因(安装主机大师Linux遇到bash: sudo: command not found 解决办法)
- docker容器如何更改tomcat端口(Docker方式启动tomcat访问首页出现404错误)
- vue实现一个tab栏(Vue实现tab导航栏并支持左右滑动功能)
- sql触发器编写教程(SQL Server 使用触发器trigger发送电子邮件步骤详解)
- css中分割线的渐变效果(CSS学习笔记之可重复渐变repeating-linear-gradient)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9