Loading [MathJax]/jax/input/TeX/config.js
前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >分区表场景下的 SQL 优化

分区表场景下的 SQL 优化

原创
作者头像
叶金荣
发布于 2017-07-04 01:49:17
发布于 2017-07-04 01:49:17
1K0
举报
文章被收录于专栏:iMySQL的专栏iMySQL的专栏

导读

有个表做了分区,每天一个分区。

该表上有个查询,经常只查询表中某一天数据,但每次都几乎要扫描整个分区的所有数据,有什么办法进行优化吗?

待优化场景

有一个大表,每天产生的数据量约100万,所以就采用表分区方案,每天一个分区。

下面是该表的DDL:

代码语言:txt
AI代码解释
复制
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,
...

该表上经常发生下面的慢查询:

代码语言:txt
AI代码解释
复制
SELECT ... FROM `t1` WHERE `date` = '2017-04-01' AND `icnt` > 300 AND `id` = '801301';

SQL优化之路

SQL优化思路

想要优化一个SQL,一般来说就是先看执行计划,观察是否尽可能用到索引,同时要关注预计扫描的行数,以及是否产生了临时表(Using temporary) 或者 是否需要进行排序(Using filesort),想办法消除这些情况。

更进一步的优化策略则可能需要调整程序代码逻辑,甚至技术架构或者业务需求,这个动作比较大,一般非核心系统上的核心问题,不会这么大动干戈,绝大多数情况,还是需要靠DBA尽可能发挥聪明才智来解决。

SQL性能瓶颈定位

现在,我们来看下这个SQL的执行计划:

代码语言:txt
AI代码解释
复制
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,剩下的条件貌似就没有合适的索引了吧?

所以,我们尝试新建一个索引:

代码语言:txt
AI代码解释
复制
yejr@imysql.com[myDB]> ALTER TABLE t1 ADD INDEX iid (iid, icnt);

然后,把SQL改造成下面这样,再看下执行计划:

