SQL查询优化技巧？面试高频

▌ 技术引导
SQL查询优化是数据库性能调优最直接有效的手段之一，特别是在处理千万级数据时，千万不能盲目写JOIN或子查询。我见过太多项目因为查询没优化导致CPU飙升、GC频繁，最终拖垮整个服务。真实场景中，慢查询往往不是因为索引缺失，而是因为查询逻辑错误，比如全表扫描、临时表溢出、数据类型不匹配。直接使用EXPLAIN分析执行计划是最基础却最致命的步骤，很多人根本不去看，导致性能问题长期存在。高性能查询的核心是减少数据扫描量和减少CPU开销，这两点在实际中必须同时关注。索引虽然重要，但比例不合理的索引反而会带来写入压力。查询重写、分区表、缓存策略、连接方式选择、避免SELECT 等都是真实踩过的坑，必须记录。

真实项目中，全连接的使用要慎之又慎，尤其是当两边数据量悬殊时，完全可能把数据库打成狗。我见过有团队把LEFT JOIN改成INNER JOIN后，性能提升了3倍，甚至有人尝试用STRAIGHT_JOIN强制顺序，结果反而更糟。索引的使用不是万能，尤其在LIKE模糊查询中，前缀索引才是王道。临时表的使用要抱着“能不建就别建”的态度，除非数据量极大，否则临时表可能变成了性能黑洞。缓存策略要和业务场景融合，比如热点数据写入Redis，冷数据用本地缓存，但不要过度依赖，否则一旦数据变化，缓存失效反而会更慢。

查询优化要关注执行计划中的rows和type，type是ALL就注定是灾难。索引失效的常见场景包括函数运算、前导模糊查询、索引列参与计算、类型转换等，这些在真实场景中都踩过。有时候，调整JOIN顺序反而比优化索引更有效，尤其是当左表数据量远大于右表时。查询重写技巧中，将子查询转换为JOIN通常能带来性能提升，但需要配合合适的索引。另外，使用覆盖索引避免回表也是一种常见手段，但必须确保查询字段全部包含在索引中，否则索引反而成了负担。

性能影响上，合理优化能将查询时间从秒级压缩到毫秒级，但过度优化也可能导致写入延迟，这需要根据业务需求权衡。比如，电商系统中，订单查询必须快，但库存更新不能影响其他操作。实际中，我见过用分区表把单表查询时间从15秒降到2秒，但分区字段选择不当会导致写入变慢，甚至数据分布不均。替代方案包括使用物化视图、预计算、异步处理等，但这些都需要业务支持，不是所有场景都适用。

我见过最离谱的是有人在WHERE条件中使用函数，比如WHERE YEAR(create_time) = 2024，这直接导致索引失效，明明有create_time的索引却用不上。使用EXPLAIN时，重点看Extra列是否出现Using temporary或Using filesort，这两个字段意味着性能问题。真实案例中，把SELECT 改成只选必要字段，结合合适的索引，查询效率直接提升5倍以上。优化查询不是一次性的，需要持续监控和调整，特别是在数据量增长时，查询计划很容易变化。

▌ 技术参考
一 查询重写技巧
在实际场景中，频繁使用子查询会导致执行计划复杂化，影响性能。比如，将子查询转换为JOIN通常能带来显著提升。真实案例中，把SELECT FROM logs WHERE id IN (SELECT log_id FROM users)改成JOIN方式后，查询时间从20秒降到5秒。JOIN的顺序也很重要，尽量让数据量小的表作为驱动表。此外，避免在WHERE子句中对字段进行函数操作，比如WHERE YEAR(create_time) = 2024，这直接导致索引失效。可以使用函数索引或转换查询逻辑，例如将create_time >= '2024-01-01' AND create_time < '2025-01-01'，这样索引就能发挥作用。

二 索引设计与使用
索引是查询优化的核心，但使用不当反而会拖后腿。在真实项目中，我见过有人在经常查询的字段上建立单列索引，结果查询时却使用了全表扫描，原因在于查询条件涉及多个字段。这时候，联合索引更合适，但必须遵循最左前缀原则。比如，查询WHERE a=1 AND b=2 AND c=3，联合索引(a,b,c)会生效，但如果是WHERE b=2 AND a=1，则不会。索引选择性是关键，选择性越高的字段越适合作为索引。例如，status字段如果有很多重复值，建立索引的意义不大。在MySQL中，可以使用SHOW INDEX FROM table_name查看索引信息。

