索引结构-存储引擎

懵逼树下你和我

二叉树

（左子树和右子树是有顺序的，不能颠倒：左小右大）
用二叉树做索引结构时，如果索引列是单边增长时:1,2,3,4,5,6,二叉树高度太高

平衡二叉查找树

平衡二叉搜索树（Self-balancing binary search tree）又被称为AVL树（有别于AVL算法），且具有以下性质：
它是一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树

红黑树

R-B Tree是一种不严格的平衡二叉查找树，又称对称二叉树
添加新节点时，会自动旋转来维持平衡（见笔记，7加入时，5和6发生旋转）
用红黑树做索引结构时，树整体的高度太大，查找时，IO大

Hash表

select * from t1 where t.col1=6
hash(6)----一一映射----地址值，查找时，只用一次IO；但范围查找时不行 col1>6

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

• 无法用于排序与分组；
• 只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

B-Tree

B-Tree 指的是 Balance Tree，也就是平衡树

• 叶节点具有相同的深度
• 叶节点的指针为空
• 节点中的数据索引从左到右递增排序
  （B-Tree来源：想控制查找树的高度，就横向做文章，一个大节点上存储了很多索引小元素）
  但范围查找时，不行
  

B+Tree（B-Tree变种）

B+ Tree 是基于 B-Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。

• 非叶子节点不存储data，只存储索引，所以节点里可以放更多的索引
• 叶子节点不存储指针（存储数据项）
• 顺序访问指针，提高区间访问的性能

B+树相比B树的优势：

1. mysql对节点大小有限制：16k，单一节点里只存放索引就可以存放更多元素（指针小，数据较大），所以B+树矮胖，索引IO次数少
2. B+树 把 关键的处于 中间位置的 索引元素，做了冗余，提到了非叶子结点上：如15,20,49.
3. 由于叶子节点之间有（双向）指针，形成有序列表，可以进行范围查找
4. 所有的查询都要查找到叶子节点，查询性能是稳定的；而B树，每个节点都可以查找到数据，不稳定

B+树的非叶子节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”
B+树查询必须查找到叶子节点，B树只要匹配到即可不用管元素位置，因此B+树查找更稳定（也不慢）
对于范围查找来说，B+树只需遍历叶子节点链表即可，B树却需要重复地中序遍历

操作
  进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。
  插入删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

与红黑树的比较
  红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，主要有以下两个原因：
（一）更少的查找次数
  平衡树查找操作的时间复杂度和树高 h 相关，O(h)=O(log d N)，其中 d 为每个节点的出度。
  红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多，查找的次数也就更多。
（二）利用磁盘预读特性
  为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。
  操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。

MySQL 中的存储引擎

概念

1、概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中。
2、通过show engines；来查看mysql支持的存储引擎。
3、 在mysql中用的最多的存储引擎有：innodb（默认）， myisam ,memory 等。

比较

• 事务： InnoDB支持事务，而myisam、memory等不支持事务
• 并发：InnoDB支持行级锁，而MyISAM锁的粒度是表级的（表共享读锁，表独占写锁）
• 表的文件结构（InnoDB聚集，MyISAM非聚集，）
• 外键：InnoDB支持外键
• 备份：InnoDB 支持在线热备份
• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢

存储引擎是表级别的，每个表有单独的存储引擎

索引是在存储引擎层实现的，所以不同存储引擎具有不同的索引类型和实现。
Mysql的Myisam存储引擎和Innodb存储引擎的索引的数据结构都是B+树结构

为什么MyISAM会比Innodb的查询速度快

INNODB在做SELECT的时候,要维护的东西比MYISAM引擎多很多:
1)数据块：MYISAM只缓存索引块, 这中间还有换进换出的减少;
2)innodb寻址要映射到块,再到行，MYISAM记录的直接是文件的OFFSET,定位比INNODB要快
3)INNODB还需要维护MVCC一致;虽然你的场景没有,但他还是需要去检查和维护MVCC (Multi-Version Concurrency Control)多版本并发控制

MyISSAM索引文件和数据文件是分离的（非聚集）

.MYI文件：存放索引（索引列的索引值，以B+树这个数据结构来组织，存储在MYI文件）
.MYD文件：数据（这张表里的所有数据行）
.frm文件：表结构

检索col1=49
去MYI文件，通过B+树快速定位到49这个元素，该元素的data里存储0x90（49索引，即Col1=49，所在这一行数据的磁盘文件指针）
通过49索引 key对应的那个value：0x90，根据它快速从MYD文件里定位到这一行数据。

Innodb索引实现（聚集）

.IDB文件：index-data，数据和索引一起存储在xx.IDB文件
.frm：表结构

• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引- 叶节点包含了完整的数据记录
• Innodb表必须有主键，并且推荐使用整型的自增主键
• 非主键索引结构叶子节点存储的是主键值（一致性和节省存储空间）

1. 由于叶子节点之间存储了索引所在行 的 其他列的全部字段数据（col1=20那一行）
   找到col1=20后，就不需要再根据磁盘文件指针去另一个文件里查找该行数据，可以直接从value里取出（性能高）
2. Innodb表必须有主键，并且推荐使用整型的自增主键：
   解释：
    主键是唯一标识该列；
    如果使用UUID（通用唯一识别码Universally Unique Identifier）是一个长字符串，占用存储空间大，且查找时要比较大小，UUID需逐位比较，效率低；
    主键自增，当增加节点时，就可以往节点后面存储，用链表指针连接（如果不自增，增加中间数节点，就会导致节点分裂）
   

