|
|
|
|
公众号矩阵

牛批!一张6000w数据表的查询优化到0.023s

我司系统中有一个专门存车流量的库(没有主键),其中一个历史表数据量太大,表空间占据太大,每天有 500w 的数据写入。

作者:马男波杰克来源:掘金|2020-12-18 09:19

 

图片来自 Pexels

详细需求

老大给我安排了个任务,让我写个按天分表的定时任务,每次把一天的数据转移到按天生成的表中,并删除原表中的数据,主要目的是不想再增长表空间了,保持一个平衡,因为每天删 500w 也会加 500w。

表空间和数据量如下图:

实现思路

实现做法流程,如图:

实现伪代码(删减了部分代码):

  1. /** 
  2.     * 转移数据 每天凌晨3点 每次只能转移一天的数据 
  3.     */ 
  4.    @Scheduled(cron = "0 0 3 * * ?"
  5.    public void dataTransfer()throws Exception{ 
  6.        System.out.println("定时器开始运行------------------------------------------"); 
  7.        String tabaleName = "XXX"
  8.        String isTable = getTableName(tabaleName); 
  9.         // 当返回为空时,代表该表不存在,则创建 
  10.         if(ObjectUtils.isNull(isTable)){ 
  11.                 createHistoryDate(names); 
  12.         } 
  13.  
  14.         // 得到最远的时间段 
  15.        Map<String, Object> orderTime = orderByTime(); 
  16.        // 得到开始和结束时间 
  17.        if(SysFun.isNotEmpty(orderTime) && orderTime.size() > 0){ 
  18.            orderTime.put("startTime",startTime); 
  19.            orderTime.put("endTime",endTime); 
  20.            orderTime.put("tableName",tabaleName); 
  21.            int i=0; 
  22.            for (;;) { 
  23.                System.out.println("进入循环"); 
  24.                // 转移数据 
  25.                int rst =  dataTransfer(orderTime); 
  26.                // 删除重复数据 
  27.                int delt = deleteDataTransfer(orderTime); 
  28.                // 当今天数据转移完成时,退出本次循环 
  29.                if(rst<=0 && delt <=0){ 
  30.                    break; 
  31.                } 
  32.                i++; 
  33.                System.out.println("转移数据表为:"+tabaleName+" 转移数据次数: "+i); 
  34.            } 
  35.        } 
  36.         System.out.println("定时器结束运行------------------------------------------"); 
  37.    } 

心路历程

方法完成之后,上周五去服务器正式实测,实测时方法用 @PostConstruct 修饰,会在服务器加载 Servlet 的时候运行,并且只会被服务器执行一次。

当时控制台打印:(“定时器开始运行”)卡住,去库中看到表已成功创建。

开始以为是某个地方异常了,后面逐一打印步骤发现是得到最远时间段是卡住了,也就是被一条 SQL 查询卡住了(直接用这条 SQL 去库里查询 300s+ 也没查询出来)。

然后维护这个库的小伙跟我说:要不直接 limit 1 吧,它的插入是根据时间顺序插入的,当时也想到了会出问题,时间顺序肯定不可能完全按照顺序写入。

周末程序走了 2 天果然有问题,如图:

limit 1 是行不通了,那就只能来查询优化了,讲查询优化之前,先说说为什么我们使用ORDER BY 为什么会这么慢?

深入分析

MySQL 有两种方式可以实现 ORDER BY 这里只做简单介绍:

①通过索引扫描生成有序的结果 举个例子。

假设 history 表有 id 字段上有主键索引,且 id 目前的范围在 1001-1006 之间,则 id 的索引 B+Tree 如下:

现在当我们想按照 id 从小到大的顺序中取出数据时,执行以下 SQL:

  1. select * from history order by id 

MySQL 会直接遍历上图 id 索引的叶子节点链表,不需要进行额外的排序操作。这就是用索引扫描来排序。

②使用文件排序(filesort)

但如果 id 字段没有任何索引,上图的 B+Tree 结构不存在,MySQL 就只能先扫表筛选出符合条件的数据,再将筛选结果根据 id 排序。这个排序过程就是 filesort。

我们要让 ORDER BY 字句使用索引来避免 filesort(用“避免”可能有些欠妥,某些场景下全表扫描、filesort 未必比走索引慢),以提高查询效率。

进行优化之前我们还需要学会看 SQL 的执行计划(EXPLAIN)分别为(这里着重讲解 type、rows、Extra,其它的这里不做讲解,可自己私下进行了解):

  1. id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra 

type:对表访问方式,表示 MySQL 在表中找到所需行的方式,又称“访问类型”。

MySQL 找到数据行的方式,效率排名 NULL>system>const>eq_ref>ref>range>index>All。

range 只检索给定范围的行,使用一个索引来选择行,一般是在 where 中出现 between、<、>、in 等查询,范围扫描好于全表扫描。

index Full Index Scan,Index 与 All 区别为 index 类型只遍历索引树。

通常比 All 快,因为索引文件通常比数据文件小。也就是说,虽然 all 和 index 都是读全表,但是 index 是从索引中读取的,而 all 是从硬盘读取的。

ALL Full Table Scan,将遍历全表以找到匹配的行。

rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,也就是说,用的越少越好。

extra:包含不适合在其他列中显式但十分重要的额外信息。

Using Index:表示相应的 select 操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错。

如果同时出现 using where,表明索引被用来执行索引键值的查找;如果没有同时出现 using where,表明索引用来读取数据而非执行查找动作。

Using filesort:当 Query 中包含 order by 操作,而且无法利用索引完成的排序操作称为“文件排序”。

下面我们通过一张真实数据图来分析:

PS:由于正式库的未优化之前的执行计划图片忘记保存,这里用本地环境代替一下,内容相同 执行 SQL:

  1. EXPLAIN SELECT updateTime FROM historydata ORDER BY updateTime LIMIT 1 

通过我们那种查询是没有办法命中索引的,遵循最左原则,为 updateTime 新建一个普通索引(index)NORMAL:

添加索引的过程中再提一嘴:因为这个历史库时时刻刻都有数据写入,所以我当时建索引时担心锁表。

后面查询相关资料就知道了,MySQL 5.6 之后的版本不影响读写,不会锁表,前提存储引擎为 InnoDB,MyISAM 加索引锁表,读写会全部堵塞。

如果表数据量过多,可能建立索引的时间会过长,以我举例 6000w 差不多建了 4h,下面为索引效果图:

结合执行计划分析该数据,优化就到这了,优化过后这几天定时程序异常的稳,每天定时 500w 数据的转移和删除,也算是解决了。

结尾

其实本文就是一些很基础得东西,欢迎指出问题,可能大家都知道,但是没有机会去实际接触这么多数据,实际去优化这样的东西,我也是第一次接触这些东西,写本文单纯就是想分享下,顺便加深下自己的印象,写的不好,请见谅!!

【编辑推荐】

  1. Redis在Python接口自动化中效率优化应用代码实战(附源码)【大雄测试】
  2. JavaScript优化技巧
  3. 微软优化Windows 10锁屏、桌面和相机设置 新功能抢先看
  4. linux系统安装与优化-centos-6.7
  5. Mysql高级性能优化
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

数据湖与数据仓库的分析实践攻略

数据湖与数据仓库的分析实践攻略

助力现代化数据管理:数据湖与数据仓库的分析实践攻略
共3章 | 创世达人

1人订阅学习

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

30人订阅学习

数据中心和VPDN网络建设案例

数据中心和VPDN网络建设案例

漫画+案例
共20章 | 捷哥CCIE

209人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微