MySQL: 索引优化策略详解：核心原则、高级应用与维护实践

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

索引优化策略一：核心原则与高效索引设计

1. 避免在索引列上使用表达式或函数

策略核心：在索引列上使用表达式或函数会导致索引失效。

典型错误案例：查找30天内过期的数据时，错误写法使索引失效：

        -- 错误写法（索引失效）
        SELECT * FROM opportunities
        WHERE DATE_ADD(expiry_date, INTERVAL 30 DAY) < NOW();
    

正确改写（利用索引）：

        -- 正确改写（利用索引）
        SELECT * FROM opportunities
        WHERE expiry_date < DATE_SUB(NOW(), INTERVAL 30 DAY);
    

技术原理：索引存储的是原值而不是计算值，只有直接比较原字段才能命中索引。

B树索引存储的是原始键值，对列进行计算会破坏索引的有序性，导致全表扫描。日期列是最容易违反这一规则的场景，需要显式转换计算逻辑。

expiry_date

2. 前缀索引的权衡应用

核心问题：B树索引的键值大小有限制（InnoDB ≤ 767字节，MyISAM ≤ 1000字节），对于长字符串，需要使用前缀索引来减少空间占用。

创建语法：

        CREATE INDEX idx_name_prefix ON table_name (column_name(255)); -- 指定前缀长度
    

示例：

        -- 对title列前255字符建索引
        CREATE INDEX idx_title_prefix ON films (title(255));
    

关键问题：前缀索引可能导致索引选择性降低。例如：

• 前2字节：仅2个唯一值，选择性差
• 前3字节：唯一值增多，选择性提升

关键权衡：

• 优势：减少索引体积，提高I/O效率（特别是对于大文本列）
• 劣势：选择性降低，索引值重复率增加

优化建议：通过统计不同前缀长度的唯一值比例，平衡索引大小与选择性：

        -- 计算选择性语法
        SELECT
            COUNT(DISTINCT LEFT(column_name, 3)) / COUNT(*) AS selectivity_3,
            COUNT(DISTINCT LEFT(column_name, 5)) / COUNT(*) AS selectivity_5
        FROM table_name;
    

示例：

        -- 示例
        SELECT
            COUNT(DISTINCT LEFT(title, 3)) / COUNT(*) AS selectivity_3,
            COUNT(DISTINCT LEFT(title, 5)) / COUNT(*) AS selectivity_5
        FROM films;
    

最佳实践：选择最小前缀长度，使选择性 > 0.3（避免重复值过多），选择性 > 0.3 时通常可接受。

AB

BC

ABC

ABD

TEXT

VARCHAR

3. 联合索引列顺序三原则

优先级：