代码语言:txt
AI代码解释
复制
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及相应的执行计划 没有这些信息的话,就别去麻烦别人了吧。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
Java程序员:从菜鸟码农到架构师六步走
在外人眼里,程序员这个职业总是被打上高薪、高大上的标签。可是鬼知道我们经历了什么,付出了多少。但是付出终会有收获的,IT这个行业,多数都是从程序员开始,小编也是怀揣着梦想,从这里出发。
java架构师
2018/08/23
5180
Java程序员:从菜鸟码农到架构师六步走
学习java需要会哪些知识才能够去应聘工作?
按照我去培训机构的学习经历,给初学还有自学Java 的同学一个基本的学习脉络,希望对大家有帮助。 不建议找到一本书死啃,没啥用,不要有这一页看不明白我就不往下看的想法。计算机的学习和原来的代数、几何不大一样。不是用几个公理推出所有结果的。! 首先我们学习Java需要有目标!应该知道先学哪些再学哪些 !不是盲目的去乱学去! Java基础入门 Java高级阶段 数据库和JDBC Html&Javascript Jsp&Servlet Struts2框架讲解 Spring框架讲解 Hibernate框架讲解 流行
Java学习
2018/04/16
1.3K0
学习java需要会哪些知识才能够去应聘工作?
分享下Java大数据学习路线图
1.开发工具的安装配置的介绍 2.JDK安装 3.DOS环境编程 4.Eclipse的安装使用
全栈程序员站长
2022/09/01
7330
Java零基础到高级
曾经迷惘过,一直想知道java的具体学习路线,看过了许许多多的java经验分享的帖子,评论,以及其他各种培训机构所谓的学习路线,发现没有一个符合我个人需求的学习路线,根据个人实际的经历,结合多种书籍(其中个人觉得“疯狂java讲义”这本书写的特别好,所以大部分是根据这本书整理的),特别整理出了下面的java学习路线,跟大家分享,觉得好的话,希望大家多多指点,下面进入主题: 一、Java SE部分
瑞新
2020/07/07
6290
2017最全的Java学习方向
方向不对努力白费,Java技术的学习并不是一蹴而就的,正确的学习方向能让你事半功倍,如果你想在自己的Java学习之初就了解学Java又好又快的方法,那么这篇文章就是为你准备的。 1Java SE部分 1、java基础:基础语法;面向对象(重点);集合框架(重点);常见类库API; 2、java界面编程:AWT;事件机制;Swing; 3、java高级知识:Annotation;IO和NIO、AIO;多线程、线程池;阻塞、非阻塞、异步网络通信;反射、动态代理; 2JDBC编程 4、SQL基础:基础SQL语句;
企鹅号小编
2018/01/26
1.7K0
Java工程师学习指南(初级篇)
最近有很多小伙伴来问我,Java小白如何入门,如何安排学习路线,每一步应该怎么走比较好。原本我以为之前的几篇文章已经可以解决大家的问题了,其实不然,因为我之前写的文章都是站在Java后端的全局上进行思考和总结的,忽略了很多小白们的感受,而很多朋友都需要更加基础,更加详细的学习路线。
程序员黄小斜
2019/01/21
6700
Java程序员如何面试拿到月薪3万, 这些成功秘籍你知道吗?
前言:想要成为合格的Java程序员或工程师到底需要具备哪些专业技能,面试者在面试之前到底需要准备哪些东西呢?本文陈列的这些内容既可以作为个人简历中的内容,也可以作为面试的时候跟面试官聊的东西,你可以把这些内容写到你的简历中,当然更需要的是你在面试的时候向面试官展示这些专业技能。此文为对正在寻觅Java程序员(Java工程师)职位的freshman以及希望成为中高级Java开发者的junior求职秘籍。
美的让人心动
2018/07/30
5940
Java程序员如何面试拿到月薪3万, 这些成功秘籍你知道吗?
Java学习路线图分析
 Java学习路线分析图 第一阶段 技术名称 技术内容 J2SE(java基础部分) java开发前奏 计算机基本原理,Java语言发展简史以及开发环境的搭建,体验Java程序的开发,环境变量的设置,
