查询优化：记一次查询优化实战：24秒查询速度优化至0.1秒

(本次文章写于2020年，思路尚未清晰，有所遗漏疏忽多多见谅，欢迎互相讨论。)

在我们数据采集系统的设备检测数据模块中，运行两天采集了五百万数据后暴露了一个数据查询语句的性能问题，导致用户体验极差，数据库查询性能亟需优化，以下为优化过程记录及知识点总结。

• 本次优化知识点总结：

  1.善用EXPLAIN执行计划：通过使用EXPLAIN命令获得查询的执行计划信息，了解查询的执行情况、索引使用等，有针对性的去调整查询语句或者索引设置。

  2.编写良好的查询语句：确保查询语句只返回需要的结果集。（具体优化思路需要根据不同业务需求、不同查询方式来确定一个只返回需要的结果集的查询方式，例如在本次场景下，通过JOIN联表查询操作获取更多数据与其他表匹配会消耗更多时间，使用子查询预先筛选掉部分数据后再联表匹配可以消耗更少的资源）

以下是优化过程记录：

查询涉及到三个表:

acquisition_time：采集时间表，里面保存了每次采集的时间以及采集的是哪个设备的信息

index_data：采集数据表，里面保存了每个指标每次采集的数据

indicator：指标表，保存了采集数据在列表是否需要展示的字段名字

1.提取出SQL语句出执行，查看执行时长，此时已在indicator和acquisition_time表中对相应的列(station_code、acquisition_date等)建立了索引

2.提取出SQL语句出执行，加上分页条件后查看执行时长（由于实践时碰到了前面几页的速度提升，但是在几百页后的数据查询依然缓慢，因此分页条件设为从第一万条数据起查询一万行数据）

3.使用explain查看语句执行过程，可以看出ir子查询是全表扫描，需要优化

4.从大数据量的表index_data入手，将indicator和index_data表的联查写在子查询中，在去acquisition_time表联查之前筛选去部分数据，减少数据匹配的基数

5.再加上分页查询，可以发现查询速度在分页查询10000条数据的场景下对比之前，已经从24秒降到了0.16秒左右

6.使用explain查看语句执行过程，可以看出三张表的查询的type都优化至ref级别

(注：在不同情况下优化器选择索引不同，优化需根据不同场景进行优化)

7.使用explain查看语句执行过程，加上limit后，采集时间表的type变成range级别了