【数据库】通俗易懂掌握mysql索引及explain执行计划分析

一、什么是MySQL索引

1.1 索引的日常类比

想象走进一个藏书百万的图书馆，如果所有书籍都随意堆放在地上，要找到《哈利波特与魔法石》需要多久？数据库中的索引就像图书馆的图书目录系统。当我们在用户表的username字段建立索引，就相当于为所有用户名制作了按字母顺序排列的卡片目录。

1.2 索引的存储结构

MySQL最常用的B+Tree索引就像一棵倒置的树。以用户表为例：

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at DATETIME
);

当我们为username建立索引：

CREATE INDEX idx_username ON users(username);

存储结构示意图：

         [D-M]
        /  |  \
   [A-C][D-F][G-M]...
    /     |      \
Alice  David   Grace

1.3 索引类型大全：找到最适合的"目录本"

1.3.1 主键索引（身份证索引）

CREATE TABLE students (
    id INT PRIMARY KEY,  -- 自动创建主键索引
    name VARCHAR(50)
);

特点：唯一且非空，如同身份证号
场景：UPDATE/DELETE操作主键列时效率最高

1.3.2 唯一索引（防重复索引）

CREATE UNIQUE INDEX uni_email ON users(email);

特点：列值必须唯一但允许NULL
案例：用户注册邮箱校验

1.3.3 普通索引（基础目录）

CREATE INDEX idx_phone ON customers(phone);

特点：最基本的加速查询工具
场景：常用于WHERE条件中的非唯一字段

1.3.4 复合索引（组合导航）

CREATE INDEX idx_city_age ON employees(city, age);

特点：多列组合索引，遵循最左匹配原则
案例：筛选城市和年龄范围时效率倍增

1.3.5 全文索引（文本搜索引擎）

ALTER TABLE articles ADD FULLTEXT(title, content);

特点：支持自然语言搜索
案例：搜索包含"数据库优化"的文章：

SELECT * FROM articles 
WHERE MATCH(title,content) AGAINST('数据库优化');

1.3.6 空间索引（地图导航）

CREATE SPATIAL INDEX idx_location ON map_data(coordinates);

特点：处理地理空间数据
场景：查找5公里内的便利店

1.4 索引加速案例

案例1：无索引的全表扫描

SELECT * FROM users WHERE username = 'alice';
-- 执行时间：500ms（10万条数据）

案例2：使用索引的快速定位

ALTER TABLE users ADD INDEX idx_username(username);
SELECT * FROM users WHERE username = 'alice';
-- 执行时间：3ms

二、EXPLAIN实战：SQL性能放大镜

2.1 EXPLAIN基础解读

EXPLAIN SELECT * FROM users 
WHERE username = 'alice' 
AND email LIKE '%@gmail.com';

典型输出：

+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | type       | key  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| 1  | SIMPLE      | users | ref        | idx_username | 1    | 33.33    | Using where |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+

2.2 EXPLAIN字段全解析：读懂执行计划"体检报告"

2.3 常见性能问题诊断

案例3：未使用索引的慢查询

EXPLAIN SELECT * FROM users WHERE MONTH(created_at) = 3;

输出分析：

type: ALL
rows: 100000
Extra: Using where

优化方案：

ALTER TABLE users ADD INDEX idx_created_at(created_at);
SELECT * FROM users 
WHERE created_at BETWEEN '2023-03-01' AND '2023-03-31';

案例4：复合索引的最左匹配

CREATE INDEX idx_user_composite ON users(username, created_at);

-- 有效查询
EXPLAIN SELECT * FROM users 
WHERE username LIKE 'a%' 
AND created_at > '2023-01-01';

-- 失效查询
EXPLAIN SELECT * FROM users 
WHERE created_at > '2023-01-01';

三、索引优化实战宝典

3.1 索引选择策略

• 高频查询字段优先建索引
• 区分度公式：COUNT(DISTINCT col)/COUNT(*) > 30%
• 复合索引字段顺序：高频查询字段在前

3.2 索引失效的六大陷阱

1. 对索引列进行运算：WHERE age + 1 > 20
2. 使用前导通配符：LIKE '%abc'
3. 隐式类型转换：WHERE username = 123
4. OR条件使用不当
5. 使用NOT或<>操作
6. 复合索引跳过最左字段

3.3 性能优化对比实验

优化前（1200ms）：

SELECT * FROM orders 
WHERE customer_id = 100 
AND YEAR(order_date) = 2023
ORDER BY amount DESC;

优化后（45ms）：

CREATE INDEX idx_customer_order ON orders(customer_id, order_date, amount);

SELECT * FROM orders
WHERE customer_id = 100
AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY amount DESC;

四、高级技巧与工具

4.1 EXPLAIN高级用法

EXPLAIN FORMAT=JSON SELECT ...;  -- JSON格式详细分析
SELECT * FROM users USE INDEX(idx_username);  -- 强制使用索引

4.2 可视化工具推荐

1. MySQL Workbench：图形化执行计划查看
2. pt-visual-explain：命令行可视化工具
3. EverSQL Explain Analyzer：在线分析平台

五、最佳实践清单

1. 所有高频查询必须使用索引
2. 定期执行SHOW INDEX FROM table分析索引健康度
3. 慢查询日志需结合EXPLAIN分析（设置阈值1秒）：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

4. 优先使用覆盖索引：

-- 索引(username, age)
SELECT username, age FROM users WHERE username = 'alice';

到此，我们已基本掌握从索引原理到执行计划分析的完整知识体系。合理使用索引可使查询效率提升10-100倍，但需牢记：索引是双刃剑，精准设计才能发挥最大价值。建议在新功能上线前，对核心SQL必做EXPLAIN分析，就像飞行员起飞前必查仪表盘。