悠悠楠杉
网站页面
MySQL如何查看表索引？全面解析索引类型与查询方法
08/19
一、列出表索引的SQL语句大全
1. 基础查询命令
sql
-- 查看某张表的全部索引
SHOW INDEX FROM 表名;
-- 示例（查看users表的索引）
SHOW INDEX FROM users;
执行结果包含以下关键字段：
- Non_unique：是否唯一索引（0为唯一，1为非唯一）
- Key_name：索引名称（PRIMARY表示主键）
- Seq_in_index：索引中的列序号
- Column_name：索引列名
- Index_type：索引类型（BTREE/HASH等）
2. 信息模式查询
sql
-- 通过information_schema查看
SELECT * FROM information_schema.STATISTICS
WHERE table_schema = '数据库名' AND table_name = '表名';
3. 快速查看建表语句
sql
SHOW CREATE TABLE 表名;
4. 命令行工具查询
bash
mysqlshow -k 数据库名 表名
二、MySQL索引类型深度解析
1. 按数据结构分类
| 索引类型 | 特性 | 适用场景 |
|------------|-------------------------------|------------------------|
| B-Tree | 平衡树结构，支持范围查询 | 默认索引类型 |
| 哈希 | 精确匹配快，不支持排序 | 等值查询频繁的表 |
| R-Tree | 空间索引 | 地理数据存储 |
| 全文 | 文本分词检索 | 文章内容搜索 |
2. 按功能特性分类
(1) 普通索引
sql
ALTER TABLE users ADD INDEX idx_username (username);
- 最基本的索引类型
- 允许重复值和NULL值
(2) 唯一索引
sql
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);
- 列值必须唯一
- 允许NULL值（但仅能有一个NULL）
(3) 主键索引
sql
ALTER TABLE users ADD PRIMARY KEY (id);
- 特殊的唯一索引
- 不允许NULL值
- 每个表只能有一个
(4) 全文索引
sql
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);
- 仅支持MyISAM和InnoDB引擎
- 支持MATCH AGAINST语法
(5) 组合索引
sql
ALTER TABLE orders ADD INDEX idx_combo (user_id, order_date);
- 最左前缀原则：查询必须包含第一列
- 索引选择性高的列建议放前面
(6) 前缀索引
sql
ALTER TABLE logs ADD INDEX idx_url_prefix (url(100));
- 对文本字段前N个字符建立索引
- 可显著减少索引空间
三、索引使用实战建议
选择合适列：
- WHERE子句中的高频查询列
- JOIN操作关联列
- ORDER BY/GROUP BY排序列
避免索引失效：sql
-- 反例：索引列使用函数会导致失效
SELECT * FROM users WHERE YEAR(create_time) = 2023;-- 正例：
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';定期维护：sql
-- 重建索引（InnoDB）
ALTER TABLE users ENGINE=InnoDB;-- 分析索引使用情况
ANALYZE TABLE users;监控工具：sql
-- 查看索引使用统计
SELECT * FROM sys.schemaindexstatistics;-- 查看未使用索引
SELECT * FROM sys.schemaunusedindexes;
注：索引不是越多越好，每个索引会增加约5-10%的写入开销。建议单表索引数量控制在5-8个以内。
