优化索引和 SQL 语句

优化索引和 SQL 语句是数据库性能调优的核心工作。它们相辅相成，好的索引能让 SQL 跑得更快，而优化的 SQL 语句又能更好地利用索引，甚至避免索引的滥用。

一、 索引优化

索引是提高查询速度最有效的方式之一，但并非越多越好，也不是盲目创建。

1. 索引设计原则

  • 选择性高的列： 索引列的值越分散，选择性越高，索引效果越好。例如，唯一 ID、用户名的列就非常适合建索引。而性别、状态等只有少数几个值的列，选择性很低，通常不适合单独建索引，除非是联合索引的一部分。

  • WHERE 子句中的列： 经常出现在 WHERE 子句中作为查询条件的列。

  • JOIN 子句中的列： 在多表连接 (JOIN) 操作中作为连接条件的列。

  • ORDER BY 和 GROUP BY 子句中的列： 如果查询结果需要排序或分组，在这些列上创建索引可以避免额外的排序操作（Using filesort）。

  • 覆盖索引 (Covering Index)： 如果一个查询所需的所有列都包含在某个索引中（或者说是该索引的一部分），那么数据库可以直接从索引中获取数据，而无需访问实际的数据行（回表），这会大大提高查询效率。

    • 示例： SELECT name, email FROM users WHERE id = 123; 如果在 id 和 (name, email) 上有一个复合索引，那么这个查询就是覆盖索引。

  • 避免在频繁更新的列上创建过多索引： 每次更新索引列，都需要同时更新索引结构，这会增加写操作的开销。

  • 复合索引 (联合索引) 的最左前缀原则：

    • 对于复合索引 (col1, col2, col3)：

      • WHERE col1 = ? 可以使用索引。
      • WHERE col1 = ? AND col2 = ? 可以使用索引。
      • WHERE col1 = ? AND col2 = ? AND col3 = ? 可以使用索引。
      • WHERE col1 = ? AND col3 = ? 只能使用 col1 部分的索引。
      • WHERE col2 = ? 或 WHERE col3 = ? 不能使用该索引。

    • 因此，在设计复合索引时，将最常用作查询条件的列放在前面。

2. 索引的创建和管理

  • 创建索引：

    • CREATE INDEX index_name ON table_name (column1, column2); (普通索引)
    • CREATE UNIQUE INDEX index_name ON table_name (column_name); (唯一索引)
    • ALTER TABLE table_name ADD INDEX index_name (column_name);
    • ALTER TABLE table_name ADD PRIMARY KEY (column_name); (主键索引)

  • 删除不必要的索引：

    • DROP INDEX index_name ON table_name;
    • ALTER TABLE table_name DROP INDEX index_name;
    • 考虑删除那些从未被使用或使用频率极低、或者可以通过其他更优索引覆盖的索引。过多的索引会降低写入性能，并占用存储空间。

  • 重建索引：

    • 在数据大量增删改后，索引可能会出现碎片化，影响查询效率。定期重建索引可以优化其结构。
    • MySQL/InnoDB: 通常不需要手动重建，InnoDB 会自动管理。但对于某些场景（如全文索引、B-tree索引统计信息过期），可能需要 ANALYZE TABLE 或 OPTIMIZE TABLE。
    • PostgreSQL: REINDEX TABLE table_name; 或 REINDEX INDEX index_name;。
    • SQL Server: 定期维护计划中包含索引重组或重建。

3. 避免索引失效的常见情况

  • 在索引列上使用函数： WHERE YEAR(date_col) = 2023 (会使 date_col 上的索引失效)。

    • 优化： 转换为范围查询 WHERE date_col >= '2023-01-01' AND date_col < '2024-01-01'。

  • 类型转换： 如果列是字符串类型，但在查询时用了数字，或反之。WHERE string_col = 123。

    • 优化： 确保类型匹配。

  • 模糊查询以 % 开头： WHERE name LIKE '%john%' (索引失效)。WHERE name LIKE 'john%' 可以使用索引。

    • 优化： 尽可能使用前缀匹配。如果必须使用 '%john%'，考虑使用全文索引或 ElasticSearch 等外部搜索方案。

  • 使用 OR 连接的条件： WHERE col1 = 1 OR col2 = 2 (如果 col1 和 col2 没有分别建立索引，或不是联合索引的最左前缀，可能导致索引失效)。

    • 优化： 如果条件少，可以考虑使用 UNION ALL 分别查询再合并。

  • 使用 NOT IN 或 != (或 <>)： 这些操作符可能导致索引失效，尤其是当匹配结果集很大时。

    • 优化： 如果可能，考虑使用 EXISTS 或将 != 转换为 > 和 < 的组合。

  • 空值判断： WHERE col IS NULL 或 WHERE col IS NOT NULL。取决于数据库和索引类型，某些情况下索引可能不起作用。

    • 优化： 可以考虑为这些列添加默认值或使用特殊的索引。

