谈一谈mysql innoDB索引与优化

理解索引

索引就是书签，有了索引可以对内容进行快速定位，实现加速读。
如果把一张表看作一个内容线性表，没有索引的表可能就是一个普通的链表结构，我们只能从头读到尾，而具备索引结构，它就可以看作一个跳表结构，可以支持部分随机读取的行为。
具体的谈论索引，我们必须具体到某一种实现。

索引具有以下优点：
【1】提高查询速度、表连接速度
【2】避免额外的内存或临时文件排序，减少排序和分组的时间
【3】唯一索引保证每一行数据的唯一性

维护索引的数据结构，也需要付出以下代价：
【1】维护、创建、页合并、页分裂等操作耗时
【2】创建索引时，需要对表进行加锁，可能会其他事务的影响正常操作（锁其实就是基于索引实现的）
【3】索引缓存和各种索引文件需要额外占用内存和磁盘资源（需要有额外的文件和内存去存放索引）
【4】修改数据时会触发索引的维护，降低性能

索引的几种实现

索引本质上就是一种数据结构，它支持快速查找

【1】有序数组
查询效率很高，因为有序数组结构适合通过二分查找算法，但是插入删除可能会造成整体拷贝，因此更加适合静态存储引擎或者只读的数据类型存储。例如：历史记录，用户可以通过日期快速从有序数组中找到历史记录。
【2】哈希表
哈希表的效率一定程度上依赖哈希函数（桶映射函数）和解决哈希冲突的方案。
输入待查找的值作为key，通过对key进行哈希计算得到一个桶的索引，一般通过链表法解决哈希冲突，因此查询效率高，增删效率也高。
但是由于元素在桶内的分布是无序的，因此做区间查询的时候很慢，仅适合做等值查询的场景，如nosql的redis数据库。
哈希表实现索引的限制：
a.只能包含哈希值和行指针，无法存储字段值或数据行，因此无法避免回表
b.不支持部分索引匹配查找，因为hash索引始终使用索引列的全部内容计算hash值的，不能更好的利用联合索引

如果索引使用哈希表实现，则一个索引对应一颗哈希表。默认会对主键建立一个索引（哈希表）。假如对name字段建立索引，则磁盘上至少存在两个索引文件。Where name = ‘123’，首先查询name索引文件，通过hash(‘123’)得到键值对的存放位置，得到‘123’-主键id，然后再查询一次主键索引哈希表得到最终的记录。

c.hash索引不是按照索引值顺序存储的，天生无序。
d.仅支持等值查询
e.存在哈希冲突问题

innoDB会在系统自动生成自适应索引，由于哈希表是离散存储的，因此无法排序，也不支持最左前缀索引，而且存储哈希冲突的问题，不支持范围查询。

【3】平衡二叉树/红黑树

搜索二叉树、平衡二叉树和红黑树都是二叉树，其中平衡二叉树解决了搜索二叉树“在一定情况下会退化成单向链表”的问题，而平衡二叉树的约束过多且实现复杂，红黑树可以看作平衡二叉树的一种“弱平衡性”的一种实现方案。

平衡二叉树的查询速度是log2(N)，每次查找都是一次二分，但是每一层分叉过少的代价就是树的高度过高，每一层查找都是一次磁盘I/O，平衡二叉树的查询成本随着树高升高而增加，因此平衡二叉树不适合作为数据库的索引结构。

每次查找都会将索引页面读入内存，然后确定下一个需要读入的索引页面，因为页面本身也是有序的，因此在内存中的查找是很快的（可以基于二分查找算法），而这时的瓶颈就在于它是二叉还是二十叉了，而是在于I/O导致的时间开销，因为每读入一次节点都至少需要一次I/O，而一层中不管几个节点都是对应一次I/O，树高过高导致I/O次数上升才是正在的瓶颈，因此二叉树更多用于内存结果对象的查找，比如在内存中维护一个treeMap作为作为内存索引结构。

