1 索引

MySQL中的索引由MySQL来维护，不需要人为维护，MySQL中的索引分成5类：主键索引、唯一索引、普通索引、全文索引和组合索引。

  1. 主键索引：主键是一种唯一索引，但它必须被指定为primary key，每个表只能有一个主键（主键并代表只有一列数据，因为主键也可以有联合主键，即多个列的唯一即可）
  2. 唯一索引：索引列的所有值都只能出现一次，即必须唯一，但是值也可以为空，空可以出现多次
  3. 普通索引：基本的索引，值可以为空，没有唯一性的限制
  4. 组合索引：表中的多个列组成一个索引，专门用于组合搜索
  5. 全文索引：全文索引的索引类型为FULLEXT，全文索引可以在varchar、char、text类型的列上创建。

2 MySQL存储引擎

MySQL有两种存储引擎，分别是InnoDB和MyISAM，他们的区别如下：

MyISAN InnoDB
索引类型 非聚簇索引 聚簇索引
支持事务 否 是
支持表锁 是 是
支持行锁 否 是
支持外键 否 是
支持全文索引 是 是（5.6以后支持）
适合操作类型 大量select 大量insert、delete、update

B-Tree和B+Tree的区别？

  • B-Tree的叶子节点和中间都存储数据
  • B+Tree中间节点存索引的key，只有叶子节点才存储索引的key对应的记录值

注意：B-Tree的中文名称是B树，而不是B减树，这里需要注意一下。

2.1 InnoDB–B+Tree

注意：

  1. InnoDB是通过B+Tree结构对主键创建索引，然后叶子节点存储记录，如果该表没有主键，那么会选唯一键当成创建索引的”主键“；如果没有唯一键，那么会生产一个6位的row_id来作为创建索引的”主键“。
  2. 如果创建索引的键是表中的其他字段，那么在叶子节点中存储的数据是该条记录的主键而不是整条记录的数据，然后再通过主键索引找到对应的记录。

举个例子，假如这里有一张user表，表中有三个字段，分别是id、name、age。其中id是主键，然后在name这一列上创建了一个索引。那么当在查询语句的条件中使用了name作为条件的话，此时在MySQL的底层会走两次B+Tree的索引树，这个过程也叫回表。具体流程如下：

  1. 首先通过name的值在name的索引树进行查找，上面介绍过，如果创建索引的键是表中的其他字段，即不是主键，那么在索引树的叶子节点中存放的是该表的主键，而不是该条记录。那么此时使用name作为条件进行查找的时候，会先走name的索引树，如果在name索引树中找到了相应的值，那么此时就到了叶子节点，也就拿到了该条记录对应的主键的值，然后MySQL再拿着这个主键的值去以主键为索引的索引树中查找那条记录的值，也就是说走了两次B+Tree索引树。

  2. 也就是说如果使用的是非主键索引进行查找的时候，要比使用主键索引进行查找更慢，但是这个影响有限，因为每次IO从磁盘中读取的一个数据块的大小大概是4KB，这个大小对电脑的性能影响比较小。

2.2 MyISAM–B+Tree

注意：

  1. MyISAM引擎的索引结构和InnoDB类似，但是有区别，就是InnoDB会将主键索引所对应的那条记录的数据和索引值放在叶子节点，而MyISAM只是将该条记录存放的地址放在叶子节点，当通过索引找到对应的值之后，还需要根据这个地址去对应地址中取数据，这个过程需要耗费一定的时间，但是MyISAM创建的索引树会相比InnoDB创建的索引树更轻量。

2.3 memory存储引擎

hash索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。Memory引擎默认使用的是此种索引。

存储引擎对所有的索引列计算出一个哈希码，将哈希码存储在索引中，同时哈希表中保存每个数据行的指针。这样，对于此种索引查找速度是非常快的。出现哈希值碰撞的话，索引会以链表的形式存放多个记录指针到同一个哈希条目中。

举个🌰：

name age
Jane 28
Peter 20
David 30

假设使用假想的哈希函数f()，生成对应的设想值：

  • f(‘Jane’) = 2323
  • f(‘Peter’) = 2456
  • f(‘David’) = 2400

则哈希索引的数据结构如下：

