表格怎么筛选出需要的数据(两张数据表对比)

　　　　"　　

　　

　　　　我们公司在系统有一个专门存储流量的库(没有主键)。其中一个历史表数据太多，占用表空间太大，每天写500w的数据。　　

　　

　　　　？　　

　　

　　　　　　

　　

　　　　图片来自Pexels　　

　　

　　　　详细要求　　

　　

　　　　？　　

　　

　　　　表空间和数据量如下：　　

　　

　　　　　　

　　

　　　　　　

　　

　　　　实现理念　　

　　

　　　　实施实践过程，如图：　　

　　

　　　　实现伪代码(删除部分代码):　　

　　

　　　　？　　

　　

　　　　智力　　

　　

　　　　该方法完成后，于上周五由服务官方测量。在实际测量过程中，使用@PostConstruct对该方法进行了修改，该方法将在服务，加载Servlet时运行，并且只在服务执行运行一次。　　

　　

　　　　这时候控制台打印：(“计时器开始运行”)卡住了，去图书馆看表创建成功。　　

　　

　　　　刚开始我以为是什么不正常的东西，但是一个个打印出来之后发现卡在了最远的时间段，就是被一个SQL查询卡到了(直接去数据库查了300s也没找到)。　　

　　

　　　　然后图书馆的一个叫维护，的家伙对我说：你为什么不限制1？根据时间顺序插入。当时也觉得会有问题，时间顺序肯定不能完全按顺序写。　　

　　

　　　　2天后周末节目出现问题，如图：　　

size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=" />

　　

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

　　

　　　　深入分析

　　

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

　　

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

　　

　　　　假设 history 表有 id 字段上有主键索引，且 id 目前的范围在 1001-1006 之间，则 id 的索引 B+Tree 如下：现在当我们想按照 id 从小到大的顺序中取出数据时，执行以下 SQL：

　　

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

　　

　　　　?

　　

　　　　②使用文件排序（filesort）

　　

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

　　

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

　　

　　　　进行优化之前我们还需要学会看 SQL 的执行计划（EXPLAIN）分别为（这里着重讲解 type、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：

　　

　　　　?

　　

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

　　

　　　　

　　

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

　　

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

　　

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

　　

　　　　

　　

　　　　

　　

　　　　

　　

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

　　

　　　　结尾

　　

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

　　

　　　　作者：马男波杰克

　　

　　　　编辑：陶家龙

　　

　　　　出处：juejin.cn/post/6907115764545748999