mysql获取表索引信息的方法 mysql获取表索引类型的技巧-mysql教程-PHP中文网

要快速查看mysql表的索引信息和类型，最直接的方法是使用show index from table_name命令，它能详细列出索引名称、涉及的列和索引类型（如btree、hash等），而通过查询information_schema.statistics表则可实现更灵活的元数据检索，两种方法结合explain分析查询执行计划，可全面掌握索引的使用情况与性能影响，从而进行有效优化。

mysql获取表索引信息的方法 mysql获取表索引类型的技巧

要快速查看MySQL表的索引信息和它们的类型，最直接的方法是使用

SHOW INDEX FROM table_name;

登录后复制

命令。它能给你一个关于表上所有索引的详细概览，包括索引的名称、涉及的列以及最重要的——索引的类型。如果需要更灵活的查询或从系统视图层面获取信息，

information_schema.STATISTICS

登录后复制

表也是一个非常强大的工具。

解决方案

获取MySQL表索引信息和类型主要有两种途径，各有侧重。

首先，最常用也最直观的是

SHOW INDEX FROM table_name;

登录后复制

命令。这个命令会返回一个结果集，其中包含了表上定义的所有索引的详细元数据。它会列出很多列，但我们主要关注几个关键的：

```
Key_name
```
登录后复制
: 索引的名称。
```
Column_name
```
登录后复制
: 索引包含的列名。对于复合索引，同一
```
Key_name
```
登录后复制
会有多行，每行对应一个列。
```
Index_type
```
登录后复制
: 这就是我们想知道的索引类型，比如
```
BTREE
```
登录后复制
、
```
HASH
```
登录后复制
、
```
FULLTEXT
```
登录后复制
、
```
SPATIAL
```
登录后复制
等。在InnoDB存储引擎中，绝大多数索引都是
```
BTREE
```
登录后复制
类型的。
```
Non_unique
```
登录后复制
: 如果是0，表示这是一个唯一索引；如果是1，表示是非唯一索引。
```
Cardinality
```
登录后复制
: 索引中唯一值的估计数量。这个值越高，索引的区分度就越好，对查询优化器的帮助也越大。

举个例子，如果你有一个名为

users

登录后复制

的表，想看看它的索引：

SHOW INDEX FROM users;

登录后复制

你可能会看到类似这样的输出：

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
------- ----------- ----------- --------------- ----------- ----------- ----------- ----------- ------- ------- ----------- ------- -------------
users   0           PRIMARY     1               id          A           100000      NULL        NULL    BTREE
users   1           idx_username    1           username    A           98000       NULL        NULL    BTREE
users   1           idx_status_created_at   1   status      A           5           NULL        NULL    BTREE
users   1           idx_status_created_at   2   created_at  A           100000      NULL        NULL    BTREE

登录后复制

从这个结果里，我们可以清楚地看到

PRIMARY

登录后复制

索引（主键）和

idx_username

登录后复制

、

idx_status_created_at

登录后复制

等自定义索引，它们都是

BTREE

登录后复制

类型。

其次，对于需要更灵活的查询，或者在应用程序中批量获取索引信息时，查询

information_schema.STATISTICS

登录后复制

表是更好的选择。

information_schema

登录后复制

是一个虚拟数据库，包含了MySQL服务器的元数据信息。

你可以这样查询：

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    INDEX_TYPE,
    NON_UNIQUE,
    CARDINALITY
FROM
    information_schema.STATISTICS
WHERE
    TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

登录后复制

将

your_database_name

登录后复制

和

your_table_name

登录后复制

替换成你实际的数据库名和表名。这种方式的优势在于你可以通过SQL语句进行更复杂的过滤、连接和聚合操作，比如查找某个数据库中所有表的索引，或者统计某种类型的索引数量。不过，需要注意的是，直接查询

information_schema

登录后复制

在大型数据库中可能会比较慢，因为它需要从内部数据字典中读取信息。

为什么需要了解索引类型？它对查询性能有何影响？

