mysql主键始终从小到大_Mysql从入门到入神之（四）B+树索引

前言

文本已收录至个人GitHub仓库，欢迎Star：github.com/bin39232820…

种一棵树最好的时间是十年前，其次是如今

我知道不少人不玩qq了,可是怀旧一下,欢迎加入六脉神剑Java菜鸟学习群，群聊号码：549684836 鼓励你们在技术的路上写博客mysql

絮叨

咱们继续来探索mysql。前面咱们了解了mysql的索引的一些基础知识，今天咱们来康康B+树索引git

来复习一下一下昨天的 首先是InnoDB的页存储结构，咱们知道 多个不一样的页组成的是一个双向链表，而每一个页里面的数据行会按主键的大小组成一个单向链表，而且每4到8个数据组成一个槽，每一个槽存储在pageDirectoy里面 ，当咱们要查询页的行数据的时候，能够先定位到页，而后用2分法定位到槽，而后遍历槽，来定位到当前行的数据。(大佬画的图，你们能够好好理解一下)github

其中页a、页b、页c ... 页n 这些页能够不在物理结构上相连，只要经过双向链表相关联便可。

没有索引下的查找数据的方式

第一种，查询的是id主键的一个肯定值，这个好像还不是那么难，首先遍历全部的页，定位到页，从页里面找到槽，从槽里面找到当前行，因此这样说的话，这种若是页数比较多的话，查询也会很慢

第二种，也就是咱们说的全表扫描，一个个去遍历，最后来找到这一行数据，由于这种查询的会很是的慢，因此呢咱们的索引就派上用场了

InnoDB中的索引方案

InnoDB是使用页来做为管理存储空间的基本单位，也就是最多能保证16KB的连续存储空间，而随着表中记录数量的增多，须要很是大的连续的存储空间才能把全部的目录项都放下，这对记录数量很是多的表是不现实的。

咱们时常会对记录进行增删，假设咱们把页中的记录都删除了，页也就没有存在的必要了，那意味着目录项也就没有存在的必要了，这就须要把目录项后的目录项都向前移动一下，这种牵一发而动全身的设计不是什么好主意～

它是怎么来实现 ，页记录 和用户用户记录 ，它每一个行数据中又一个record_type 这个既能够表示，页记录 和用户用户记。它有如下的4种取值方式sql

0：普通的用户记录

1：目录项记录

2：最小记录

3：最大记录

不管是存放用户记录的数据页，仍是存放目录项记录的数据页，咱们都把它们存放到B+树这个数据结构中了，因此咱们也称这些数据页为节点。从图中能够看出来，咱们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其他用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点。数据结构

聚簇索引

上面的B+数 自己就是一个主键索引 咱们也叫聚簇索引，它有两个特色post

使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：性能

存放目录项记录的页分为不一样的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。 (树的每一层都是一个双向链表)

各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。(最后一层的用户数据层也是一个双向链表)

页内的记录是按照主键的大小顺序排成一个单向链表。(页内是一个单休链表，和一个有着顺序的槽目录)

B+树的叶子节点存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了全部列的值(包括隐藏列)。学习

咱们把具备这两种特性的B+树称为聚簇索引，全部完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不须要咱们在MySQL语句中显式的使用INDEX语句去建立(后边会介绍索引相关的语句)，InnoDB存储引擎会自动的为咱们建立聚簇索引。另外有趣的一点是，在InnoDB存储引擎中，聚簇索引就是数据的存储方式(全部的用户记录都存储在了叶子节点)，也就是所谓的索引即数据，数据即索引。优化

二级索引

你们有木有发现，上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥做用，由于B+树中的数据都是按照主键进行排序的。那若是咱们想以别的列做为搜索条件该咋办呢？难道只能从头至尾沿着链表依次遍历记录么？设计

不，咱们能够多建几棵B+树，不一样的B+树中的数据采用不一样的排序规则。比方说咱们用c2列的大小做为数据页、页中记录的排序规则，再建一棵B+树，效果以下图所示：

其实这个呢，和上面的也差不都就是说这个说子节点存放的是咱们的索引列+咱们的主键的数据。若是咱们想要当前那一行的全部数据的话，咱们是须要作一次回表操做的。