【4】B树/B+树（多叉平衡树）
B树是专门为磁盘设备设计的平衡查找树，它相对于二叉平衡树有了更适合磁盘I/O的特点，如：树的节点可以存放多个元素，这样树的高度就得到了压缩，查询某个节点可以进行更少的I/O次数。B树的节点单位通常与磁盘的存储单位是对应的，数据库设计者通常利用磁盘预读原理，将一个节点的大小设计等于一个页的大小，这样每个节点只需要一次I/O便可以完全载入，因此每次读入内存的其实是一个数据块或索引块。
另一方面，B数节点中的元素数据行都是有序的，这就降低了排序成本，将随机I/O转换为了顺序I/O，避免为排序分配临时内存或临时中间文件。

B树和B+树

一颗M叉的B树是一颗平衡的M路搜索树，所有叶子节点都在同一层，每个节点中即存放key值又存放指针。
而B+树是B树的变种，B+树的叶子节点用于保存key值的信息，而所有的非叶子节点都可以看作是搜索目标叶子节点的索引部分。
B+树的所有叶子节点都新增了链表指针，这使得所有数据在B+数叶子节点中按照key排序。

B树通过压缩高度虽然解决了“磁盘IO次数较多的问题”，但是未能解决遍历查找效率低下的问题

B+树索引每次加载出的都是一个页，页中包含多个数据行。根据索引字段可以定位数据行所在的数据页，将数据页加载进入内存。在内存值通过对索引的key值进行二分查找可以取出对应数据行。
B+树非叶子节点可以看作索引节点，不存储数据行，所有数据行存在于叶子节点。这使得B+树搜索可以更加稳定（因此连接查询时总是将小表做主表），每个叶子节点可以存储的元素更多，查询所需的IO次数也会更少，一个数据页可以包含更多的数据行。

B+树更适合进行范围查找，因为遍历一个范围的元素，只需要遍历叶子节点。而B树的不同页面之间不连续，如果要进行范围扫描，B+树以链表的形式扫描聚簇索引叶子节点进行优化，而B树需要同时在多个节点之间切换，因为B树节点内部的数据行虽然是有序的，但是从节点之间看，则是不连续的。

而B树更加适合等值查找，因为B树的key不像B+树全部存储在叶子节点，因此如果对B树查询某个key，很可能在某个距离根很近的位置就能找到，而最坏的情况才是在根结点找到。另一方，B树不适合大范围的搜索，如果仅仅是小范围的搜索并不影响性能，如果需要大范围遍历，顺序I/O将退化为随机I/O（不断回旋查找），从而导致I/O次数上升，性能下降。B+树含有数据行的节点都是根结点，有序且相连，直接对叶子节点遍历即可，查找较稳定，遍历元素效率高

B+树的非叶子节点会冗余一份在叶子节点中（所有的非叶子节点key都保存一份key/value到叶子节点），并且叶子节点之间使用指针相连。B树一个叶子节点可以存储多个元素，相当于完全平衡二叉树整体的树高降低了，磁盘IO效率提升了。而B+树只是通过叶子节点冗余非叶子节点，提升了范围查找的效率，B+树同时也具有B树的一切优点。

树的路数也不是无限延伸的， 如果树的路数无限延伸则会退化成一个有序数组，如果数据量过大无法一次性载入内存，会进行多次IO，效率会下降。因为树高下降，索引的粒度（叉数）就会变大，节点装入的内容变多，会多于一页，这样读入一个页面可能需要至少两次I/O

总结：
B+数的单一节点存放更多数据，IO效率高。由于每次都是从叶子节点载入页面，查找更稳定。天然有序，便于范围查询和排序。
B树比较适合单值查询，因为如果所在页靠近根结点，那么可以很快锁定目标页。

mysql innoDB的索引

mysql具有许多需要遍历元素的场景：范围查询、全表查询、关联子查询等，因此B+树是更合适的，而B树更适合于单条记录的查询。innoDB使用B+树索引模型，所以数据都是存储在B+树中的，每一个索引在innoDB中对应一颗B+树。索引文件和数据文件存放在表空间中，而且mysql为每一个索引分配两个段去存放叶子节点和非叶子节点。

索引创建

索引可以分为三类：
【1】单值索引：一个索引只包含单个列，一个表可以有多个单列索引