坦白说，很多时候我们创建索引，可能只是习惯性地在WHERE子句涉及的列上加一个，或者在JOIN的列上加一个，但很少会深入思考“索引类型”这个东西。但它真的挺重要的，尤其是在你遇到一些难以解释的慢查询时，回过头来看看索引类型，可能会发现一些端倪。

MySQL（特别是InnoDB）最常见的索引类型是

BTREE

登录后复制

（B-Tree），它也是默认的。B-Tree索引的结构决定了它在处理等值查询（

登录后复制

）、范围查询（

登录后复制

、

登录后复制

、

BETWEEN

登录后复制

）、排序（

ORDER BY

登录后复制

）以及前缀匹配（

LIKE 'abc%'

登录后复制

）时表现优秀。它的数据是经过排序的，并且以树状结构存储，这使得数据库可以快速定位到所需的数据范围。可以说，大部分我们日常使用的场景，BTREE都能很好地覆盖。

纳米搜索

纳米搜索：360推出的新一代AI搜索引擎

查看详情

然而，还有一些其他类型，比如

HASH

登录后复制

、

FULLTEXT

登录后复制

和

SPATIAL

登录后复制

。

HASH

登录后复制

索引在MySQL中比较特殊，它主要用于Memory存储引擎表，或者作为InnoDB内部的“自适应哈希索引”（Adaptive Hash Index），而不是我们手动创建的常规索引。如果一个列上能用哈希索引，那么等值查询的性能会非常快，因为它直接通过哈希值定位数据，理论上是O(1)的复杂度。但它的缺点也很明显：不支持范围查询，不支持排序，也不支持部分匹配。你无法在哈希索引上执行

WHERE col > 10

登录后复制

或者

ORDER BY col

登录后复制

。所以，除非你明确知道自己的查询模式完全是等值查找，否则通常不会主动选择哈希索引。

FULLTEXT

登录后复制

索引，顾名思义，是为全文搜索设计的。如果你需要在文本字段（如

TEXT

登录后复制

、

VARCHAR

登录后复制

）中进行关键词搜索，比如

MATCH (column) AGAINST ('keyword')

登录后复制

，那么

FULLTEXT

登录后复制

索引是你的不二之选。它有自己一套复杂的算法来处理分词、停用词、相关性排序等，和普通索引完全不同。

SPATIAL

登录后复制

索引则用于地理空间数据类型，比如

POINT

登录后复制

、

LINESTRING

登录后复制

、

POLYGON

登录后复制

等。如果你在处理地图、位置信息等场景，需要进行空间查询（例如查找某个区域内的所有点），那就需要用到

SPATIAL

登录后复制

索引。

了解这些索引类型，能帮助我们更好地理解查询优化器为什么会选择某个索引，或者为什么某个查询没有走我们预期的索引。比如，你对一个字段做了范围查询，但如果那个字段上只有哈希索引，那么这个索引就不会被使用，查询可能会退化为全表扫描。或者，你尝试在BTREE索引上做

LIKE '%keyword%'

登录后复制

这样的模糊匹配，你会发现它也无法利用索引，因为BTREE索引是按从左到右的顺序排列的，开头的通配符让它无从下手。所以，知道索引类型，就是知道索引的“脾气”和“擅长”，这样我们才能更好地“使唤”它。

如何判断一个索引是否被有效使用？有哪些常见误区？

判断一个索引是否被有效使用，最核心、最权威的工具就是

EXPLAIN

登录后复制

语句。当你对一个

SELECT

登录后复制

查询感到疑惑，或者它运行得很慢时，第一步就应该是

EXPLAIN

登录后复制

它。

EXPLAIN SELECT column1, column2 FROM your_table WHERE condition_column = 'value' ORDER BY another_column;

登录后复制

EXPLAIN

登录后复制

的输出结果有很多列，其中几个关键的能告诉你索引的使用情况：

