MySQL 8.4 索引实战：3种查看方法与 SHOW INDEX 字段全解析

1. 索引查看的三种核心方法对比

在日常数据库运维中，快速准确地获取索引信息是性能调优的基础工作。MySQL 8.4 提供了三种主流索引查看方式，每种方法各有其适用场景和特点。

方法	执行速度	输出信息量	适用场景	语法示例
SHOW INDEX	最快	中等	快速查看单表索引详情	SHOW INDEX FROM employees
INFORMATION_SCHEMA	中等	最全面	跨表分析或程序化处理	SELECT * FROM STATISTICS WHERE TABLE_NAME='employees'
DESC / EXPLAIN	最快	最简略	初步检查表结构和索引存在性	DESC employees

实际案例： 当我们需要分析一个订单表的索引分布时，可以先用 DESC orders 快速确认主键，再用 SHOW INDEX FROM orders 查看完整索引列表，最后通过 INFORMATION_SCHEMA 统计不同索引的选择性：

SELECT 
    INDEX_NAME,
    ROUND(CARDINALITY/TABLE_ROWS*100,2) AS selectivity 
FROM 
    INFORMATION_SCHEMA.STATISTICS 
WHERE 
    TABLE_NAME='orders';

提示： Cardinality/Table_rows 比值越接近1，索引选择性越好。通常建议优先优化选择性低于10%的索引。

2. SHOW INDEX 输出字段深度解析

执行 SHOW INDEX FROM employees 会返回包含12个关键字段的结果集，这些字段构成了索引诊断的基础数据。

2.1 核心字段解读

Table
当前索引所属的表名，在联合查询时特别有用。例如当执行 SHOW INDEX FROM dept_emp 时，可以确认返回的是部门-员工关联表的索引。

Non_unique
标识索引唯一性的关键指标：

• 0 ：唯一索引（如主键或UNIQUE约束）
• 1 ：普通索引，允许重复值

Key_name
索引的命名标识，需注意：

• 主键固定命名为 PRIMARY
• 联合索引的多个列共享相同索引名
• 删除索引时需指定此名称

Seq_in_index
对于联合索引（如 (last_name, first_name) ），该字段表示列在索引中的顺序位置：

• 从1开始编号
• 相同的Key_name会有多条记录，通过此字段区分顺序

Column_name
索引涉及的列名，当使用函数索引时可能显示表达式而非列名。

2.2 高级技术字段

Collation
字符列的排序规则：

• A (Ascending)：升序排列（默认）
• NULL ：未排序（如HASH索引）

Cardinality
索引唯一值的估算数量，这个值的准确性直接影响优化器的索引选择：

-- 手动更新统计信息以提高准确性
ANALYZE TABLE employees;

注意：该值是采样估算结果，在数据量变化超过1/6或执行ANALYZE TABLE后会重新计算。

Sub_part
前缀索引的长度指示器：

• NULL ：整列被索引
• 数字：如 10 表示只索引前10个字符
• 对于BLOB/TEXT类型必须指定前缀长度

Index_type
MySQL 8.4支持的索引类型包括：

• BTREE ：默认的平衡树结构
• HASH ：仅Memory引擎支持
• FULLTEXT ：全文检索专用
• SPATIAL ：地理空间索引

3. 索引诊断实战技巧

3.1 识别冗余索引

通过以下查询可发现重复或前缀重叠的索引：

SELECT 
    a.TABLE_NAME,
    a.COLUMN_NAME,
    GROUP_CONCAT(a.INDEX_NAME) AS indexes
FROM 
    INFORMATION_SCHEMA.STATISTICS a
JOIN 
    (SELECT TABLE_NAME, COLUMN_NAME 
     FROM INFORMATION_SCHEMA.STATISTICS 
     WHERE TABLE_SCHEMA=DATABASE()
     GROUP BY TABLE_NAME, COLUMN_NAME 
     HAVING COUNT(*) > 1) b
ON a.TABLE_NAME = b.TABLE_NAME AND a.COLUMN_NAME = b.COLUMN_NAME
GROUP BY a.TABLE_NAME, a.COLUMN_NAME;

3.2 索引使用率分析

检查从未被使用的"僵尸索引"：

SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM 
    performance_schema.table_io_waits_summary_by_index_usage
WHERE 
    INDEX_NAME IS NOT NULL
    AND COUNT_STAR = 0
    AND OBJECT_SCHEMA NOT IN ('mysql','sys');

3.3 索引碎片整理

当索引碎片率超过30%时应考虑优化：

SELECT 
    TABLE_NAME,
    INDEX_NAME,
    ROUND(DATA_FREE/(INDEX_LENGTH+DATA_FREE)*100,2) AS frag_ratio
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    DATA_FREE > 0
    AND TABLE_SCHEMA=DATABASE();

执行优化命令：

ALTER TABLE employees ENGINE=InnoDB;  -- 在线重组表
OPTIMIZE TABLE employees;            -- 锁表操作，需在维护窗口执行

4. 性能优化进阶策略

4.1 索引选择性优化

计算各索引的选择性指标：

SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    CARDINALITY,
    TABLE_ROWS,
    ROUND(CARDINALITY/TABLE_ROWS*100,2) AS selectivity_pct
FROM 
    INFORMATION_SCHEMA.STATISTICS
WHERE 
    TABLE_SCHEMA=DATABASE()
ORDER BY 
    selectivity_pct DESC;

4.2 覆盖索引检测

通过EXPLAIN验证查询是否使用覆盖索引：

EXPLAIN 
SELECT employee_id, department_id 
FROM dept_emp 
WHERE from_date > '2020-01-01';

当Extra列显示"Using index"时，表示查询只需扫描索引无需回表。

4.3 索引合并优化

MySQL 8.4支持多种索引合并策略：

-- 查看优化器开关状态
SHOW VARIABLES LIKE 'optimizer_switch';

-- 临时启用特定合并策略
SET SESSION optimizer_switch='index_merge=on,index_merge_union=on';

5. 特殊索引类型处理

5.1 函数索引管理

MySQL 8.0+支持函数索引：

-- 创建函数索引
ALTER TABLE employees 
ADD INDEX idx_name_upper ((UPPER(last_name)));

-- 查看函数索引
SHOW INDEX FROM employees 
WHERE Key_name='idx_name_upper';

5.2 不可见索引测试

安全测试索引的有效性：

-- 将索引设置为不可见
ALTER TABLE employees 
ALTER INDEX idx_name INVISIBLE;

-- 测试查询性能变化
EXPLAIN SELECT * FROM employees WHERE last_name='Smith';

-- 确认无影响后删除
ALTER TABLE employees 
ALTER INDEX idx_name VISIBLE;

5.3 降序索引应用

优化特定排序场景：

-- 创建降序索引
CREATE INDEX idx_salary_desc ON employees(salary DESC);

-- 验证排序利用
EXPLAIN 
SELECT * FROM employees 
ORDER BY salary DESC LIMIT 100;