跳到主要内容

Mysql索引经验

“索引是双刃剑”，过多索引会降低写入性能。可通过ANALYZE TABLE更新统计信息，用OPTIMIZE TABLE整理索引碎片。

Mysql 索引经验

实战工具：​

  • Percona Toolkit：使用pt-index-usage分析索引使用情况，pt-duplicate-key-checker检测冗余索引。
  • sys schema：通过sys.schema_unused_indexes视图查找未使用的索引。

一、索引设计核心原则​

1. 选择性优先法则​

  • 核心指标：选择性 = 唯一值数量 / 总行数，优先选择选择性高的列（如用户 ID、订单号）作为索引列。
    • 示例：性别字段（选择性 ≈0.5）不适合建索引，而邮箱字段（选择性 ≈1）适合。
  • 计算方法：通过  SELECT COUNT(DISTINCT col)/COUNT(*) FROM table;  评估字段区分度。
  • 辅助确认：可以查看 index 信息里的 Cardinality 字段，该值越大越好。

2. 最左前缀匹配原则​

  • 联合索引铁律：索引列顺序必须与查询条件的最左前缀一致。例如索引  (a, b, c)  支持：
    • WHERE a=1
    • WHERE a=1 AND b=2
    • WHERE a=1 AND b=2 AND c=3
  • 失效场景：跳过前缀列（如  WHERE b=2）或中间出现范围查询（如  WHERE a=1 AND b>2）会导致后续列失效。
  • 索引失效还有很多情况，请参考：[[0006-索引失效详解]]

3. 覆盖索引策略​

  • 定义：索引包含查询所需的所有列，避免回表。例如：
CREATE INDEX idx_covering ON users(name, age);
SELECT name, age FROM users WHERE name='Tom'; -- 直接从索引获取数据
  • 性能提升：减少磁盘 I/O，某电商平台使用覆盖索引后查询响应时间从 500ms 降至 50ms 以下。

二、实战优化技巧​

1. 联合索引设计技巧​

  • 高频字段靠左：将查询条件中出现频率高的列放在索引左侧。例如  (status, create_time)  比  (create_time, status)  更高效。
  • 范围查询右置：若存在范围查询（如  WHERE a>10），将范围列放在索引最右侧，避免后续列失效。在 MySQL 中，对于WHERE子句中用AND连接的多个条件，优化器会自动重排条件的执行顺序，其核心目标是减少需要扫描的数据量，从而提高查询效率。
-- 优化前：索引 (a, b) 无法有效利用b列
WHERE a>10 AND b=20;

-- 优化后：索引 (b, a) 可充分利用b列
CREATE INDEX idx_ba ON table(b, a);
  • 排序字段前置：若查询包含  ORDER BY create_time DESC，将排序字段加入索引左列以避免临时文件排序。有坑！！！，不建议直接给 order by 建索引。

2. 前缀索引与长字段优化​

  • 适用场景：对长字符串（如  VARCHAR(500)）使用前缀索引，减少索引体积。
ALTER TABLE users ADD INDEX idx_email_prefix(email(10)); -- 索引前10个字符
  • 长度选择：通过计算不同前缀长度的选择性（如  LEFT(email, 10)  的选择性接近完整列）确定最优值。

3. 分页查询优化​

  • 深度分页问题：LIMIT 1000000, 10  需扫描大量数据，性能低下。
  • 优化方案：
    • 游标标记：基于上一页末条记录的时间戳查询： 
      SELECT * FROM orders WHERE create_time > '2025-04-22' ORDER BY create_time LIMIT 10;
    • 覆盖索引：将排序字段和分页条件包含在索引中，减少回表。

总结：​

尽可能用 where 条件过滤掉大部分数据，给过滤条件最有效的建索引。