mysql获取表的索引列表语句 mysql获取表的索引类型列表教程

雪夜

发布时间：2025-09-01 09:43:01

315人浏览过

来源于php中文网

原创

要查看mysql表中所有索引的详细信息，最直接的方法是使用show index from table_name;或查询information_schema.statistics表。前者适用于快速查看特定表的索引，返回结果包含索引名、类型、列名、唯一性等信息；后者适合跨库查询或复杂筛选，可通过sql条件过滤获取指定数据库或表的索引详情，并能结合表的存储引擎、行数等上下文信息进行分析。两种方法均能准确展示索引结构，其中show index输出直观，而information_schema方式更灵活，适用于自动化管理和性能优化场景，完整支持对btree、hash、fulltext、spatial等索引类型的识别与分析，且可扩展用于数据库审计、索引效率评估等任务，最终实现对索引全面掌控的目的。

mysql获取表的索引列表语句 mysql获取表的索引类型列表教程

要获取MySQL表的索引列表和索引类型，最直接的方式就是使用

SHOW INDEX FROM table_name;

或

SHOW KEYS FROM table_name;

语句。如果需要更系统地查询，比如跨库或更复杂的条件，

INFORMATION_SCHEMA.STATISTICS

表提供了更丰富的数据。

解决方案

查看MySQL表索引的详细信息，通常我会用到两种主要方法，它们各有侧重，但都能清晰地展示索引结构和类型。

方法一：使用

SHOW INDEX

或
SHOW KEYS
语句

这是最常用也最直观的方式，尤其适合快速查看某个特定表的索引情况。

SHOW INDEX FROM your_table_name;
-- 或者
SHOW KEYS FROM your_table_name;

将

your_table_name

替换为你想要查询的表名。这条命令会返回一个结果集，包含了该表所有索引的详细信息。我个人觉得这个命令的输出格式非常友好，一目了然。

方法二：查询

INFORMATION_SCHEMA.STATISTICS

表

INFORMATION_SCHEMA

是MySQL提供的一个虚拟数据库，它存储了关于数据库服务器的元数据，包括数据库、表、列、索引等信息。

STATISTICS

表就包含了所有表的索引信息。

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    COLLATION,
    CARDINALITY,
    SUB_PART,
    PACKED,
    `NULL`,
    INDEX_TYPE,
    COMMENT,
    INDEX_COMMENT
FROM
    INFORMATION_SCHEMA.STATISTICS
WHERE
    TABLE_SCHEMA = 'your_database_name'
    AND TABLE_NAME = 'your_table_name'
ORDER BY
    TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

这里你需要将

your_database_name

和

your_table_name

替换成实际的数据库名和表名。这种方式的优势在于你可以通过

WHERE

子句灵活地过滤，比如查询某个数据库下所有表的索引，或者查找特定类型的索引。在需要编写脚本或进行自动化管理时，这种方式就显得非常强大。

如何查看MySQL表中所有索引的详细信息？

当我们执行

SHOW INDEX FROM your_table_name;

命令时，它会返回一个包含多列的结果集，每一列都提供了索引的某个方面的信息。理解这些列的含义，对于我们分析索引性能和设计优化方案至关重要。

以下是这些列的常见含义：

Table: 索引所属的表名。这没什么好说的，就是指明是哪个表的索引。
Non_unique: 如果索引是非唯一的，值为1；如果是唯一索引或主键，值为0。这是判断索引是否允许重复值的关键。
Key_name: 索引的名称。主键索引的名称通常是
```
PRIMARY
```
。自定义的索引会有你设定的名字。
Seq_in_index: 索引中列的序号，从1开始。对于复合索引（多列索引），这个值能告诉你列的顺序。
Column_name: 索引中包含的列名。
Collation: 列在索引中的排序方式。
```
A
```
表示升序，
```
D
```
表示降序，
```
NULL
```
表示未排序。大多数时候我们看到的是
```
A
```
。
Cardinality: 索引中唯一值的估计数量。这个值非常重要，它反映了索引的选择性。基数越高，索引的选择性越好，查询效率可能越高。MySQL优化器会根据这个值来决定是否使用索引。
Sub_part: 对于字符串列，如果索引只使用了列的一部分（前缀索引），这里会显示前缀的长度。如果整个列都被索引，则为
```
NULL
```
。
Packed: 指示关键字如何被压缩。如果未压缩，则为
```
NULL
```
。
Null: 如果列可以包含
```
NULL
```
值，则为
```
YES
```
；否则为
```
NO
```
。
Index_type: 索引的类型。这是我们关注的重点之一，比如
```
BTREE
```
、
```
HASH
```
、
```
FULLTEXT
```
或
```
SPATIAL
```
。
Comment: 索引的注释。
Index_comment: 索引的更多注释信息。
Visible: 索引是否可见。MySQL 8.0 引入了隐形索引的概念，不可见索引不会被优化器使用。

