图片来自Pexels需求详解老板给我安排了一个任务，让我写一个基于天天表的定时任务，并且每次将一天的数据转移到day-by-day生成的表中，并删除原表中的数据，主要目的是不增加表空间，保持平衡，因为每天删除500w还会加500w。表空间和数据量如下图所示：实现思路和实现过程，如图：实现伪代码（部分代码删除）：/***传输数据只能在3传输一天的数据每天早上一点*/@Scheduled(cron="003**?")publicvoiddataTransfer()throwsException{System.out.println("定时器开始运行----------------------------------------");StringtabaleName="XXX";StringisTable=getTableName(tabaleName);//当返回为空，表示该表不存在，则创建if(ObjectUtils.isNull(isTable)){createHistoryDate(names);}//获取最远时间段MaporderTime=orderByTime();//获取开始和结束时间if(SysFun.isNotEmpty(orderTime)&&orderTime.size()>0){orderTime.put("startTime",startTime);orderTime.put("endTime",endTime);orderTime.put("tableName",tabaleName);inti=0;for(;;){System.out.println("进入循环");//传输数据intrst=dataTransfer(orderTime);//删除eteduplicatedataintdelt=deleteDataTransfer(orderTime);//今天数据传输完成，退出本次循环if(rst<=0&&delt<=0){break;}i++;System.out.println("传输数据表is:"+tabaleName+"传输数据次数:"+i);}}System.out.println("定时器运行完毕----------------------------------------");}心路历程方法完成后，上服务器上周五进行正式测试，实际测试时用@PostConstruct修饰方法，在服务器加载Servlet时运行，控制台打印时服务器只执行一次：("定时器启动running")卡住，去库里看表已经创建成功开始我以为是某处异常，但是一步步打印后，发现最远的时间段卡住了，也就是被一个SQL查询卡住了（直接用这个SQL查询库300s+，没查询出来）。然后维护这个库的小哥跟我说：为什么不直接限制1，它的插入是按照时间顺序插入的，而且我当时也觉得会出问题，时间顺序一定不能写全序。周末节目走了2天，确实有亲blem，如图：limit1不可行，只能来查询优化。在说查询优化之前，先说说为什么我们用ORDERBY这么慢？深入分析MySQL有两篇。ORDERBY的实现方式有以下三种。这里只简单介绍一下：①通过索引扫描生成有序的结果。举个例子。假设history表有一个id字段，主键索引，id当前范围在1001-1006之间，那么id索引B+Tree如下：现在当我们要按id升序检索数据，执行如下SQL：select*fromhistoryorderbyidMySQL会直接遍历上图中id索引的叶子节点链表，不需要额外的排序操作。这是按索引扫描排序。②使用文件排序（filesort）但是如果id字段没有任何索引，上图中的B+Tree结构是不存在的，MySQL只能扫表过滤出符合条件的数据，然后根据filter结果进行排序到身份证。这个排序过程就是filesort。我们需要让ORDERBY子句使用索引来避免filesort（使用“avoid”可能有点不合适，在某些场景下全表扫描和filesort可能并不比索引慢），从而提高Query效率。在优化之前，我们还需要学会分别看SQL的执行计划（EXPLAIN）（这里重点讲解type、rows、Extra，其他这里不做讲解，大家可以私下了解）:id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extratype：表访问方式，表示MySQL在表中查找所需行的方式，也称为“访问类型”。MySQL查找数据行的方式，效率排序为NULL>system>const>eq_ref>ref>range>index>All。range只检索给定范围内的行，并使用索引来选择行。一般来说，between、<、>、in等查询出现在where中，范围扫描优于全表扫描。indexFullIndexScan，Index和All的区别在于索引类型只遍历索引树。通常比All快，因为索引文件通常比数据文件小。也就是说，虽然all和index都是读全表，但是index是从索引中读取，而all是从硬盘中读取。ALLFullTableScan，会遍历全表寻找匹配的行。rows：根据表统计和索引选择，粗略估计找到所需记录需要读取的行数，也就是说越少越好。extra：包含重要的额外信息，不适合在其他列中显式显示。UsingIndex：表示在相应的select操作中使用覆盖索引（CoveringIndex），避免访问表的数据行，效率较好。如果同时出现usingwhere，则表示该索引用于执行索引键值的查找；如果没有同时使用where，说明该索引用于读取数据，而不是执行查找动作。Usingfilesort：当Query中包含orderby操作时，使用索引无法完成的排序操作称为“文件排序”。我们通过一张真实的数据图来分析一下：PS：忘记保存官方库未优化的执行计划图了，所以这里我换成本地环境，内容是一样的。执行SQL：EXPLAINSELECTupdateTimeFROMhistorydataORDERBYupdateTimeLIMIT1通过我们的查询，是没有办法命中索引的，遵循最左原则，新建一个普通索引（index）NORMALforupdateTime:加索引的过程中会再提一下：因为这个historicaldatabase一直有数据写入，建索引的时候担心锁表。后面查询相关资料就可以知道了。MySQL5.6以后的版本不影响读写，不会锁表。前提是存储引擎是InnoDB，MyISAM加上索引锁表，所有的读写都会被阻塞。如果表数据太多，建立索引的时间可能会过长。比如6000w，建了差不多4个小时。下面是索引效果图：结合执行计划分析数据，优化到此结束。程序极其稳定，每天定时传输和删除500w数据也算是一个解决方案。最后，这篇文章其实是一些很基础的东西。欢迎大家指出问题。可能大家都知道，但是我没有机会真正去接触那么多数据，真正去优化这样的东西。我也是第一次接触这些东西。分享一下，顺便加深一下印象，写的不好还请见谅！！
