【MYSQL数据库一】基本概念、控制语句、底层数据结构

【MYSQL数据库一】基本概念、控制语句、底层数据结构

一、SQL控制语句分类介绍:

使用SQL语句，可以完成对数据库信息的检索、更新数据库信息、修改表结构，标准的SQL语句有五种类型。
查询语句:数据库查询语句，通常用select完成，查询数据库信息。
DDL语句:数据库定义语句，通常由drop、create、alter、truncate组成。
DCL语句:数据库控制语句，通常由grant、revoke两个关键字组成，DCL语句一般用于对数据库用户授权或者指定用户权限的回收。
DML语句:数据库操作语句，通常由insert、update、delete构成。
事务控制语句:由commit、rollback、savepoint构成。
注意:DDL语句中truncate性能一般优于delete，因为delete需要记录与事务相关的日志redo，undo等，但truncate不需要记录日志，使用时需谨慎。

二、MYSQL相关术语

● 数据库: 一些关联表的集合，在data目录下一个数据库对应一个文件夹。
● 数据表:表是相关数据的集合，结构相当于多行多列的excel表格。
● 表空间:一个表涉及的索引文件、数据文件等集合，可以看作为一个大的文件集合。
● 段:表空间由多个段组成，段分为叶子节点段和非叶子节点段以及回滚段。
● 组:一个段由多个组组成，一个组大约为256M，因为一个表最大可以存储64TB的数据，在物理上以页面为单位不好管理。
● 区:一个组由多个区组成，一个区大约为1M，一个区包含64个页，目的是1.方便页面的管理 2将物理上的随机IO转换成顺序IO。
● 页:MYSQL的最小读取单位，一个页为16KB，64个页为一个区。
● 列:包含了相同的数据，eg: select name from T。
● 行:包含一条数据的所有属性元素，select * from T where ID = 1，查看id为1的一行数据。
● 冗余:存储了重复的元素，冗余浪费了存储空间，损耗了部分场景的性能，但是一定程度上提高了查找效率。
● 主键:每个表中都会设置主键，这个主键唯一不重复，是数据的唯一特性。
● 外键:两个表在业务相关的场景之下，可以用外键来定义约束。
● 索引:排好序的数据结构，一般用于加快查询，实际上是减少了对数据的查询，减少了回表。
● 复合键:也称为组合键，多个列看做为一个索引键，一般用于联合索引的建立。
● 约束: constraint，执行数据的校验规则，一般用于校验数据的完整性。
● 视图(view):一个或者多个表中的逻辑显示，不存储数据，仅仅显示数据。
● 函数(function):用于完成一次特定的计算，具有一个返回值。
● 触发器(trigger): 类似于监听器，当被监听的对象(数据库)发生了特定事件后，触发器被触发，完成相应的处理。
● 存储过程(proceduren): 用于完成一次完整的业务处理，无法回值，但是可以以参数的形式传递参数给调用环境。
● 参照完整性: 参照完整性中不允许关系中引用不存在的实体，参照完整性是关系模型的完整约束之一，属于数据完整性的一种，其余还有：实体完整性、用户自定义完整性。举个列子:一个film表和film_actor关联表，两个表以film_id为关联字段，如果仅仅删除film表中film_id为1的字段，那么会导致对应关系错乱，所以这时候一般可以使用外键约束来保证参照完整性，
存储过程、视图、触发器都不常用于JDBC编程。

三、MYSQL底层数据结构

3.1索引数据结构分类

● 二叉树
● AVL树
● 红黑树
● Hash表
● B Tree
● B+ Tree

二叉树

左边节点小于父节点，右边节点大于父节点

但是如果遇到单边增长的数据，或者表中涉及到数据的倾斜，那么二叉树的结构会退化成链表，如下图所示:

从上图可知，在数据量很大的情况下，如果表中有数据倾斜，那么查询的时间复杂度会变成O(n)，大大的降低查询性能，与全表扫描没任何区别，所以这也是MYSQL没有利用二叉树这种数据结构来存储数据的原因。

AVL树(绝对平衡二叉树)

简介:

AVL树相比于二叉树来说增加了节点平衡的功能，是带有平衡条件的二叉树，一般是使用平衡因子差值来判断节点是否平衡，并通过旋转来实现节点的平衡，当左右子树高度超过1时，AVL树会通过旋转节点来实现树的平衡，与红黑树相比，AVL树是绝对平衡二叉树，平衡的条件是:所有节点的左右子树高度绝对值不超过1，不管是执行插入还是删除操作，只要不满足平衡条件，那么就需要通过旋转来平衡次树，旋转是非常消耗性能的操作，所以在插入删除多，查询少的场景红黑树性能优于AVL树，对于新增删除比较少，查询比较多的场景AVL树性能会优于红黑树(绝对平衡二叉树，树的高度要比红黑树小)

左子树高度为2，右边子树高度为3，此时绝对值相差刚好为1，再往右子树插入节点99，那么会进行旋转，如下图。

局限性:

由于维护这种树的高度的代价高于从中获取的收益，所以在实际应用中，更多地会选择追求局部性而不是非常严格的整体平衡的红黑树，当然，如果在插入删除比较少(涉及到的节点旋转比较少)的场景，AVL树的性能还是优于红黑树的。MYSQL并没有使用此数据结构的原因是: 1.如果表中的数据量比较大，这种数据结构的度很小，树的高度会很高，查找数据需要经历多次IO才能定位到需要的数据，不适合数据量大的场景。2.插入删除节点的旋转次数太过频繁。

应用

windows NT内核中广泛应用。

红黑树

简介:

一种二叉查找树，但是每个节点都有自己的储存位表示自己当前节点的相应颜色，每个节点非黑即红，通过任何一条从根节点到叶子节点的着色方式的限制，任何两条路径的绝对值差都不会超过两倍，如果超过了这个值，就会发生树的平衡旋转。

性质:

1.每个节点非黑即红。
2.根节点是黑色。
3.每个叶节点(叶节点即树的尾端NULL指针或NULL节点)都是黑色的。
4.如果一个节点是红色那么它的两个子节点是黑色。
5.任意节点到叶子节点null指针路径上包含的黑色节点为空。
6.任意两条路径包含的黑色节点都相同。

应用:

1.广发应用于C++的STL中，地图合集都是用红黑树实现的。
2.linux的进程调度完全的公平和非公平调度程序，进程的控制块就是用红黑树来管理的，进程的虚拟内存区域都存在一棵红黑树上，每个虚拟地址区域都对应一个红黑树的节点，左指针指向相邻虚拟内存的低地址区域，右指针指向相邻虚拟内存的高地址区域。
3.java jdk1.8 当链表长度到了8这个阈值，链表转红黑树。jdk中的TreeMap也是用红黑树实现的。
4.IO多路复用的实现epoll中的sockfd采用的是红黑树管理的，为了支持更快的增删改查。
5.ngnix的定时器，可以快速的找到路径最短的定时器，以达到公平的效果。

Hash表

简介:

假设一个数据为0-A，我们都知道hash索引是以key-value键值对的形式存储数据的，底层实现一般是数组加链表， Hash索引会取需要存储字段的值做hash运算，然后再通过对数组/槽位取模运算，使其分布在数组之上，并且以存储的值为key，以存储数据所在行的地址为value进行存储。

性质:

优点: 很多时候，如果没有发生hash冲突的情况下，往往一次查找就能获取到我们锁需要的数据，时间复杂度为O(1)。这种数据结构有时候查询性能往往比B+树结构效率更高。
缺点:
1.因为hash的存储特性，一些特性相同的key通过hash计算出来，可能在磁盘上隔得很远，对于范围查询不支持。
2.hash索引存储的是字段的值和该行所在的磁盘文件地址，需要先对查询的字段做hash运算，得到hash值定位到具体的数组位置，取出该位置上的数据，再到磁盘上找具体的数据记录，需要历经两次查找。
3.hash冲突，退化成链表，时间复杂度为O(n)。
4.仅仅支持in or = <=>查询，不支持like 或者范围等查询。

B树

简介:

B树，是一种多路平衡二叉树，这里的B并不是Binary而是Balance，对于红黑树和AVL树来说，做了横向扩展，为一个节点划分16k大小的空间，为了横向能存储更多的节点，大大的降低了树的高度。从B树的数据结构内部来看，它也是一棵二叉树，左子树的数据小于父节点的数据，右子树的数据大于等于父节点的数据。