1. 高频查询列优先：最左列应覆盖80%查询场景（如user_id）
2. 高选择性列优先：过滤性强的列（如order_id）应前置，减少扫描范围
3. 小宽度列优先：在选择性相近时，优先整型等小类型（如INT vs VARCHAR(255））

联合索引的列顺序至关重要，策略优先级如下：

• 高频查询列优先：最左列应覆盖高频过滤条件（如状态列），快速过滤大量数据
• 高选择性列优先：高基数（Cardinality）列（如用户ID）靠左
• 小宽度列优先：小类型列（如整型）靠左减少单页存储量，提升 I/O 效率

反例：若将低选择性列置于最左，优化器可能忽略索引

        -- 反例
        CREATE INDEX idx_poor ON orders(status, create_time); -- status选择性低，应避免最左
    

(col1, col2, col3)

status

WHERE status=val

user_id

gender

INT

gender

4. 覆盖索引（Covering Index）的高效查询优化

定义：索引包含查询所需的全部字段（WHERE/SELECT/ORDER BY/GROUP BY），无需回表查询。

核心优势：

• I/O 优化：仅读取索引页（体积远小于数据行），减少磁盘访问
• 顺序 I/O 加速：B树索引有序存储，将随机 I/O 转为顺序 I/O（磁盘顺序读写性能提升 10-100 倍）
• 二级索引优化：InnoDB 二级索引叶节点含主键值，覆盖索引避免二次主键查找

使用限制：

场景	是否支持	原因
SELECT *	?无法覆盖所有字段	?
内存引擎（MEMORY）	?存储引擎不支持	?
LIKE '%value%'	?存储引擎 API 限制	?

示例：

        -- 覆盖索引正例（仅需索引）
        EXPLAIN SELECT language_id FROM film WHERE film_id = 1; -- Extra: "Using index"
    

反例（需回表）

EXPLAIN SELECT * FROM film WHERE language_id = 1;       -- Extra: "Using where"

失效场景：

• 存储引擎不支持（如MEMORY）
• 查询包含

  SELECT *

• 查询包含

  LIKE '%双百分号%'

• 索引未包含全部所需列

扩展：索引的选择性

在 MySQL 中，索引选择性（Index Selectivity）是衡量一个索引是否有效的关键指标。选择性越高，表示索引越能有效地过滤数据，从而加快查询速度。

选择性定义：选择性 = 唯一值数量 / 总行数

选择性高：意味着大多数值都不重复，索引过滤能力强；选择性低：意味着很多值是重复的，索引过滤能力差。

指标	高选择性	低选择性
唯一值数量	接近总行数	远小于总行数
举例字段	用户邮箱	用户性别、状态
索引效率	高	低
优化器倾向	优先使用索引	可能放弃索引

为什么低选择性会导致索引失效？

当你在复合索引的最左列使用了低选择性字段（如状态列只有3个值），MySQL 的优化器可能会认为：“与其通过索引一个个查找，还不如直接全表扫描来的快。”这是因低选择性字段的值重复率高，通过索引查找仍然需要访问大量行；索引访问的成本（如回表）可能超过直接全表扫描的成本；MySQL 优化器基于成本模型选择最优执行计划，可能会放弃低效索引。

示例说明：

-- 假设复合索引为 (status, user_id)
SELECT * FROM users WHERE status = 'active' AND user_id = 123;

如果

status

字段只有 3 个值（active、inactive、pending），那它就是低选择性字段；即便

user_id

是高选择性的，优化器也可能因为最左列

status

的低选择性而跳过整个索引；如果反过来，索引是

(user_id, status)

，因为

user_id

是高选择性，索引很可能被正常使用。

补充说明：如何提高低选择性字段的索引效率？

你可以通过以下方式优化：

• 调整索引列顺序：将高选择性字段放前面 [14]。
• 使用复合索引：将多个字段组合起来提高整体选择性 [10]。
• 避免单独为低选择性字段建索引：除非配合高选择性字段一起使用 [29]。

结论

低选择性指的是字段中不同值较少、重复率高的情况。在 MySQL 中，这种字段如果放在复合索引的最左列，可能导致优化器放弃使用索引，从而引发性能问题。因此，设计索引时应优先考虑将高选择性字段放在前列。

索引优化策略二：高级应用与查询加速

1) 利用索引优化排序（ORDER BY）

生效条件：

• 索引列顺序/升降序与

  ORDER BY

  完全一致。
• 排序字段均属关联表的第一张表（多表 JOIN 时）。
• 最左列禁用范围查询（否则右侧列索引失效）。

InnoDB vs MyISAM 差异：

• InnoDB表主键天然支持排序
• MyISAM需显式创建索引

-- InnoDB 主键索引天然支持排序
EXPLAIN SELECT * FROM rental ORDER BY rental_id; -- Type: index (索引扫描排序)

-- MyISAM 需显式索引支持
EXPLAIN SELECT * FROM rental_myisam ORDER BY return_date; -- Extra: "Using filesort"（文件排序）

联合索引排序示例：

CREATE INDEX idx_rental ON rental (return_date, inventory_id, customer_id);

-- 有效排序（索引顺序匹配）
EXPLAIN SELECT * FROM rental
WHERE return_date = '2005-05-24'
ORDER BY inventory_id, customer_id; -- Extra: NULL（无 filesort）

-- 失效案例（升降序冲突）
EXPLAIN SELECT * FROM rental
ORDER BY return_date DESC, inventory_id ASC; -- Extra: "Using filesort"

强调：B树索引天然有序，可替代

ORDER BY

的文件排序：

-- 使用索引 (return_date, inventory_id, customer_id) 排序
SELECT * FROM rental
WHERE return_date = '2005-05-24'
ORDER BY return_date, inventory_id, customer_id;

严格条件：

• 索引列顺序、升降序（ASC/DESC）必须与

  ORDER BY

  完全一致
• 范围查询（如

  return_date > '2005-05-24'

  ）会使右侧索引列失效

有效排序（利用联合索引

(return_date, customer_id)

）：

