sql查询优化排查三板斧：

1 避免使用【SELECT * 】

• 问题
  ：SELECT *会返回表中所有列，即使你只需要其中几列，这会导致不必要的数据传输和处理。
• 优化
  ：明确指定需要的列,减少数据传输和处理的开销。

2 使用索引

确保查询中的WHERE条件、JOIN条件和ORDER BY子句中的列都有适当的索引。

• 问题
  ：如果查询中的WHERE、JOIN或ORDER BY子句中的列没有索引，数据库可能需要进行全表扫描。
• 优化
  ：（1）为查询中的关键列创建索引；（2）对于复合查询，使用复合索引。

3 避免子查询

尽量将子查询转换为JOIN操作，子查询可能会导致全表扫描。

• 问题
  ：子查询可能会导致全表扫描，尤其是在嵌套查询中。
• 优化
  ：尽量将子查询转换为JOIN操作。

4 使用EXISTS代替IN

在某些情况下，EXISTS比IN更高效。

• 问题
  ：IN子句在处理大量数据时性能较差。
• 优化
  ：使用EXISTS代替IN，尤其是当子查询返回大量数据时。

备注：区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

5 避免使用函数

在WHERE条件中避免对列使用函数，这会导致索引失效。

• 问题
  ：在WHERE子句中对列使用函数（如DATE()、UPPER()等）会导致索引失效。
• 优化
  ：尽量在应用层处理数据，或者在查询中避免对列使用函数。

6 使用LIMIT

如果只需要部分结果，使用LIMIT来减少返回的数据量。

• 问题
  ：查询返回大量数据时，会占用大量内存和网络带宽。
• 优化
  ：如果只需要部分结果，使用LIMIT来减少返回的数据量。

7 优化JOIN操作

• 问题
  ：JOIN操作可能会导致大量数据扫描，尤其是在没有索引的情况下。
• 优化
  ： (1)确保JOIN条件中的列有索引。(2) 尽量减少JOIN的表数量。(3)使用小表驱动大表（小表在前，大表在后）。

8 避免使用DISTINCT

• 问题
  ：DISTINCT会对结果集进行去重，可能导致额外的排序和扫描操作。
• 优化
  ：如果可能，通过优化查询逻辑来避免使用DISTINCT

9 使用UNION ALL代替UNION

• 问题
  ：UNION会对结果集进行去重，导致额外的开销。
• 优化
  ：如果不需要去重，使用UNION ALL。

10 分页优化

• 问题
  ：使用LIMIT和OFFSET进行分页时，OFFSET值越大，查询越慢。
• 优化
  ：使用基于游标的分页（如WHERE id > last_id）来替代OFFSET。

11 优化Group By语句

• 优化1：如果对group by语句的结果没有排序要求，要在语句后面加 order by null（group 默认会排序）；
• 优化2：尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort；
• 优化3：使用where子句替换Having子句：避免使用having子句，having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过where子句提前过滤查询的数目，就可以减少这方面的开销。
• 如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；
• 如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法（直接用磁盘临时表）得到group by的结果。

12 使用EXPLAIN分析查询

• 问题
  ：无法直观了解查询的执行计划。
• 优化
  ：使用EXPLAIN或EXPLAIN ANALYZE查看查询的执行计划，找出瓶颈。

二、索引优化

• 创建合适的索引
  ：确保经常查询的列有索引，但不要过度索引，因为索引会增加写操作的开销。
• 复合索引
  ：对于多列查询，使用复合索引（组合索引）来提高查询效率。
• 索引选择性
  ：高选择性的列（如唯一值较多的列）更适合建立索引。
• 删除不必要的索引
  ：未使用或很少使用的索引会增加写操作的开销。

三、数据库设计优化

• 范式化与反范式化
  ：适当的反范式化可以减少JOIN操作，提高查询性能。
• 分区表
  ：对于大表，使用分区表可以提高查询效率，减少扫描的数据量。
• 数据类型优化
  ：使用合适的数据类型，避免使用过大的数据类型。

四、执行计划分析

• 查看执行计划
  ：使用EXPLAIN或EXPLAIN ANALYZE查看查询的执行计划，找出瓶颈。
• 优化JOIN顺序
  ：确保JOIN的顺序是最优的，减少中间结果集的大小。
• 避免全表扫描
  ：尽量通过索引来避免全表扫描。

五、硬件和配置优化

• 增加内存
  ：增加数据库服务器的内存，特别是缓冲池（Buffer Pool）的大小。
• 优化磁盘I/O
  ：使用SSD或RAID来提高磁盘I/O性能。
• 调整数据库参数
  ：根据工作负载调整数据库的配置参数，如innodb_buffer_pool_size、query_cache_size等。

六、并发和锁优化

• 减少锁竞争
  ：避免长时间的事务和锁，减少锁等待时间。
• 使用读写分离
  ：对于读多写少的场景，使用主从复制和读写分离来分担负载。

七、缓存优化

• 使用查询缓存
  ：如果查询结果不经常变化，可以使用查询缓存。
• 应用层缓存
  ：在应用层使用缓存（如Redis、Memcached）来减少数据库查询。

八、定期维护

• 优化表和索引
  ：定期使用OPTIMIZE TABLE或ANALYZE TABLE来优化表和索引。
• 清理无用数据
  ：定期清理无用数据，减少表的大小。

九、分库分表

• 水平分表
  ：对于非常大的表，考虑将其分成多个小表。
• 垂直分表
  ：将不常用的列拆分到其他表中，减少单表的大小。

十、使用数据库工具

• 性能监控工具
  ：使用数据库自带的性能监控工具（如MySQL的Performance Schema）来监控和分析查询性能。
• 慢查询日志
  ：启用慢查询日志，找出执行时间较长的查询。

最后、总结

SQL查询性能调优需要从多个角度入手，包括查询语句、索引、数据库设计、硬件配置等。通过逐步排查和优化，可以显著提高查询性能。当然，最可控的还是规范化编写sql脚本的形式，了解熟悉常见的sql查询语句编写的优化应对之策。