槽(slot) 值(value)
2323 指向第1行指针
2400 指向第3行指针
2456 指向第2行指针

对于select * from user where name = 'Jane'那么直接先算Jane的哈希值，然后根据Jane的hash值2323去找到对应的第一行数据，查询速度相对于B-Tree索引是要快，但是也有一些局限：

  • hash索引中只有hash值和行数的指针，因此无法直接使用索引来避免读取行，但是因为这种索引读取快，性能影响不明显。
  • hash索引不是按照索引值顺序存储，无法使用于排序。
  • 不支持部分列匹配查找，这里面是使用索引列的全部内容来计算哈希值，例如(A,B)两列一起建索引，单纯使用A一列，那么就无法使用索引，B-Tree索引的话，因为支持匹配最左前缀，所以这种情况适用性偏好。
  • 哈希索引只支持等值查询，包括=、in()、<=>，不支持where age > 10 这种范围查询。
  • 哈希冲突很多的话，维护索引操作的代价也很高

3 索引的最左匹配原则

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

　　要想理解联合索引的最左匹配原则，先来理解下索引的底层原理。索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。

举例：创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。

img-1

　可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可发现a在等值的情况下，b值又是按顺序排列的，但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。所以b=2这种查询条件没有办法利用索引。

　　由于整个过程是基于explain结果分析的，那接下来在了解下explain中的type字段和key_lef字段。

  1. type：联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:（重点看ref,rang,index）

    1. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计
    2. const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const
    3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
    4. 注意：ALL全表扫描的表记录最少的表如t1表
    5. ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
    6. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引。
    7. index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）
    8. ALL：Full Table Scan，遍历全表以找到匹配的行

  2. key_len：显示MySQL实际决定使用的索引的长度。如果索引是NULL，则长度为NULL。如果不是NULL，则为使用的索引的长度。所以通过此字段就可推断出使用了那个索引。

    计算规则：

    1. 定长字段，int占用4个字节，date占用3个字节，char(n)占用n个字符。
    2. 变长字段varchar(n)，则占用n个字符+两个字节。
    3. 不同的字符集，一个字符占用的字节数是不同的。Latin1编码的，一个字符占用一个字节，gdk编码的，一个字符占用两个字节，utf-8编码的，一个字符占用三个字节。（由于我数据库使用的是Latin1编码的格式，所以在后面的计算中，一个字符按一个字节算）
    4. 对于所有的索引字段，如果设置为NULL，则还需要1个字节。

示例：
首先创建一个表

img-2

该表中对id列.name列.age列建立了一个联合索引 id_name_age_index，实际上相当于建立了三个索引（id）（id_name）（id_name_age）。

下面介绍下可能会使用到该索引的几种情况：

3.1 全值匹配查询时

img-3

img-4

img-5

　　通过观察上面的结果图可知，where后面的查询条件，不论是使用（id，age，name）（name，id，age）还是（age，name，id）顺序，在查询时都使用到了联合索引，可能有同学会疑惑，为什么底下两个的搜索条件明明没有按照联合索引从左到右进行匹配，却也使用到了联合索引？ 这是因为MySQL中有查询优化器explain，所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高，最后才能生成真正的执行计划，所以不论以何种顺序都可使用到联合索引。另外通过观察上面三个图中的key_len字段，也可说明在搜索时使用的联合索引中的（id_name_age）索引，因为id为int型，允许null，所以占5个字节，name为char(10)，允许null，又使用的是latin1编码，所以占11个字节，age为int型允许null，所以也占用5个字节，所以该索引长度为21（5+11+5），而上面key_len的值也正好为21，可证明使用的（id_name_age）索引。

3.2 匹配最左边的列时

img-6

　　该搜索是遵循最左匹配原则的，通过key字段也可知，在搜索过程中使用到了联合索引，且使用的是联合索引中的（id）索引，因为key_len字段值为5，而id索引的长度正好为5（因为id为int型，允许null，所以占5个字节）。

img-7

　　由于id到name是从左边依次往右边匹配，这两个字段中的值都是有序的，所以也遵循最左匹配原则，通过key字段可知，在搜索过程中也使用到了联合索引，但使用的是联合索引中的（id_name）索引，因为key_len字段值为16，而(id_name)索引的长度正好为16（因为id为int型，允许null，所以占5个字节，name为char(10)，允许null，又使用的是latin1编码，所以占11个字节）。