SQL查询语句示例：

    SELECT * FROM rental
    WHERE return_date = '2005-05-09'
    ORDER BY return_date ASC, customer_id ASC; -- 使用索引
  

失效场景：

• ORDER BY return_date DESC, customer_id ASC; —— 排序方向不一致导致使用文件排序
• WHERE return_date > '2005-05-09' ORDER BY customer_id; —— 最左前缀规则导致使用文件排序

B树索引模拟哈希索引

在哈希索引不可用的情况下，B树索引可以通过以下方式模拟哈希索引，适用于长字符串的精确匹配：

1. 增加一个哈希值列（例如使用MD5算法）并创建索引。
2. 查询时，先通过哈希列过滤，再通过原字段进行第二次验证，防止哈希冲突。

SQL实现步骤如下：

    -- 1. 添加哈希列
    ALTER TABLE film ADD COLUMN title_md5 CHAR(32);
    UPDATE film SET title_md5 = MD5(title);

    -- 2. 创建B树索引
    CREATE INDEX idx_title_md5 ON film (title_md5);

    -- 3. 查询（双重过滤防止冲突）
    SELECT * FROM film
    WHERE title_md5 = MD5('ACADEMY DINOSAUR')
    AND title = 'ACADEMY DINOSAUR';
  

哈希函数的选择：

• 推荐使用：
  

  MD5

  （128位）
  

  SHA1

  （160位）
• 避免使用：
  

  CRC32

  （冲突率较高）

注意事项：

• 哈希函数应生成固定长度的值，如MD5或SHA1。
• 在发生哈希冲突时，必须使用原始字段进行二次验证。

覆盖索引的存储引擎差异

存储引擎	二级索引结构	覆盖索引的好处
InnoDB	叶节点存储主键值	避免主键的二次查找
MyISAM	叶节点存储数据行的物理地址	避免系统调用，提高效率

例如，在MyISAM中使用覆盖索引可以直接通过索引返回数据，避免额外的系统调用：

    EXPLAIN SELECT actor_id, last_name FROM actor_myisam
    WHERE last_name = 'WAHLBERG'; -- 直接通过索引返回数据
  

索引优化策略（下）：锁、冗余与维护

1. 索引优化行级锁（InnoDB）

在InnoDB中，合理的索引设计可以显著提高并发性能：

• 索引未命中时锁全表（例如：
  

  last_name

  无索引时
  

  SELECT ... FOR UPDATE

  锁所有行）
• 索引命中后仅锁住筛选出的行，提高并发性。

机制：索引帮助存储引擎层过滤掉无效行，减少锁定范围。

无索引时的锁问题示例：

    -- 会话1（无索引，全表锁）
    BEGIN;
    SELECT * FROM actor WHERE last_name = 'GUINESS' FOR UPDATE; -- 锁全表，阻塞其他会话

    -- 会话2（被阻塞）
    BEGIN;
    SELECT * FROM actor WHERE last_name = 'WAHLBERG' FOR UPDATE; -- 等待锁释放
  

索引优化后：

    CREATE INDEX idx_last_name ON actor (last_name);
    -- 会话1（仅锁目标行）
    SELECT ... WHERE last_name = 'GUINESS' FOR UPDATE;

    -- 会话2（立即执行）
    SELECT ... WHERE last_name = 'WAHLBERG' FOR UPDATE;
  

结论：通过索引将锁粒度从表级降低到行级，可以显著提升并发性能。

2. 冗余与重复索引处理

冗余和重复索引不仅浪费存储空间，还会影响性能。常见的冗余和重复索引包括：

• 在同一列上建立等效索引（例如：
  

  PRIMARY KEY

  +
  

  UNIQUE

  ）
• 复合索引包含前缀列索引（例如：
  

  (a)

  +
  

  (a,b)

  ）

检测工具：

pt-duplicate-key-checker

（Percona Toolkit）

    pt-duplicate-key-checker --database=sakila
  

输出示例：

Table `sakila.payment`:
  INDEX `idx_customer_id` (customer_id) -- 冗余，可被联合索引覆盖 
  INDEX `idx_customer_staff` (customer_id, staff_id)

# Key idx_customer_id on payment is redundant to idx_customer_staff
- DROP INDEX idx_customer_id ON payment;

payment

表：

idx_customer_id

可被复合索引

(customer_id, staff_id)

替代，建议删除

例外情况：当复合索引过大时，保留高频使用的单列索引可以提升性能。

