MySQL(一)中的B+Tree和引擎

MySQL发展

正确创建合适索引，是提升数据库查询性能的基础

索引本质

通过快速检索101，找到磁盘位置，快速查询数据

主键索引是一种特殊的唯一索引，不能有空值

索引是数据库管理系统中的一个排序的数据结构，用来快速查找，更新数据库表中的数据
那他选择什么样的数据结构可以达到效果呢？

为什么MySQL选择了B+Tree？
下面开始介绍各种结构树，来表明我们为什么会选择B+Tree。

二叉树

通过二叉树查找（第一个插入的就是根节点）

平衡二叉树

节点的子节点的高度差不超过1，红黑树就是一个平衡二叉树
一个节点就是一个磁盘块，每一个节点保存的数据是关键字，数据区(指向磁盘位置，加载数据)，通过P1快速的找到5
一个数的节点就是一个页的大小，一个节点只放一个键值浪费空间，当键值越多的时候，指针越多，就会变成多路查找树。


下面是平衡二叉树的存放数据介绍

上面这个高度超过了一，平衡二叉树会做一个旋转，保证树的平衡
下面是一个国外的网站，可以展示树的插入

多路平衡查找树(B-Tree)

绝对平衡树，如何保证绝对平衡，向上合并，保证绝对平衡（使用分裂合并达到平衡）
一个节点16K，我们一般用int类型id作为索引，能短则短，这个一个节点就可以有多个路

B+Tree

支节点只保存关键字和引用，不保存数据，数据全部在叶子节点上
所以保存的关键字会比B-Tree的多
三个节点就有三个分叉

为什么选用B+Tree？

