mysql查看表索引创建语句 mysql查看表索引类型创建方法详解-mysql教程-PHP中文网

mysql查看表索引创建语句 mysql查看表索引类型创建方法详解

看不見的法師

发布： 2025-08-16 10:07:01

原创

650人浏览过

查看MySQL索引使用SHOW CREATE TABLE table_name；可识别主键、唯一、普通索引；2. 常见索引类型包括B-Tree（默认，支持范围查询）、Hash（精确匹配，Memory引擎）、Fulltext（全文搜索）、R-Tree（地理空间）；3. 选择索引需根据查询类型：B-Tree适用于大多数场景，Hash用于精确匹配，Fulltext用于文本关键词搜索，R-Tree用于空间数据；4. 优化索引需定期ANALYZE TABLE更新统计信息，删除无用索引，重建碎片化索引，避免WHERE中使用函数导致索引失效，并利用覆盖索引提升性能。

mysql查看表索引创建语句 mysql查看表索引类型创建方法详解

查看MySQL表索引的创建语句和类型，主要是为了了解索引的定义和优化方式，以便更好地管理和优化数据库性能。

SHOW CREATE TABLE table_name;

这条语句会显示创建表的完整SQL语句，其中包含了索引的定义。例如：

SHOW CREATE TABLE users;

登录后复制

输出结果类似：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

登录后复制

从这个结果中，你可以看到：

```
PRIMARY KEY (\
```
登录后复制
id`)`: 主键索引，通常用于唯一标识表中的每一行。
```
UNIQUE KEY \
```
登录后复制
username` (`username`)
```
:  唯一索引，确保
```
登录后复制
username`列的值是唯一的。
```
KEY \
```
登录后复制
email` (`email`)
```
:  普通索引，用于加速
```
登录后复制
email`列的查询。

MySQL有哪些常见的索引类型？

MySQL支持多种索引类型，每种索引类型都有其特定的使用场景和优缺点。了解这些索引类型有助于你选择最适合你需求的索引。

B-Tree 索引: 这是MySQL中最常用的索引类型，实际上在没有特别指明索引类型时，默认都是B-Tree索引。它适用于全键值、键值范围和键前缀查找。InnoDB和MyISAM存储引擎都支持B-Tree索引。例如，可以加速
```
WHERE username = 'john'
```
登录后复制
或
```
WHERE age BETWEEN 20 AND 30
```
登录后复制
这样的查询。但需要注意的是，B-Tree索引不适合用于处理
```
WHERE name LIKE '%john'
```
登录后复制
这种以通配符开头的模糊查询，因为无法有效利用索引。
Hash 索引: Hash索引基于哈希表实现，只有Memory存储引擎显式支持。Hash索引查找速度非常快，时间复杂度为O(1)，但它也有一些限制。它只适用于精确匹配，不支持范围查询，也不支持排序。由于Hash索引存储的是哈希值，而不是实际值，所以无法用于避免回表查询。
Fulltext 索引: 全文索引用于在文本中查找关键词，适用于MyISAM和InnoDB存储引擎。它可以在
```
CHAR
```
登录后复制
、
```
VARCHAR
```
登录后复制
或
```
TEXT
```
登录后复制
类型的列上创建。全文索引通过建立单词和文档之间的映射关系，可以快速找到包含指定关键词的文档。使用
```
MATCH AGAINST
```
登录后复制
语法进行查询。例如：
```
SELECT * FROM articles WHERE MATCH (body) AGAINST ('keyword');
```
登录后复制
。
空间数据索引 (R-Tree): 空间数据索引用于存储地理空间数据，例如点、线和多边形。MyISAM存储引擎支持空间数据索引，可以用于加速地理空间查询。

如何根据查询需求选择合适的索引类型？

选择合适的索引类型，需要考虑你的查询模式、数据特征和存储引擎的限制。

豆绘AI

豆绘AI是国内领先的AI绘图与设计平台，支持照片、设计、绘画的一键生成。

485

查看详情

精确匹配查询: 如果你的查询主要是精确匹配，例如
```
WHERE column = value
```
登录后复制
，并且你使用的是Memory存储引擎，可以考虑使用Hash索引。但是，大多数情况下，B-Tree索引也能很好地处理精确匹配查询，而且更通用。
范围查询: 如果你的查询涉及到范围查询，例如
```
WHERE column BETWEEN value1 AND value2
```
登录后复制
或
```
WHERE column > value
```
登录后复制
，那么B-Tree索引是最佳选择。
模糊查询: 如果你需要进行模糊查询，例如
```
WHERE column LIKE 'value%'
```
登录后复制
，B-Tree索引仍然适用。但是，如果模糊查询以通配符开头，例如
```
WHERE column LIKE '%value'
```
登录后复制
，B-Tree索引就无法有效利用。在这种情况下，可以考虑使用全文索引，或者使用其他技术，例如倒排索引。
全文搜索: 如果你需要进行全文搜索，例如在文章内容中查找关键词，那么Fulltext索引是最佳选择。
地理空间查询: 如果你需要进行地理空间查询，例如查找附近的位置，那么空间数据索引是最佳选择。

如何优化已有的索引？

即使你已经创建了索引，仍然需要定期检查和优化它们，以确保它们能够有效地加速查询。

定期分析表: 使用
```
ANALYZE TABLE table_name;
```
登录后复制
命令可以更新索引的统计信息，优化查询优化器的决策。
删除未使用的索引: 未使用的索引会占用存储空间，并且会降低写入性能。可以使用
```
pt-index-usage
```
登录后复制
工具来查找未使用的索引。
重建索引: 对于频繁更新的表，索引可能会变得碎片化，导致查询性能下降。可以使用
```
ALTER TABLE table_name ENGINE=InnoDB;
```
登录后复制
命令来重建索引。
优化SQL查询: 确保你的SQL查询能够有效地利用索引。避免在
```
WHERE
```
登录后复制
子句中使用函数或表达式，这可能会导致索引失效。
考虑覆盖索引: 覆盖索引是指索引包含了查询所需的所有列，这样就可以避免回表查询，提高查询性能。