MySQL深度解析--索引

Ⅰ.索引

索引的作用

索引的出现就是为了提高查询效率，相当于数据的一个目录。

索引的模型选择

提高数据读写效率的模型主要有Hash、有序数组、搜索树。

• Hash 数据结构由Hash函数、数组和链表组成。在写入数据时可以直接找到对应位置存放数据，或者直接在对应位置链表后追加数据。写入效率高。但是查询的时候只有第一层数组的时候较快，如果同一个Hash位置有多个值组成的链表，那么就需要遍历该位置所有链表数据，在范围查询时效率低。所以Hash只适用于只有等值查询的场景，比如NoSQL引擎。
• 有序数组中所有元素都是有序的，所以查询（范围和等值）效率较高。但是在更新数据时为了维持数据有序，往往需要大量移动数据，导致更新效率极低。所以，有序数组索引只适用于静态存储引擎。
• 搜索树（B+树、N叉树）使用树形结构可以以相对高的效率来读和写，为了尽可能少的访问数据块，需要使用N叉树，这个N取决于数据块的大小。InnoDB 索引底层使用的就是B+树。

InnoDB的索引模型

在InnoDB中，每一个索引对应一颗B+树
例如：

create table T(
	id int primary key, 
	k int not null, 
	name varchar(16),
	index (k)
	)engine=InnoDB;
	#表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)

上表的索引结构如下

MySQL中的索引分为主键索引和非主键索引

主键索引

叶子节点存的是整行数据
在InnoDB里，主键索引也被称为聚簇索引（clustered index）

非主键索引

叶子节点存的是主键的值
在InnoDB里，非主键索引也被称为二级索引（secondary index）

---

SQL查询时：
如果以主键为条件查询，那会直接搜索主键的索引树，找到结果后返回。
如果以非主键有索引的列作为查询条件，那会先搜索该列的索引树，找到主键后，再搜索主键的索引树（回表），最终返回结果
也就是说，非主键索引的查询会多扫描一颗索引树。因此，在查询时应该尽量使用主键查询。

InnoDB的索引维护

性能

B+树上的数据是有序的，为了维护索引的有序性，在插入索引时如果插入的是中间位置的数据，那就需要挪动后面的数据，空出位置。挪动位置会影响索引维护的效率。

使用自增主键

插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。
自增主键的索引建立，正好是一个递增的过程，不会涉及挪动数据，也不会触发叶子节点的分裂。

而如果使用有业务逻辑的字段做主键，往往不容易保持有序插入，写数据成本相对较高

空间

非主键索引的叶子节点存的是主键的值。这意味着，非主键索引占用空间的大小和主键的长度有直接的关联。主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间就越小。

从性能和存储空间方面考量，自增主键往往是更合理的选择。
但也有一些场景适合用业务字段直接做主键：（K/V）
1.只有一个索引
2.该索引必须是唯一索引

---

Ⅱ.索引的优化

回表：非主键索引拿到主键后，回到主键索引树搜索的过程，称为回表。

1.覆盖索引

执行一个SQL查询，当索引的键值是查询条件，索引的叶子节点是查询结果。也就是说这个查询只需要走这一个索引就可以获得想要的结果，不需要回表。这个索引称为覆盖索引。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

对于一些常用的高频的查询，可以建立条件和结果字段的联合索引作为覆盖索引。减少语句的执行时间。但是建立索引后也会占用一定的空间和资源。需要根据实际情况权衡。

2.最左前缀原则

不管是独立索引还是联合索引，只要满足最左前缀，就可以利用索引来加速检索。
最左前缀可以是联合索引的最左N个字段。也可以是字符串索引的最左M个字符。

根据最左前缀原则，建立索引时要考虑索引的复用能力。
如果有联合索引（a,b） 那么就不需要建立a的单独索引了。

建立索引的原则：

1. 如果通过调整顺序，可以少维护一个索引，那么优先采用这个顺序。
2. 整体索引占的空间要尽可能的少。

例如有两个字段：name 和 age
如果需要一个name和age的联合索引并且两个字段都各自有单独查询的情况，那么就应该把占用空间比较大的name和age建立一个联合索引（name,age），然后给age建立一个单独的索引

3.索引下推

从MySQL5.6开始，引入了索引下推优化，可以在二级索引遍历的过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

