常见索引问题

常见索引问题

在满足语句需求的情况下，尽量少的访问资源是数据库设计的重要原则，这和执行的SQL有直接的关系，索引问题又是SQL问题中出现频率最高的，常见的索引问题包括：无索引(失效)、隐式转换。

1、SQL执行流程

看一个问题，在下面这个表T中，如果我要执行select * from T where k between 3 and 5;需要执行几次树的搜索操作，会扫描多少行？

mysql> create table T (
    -> ID int primary key,
    -> k int NOT NULL DEFAULT 0, 
    -> s varchar(16) NOT NULL DEFAULT '',
    -> index k(k))
    -> engine=InnoDB;
mysql> insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

这分别是ID字段索引树、k字段索引树

这条SQL语句的执行流程：
1、在K索引树上找到k=3，获9得ID=300
2、回表到ID索引树查找ID=300的记录，对应R3
3、在k索引树找到下一个值k=5，ID=500
4、再回到ID索引树找到对应ID=500的R4
5、在k索引树去下一个值k=6,不符合条件，循环结束
这个过程读取了k索引树的三条记录，回表了两次

因为查询结果所需要的数据只在主键索引上有，所以必须得回表。所以，我们该如何通过优化索引，来避免回表呢？

2、常见索引优化

2.1、覆盖索引

覆盖索引，换言之就是索引要覆盖我们的查询请求，无需回表。

如果执行的语句是select ID from T where k between 3 and 5;，这样的话因为ID的值在K索引树上，就不需要回表了。

覆盖索引可以减少树的搜索次数，显著提升查询性能，是常用的性能优化手段。

但是，维护索引是有代价的，所以在建立冗余索引来支持覆盖索引时要权衡利弊。

2.2、最左前缀原则

B+树的数据项是复合的数据结构，比如(name,sex，age)的时候，B+树是按照从左到右的顺序来建立搜索树的，当(张三,F,26)这样的数据来检索的时候，B+树会优先比较name来确定下一步的检索方向，如果name相同再依次比较sex和age，最后得到检索的数据。

## 有这样一个表P
mysql> create table P (id int primary key, name varchar(10) not null, sex varchar(1), age int, index tl(name,sex,age)) engine=IInnoDB;
mysql> insert into P values(1,'张三','F',26),(2,'张三','M',27),(3,'李四','F',28),(4,'乌兹','F',22),(5,'张三','M',21),(6,'王五','M',28);
## 下面的语句结果相同
mysql> select * from P where name='张三' and sex='F';     ## A1
mysql> select * from P where sex='F' and age=26;         ## A2
## explain看一下
mysql> explain select * from P where name='张三' and sex='F';
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | P     | NULL       | ref  | tl            | tl   | 38      | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+

mysql> explain select * from P where sex='F' and age=26;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | P     | NULL       | index | NULL          | tl   | 43      | NULL |    6 |    16.67 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

可以清楚的看到，A1使用tl索引，A2进行了全表扫描，虽然A2的两个条件都在tl索引中出现，但是没有使用到name列，不符合最左前缀原则，无法使用索引。

所以在建立联合索引的时候，如何安排索引内的字段排序是关键。评估标准是索引的复用能力，因为支持最左前缀，所以当建立（a，b）这个联合索引之后，就不需要给a单独建立索引。

原则上，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的

上面这个例子中，如果查询条件里只有b，就是没法利用（a，b）这个联合索引的，这时候就不得不维护另一个索引，也就是说要同时维护（a，b）、（b）两个索引。这样的话，就需要考虑空间占用了，比如，name和age的联合索引，name 字段比age字段占用空间大，所以创建（name，age）联合索引和（age）索引占用空间是要小于（age，name）、(name）索引的。

2.3、索引下推

以人员表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是26岁的所有男性”。那么，SQL语句是这么写的mysql> select * from tuser where name like '张%' and age=26 and sex=M;

通过最左前缀索引规则，会找到ID1，然后需要判断其他条件是否满足

在MySQL 5.6之前，只能从ID1开始一个个回表。到主键索引上找出数据行，再对比字段值。

而MySQL 5.6 引入的索引下推优化(index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

这样，减少了回表次数和之后再次过滤的工作量，明显提高检索速度。

2.4、隐式类型转化

隐式类型转化主要原因是，表结构中指定的数据类型与传入的数据类型不同，导致索引无法使用。

所以有两种方案：

• 修改表结构，修改字段数据类型。
• 修改应用，将应用中传入的字符类型改为与表结构相同类型。

3、为什么会选错索引

3.1、优化器

选择索引是优化器的工作，其目的是找到一个最优的执行方案，用最小的代价去执行语句。

在数据库中，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

3.2、扫描行数

MySQL在真正开始执行语句之前，并不能精确的知道满足这个条件的记录有多少条，只能通过索引的区分度来判断 。显然，一个索引上不同的值越多，索引的区分度就越好，而一个索引上不同值的个数我们称为“基数”，也就是说，这个基数越大，索引的区分度越好。

## 通过show index方法，查看索引的基数
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |       95636 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a        |            1 | a           | A         |       96436 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b        |            1 | b           | A         |       96436 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

MySQL使用采样统计方法来估算基数：

采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

在MySQL中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选择：

• on的时候，表示统计信息会持久化存储。这时，默认的N是20，M是10。
• off的时候，表示统计信息只存储在内存中。这时，默认的N是8，M是16。

由于是采样统计，所以不管N是20还是8，这个基数都很容易不准确。

所以，冤有头债有主，MySQL选错索引，还得归咎到没能准确地判断出扫描行数。

可以用analyze table来重新统计索引信息，进行修正

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

3.3、索引选择异常和处理

1、采用force index强行选择一个索引。

2、可以考虑修改语句，引导MySQL使用我们期望的索引。

3、有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。