MySQL中如何识别低效的索引

前言(可以跳过直接看正文)

索引的基本原理

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。
索引的原理很简单，就是把无序的数据变成有序的。读取数据时，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据。如下图，MySQL索引默认结构为：

MySQL索引改良后的结构为：

索引设计的原则

1. 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
2. 基数较小的类，索引效果较差，没有必要在此列建立索引
3. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
4. 不要过度索引，索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可

创建索引的原则

索引虽好，但也不是无限制的使用，最好符合一下几个原则：

1. 最左前缀匹配原则
2. 较频繁作为查询条件的字段才去创建索引，更新频繁字段不适合创建索引
3. 若是不能有效区分数据的列不适合做索引列，如性别，男女未知，最多也就三种，区分度实在太低，尽量的扩展索引，不要新建索引
4. 定义有外键的数据列一定要建立索引
5. 对于那些查询中很少涉及的列
6. 对于定义为text、image和bit的数据类型的列不要建立索引

正文

使用索引查询一定能提高查询的性能吗？

通常，通过索引查询数据比全表扫描要快。合理的索引设计，可以大大减少慢SQL，但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

• 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
• 基于非唯一性索引的检索

怎样查看索引是否有高选择性？

通过Show Index结果中的列Cardinality来观察。非常关键，表示所以中不重复记录的预估值，需要注意的是Cardinality是一个预估值，而不是一个准确值基本上用户也不可能得到一个准确的值，在实际应用中，Cardinality/n_row_in_table应尽可能的接近1，如果非常小,那用户需要考虑是否还有必要创建这个索引。

在InnoDB存储引擎中，Cardinality统计信息的更新发生在两个操作中：insert和update。InnoDB存储引擎内部对更新Cardinality信息的策略为:

表中1/16的数据已发生了改变
stat_modified_counter>2000 000 000

用一条SQL查看低效的索引

根据Cardinality/n_row_in_table的大小，我们可以通过以下SQL查找某个schema（或者整个数据库实例）中，低效的索引，如下：

SELECT TABLE_SCHEMA, 
       TABLE_NAME, 
       INDEX_NAME,
       COLUMN_NAME,
       CARDINALITY,
       TABLE_ROWS, 
       index_rate   
FROM (SELECT a. TABLE_SCHEMA,
                a.TABLE_NAME,
                a.INDEX_NAME,
                a.COLUMN_NAME,
                a.CARDINALITY,
                b.TABLE_ROWS, 
               LEFT(IF(b. TABLE_ROWS = 0 || b. TABLE_ROWS IS NULL,
               0.00,a.CARDINALITY/b.TABLE_ROWS),4) AS index_rate
           FROM (SELECT TABLE_SCHEMA, 
                       TABLE_NAME,
                       INDEX_NAME,
                       COLUMN_NAME,
                       CARDINALITY
                   FROM information_schema.STATISTICS
                  WHERE TABLE_SCHEMA='xxxx'  -- xxxx为schema的名字
                  GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) AS a JOIN information_schema.TABLES AS b
             ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
             AND a.TABLE_NAME = b.TABLE_NAME
            and table_rows > 100000) c
                    where CARDINALITY < 100
  order by 5 asc,6 desc
  limit 10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

说明：低效的索引虽然大多时候不会被SQL执行计划选中，但是，不仅仅占用宝贵数据库空间，而且在INSERT，DELETE，UPDATE时需要对索引进行维护，降低性能，所以，低效的索引必须清理掉。