第七篇：MySQL之索引

索引的基本思想

我们都知道算法设计里面有一个非常经典的思想：用空间复杂度来换取时间复杂度的下降！索引就是用额外的空间来增加数据库的查询效率。

索引的类别

1、HASH
按照hash表的方式来组织数据，通过拉链来解决hash冲突。
优点：查询快，插入和删除效率高
缺点：只适合于等值查询，区间查询效率低下

2、B+树

MySQL的底层物理结构是page（页），每页中会存放若干条数据行，具体的组织形式可以见第二篇：MySQL之InnoDB物理存储结构

B+树是一个N叉树，叶子节点存储的为实际的数据，非叶子节点存储的为索引数据。

B树
B树和B+树的格式大概一致，只不过B树的非叶子节点存储的是实际的数据。

B树和B+树的区别：
1、B树的区间查询和排序查询的效率比较低。
2、因为B树的非叶子节点存储的为实际的数据，比B+树只存储主键和页号的数据量要大得多。因此，相比于B+树，B树每个页能够存储的记录数量要少，树也就越高。

InnoDB索引

InnoDB的索引类型主要是B+树。一个索引对应着一颗B+树。

索引的缺点：
索引的好处是查询效率变高，但是为了维护B+树的有序性，插入数据的时候有点麻烦：
1、插入数据的数据页有空闲，这时有可能需要页内之间数据的挪动，效率低下
2、插入数据的数据页已经满了，这个时候需要重新申请一个空白页，然后将数据页中的部分数据挪动过去，这个操作叫做页分裂，此时，数据页的空间利用率也比较低下，时间效率也低下。

索引主要分为以下几种类型

1、主键索引（聚簇索引）
主键上的索引被称为主键索引或者聚集索引、又称为聚簇索引。

2、非主键索引（二级索引）

非主键索引叫做二级索引。
和主键索引不同的是，二级索引中的叶子节点中存储的不是实际的整行数据，而是对应的主键。这样的做法的好处是减少一条数据记录对应的数据大小，使一个叶子节点能够容纳更多数量的数据记录。

二级索引的查询方式：
首先在二级索引中查询出对应的主键，然后在利用主键查询主键索引，这个过程叫做回表。

3、联合索引

联合索引就是对两个或者多个列来建立索引，例如对上表中的t1、t2建立联合索引，就是首先对t1进行排序，在t1相同的情况下，按照t2进行排序。
此时，叶子节点存放的是联合索引中对应的数据列+主键

实际操作判断索引使用方式：

explain select XX from XX where xx

在查询语句的开头加入explain关键字后，会返回该查询语句的查询方式，是否走索引，走的哪个索引！

如上图所示，记录了查询的具体信息，我们主要看四个方面：
1、type，查询的方式，从好到坏依次如下所示：
a、system 需要查询的表中只有一条记录或者是空表
b、const 在唯一索引或者主键索引上的等值查询
c、ref 除唯一索引和主键索引外的等值查询
d、range 在所有类别索引上的范围查询
e、index 在所有类别索引上的从头到尾遍历查询
f、all 在主键索引上从头到尾遍历查询

index和all的区别就是 索引上的叶子节点只存储索引对应的列信息，而主键索引对应的叶子节点存放了所有的列信息。因此索引的叶子节点中存放的数据行的数据量小，一个数据页中能够存放更多的数据行。这样的话，如果遍历所有数据行，索引占用的数据页个数要比主键索引更少，查询效率也更高，因为从磁盘上读取数据的成本很高

2、key，使用的索引名字

3、rows 查询时扫描的粗略的行数

4、extra
a、 using index 表示查询的时候直接使用索引上的数据，无需回表
b、 using where 当需要多个条件查询索引时，直接在索引上过滤不符合条件的记录。5.6版本之前还需要将所有记录返回到Server层，在Server层重新过滤。5.6版本之后直接下放到了存储引擎中，这也叫做索引下推
c、 using index condition 和using where 一样，只不过using index condition查询的数据需要重新回表

