1. 项目概述为什么我们需要查看索引在数据库的日常运维和性能调优中索引是绕不开的核心话题。想象一下你走进一个巨大的图书馆里面有几百万本书但没有目录卡片也没有按字母顺序排列的书架。你要找一本特定的书唯一的办法就是一本一本地翻看。这听起来很荒谬对吧但在数据库里如果没有索引查询数据的过程就和这个场景一模一样——它需要进行全表扫描效率极其低下。我处理过太多因为索引问题导致的线上慢查询轻则页面加载缓慢重则直接拖垮整个数据库。很多开发者尤其是刚入行的朋友建表时凭感觉加几个索引上线后发现问题却不知道如何系统地查看和分析现有的索引结构。他们可能会问“我这个表到底有哪些索引”“哪些索引是有效的哪些又是冗余的”“为什么我明明加了索引查询还是慢”这就是我们今天要解决的问题。“MySQL 如何查看表和数据库索引”这不仅仅是一个简单的命令查询而是一套完整的诊断和分析流程。它关乎你能否清晰地洞察数据库的“骨架”理解查询执行的路径并最终做出正确的优化决策。无论是排查一个突发的性能问题还是进行常规的健康检查掌握查看索引的方法都是数据库从业者的基本功。接下来我会带你从最基础的命令开始逐步深入到原理和实战分析让你不仅能“看到”索引更能“看懂”索引。2. 核心工具与命令全解析查看索引我们主要依赖两个强大的 SQL 命令SHOW INDEX和查询信息模式表INFORMATION_SCHEMA.STATISTICS。它们各有侧重一个方便快捷一个信息全面灵活。2.1 SHOW INDEX快速诊断的利器SHOW INDEX命令是 MySQL 内置的用于快速查看特定表索引情况的最直接工具。它的语法非常简单SHOW INDEX FROM your_table_name; -- 或者 SHOW INDEX FROM your_table_name FROM your_database_name;执行这条命令后你会得到一个结构化的结果集。我们以一个用户表user为例假设它有主键id、一个在username上的唯一索引和一个在email上的普通索引。执行SHOW INDEX FROM user;后我们来逐列解读这个结果这比单纯看输出更重要Table:表名。这很直观。Non_unique:索引是否允许重复值。这是关键信息。0代表唯一索引如主键、UNIQUE约束1代表非唯一索引。Key_name:索引的名称。主键索引的名字固定为PRIMARY。这是你后续操作如删除索引时需要引用的标识。Seq_in_index:该列在复合索引多列索引中的位置从1开始计数。对于单列索引这个值总是1。通过这个字段你可以清晰地看出复合索引的列顺序顺序是复合索引的灵魂。Column_name:构成索引的列名。Collation:列在索引中的排序方式。A表示升序NULL表示未排序如全文索引。通常我们见到的都是A。Cardinality:这是一个极其重要的估算值。它表示索引中不重复值的数量的估计值。这个值不是实时精确的而是由存储引擎采样估算的。Cardinality 与表总行数的比值直接反映了索引的选择性。选择性越高越接近1索引过滤数据的能力就越强。一个性别字段的索引Cardinality 可能只有2男/女选择性极差而用户ID的索引Cardinality 几乎等于总行数选择性极高。优化器非常依赖这个值来决定是否使用该索引。Sub_part:索引前缀长度。如果索引只使用了列值的前N个字符例如INDEX (email(10))这里会显示10。如果是整列索引则为NULL。使用前缀索引可以节省空间但会影响排序和覆盖索引查询。Packed:指示键值如何被压缩NULL表示未压缩。Null:该列是否允许存储NULL值。YES或。Index_type:索引的类型。最常见的是BTREEB树这也是 InnoDB 默认的索引结构。还可能见到FULLTEXT全文索引、HASHMemory引擎等。Comment:索引的备注信息可能包含一些额外的说明。Index_comment:创建索引时通过COMMENT子句添加的注释。实操心得SHOW INDEX的输出结果Cardinality列最值得关注。一个长期未更新的表其Cardinality可能严重失准导致优化器做出错误判断。如果你怀疑索引失效可以尝试对表执行ANALYZE TABLE your_table_name;来更新统计信息。另外通过Key_name和Seq_in_index你可以一眼看出哪些是复合索引以及它们的列顺序这对于理解索引的最左前缀匹配原则至关重要。2.2 INFORMATION_SCHEMA.STATISTICS元数据的宝库如果说SHOW INDEX是给你一张快照那么查询INFORMATION_SCHEMA.STATISTICS系统表就是给了你整个底片库。INFORMATION_SCHEMA是 MySQL 的一个数据库里面存储了关于所有其他数据库、表、列、索引等元数据信息。STATISTICS表提供了比SHOW INDEX更底层、更丰富的信息并且因为它是张标准的表你可以用SELECT语句进行灵活的过滤、连接和聚合查询这在大规模数据库管理中非常有用。一个基础的查询示例SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA ‘your_database_name‘ AND TABLE_NAME ‘your_table_name‘ ORDER BY INDEX_NAME, SEQ_IN_INDEX;这条查询会返回指定库和表的所有索引信息结果字段与SHOW INDEX类似但更全面。比如它包含了INDEX_SCHEMA数据库名让你在跨库查询时更方便。它的强大之处在于灵活性批量分析整个数据库的索引情况-- 查找数据库中所有未被使用的冗余索引通过Cardinality为0或很小初步判断需结合查询日志确认 SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, COLUMN_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA NOT IN (‘mysql‘, ‘information_schema‘, ‘performance_schema‘, ‘sys‘) AND CARDINALITY 0;查找包含特定列的所有索引-- 当你打算修改某个列的数据类型时可以先看看它被哪些索引引用 SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE COLUMN_NAME ‘email‘;统计每个表的索引数量和总大小需结合TABLES表-- 这是一个进阶查询可以大致了解索引的存储开销 SELECT t.TABLE_SCHEMA, t.TABLE_NAME, COUNT(DISTINCT s.INDEX_NAME) as index_count, SUM(t.DATA_LENGTH t.INDEX_LENGTH) / 1024 / 1024 as total_size_mb FROM INFORMATION_SCHEMA.TABLES t LEFT JOIN INFORMATION_SCHEMA.STATISTICS s ON (t.TABLE_SCHEMA s.TABLE_SCHEMA AND t.TABLE_NAME s.TABLE_NAME) WHERE t.TABLE_SCHEMA ‘your_database‘ GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME;注意事项直接查询INFORMATION_SCHEMA在某些情况下尤其是表非常多时可能会对性能有轻微影响因为它需要访问元数据。不建议在业务高峰期频繁执行复杂的关联查询。但对于离线分析、健康检查报告生成等场景它是无可替代的工具。3. 实战从查看索引到性能分析知道了怎么看下一步就是看懂并用于分析。我们通过一个完整的实战案例来串联。3.1 案例背景与初始探查假设我们有一个电商订单表orders结构简化如下CREATE TABLE orders ( id bigint(20) NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL COMMENT ‘订单号‘, user_id int(11) NOT NULL, amount decimal(10,2) NOT NULL, status tinyint(4) NOT NULL DEFAULT ‘0‘ COMMENT ‘状态0待支付1已支付2已发货3已完成4已取消‘, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, pay_time datetime DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_create_time (create_time), KEY idx_status (status), KEY idx_user_status (user_id,status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;开发同学反馈有一个查询用户最近订单的接口变慢了。查询语句大概是SELECT * FROM orders WHERE user_id 123 AND status 1 ORDER BY create_time DESC LIMIT 10;首先我们用SHOW INDEX看看这个表的索引情况SHOW INDEX FROM orders;输出会列出我们建表时定义的所有索引PRIMARY(id),uk_order_no(order_no),idx_user_id(user_id),idx_create_time(create_time),idx_status(status),idx_user_status(user_id, status)。3.2 结合 EXPLAIN 进行深度诊断仅仅看索引列表是不够的我们需要知道 MySQL 优化器在执行上述慢查询时实际选择了哪个索引以及为什么这么选。这就需要用到EXPLAIN命令。在慢查询语句前加上EXPLAINEXPLAIN SELECT * FROM orders WHERE user_id 123 AND status 1 ORDER BY create_time DESC LIMIT 10;我们来解读关键字段type: 访问类型。这是衡量查询效率的核心。常见的有const/eq_ref: 最佳通过主键或唯一索引一次找到。ref: 使用非唯一索引进行等值查找。我们期望看到这个。range: 使用索引进行范围查找BETWEEN, IN, , 。index: 全索引扫描比全表扫描快因为只读索引树。ALL: 全表扫描最差情况。 我们的查询user_id和status都是等值查询理想情况下应该是ref。possible_keys: 可能用到的索引。这里应该会列出idx_user_id,idx_status,idx_user_status。key:优化器实际选择的索引。这是最重要的信息之一。它可能选择idx_user_id也可能选择idx_user_status。key_len: 使用的索引的长度字节数。通过这个值可以反推使用了复合索引的哪些部分。例如如果key_len是 4user_idint 占4字节说明只用了idx_user_status的第一列。如果是 541status是 tinyint说明两列都用了。rows: 预估需要扫描的行数。基于索引的Cardinality估算。这个值越接近实际返回的行数本例是10说明索引选择性越好估算越准。Extra: 额外信息。这里需要重点关注Using where: 表示在存储引擎检索行后MySQL 服务器层再次进行了过滤。如果我们的WHERE条件能完全被索引覆盖这里可能不会出现。Using index: 表示使用了覆盖索引即查询的列全部包含在索引中无需回表。我们的查询是SELECT *所以不可能出现这个。Using filesort:这是一个危险信号表示 MySQL 无法利用索引完成排序需要额外的排序步骤可能在内存或磁盘。我们的查询有ORDER BY create_time DESC如果选择的索引不包含create_time列就很可能出现Using filesort这正是性能杀手。假设EXPLAIN结果显示key是idx_user_status但Extra里有Using filesort。这说明虽然索引帮助快速过滤了user_id和status但排序字段create_time不在索引中导致需要额外的排序操作。3.3 索引优化方案设计与验证基于以上分析问题根因是现有的索引idx_user_status (user_id, status)无法覆盖ORDER BY create_time的需求。一个直接的优化思路是创建一个包含排序字段的复合索引。但怎么建这里有几种方案方案A创建(user_id, status, create_time)索引。优点这是一个完美的“三星索引”。第一星WHEREuser_id和status作为等值条件放在最左。第二星ORDER BYcreate_time作为排序字段紧接其后可以利用索引的有序性避免filesort。第三星覆盖索引虽然我们的查询是SELECT *但如果未来有只查询这几列的语句就能实现覆盖索引。缺点索引列增加了写入开销会略微增大。同时原有的idx_user_status索引可能变得冗余因为新索引的前缀(user_id, status)功能完全覆盖了它。方案B仅依赖idx_user_id并期望status过滤掉的行不多。分析如果status1已支付的订单占所有订单的比例很小即选择性高那么使用idx_user_id先快速定位到该用户的所有订单再在内存中过滤status并排序可能也不错。但这依赖于数据分布不稳定。显然方案A更优。我们来实施并验证首先创建新索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);然后删除可能冗余的旧索引务必先确认该索引没有其他查询使用-- 使用 PERFORMANCE_SCHEMA 或慢查询日志确认 idx_user_status 是否还被使用 -- 确认无误后删除 DROP INDEX idx_user_status ON orders;再次执行EXPLAINEXPLAIN SELECT * FROM orders WHERE user_id 123 AND status 1 ORDER BY create_time DESC LIMIT 10;理想的输出应该是type: refkey: idx_user_status_timekey_len: 5 (表明使用了user_id和status)Extra:Using index condition(如果存在) 且Using filesort消失Using filesort的消失意味着排序操作通过遍历有序的索引叶子节点即可完成性能得到质的提升。踩坑记录在一次优化中我创建了(status, create_time)索引试图优化一个按状态和时间排序的查询。但status的选择性非常差就几个枚举值导致优化器根本不用这个索引还是选择了全表扫描。教训是复合索引的首列选择性一定要高否则整个索引可能失效。在(user_id, status, create_time)中user_id的选择性通常远高于status所以把它放在首位是正确的。4. 高级技巧与自动化监控掌握了基础查看和单次优化后我们需要更系统化、自动化地管理索引。4.1 使用 SHOW CREATE TABLE 辅助分析SHOW CREATE TABLE命令以完整的 DDL 语句形式展示表结构其中索引定义一目了然对于理解索引的完整定义包括索引类型、注释等非常有用。SHOW CREATE TABLE orders\G使用\G代替分号可以让结果以垂直格式显示在终端中更易读。你可以清晰地看到每个索引是UNIQUE KEY还是KEY以及它的组成列。4.2 识别冗余与重复索引冗余索引是数据库的“隐形杀手”它们占用磁盘空间降低写入速度还会让优化器选择执行计划时更加困惑。常见的冗余有两种前缀重复INDEX (a)和INDEX (a, b)。后者完全包含前者的功能。通常可以删除前者(a)。主键包含对于 InnoDB 表所有二级索引的叶子节点都包含了主键值。因此INDEX (a)和INDEX (a, id)在功能上是等价的后者是隐式存在的。我们可以通过查询INFORMATION_SCHEMA来系统性地查找冗余索引。下面是一个查找“可能冗余索引”的查询思路需要根据实际情况调整SELECT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME as ‘可能冗余的索引‘, GROUP_CONCAT(s.COLUMN_NAME ORDER BY s.SEQ_IN_INDEX) as ‘索引列‘, s2.INDEX_NAME as ‘可能覆盖它的索引‘, GROUP_CONCAT(s2.COLUMN_NAME ORDER BY s2.SEQ_IN_INDEX) as ‘覆盖索引列‘ FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.STATISTICS s2 ON s.TABLE_SCHEMA s2.TABLE_SCHEMA AND s.TABLE_NAME s2.TABLE_NAME AND s.INDEX_NAME ! s2.INDEX_NAME AND s.SEQ_IN_INDEX s2.SEQ_IN_INDEX AND s.COLUMN_NAME s2.COLUMN_NAME -- 核心逻辑寻找那些是另一个索引前缀的索引 WHERE NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS s3 WHERE s3.TABLE_SCHEMA s.TABLE_SCHEMA AND s3.TABLE_NAME s.TABLE_NAME AND s3.INDEX_NAME s.INDEX_NAME AND s3.SEQ_IN_INDEX s.SEQ_IN_INDEX 1 AND s3.COLUMN_NAME NOT IN ( SELECT s4.COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS s4 WHERE s4.TABLE_SCHEMA s2.TABLE_SCHEMA AND s4.TABLE_NAME s2.TABLE_NAME AND s4.INDEX_NAME s2.INDEX_NAME AND s4.SEQ_IN_INDEX s.SEQ_IN_INDEX 1 ) ) AND s.INDEX_NAME ! ‘PRIMARY‘ GROUP BY s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME, s2.INDEX_NAME HAVING COUNT(*) (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS s5 WHERE s5.TABLE_SCHEMA s.TABLE_SCHEMA AND s5.TABLE_NAME s.TABLE_NAME AND s5.INDEX_NAME s.INDEX_NAME);这个查询比较复杂它的目的是找出那些列完全是另一个索引前缀的索引。在实际操作中更常用的方法是结合pt-duplicate-key-checkerPercona Toolkit 中的工具来自动化检测它更专业和准确。4.3 利用 sys 库进行性能洞察MySQL 5.7 及以上版本提供了sys库它基于PERFORMANCE_SCHEMA和INFORMATION_SCHEMA提供了一系列人类可读的视图用于性能诊断。其中与索引相关的有用视图包括sys.schema_unused_indexes查看可能未使用的索引。这个视图会列出那些自从服务器启动以来没有被任何查询使用过的索引通过performance_schema追踪。这是一个发现并清理冗余索引的强力证据。SELECT * FROM sys.schema_unused_indexes;重要提示这里“未使用”指的是没有用于数据访问如 WHERE, ORDER BY, JOIN但唯一索引用于约束强制的情况不会被统计在内。删除此类索引前务必确认它没有用于数据完整性约束。sys.schema_redundant_indexes直接报告冗余索引。SELECT * FROM sys.schema_redundant_indexes;使用sys库可以极大地简化 DBA 的日常索引管理工作。4.4 建立索引监控与评估流程索引不是一劳永逸的。随着业务发展数据分布和查询模式都会变化。一个良好的索引监控流程应包括定期检查冗余/未使用索引每月或每季度运行一次pt-duplicate-key-checker和查询sys.schema_unused_indexes生成报告。监控索引大小增长定期检查INFORMATION_SCHEMA.TABLES中的INDEX_LENGTH警惕索引空间异常增长。分析慢查询日志将慢查询日志接入分析系统如 pt-query-digest持续关注新增的慢查询其背后往往隐藏着缺失或低效的索引。变更评审任何索引的创建和删除都应经过评估考虑其对写性能的影响、是否与其他索引冗余、以及是否能解决目标查询问题通过 EXPLAIN 验证。我个人习惯在每次大的业务迭代或数据量阶段性增长后对核心表做一次全面的索引健康度检查。检查清单包括现有索引列表、每个索引的 Cardinality/选择性、是否存在冗余、是否有查询报告了Using filesort或Using temporary以及sys库中关于索引使用情况的报告。这套组合拳打下来基本上能保证索引体系处于一个比较健康的状态。索引是数据库性能的基石而“查看”是理解和优化它的第一步。从简单的SHOW INDEX到结合EXPLAIN和INFORMATION_SCHEMA进行深度分析再到利用sys库和工具进行自动化监控这是一个从入门到精通的必经之路。记住最好的索引策略是源于对业务查询模式的深刻理解并通过持续的数据验证来迭代优化。不要害怕调整索引在测试环境充分验证后该加就加该删就删让索引真正为你的业务查询服务。