索引

• 索引是帮助Mysql高效获取数据的排好序的数据结构

索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。

• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储到磁盘上

平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉树）结构组织的索引，其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。
除了B+树这种类型的索引，还有哈希索引等

• Ｂ树和Ｂ＋树的出现是因为磁盘IO问题；IO操作的效率很低，当在大量数据存储中，查询时我们不能一下子将所有数据加载到内存中，只能逐一加载磁盘页，每个磁盘页对应树的节点，造成大量磁盘ＩＯ操作（最坏情况下为树的高度）。
  平衡二叉树由于树深度过大而造成磁盘IO读写过于频繁，进而导致效率低下。
• 所以，我们为了减少磁盘ＩＯ的次数，就你必须降低树的深度，将“瘦高”的树变得“矮胖”。一个基本的想法就是：
  　　（1）每个节点存储多个元素
  　　（2）摒弃二叉树结构，采用多叉树

这样就引出来了一个新的查找树结构 ——多路查找树。 根据AVL给我们的启发，一颗平衡多路查找树(B~树)自然可以使得数据的查找效率保证在O(logN)这样的对数级别上。

检索原理

• 三阶B树（实际中节点中元素很多）
  

1. 初始化介绍
   一棵b+树，浅蓝色的块称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1，P2，P3
   P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块
   叶子节点存储真实的数据，即3，5，9，10，13,15,28,29,36,60,75,79,90,99
   非叶子节点只存储指引索引方向的数据项（指针），如17,37，它并不真实存在于数据项中
2. 查找过程
   (1）. 如果要查找数据项29，先把磁盘1由磁盘加载到内存，此时发生一次磁盘IO
   （在内存中用二分查找确定29在17和35之间，于是锁定磁盘1的P2指针，内存时间因为非常短（相比磁盘IO）可以忽略不计）
   (2）. 通过磁盘1的P2指针的磁盘地址 把 磁盘块3由磁盘加载到内存，发生第二次IO
   (3）. 29在26和30之间，锁定磁盘3的P2指针，通过指针加载磁盘8到内存，发生第三次IO
   （同时内存中做二分查找 找到29，结束查询）总计3次IO.

真实的情况是，3层的b+树可以表示上百万的数据，加载上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，成本非常非常高

索引优化

理解联合索引的存储结构，搞定索引优化原则

1. 独立的列
   在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。
   例如下面的查询不能使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

2. 多列索引
   在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。
   例如下面的语句中，最好把actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor 
WHERE actor_id = 1 AND film_id = 1;

3. 索引列的顺序
   让选择性最强的索引列放在前面。

索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;

选择性：
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049

4. 前缀索引
   对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。
   前缀长度的选取需要根据索引选择性来确定。

5. 覆盖索引
   索引包含所有需要查询的字段的值。
   具有以下优点：
    （1）索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
    （2）一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
    （3）对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

索引的优点

• 大大减少了服务器需要扫描的数据行数。
• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和
  GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

索引的缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度；

• 空间方面：索引需要占物理空间。

索引的使用条件

• 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；
• 对于中到大型的表，索引就非常有效；
• 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

什么情况下设置了索引但无法使用？

• 以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配；

• OR语句前后没有同时使用索引；

• 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；

• 对于多列索引，必须满足 最左匹配原则 (eg：多列索引col1、col2和col3，则 索引生效的情形包括 col1或col1，col2或col1，col2，col3)。

什么样的字段适合创建索引？

• 经常作查询选择的字段

• 经常作表连接的字段

• 经常出现在order by, group by, distinct 后面的字段

创建索引时需要注意什么？

• 非空字段 ：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；

• 取值离散大的字段 ：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；

• 索引字段越小越好 ：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

索引的分类

• 普通索引和唯一性索引：索引列的值的唯一性

• 单个索引和复合索引：索引列所包含的列数

• 聚簇索引与非聚簇索引：聚簇索引按照数据的物理存储进行划分的。对于一堆记录来说，使用聚集索引就是对这堆记录进行堆划分，即主要描述的是物理上的存储。正是因为这种划分方法，导致聚簇索引必须是唯一的。聚集索引可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了；而非聚集索引是把一个很大的范围，转换成一个小的地图，然后你需要在这个小地图中找你要寻找的信息的位置，最后通过这个位置，再去找你所需要的记录。

主键、自增主键、主键索引与唯一索引概念区别

主键：指字段 唯一、非空 的列；

主键索引：指的就是主键，主键是索引的一种，是唯一索引的特殊类型。创建主键的时候，数据库默认会为主键创建一个唯一索引；

自增主键：字段类型为数字、自增、并且是主键；

唯一索引：索引列的值必须唯一，但允许有空值。主键是唯一索引，这样说没错；但反过来说，唯一索引也是主键就错误了，因为唯一索引允许空值，主键不允许有空值，所以不能说唯一索引也是主键 。

主键就是聚集索引吗？主键和索引有什么区别？

主键是一种特殊的唯一性索引，其可以是聚集索引，也可以是非聚集索引 。在SQLServer中，主键的创建必须依赖于索引，默认创建的是聚集索引，但也可以显式指定为非聚集索引。InnoDB作为MySQL存储引擎时，默认按照主键进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。所以，对于聚集索引来说，你创建主键的时候，自动就创建了主键的聚集索引。