如何优化一条SQL语句的性能？

优化SQL语句性能的核心步骤包括定位问题、调整索引与查询结构、优化执行顺序及长期监控维护，具体方法如下：

• 通过EXPLAIN查看执行计划：识别全表扫描（type=ALL）、文件排序（Using filesort）、索引失效等关键问题。例如，原始SQL中orders、users、products三表均为全表扫描，且未利用索引。
• 诊断常见问题：

  全表扫描：表数据量大时性能极差。

  文件排序：内存不足时需临时文件，耗时且可能磁盘I/O瓶颈。

  索引失效：如JSON字段索引、OR条件、函数操作列等导致索引无法使用。

• 创建有效索引：

  单列索引：针对高频过滤条件（如vip_level）创建单列索引。

  组合索引：覆盖查询条件，遵循最左前缀原则。例如：

  ALTER TABLE orders ADD INDEX idx_create_user (create_time, user_id);

  避免无效索引：如JSON字段直接索引（MySQL 8.0以下无效），应改用实体列。

• 强制索引使用：通过FORCE INDEX指定索引，避免优化器选择错误索引。SELECT /*+ INDEX(o idx_create_user) */ o.id, o.amount FROM orders o FORCE INDEX (idx_create_user)WHERE o.create_time > '2023-01-01';
• 覆盖索引：索引包含查询所需全部字段，避免回表。例如：ALTER TABLE orders ADD INDEX idx_cover (create_time, user_id, amount);

• 调整JOIN顺序：用小表驱动大表，减少中间结果集。例如：SELECT o.* FROM products p INNER JOIN ( SELECT o.id, o.amount, o.create_time FROM orders o WHERE o.create_time > '2023-01-01') o ON p.id = o.product_id INNER JOIN ( SELECT id FROM users WHERE vip_level > 3) u ON o.user_id = u.id WHERE p.category_id IN (5,8)ORDER BY o.amount DESCLIMIT 1000,20;
• 减少数据传输：避免SELECT *，仅查询必要字段。
• 优化分页查询：使用子查询或衍生表减少排序数据量。例如：SELECT * FROM ( SELECT id, amount FROM orders WHERE create_time > '2023-01-01' ORDER BY amount DESC LIMIT 1020) tmp ORDER BY amount DESCLIMIT 1000,20;

• 警惕OR条件：可能导致索引失效，改用UNION ALL或拆分查询。
• 慎用CTE表达式：复杂CTE可能影响性能，需测试验证。
• 参数化查询：防止SQL注入且优化执行计划复用。例如：@Select("SELECT * FROM orders WHERE create_time > #{time}")List<Order> findByTime(@Param("time") Date time);

• 索引监控：

  查看索引使用情况：

  SHOW INDEX FROM orders;SELECT object_schema, object_name, index_name, count_read, count_fetch FROM performance_schema.table_io_waits_summary_by_index_usageWHERE index_name IS NOT NULL;

  删除未使用索引：减少写操作开销。

• 慢查询日志：开启并分析慢查询，定位优化重点。
• 自动化工具：接入SQL审核平台（如Yearning）、MyBatis拦截器等，预防低效SQL。

• 分库分表：对超大表按时间或用户ID分片。例如：spring.shardingsphere.rules.sharding.tables.orders.actual-data-nodes=ds$0..1.orders_$->{2020..2023}spring.shardingsphere.rules.sharding.tables.orders.table-strategy.standard.sharding-column=create_time
• 冷热分离：历史数据归档至低成本存储（如OSS）。
• 缓存与换库：高频查询加缓存（Redis），极端场景考虑更换数据库（如HBase）。

总结：SQL优化需结合执行计划分析、索引调整、查询重构及长期监控，遵循“定位-切割-重建”三板斧，同时避免过度依赖索引，根据业务场景选择合适方案。