性质

1.叶子结点指针为空，叶子节点到根节点的距离相同，具有相同深度。
2.所有节点元素不重复(对于主键索引来说)。
3.节点内和节点之间数据从小到大依次排列。
4.叶子结点和非叶子节点都会存储数据。
优点:
1.一个节点可以存储更多的数据，在相同数据量的情况下，树的高度可以控制的比红黑树和AVL树小。
2.由于是排好序的数据结构，在某些情况，可以有效的利用索引快速定位，快速的定位到需要的数据。
缺点:
1.如果数据量很大，到了几百万，千万级别，树的高度会很高，因为一个节点内存储下一个页面的指针大小为6字节，一条数据假设为几百到1k之间，每个节点都会存储该索引所在的整行数据，这样的话一个节点能存储十几到几十条数据，20^x = 1000000，x = log20(1000000) ≈ 5，一百万的数据大概在5层左右，也就是说在100w的数据量中查询所需要的数据需要经历5次的磁盘IO，如果数据量越大，树的高度会越高。
2.因为叶子节点的指针为空，所以对于范围查询，需要多次从根节点访问叶子节点，对于区间的访问性能并没有很大的提升。
3.因为一个节点能存放的数据量太少，可能一次查询需要多次从内存或者磁盘获取需要的数据。

B+树

简介:

B+树，也是一种多路平衡二叉树，为了减少内存的访问与磁盘的IO次数，在B-树的基础之上做了一定的优化，在非叶子节点中不存储数据，仅仅只存索引字段的值，这样的话一个页16k就能存更多的索引数据了。在叶子节点中包含了所有的字段，所以非叶子节点的索引是冗余的，可能重复的，牺牲了一部分的存储空间，提升了数据查询的稳定性，尤其是针对数据量比较大的场景，稳定的只需要几次内存访问加磁盘IO就能找到目标数据。并且在叶子节点增加了双向指针，针对范围查询等区间访问的数据搜索功能只需要从叶子节点依次访问即可。

性质

1.叶子节点不存储索引所在行的数据，只存储冗余索引，增大了度，降低了树的高度。
2.叶子节点包含了表中完整的数据，或者相关主键的值。
3.叶子节点有双向指针，大大的提高了区间访问性能。
4.节点内和节点之间数据从小到大依次排列，MYSQL8.0之后支持从大到小排列。

聚集索引

查看页大小:

show variables like "innodb_page_size";
show global status like "innodb_page_size";

3.2 索引的类型划分

数据结构角度

B-Tree(B+ Tree)

在MYSQL5.55版本之后，默认的存储引擎就是Innodb，如果不设置默认的存储引擎，MYSQL会默认适用Innodb作为存储引擎，存储引擎是基于表的，而不是基于数据库来设置的，也就是说建表的时候可以设置MYSQL使用何种存储引擎。Innodb和MYISAM使用的索引数据结构就是B+树，是B-Tree的变种，大多数MYSQL存储引擎都支持B-Tree这种数据结构除了Archive引擎，在5.1之前它并不支持索引，直到5.1才支持Auto Increament类型的索引。不同的存储引擎，使用的存储数据结构也可能不一样，比如MYSQL + NDB集群，NDB存储引擎实际上使用的是T-Tree，其名字是B-Tree，Innodb是B+Tree。 对于同一种存储数据结构，不同的存储引擎内部对于存储结构的使用方式不同，也会导致性能的不一样，各有优劣，比如MYISAM中的B+Tree，叶子节点存储的是数据的物理地址，而Innodb存储的是主键Id或者整行数据，MYISAM的行是以前缀压缩的形式来存储的，为了让索引更小，行格式为compressed ，而Innodb使用的是以完整原始数据行的形式来存储的，行格式为Dynamic。

