MySQL 索引与性能优化实战

索引是数据库性能优化的最重要手段——没有之一！

一、什么是索引？

索引就像书的目录：没有目录时你要逐页翻找（全表扫描），有了目录可以直接翻到目标页。

-- 没有索引：逐行扫描 100 万行
SELECT * FROM users WHERE email = 'test@example.com';  -- 可能耗时 1秒+

-- 有索引：直接定位到目标行
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'test@example.com';  -- 只需 1毫秒

二、索引类型

1. 普通索引（INDEX）

CREATE INDEX idx_name ON users(name);
-- 或
ALTER TABLE users ADD INDEX idx_name(name);

2. 唯一索引（UNIQUE）

CREATE UNIQUE INDEX idx_email ON users(email);
-- 或建表时直接指定
CREATE TABLE users (
    email VARCHAR(100) UNIQUE
);

唯一索引保证字段值不重复，自动做唯一性检查。

3. 主键索引（PRIMARY KEY）

-- 建表时指定
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ...
);

主键是特殊的唯一索引，每张表只能有一个。

4. 复合索引（多列索引）

-- 联合索引，最左前缀原则
CREATE INDEX idx_city_age ON users(city, age);
-- 生效的场景：
WHERE city = '北京'           -- √
WHERE city = '北京' AND age = 20  -- √
WHERE age = 20                -- × 不生效（没从最左开始）

5. 全文索引（FULLTEXT）

CREATE FULLTEXT INDEX idx_content ON articles(content);
-- 适用于大文本的搜索
SELECT * FROM articles WHERE MATCH(content) AGAINST('数据库');

三、如何发现慢查询？

开启慢查询日志

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log%';

-- 设置阈值为 2 秒
SET GLOBAL long_query_time = 2;

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

使用 EXPLAIN 分析

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com'G

关键字段含义：

四、索引设计原则

适合加索引的场景

不适合加索引的场景

-- 1. 数据量很小的表（< 1000行）
-- 2. 频繁增删改的字段
-- 3. 值重复度很高的字段（如性别只有男/女）
-- 4. 很少用于查询的字段

五、索引优化实战

1. 最左前缀原则

-- 复合索引 (a, b, c)
INDEX idx_abc (a, b, c)

-- √ 能用索引
WHERE a = 1
WHERE a = 1 AND b = 2  
WHERE a = 1 AND b = 2 AND c = 3

-- × 不能使用
WHERE b = 2              -- 跳过了 a
WHERE c = 3              -- 跳过了 a, b

2. 覆盖索引

-- 如果查询的所有字段都在索引中，不需要回表查询
-- 索引: INDEX idx_name_age(name, age)
SELECT name, age FROM users WHERE name = '张三';  -- 覆盖索引 √
SELECT * FROM users WHERE name = '张三';           -- 需要回表 ×

3. 索引下推（ICP）

-- MySQL 5.6+ 特性，在存储引擎层就过滤数据
-- 减少回表次数
SET optimizer_switch = 'index_condition_pushdown=on';

六、常见性能问题排查

-- 1. 查看当前数据库连接
SHOW PROCESSLIST;

-- 2. 查看表大小
SELECT 
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY size_mb DESC;

-- 3. 查看索引使用情况
SELECT 
    index_name,
    cardinality,
    (cardinality / (SELECT COUNT(*) FROM your_table)) * 100 AS selectivity
FROM information_schema.statistics
WHERE table_name = 'your_table';

-- 4. 分析表（更新索引统计信息）
ANALYZE TABLE your_table;

-- 5. 优化表（整理碎片）
OPTIMIZE TABLE your_table;

七、索引的代价

索引不是越多越好！

黄金法则：

索引数量控制在 5~10 个 以内，根据实际查询模式创建。

八、实战：优化一个慢查询

原始查询（超慢）

SELECT * FROM orders 
WHERE customer = '张三' 
  AND order_date BETWEEN '2026-01-01' AND '2026-06-30'
ORDER BY order_date DESC;

查看执行计划

EXPLAIN SELECT ...;
-- type: ALL（全表扫描！） rows: 1000000

添加索引

-- 根据查询条件创建复合索引
ALTER TABLE orders ADD INDEX idx_customer_date (customer, order_date);

优化后

EXPLAIN SELECT ...;
-- type: ref, rows: 5（扫描 5 行！）

速度提升 几十万倍，这就是索引的威力！

本篇小结

√ 理解了索引的工作原理（类似图书目录）
√ 掌握了 5 种索引类型及其适用场景
√ 学会了用 EXPLAIN 分析慢查询
√ 知道了索引设计的黄金法则
√ 实战优化了一个慢查询，速度提升数十万倍

下一篇我们将学习 事务与锁机制！