```
type
```
登录后复制
：这是最重要的列之一，它显示了MySQL如何扫描表来查找所需数据。理想情况是
```
const
```
登录后复制
（常量）、
```
eq_ref
```
登录后复制
（唯一索引查找）、
```
ref
```
登录后复制
（非唯一索引查找）或
```
range
```
登录后复制
（范围查找）。最差的是
```
ALL
```
登录后复制
，这意味着全表扫描。如果你的查询返回
```
ALL
```
登录后复制
，但你期望它走索引，那肯定有问题。
```
key
```
登录后复制
：显示MySQL实际使用的索引名称。如果这里是
```
NULL
```
登录后复制
，表示没有使用索引。
```
key_len
```
登录后复制
：显示MySQL使用的索引的长度。对于复合索引，这可以帮助你判断索引的哪些部分被使用了。
```
rows
```
登录后复制
：MySQL估计需要检查的行数。这个数字越小越好。
```
Extra
```
登录后复制
：提供了额外的信息，比如“Using index”（表示使用了覆盖索引，查询所需的所有列都在索引中，无需回表）、“Using where”（表示在存储引擎层进行了筛选）、“Using filesort”（表示需要对结果进行排序，通常是性能瓶颈）、“Using temporary”（表示需要创建临时表，也通常是性能瓶颈）。

如果

EXPLAIN

登录后复制

显示

key

登录后复制

为

NULL

登录后复制

，或者

type

登录后复制

是

ALL

登录后复制

，或者

Extra

登录后复制

中出现了

Using filesort

登录后复制

、

Using temporary

登录后复制

，那通常意味着索引没有被有效利用，或者利用得不够充分。

关于索引使用的常见误区，我见过不少，也踩过一些坑：

函数或表达式在索引列上： 这是一个经典错误。如果你在WHERE子句中对索引列使用了函数，比如
```
WHERE DATE(created_at) = '2023-01-01'
```
登录后复制
，或者进行了计算
```
WHERE price * 1.1 > 100
```
登录后复制
，那么这个索引通常会失效。因为索引存储的是原始值，经过函数或计算后的值无法直接通过索引查找。正确的做法是把函数或计算放到等号的另一边，比如
```
WHERE created_at BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59'
```
登录后复制
。
LIKE
登录后复制
语句以通配符开头：
```
WHERE name LIKE '%john%'
```
登录后复制
这样的查询，因为通配符在字符串开头，索引无法进行前缀匹配，所以会失效。只有
```
WHERE name LIKE 'john%'
```
登录后复制
才能有效利用索引。
隐式类型转换： 如果你把一个字符串和数字类型的列进行比较，MySQL可能会进行隐式类型转换，这可能导致索引失效。例如，
```
WHERE int_column = '123'
```
登录后复制
，如果
```
int_column
```
登录后复制
是整数类型，MySQL可能会将其转换为字符串再比较，或者反过来，导致索引无法使用。保持数据类型一致性很重要。
复合索引的“最左前缀原则”： 如果你有一个复合索引
```
(col1, col2, col3)
```
登录后复制
，那么这个索引可以用于
```
col1
```
登录后复制
，或者
```
(col1, col2)
```
登录后复制
，或者
```
(col1, col2, col3)
```
登录后复制
的查询。但如果你只查询
```
col2
```
登录后复制
或
```
col3
```
登录后复制
，或者查询
```
(col2, col3)
```
登录后复制
，那么这个索引就无法完全利用，甚至根本不被使用。这就是最左前缀原则。理解这一点对于设计复合索引至关重要。
OR
登录后复制
条件：在某些情况下，
```
OR
```
登录后复制
条件可能会导致索引失效，因为它可能需要扫描多个索引或全表扫描。不过，MySQL优化器现在也越来越智能，对于一些简单的
```
OR
```
登录后复制
条件，如果每个条件都能使用独立的索引，它可能会使用“索引合并”（Index Merge）策略。但复杂
```
OR
```
登录后复制
还是需要警惕。
索引选择性（Cardinality）低： 如果一个列的唯一值很少（比如性别列），那么即使你为它创建了索引，优化器也可能觉得全表扫描更快，从而放弃使用这个索引。
```
SHOW INDEX
```
登录后复制
结果中的
```
Cardinality
```
登录后复制
列可以帮助你判断。

