MySQL索引系统

每个节点占用一个磁盘块，一个节点上又两个升序排序的关键字和三个指向子树根节点的指针，指针存储的值子节点所在磁盘块的地址，两个关键字划分成的三个范围域对应三个指针指向的子树的数据的范围域，以根节点为例。p1指针指向的子树的数据范围为小于16，p2指针指向的子树的数据范围为16-34, p3指针指向的子树的数据范围为大于34.

查找关键字28的过程(一共读了16k + 16k + 16k = 48k的数据)

根据根节点找到磁盘块1，读入内存【磁盘I /O操作第1次】
比较关键字28在区间(16, 34)，找到磁盘块1的指针p2
根据p2指针找到磁盘块3，读取内存【磁盘I /O操作第2次】
比较关键字28在区间(25, 31)，找到磁盘块1的指针p2
根据p2指针找到磁盘块8，读取内存【磁盘I /O操作第3次】定位到数据

假设每行记录大小为1k,那么一个磁盘块可以放16行数据。那么 3层的B树可以放16*16*16 = 2^12行记录。(太少了) 把每个非叶子节点中的data域消除，放入到叶子节点中去，演化成B+树。

4. B+树

在B+树上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+树进行两种查找运算

一是对于主键的范围查找和分页查找
二是从根节点开始，进行随机查找。(底层是双向链表)

假设(p+key)大小为10B,则非叶层可以放置1600行记录，共可以放置 1600*1600*16行记录，和B树相比，大大提高。(粗略的估计算法)

问题:mysql种的B+树到底是3层还是4层(取决于数据量，非固定的，degree也是计算出来的，而非指定的，根据key的具体类型来定，int和long都是不同的，索引越小越好)

MyISAM和B+树都是采用的B+树的索引结构，二者的区别在于叶子节点中data中的内容是不同的。

InnoDB 叶子节点直接放置数据 -聚簇索引(索引和数据是放在一起的)

InnoDB是通过B+树结构对主键创建索引，然后叶子节点存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6字节的row_id来作为主键。
如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键(没有就是唯一键，再没有就是row_id)，然后再通过主键索引找到对应的记录。叫作回表。

为什么关系型数据库中推荐使用主键自增?

如果是单体的话，强烈推荐，只要往最后一个节点添加数据，但如果是乱序的话，可能会往树的中间添加节点，需要更多的页分裂，页合并等等，系统维护起来比较麻烦。分布式中可以使用雪花算法。

MyISAM中放置的是数据行的地址-非聚簇索引

(4）前缀索引

(5) 索引相关名词

1. 回表

查询两颗B+树，先从name的B+树中，再从id的B+树中。二级索引，或者辅助索引会产生回表。

2. 索引覆盖

select * from table where name = ?
select id from table where name = ?

不需要触发回表。

3. 索引下推

select * from table where name = ? and age = ?

数据存储磁盘
mysql有自己的服务
mysql服务要和磁盘发生交互

组合索引如何存储？

所以一定要先拿到第一列，然后再匹配第二列的内容。

没有索引下推

1. 先从存储引擎中拉取数据(根据name筛选的数据)

再mysql server 根据age进行数据的筛选有索引也就是会先把(1, 2) 和(1,3) 都读入内存，然后再由mysql服务进行筛选出(1,3)

有索引下推后会在拉取数据时IO-mysql5.6之后使用

下推的时候直接根据name,age来获取数据，不需要server做任何的数据筛选。从数据服务层下推至数据引擎层。也就是只会把(1,3)读入内存，IO量比无索引下推的要少很多，性能可以有所提升。

select t1.name, t2.name from t1 join t2 on t1.id = t2.id

先做表连接，然后查询需要的字段。
先把需要的所有字段都拿出来，然后再做关联。(IO量明显比第一种方式少， 谓词下推)

索引下推的唯一缺点就是需要在磁盘上多做数据筛选，原来的筛选是放在内存中的，现在放到了磁盘查找数据的环节，这样做看起来成本比较高，但是数据是排序的，所有的数据都是聚集存放的，所以性能不会有影响，而且整体的IO量会大大减少，反而会提升性能。

4. 最左匹配

组合索引(name age) 类比于在淘宝中的搜索，(先匹配省再匹配市，如果匹配不到省，那也无法匹配到市)

where name = ? and age = ? (会走索引)
where name = ? (会走索引)
where age = ?(不会走索引)
where age = ？ and name = ?(会走索引，因为Mysql中的优化器，会把name放前， age放后)，CBO基于成本的优化 RBO基于规则的优化

5. MRR

mult_range read 如果范围查找name，找到1000个id,然后回表查询记录，如果一一匹配的话，效率很慢，可以先在内存中对所有查询到的id做一个排序，然后再范围查找。

6. FCI

fast index create

插入和删除数据的过程

先创建临时表，将数据导入临时表
删除原始数据表
修改临时表的名字

给当前表添加一个share锁，不会有创建临时文件的资源消耗，还是在源文件中，但是此时如果有人发起DML操作，很明显数据会不一致，所以添加share锁，读取时没有问题，但是DML会有问题。

三、索引

(1) 索引的分类

主键索引
唯一索引
普通索引(二级索引和辅助索引)
全文索引：文档管理系统 5.6之后支持，但实际都用的是ES服务器
组合索引

(2) 索引匹配方式

全值匹配和索引中的所有列进行匹配
匹配最左前缀只匹配前面的几列
匹配列前缀可以匹配某一列的值的开头部分
匹配范围值: 可以查找某一范围的数据
精确匹配某一列并范围匹配另外一列
只访问索引的查询

(3) 哈希索引

基于hash表的实现，只有精确匹配所有列的查询才有

在mysql中，只有memory的存储引擎显示支持哈希索引

哈希索引自身只需存储对应的hash值，索引索引的结构十分紧凑，这让哈希索引查找的速度非常块

哈希索引的限制

只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行
哈希索引数据并不是按照索引值顺序存储的，所以无法排序
不支持部分匹配查找，使用索引列的全部内容来计算哈希值
支持等值比较查询，也不支持任何范围查询
访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中过的所有行指针，逐行进行比较，查找到所有符合条件的行
哈希冲突比较多的话，维护的代价也会很高。

案例

当需要存储大量的URL,并且根据URL进行搜索查找，如果使用B+树，存储的内容就会很大

select id from url where url = "" 也可以利用将url使用CRC3做哈希，可以使用一下查询方式:

select id from url where url = "" and url_crc = CRC32("")

此查询性能较高原因是使用体积很小的索引来完成查找。

（4) 组合索引

当包含多个列作为索引，需要注意的是正确的顺序依赖该索引的查询，同时需要考虑如何更好的满足排序和分组的需要。建立组合索引(a, b, c)

语句	索引是否发挥作用
where a = 3	是，只使用了a
where a = 3 and b = 5	是，使用了a, b
where a = 3 and b = 5 and c = 4	是，使用了a, b, c
where a = 3 or where c = 4	否
where a = 3 and c = 4	是，只使用了a
where a = 3 and b = 5 and c = ?	是，使用了a， b
where a = 3 and b like "%xx%" and c = ?	是，只使用了a