1. 索引数据库性能的“导航系统”如果你用过纸质地图或者手机导航应该能理解索引在数据库里的角色。想象一下你要在一本没有目录、页码混乱的百科全书里找“光合作用”这个词条唯一的办法就是从第一页开始逐页翻找这效率低得令人绝望。数据库里的表在没有索引的情况下查询数据就是这种“全表扫描”的体验。索引本质上就是一种为了快速找到数据而创建的有序数据结构它就像那本百科全书的目录或者地图上的坐标网格能让你直接定位到目标数据所在的大致位置从而避免低效的全表遍历。在MySQL中索引主要建立在表的列上。当你为一个经常用于查询条件WHERE子句、排序ORDER BY或连接JOIN的列创建索引后MySQL会维护一个额外的、体积更小的“导航表”。这个导航表里存储了该列的值以及对应数据行的物理位置指针。当你执行查询时MySQL会优先去这个导航表里查找快速拿到地址再去主数据表里取出完整的行数据。这个“导航表”就是索引它用额外的存储空间和少量的维护成本增删改数据时需要同步更新索引换来了查询性能几个数量级的提升。所以学会查看索引是数据库管理和性能优化的第一步。你不仅要知道表上有没有索引更要清楚有哪些索引、它们建立在哪些列上、是什么类型、效果如何。这能帮你诊断慢查询评估现有索引设计是否合理以及为后续的索引优化提供决策依据。无论是开发、测试还是运维同学这都是必须掌握的日常技能。2. 核心查看命令从宏观到微观的探查查看索引不是单一命令而是一套组合拳。你需要从数据库、表、再到索引本身层层深入。最常用、最核心的工具就是SHOW语句和INFORMATION_SCHEMA系统数据库。2.1 使用 SHOW 语句快速概览SHOW语句是MySQL提供的快捷命令语法简单返回结果直观适合快速检查和日常巡检。2.1.1 查看特定表的索引SHOW INDEX这是最直接的方法。假设我们有一个名为users的表想看看它上面有哪些索引SHOW INDEX FROM users; -- 或者使用简写 SHOW INDEX FROM users\G使用\G代替分号会让结果以垂直格式显示在终端里阅读长记录时更清晰。这条命令会返回一个结果集包含以下关键列Table: 表名。Non_unique: 索引是否允许重复值。0代表唯一索引如主键1代表非唯一索引。Key_name: 索引的名称。主键索引的名字固定为PRIMARY。Seq_in_index: 该列在索引中的位置对于复合索引非常重要。从1开始计数。Column_name: 建立索引的列名。Collation: 列在索引中的排序方式。A表示升序NULL表示未排序如全文索引。Cardinality:基数。这是一个非常重要的估算值表示索引中不重复值的数量。基数越高越接近表的总行数该索引的选择性就越好查询时利用索引的效率通常也越高。注意这是一个采样统计值并非实时精确值有时需要运行ANALYZE TABLE命令来更新。Sub_part: 索引的前缀长度。如果只为列的前N个字符创建了索引前缀索引这里会显示N否则为NULL。Packed: 指示键如何被压缩NULL表示未压缩。Null: 该列是否允许存储NULL值。Index_type: 索引的类型最常见的是BTREEB树还有HASH,FULLTEXT全文,SPATIAL空间等。Comment: 索引的备注信息。通过SHOW INDEX你可以一目了然地看到所有索引的构成。比如看到一个Key_name为idx_email_status的索引其Seq_in_index分别为1和2Column_name分别为email和status你就知道这是一个在(email, status)列上创建的复合索引。2.1.2 查看创建表的语句SHOW CREATE TABLE这条命令能展示出创建该表的完整SQL语句其中自然包含了索引的定义。它对于理解表结构和索引的创建方式特别有用。SHOW CREATE TABLE users;输出结果中的CREATE TABLE语句会明确显示PRIMARY KEY、UNIQUE KEY、KEY或INDEX等子句。这种方式让你在“上下文”中看到索引有时比单纯的列表更易于理解索引与表结构的关系。2.2 查询 INFORMATION_SCHEMA 获取元数据INFORMATION_SCHEMA是MySQL的一个系统数据库它提供了访问数据库元数据的标准化SQL接口。相比SHOW命令使用SQL查询INFORMATION_SCHEMA更灵活可以进行过滤、连接和聚合操作适合在脚本或复杂分析中使用。核心的表是STATISTICS它存储了索引的统计信息。2.2.1 基础查询示例查询指定数据库如mydb中指定表如users的索引信息SELECT TABLE_SCHEMA AS 数据库, TABLE_NAME AS 表名, NON_UNIQUE AS 是否非唯一, INDEX_NAME AS 索引名, SEQ_IN_INDEX AS 列序号, COLUMN_NAME AS 列名, CARDINALITY AS 基数, INDEX_TYPE AS 索引类型 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA mydb AND TABLE_NAME users ORDER BY INDEX_NAME, SEQ_IN_INDEX;2.2.2 进阶分析与实战技巧INFORMATION_SCHEMA的强大之处在于可以轻松进行跨表分析。例如作为一名DBA你可能想快速找出整个数据库中所有未使用的索引一个常见的性能优化点。虽然MySQL没有直接记录索引使用次数但我们可以结合STATISTICS表和慢查询日志分析或者使用sys库MySQL 5.7。这里举一个利用INFORMATION_SCHEMA分析索引冗余的例子假设你想找出那些前缀完全相同的冗余索引例如已有索引(A, B)又创建了(A, B, C)前者可能冗余。这可以通过自连接查询来实现SELECT s1.TABLE_SCHEMA, s1.TABLE_NAME, s1.INDEX_NAME AS 可能冗余的索引, s2.INDEX_NAME AS 可能覆盖它的索引, GROUP_CONCAT(s1.COLUMN_NAME ORDER BY s1.SEQ_IN_INDEX) AS 冗余索引列顺序 FROM INFORMATION_SCHEMA.STATISTICS s1 JOIN INFORMATION_SCHEMA.STATISTICS s2 ON s1.TABLE_SCHEMA s2.TABLE_SCHEMA AND s1.TABLE_NAME s2.TABLE_NAME AND s1.INDEX_NAME ! s2.INDEX_NAME AND s1.COLUMN_NAME s2.COLUMN_NAME AND s1.SEQ_IN_INDEX s2.SEQ_IN_INDEX WHERE s1.TABLE_SCHEMA mydb GROUP BY s1.TABLE_SCHEMA, s1.TABLE_NAME, s1.INDEX_NAME, s2.INDEX_NAME HAVING COUNT(*) (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS s3 WHERE s3.TABLE_SCHEMA s1.TABLE_SCHEMA AND s3.TABLE_NAME s1.TABLE_NAME AND s3.INDEX_NAME s1.INDEX_NAME);这个查询稍复杂但它展示了如何利用元数据进行深度分析。在实际操作中更推荐使用Percona Toolkit中的pt-duplicate-key-checker工具来做这件事它更专业和全面。注意直接查询INFORMATION_SCHEMA在某些情况下尤其是表非常多时可能会对性能有轻微影响因为它需要访问系统表。在生产环境做大规模元数据查询时建议在业务低峰期进行。3. 图形化工具直观管理的利器对于不习惯命令行或者需要更直观、更高效管理多数据库实例的开发者或DBA图形化客户端是绝佳选择。它们将索引信息以可视化的形式呈现大大提升了可读性和操作效率。3.1 MySQL Workbench官方全能选手MySQL Workbench是MySQL官方的集成环境功能强大。查看索引的路径通常是连接到数据库服务器。在左侧“Navigator”面板的“Schemas”选项卡下找到你的数据库并展开。展开“Tables”找到目标表如users。右键点击该表选择 “Alter Table...”。在弹出的表结构编辑器中切换到 “Indexes” 标签页。在这里你会看到一个清晰的列表显示所有索引的名称、类型、包含的列以及排序规则。你不仅可以查看还可以直接在此界面添加、修改或删除索引操作非常直观。Workbench还会在界面下方显示生成对应操作的SQL脚本这对于学习SQL语法也很有帮助。3.2 Navicat、DBeaver等第三方工具像Navicat、DBeaver、DataGrip这类流行的第三方数据库管理工具在索引可视化方面做得同样出色且各有特色。以DBeaver为例连接数据库后在数据库导航树中展开表。你会发现表下面直接有一个 “Indexes” 的子节点点击它右侧主窗口就会列出所有索引的详细信息。很多工具还支持直接拖拽列来创建索引或者通过图形化界面设置索引类型、方法等属性。图形化工具的优势在于“所见即所得”特别适合进行索引的对比和设计。你可以同时打开两个表的结构进行对比或者快速浏览一个数据库中所有表的索引概况。对于团队协作和知识沉淀将表结构含索引通过这些工具导出为ER图或PDF文档也是常见的做法。4. 解读索引信息从看到懂的关键步骤拿到了索引的列表信息只是第一步就像医生拿到了化验单关键是要能看懂各项指标的含义并做出诊断。这里有几个需要重点关注的字段和它们的实战意义。4.1 理解“基数”Cardinality与索引选择性Cardinality可能是SHOW INDEX结果中最重要也最容易被误解的字段。它表示索引列中不重复值的估算数量。这个值不是实时更新的而是MySQL通过采样统计得来的。高基数接近表行数例如一个存储用户邮箱的UNIQUE列其基数应该等于总行数。这意味着索引选择性极高通过该索引能快速定位到极少甚至唯一的行索引效率非常高。低基数远小于表行数例如一个gender列只有‘M’和‘F’两种值。即使有10万行数据其基数也只有2。为这种低选择性的列创建独立索引通常意义不大因为通过索引筛选后仍然要回表读取大量数据行。如何利用基数判断索引有效性如果一个索引的基数非常低你需要思考它是否真的被有效用于查询加速。它可能只在某些特定值的查询如WHERE gender F时与另一个筛选性强的列组成复合索引才有用。更新统计信息如果发现基数值很久没变或者明显失真例如表数据已增长十倍但基数未变可以使用ANALYZE TABLE table_name;命令来更新统计信息让优化器能做出更准确的执行计划选择。4.2 识别索引类型与组合Key_name和Index_typePRIMARY代表主键索引一种特殊的唯一索引。Index_type为BTREE是最常见的适用于等值查询和范围查询。FULLTEXT用于全文搜索HASH用于内存表。复合索引与列顺序Seq_in_index这是优化索引设计的核心。一个名为idx_a_b_c的索引如果Seq_in_index显示为1(a), 2(b), 3(c)那么它遵循最左前缀匹配原则。这意味着查询条件必须包含a才能用到这个索引。WHERE a1 AND b2能用上WHERE b2 AND c3则用不上。理解这一点对于编写高效SQL和创建合理索引至关重要。4.3 检查潜在问题冗余、重复与碎片通过查看索引列表你可以手动发现一些常见问题重复索引指在相同的列集合上以相同的顺序创建了多个索引。例如既有INDEX (A)又有INDEX (A)这完全是浪费。但需注意INDEX (A)和UNIQUE INDEX (A)在功能上是不同的后者约束唯一性不算严格重复。冗余索引指一个索引的功能可以被另一个已存在的索引覆盖。最常见的情况是已经有了复合索引(A, B)然后又创建了一个单列索引(A)。因为复合索引(A, B)的前缀就是(A)所以单列索引(A)通常是冗余的。但反过来有(A)再建(A, B)则不是冗余因为后者提供了额外的列B用于覆盖查询或排序。索引碎片SHOW INDEX命令不直接显示碎片率。但你可以通过查询INFORMATION_SCHEMA.TABLES中的DATA_FREE列或者使用SHOW TABLE STATUS LIKE table_name来查看数据碎片情况。对于频繁更新的表索引碎片化会降低查询性能。定期使用OPTIMIZE TABLE table_name;对于InnoDB它等价于ALTER TABLE ... FORCE可以重建表并整理碎片但这是一个重量级操作会锁表需要在维护窗口进行。5. 性能库Performance Schema sys Schema洞察索引使用情况知道有哪些索引后一个更高级的问题是这些索引真的被用到了吗创建了却不使用的索引是“死索引”它们白白占用磁盘空间并在每次数据写入INSERT/UPDATE/DELETE时带来不必要的维护开销必须坚决清理。MySQL提供了强大的性能监控库来回答这个问题。5.1 Performance Schema底层数据收集器Performance SchemaP_S是MySQL内置的一个性能数据收集引擎它提供了大量关于服务器运行时状态的底层指标。其中table_io_waits_summary_by_index_usage表记录了每个索引的I/O等待事件统计这可以作为索引使用情况的强力参考。SELECT OBJECT_SCHEMA AS 数据库, OBJECT_NAME AS 表名, INDEX_NAME AS 索引名, COUNT_FETCH AS 读取次数, COUNT_INSERT AS 插入次数, COUNT_UPDATE AS 更新次数, COUNT_DELETE AS 删除次数 FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA mydb AND INDEX_NAME IS NOT NULL -- 排除全表扫描的记录 ORDER BY (COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE) ASC;如果某个索引的各类操作计数尤其是COUNT_FETCH长期为0或极低而表本身又有一定的查询量那么这个索引就非常可疑了。但请注意P_S中的数据是服务器启动后累积的如果服务器刚重启数据可能不具代表性。另外一些非常轻量级的查询可能不会触发等待事件统计。5.2 sys Schema人性化的视图sys Schema是基于Performance Schema和Information Schema构建的一系列视图、函数和存储过程它将底层的性能数据转换成了更易于人类理解的格式。对于查看索引使用情况sys库是更推荐的工具。一个非常有用的视图是schema_unused_indexesSELECT * FROM sys.schema_unused_indexes WHERE object_schema mydb;这个视图会列出自上次服务器启动以来从未被使用过的索引。它的判断逻辑主要基于Performance Schema中的索引访问事件。结果非常直观直接给出了“冗余索引”的候选名单。5.3 使用建议与陷阱数据积累期无论是P_S还是sys都需要服务器运行一段时间积累足够的操作数据后其判断才准确。刚重启后立即查询没有意义。读写分离环境在读写分离架构中只在从库上执行读操作。因此在从库上看到的索引使用情况可能无法反映主库写库上索引的使用情况。有些索引可能专为报表类复杂查询而建这些查询只在从库跑。所以分析时需要结合具体架构。并非绝对真理sys.schema_unused_indexes是一个极好的参考但并非圣旨。在删除一个索引前务必结合业务逻辑确认这个索引是否为季度报表、月度统计等低频但重要的查询服务它是否是一个唯一约束索引其存在是为了保证数据完整性而非查询性能是否可能在某些异常或恢复流程中被用到 最稳妥的做法是先将怀疑的索引标记为INVISIBLEMySQL 8.0 支持观察一段时间业务和监控是否有异常确认无误后再执行DROP INDEX。6. 结合执行计划EXPLAIN进行深度验证查看索引的终极目的是为了让查询语句能高效地使用它们。EXPLAIN命令就是让你看到MySQL优化器最终决定如何使用或不用索引来执行某条查询的“执行计划”。它是验证索引效果、诊断慢查询的黄金工具。6.1 如何使用 EXPLAIN在你要分析的SELECT语句前加上EXPLAIN关键字即可。EXPLAIN SELECT * FROM users WHERE email userexample.com AND status active;或者使用更详细的格式MySQL 8.0.18EXPLAIN FORMATTREE SELECT * FROM users ...; -- 或 EXPLAIN ANALYZE SELECT * FROM users ...; -- MySQL 8.0.18会实际执行并给出耗时6.2 解读关键字段关联索引使用EXPLAIN输出结果中有几个字段与索引使用直接相关需要重点关注type访问类型从好到坏大致是systemconsteq_refrefrangeindexALL。我们的目标是让查询至少达到range级别避免出现ALL全表扫描。const/eq_ref通过主键或唯一索引进行等值查询性能最佳。ref使用非唯一索引进行等值查询。range使用索引进行范围查询BETWEEN, , , IN等。index索引全扫描比全表扫描好一点但也是遍历整个索引树。ALL灾难性的全表扫描必须优化。possible_keys查询可能使用到的索引。这是优化器根据查询条件和表结构初步判断出来的。key查询实际决定使用的索引。如果为NULL则表示未使用索引。这是最重要的字段之一。key_len使用的索引的长度字节数。通过这个值可以反推实际使用了复合索引的哪些部分。例如一个INT列4字节加上可为NULL1字节的索引如果key_len5说明这个索引被完全使用。如果复合索引(a int, b varchar(10))的key_len只有4说明只用了a列。rowsMySQL预估为了找到所需的行需要扫描的行数。这是一个估算值但非常有用。结合key字段如果使用了索引但rows值仍然很大可能意味着索引选择性不高。Extra包含额外的执行信息。一些重要提示Using index表示使用了覆盖索引即查询的列全部包含在索引中无需回表读取数据行性能极佳。Using where表示在存储引擎检索行后服务器层还需要进行额外的过滤。如果type是ALL且出现Using where说明性能很差。Using filesort表示MySQL需要额外的一次排序操作无法利用索引的有序性。对于ORDER BY子句这是一个需要关注的信号。Using temporary表示需要创建临时表来处理查询常见于GROUP BY和DISTINCT性能开销大。6.3 实战案例诊断未使用索引的查询假设我们有一个orders表在customer_id和order_date上有一个复合索引idx_customer_date。执行以下查询EXPLAIN SELECT * FROM orders WHERE order_date 2023-01-01 ORDER BY customer_id;如果key显示为NULLtype为ALL说明发生了全表扫描。为什么因为复合索引(customer_id, order_date)遵循最左前缀原则。查询条件order_date不是索引的最左列因此无法有效利用该索引。优化方案可能是1) 调整查询条件使其包含customer_id2) 或者为order_date单独创建一个索引如果这种查询模式很常见3) 调整索引顺序为(order_date, customer_id)但这需要评估其他查询的影响。通过EXPLAIN我们不仅看到了“有没有用索引”更看到了“怎么用的索引”从而能进行精准的优化。