sql调优总结

sql调优
线上发现部分sql查询时间过长。使用explain观察是否命中表的索引。未命中索引，使用 TABLE add index 语句添加索引。
除此之外，单个字段命中联合索引的情况也会导致查询变慢

针对多个字段的查询可添加联合索引。
总结如下慢sql的原因：
一、查询语句本身的问题
查询语句的编写是否高效，直接影响执行效率，常见问题包括：

1. 未使用索引，或条件无法命中索引时
   • 当查询未使用索引，或条件无法命中索引时，数据库会遍历整个表的所有行，尤其对于大表（百万级以上数据），耗时会急剧增加。
   • 例：SELECT * FROM orders WHERE amount > 1000; 若amount字段无索引，会触发全表扫描。
2. 使用SELECT *查询所有列
3. 复杂的联表查询（JOIN）
   • 多表联表时未指定有效的关联条件（如ON子句缺失或不合理），导致产生笛卡尔积（行数呈指数级增长）。
   • 联表顺序不合理：数据库优化器可能因表大小或统计信息不准确，选择低效的联表顺序（如小表驱动大表更高效，反之则耗时）。
4. 子查询嵌套过深
   • 多层嵌套的子查询（尤其是IN子句中的子查询）可能被数据库多次执行，而非一次性优化，导致重复计算。
   • 例：SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status=1); 若子查询返回大量数据，效率会很低。同理避免in（）中有大量数据。
5. 排序和分组操作低效
   • ORDER BY、GROUP BY、DISTINCT等操作需要对数据进行排序或聚合，若涉及数据量过大且无索引支持，会触发临时表或文件排序（磁盘IO耗时远高于内存）。
     二、索引相关问题
     索引是提升查询效率的核心，但不合理的索引设计或使用会适得其反：
6. 缺少必要的索引
   • 查询的过滤条件（WHERE）、联表条件（JOIN ON）、排序字段（ORDER BY）等未建立索引，导致全表扫描或低效扫描。
7. 索引失效
   • 索引字段被函数或表达式操作：如WHERE SUBSTR(name, 1, 1) = 'A'，会使索引失效。
   • 使用不等号（!=、<>）、NOT IN、IS NULL（部分数据库）、模糊查询前缀带%（如LIKE '%abc'），可能导致索引失效。
   • 联合索引未遵循“最左前缀原则”：如联合索引(a, b, c)，查询条件仅用b或c时，无法命中索引。
8. 索引过多
   • 表上索引数量过多，会导致INSERT、UPDATE、DELETE操作变慢（因为每次修改数据需同步更新索引），同时索引本身会占用磁盘空间，增加查询时的索引选择成本。
9. 索引碎片化
   • 频繁的更新或删除操作会导致索引页碎片化（存储空间不连续），查询时需要多次读取磁盘，降低效率。
     三、数据库设计问题
     不合理的表结构或数据分布会长期影响查询性能：
10. 表结构设计不合理
    • 表字段过多（宽表）或字段类型不合适（如用VARCHAR(255)存储手机号，而非CHAR(11)），导致单条记录体积过大，扫描时IO成本高。
    • 未进行分表分库：大表（如千万级以上数据）未按时间、地区等维度拆分，单表数据量过大，查询耗时自然增加。
11. 数据分布不均
    • 表中存在“热点数据”（如某类记录占比90%以上），即使有索引，查询这类数据时仍需扫描大量索引页（类似全表扫描）。
    • 例：WHERE status=0，若90%的记录status都是0，索引可能失效，转为全表扫描。
12. 缺少分区表设计
    • 对于时间序列数据（如日志、订单），未按时间分区（如按月份），查询历史数据时仍需扫描全表，而分区表可仅扫描目标分区。
      四、数据库配置与状态问题
      数据库的运行状态和配置参数也会影响查询效率：
13. 统计信息过时
    • 数据库优化器依赖表的统计信息（如行数、字段分布、索引基数）生成执行计划，若统计信息过时（如长期未更新），可能选择低效计划（如错误地走全表扫描而非索引）。
    • 解决：定期执行ANALYZE TABLE（MySQL）或UPDATE STATISTICS（SQL Server）更新统计信息。
14. 连接数或资源限制
    • 数据库连接数已满，查询需等待释放连接，导致“排队耗时”。
    • 内存、CPU资源不足：查询需要的内存（如排序缓存、连接缓存）被耗尽，被迫使用磁盘临时表，或CPU被其他进程占用，处理速度下降。
15. 锁等待或事务阻塞
    • 若查询涉及的表或行被其他事务锁定（如SELECT ... FOR UPDATE未及时提交），当前查询会进入等待状态，直到锁释放，表现为“查询超时”。
      五、硬件与环境问题
      底层硬件或部署环境的瓶颈也可能导致查询缓慢：
16. 磁盘IO性能不足
    • 机械硬盘（HDD）读写速度远低于固态硬盘（SSD），若数据库文件存储在HDD，且存在大量随机IO（如索引扫描），会成为瓶颈。
    • 磁盘空间不足，导致临时文件读写效率下降。
17. 内存不足
    • 数据库缓存（如MySQL的InnoDB Buffer Pool、Oracle的SGA）不足，频繁将数据从磁盘加载到内存，增加IO次数。
      总结
      SQL查询慢的原因可归纳为：查询不优、索引不当、表设计不合理、数据库状态异常、硬件资源不足。排查时可通过执行计划（如EXPLAIN命令）分析查询路径，结合数据库监控工具（如MySQL的SHOW PROCESSLIST、SQL Server的 Profiler）定位具体瓶颈，再针对性优化（如加索引、改写查询、分表分库等）。