联合索引

咱们也能够同时以多个列的大小做为排序规则，也就是同时为多个列创建索引，比方说咱们想让B+树按照c2和c3列的大小进行排序，这个包含两层含义：

先把各个记录和页按照c2列进行排序。

在记录的c2列相同的状况下，采用c3列进行排序

相似于这种，就是先把第一个列作好索引，而后再排第二个列，必须按前后顺序来，因此咱们所说的前缀索引就是这样来的。

索引的代价

在熟悉了B+树索引原理以后，本篇文章的主题是唠叨如何更好的使用索引，虽然索引是个好东西，可不能乱建，在介绍如何更好的使用索引以前先要了解一下使用这玩意儿的代价，它在空间和时间上都会拖后腿：

空间上的代价

这个是显而易见的，每创建一个索引都要为它创建一棵B+树，每一棵B+树的每个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那但是很大的一片存储空间呢。

时间上的代价

每次对表中的数据进行增、删、改操做时，都须要去修改各个B+树索引。并且咱们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不管是叶子节点中的记录，仍是内节点中的记录(也就是不管是用户记录仍是目录项记录)都是按照索引列的值从小到大的顺序而造成了一个单向链表。而增、删、改操做可能会对节点和记录的排序形成破坏，因此存储引擎须要额外的时间进行一些记录移位，页面分裂、页面回收啥的操做来维护好节点和记录的排序。若是咱们建了许多索引，每一个索引对应的B+树都要进行相关的维护操做，这还能不给性能拖后腿么？

建立高性能索引原则

独立的列

什么意思呢？就是咱们where = 后面的条件，必须是独立的一个列，不能是id+1,这种计算，因此有一个原则就是始终将索引列单独放在比较符合的一侧。

前缀索引

好比一个字符串很长，而后你要给这个字段创建索引，若是说他们前几个字段的识别度很高了话，就建议创建一个前缀索引。这样就能够大大的节省索引空间

多列索引

一个常见的错误就是，给每一个列都创建一个索引，这样是错误的，还有就是创建联合索引的时候的顺序是随便填的，这种方式也是错误的。若是你用explain 关键字看到了 索引合并的信息，就说明你这个索引看是否可否优化。

选择合适的索引顺序

假设你有2个列要创建组合索引，那么这个组合索引的列的字段究竟是哪一个先 哪一个后呢？这个是没有必定的标准的，可是默认的条件是若是你有数量少的字段尽可能是放到前面，在不考虑，分组的条件下，这种状况确实是比较快的。

覆盖索引

覆盖索引的意思就是咱们创建索引的时候，我把须要查询的条件一块儿创建一个联合索引，那么查询这些数据的时候，咱们就不须要回表操做了。

尽可能用索引扫描来排序

若是explain中type的结果是index,就说明mysql使用了索引扫描来作排序，

未使用的索引

若是发现有些索引是一直不会使用的索引，建议删除它。

不可使用索引进行排序的几种状况

ASC、DESC混用 对于使用联合索引进行排序的场景，咱们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。

WHERE子句中出现非排序使用到的索引列

总结

索引并非想建就建，凡事都是有代价的吗，咱们只能说权衡利弊

索引通用的一些场景

等值查询

匹配组合索引左边的索引

匹配组合索引的左边的索引的范围查询

匹配等值查询和范围查询

分组查询

排序

索引的一些注意事项

只为搜索，分组，排序的列创建索引

只为数据的识别度高的列创建索引(例如性别就不建议创建索引)

对于字符串的列，若是它的能创建前缀索引，最好就创建前缀索引

为了让页尽可能减小页分裂的状况，最好给主键创建自增

删除没必要要的索引

若是能用覆盖索引的尽可能用覆盖索引，减小回表的次数。

结尾

平常求赞

好了各位，以上就是这篇文章的所有内容了，能看到这里的人呀，都是真粉。

创做不易，各位的支持和承认，就是我创做的最大动力，咱们下篇文章见

六脉神剑 | 文 【原创】若是本篇博客有任何错误，请批评指教，不胜感激 ！