悠悠楠杉
网站页面
MySQL显示表的所有索引命令与类型解析：从基础到高阶应用
08/26
一、查看表索引的基础命令
在MySQL运维和开发过程中，查看表的索引信息是常规操作。最常用的命令是：
sql
SHOW INDEX FROM 表名;
-- 示例：
SHOW INDEX FROM users;
这条命令会返回包含10个关键字段的结果集：
1. Table：索引所属表名
2. Nonunique：是否允许重复值（0表示唯一索引）
3. Keyname：索引名称（PRIMARY表示主键）
4. Seqinindex：索引中的列序号
5. Columnname：索引列名
6. Collation：排序方式（A升序/D降序）
7. Cardinality：基数估计值（影响优化器决策）
8. Subpart：索引前缀长度
9. Packed：键的压缩方式
10. Index_type：索引类型（核心关注点）
二、索引类型深度解析
1. B-Tree索引（默认类型）
sql
-- 创建示例
CREATE INDEX idx_name ON users(username);
- 特点：平衡树结构，支持全键值、键值范围和键前缀查找
- 优势：适合范围查询（>、<、BETWEEN）和排序操作
- 限制：最左前缀原则，无法跳过索引中的列
2. 哈希索引
sql
-- 需要指定存储引擎
CREATE TABLE hash_table (
id INT,
data VARCHAR(100),
INDEX USING HASH (id)
) ENGINE=MEMORY;
- 特点：精确匹配O(1)时间复杂度
- 优势：等值查询极快，常用于内存表
- 致命缺陷：不支持范围查询、排序，不适用于磁盘存储（InnoDB的自适应哈希索引除外）
3. 全文索引（FULLTEXT）
sql
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body);
- 特点：基于分词的自然语言搜索
- 使用场景：MATCH AGAINST语法实现文本搜索
- 注意事项：仅MyISAM和InnoDB（5.6+）支持，有最小词长限制
4. 空间索引（SPATIAL）
sql
CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
- 专用于：GIS地理数据查询
- 要求：MyISAM引擎或InnoDB（5.7+）
三、高级索引技巧
1. 复合索引优化
sql
CREATE INDEX idx_cover ON orders(user_id, status, create_time);
- 设计原则：将高选择性列放在左侧，遵循最左前缀原则
- 覆盖索引：当查询的所有列都包含在索引中时，可避免回表操作
2. 索引选择性分析
通过计算不同值的数量与总行数的比值：
sql
SELECT
COUNT(DISTINCT column_name)/COUNT(*)
FROM table_name;
比值越接近1，索引效果越好。低于0.1时可能成为无效索引。
3. 使用EXPLAIN验证
sql
EXPLAIN SELECT * FROM users WHERE username='admin';
观察type列：const > ref > range > index > ALL，性能依次降低。
四、生产环境索引管理建议
监控索引使用率：
sql SELECT * FROM sys.schema_unused_indexes;定期维护：
sql ANALYZE TABLE users; -- 更新统计信息 OPTIMIZE TABLE users; -- 重建表（MyISAM有效）避坑指南：
- 避免在低选择性列（如性别）建索引
- 谨慎使用函数索引（MySQL 8.0+支持函数索引）
- TEXT/BLOB列需使用前缀索引
- 性能对比测试：
sql -- 禁用索引 ALTER TABLE users DISABLE KEYS; -- 启用索引 ALTER TABLE users ENABLE KEYS;