举个例子，假设我们有一个

users

表，里面有

id

(主键),

username

(唯一索引),

email

(普通索引) 和

bio

(可能有个全文索引)。执行

SHOW INDEX FROM users;

可能会看到类似这样的输出（简化版）：

Table	Non_unique	Key_name	Seq_in_index	Column_name	Cardinality	Index_type
users	0	PRIMARY	1	id	10000	BTREE
users	0	username	1	username	10000	BTREE
users	1	idx_email	1	email	9000	BTREE
users	1	ft_bio	1	bio	5000	FULLTEXT

从这里，我们能清晰地看到

id

和

username

是唯一索引 (

Non_unique

为 0)，

idx_email

是普通索引 (

Non_unique

为 1)。同时，它们都是

BTREE

类型，而

ft_bio

是

FULLTEXT

类型。

住哪API酒店+租车源码包

数据本地化解决接口缓存数据无限增加，读取慢的问题，速度极大提升更注重SEO优化优化了系统的SEO，提升网站在搜索引擎的排名，增加网站爆光率搜索框本地化不用远程读取、IFRAME调用，更加容易应用及修改增加天气预报功能页面增加了天气预报功能，丰富内容增加点评和问答页面增加了点评和问答相关页面，增强网站粘性电子地图优化优化了电子地图的加载速度与地图功能酒店列表增加房型读取酒店列表页可以直接展示房型，增

下载

MySQL中索引类型有哪些？它们各自有什么特点和应用场景？

SHOW INDEX

命令输出的

Index_type

列，直接告诉了我们索引的底层存储结构或实现机制。MySQL支持几种主要的索引类型，每种都有其特定的优势和适用场景。

BTREE (B-Tree)
- 特点: 这是MySQL最常用、也是默认的索引类型，几乎所有的存储引擎（InnoDB, MyISAM等）都支持。B-Tree索引以平衡树结构存储数据，能够保持数据有序。
- 应用场景:
  - 精确查找:
```
WHERE column = 'value'
```
  - 范围查找:
```
WHERE column BETWEEN 'val1' AND 'val2'
```
    或
```
WHERE column > 'value'
```
  - 排序:
```
ORDER BY column
```
  - 前缀匹配:
```
WHERE column LIKE 'prefix%'
```
  - 多列索引: B-Tree索引在多列索引上表现出色，但要注意“最左前缀原则”。
- 个人看法: 我觉得B-Tree索引就像是图书馆里的分类目录，你想找的书（数据）总能通过分类（索引键）一层层快速定位。它非常通用，大部分情况下，你创建的索引都是B-Tree。
HASH (哈希索引)
- 特点: 基于哈希表实现，对于精确匹配查询速度非常快。它将所有索引列的值计算出一个哈希码，然后将哈希码和指向数据行的指针存储在哈希表中。
- 应用场景:
  - 精确查找:
```
WHERE column = 'value'
```
    。
- 局限性:
  - 不支持范围查询: 因为哈希值是无序的，所以无法进行范围查找。
  - 不支持排序: 同样因为无序，无法用于排序。
  - 不支持部分索引匹配: 如果是复合哈希索引，必须使用所有列才能进行查找。
  - 只支持等值比较:
```
>
```
    、
```
<
```
    、
```
LIKE
```
    等操作无效。
- 个人看法: 哈希索引就像是字典的快速查找功能，你输入一个词，它直接告诉你页码。但如果你想找“所有以A开头的词”，哈希索引就帮不了你了。在MySQL中，只有
```
MEMORY
```
  存储引擎显式支持哈希索引。InnoDB有自适应哈希索引，那是内部优化机制，我们无法直接创建。
FULLTEXT (全文索引)
- 特点: 专门用于文本内容的模糊搜索，支持自然语言查询和布尔模式查询。它会对文本内容进行分词、去除停用词等处理。
- 应用场景:
  - 文章内容搜索: 比如博客系统、论坛帖子等需要对大段文本进行关键词搜索的场景。
- 局限性:
  - 早期只支持MyISAM，MySQL 5.6+开始InnoDB也支持。
  - 需要特定的语法
```
MATCH AGAINST
```
    来使用。
