想象一下，你正在图书馆里找一本名为《MySQL 高级编程》的书。

• 没有索引：你得一本一本、一排一排地翻找，直到找到为止。这叫作 全表扫描，效率极低。
• 有了索引：你先找到图书馆的图书目录（索引），在目录中找到书名对应的页码，然后根据页码直接定位到书的位置。这叫作 索引查找，速度极快。

在数据库中，索引 就是这样一种数据结构，它能帮助 MySQL 数据库系统高效地获取数据。它就像一本书的目录，能大大减少服务器需要扫描的数据量，从而显著提升查询性能。

但索引并非万能，它是一把双刃剑。创建索引虽然能加速查询，但也会减慢数据的写入（INSERT, UPDATE, DELETE）速度，因为每次数据变动时，索引也需要同步更新。因此，合理的索引设计至关重要。

---

MySQL 核心索引类型详解

MySQL 的 InnoDB 存储引擎支持多种索引类型，其中最核心的是以下几种：

1. 主键索引 (PRIMARY KEY)

• 特性：一种特殊的唯一索引，用于唯一标识表中的每一行记录。一张表只能有一个主键索引。主键索引的值不允许重复，也不允许为 NULL。
• 底层原理：InnoDB 的主键索引是一种聚集索引。这意味着，数据行本身就是按照主键的顺序物理存储在磁盘上的。数据和索引是绑定在一起的。
• 应用场景：任何需要唯一标识的列，如用户 ID、订单 ID 等。

创建示例：

SQL

 CREATE TABLE users (
     id INT PRIMARY KEY AUTO_INCREMENT,
     username VARCHAR(50) NOT NULL,
     email VARCHAR(100)
 );

使用示例：

SQL

 -- 通过主键查询，速度最快，因为可以直接定位数据行
 SELECT * FROM users WHERE id = 100;

2. 唯一索引 (UNIQUE INDEX)

• 特性：保证索引列中的所有值都是唯一的。与主键索引不同，唯一索引允许有 NULL 值，并且一张表可以有多个唯一索引。
• 底层原理：非聚集索引。它的叶子节点存储的是索引键值和主键值，查询时需要通过主键值再进行回表查询，才能获取完整的数据行。
• 应用场景：需要保证字段唯一性的业务场景，如用户的手机号、邮箱等。

创建示例：

SQL

 -- 创建表时指定
 CREATE TABLE members (
     id INT PRIMARY KEY,
     email VARCHAR(100) UNIQUE,
     phone_number VARCHAR(20)
 );
 ​
 -- 对已存在的表添加
 CREATE UNIQUE INDEX idx_phone ON members (phone_number);

使用示例：

SQL

 -- 通过唯一索引查询，速度也很快，但可能需要回表
 SELECT * FROM members WHERE email = 'test@example.com';

3. 普通索引 (INDEX)

• 特性：最基本的索引类型，没有任何限制。索引列的值可以重复，也可以为 NULL。
• 底层原理：非聚集索引，同样需要回表查询。
• 应用场景：任何需要加速查询的非唯一字段，如商品名称、订单状态、创建日期等。

创建示例：

SQL

 -- 创建表时指定
 CREATE TABLE products (
     id INT PRIMARY KEY,
     product_name VARCHAR(100),
     price DECIMAL(10, 2),
     INDEX idx_product_name (product_name)
 );
 ​
 -- 对已存在的表添加
 CREATE INDEX idx_price ON products (price);

使用示例：

SQL

 -- 根据商品名称查询，利用索引可以快速定位
 SELECT * FROM products WHERE product_name = 'iPhone 15 Pro Max';

4. 复合索引 (Compound Index)

• 特性：在多个列上建立的索引。当查询条件经常同时使用多个字段时，复合索引能发挥巨大作用。
• 底层原理：遵循“最左前缀原则”。如果索引是 (col1, col2, col3)，查询条件必须从 col1 开始匹配，索引才能生效。
• 应用场景：查询条件常包含多个字段的业务，如 WHERE user_id = 1 AND order_status = 'paid'。

创建示例：

SQL

 CREATE TABLE orders (
     id INT PRIMARY KEY,
     user_id INT,
     order_status VARCHAR(20),
     order_time DATETIME,
     INDEX idx_user_status (user_id, order_status) -- 复合索引
 );

使用示例：

SQL

 -- 满足最左前缀原则，索引生效
 SELECT * FROM orders WHERE user_id = 100 AND order_status = 'paid';
 SELECT * FROM orders WHERE user_id = 100;
 ​
 -- 不满足最左前缀原则，索引失效
 SELECT * FROM orders WHERE order_status = 'paid';

---

索引优化技巧与避免失效

即使创建了索引，如果使用不当，MySQL 也可能选择全表扫描，导致索引失效。理解这些常见陷阱是高效利用索引的关键。

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

错误示例：

SQL

 -- 对索引列 `order_time` 使用了 `YEAR()` 函数
 SELECT * FROM orders WHERE YEAR(order_time) = 2024;

MySQL 无法直接利用索引树进行查找，因为它需要对每一行都计算 YEAR()，导致全表扫描。

优化方案：

SQL

 -- 推荐：将函数操作放在等号右边，让索引生效
 SELECT * FROM orders WHERE order_time >= '2024-01-01 00:00:00' AND order_time < '2025-01-01 00:00:00';

2. 避免在模糊查询的开头使用 %

错误示例：

SQL

 -- 模糊查询以 `%` 开头
 SELECT * FROM products WHERE product_name LIKE '%手机%';

因为索引是有序的，但你查询的字符串开头不确定，MySQL 无法利用索引进行快速定位。

优化方案：

SQL

 -- 推荐：只在字符串末尾使用 `%`
 SELECT * FROM products WHERE product_name LIKE 'iPhone%';

3. 避免隐式类型转换

错误示例：

SQL

 -- `phone_number` 是 VARCHAR 类型，但查询时使用了数字
 SELECT * FROM users WHERE phone_number = 13812345678;

MySQL 会自动进行类型转换，这个转换过程会使得索引失效。

优化方案：

SQL

 -- 推荐：保持类型一致
 SELECT * FROM users WHERE phone_number = '13812345678';

4. 善用覆盖索引 (Covering Index)

覆盖索引 是指查询所需的所有列都包含在索引中，这样 MySQL 就不需要进行回表查询，从而极大地提升查询效率。

示例：

如果有一个复合索引 (user_id, order_status)，下面的查询就是覆盖索引。

SQL

 -- SELECT 的列都包含在索引中，不需要回表
 SELECT user_id, order_status FROM orders WHERE user_id = 100;

---

如何观察和分析索引

查看索引

SQL

 -- 查看 products 表上的所有索引
 SHOW INDEX FROM products;

使用 EXPLAIN 分析查询

EXPLAIN 是 MySQL 提供的神器，它可以分析 SQL 语句，告诉你这条语句的执行计划，包括是否使用了索引，使用了哪个索引，以及大致扫描了多少行。

SQL

 EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND order_status = 'paid';

你需要关注 EXPLAIN 结果中的几个关键字段：

• type：表示连接类型，const、eq_ref、ref、range 都表示查询性能较好，而 ALL 则意味着全表扫描，性能最差。
• key：实际使用的索引名称。
• rows：MySQL 估计需要扫描的行数，值越小越好。
• Extra：额外信息。如果看到 Using index，说明使用了覆盖索引，性能极佳。

总结：索引是提升数据库性能的利器，但它的使用需要根据业务场景进行精心的设计和优化。理解索引的底层原理和失效原因，并善用 EXPLAIN 工具，你就能成为一个真正的数据库性能调优高手。