img-8

　　由于上面三个搜索都是从最左边id依次向右开始匹配的，所以都用到了id_name_age_index联合索引。

　　那如果不是依次匹配呢？

img-9

　　通过key字段可知，在搜索过程中也使用到了联合索引，但使用的是联合索引中的（id）索引，从key_len字段也可知。因为联合索引树是按照id字段创建的，但age相对于id来说是无序的，只有id只有序的，所以他只能使用联合索引中的id索引。

img-10

　　通过观察发现上面key字段发现在搜索中也使用了id_name_age_index索引，可能许多同学就会疑惑它并没有遵守最左匹配原则，按道理会索引失效，为什么也使用到了联合索引？因为没有从id开始匹配，且name单独来说是无序的，所以它确实不遵循最左匹配原则，然而从type字段可知，它虽然使用了联合索引，但是它是对整个索引树进行了扫描，正好匹配到该索引，与最左匹配原则无关，一般只要是某联合索引的一部分，但又不遵循最左匹配原则时，都可能会采用index类型的方式扫描，但它的效率远不如最做匹配原则的查询效率高，index类型类型的扫描方式是从索引第一个字段一个一个的查找，直到找到符合的某个索引，与all不同的是，index是对所有索引树进行扫描，而all是对整个磁盘的数据进行全表扫描。

img-11

img-12

　这两个结果跟上面的是同样的道理，由于它们都没有从最左边开始匹配，所以没有用到联合索引，使用的都是index全索引扫描。

3.3 匹配列前缀

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

1
2
3
select * from staffs where id like 'A%';//前缀都是排好序的，使用的都是联合索引
select * from staffs where id like '%A%';//全表查询
select * from staffs where id like '%A';//全表查询

3.4 匹配范围值

img-13

　在匹配的过程中遇到<>=号，就会停止匹配，但id本身就是有序的，所以通过possible_keys字段和key_len 字段可知，在该搜索过程中使用了联合索引的id索引（因为id为int型，允许null，所以占5个字节），且进行的是rang范围查询。

img-14

　　由于不遵循最左匹配原则，且在id<4的范围中，age是无序的，所以使用的是index全索引扫描。

img-15

　不遵循最左匹配原则，但在数据库中id<2的只有一条（id），所以在id<2的范围中，age是有序的，所以使用的是rang范围查询。

img-16

　不遵循最左匹配原则，而age又是无序的，所以进行的全索引扫描。

3.5 准确匹配第一列并范围匹配其他某一列

img-17

　　由于搜索中有id=1，所以在id范围内age是无序的，所以只使用了联合索引中的id索引。

4 相关名词

4.1 索引覆盖

假设有一张user表，表中有4个字段，分别是id、name、age、gender，其中id是主键，由于大部分情况需要根据name进行查找，所以在name这一列上创建了一个索引，假如有以下两个SQL语句：

1
2
select * from user where name="lisi";
select id from user where name="lisi";

第一个SQL语句通过name进行查找，首先在name索引的B+Tree树中查找到对应的那条记录的主键id的值，但是由于这里查找的所有字段，但是在name索引树的叶子节点中只存放了主键的值，所以需要进行回表查找

第二个SQL语句中同样也是用name进行查找，但是这里查找的结果是id，由于id是主键，所以在name索引树的叶子节点就能直接拿到id的值，不再需要回表查询，这个动作叫做相关名词：

4.2 索引下推

索引条件下推，Index Condition Pushdown，简称ICP，是MySQL内部通过索引查询数据的一种优化方法，简单来说就是将原本需要在Server层对数据进行过滤的条件下推到了引擎层去做，在引擎层过滤更多的数据，这样从引擎层发送到Server层的数据就会显著减少，从而优化性能。

注意：只有组合索引中才会出现索引下推，否则不会使用到索引下推。

假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。那么，查询语句是这么写的：

1
select * from tuser where name like '张 %' and age=10 and ismale=1;

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。
下面图1、图2分别展示这两种情况。

图1 图2

图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

Reference

写在最后

欢迎大家关注鄙人的公众号【麦田里的守望者zhg】，让我们一起成长，谢谢。
微信公众号