2026/9/6 12:59:15
FEATURED · 精选文章 
对于很多刚开始接触 Java 和数据库的开发者来说索引这个概念听起来很抽象总觉得是数据库底层高深莫测的机制。但如果你用过字典查字其实你已经理解了索引的核心思想。索引本质上就是一种帮助数据库快速定位数据的数据结构就像字典的拼音或部首检字表让你不用一页一页翻就能找到目标字。在实际 Java Web 项目中数据库查询性能往往是系统瓶颈所在。当数据量达到几十万、上百万时没有索引的查询可能从几毫秒变成几十秒而合理使用索引能让查询速度提升数十倍甚至上百倍。理解索引不仅是为了应对面试中的“八股文”更是为了在实际项目中真正解决性能问题。本文将从查字典的类比出发逐步解释 MySQL 索引的工作原理、类型选择、创建方式、使用注意事项和常见误区。学完后你将掌握如何为表结构设计合适的索引如何验证索引效果以及如何排查索引相关的性能问题。1. 为什么数据库需要索引从查字典说起1.1 没有索引的全表扫描就像翻字典假设你要在一本 1000 页的《现代汉语词典》中查找“数据库”这个词的解释。如果没有拼音索引、部首索引或笔画索引你只能从第一页开始一页一页往后翻直到找到目标词条。这种查找方式在数据库中称为“全表扫描”Full Table Scan。全表扫描的代价与数据量成正比。字典有 1000 页平均需要翻 500 页表有 100 万行数据平均需要扫描 50 万行。当数据量很大时这种线性查找的效率极低。-- 假设 users 表有 100 万行数据没有索引 SELECT * FROM users WHERE name 张三;这个查询需要逐行比较 name 字段的值直到找到所有匹配“张三”的记录。1.2 索引就像字典的检字表字典的拼音索引将汉字按拼音顺序排列每个拼音后面标注对应的页码。查找时先根据拼音定位到大致区域然后直接翻到目标页码附近。数据库索引的工作原理类似它维护一个独立的数据结构其中包含索引字段的值和对应数据行的位置信息。查询时数据库先通过索引快速定位到目标数据的位置然后直接读取这些位置的数据。-- 为 name 字段创建索引后同样的查询效率大幅提升 CREATE INDEX idx_users_name ON users(name); SELECT * FROM users WHERE name 张三;现在数据库会先在 idx_users_name 索引中快速找到“张三”对应的位置然后直接读取这些位置的数据行。1.3 索引的代价空间换时间索引虽然提高了查询速度但也需要付出代价存储空间索引需要额外的磁盘空间来存储索引数据结构。维护成本当数据增删改时索引也需要同步更新会影响写入性能。设计复杂度需要根据查询模式合理设计索引错误的索引可能反而降低性能。合理的索引设计需要在查询性能和写入性能之间找到平衡点。2. MySQL 索引的核心数据结构B树2.1 为什么选择 B树而不是其他数据结构MySQL 最常用的索引类型是基于 B树BTree的。与二叉树、哈希表等数据结构相比B树有以下优势适合磁盘存储B树的节点可以存储多个键值树的高度较低减少磁盘 I/O 次数。范围查询高效B树的叶子节点形成有序链表适合范围查询。数据稳定性B树在增删改时能保持较好的平衡性。2.2 B树的基本结构B树由根节点、中间节点和叶子节点组成根节点树的顶层存储指向中间节点的指针。中间节点存储键值和指向下一层节点的指针。叶子节点存储键值和对应数据行的位置信息聚簇索引存储完整数据行。以字典类比根节点相当于拼音索引的首字母分类A、B、C...中间节点相当于具体拼音ba、bi、bo...叶子节点相当于具体汉字和页码八→p15, 巴→p12...2.3 B树的查找过程假设要在 users 表中查找 name李四 的记录从根节点开始比较“李四”与节点中的键值确定下一步方向。沿着中间节点层层向下逐步缩小范围。到达叶子节点后找到“李四”对应的数据位置。根据位置信息读取完整数据行。这个过程的磁盘 I/O 次数等于树的高度通常只有 3-4 次即使对于上亿条数据的表也是如此。3. MySQL 索引类型及适用场景3.1 主键索引PRIMARY KEY主键索引是一种特殊的唯一索引每个表只能有一个主键索引。CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, email VARCHAR(100) );特点主键值必须唯一且不为 NULL。InnoDB 存储引擎中主键索引是聚簇索引数据按主键顺序存储。建议使用自增整数作为主键避免页分裂带来的性能开销。3.2 唯一索引UNIQUE INDEX唯一索引保证索引列的值必须唯一但允许 NULL 值。CREATE UNIQUE INDEX idx_users_email ON users(email);适用场景邮箱、手机号、身份证号等需要唯一性约束的字段。3.3 普通索引INDEX最基本的索引类型没有唯一性约束。CREATE INDEX idx_users_name ON users(name);适用场景经常作为查询条件的字段但不需要唯一性约束。3.4 复合索引Composite Index包含多个列的索引也称为联合索引。CREATE INDEX idx_users_name_age ON users(name, age);复合索引遵循最左前缀原则查询必须使用索引的最左列才能生效。有效使用复合索引的查询SELECT * FROM users WHERE name 张三; -- 使用索引 SELECT * FROM users WHERE name 张三 AND age 25; -- 使用索引无法使用复合索引的查询SELECT * FROM users WHERE age 25; -- 没有使用最左列 name3.5 全文索引FULLTEXT INDEX专门用于文本内容的全文搜索。CREATE FULLTEXT INDEX idx_articles_content ON articles(content);使用 MATCH AGAINST 进行全文搜索SELECT * FROM articles WHERE MATCH(content) AGAINST(数据库 索引 IN NATURAL LANGUAGE MODE);4. 索引的创建和使用实践4.1 创建索引的语法基本创建语法CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (column1, column2, ...);修改表结构添加索引ALTER TABLE table_name ADD [UNIQUE|FULLTEXT] INDEX index_name (column1, column2, ...);创建表时直接定义索引CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), email VARCHAR(100), INDEX idx_name (name), UNIQUE INDEX idx_email (email) );4.2 查看索引信息查看表的索引信息SHOW INDEX FROM users;结果包含以下重要信息Table: 表名Non_unique: 是否唯一0唯一1不唯一Key_name: 索引名称Seq_in_index: 索引中的列序号Column_name: 列名Cardinality: 基数索引中唯一值的数量估算4.3 使用 EXPLAIN 分析查询执行计划EXPLAIN 命令可以显示 MySQL 如何执行查询是优化查询的重要工具。EXPLAIN SELECT * FROM users WHERE name 张三;关键字段解释type: 访问类型const、eq_ref、ref、range、index、ALLpossible_keys: 可能使用的索引key: 实际使用的索引rows: 预估需要扫描的行数Extra: 额外信息Using where、Using index等4.4 索引使用情况验证检查索引是否被使用-- 开启性能模式如果需要 SET SESSION profiling 1; -- 执行查询 SELECT * FROM users WHERE name 张三; -- 查看查询详情 SHOW PROFILES; SHOW PROFILE FOR QUERY 1;5. 索引设计的最佳实践5.1 选择合适的索引列应该创建索引的列经常出现在 WHERE 子句中的列经常用于连接JOIN的列经常用于排序ORDER BY的列经常用于分组GROUP BY的列不应该创建索引的列数据重复度高的列如性别、状态标志很少用于查询的列文本内容过长的列考虑使用前缀索引5.2 复合索引的列顺序原则设计复合索引时列的顺序很重要区分度高的列放在前面选择性高的列能更快缩小查询范围。等值查询列在前范围查询列在后等值查询的列应该放在范围查询、、BETWEEN的列前面。经常排序的列考虑放在索引中如果查询需要排序可以考虑将排序列加入索引。示例-- 假设经常按部门查询并按薪资排序 CREATE INDEX idx_dept_salary ON employees(department_id, salary); -- 这样查询可以充分利用索引 SELECT * FROM employees WHERE department_id 3 ORDER BY salary DESC;5.3 前缀索引的使用对于文本类型的列如果内容过长可以使用前缀索引减少索引大小。-- 为 email 字段前10个字符创建索引 CREATE INDEX idx_email_prefix ON users(email(10));前缀长度的选择需要平衡索引大小和查询准确性。可以通过计算不同前缀长度的选择性来决策SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) as selectivity_5, COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) as selectivity_10, COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) as selectivity_15 FROM users;5.4 覆盖索引的优势覆盖索引是指索引包含了查询需要的所有列无需回表查询数据行。-- 创建覆盖索引 CREATE INDEX idx_users_cover ON users(name, age, email); -- 这个查询可以直接从索引获取数据无需访问数据行 SELECT name, age FROM users WHERE name 张三;覆盖索引的优势减少磁盘 I/O避免回表操作提升查询性能6. 索引使用的常见误区和问题排查6.1 索引失效的常见场景即使创建了索引某些查询写法可能导致索引失效在索引列上使用函数或表达式-- 索引失效 SELECT * FROM users WHERE UPPER(name) ZHANGSAN; -- 应该改为 SELECT * FROM users WHERE name zhangsan;使用 LIKE 以通配符开头-- 索引失效 SELECT * FROM users WHERE name LIKE %张%; -- 索引可能生效取决于选择性 SELECT * FROM users WHERE name LIKE 张%;对索引列进行运算-- 索引失效 SELECT * FROM users WHERE age 1 30; -- 应该改为 SELECT * FROM users WHERE age 29;使用 OR 连接条件除非所有列都有索引-- 如果 age 没有索引整个查询可能无法使用索引 SELECT * FROM users WHERE name 张三 OR age 25;6.2 索引选择性问题索引的选择性是指索引列中不同值的数量与总行数的比例。选择性越高索引效果越好。低选择性示例不适合建索引性别字段只有2-3个值状态标志只有几个状态值是否删除标志只有2个值计算选择性SELECT COUNT(DISTINCT gender) / COUNT(*) as selectivity FROM users;6.3 索引维护和监控定期检查索引使用情况-- 查看从未使用过的索引 SELECT * FROM sys.schema_unused_indexes WHERE object_schema your_database; -- 查看索引统计信息 ANALYZE TABLE users; SHOW INDEX FROM users;删除无用索引DROP INDEX index_name ON table_name;7. 生产环境中的索引管理策略7.1 索引创建规范在生产环境中创建索引需要谨慎在业务低峰期操作大数据表创建索引可能锁表影响业务。使用在线创建方式MySQL 5.6CREATE INDEX idx_name ON users(name) ALGORITHMINPLACE, LOCKNONE;先测试后上线在测试环境验证索引效果和影响。记录变更记录每个索引的创建目的和使用场景。7.2 索引性能监控建立索引监控机制慢查询日志分析-- 开启慢查询日志 SET GLOBAL slow_query_log 1; SET GLOBAL long_query_time 2; -- 分析慢查询 mysqldumpslow -s t /path/to/slow-query.log性能模式监控-- 查看索引使用统计 SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;定期索引健康检查-- 检查索引碎片率 SELECT TABLE_NAME, INDEX_NAME, ROUND(STATS_PAGES * 100 / NULLIF(LEAF_PAGES, 0), 2) as fragmentation_rate FROM information_schema.INNODB_INDEX_STATS;7.3 索引优化案例实战案例用户查询优化原始表结构CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), email VARCHAR(100), phone VARCHAR(20), created_time DATETIME, status TINYINT );常见查询-- 按姓名和状态查询 SELECT * FROM users WHERE name LIKE 张% AND status 1; -- 按创建时间范围查询 SELECT * FROM users WHERE created_time BETWEEN 2023-01-01 AND 2023-12-31; -- 按邮箱精确查询 SELECT * FROM users WHERE email zhangsanexample.com;优化方案-- 为常用查询创建复合索引 CREATE INDEX idx_users_name_status ON users(name, status); CREATE INDEX idx_users_created ON users(created_time); CREATE UNIQUE INDEX idx_users_email ON users(email); -- 覆盖索引支持常见查询字段 CREATE INDEX idx_users_cover ON users(name, status, email, created_time);7.4 索引问题排查清单当遇到查询性能问题时按以下顺序排查排查步骤检查内容解决方法1. 确认查询是否使用索引EXPLAIN 查看 key 字段优化查询条件或创建合适索引2. 检查索引选择性计算索引列的选择性选择性低的索引考虑删除或重建3. 验证索引有效性检查索引列上的操作是否导致失效重写查询避免函数、运算等4. 评估复合索引顺序确认最左前缀原则是否满足调整索引列顺序或创建新索引5. 检查索引碎片分析索引碎片率优化表或重建索引6. 评估覆盖索引可能性检查查询字段是否都在索引中创建覆盖索引减少回表索引是数据库性能优化的核心手段但需要根据实际查询模式精心设计。一个好的索引策略应该基于对业务查询的深入理解而不是盲目创建大量索引。在生产环境中要建立持续的监控和优化机制确保索引始终服务于真实的业务需求。对于 Java 开发者来说理解数据库索引的工作原理有助于编写更高效的数据库操作代码也能更好地与 DBA 协作进行系统优化。实际项目中建议将索引设计纳入代码审查环节确保数据访问模式与索引策略相匹配。
MySQL索引原理与优化实践：从B+树到Java数据库性能提升
对于很多刚开始接触 Java 和数据库的开发者来说索引这个概念听起来很抽象总觉得是数据库底层高深莫测的机制。但如果你用过字典查字其实你已经理解了索引的核心思想。索引本质上就是一种帮助数据库快速定位数据的数据结构就像字典的拼音或部首检字表让你不用一页一页翻就能找到目标字。在实际 Java Web 项目中数据库查询性能往往是系统瓶颈所在。当数据量达到几十万、上百万时没有索引的查询可能从几毫秒变成几十秒而合理使用索引能让查询速度提升数十倍甚至上百倍。理解索引不仅是为了应对面试中的“八股文”更是为了在实际项目中真正解决性能问题。本文将从查字典的类比出发逐步解释 MySQL 索引的工作原理、类型选择、创建方式、使用注意事项和常见误区。学完后你将掌握如何为表结构设计合适的索引如何验证索引效果以及如何排查索引相关的性能问题。1. 为什么数据库需要索引从查字典说起1.1 没有索引的全表扫描就像翻字典假设你要在一本 1000 页的《现代汉语词典》中查找“数据库”这个词的解释。如果没有拼音索引、部首索引或笔画索引你只能从第一页开始一页一页往后翻直到找到目标词条。这种查找方式在数据库中称为“全表扫描”Full Table Scan。全表扫描的代价与数据量成正比。字典有 1000 页平均需要翻 500 页表有 100 万行数据平均需要扫描 50 万行。当数据量很大时这种线性查找的效率极低。-- 假设 users 表有 100 万行数据没有索引 SELECT * FROM users WHERE name 张三;这个查询需要逐行比较 name 字段的值直到找到所有匹配“张三”的记录。1.2 索引就像字典的检字表字典的拼音索引将汉字按拼音顺序排列每个拼音后面标注对应的页码。查找时先根据拼音定位到大致区域然后直接翻到目标页码附近。数据库索引的工作原理类似它维护一个独立的数据结构其中包含索引字段的值和对应数据行的位置信息。查询时数据库先通过索引快速定位到目标数据的位置然后直接读取这些位置的数据。-- 为 name 字段创建索引后同样的查询效率大幅提升 CREATE INDEX idx_users_name ON users(name); SELECT * FROM users WHERE name 张三;现在数据库会先在 idx_users_name 索引中快速找到“张三”对应的位置然后直接读取这些位置的数据行。1.3 索引的代价空间换时间索引虽然提高了查询速度但也需要付出代价存储空间索引需要额外的磁盘空间来存储索引数据结构。维护成本当数据增删改时索引也需要同步更新会影响写入性能。设计复杂度需要根据查询模式合理设计索引错误的索引可能反而降低性能。合理的索引设计需要在查询性能和写入性能之间找到平衡点。2. MySQL 索引的核心数据结构B树2.1 为什么选择 B树而不是其他数据结构MySQL 最常用的索引类型是基于 B树BTree的。与二叉树、哈希表等数据结构相比B树有以下优势适合磁盘存储B树的节点可以存储多个键值树的高度较低减少磁盘 I/O 次数。范围查询高效B树的叶子节点形成有序链表适合范围查询。数据稳定性B树在增删改时能保持较好的平衡性。2.2 B树的基本结构B树由根节点、中间节点和叶子节点组成根节点树的顶层存储指向中间节点的指针。中间节点存储键值和指向下一层节点的指针。叶子节点存储键值和对应数据行的位置信息聚簇索引存储完整数据行。以字典类比根节点相当于拼音索引的首字母分类A、B、C...中间节点相当于具体拼音ba、bi、bo...叶子节点相当于具体汉字和页码八→p15, 巴→p12...2.3 B树的查找过程假设要在 users 表中查找 name李四 的记录从根节点开始比较“李四”与节点中的键值确定下一步方向。沿着中间节点层层向下逐步缩小范围。到达叶子节点后找到“李四”对应的数据位置。根据位置信息读取完整数据行。这个过程的磁盘 I/O 次数等于树的高度通常只有 3-4 次即使对于上亿条数据的表也是如此。3. MySQL 索引类型及适用场景3.1 主键索引PRIMARY KEY主键索引是一种特殊的唯一索引每个表只能有一个主键索引。CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, email VARCHAR(100) );特点主键值必须唯一且不为 NULL。InnoDB 存储引擎中主键索引是聚簇索引数据按主键顺序存储。建议使用自增整数作为主键避免页分裂带来的性能开销。3.2 唯一索引UNIQUE INDEX唯一索引保证索引列的值必须唯一但允许 NULL 值。CREATE UNIQUE INDEX idx_users_email ON users(email);适用场景邮箱、手机号、身份证号等需要唯一性约束的字段。3.3 普通索引INDEX最基本的索引类型没有唯一性约束。CREATE INDEX idx_users_name ON users(name);适用场景经常作为查询条件的字段但不需要唯一性约束。3.4 复合索引Composite Index包含多个列的索引也称为联合索引。CREATE INDEX idx_users_name_age ON users(name, age);复合索引遵循最左前缀原则查询必须使用索引的最左列才能生效。有效使用复合索引的查询SELECT * FROM users WHERE name 张三; -- 使用索引 SELECT * FROM users WHERE name 张三 AND age 25; -- 使用索引无法使用复合索引的查询SELECT * FROM users WHERE age 25; -- 没有使用最左列 name3.5 全文索引FULLTEXT INDEX专门用于文本内容的全文搜索。CREATE FULLTEXT INDEX idx_articles_content ON articles(content);使用 MATCH AGAINST 进行全文搜索SELECT * FROM articles WHERE MATCH(content) AGAINST(数据库 索引 IN NATURAL LANGUAGE MODE);4. 索引的创建和使用实践4.1 创建索引的语法基本创建语法CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (column1, column2, ...);修改表结构添加索引ALTER TABLE table_name ADD [UNIQUE|FULLTEXT] INDEX index_name (column1, column2, ...);创建表时直接定义索引CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), email VARCHAR(100), INDEX idx_name (name), UNIQUE INDEX idx_email (email) );4.2 查看索引信息查看表的索引信息SHOW INDEX FROM users;结果包含以下重要信息Table: 表名Non_unique: 是否唯一0唯一1不唯一Key_name: 索引名称Seq_in_index: 索引中的列序号Column_name: 列名Cardinality: 基数索引中唯一值的数量估算4.3 使用 EXPLAIN 分析查询执行计划EXPLAIN 命令可以显示 MySQL 如何执行查询是优化查询的重要工具。EXPLAIN SELECT * FROM users WHERE name 张三;关键字段解释type: 访问类型const、eq_ref、ref、range、index、ALLpossible_keys: 可能使用的索引key: 实际使用的索引rows: 预估需要扫描的行数Extra: 额外信息Using where、Using index等4.4 索引使用情况验证检查索引是否被使用-- 开启性能模式如果需要 SET SESSION profiling 1; -- 执行查询 SELECT * FROM users WHERE name 张三; -- 查看查询详情 SHOW PROFILES; SHOW PROFILE FOR QUERY 1;5. 索引设计的最佳实践5.1 选择合适的索引列应该创建索引的列经常出现在 WHERE 子句中的列经常用于连接JOIN的列经常用于排序ORDER BY的列经常用于分组GROUP BY的列不应该创建索引的列数据重复度高的列如性别、状态标志很少用于查询的列文本内容过长的列考虑使用前缀索引5.2 复合索引的列顺序原则设计复合索引时列的顺序很重要区分度高的列放在前面选择性高的列能更快缩小查询范围。等值查询列在前范围查询列在后等值查询的列应该放在范围查询、、BETWEEN的列前面。经常排序的列考虑放在索引中如果查询需要排序可以考虑将排序列加入索引。示例-- 假设经常按部门查询并按薪资排序 CREATE INDEX idx_dept_salary ON employees(department_id, salary); -- 这样查询可以充分利用索引 SELECT * FROM employees WHERE department_id 3 ORDER BY salary DESC;5.3 前缀索引的使用对于文本类型的列如果内容过长可以使用前缀索引减少索引大小。-- 为 email 字段前10个字符创建索引 CREATE INDEX idx_email_prefix ON users(email(10));前缀长度的选择需要平衡索引大小和查询准确性。可以通过计算不同前缀长度的选择性来决策SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) as selectivity_5, COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) as selectivity_10, COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) as selectivity_15 FROM users;5.4 覆盖索引的优势覆盖索引是指索引包含了查询需要的所有列无需回表查询数据行。-- 创建覆盖索引 CREATE INDEX idx_users_cover ON users(name, age, email); -- 这个查询可以直接从索引获取数据无需访问数据行 SELECT name, age FROM users WHERE name 张三;覆盖索引的优势减少磁盘 I/O避免回表操作提升查询性能6. 索引使用的常见误区和问题排查6.1 索引失效的常见场景即使创建了索引某些查询写法可能导致索引失效在索引列上使用函数或表达式-- 索引失效 SELECT * FROM users WHERE UPPER(name) ZHANGSAN; -- 应该改为 SELECT * FROM users WHERE name zhangsan;使用 LIKE 以通配符开头-- 索引失效 SELECT * FROM users WHERE name LIKE %张%; -- 索引可能生效取决于选择性 SELECT * FROM users WHERE name LIKE 张%;对索引列进行运算-- 索引失效 SELECT * FROM users WHERE age 1 30; -- 应该改为 SELECT * FROM users WHERE age 29;使用 OR 连接条件除非所有列都有索引-- 如果 age 没有索引整个查询可能无法使用索引 SELECT * FROM users WHERE name 张三 OR age 25;6.2 索引选择性问题索引的选择性是指索引列中不同值的数量与总行数的比例。选择性越高索引效果越好。低选择性示例不适合建索引性别字段只有2-3个值状态标志只有几个状态值是否删除标志只有2个值计算选择性SELECT COUNT(DISTINCT gender) / COUNT(*) as selectivity FROM users;6.3 索引维护和监控定期检查索引使用情况-- 查看从未使用过的索引 SELECT * FROM sys.schema_unused_indexes WHERE object_schema your_database; -- 查看索引统计信息 ANALYZE TABLE users; SHOW INDEX FROM users;删除无用索引DROP INDEX index_name ON table_name;7. 生产环境中的索引管理策略7.1 索引创建规范在生产环境中创建索引需要谨慎在业务低峰期操作大数据表创建索引可能锁表影响业务。使用在线创建方式MySQL 5.6CREATE INDEX idx_name ON users(name) ALGORITHMINPLACE, LOCKNONE;先测试后上线在测试环境验证索引效果和影响。记录变更记录每个索引的创建目的和使用场景。7.2 索引性能监控建立索引监控机制慢查询日志分析-- 开启慢查询日志 SET GLOBAL slow_query_log 1; SET GLOBAL long_query_time 2; -- 分析慢查询 mysqldumpslow -s t /path/to/slow-query.log性能模式监控-- 查看索引使用统计 SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;定期索引健康检查-- 检查索引碎片率 SELECT TABLE_NAME, INDEX_NAME, ROUND(STATS_PAGES * 100 / NULLIF(LEAF_PAGES, 0), 2) as fragmentation_rate FROM information_schema.INNODB_INDEX_STATS;7.3 索引优化案例实战案例用户查询优化原始表结构CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), email VARCHAR(100), phone VARCHAR(20), created_time DATETIME, status TINYINT );常见查询-- 按姓名和状态查询 SELECT * FROM users WHERE name LIKE 张% AND status 1; -- 按创建时间范围查询 SELECT * FROM users WHERE created_time BETWEEN 2023-01-01 AND 2023-12-31; -- 按邮箱精确查询 SELECT * FROM users WHERE email zhangsanexample.com;优化方案-- 为常用查询创建复合索引 CREATE INDEX idx_users_name_status ON users(name, status); CREATE INDEX idx_users_created ON users(created_time); CREATE UNIQUE INDEX idx_users_email ON users(email); -- 覆盖索引支持常见查询字段 CREATE INDEX idx_users_cover ON users(name, status, email, created_time);7.4 索引问题排查清单当遇到查询性能问题时按以下顺序排查排查步骤检查内容解决方法1. 确认查询是否使用索引EXPLAIN 查看 key 字段优化查询条件或创建合适索引2. 检查索引选择性计算索引列的选择性选择性低的索引考虑删除或重建3. 验证索引有效性检查索引列上的操作是否导致失效重写查询避免函数、运算等4. 评估复合索引顺序确认最左前缀原则是否满足调整索引列顺序或创建新索引5. 检查索引碎片分析索引碎片率优化表或重建索引6. 评估覆盖索引可能性检查查询字段是否都在索引中创建覆盖索引减少回表索引是数据库性能优化的核心手段但需要根据实际查询模式精心设计。一个好的索引策略应该基于对业务查询的深入理解而不是盲目创建大量索引。在生产环境中要建立持续的监控和优化机制确保索引始终服务于真实的业务需求。对于 Java 开发者来说理解数据库索引的工作原理有助于编写更高效的数据库操作代码也能更好地与 DBA 协作进行系统优化。实际项目中建议将索引设计纳入代码审查环节确保数据访问模式与索引策略相匹配。 
