悠悠楠杉
网站页面
MySQL查看表索引定义与类型解析指南
09/01
一、为什么要了解索引定义？
作为一名MySQL数据库开发者或DBA，理解表的索引结构是进行性能优化的基础。索引就像书籍的目录，能大幅提高数据查询效率。但不当的索引设计反而会拖慢写入速度并占用额外存储空间。因此，掌握查看和分析索引的方法是数据库优化的必修课。
二、查看表索引定义的常用方法
1. SHOW INDEX命令
最直接的方式是使用SHOW INDEX命令：
sql
SHOW INDEX FROM 表名;
例如查看员工表的索引：
sql
SHOW INDEX FROM employees;
执行结果会显示以下关键信息：
- Table: 表名
- Nonunique: 是否唯一索引(0表示唯一，1表示非唯一)
- Keyname: 索引名称
- Seqinindex: 索引中的列序号
- Columnname: 列名
- Collation: 排序方式(A升序，D降序，NULL不排序)
- Cardinality: 索引基数(估算的唯一值数量)
- Subpart: 索引前缀长度(如只对前100字符索引)
- Packed: 是否打包
- Null: 列是否可为NULL
- Index_type: 索引类型(BTREE, HASH等)
- Comment: 注释信息
2. 查询INFORMATION_SCHEMA
更灵活的方式是查询系统表：
sql
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名';
这种方法可以与其他表关联查询，获取更全面的信息。
3. SHOW CREATE TABLE
查看建表语句也能获取索引信息：
sql
SHOW CREATE TABLE 表名;
这种方式适合快速了解表结构和索引定义。
三、MySQL索引类型深度解析
1. B-Tree索引
最常见的索引类型，适合全值匹配、范围查询和前缀匹配：
sql
-- 创建普通B-Tree索引
CREATE INDEX idx_name ON employees(last_name);
特点：
- 默认的索引类型(InnoDB实际使用B+Tree)
- 支持=, >, <, BETWEEN等操作
- 适合高选择性列(唯一值多的列)
2. 哈希索引
Memory引擎默认使用哈希索引：
sql
-- 创建哈希索引(Memory引擎)
CREATE INDEX idx_hash USING HASH ON employees(key_col);
特点：
- 仅支持=和IN()等值查询
- 查询速度极快(O(1)时间复杂度)
- 不支持范围查询和排序
- 容易发生哈希冲突
3. 全文索引(Fulltext)
专为文本搜索设计的索引：
sql
-- 创建全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx (title, content);
特点：
- 仅支持MyISAM和InnoDB(5.6+)
- 使用MATCH AGAINST语法查询
- 支持自然语言和布尔模式搜索
4. 空间索引(R-Tree)
用于地理空间数据：
sql
-- 创建空间索引
CREATE SPATIAL INDEX sp_idx ON locations(geo);
特点：
- 仅支持MyISAM和InnoDB(5.7+)
- 专用于GEOMETRY数据类型
- 使用GIS函数查询
四、索引使用的最佳实践
选择性原则：在选择性高的列上建索引(如用户ID优于性别)
最左前缀原则：联合索引(a,b,c)只能用于a、a,b或a,b,c查询
避免过度索引：每个额外索引都会降低写入速度
索引列大小：尽量使用小的数据类型作为索引
覆盖索引：查询只需通过索引就能获取全部数据
五、索引性能分析工具
- EXPLAIN：分析查询执行计划
sql
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';
- 性能模式：MySQL 5.5+提供的性能监控
sql
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;
- 慢查询日志：记录执行缓慢的查询
六、特殊索引类型
1. 前缀索引
sql
-- 对前10个字符创建索引
CREATE INDEX idx_prefix ON employees(last_name(10));
适用于TEXT/VARCHAR等长字段。
2. 降序索引(MySQL 8.0+)
sql
-- 创建降序索引
CREATE INDEX idx_desc ON employees(hire_date DESC);
优化ORDER BY ... DESC查询。
3. 函数索引(MySQL 8.0+)
sql
-- 创建函数索引
CREATE INDEX idx_func ON employees((UPPER(last_name)));
七、维护索引的实用命令
- 重建索引(消除碎片)：
sql
ALTER TABLE 表名 ENGINE=InnoDB;
-- 或
OPTIMIZE TABLE 表名;
- 删除不需要的索引：
sql
DROP INDEX 索引名 ON 表名;
- 禁用索引(测试性能影响)：
sql
-- InnoDB不支持真正禁用，可以删除后恢复
ALTER TABLE 表名 DISABLE KEYS; -- MyISAM
ALTER TABLE 表名 ENABLE KEYS;