B+Tree索引能够加快通过对应索引来访问表数据的速度，因为存储引擎不需要再对数据进行全表扫描再逐行过滤，扫描的结果集范围大大地减小，取而代之的是从根节点开始，通过二分查找/折半查找的方式快速的定位到数据所在的子节点地址，这个子节点的地址空间称为槽，再对应加载子节点的索引数据页进行二分查找最终定位到数据所在的叶子节点的物理地址，加载对应的数据页，存储引擎要么是找到对应的值，要么找到的记录不存在，之后再返回给MYSQL执行器进行过滤。
B+ Tree叶子结点比较特别，指针指向的是被索引的数据，根节点到叶子节点之间可能有多层，这个层数跟数据量的大小成正相关。因为B+ 树内的数据是已经排好序的，并且叶子节点之间有双向指针相连，所以对于范围查询B+树提供了很好的支持，比如查询a-c开头的名字，可以很好的支持

create table people(
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `dob` date DEFAULT NULL,
  `hobby` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_dob_hb` (`name`, `dob`,`hobby`)
);

B- Tree类型的索引适合全键值、键值范围、键值前缀查询。其中键值前缀只适用于根据最左前缀的查找。

联合索引匹配规则:

全值匹配:指的是和索引所有的列都进行匹配，比如 where name = ‘a’ and dob = 1998-01-12 and hobby = “rap”。
匹配最左前缀:指的是仅使用了联合索引开头的部分列，比如where name = a;
匹配列前缀:指的是仅使用了联合索引开头的部分列，并且是使用了列的值的某一部分，比如 where name like “a%”。
匹配范围值: 指的是查询仅使用了联合索引开头的部分列，并且查询的是某个范围，比如where name > “a” and name < “c”
精准匹配某列并范围匹配另一列:指的是使用了开头部分精准匹配，后面的索引部分使用的是范围匹配，比如where name = “a” and dob > “1998-01-12” and dob < “2022-09-12”;
只访问索引树上的查询:覆盖索引，查询仅仅只访问索引树上包含的数据值，无需访问主键索引上的其它行。

因为B+ Tree中的索引节点是有序的，按照建立联合索引的先后顺序来进行排序，比如先排name，当name相同的时候再排dob。所以除了按值查找之外，还可以根据ORDER BY 操作(顺序查找)，一般来说，索引可以满足ORDER BY后面的排序条件，避免了使用临时内存或者磁盘文件进行排序。
关于B+树索引的限制:
● 如果不是按照索引的最左列开始查找，那么无法使用索引，比如where dob = “1998-02-01”;跳过了name，那么dob就是无序的了。
● 不能跳过索引中间的列，比如where name = “a” and hobby = “rap” 跳过了联合索引中间列dob。
● 如果查询中使用了某个列的范围查询，优化器可能不会使用这个列右边的索引进行优化查询。比如 where name = “a” and dob > “1998-03-09” and hobby = “rap” ; 因为dob为范围查询，优化器就不能进行全值匹配，就不会使用hobby来进行索引来加快查询，只能使用name和dob两个索引字段。

所以，联合索引的列顺序是至关重要的，查询的时候尽量按照建立索引的顺序来作为查询条件从而达到加快查询的目的，而且相同的列建立不同顺序的索引，可以满足不同类型的查询需求，进而对查询性能进一步的优化。

哈希索引

在前面已经讲过这种类型的索引，所以在这里仅仅只增加几点:
1.Memory引擎支持唯一性Hash索引和非唯一性Hash索引，并且Memory存储引擎是默认Hash为其使用的索引类型，也就是说Memory引擎是以数组加链表的形式来维护Hash索引的，Memory还可以支持B-Tree索引。NDB集群仅仅支持唯一HASH索引，
2.NDB集群仅仅支持唯一性Hash索引。
3.Innodb内部的自适应HASH索引也是使用的这种数据结构，对于某些使用非常频繁的索引，MYSQL内部会对应为这些使用频繁的索引，取其前缀再建立一次hash索引，从而可以快速的定位到内存中的数据页，这种自适应hash索引对于范围或者模糊匹配是无法支持的，并且占用Buffer Pool的内存空间，这是一个完全自动化的，内部的行为，用户无法控制或者配置。如果不需要使用可以关闭。
4.如果引擎不支持hash索引，可以效仿innodb来建立伪Hash索引，思路为: B+ Tree + 触发器。
比如: 对于URL网站地址的查询，如果使用B+树作为URL索引的存储结构，那么索引树会变的很大，URL本身很长，比如查询

伪Hash索引的使用

mysql> select * from url where url = "http://www.baidu.com";

可以先对原来的列上的二级索引URL删除(视情况而定)，新增一个URL对应的hashCode列url_crc，这个这个列就是使用哈希函数对url取hashCode得到的值，查询的时候where条件需要带上对应的哈希函数。

mysql> select * from url where url = "http://www.baidu.com" and url_crc = CRC32("http://www.baidu.com")

在这里就可以看出来，其实实现思路就是:将B+树叶子节点的数据全部取出，然后利用url的哈希码url_crc来快速定位相关的数据，这样做的性能会非常高，即使出现hash冲突(字段hash后的选择性很高,哈希码为3500265894)，这种情况概率也很低，查询仍然会很快。
但是这种伪hash索引需要手动维护或者使用触发器进行维护，因为如果做了数据的修改或者新增，需要先对url进行hash运算，再存url对应的哈希码。

CREATE TABLE pseudohash(
id int UNSIGNED NOT NULL auto_increment,
url varchar(255) not null,
url_crc int UNSIGNED not null default 0,
PRIMARY KEY(`id`)
)

手动维护:
插入时:

INSERT INTO `mysql`.`pseudohash` (`id`, `url`, `url_crc`) VALUES (1, "http://www.baidu.com", CRC32("http://www.baidu.com"));

更新时:

UPDATE `mysql`.`pseudohash` SET `url`='http://www.google.com', `url_crc`= CRC32("http://www.google.com") WHERE (`id`='2');

触发器维护:

DELIMITER //
CREATE TRIGGER pseudohash_crc_ins before insert on pseudohash for each row BEGIN
SET NEW.url_crc=CRC32(NEW.url);
END;
//
CREATE TRIGGER pseudohash_crc_upt before update on pseudohash for each row BEGIN
SET NEW.url_crc=CRC32(NEW.url);
END;
//

验证触发器是否生效:

UPDATE `test`.`pseudohash` SET `id`='3', `url`='www.baidu.com' WHERE (`id`='3');

INSERT INTO `test`.`pseudohash` (`id`, `url`, `url_crc`) VALUES ('4', 'http://www.hao123.com', '0');

尽量使用CRC32hash算法来维护hashCode，这样占的空间比较小，如果用md5()或者SHA1()计算出来的字符串会非常的长，浪费空间。如果表的数据量非常的大，推荐自定义哈希函数，如果想要避免hash冲突的问题，并且查询的时候不仅要带上hash值还需要带上列的值。

空间数据索引(R-Tree)

MYISAM存储引擎支持空间索引R-Tree，可以用作空间、地理信息、三维坐标存储，本质上就是三维表。Mysql的GIS支持并不完善，推荐使用PostgreSql中的PostGIS。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键字，而不是直接比较索引中的值，将文本拆解成一个个词元，然后利用索引倒排的方式加快文本关键字的查询。全文搜索和其它鸡肋索引的匹配方式完全不一样，它有许多要注意的细节，如:停用词，词干和负数，布尔搜索等，全文索引更像是搜索引擎要做的事情，而不是简单的where。
在相同的列上同时创建全文索引和基于值的B-Tree索引不会冲突，全文索引使用与MATCH AGAINST操作，而不是普通的where条件操作。

其它类别的索引

还有很多第三方的存储引擎使用不同类型的数据结构来存储索引，比如TokuDB使用分形树索引，这是新开发的一种数据结构，既有B-Tree的许多有点，也避免了很多B-tree的缺点，多数情况下针对Innodb的讨论也适用于TokuDB。

从物理角度划分

聚集索引

聚集索引:也被称为聚簇索引，或者主键索引，一个表中只有一个聚集索引。索引本身就是由B+树构建，索引和数据在同一棵索引树之上的索引，叶子节点包含了完整的数据记录，这样的索引被称为聚集索引，叶子节点的这些数据不仅在B+树上有序，在物理存储上依然有序。在Innodb中主键索引就是聚集索引，非叶子节点由主键id组成，叶子节点由表中其它数据组成。从物理存储上来看，索引和数据都存储在data目录数据库文件下的.ibd文件中，在MYSQL8.0之前，表结构也有单独的文件.frm存储，8.0之后统一存储在ibd文件之中。

聚簇索引:

非聚集索引

非聚集索引:非聚集索引也叫二级索引或者稀疏索引，辅助索引等，一个表可以有多个非聚集索引，对于MYISAM来说，索引存储在.MYI结尾的文件中，数据存储在.MYD结尾的文件中，同样在MYSQL8.0之前，表结构也有单独的文件.frm存储，8.0之后统一存储在ibd文件之中，像这样索引和数据分开存储的索引称为非聚集索引，Innodb中的唯一索引，联合索引，前缀索引都属于二级索引，在非聚集索引中，叶子节点的存储并不是物理连续的，所以不合理的使用索引会导致大量的随机IO，效率可能不如全表扫描。
非聚簇索引:

注意: innodb主键索引是聚集索引，并且每个innodb的表只有一个，其它索引都是二级索引。MYISAM主键索引和二级索引都是非聚集索引，并且结构上是没有区别的，叶子节点存储的都是数据的物理地址。

四、总结以及一些相关问题或面试题

1.Mysql如何通过索引来快速定位数据的？

• 如果是Innodb引擎，执行器会调用存储引擎会先从内存中加载根节点的页面，如果没有会调用引擎接口获取根节点的页面数据，通过二分查找快速定位到数据所在的子节点的地址，也就是槽位，然后再加从内存中或者磁盘上载子节点的页面，继续通过二分查找定位到数据所在的槽位，最终从磁盘.ibd文件上加载到目标数据页到内存中，MYSQL再对结果集页面做进一步的条件过滤。如果where条件建立的是二级索引，获取完叶子节点的页面之后，还需要拿主键ID到聚簇索引树上回表查找所需要的数据。
• 如果是MYISAM引擎和上述步骤类似，只是从根节点开始查询的时候需要通过MYI文件来加载定位索引页，最终获取到叶子节点页面时，再一次到.MYD文件中获取对应的数据。

2.Mysql为什么要用户自定义主键，并且推荐使用整型且自增的列作为主键？

• 如果用户设置了主键，MYSQL会使用用户设置的主键作为构建整张表数据的唯一性索引，如果没有显示的设置主键，那么MYSQL会选取表中的唯一且不为空的索引作为主键，如果也没有设置这样的唯一性索引，那么MYSQL会维护一个8个字节的隐藏列Row_id字段，作为主键。
• 使用整型的原因是因为整型占的空间比较小，二级索引叶子节点存储的值就是主键字段，CPU对于整型字段的排序比字符串或者其它类型的快，一定程度上提高性能，并且可以减少CPU对缓存的访问(整型小，相同数据量情况下，一次可以读取更多的主键到寄存器或者高速缓存中)。
• 使用自增主键的原因是因为: 1.减少页面的分裂与合并，如果以大小无序的字段作为主键，如uuid 可能每次插入的数据在物理上相隔很远(索引在构建时就会进行排序，有序的数据结构)，页面【1,2,4,6】，如果再插入一个5，页面需要进行分裂成【1,2】,【4,5,6】，当某个页数量达到了MERGE_THRESHOLD(页体积的百分之50)会寻找附近的相邻页面进行合并以优化空间使用率 ，变成【1,2,4,5】【6…】。2.增加内存Buffer Pool的利用率，Innodb的select和DML语句都是基于Buffer Pool来操作数据的。如果每次插入自增的主键，仅需要读取该主键值所在的磁盘页到BP中，插入数据即可，如果满了再开辟一个页的空间继续插入。如果不是自增主键，这些主键的分布很大可能不在一个数据页之上，在磁盘不连续，所以每次插入前都需要读取一个页的数据，这些页读到Buffer Pool中不使用就造成了缓冲区的污染。3.避免了叶子节点内数据的排序。

3.唯一索引和普通索引有什么区别?

• 唯一索引维护的成本更高，需要对索引进行唯一性的判断，插入的效率更低，插入数据时，先判断BP中是否有重复的索引，如果有就报索引重复的错误，如果没有那么需要从磁盘读取数据页面到BP中，再进行判断页面中索引是否重复，如果有就报索引重复的错误，对缓存利用率更低，查找性能比较高一点，只需要查询到一条满足的记录就停止匹配。

• 普通索引维护的成本更低，插入的效率更高，并不需要读取数据到Buffer Pool中，只需要往，并不需要做唯一性的判断，查询的效率会更低一点，查询第一条满足条件的记录之后，还需要判断下一条记录是否满足，直到碰到第一个不满足条件为止。

• 唯一性索引不能使用change Buffer更新，所以需要从磁盘读取数据到内存涉及磁盘随机IO访问，这是DB成本最高的操作之一了，而change Buffer可以一定程度上减少随机IO。

4.为什么辅助索引的叶子节点存的是主键值而不是整行数据?

• 节约存储空间，如果辅助索引也存储完整的数据，会浪费磁盘和内存空间，因为二级索引有很多，相同的数据存多份会存在大量的冗余，如果只存主键，可以通过主键ID再到聚簇索引上找到目标记录。
• 一致性，如果辅助索引和聚簇索引都存完整的数据记录，那么维护的时候需要维护多份，并且MYSQL做DML操作的时候，需要对所有索引树的操作成功才算成功，如果一个索引树同步失败会有一致性的问题存在。
• 更新操作维护成本更低，减少了行移动和页面分裂，如果更新涉及了主键更新，是需要先逻辑删除原始数据，再插入更新后的数据的，如果只保存主键ID，那么只需要维护一棵聚簇索引就行了。

5.为什么mysql最终选择了B+树，而不是二叉树，AVL树或者红黑树，B树呢？

• 二叉树: 单边增长的数据会退化成链表，时间复杂度为O(n)和全表扫描差不多。
• AVL树: 称为绝对平衡二叉树，任意两条路径的高度差绝对值不能超过1，如果超过了1那么节点会进行平衡，对于插入删除修改多的场景尽量不要选择这种数据结构，因为涉及到了过多的节点平衡。对于查询多，插入删除修改少的场景，AVL树比红黑树性能更好，因为平衡的次数更多树高度更矮。
• 红黑树: 弱平衡二叉树，节点非黑即红，根节点为黑色，叶子节点指向NULL，当任意两条路径相差的绝对值超过了2倍，那么会进行平衡。当插入删除修改多的场景，性能优于AVL树，因为涉及的节点平衡(分裂)少。
• 不选择红黑树和AVL树的原因: 这两种树虽然对于数据倾斜的情况会进行树的平衡，但是mysql依然没有选择AVL树和红黑树，是因为数据量大的情况这两种树的度太小，树的高度依然会很高，一次IO仅能查询一个节点的数据，无法有效的减少IO次数。
• Hash : 如果where条件之后的字段有索引，where name = “caixukun”,mysql会将这个值进行一次槽位的hash,判断该值在哪个槽位,直接取出这行数据的磁盘地址直接拿去数据。因为槽位长度是固定的，在没有hash冲突的情况下，查询的时间复杂度是O(1)，比B树的性能要好。如果有hash冲突，且hash冲突较多，桶位的数据结构会变成链表时间复杂度为O(n)。
• 不选择hash的原因:1.hash冲突[O(n)]，效率比二叉树还慢 [O(log2N)]。 2.不支持范围查询 >，<，只支持in或者or ，<=>，=。
• B树: 1.叶子节点深度相同，且叶子节点的指针为空。
  2.所有索引元素不重复，叶子结点和非叶子节点都会存数据。
  3.节点内和节点之间以从小到大的顺序排列，不支持降序索引。
• 不选择B树的原因主要是因为两点:
  1):非叶子节点存了数据,减小了度,增高了树的高度。
  mysql给一个节点划分了一个页的大小16kb,如果一行数据为1k,
  那么不计算存储下一个页内存位置的page(6b),那么一页只能存16
  条数据，如果是百万千万级别的表，树的高度会很高。
  2):叶子节点没有指针，意味着当用b树作为mysql数据结构的时候,
  如果进行范围查询，不能利用叶子节点的指针快速的访问下一页，而是需要重新从根节点再一次利用索引快速定位来找到下一页的数据。
• B+树:
  1.非叶子节点不储存数据，只存冗余索引(可能包含了重复的索引键)
  为了增大度，降低树的高度。
  2.叶子节点包含了所有的索引字段以及数据或者主键id。
  3.叶子结点有双向指针，提高了区间访问的性能。