Kevin_Zhang
2018/05/22
1.4K0
初学Java编程需要知道的几大重点步骤
初学Java编程需要知道的几大重点步骤 一、学习前的准备工作 java语言一般用于大型的服务器程序开发,所有有必要了解如下内容:Unix开发环境Unix系统原理、Unix开发环境、Unix常用命令。熟练掌握Unix常用命令;熟练构建下的Java开发环境。 二、Java语言基础的学习阶段学习 任何一门语言都要打好基础,曾经学过高级程序语言(C,C++等)来说,java语言基础有自己的特点,但是基本规律都是相通的,如:算法基础、常用数据结构、企业编程规范。掌握常见的数据结构和实用算法;培养良好的企业级编程习
企鹅号小编
2018/02/02
1.1K0
初学Java编程需要知道的几大重点步骤
Java工程师学习指南 初级篇
最近有很多小伙伴来问我,Java小白如何入门,如何安排学习路线,每一步应该怎么走比较好。原本我以为之前的几篇文章已经可以解决大家的问题了,其实不然,因为我之前写的文章都是站在Java后端的全局上进行思考和总结的,忽略了很多小白们的感受,而很多朋友都需要更加基础,更加详细的学习路线。
程序员黄小斜
2019/03/10
7520
Java学习路线指南
首先,我不建议那些头脑一时发热盲目的想要学习编程,特别是毕业工作一段时间了想要转行互联网(美其名曰:互联网工资高)学习编程的;在职业规划上大家尤其要慎重考虑,至少是自己深思熟虑和结合自身而选择的职业。如果,你是一个性格非常外向,而性子又急坐不住的人。那么,编程可能就不太适合你了。当然,凡是无绝对,还是得本人亲自体验经历过后才知道。那么,小编这里有几点建议和要求:
程序大视界
2020/07/21
8890
Java学习路线指南
面试的角度诠释Java工程师(二)
上一篇:面试的角度诠释Java工程师(一) ---- 三、框架篇 框架基础 反射:反射是Java开发的一类动态相关机制。因为本身Java语言并不是一款动态语言,如果我们想要得到程序动态的效果,因
Spark学习技巧
2018/06/22
4350
面试的角度诠释Java工程师(二)
反射:反射是Java开发的一类动态相关机制。因为本身Java语言并不是一款动态语言,如果我们想要得到程序动态的效果,因此便引入了反射机制这一概念。
Java团长
2018/08/03
4510
盘点 6 个被淘汰的 Java 技术,它们都曾经风光过!
Swing 算是 Java 早期代替 AWT 的桌面应用 GUI 开发工具包,一个听到就已经淘汰的技术,给我的感觉就是丑丑丑!现在与 AWT 一起在时间这个长河里长眠。
Java技术栈
2020/07/22
1.8K0
盘点 6 个被淘汰的 Java 技术,它们都曾经风光过!
Java后端学习流程
首先,我个人比较推崇的学习方法是:先学java前端,也就是HTML,css,js,因为学习java以后肯定是往java ee方向发展的,学习完前端,在学习后端很多东西比计较容易理解!
Java团长
2018/08/06
1.6K0
java初学者怎么学习java路线最效率,不走弯路
如果我们是零基础或者非计算机专业毕业的,或者是计算机毕业的铜须,那么我们的基础我认为是比较不扎实的,换句话来说就是即使作为实习生来说,工作给我们的小任务我们都无法完成,我们只能请叫老同志寻求帮助。
java学长
2020/05/27
4000
java初学者怎么学习java路线最效率,不走弯路
Java项目经验——程序员成长的钥匙
本文主要分享了作者在找Java工作时的一些面试经验和心得,包括如何通过项目经验、基础知识、多线程、设计模式等方面展示自己的Java技能。作者还提供了面试Java常见问题的答案和训练方法,以帮助读者更好地应对面试。
Java后端工程师
2017/12/08
2.5K0
Java程序员修炼之路
大多数人选择Java可能只是因为听说Java前景好、Java比较好找工作、Java语言在TIOBE排行榜上一直位于前三等等之类的原因,但是Java具体好在哪里,心里却是没有什么概念的。其实我选择Java也是出于以上的原因,但是现在确实真正地爱上了Java。
Java团长
2018/08/06
6950
JavaEE就业学习路线(给初学者以及自学者一个学习方向)
第一节java入门 1-Java 背景介绍 2-Java 入门程序的编写 3-环境配置 4-基本概念介绍 5-类型转换 6-开发工具使用 第二节java基础 1-运算符的使用 2-键盘录入的练习 3-Java语法-判断 4-Java语法-循环一 5-Java语法-循环二 6-随机数获取和使用 7-数组简介 8-数组的使用 9-方法的定义和使用 10-方法的练习 11-断点调试 12-基础语法练习一 13-基础语法练习二 第三节面向对象 1-面向对象概述 2-类的定义和使用 3-关键字和访问权限 第四节核心A
Java学习
2018/04/17
2.6K0
Java程序员面试题集(86-115)
Java程序员面试题集(86-115) 摘要:下面的内容包括Struts 2和Hibernate的常见面试题,虽然Struts 2在2013年6月曝出高危漏洞后已经显得江河日下,而Spring MVC的异军突起更加加速了Struts 2的陨落,但面试中仍然有可能被问及和此框架相关的内容,毕竟Struts 2曾经被阿里巴巴、京东以及政府企业门户网站广泛采用。另一方面,Hibernate目前仍然是ORM框架中的中坚力量,MyBatis在此领域也有不容忽视的一席之地,因此了解这两个ORM框架对Java程序员
汤高
2018/01/11
2K0
Java程序员面试题集(86-115)
相关推荐
Java程序员:从菜鸟码农到架构师六步走
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档