![在这里插入图片描述](https://img-blog.csdnimg.cn/20200206164733386.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDE0OTkwMw==,size_16,color_FFFFFF,t_70
每一个叶子节点有个有序指针指向下一个，有了顺序指针，做范围查找，只需要根据指针找到数据就行，不需要回到根节点再查

一颗B_Tree深度一般是2或者3就可以将数据全部放下了

页

索引信息和数据记录都是存储在页结构，innodb将数据划分为若干个页，默认16kb（16384字节），页作为磁盘和内存交互的几本单位，在数据库，无论读取一行还是多行，都是将这些行所在的页进行加载，数据库io最小单位就是页。页之间不在屋里结构上相连，只要通过双向链表相关联就行，每个数据页中的记录会按主键值从小到大的顺序组成一个单向链表，每个页都会为存储在它里面的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可。

页的上层结构

innodb引擎中，一个区分配64个连续的页，一个区默认16KB*64=1MB

页的内部结构

数据页（保存B+Tree结点），系统页，undo页，事务数据页等。
数据页的存储空间16KB划分为：文件头，页头，最大最小记录，用户记录，页目录，空闲空间和文件尾。

红黑树

最终达到的是：
根节点到叶子节点的最长路径不大于最短路径的两倍。
红黑树一般会放在内存使用，不会放在磁盘上的索引上面使用(TreeMap)

B+Tree索引在MySQL中体现

引擎InnoDB和Myisam

Myisam是数据和索引分别存储，InnoDB没有分开

frm是表定义文件，数据保存在MYD，索引保存在MYI
InnoDB数据和索引在ibd文件中



InnoDB叶子节点中保存数据所有信息

主键是最常用的数据条件，辅助索引叶子节点是主键索引，一旦数据发生迁移，无需修改辅助索引
InnoDB没有主键会创建一个隐式主键

索引知识

列的离散性

离散性越高，选择性越好
面试中碰到有问性别这一列上面可以创建索引吗？
不能，性别只有男女之分，重复度高，还是需要扫描好多的行数，离散性太差，创建索引后，执行计划还需要扫描很多行，没有意义，反而会浪费索引存储空间，因为索引是数据结构，存储在磁盘上面的

联合索引最左匹配原则

已经创建了联合索引就无需创建name的索引了

覆盖索引

回表：辅助索引还需通过主键去查询主键索引，这就是回表
如果查询的字段包含二级索引，辅助索引有保存这个字段，就无需回表操作
一旦命中覆盖索引，可以提高性能
通过explain执行计划查询是否使用到了覆盖索引，这个字段看是否使用到了覆盖索引
可以减少io次数，减少数据量的访问
这就是为什么我们禁止使用*来查询数据的原因

面试时候经常会问我们为什么不用来查询数据，一个原因就是因为如果使用号就不会命中覆盖索引了，避免了回表操作，这样就会增加io交互，效率变低。本来使用覆盖索引就能查询到了，结果你*，查询所有数据，这是辅助索引就没用了，需要回表去查询主键索引，效率低了。没有用的数据也返回，数据库计算负担增加，全部字段从磁盘读取到内存，磁盘io开销大。
主键索引，id，innodb自动为主键id创建一颗名为主键索引的B+Tree，这颗树的特点就是叶子结点上包含了完整的用户记录

索引下推

Mysql5.6后，有了索引下推，
比方name like ‘kk%’ and age = 1，这个在5.6之前只能从name字段找出符合条件的行然后进行回表，到聚集索引上找出数据行，再对age进行对比，5.6之后引入了索引下推优化，在索引遍历过程中，对索引中包含字段先做判断，这里对age进行判断，直接将age字段不满足的数据行排除，减少回表的次数。
索引下推智能存在联合索引里面。
索引下推遇到子查询，遇到函数，非innodb和myisam时候，无法使用索引下推

索引的过滤是在存储引擎进行，数据的过滤是在Server层进行

5.7默认开启icp，数据库自动优化，无需关心，应用二级索引，减少io操作

为什么建议是有自增索引，而不是用无序的值，因为无序的值会使得B+Tree频繁的分裂和合并，效率低。
过长的字段如何建立索引？
可以截取长度作为索引，通过下面语句查看选择度，选择度高的就是你需要截取的长度来作为索引。

创建索引的时候可以选择13位进行创建

什么时候用不到索引？
负向查询 != 和not in可以用到索引
用不用索引最终还是优化器来决定的

对于like，和离散性有关，离散性差用不到索引
语句前面加explain select * from user；

存储引擎

每个表都可以指定存储引擎
无论什么存储引擎都会有frm表结构定义文件

CSV存储引擎，数据的快速导入导出，表格直接转换成csv文件（不介绍了）
Archive，数据存储ARZ文件，不支持事务，至只允许insert和select，应用日志系统 （不介绍了）
Memory存储引擎

面试可能会问Myisam和InnoDB的区别

Myisam是MySQL5.5之前默认的存储引擎

而InnoDB支持行级锁，支持事务，主键索引方式进行数据存储，MVCC（多版本控制）
在5.5版本之后使用InnoDB引擎

innodb不能直接创建一个hash类型的索引
聚集索引：索引的键值的逻辑顺序跟表数据行的物理存储顺序是一致的。主键索引就是聚集索引》而非聚集索引的顺序和表数据行的数据是不一致的
在InnoDB中辅助索引

现在辅助索引中查询，找到主键索引，再到主键索引上面查询数据。
如果没有主键索引呢？不可能没有主键，有隐含的rowid会作为主键索引

索引创建/设计需要注意哪些原则？

1）、频繁出现在where条件字段，order by排序，group by分组的字段可考虑建立索引；where与order by冲突时优先where；

2）、select频繁查询的列，可考虑创建联合索引（覆盖索引，不回表）；

3）、多表join关联查询，on字段两边的字段尽量都要创建索引；

4）、表记录很少不要创建索引（因为索引有存储、修改、删除的开销，通常表数据量较少时MySQL会选择全表扫描）；

5）、一个表的索引个数不能过多；

索引个数过多会造成：

a、空间：浪费空间。每个索引都是一个索引树，占据大量的磁盘空间。

b、时间：更新（插入/Delete/Update）变慢。需要更新所有的索引树。太多的索引也会增加优化器的选择时间。所以索引虽然能够提高查询效率，索引并不是越多越好，应该只为需要的列创建索引。

6）、频繁更新的字段不建议建立索引；频繁更新的字段引发频繁的页分裂和页合并，性能消耗比较高。

7）、区分度低的字段不要建索引；比如性别，只有男和女最多再多一个未知，区分度太低，会导致扫描行数过多，和回表查询次数过多。如果使用索引，比全表扫描的性能还要差。这些字段一般会用在组合索引中。姓名，手机号等区分度比较高的列非常适合建索引。

8）、不建议用无序的值作为索引；例如身份证、UUID。更新数据时会发生频繁的页分裂，页内数据不紧凑，浪费磁盘空间。

9）、尽量创建组合索引，而不是单列索引；因为1个组合索引等同于多个索引效果，节省空间；同时可以使用覆盖索引；

10）、组合索引应该把频繁用到的列、区分度高的值放在前面；频繁使用说明了索引的利用率高，区分度高说明筛选粒度大，这样做可最大限度利用索引价值，缩小筛选范围；

11）、业务上具有唯一特性的字段，即使是组合字段，也尽量建成唯一索引；因为唯一索引对提高查找速度是明显的；同时也可以防止脏数据的产生。

12）、在varchar字段上建立索引时，要指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。因为索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。