实践
我们创建了一个num表，id为主键，创建了一个（t1，t2，t3）的联合索引，名字为t1_t2_t3.

1、explain select * from num where id = 1;

主键索引上的等值查询 type为const key 为primary ，也就是主键

2、explain select * from num where id > 1;

所有索引上的范围查询 包括主键索引 的type为range key为primary

3、explain select * from num where t1 = 10 and t2 > 10;

extra 为using index condition 表示为索引下推，并且需要回表，因为查询语句为select * ，还需要查询t4 ，因为（t1，t2，t3）联合索引中只有 id，t1，t2，t3的数据。

4、explain select id,t1,t2,t3 from num where t1 = 10 and t2 > 10;

extra为using index 表示为直接利用联合索引（t1，t2，t3）的索引数据，不需要回表，所以using index condition 变成了using where

5、explain select * from num where t1 = 10;

type为ref，key为t1_t2_t3，表示这个查询利用了联合索引（t1，t2，t3）

6、explain select * from num where t1 = 1 and t2 = 10

这个查询语句仍然能利用联合索引。

7、explain select * from num where t2 = 10;

和6相比，7的查询语句无法利用联合索引。

这就叫做最左前缀原则，当利用联合索引的前n个字段进行查询的时候都可以利用联合索引。

8、explain select t1,t2 from num where t2 = 10 and t1 =10

即使在查询语句中的查询条件顺序不符合联合索引，仍然会走联合索引。因为在MySQL中的优化器中会对查询语句优化，找到最合适的索引执行查询。

9、explain select t1,t2 from num where t1 = 10 and t3 > 10;

这种情况也会走联合索引，因为t1是有序的，这时会先找到满足t1=10的第一条记录，然后依次往下遍历，直到t1！=10。

10、如果id是字符型，那么前缀匹配用的是索引，中坠和后缀用的是全表扫描。

select * from num where id like ‘A%’;//前缀都是排好序的，使用的都是联合索引
select * from num where id like ‘%A%’;//全表查询
select * from num where id like ‘%A’;//全表查询

11、explain select t1,t2 from num where t2 = 10;

type为index，直接在联合索引上从头到尾遍历t2，因为t1，t2的数据行存在联合索引（t1，t2，t3）上，遍历联合索引比遍历主键索引效率更高！。联合索引的叶子节点的数据行对应的数据量小，一个数据页能够容纳更多数量的数据行。

12、explain select t1,t2 from num where t4 = 10;

type为all，因为t4的数据不存在于联合索引（t1，t2，t3）上，所以需要从头到尾遍历主键索引。

13、

以上两个查询语句，就因为一个t1的大于10，一个t1大于9，却一个type为range，一个type为index。
t1>=9对应的type为index

t1>=10对应的type为range

为什么呢？
因为t1>=9的数据行中的t3不是有序的
而t1>=10的数据行中的t3是有序的，我猜测有序的话可以利用二分或者其他更加高效的算法来查询数据，所以type为range

索引小技巧

1、是否选用自增主键？
建议一般情况下选择自增主键。
因为如果主键不自增的话，新记录的主键是随机的，插入的时候还要先查询，并且很容易在一个数据页中间插入记录，需要数据页分裂调整索引，效率低下。
如果主键自增的话，直接在叶子节点的链表的最后一个数据页插入记录就行，并且页分裂的概率不大，效率更高。

2、主键越小越好？
主键越小，二级索引对应的叶子节点中的数据行的数据量越小，叶子节点能够容纳的数据行的数量也就越多，效率更高！

3、是否能够使用业务字段作为主键
不太推荐业务字段作为主键，因为业务字段往往不能保证有序插入，写入数据的成本太高。

4、覆盖索引
对于select t1,t2 from table where t1 = "xx"这个查询语句，如果只有对于t1的索引，在查询t1索引后还需要回表，大大降低了效率，这个时候我们可以将t1索引改为（t1，t2）的联合索引，当走（t1，t2）的联合索引时，会直接返回数据，不需要在回表了，提高了效率。