清理策略：定期检查未使用的索引：

    SELECT * FROM sys.schema_unused_indexes
    WHERE object_schema = 'sakila';
  

3. 索引统计与碎片维护

优化器依赖统计信息来选择合适的索引，因此保持统计信息的准确性非常重要：

    -- 更新表的统计信息（InnoDB/MyISAM）
    ANALYZE TABLE payment;
  

不同存储引擎的统计信息存储位置及更新成本：

存储引擎	统计信息存储位置	更新成本
MyISAM	磁盘	高（全索引扫描）
InnoDB	内存	低（随机采样）

MyISAM：执行全索引扫描时，表锁定成本较高。

InnoDB：采用随机抽样评估方法，虽然效率较高，但可能不够准确。

索引碎片的维护：

• 通过重建表和索引来消除碎片。
• OPTIMIZE TABLE payment; —— 这是一个表锁定操作，建议在维护时段执行。

注意：

OPTIMIZE TABLE

表锁定操作应在维护窗口内执行。

碎片的影响：

• 物理存储的无序导致随机I/O增加。
• 页面填充率的下降使得内存利用率降低。

关键总结：

• 索引失效的常见原因包括表达式计算、函数调用以及前缀索引设置过短。
• 覆盖索引的核心理念是通过增加存储空间来提高查询速度，减少回表和随机I/O操作。
• 联合索引的设计应遵循频率高、选择性强和字段宽度小的原则进行排序。
• 锁优化的关键在于通过索引减少锁定的粒度，从而提高系统的并发处理能力。
• 数据库维护的基本任务包括定期清理不必要的索引、更新统计信息以及整理索引碎片。

原生SQL实例：

/* 覆盖索引优化示例 */
  CREATE INDEX idx_covering ON rental (return_date, customer_id, staff_id);
  EXPLAIN
  SELECT return_date, customer_id
  FROM rental
  WHERE return_date = '2005-05-09'
  ORDER BY customer_id; -- 使用索引
  

NestJS集成示例：

1. TypeORM中的索引管理

// film.entity.ts
  import { Entity, Column, PrimaryColumn, Index } from 'typeorm';

  @Entity()
  @Index('idx_title_md5', ['titleMd5']) // 创建前缀索引
  @Index('idx_language', ['languageId']) // 创建单列索引
  @Index('idx_rental', ['returnDate', 'inventoryId', 'customerId']) // 创建复合索引
  export class Film {
    @PrimaryColumn()
    film_id: number;

    @Column({ length: 255 })
    title: string;

    @Column({ name: 'title_md5', length: 32 })
    titleMd5: string; // 模拟哈希索引

    @Column()
    languageId: number;
  }

  // 更新表的统计信息（使用原生查询）
  import { getManager } from 'typeorm';

  async function analyzeTable() {
    await getManager().query('ANALYZE TABLE film;');
  }
  

2. 定义和使用索引

// 实体定义（TypeORM）
  @Entity()
  export class Film {
    @PrimaryGeneratedColumn()
    id: number;

    @Column({ length: 255 })
    title: string;

    @Column({ name: 'title_md5', length: 32 })
    titleMd5: string; // 模拟哈希索引

    @Index('idx_language_id')
    @Column()
    languageId: number;
  }

  // 查询服务
  @Injectable()
  export class FilmService {
    constructor(@InjectRepository(Film) private filmRepository: Repository) {}

    async findFilmByTitle(title: string): Promise {
      const titleMd5 = createHash('md5').update(title).digest('hex');
      return this.filmRepository.findOne({
        where: { titleMd5, title },
      }); // 利用覆盖索引
    }
  }
  

总结要点（归纳为五点）：

1. 索引设计应遵循三条基本原则：避免使用表达式、合理选择前缀长度、按照选择性从高到低排序联合索引。
2. 禁止在索引列上使用函数，对于涉及日期的计算应重写表达式。
3. 前缀索引的长度与选择性之间需要找到一个平衡点，以确保索引的有效性。

   SELECTivity < 0.8

4. 联合索引的排序原则是“频率高 > 选择性强 > 字段宽度小”，以最大化索引的利用效率。

   SELECT *

   和

   LIKE '%...'

5. 覆盖索引能显著提升查询性能，其核心价值在于减少I/O操作、避免回表操作以及加速排序和分组操作。

   ANALYZE TABLE

   、消除碎片

   OPTIMIZE TABLE

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：MySQL sql selectivity Inventory duplicate