三 避免SELECT 和字段选择
SELECT 是性能杀手，我见过有团队因为这个习惯，把查询时间拖到几十秒。在真实项目中，将SELECT 改为只选必要字段，配合覆盖索引，查询效率直接提升5倍以上。比如，一个订单查询只需要order_id、user_id、amount和create_time，建立联合索引(order_id, user_id, amount, create_time)后，不需要回表，查询速度大幅提升。此外，避免在查询中使用复杂的表达式或函数，如DATE_FORMAT，这会导致索引失效。真实场景中，使用字段别名能够降低CPU计算压力，例如SELECT id AS order_id FROM orders。

四 覆盖索引与回表优化
覆盖索引能够避免回表，提升查询速度。在真实项目中，将查询字段全部包含在索引中，可以大幅减少磁盘IO。例如，索引建立在(order_id, user_id, amount, create_time)上，查询SELECT order_id, user_id, amount FROM orders WHERE create_time > '2024-01-01'，无需回表，性能提升明显。但覆盖索引的代价是索引文件变大，写入性能下降，需要根据业务需求权衡。此外，避免在索引列上使用OR，这可能导致索引失效。可以将OR条件转换为UNION ALL，分别建立索引再查询，例如SELECT FROM logs WHERE a = 1 OR a = 2，改为两个查询，使用索引a。

五 避免全表扫描与临时表
全表扫描是性能噩梦，我见过有项目因为全表扫描导致数据库负载过高，甚至宕机。在真实场景中，使用EXPLAIN查看执行计划，type为ALL就说明需要索引。临时表的使用要谨慎，尤其是当数据量极大时，临时表可能变成性能黑洞。在MySQL中，避免使用临时表的策略包括优化查询逻辑、合理使用索引、减少不必要的计算。例如，在子查询中使用临时表，加载到内存后查询速度反而更慢，换成JOIN方式性能提升3倍以上。

六 慎用JOIN的类型与顺序
JOIN的类型和顺序直接影响性能。在真实项目中，使用INNER JOIN比LEFT JOIN更快，因为LEFT JOIN需要处理NULL值。JOIN的顺序也至关重要，尽量让数据量小的表作为驱动表，这样可以减少后续扫描的数据量。例如，将大表作为被驱动表，小表作为驱动表，JOIN的效率会更好。此外，避免使用STRAIGHT_JOIN，除非你绝对确定表顺序，否则可能导致执行计划错误。在PostgreSQL中，使用JOIN的顺序优化，比如将过滤条件放在JOIN条件中，能显著减少数据扫描量。

七 模糊查询与索引失效
LIKE 'abc%' 时索引有效，但LIKE '%abc' 时索引失效。在真实项目中，处理前导模糊查询的方案包括使用全文索引或倒序索引。例如，建立倒序索引，将字段存储为reverse，查询时使用reverse('abc%')，这样索引依然可用。此外，使用正则表达式可能会导致索引失效，需要将查询转换为LIKE形式。在MySQL中，可以通过ALTER TABLE ADD INDEX (reverse_column)来实现。

八 优化连接方式与性能对比
JOIN有多种方式，包括NATURAL JOIN、INNER JOIN、LEFT JOIN等。在真实场景中，INNER JOIN比LEFT JOIN更快，因为LEFT JOIN需要处理NULL值。使用STRAIGHT_JOIN强制连接顺序在某些场景有效，但要慎用。例如，在一个订单与用户关联的场景中，INNER JOIN比LEFT JOIN快2倍以上，但需要确保用户表中存在对应的订单ID。此外，使用JOIN代替子查询通常能提高性能，尤其是在数据量大的时候。

九 避免使用函数和隐式类型转换
函数运算会导致索引失效，比如WHERE YEAR(create_time) = 2024，此时索引无法使用。真实案例中，将查询条件改为WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'，索引就能正常生效。隐式类型转换同样会导致索引失效，例如VARCHAR字段与整数比较，MySQL会自动转换类型，但索引无法使用。在真实项目中，需要确保查询字段与数据库字段类型一致，否则索引可能完全失效。

十 使用查询缓存与热点数据
查询缓存在MySQL 8.0中已被移除，但在之前的版本中，合理使用能提升性能。真实项目中，将频繁读取的数据放入Redis或本地缓存，比如查询用户基本信息，缓存命中率在90%以上时，查询速度从1秒下降到50毫秒。但缓存需要与数据库同步，否则会导致数据不一致。在MongoDB中，可以使用hint来强制使用特定索引，但需要谨慎，因为错误的索引可能导致性能下降。