---

Ⅲ.普通索引和唯一索引的选择和应用场景

1.原理分析

查询过程

在执行一条SQL查询语句时，先从B+树的树根开始，按层搜索到叶子节点的数据页，然后数据页内部使用通过二分法来定位记录。

• 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

上面两种操作由于都是把数据页拿到内存中来操作，所以差距微乎其微。

普通索引和唯一索引底层都是使用B+树作为数据结构。

更新过程

buffer pool ：内存中存放数据页的区域。
change buffer ：buffer poll 的一部分，用于缓存更新操作。也会被写入磁盘持久化。
merge ：将change buffer 中的操作应用到对应的数据页。

普通索引的更新过程：

1. 如果这个记录要更新的目标页在内存中，那只需要找到对应的位置，插入这个值，结束。
2. 如果这个记录要更新的目标页不在内存中，就将更新操作记录到change buffer，结束。

唯一索引的更新过程：

1. 如果这个记录要更新的目标页在内存中，那就找到数据页中对应的位置，判断有没有冲突，插入这个值，结束。
2. 如果这个记录要更新的目标页不在内存中，那就需要先从磁盘读入该页数据，判断有没有冲突，插入这个值，结束。

两种索引的更新过程，如果数据页都在内存中，则差距微乎其微。但是如果要更新的数据页不在内存中，唯一索引还需要进行磁盘的IO访问，效率明显不如普通索引。

---

2.索引的选择和实践

change buffer的使用场景:

对于写多读少的业务来说，把越多的更新操作缓存在change buffer 然后再 merge 收益就越多。（比如账单类、日志类业务）
但是如果一个业务经常在更新后立刻会查询，那么即使缓存在 change buffer也会被立刻执行的查询操作引发 merge ，这样反而增加了维护 change buffer 的开销。对于这种业务来说，就不应该使用 change buffer。
综上所述，如果一个业务更新后立刻又会查询，那就使用唯一索引。如果更新多查询少，就使用普通索引。

---

3.change buffer 和 redo log

下面用一个完整的更新、查询流程来说明 change buffer 和 redo log 在其中的作用。

更新流程

上图中涉及四个部分：
内存（InnoDB buffer pool）、redo log（ib_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）
操作流程：

1. page1在内存中，直接更新内存。
2. page2不在内存中，就在 change buffer 中记录 add(id2,k2) to page2 这一行。
3. 将上面两个动作记入 redo log （图中3和4）。
4. 事务结束
5. 后台给系统表空间（ibdata1）持久化 change buffer
6. 定时merge 更新磁盘的数据

查询流程

操作流程：

1. 假设page1还在内存，读page1时直接从内存返回数据。
2. 读page2时，需要先把page2从磁盘读到内存，然后应用change buffer 中的操作日志，生成一个正确的版本并返回结果。

redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗（更新不读，查询读）

Ⅳ.给字符串字段加索引

建立索引时可以指定索引字段的长度，指定后生成的索引就是截取该字段字符串前几位的结果（前缀索引）。

alter table SUser add index index2(email(6));

以邮箱为例：
如果使用 email整个字符串的索引结构，执行顺序是这样的：

1. 从index1索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得ID2的值；
2. 到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；
3. 取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email='zhangssxyz@xxx.com’的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
如果使用的是 email(6)索引结构，执行顺序是这样的：

1. 从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；
2. 到主键上查到主键值是ID1的行，判断出email的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
3. 取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；
4. 重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。

在这个过程中，要回主键索引取4次数据，也就是扫描了4行。

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
长度的标准是区分度和可接受损失的比例。

# 算出有多少不同的值
select count(distinct email) as L from SUser;

# 选不同的前缀来看这个值
select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

PS：前缀索引对覆盖索引也有影响，如果使用前缀索引，就意味着必须要回表。可能本来可以用覆盖索引查询的语句也不得不回表。选用时要注意。

---

对于前缀的区分度不够好的情况，可以采用以下解决方法：

1. 倒序存储，倒序建立索引，取数据时再调用reverse函数
2. 使用hash 字段，通过表里的字段来生成一个整数字段作为标识码，并在这个标识码上建立索引。

小结：

1. 直接创建完整索引，这样可能比较占用空间；

2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；

3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；

4. 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。