- 个人看法: 全文索引就像搜索引擎，你输入几个关键词，它能从海量文本中找出最相关的结果。对于需要强大文本搜索功能的系统，它是不可或缺的。
SPATIAL (空间索引)
- 特点: 用于地理空间数据类型（如
```
GEOMETRY
```
  ,
```
POINT
```
  ,
```
LINESTRING
```
  ,
```
POLYGON
```
  ）的索引，遵循开放地理空间联盟（OGC）标准。
- 应用场景:
  - 地理位置查询: 查找某个区域内的点，或者计算两个地理对象之间的距离等。
- 局限性:
  - 通常要求存储引擎为MyISAM（InnoDB从MySQL 5.7.5开始支持）。
  - 索引的列不能为
```
NULL
```
    。
- 个人看法: 空间索引是处理地图、位置信息的核心。如果你在开发LBS（Location-Based Service）应用，这个就非常重要了。

除了这些

Index_type

，我们平时还会提到一些“逻辑上的索引类型”，比如：

PRIMARY KEY (主键索引): 一种特殊的唯一索引，一个表只能有一个，且列值不能为
```
NULL
```
。它通常是聚簇索引（InnoDB）。
UNIQUE INDEX (唯一索引): 确保索引列的所有值都是唯一的，但允许
```
NULL
```
值（多个
```
NULL
```
值）。
NORMAL INDEX (普通索引): 最基本的索引，没有唯一性限制。
MULTI-COLUMN INDEX (复合索引/联合索引): 包含多个列的索引。

这些逻辑类型通常都是基于

BTREE

结构实现的，通过

Non_unique

字段和

Key_name

来区分。

如何通过INFORMATION_SCHEMA查看跨库或更复杂的索引信息？

INFORMATION_SCHEMA.STATISTICS

表是MySQL元数据查询的强大工具，它允许我们以编程的方式获取数据库中所有索引的详细信息，而不仅仅是单个表。这在进行数据库审计、性能分析或者自动化运维时尤其有用。

前面已经给出了一个基本的查询示例，这里我们再深入一下，看看如何利用它来解决更复杂的需求：

SELECT
    s.TABLE_SCHEMA AS DatabaseName,
    s.TABLE_NAME AS TableName,
    s.INDEX_NAME AS IndexName,
    s.SEQ_IN_INDEX AS ColumnSequence,
    s.COLUMN_NAME AS ColumnName,
    s.COLLATION AS SortOrder,
    s.CARDINALITY AS EstimatedUniqueValues,
    s.SUB_PART AS PrefixLength,
    s.`NULL` AS IsNullable,
    s.INDEX_TYPE AS IndexType,
    s.COMMENT AS IndexComment,
    t.ENGINE AS StorageEngine,
    t.TABLE_ROWS AS TableRows
FROM
    INFORMATION_SCHEMA.STATISTICS s
JOIN
    INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME
WHERE
    s.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') -- 排除系统数据库
    -- AND s.TABLE_SCHEMA = 'your_specific_database' -- 如果只想查特定库
    -- AND s.INDEX_TYPE = 'FULLTEXT' -- 查找所有全文索引
    -- AND s.CARDINALITY < 1000 -- 查找基数较低的索引，可能效率不高
ORDER BY
    s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME, s.SEQ_IN_INDEX;

这个查询的亮点在于：

跨库查询: 默认情况下，只要你有权限，它会查询所有非系统数据库的索引信息。你可以通过
```
WHERE s.TABLE_SCHEMA = 'your_specific_database'
```
来指定只查询某个数据库。
更丰富的上下文信息: 通过
```
JOIN INFORMATION_SCHEMA.TABLES t
```
，我们能同时获取到表的存储引擎 (
```
t.ENGINE
```
) 和大致的行数 (
```
t.TABLE_ROWS
```
)。这对于评估索引的重要性或潜在影响非常有用。比如，一个大表上的索引，其性能影响通常会比小表上的更显著。
灵活的过滤条件:
- ```
s.TABLE_SCHEMA NOT IN (...)
```
  : 排除MySQL自带的系统数据库，让结果更聚焦于业务数据。
- ```
s.INDEX_TYPE = 'FULLTEXT'
```
  : 可以轻松找出所有全文索引，这对于分析文本搜索功能很有帮助。
- ```
s.CARDINALITY < 1000
```
  : 这是一个简单的例子，用于筛选可能效率不高的索引（基数太低意味着区分度不高）。当然，实际的判断需要结合业务场景和查询模式。