什么是索引？为什么需要它？
想象一下，你正在图书馆里找一本名为《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 工具，你就能成为一个真正的数据库性能调优高手。