当前位置:首页 >  站长 >  数据库 >  正文

分区表场景下的 SQL 优化

 2020-10-19 11:02  来源: 脚本之家   我来投稿 撤稿纠错

  【推荐】海外独服/站群服务器/高防

这篇文章主要介绍了分区表场景下的 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性能瓶颈定位

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

现在,我们来看下这个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
 `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分区表优化的资料请关注脚本之家其它相关文章!

来源:脚本之家

链接:https://www.jb51.net/article/193531.htm

申请创业报道,分享创业好点子。点击此处,共同探讨创业新机遇!

相关标签
sql优化

相关文章

  • UCloud高可用数据库UDB主从复制延时的解决

    MySQL主从复制的延时一直是业界困扰已久的问题。延时的出现会降低主从读写分离的价值,不利于数据实时性较高的业务使用MySQL。UDB是UCloud推出的云数据库服务,上线已达六年,运营了数以万计的UDBMySQL实例

    标签:
    sql优化
    数据库
    UC
  • 如何修复网站漏洞之metinfo远程SQL注入漏洞

    2018年11月23日SINE网站安全检测平台,检测到MetInfo最新版本爆出高危漏洞,危害性较大,影响目前MetInfo5.3版本到最新的MetInfo6.1.3版本,该网站漏洞产生的主要原因是MetInfo的上传代码里的参数值没有进行安全过滤,导致上传路径这里进行伪造路径,并可以插入恶意的代码

  • 虚拟主机mysql数据库三种导入方法

    在导入mysql数据库操作时通常会出现一些错误导致数据库导入失败,这里以老牌虚拟主机提供商第一主机(http://www.5778.com)的虚拟空间为例,演示介绍三种mysql数据库的正确导入方法。1、采用phpmyadmin导入:虚拟主机接入商在提供mysql数据库的同时都会配备phpmyadm

    标签:
    sql优化
  • MySQL技术嘉年华会2016即将召开

    会议通知大会简介2016年MySQL技术嘉年华将于2016年04月24日在上海举行,本次大会由IMG(InsideMySQLGroup)社区主办,专注于MySQL数据库最佳应用与实践的技术分享,将为数据库技术爱好者、应用开发人员、企业架构师们带来最具技术价值的饕餮大餐。毫无疑问,MySQL是最为流行

    标签:
    sql优化
  • linux下配置开发环境,mysql+jdk+tomcat教程全解

    linux下配置开发环境,mysql+jdk+tomcat教程全解最近由于自己开发需要,就下手租了一台服务器,系统版本是centos6.5,64位机器。在配置环境的时候在网上找了半天也没有发现比较全的开发环境配置方法,于是把自己的配置方法发上来以供大家交流学习。首先安装JDK:在oracle网站下载

    标签:
    sql优化

热门排行

信息推荐