本文将深入讲解 MySQL 索引的底层原理、常见类型、使用技巧，并结合 EXPLAIN 工具分析查询执行计划，配合慢查询日志识别瓶颈，逐步建立起系统的 MySQL 查询优化知识体系。适合有一定基础、希望在数据量增长或面试中脱颖而出的开发者阅读。
一、MySQL索引是什么？1.1 索引的本质索引是一种数据结构，其目的是提升数据库查询效率。它将表中的某些列值抽取出来，构建一个高效的查找结构（通常是 B+ 树），通过该结构定位数据的存储位置。
换句话说，索引是表数据的“加速器”。没有索引时，MySQL 只能做全表扫描；有索引时，可快速缩小查找范围。
1.2 索引的类比无索引：就像找一本书中某个词，必须逐页翻阅。有索引：像是查字典，有字母目录直接定位页码。二、MySQL常见索引类型2.1 主键索引（PRIMARY KEY）每张表只能有一个主键索引，默认是聚簇索引。
2.2 唯一索引（UNIQUE）保证字段值唯一，适合如邮箱、身份证号等字段。
2.3 普通索引（INDEX）最基础的索引，无任何约束，只提升查询性能。
2.4 组合索引（Composite Index）在多个列上创建的索引，遵循“最左前缀”原则。
2.5 全文索引（FULLTEXT）用于全文搜索，支持自然语言分析。
2.6 空间索引（SPATIAL）主要用于 GIS 地理信息类型字段。
三、索引底层原理：B+树结构详解MySQL 的 InnoDB 存储引擎默认使用 B+ 树作为索引结构。
3.1 B+树特性所有数据都存储在叶子节点。非叶子节点只存储键值（索引项），不存储数据。所有叶子节点通过链表相连，方便区间查询。3.2 聚簇索引 vs 非聚簇索引聚簇索引：主键索引，数据和索引存储在一起。二级索引（辅助索引）：索引结构中存储的是主键的值，需要二次回表查询原始数据。四、创建索引的最佳实践4.1 如何选择索引列？用于 WHERE 子句过滤的字段用于 JOIN、ORDER BY、GROUP BY 的字段高基数（distinct 值多）的字段优先考虑4.2 创建索引示例代码语言：javascript复制-- 普通索引
CREATE INDEX idx_email ON users(email);
-- 唯一索引
CREATE UNIQUE INDEX idx_mobile ON users(mobile);
-- 组合索引
CREATE INDEX idx_multi ON orders(user_id, status);4.3 删除索引代码语言：javascript复制DROP INDEX idx_email ON users;4.4 查看索引代码语言：javascript复制SHOW INDEX FROM users;五、查询优化利器：EXPLAIN 执行计划5.1 基本使用代码语言：javascript复制EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';5.2 关键字段解析字段
含义
id
查询序列编号
select_type
查询类型（SIMPLE、PRIMARY、SUBQUERY 等）
table
当前访问的表
type
连接类型（ALL、index、range、ref、const、eq_ref、NULL）
key
使用的索引
rows
预计扫描的行数
Extra
额外信息，如"Using where"、“Using index”
5.3 type 字段详解ALL：全表扫描（最差）index：全索引扫描range：范围扫描，如 BETWEEN、>、 -- 命中索引 SELECT * FROM orders WHERE user_id = 123 AND status = 'paid'; -- 未命中组合索引 SELECT * FROM orders WHERE status = 'paid';六、慢查询日志：发现性能瓶颈6.1 开启慢查询日志代码语言：javascript复制[mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 16.2 查询慢日志内容代码语言：javascript复制mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log6.3 使用 pt-query-digest 分析慢查询代码语言：javascript复制pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt七、常见查询优化技巧7.1 避免 SELECT *明确列字段，避免读取不必要数据。 7.2 使用覆盖索引查询所用字段全部在索引中，避免回表。 代码语言：javascript复制-- 创建覆盖索引 CREATE INDEX idx_name_age ON users(name, age); -- 查询使用覆盖索引 SELECT name, age FROM users WHERE name = 'Tom';7.3 避免在 WHERE 中对索引字段做函数操作代码语言：javascript复制-- 不走索引 SELECT * FROM users WHERE DATE(create_time) = '2024-01-01'; -- 优化后 SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';7.4 利用 LIMIT + 索引分页优化代码语言：javascript复制-- 分页慢 SELECT * FROM users ORDER BY id LIMIT 10000, 10; -- 延迟关联优化 SELECT * FROM users WHERE id > (SELECT id FROM users ORDER BY id LIMIT 10000, 1) LIMIT 10;7.5 拆分大查询将一次性操作百万数据的语句，拆分为批量处理： 代码语言：javascript复制DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 1000;八、避免这些索引误区所有字段都建索引：浪费空间 + 写入变慢忽视组合索引顺序：需遵循最左前缀原则数据量小也加索引：小表加索引反而可能变慢高频更新字段建索引：更新频繁的字段不建议建索引九、实践案例：优化百万级用户查询9.1 初始场景代码语言：javascript复制SELECT * FROM users WHERE email = 'abc@example.com';数据量：用户表 500 万条无索引：执行时间 > 3 秒9.2 添加索引代码语言：javascript复制CREATE INDEX idx_email ON users(email);9.3 使用 EXPLAIN 检查代码语言：javascript复制EXPLAIN SELECT * FROM users WHERE email = 'abc@example.com'; -- type: ref, key: idx_email, rows: 1查询时间降低至 < 10ms本项目适用于后台管理系统、电商用户中心、SaaS 用户模块等场景，特别适合开发者进行实战演练与面试准备。 一、项目背景与需求概述我们将构建一个基础版的用户管理系统，具备以下业务功能： 用户注册与登录用户角色与权限分配日志记录与用户状态追踪多条件用户查询与分页涉及的核心业务对象包括：用户、角色、权限、日志等。 二、数据库建模与表结构设计2.1 实体关系图（ER图）简要说明一位用户可以拥有多个角色（多对多）一个角色可以拥有多个权限（多对多）用户与登录日志是一对多关系2.2 用户表（users）代码语言：javascript复制CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(100) NOT NULL, email VARCHAR(100), status TINYINT DEFAULT 1 COMMENT '0:禁用, 1:启用', created_at DATETIME DEFAULT CURRENT_TIMESTAMP );2.3 角色表（roles）代码语言：javascript复制CREATE TABLE roles ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL UNIQUE, description VARCHAR(255) );2.4 权限表（permissions）代码语言：javascript复制CREATE TABLE permissions ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL UNIQUE, code VARCHAR(50) NOT NULL UNIQUE COMMENT '用于权限标识，如 user:view' );2.5 用户-角色关联表（user_role）代码语言：javascript复制CREATE TABLE user_role ( user_id INT, role_id INT, PRIMARY KEY (user_id, role_id), FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (role_id) REFERENCES roles(id) );2.6 角色-权限关联表（role_permission）代码语言：javascript复制CREATE TABLE role_permission ( role_id INT, permission_id INT, PRIMARY KEY (role_id, permission_id), FOREIGN KEY (role_id) REFERENCES roles(id), FOREIGN KEY (permission_id) REFERENCES permissions(id) );2.7 登录日志表（login_logs）代码语言：javascript复制CREATE TABLE login_logs ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, ip_address VARCHAR(45), login_time DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) );三、数据初始化脚本3.1 插入初始角色与权限代码语言：javascript复制INSERT INTO roles(name, description) VALUES ('admin', '系统管理员'), ('user', '普通用户'); INSERT INTO permissions(name, code) VALUES ('查看用户', 'user:view'), ('新增用户', 'user:create'), ('删除用户', 'user:delete'); -- 分配权限给角色 INSERT INTO role_permission(role_id, permission_id) VALUES (1, 1), (1, 2), (1, 3), -- admin 拥有全部权限 (2, 1); -- user 仅能查看用户3.2 插入测试用户代码语言：javascript复制INSERT INTO users(username, password, email) VALUES ('alice', 'hashed_pwd1', 'alice@example.com'), ('bob', 'hashed_pwd2', 'bob@example.com'); -- 分配角色 INSERT INTO user_role(user_id, role_id) VALUES (1, 1), -- alice 为管理员 (2, 2); -- bob 为普通用户四、典型查询场景实现4.1 查询所有启用用户及其角色代码语言：javascript复制SELECT u.id, u.username, r.name AS role FROM users u JOIN user_role ur ON u.id = ur.user_id JOIN roles r ON ur.role_id = r.id WHERE u.status = 1;4.2 查询某用户拥有的所有权限代码语言：javascript复制SELECT p.name, p.code FROM users u JOIN user_role ur ON u.id = ur.user_id JOIN role_permission rp ON ur.role_id = rp.role_id JOIN permissions p ON rp.permission_id = p.id WHERE u.username = 'alice';4.3 查询最近7天登录日志代码语言：javascript复制SELECT u.username, l.ip_address, l.login_time FROM login_logs l JOIN users u ON l.user_id = u.id WHERE l.login_time >= NOW() - INTERVAL 7 DAY ORDER BY l.login_time DESC;4.4 用户分页查询（带关键字搜索）代码语言：javascript复制SELECT * FROM users WHERE username LIKE '%bob%' ORDER BY created_at DESC LIMIT 0, 10;五、事务控制与一致性保障在角色授权或用户注册等业务流程中，可以使用事务来确保数据完整性。 5.1 注册用户 + 分配默认角色（事务）代码语言：javascript复制START TRANSACTION; INSERT INTO users(username, password, email) VALUES('charlie', 'hashed_pwd3', 'charlie@example.com'); SET @uid = LAST_INSERT_ID(); INSERT INTO user_role(user_id, role_id) VALUES(@uid, 2); -- 默认赋普通角色 COMMIT;5.2 授权失败时回滚代码语言：javascript复制START TRANSACTION; -- 假设某权限不存在导致失败 INSERT INTO role_permission(role_id, permission_id) VALUES(1, 999); -- 失败时回滚 ROLLBACK;六、索引优化与执行分析6.1 建议加索引字段users.username：用于登录验证、搜索login_logs.user_id：日志查询user_role.user_id / role_permission.role_id：JOIN 优化代码语言：javascript复制CREATE INDEX idx_username ON users(username); CREATE INDEX idx_user_log ON login_logs(user_id);6.2 执行计划分析代码语言：javascript复制EXPLAIN SELECT u.username, r.name FROM users u JOIN user_role ur ON u.id = ur.user_id JOIN roles r ON ur.role_id = r.id;可查看索引是否使用、JOIN 类型、Rows 扫描数量等。