悠悠楠杉
网站页面
MySQL索引字段属性查询详解
08/22
MySQL索引字段属性查询详解
索引的重要性与查询方法
在MySQL数据库管理中，索引是提升查询性能的关键因素。了解索引字段的属性对于数据库优化至关重要。本文将详细介绍如何查看MySQL中索引字段的各种属性，帮助DBA和开发者更好地理解和使用索引。
查看索引基本信息
要查看表的索引信息，最常用的方法是使用SHOW INDEX命令。这个命令会返回表中所有索引的详细信息：
sql
SHOW INDEX FROM 表名;
执行结果包含多个重要列：
- Table：索引所属的表名
- Non_unique：是否唯一索引（0表示唯一，1表示非唯一）
- Key_name：索引名称
- Seq_in_index：索引中的列序号
- Column_name：索引列名
- Collation：排序方式（A表示升序，NULL表示不排序）
- Cardinality：基数（估算的唯一值数量）
- Sub_part：索引前缀长度（NULL表示整列被索引）
- Packed：是否打包（NULL表示未打包）
- Null：列是否包含NULL值
- Index_type：索引类型（BTREE、HASH等）
- Comment：索引注释信息
通过information_schema查询索引
除了SHOW INDEX命令，我们还可以通过查询information_schema数据库获取更详细的索引信息：
sql
SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名';
这个查询返回的结果与SHOW INDEX类似，但提供了更多灵活性，可以添加WHERE条件筛选特定索引，或者通过JOIN连接其他系统表获取更多信息。
查看索引字段的数据类型
了解索引字段的数据类型对于优化查询非常重要。我们可以结合SHOW COLUMNS命令查看：
sql
SHOW COLUMNS FROM 表名;
或者使用更详细的查询：
sql
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名'
AND COLUMN_NAME IN (
SELECT COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名'
);
索引使用情况分析
仅仅知道索引结构是不够的，我们还需要了解索引的实际使用情况。MySQL提供了EXPLAIN命令来分析查询如何使用索引：
sql
EXPLAIN SELECT * FROM 表名 WHERE 索引字段 = 值;
EXPLAIN结果中的key列显示了实际使用的索引，possible_keys列显示了可能使用的索引，rows列显示了需要检查的行数估计值。
查看索引大小和碎片情况
索引的大小和碎片情况也会影响性能。我们可以通过以下查询获取这些信息：
sql
SELECT
TABLE_NAME,
INDEX_NAME,
ROUND(SUM(INDEX_LENGTH)/1024/1024, 2) AS '索引大小(MB)',
ROUND(SUM(DATA_FREE)/1024/1024, 2) AS '碎片空间(MB)'
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = '数据库名'
GROUP BY
TABLE_NAME, INDEX_NAME;
复合索引的列顺序
对于复合索引（多列索引），列的顺序非常重要。我们可以通过以下方式查看复合索引的列顺序：
sql
SELECT
INDEX_NAME,
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS 索引列顺序
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名'
GROUP BY
INDEX_NAME;
索引的基数分析
索引基数（Cardinality）是指索引列中不同值的数量估计。高基数列通常更适合作为索引：
sql
SELECT
INDEX_NAME,
COLUMN_NAME,
CARDINALITY,
TABLE_ROWS,
ROUND((CARDINALITY/TABLE_ROWS)*100, 2) AS '选择性(%)'
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名';
索引类型和存储方式
MySQL支持多种索引类型，了解索引类型有助于优化：
sql
SELECT
INDEX_NAME,
INDEX_TYPE,
(CASE WHEN NON_UNIQUE = 0 THEN '唯一索引' ELSE '非唯一索引' END) AS 索引性质,
(CASE WHEN NULLABLE = 'YES' THEN '可空' ELSE '不可空' END) AS 是否可空
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名';
索引前缀长度查询
对于字符串类型的索引，MySQL支持只索引列值的前缀部分：
sql
SELECT
INDEX_NAME,
COLUMN_NAME,
SUB_PART AS 前缀长度
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名'
AND SUB_PART IS NOT NULL;
索引的隐藏属性
有些索引属性不直接显示在标准查询结果中。例如，我们可以检查是否存在降序索引：
sql
SELECT
INDEX_NAME,
COLUMN_NAME,
COLLATION
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名'
AND COLLATION = 'D';
索引的创建语句
如果需要重建索引，了解索引的原始创建语句很有帮助：
sql
SHOW CREATE TABLE 表名;
这个命令会返回完整的表创建语句，包括所有索引的定义。
通过性能模式监控索引使用
MySQL的performance_schema提供了索引使用统计：
sql
SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_READ,
COUNT_WRITE,
COUNT_FETCH
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
OBJECT_SCHEMA = '数据库名' AND OBJECT_NAME = '表名';
这个查询可以显示每个索引的读取、写入和获取次数，帮助识别很少使用的索引。
索引与存储引擎的关系
不同存储引擎对索引的支持有所不同。我们可以查看表使用的存储引擎：
sql
SELECT
TABLE_NAME,
ENGINE,
(CASE WHEN INDEX_COMMENT LIKE '%FULLTEXT%' THEN '全文索引'
WHEN INDEX_COMMENT LIKE '%SPATIAL%' THEN '空间索引'
ELSE '普通索引' END) AS 索引类型
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名';
索引的注释信息
索引的注释可能包含有用的元数据：
sql
SELECT
INDEX_NAME,
COLUMN_NAME,
COMMENT AS 索引注释
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名'
AND COMMENT IS NOT NULL AND COMMENT != '';
索引与外键关系
对于外键约束，MySQL会自动创建索引：
sql
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名'
AND REFERENCED_TABLE_NAME IS NOT NULL;
索引的排序规则
索引的排序规则影响字符串比较和排序：
sql
SELECT
INDEX_NAME,
COLUMN_NAME,
COLLATION AS 排序规则
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名';
索引的压缩信息
对于压缩索引，我们可以查看相关信息：
sql
SELECT
INDEX_NAME,
COLUMN_NAME,
(CASE WHEN PACKED = 'NO' THEN '未压缩' ELSE '压缩' END) AS 压缩状态
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名';
索引的虚拟列支持
MySQL 8.0+支持在虚拟列上创建索引：
sql
SELECT
COLUMN_NAME,
COLUMN_TYPE,
EXTRA
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名'
AND EXTRA LIKE '%VIRTUAL%'
AND COLUMN_KEY != '';
索引的不可见属性
MySQL 8.0+支持不可见索引：
sql
SELECT
INDEX_NAME,
(CASE WHEN IS_VISIBLE = 'YES' THEN '可见' ELSE '不可见' END) AS 可见性
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名';
索引的函数索引支持
MySQL 8.0+支持函数索引：
sql
SELECT
INDEX_NAME,
COLUMN_NAME,
EXPRESSION
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名'
AND EXPRESSION IS NOT NULL;