十一 分区表与数据分片
分区表能有效提升大数据量下的查询性能，尤其适用于时间范围查询。在真实项目中，使用范围分区，将数据按create_time分片，查询时只需要访问相应分区，减少扫描量。例如，将orders表按create_time分区，查询2024年的数据只需扫描对应分区，性能提升3倍以上。但分区表需要考虑数据写入和维护的复杂性，不适用于频繁更新的场景。在Cassandra中，分片策略已经内置，但需要合理设计主键。

十二 避免全排序与避免文件排序
文件排序是性能瓶颈，我见过有项目因为文件排序导致查询时间增加到分钟级。在真实场景中，使用索引覆盖排序字段，比如WHERE a = 1 ORDER BY b，此时索引(b,a)就能避免文件排序。如果排序字段不在索引中，MySQL会使用文件排序，效率低下。在PostgreSQL中，可以通过SET LOCAL statement_timeout='60s'来限制排序时间，防止超时。

十三 优化WHERE条件与过滤顺序
WHERE条件的顺序会影响索引使用效率，尽量把条件性更强的字段放在前面。在真实项目中，将WHERE条件改为WHERE a = 1 AND b = 2，而不是WHERE b = 2 AND a = 1，这样查询性能提升10%以上。避免在WHERE条件中使用OR，除非能通过UNION ALL替代。例如，WHERE a=1 OR a=2 改为两个查询，使用索引a，性能更好。

十四 使用索引提示与JOIN缓冲区
在MySQL中，可以通过FORCE INDEX来提示使用特定索引，但要确保索引存在且有效。例如，SELECT FROM orders FORCE INDEX (idx_status) WHERE status = 'paid'，这样能强制使用status的索引。JOIN缓冲区的大小可以通过JOIN_BUFFER_SIZE调整，合理设置能提升JOIN效率。在真实项目中，将JOIN缓冲区调大至1MB，查询速度提升20%以上。

十五 避免不必要的事务与锁
事务和锁会显著影响查询性能，尤其是在高并发场景下。在真实项目中，将查询拆分为多个无事务的语句，避免长时间持有锁。例如，将SELECT操作放在事务外执行，减少对其他操作的阻塞。在PostgreSQL中，使用SET LOCAL lock_timeout='1000'可以限制锁等待时间，避免查询长时间挂起。

十六 调整查询计划与执行过程
在真实项目中，EXPLAIN输出中的rows和type是关键指标，type为ALL表示全表扫描，必须建立索引。使用SHOW PROFILES查看查询执行时间，配合SHOW PROFILE CPU, BLOCK IO来分析瓶颈。在MySQL中，可以使用optimizer_switch参数调整查询优化策略，比如设置derivation=1让优化器更激进。

十七 索引合并与多索引使用
索引合并在某些情况下能提升性能，比如WHERE a=1 OR b=2。在真实项目中，这需要MySQL支持，且查询字段必须包含在索引中。索引合并的代价是查询计划复杂，容易误判。因此，优先使用联合索引，而非多个单列索引。在PostgreSQL中，索引合并不被支持，需要将OR条件转换为UNION ALL。

十八 使用缓存工具与性能监控
使用Redis或本地缓存能减少查询压力。在真实项目中，将查询结果缓存到Redis，并设置TTL为1小时，命中率在95%以上的情况下，查询时间从1秒降到100毫秒。性能监控工具如Prometheus+Grafana能实时展示查询性能，帮助快速定位问题。在MySQL中，使用SHOW ENGINE INNODB STATUS可以查看锁等待和查询状态。

十九 避免全扫描和优化查询逻辑
在真实场景中，避免使用全扫描的查询方式，比如WHERE 1=1，这会导致执行计划错误。使用WHERE id > 0 AND id < 1000，配合索引id，能提升查询效率。此外，优化查询逻辑，比如将WHERE a = 1 AND b = 2 改为WHERE a = 1 AND b = 2，减少不必要的计算。

二十 索引失效场景与解决方案
在真实项目中，使用函数索引或覆盖索引是解决索引失效的常见方案。例如，在MySQL中使用ALTER TABLE orders ADD INDEX idx_create_time (create_time) ONLINE，建立索引后，查询create_time的条件就能使用索引。避免在索引列上进行类型转换，如将VARCHAR与整数比较，可以改为使用CAST或修改字段类型。在PostgreSQL中，使用GIN索引处理文本搜索，提升查询效率。