优化索引时，除了类型和使用情况，还需要考虑哪些因素？

优化索引可不是个简单活，它像是一门艺术，需要经验、直觉，还得结合实际业务场景。除了前面提到的索引类型和通过

EXPLAIN

登录后复制

判断使用情况，还有几个关键点是优化时必须考虑的：

索引的基数（Cardinality）和选择性： 这是个非常重要的指标。基数是指一个列中不重复值的数量。选择性则是基数与总行数的比率。一个高基数的列（比如用户ID、邮箱地址）是创建索引的理想选择，因为索引能很快地定位到少数几行。而低基数的列（比如性别、状态码），即使建了索引，优化器也可能觉得全表扫描更划算。
```
SHOW INDEX FROM table_name
```
登录后复制
结果中的
```
Cardinality
```
登录后复制
列就反映了这一点。如果一个索引的基数很低，它很可能无法带来预期的性能提升。
索引的维护成本： 索引并不是越多越好。每一个索引都会占用磁盘空间，并且在数据进行插入（
```
INSERT
```
登录后复制
）、更新（
```
UPDATE
```
登录后复制
）和删除（
```
DELETE
```
登录后复制
）操作时，数据库也需要同时维护这些索引。索引越多，写操作的开销就越大，性能可能反而下降。所以，在创建新索引时，要权衡它带来的读性能提升是否值得其写入成本。那些几乎不被查询但又有很多更新的表，可能就不适合建太多索引。
覆盖索引（Covering Index）： 这是一个非常强大的优化手段。如果一个查询所需的所有列（包括
```
SELECT
```
登录后复制
列表中的列、
```
WHERE
```
登录后复制
子句中的列、
```
ORDER BY
```
登录后复制
和
```
GROUP BY
```
登录后复制
中的列）都能在索引中找到，那么数据库就不需要再去回表（访问实际的数据行）获取数据了。这会大大减少I/O操作，显著提升查询速度。
```
EXPLAIN
```
登录后复制
结果中的
```
Extra
```
登录后复制
列显示
```
Using index
```
登录后复制
就表示使用了覆盖索引。设计覆盖索引通常意味着创建复合索引，将查询中涉及的所有列都包含进去。
复合索引的列顺序： 对于复合索引（多列索引），列的顺序至关重要，因为它直接影响到“最左前缀原则”的有效性。通常，我们建议将选择性最高的列放在复合索引的最前面，这样可以最快地缩小搜索范围。然后，再根据查询模式，将那些经常用于过滤、排序或分组的列按顺序添加到后面。这是一个需要仔细规划的地方，没有放之四海而皆准的规则，得根据具体的查询模式来定。
聚簇索引与二级索引（InnoDB特有）： 在InnoDB存储引擎中，主键就是聚簇索引。这意味着表的实际数据行是按照主键的顺序物理存储的。所有非主键索引（二级索引）的叶子节点存储的不是行指针，而是对应行的主键值。这意味着通过二级索引查询时，首先找到主键值，然后再通过主键值去聚簇索引中找到完整的行数据，这个过程称为“回表”。如果查询只需要二级索引中的列（即覆盖索引），就不会发生回表。理解这一点对优化非常重要，因为它解释了为什么主键查询通常比二级索引查询更快，以及为什么覆盖索引如此高效。
识别和删除冗余或未使用的索引： 随着时间的推移，业务需求可能会变化，一些索引可能变得不再需要，或者被其他更优的索引所覆盖。这些冗余索引不仅占用空间，还增加了写操作的开销。定期审查和删除未使用的索引是一个很好的习惯。可以通过查询
```
information_schema.SCHEMA_STATISTICS
```
登录后复制
或通过性能监控工具来识别那些几乎没有被使用的索引。
外键上的索引： 如果你的表之间有外键关系，确保外键列上有索引是至关重要的。这不仅能加速
```
JOIN
```
登录后复制
操作，还能提高参照完整性检查的效率，避免在删除或更新父表记录时出现性能问题。MySQL在外键创建时通常会自动创建索引，但检查一下总没错。