二、 SQL 语句优化

SQL 语句优化旨在编写更高效、更易读的查询，从而最大限度地利用数据库资源。

1. 选择必要的列

  • 避免 SELECT *： 只选择你实际需要的列。

    • 好处：

      • 减少数据传输量。
      • 减少数据库服务器和客户端之间的网络I/O。
      • 减轻数据库服务器的CPU和内存负担。
      • 提高覆盖索引的可能性。

    • 示例： SELECT id, name FROM users WHERE age > 30; 而不是 SELECT * FROM users WHERE age > 30;

2. 优化 JOIN 操作

  • 确保连接列有索引： 这是 JOIN 性能的关键。

  • 选择合适的连接类型： INNER JOIN, LEFT JOIN, RIGHT JOIN 等。理解它们之间的区别，并选择能满足需求的最小连接类型。

  • 小表驱动大表（MySQL/PostgreSQL等优化器通常会自动选择最佳顺序，但了解概念有助于理解）： 在多表 JOIN 时，优化器通常会选择小结果集的表先进行处理。

    • 例如：SELECT * FROM large_table lt JOIN small_table st ON lt.id = st.id; 数据库会先处理 small_table。

  • 避免不必要的 JOIN： 如果可以通过子查询或预先计算来避免 JOIN，且不影响性能，可以考虑。

3. 优化 WHERE 子句

  • 利用索引： 确保条件能够使用到索引（见索引优化部分）。

  • 将筛选范围小的条件放在前面： 虽然优化器会重新排序，但好的习惯有助于理解和某些情况下的性能。

  • 避免隐式转换。

  • 善用 EXISTS / NOT EXISTS vs IN / NOT IN：

    • 当子查询返回的结果集较小（如几十、几百条）时，IN 通常比 EXISTS 效率高。
    • 当子查询返回的结果集非常大时，EXISTS 通常比 IN 效率高。
    • NOT EXISTS 通常比 NOT IN 效率高，因为 NOT IN 需要处理 NULL 值的情况，可能导致全表扫描。

4. 优化 ORDER BY 和 GROUP BY

  • 利用索引排序/分组： 如果 ORDER BY 或 GROUP BY 的列有索引，并且排序/分组顺序与索引顺序一致，数据库可以直接使用索引进行排序，避免 Using filesort（在内存或磁盘上进行额外排序）和 Using temporary（使用临时表）。
  • 限制结果集： 配合 LIMIT 可以减少需要排序的数据量。
  • 只在必要时排序： 如果结果顺序不重要，就不要使用 ORDER BY。

5. 优化子查询

  • 尽可能避免嵌套子查询： 有些复杂的嵌套子查询可以被改写为 JOIN 或 UNION。
  • 相关子查询（correlated subquery）慎用： 相关子查询会为外部查询的每一行执行一次，效率低下。尽量改写为非相关子查询或 JOIN。

6. 优化分页查询 (针对大表)

  • 普通 LIMIT offset, count 的问题： 当 offset 很大时，数据库需要扫描 offset + count 条记录，然后丢弃 offset 条，效率非常低。

  • 优化方案：

    • 基于索引的偏移量查询： 适用于主键或唯一索引是连续且有序的情况。

      -- 查询第 N 页的 M 条记录 (每页 M 条，跳过 N*M 条)
-- 假设每次取 10 条，从第 100000 条开始
SELECT * FROM products WHERE id > (SELECT id FROM products ORDER BY id LIMIT 99999, 1) LIMIT 10;

    • 利用 JOIN 和子查询优化：

      SELECT t1.*
FROM your_table t1
JOIN (SELECT id FROM your_table ORDER BY some_indexed_column LIMIT 100000, 10) t2
ON t1.id = t2.id;

    • 基于游标的分页（更适合API）： 记录上一页的最后一条记录的 ID 或时间戳，下一页从该点开始查询。

      SELECT * FROM products WHERE id > [last_id_from_previous_page] ORDER BY id LIMIT 10;

7. 善用 UNION ALL 而不是 UNION

  • UNION 会对结果集进行去重，去重操作需要额外的 CPU 和内存开销。
  • UNION ALL 不进行去重，效率更高。如果确定结果集中不会有重复行，或者重复行可以接受，就使用 UNION ALL。

8. 使用 EXPLAIN 检查优化效果

在每次进行索引或 SQL 语句优化后，务必再次使用 EXPLAIN 命令来验证优化效果，查看执行计划是否如预期般改进（例如 type 是否从 ALL 变为 range 或 ref，Extra 列是否移除了 Using filesort 或 Using temporary）。

posted @ 2025-08-15 14:26  咩啊咩咩咩  阅读(17)  评论(0)    收藏  举报