如何优化慢SQL？索引失效的常见场景有哪些？

以下是一些优化慢SQL的方法以及索引失效的常见场景：

优化慢SQL的方法

• 索引优化
  • 分析查询语句：通过数据库的查询分析工具，如MySQL的EXPLAIN命令，查看查询执行计划，了解数据库如何使用索引来执行查询，确定是否存在索引未被使用或使用不当的情况。
  • 添加必要索引：根据查询语句中经常用于过滤、连接和排序的列，添加合适的索引。但要注意索引并不是越多越好，过多的索引会增加数据插入、更新和删除的开销。
  • 覆盖索引：尽量使用覆盖索引，即查询所需的所有列都包含在索引中，这样数据库可以直接从索引中获取数据，而无需回表查询，提高查询效率。
  • 操作示例：

    -- 1) 看执行计划EXPLAINSELECTuser_id,status,created_atFROMordersWHEREuser_id=123ANDstatus=1ORDERBYcreated_atDESCLIMIT20;-- 2) 按过滤 + 排序列建立联合索引CREATEINDEXidx_orders_user_status_createdONorders(user_id,status,created_at);-- 3) 覆盖索引：只查索引里的列，减少回表EXPLAINSELECTuser_id,status,created_atFROMordersWHEREuser_id=123ANDstatus=1ORDERBYcreated_atDESCLIMIT20;

• 查询语句优化
  • 简化查询逻辑：避免复杂的子查询、嵌套查询和不必要的连接操作。可以尝试将复杂查询分解为多个简单的查询，以提高查询性能。
  • 合理使用连接条件：在使用连接操作时，确保连接条件使用了正确的列和合适的数据类型，并在连接列上建立了索引。
  • 避免全表扫描：尽量使用有选择性的条件来过滤数据，避免使用导致全表扫描的查询条件，如在查询条件中使用了函数或表达式，可能会使索引失效。
  • 操作示例：

    -- 1) 子查询改 JOIN（示例）EXPLAINSELECTu.id,u.nameFROMusers uJOINorders oONo.user_id=u.idWHEREo.status=1;-- 2) 避免在索引列上做函数计算：用范围条件替代EXPLAINSELECT*FROMusersWHEREcreated_at>='2024-01-01'ANDcreated_at<'2025-01-01';

• 数据库配置优化
  • 调整缓存参数：根据服务器的硬件资源和数据库的使用情况，合理调整数据库的缓存参数，如InnoDB缓冲池大小等，以提高数据的缓存命中率，减少磁盘I/O。
  • 优化数据库引擎参数：针对不同的数据库引擎，调整相关的性能参数，如MySQL的查询缓存、线程池大小等，以适应具体的业务需求和硬件环境。
  • 操作示例：

    -- 查看 InnoDB 缓冲池大小（单位：bytes）SHOWVARIABLESLIKE'innodb_buffer_pool_size';-- 开启慢查询日志并设置阈值（示例：生产环境请评估影响）SETGLOBALslow_query_log='ON';SETGLOBALlong_query_time=0.5;

• 数据结构优化
  • 范式化与反范式化：根据业务需求和查询特点，合理设计数据库的表结构。在某些情况下，适当的反范式化可以减少连接操作，提高查询性能，但要注意数据冗余和一致性问题。
  • 分区分表：对于数据量较大的表，可以考虑进行分区分表操作，将数据分散存储在不同的物理文件或服务器上，以提高查询和数据管理的效率。
  • 操作示例：

    -- 按时间范围分区（示例：按月）CREATETABLEorders_p(idBIGINTPRIMARYKEY,created_atDATETIMENOTNULL,user_idBIGINTNOTNULL,amountDECIMAL(10,2)NOTNULL,KEYidx_user_created(user_id,created_at))PARTITIONBYRANGE(TO_DAYS(created_at))(PARTITIONp202501VALUESLESS THAN(TO_DAYS('2025-02-01')),PARTITIONpmaxVALUESLESS THAN MAXVALUE);

索引失效的常见场景

• 数据类型不匹配
  • 列类型与查询值类型不一致：如果列定义为字符串类型，而在查询中使用数字作为条件，可能导致索引失效。例如在MySQL中，将一个VARCHAR类型的列与一个INT类型的值进行比较。
  • 操作示例：

    -- phone 是 VARCHAR 且有索引EXPLAINSELECT*FROMusersWHEREphone=13800138000;EXPLAINSELECT*FROMusersWHEREphone='13800138000';

• 使用函数或表达式
  • 在索引列上使用函数：在查询条件的索引列上使用函数，会使索引失效。例如在MySQL中，SELECT * FROM users WHERE YEAR(created_at) = 2024;，这里对created_at列使用了YEAR函数。
  • 表达式计算：如果查询条件中对索引列进行了表达式计算，索引可能无法使用。如SELECT * FROM orders WHERE order_amount * 2 > 1000;，对order_amount列进行了乘法运算。
  • 操作示例：

    -- created_at 有索引EXPLAINSELECT*FROMusersWHEREYEAR(created_at)=2024;EXPLAINSELECT*FROMusersWHEREcreated_at>='2024-01-01'ANDcreated_at<'2025-01-01';-- order_amount 有索引EXPLAINSELECT*FROMordersWHEREorder_amount*2>1000;EXPLAINSELECT*FROMordersWHEREorder_amount>500;

• 查询条件使用不当
  • 使用不等于操作：使用不等于（<>或!=）操作符时，索引可能无法有效利用。因为数据库需要扫描大量的数据来确定不满足条件的记录。
  • 使用OR连接条件：当OR连接的多个条件中只有部分列有索引时，可能导致索引失效。例如SELECT * FROM products WHERE product_id = 100 OR product_name = 'Apple';，如果product_name列没有索引。
  • 范围查询后的列：在一个查询中，如果有多个条件，范围查询之后的列上的索引可能失效。如SELECT * FROM users WHERE age > 30 AND name = 'John';，如果name列在age列之后进行范围查询，name列的索引可能不被使用。
  • 操作示例：

    -- 不等于通常选择性较差EXPLAINSELECT*FROMtWHEREstatus!=1;-- OR：两边都要可用索引，否则容易回退EXPLAINSELECT*FROMproductsWHEREproduct_id=100ORproduct_name='Apple';-- 联合索引 (age, name)：范围条件会影响后续列利用EXPLAINSELECT*FROMusersWHEREage>30ANDname='John';EXPLAINSELECT*FROMusersWHEREage=31ANDname='John';

• 数据分布不均匀
  • 列数据倾斜：如果索引列中的数据分布非常不均匀，大量数据集中在少数几个值上，数据库可能会选择不使用索引，而采用全表扫描。
  • 操作示例：

    -- is_deleted 只有 0/1，选择性差；通常用组合索引提升过滤效果EXPLAINSELECT*FROMordersWHEREis_deleted=0ANDuser_id=123;

• 其他情况
  • 表结构变更：在对表进行结构变更操作后，如添加或删除列、修改列的数据类型等，可能会导致索引失效或性能下降，需要及时重建或优化索引。
  • 存储引擎限制：不同的存储引擎对索引的支持和使用方式可能有所不同。某些存储引擎可能在特定情况下无法有效地使用索引，如MyISAM存储引擎在处理全文搜索时，与InnoDB存储引擎的索引使用方式和性能就有所差异。
  • 操作示例：

    ALTERTABLEusersMODIFYCOLUMNphoneVARCHAR(32)NOTNULL;ANALYZETABLEusers;OPTIMIZETABLEusers;