# 项目1 根据年份分表, 每表1千万左右 这个项目是接手另一个同事的二期项目, 二期新开发未上线, 预估(站点如果增加, 这个也会更大)数据每年1千万左右 业务: 分为按月和按年查询, 按年查询就是全表查, 目前问题是整个表就30万数据, 按月查询时20多万数据, 返回给前端用时超过20s, 按年查询1千万直接超时。 优化：使用 Java + SQL 对功能实现流程改造后，按月查询1s内， 按年查询17s。和公司业务太相关这里不去叙述了。 主要是之前返回太多字段，而且 按年查询是全表查询，返回的数据量也太多。后面是减少返回字段，使用覆盖索引，同时使用max和group by 将返回数据量降低90多倍， 其他功能java再循环处理 再次优化：从 17 s 优化到 5s， 重点是优化了group by 使用 EXPLAIN 发现 type 是 index, 但是 extra 有这俩 ``` Using temporary; Using filesort ``` 经过排查 group by 使用了 date_format 导致的使用了临时表, 而且联合索引里也排序了 去掉 date_format 和排序 extra 则没有这俩， 速度也大幅提高了， 大部分时候都是1秒内，加上网络传输时间是2秒左右 解决流程： 1. 优化SQL，把没必要的字段去掉，同时将查询条件和返回值做成联合索引，这样就能使用MySQL覆盖索引，同时使用max、count和group by 将返回数据量降低90多倍（每个设备每日就返回一条了）， 其他功能java再循环处理。 * max函数结果有2表示此设备本日有失败，还有一个下载成功的数量，这个就可以确认设备每日下载状态，这样每个设备每天查询一条数据就行 3. 像设备名称这种，直接把所有设备缓存到Redis，返回时从Redis集合获取名称就行，减少连表，其他SQL不好解决的，也放到Java里 4. 专门写一个类,把无关的字段全部删除,减少返回的无效字段 使用 EXPLAIN 发现 type 是 index, 但是 extra 有这俩 Using temporary; Using filesort 经过排查 group by 使用了 date_format 导致的使用了临时表, 而且联合索引里也排序了，再次优化去掉date_format 和 排序, 将这些放到Java去处理了, 速度再次提升 最终结果是： 按月查询全部站， 大部分时候都是1秒内，加上网络传输时间接口是2秒左右 按年查询全部站， 大部分时候都是17秒内，加上网络传输时间接口是25-30秒了 按年查询由于是全表查询， 而且也不常用， 没有再优化， 再优化是打算弄个统计表，但是由于有回补功能，改动比较大，就没处理。 # 项目2 分页查询太慢, 数据2千万,并且日增5万 这个业务就是个普通查询, 目前核心问题是要处理深度分页, 也有走联合索引, 返回的数据太多, 没法使用覆盖索引。 未处理深度分页时, 查询同一页数据是 104s, 处理后是6秒 就这样一个简单查询也要3s查询（key_len 4, type = index） ``` SELECT id FROM 表 LIMIT 17323100, 10; ``` 实际我们会根据索引字段去倒序 这样查询也要5s查询（key_len 13, type = index） ``` SELECT id FROM 表 ORDER BY 索引字段1 desc LIMIT 17323100, 10; ``` 目前是先使用类似上面的语句查询到主键id，然后返回给Java，Java再in去查询返回所有字段到前端， 速度也是大幅度提升 也可以一条SQL直接返回， 不能直接 in，需要嵌套一层， 类似 ``` SELECT 所有字段 FROM 表1 WHERE id IN ( SELECT t.id FROM ( SELECT id FROM 表1 WHERE is_delete = '0' ORDER BY 索引字段1 DESC, id DESC LIMIT 17322210, 10 ) AS t ) ```