mysql怎样查看表的索引使用情况 mysql索引字段使用分析教程

使用EXPLAIN分析查询执行计划，结合SHOW INDEX和慢查询日志，判断索引是否被有效使用；选择性高的列应优先用于索引，避免函数操作、类型不匹配、OR条件及违反最左前缀原则导致索引失效；组合索引需按查询频率和选择性合理排序，避免冗余，持续优化以提升查询性能。

MySQL索引的使用情况，简单来说，就是看你的查询到底有没有真的用上索引，以及索引的使用效率如何。这直接关系到你的数据库性能，慢查询的根源往往就在这里。

查看索引使用情况，分析索引字段的使用，是为了找出那些“摆设”索引，以及优化那些效率不高的索引。别让索引成了负担，优化才是王道。

解决方案

1. 使用

   EXPLAIN

   语句： 这是最常用的方法。在你的

   SELECT

   语句前加上

   EXPLAIN

   ，MySQL会告诉你它打算如何执行这个查询，包括是否使用了索引，使用了哪个索引，以及扫描了多少行。

   EXPLAIN SELECT * FROM your_table WHERE your_indexed_column = 'your_value';

   重点关注

   type

   列（显示连接使用的类型，

   index

   、

   range

   、

   ref

   等，越靠前性能越好），

   possible_keys

   列（显示可能应用在这张表中的索引），

   key

   列（显示实际决定使用的索引），

   key_len

   列（显示索引中被使用部分的长度），

   rows

   列（显示MySQL认为它执行查询时必须检查的行数）。

   Extra

   列也很有用，它包含MySQL解决查询的额外信息。

2. SHOW INDEX

   语句： 可以查看表的所有索引信息。

   SHOW INDEX FROM your_table;

   这个命令会显示索引的名称、使用的列、索引类型（B-Tree、HASH等）、是否唯一索引等。虽然不能直接告诉你索引的使用情况，但可以帮助你了解索引的结构。

3. 开启

   slow_query_log

   ： 记录执行时间超过

   long_query_time

   秒的所有SQL语句。分析慢查询日志，可以找出哪些查询没有使用索引或者索引使用效率低下。

   修改MySQL配置文件（例如

   my.cnf

   或

   my.ini

   ）：

   slow_query_log = 1 slow_query_log_file = /path/to/your/slow_query.log long_query_time = 2  # 单位：秒

   重启MySQL服务后，执行时间超过2秒的查询会被记录到

   /path/to/your/slow_query.log

   文件中。可以使用

   mysqldumpslow

   工具分析慢查询日志。

4. 使用性能分析工具： 像Percona Toolkit、pt-query-digest等工具，可以更深入地分析查询性能，找出瓶颈。这些工具通常提供更详细的报告，帮助你识别需要优化的索引。

5. MySQL Enterprise Monitor： 如果你使用了MySQL Enterprise Edition，它自带的监控工具可以提供实时的性能数据和索引使用情况。

索引列的选择性如何影响查询性能？

索引的选择性是指索引列中不同值的数量与表中总记录数的比值。选择性越高，索引的效率越高。比如，一个性别字段（只有男女两个值）的索引，选择性就很低，MySQL可能不会使用它，因为它扫描索引的成本可能比全表扫描还高。

计算索引的选择性：

SELECT COUNT(DISTINCT your_indexed_column) / COUNT(*) FROM your_table;

如果选择性接近1，说明索引效果很好。如果选择性很低，考虑删除这个索引或者考虑组合索引。

怎样判断索引失效的情况？

索引失效是指查询语句中使用了索引，但MySQL并没有真正使用它。常见的原因包括：

• 使用了函数或表达式： 在索引列上使用了函数（如

  UPPER()

  、

  DATE()

  ）或表达式，会导致索引失效。

  SELECT * FROM your_table WHERE UPPER(your_indexed_column) = 'VALUE'; -- 索引失效

• 类型不匹配： 查询条件的数据类型与索引列的数据类型不匹配，MySQL可能会进行隐式类型转换，导致索引失效。

  SELECT * FROM your_table WHERE your_indexed_column = 123; -- 如果your_indexed_column是字符串类型，索引可能失效

• OR

  条件： 如果

  OR

  条件连接的多个列都未使用索引，或者只有部分列使用了索引，MySQL可能会放弃使用索引。

• LIKE

  模糊查询： 使用

  LIKE '%keyword%'

  进行模糊查询，索引会失效。

  LIKE 'keyword%'

  可以使用索引。

• 组合索引未遵循最左前缀原则： 如果创建了组合索引

  (col1, col2, col3)

  ，查询时必须从

  col1

  开始，才能使用索引。如果只查询

  col2

  或

  col3

  ，索引会失效。

• MySQL认为全表扫描更快： 在某些情况下，即使使用了索引，MySQL优化器可能会认为全表扫描更快，从而放弃使用索引。例如，当查询结果集很大时。

如何优化组合索引？

组合索引是将多个列组合在一起创建的索引。优化组合索引的关键在于确定列的顺序，遵循以下原则：

1. 最左前缀原则： 查询必须从组合索引的最左侧列开始，才能使用索引。
2. 选择性高的列放在前面： 将选择性最高的列放在组合索引的最左侧，可以更快地过滤数据。
3. 考虑查询频率： 将查询频率最高的列放在组合索引的最左侧。
4. 避免过度索引： 不要创建过多的组合索引，每个索引都需要占用存储空间，并且会影响

   INSERT

   、

   UPDATE

   操作的性能。

举个例子，假设有一个

users

表，包含

city

、

age

、

name

三个字段，经常需要查询某个城市某个年龄段的用户。可以创建一个组合索引

(city, age)

。如果还需要根据姓名查询，可以考虑再创建一个索引

(name)

，而不是将

name

添加到组合索引

(city, age)

中，因为

(city, age, name)

的索引，在只查询

name

的时候是用不上的。

组合索引的创建还需要结合具体的业务场景和查询模式，没有一劳永逸的解决方案，需要不断地测试和优化。