为什么会有需要索引呢?

索引在现实世界中，就是书的目录一样，我们可以根据目录找到书的具体某一页，这样可以降低我们查找的时间。目录需要占用一定的页，一般情况目录的页和内容页相比而言只占一小部分。但是这部分页也是需要占用空间的，所以索引本身是一个空间换时间的数据结构。我们需要索引的原因是，我们可以牺牲一定的空间换取查询数据的速度。在数据库中如何选择一个合适的索引数据结构呢? 索引在计算机中也是一种数据结构，数据结构是由链表或者数组这两个基础结构，根据不同的场景结合而来的。

那对于数据库这种场景，索引应该使用那种数据结构组织数据呢？

我们思考使用什么数据结构，首先要明确主要应用的场景和特征，所以我们先来回顾一下我们经常使用的数据库操作:

select cols form table where col = ?;
select cols from table where col = ? order by col; 我们一般使用数据库查询分为两种，等值查询和根据某列进行排序，两种使用方式在数据库的过程中经常见到，基于这两种场景下，我们应该选择什么数据结构？
hash表 hash表的特点就是快，使用数组作为基础，有O(1)的查询、增删的时间复杂度(排除hash冲突较高的前提下), 但是hash表有一个很大的缺点不适合我们的存储数据。因为hash表是无序的，我们如果想按照给定字段进行排序，那么至少需要O(n)的时间复杂度。索引hash表这种数据结构，并没有大范围在数据库中作为索引组织数据的结构，而一般是基于某些场景使用，例如innodb中有自适应hash索引，在某些行等值查询超过指定次数后，会进行自动添加hash索引。

那是否有等值查询和排序查询时间复杂度相差不大的数据结构呢？当然有，答案就是以链表作为组织数据接触的树结构，树这种结构有很多不同的实现，如二叉搜索树、AVL树、红黑树等。红黑树的查询、添加删除等操作的时间复杂度平均都在O(logN),虽然实现起来很复杂，但是依旧广泛应用在不同的领域，例如Java的TreeSet就是通过红黑树实现的。

数据库是否使用了二叉搜索树、红黑树作为组织数据的数据结构呢？并没有，因为根据二叉树和红黑树的特性，会导致数据的深度特别的高，mysql这种数据存储在文件的数据库，如果深度太高，会导致查询请求io的次数太多，效率太差，二者更适合于在内存中组织数据。

那数据库使用什么数据结构作为存储数据的索引呢？答案是B+Tree,为什么选择B+Tree，B+Tree有什么特点，就是我们本文主要讲解的内容。

为什么是B+Tree 根据上文描述，我们要选择的数据结构一定要有这几个特征:

等值、排序查询时间复杂度不能太高
树的深度不能太高

根据上面的场景，B-Tree索引应任而生，B-Tree索引是一个N叉树，叉越多，树的结构越矮胖，这样虽然会让单节点的体积变大，但是可以降低深度，从而减少IO请求次数。节点存储的数据更多，节点以页为单位存储，更符合局部性原理。

一个M叉树，一个页存储节点个数符合2/M <= N <= M，举个例子:假设主键使用bigint类型，那么每个主键占用的字节是8byte，假设页的大小是4kb，那么一个节点可以存储500条数据，m大约为1000，那么三层就可以存储500 * 1000 * 1000 = 5亿个key

在B-Tree的基础上，Mysql又引入了B+Tree.B+Tree是B-Tree的升级，主要升级的点是B+Tree的非叶子节点只存储索引不存储数据，同时B+Tree的叶子节点增加了链表关系。

非叶子节点只存储索引不存储数据，这样可以保证所有数据都在一行，范围查询不需要再访问父节点从而减少请求其他页的次数
同时数据都在子节点，更适合范围查询，因为B+Tree本身是有序的，所以直接遍历遍历链表就可以实现。
叶子节点如果放数据，那么单位一个页面存储的索引数量会更小

什么是B+Tree的页分裂，为什么会产生页分裂，如何避免页分裂

在B+Tree中，页的大小是固定也就是说，

B+Tree按照页进行分配数据，为了维持数据的有效性，所以必须进行分裂。顺序存储可以减少页分裂，从而减少磁盘io次数.

因为顺序插入，只有节点满了以后才会进行分裂。无序的话，多种情况都会进行分页。

聚集索引和辅助索引

聚集索引

数据和索引一起存放

InnoDB存储引擎是索引组织表,即表中数据按照主键存放,由每张表的主键构成一颗B+树,同时叶子节点存放整张表的行记录数据,聚集索引的叶子节点也成为数据页. (数据 + 索引)
数据页只能是一颗B+树进行排序,因此每张表只拥有一个聚集索引
记录的索引顺序与数据逻辑物理顺序相同

辅助索引

除了聚集索引其他索引都是辅助索引,与聚集索引的区别是:辅助索引的叶子节点不包含行记录的全部数据
叶子结点不直接指向数据页
每张表可以有多个非聚集索引，需要更多磁盘和内容,多个索引会影响insert和update的速度

联合索引和覆盖索引

联合索引:对表的多个列进行索引(idx_col1_col2)

为什么mysql要设计一个最左匹配原则？

核心是因为联合索引，会将联合最左侧的字段当做维护索引顺序的标记， (a,b)两个索引因为是按照a的字段进行排序，所以如果单独使用b，索引本身是无序的，没办法按序查找，只能扫描。

col1,col2 idx_col1值相同的情况下，col2是有序的.

所以使用联合索引可以避免内存排序, 在explain中也就是Using fileSort 覆盖索引可以减少回表，也就是索引中包含了需要查询的字段内容.

explain中 extra中如果描述使用using index代表使用覆盖索引

Cardinality

重复度越低越适合使用索引，索引本身是有维护成本的，例如新增一行数据，同时要维护多个索引数据，降低插入的效率。所以不能无脑加，最好加在一些重复度低的字段上，如id、name这种

SHOW INDEX FROM TABLE 用来显示结果列中不重复记录数量的预估值(不是准确值)

Cardinality并不是在每次插入和修改的时候都进行更新，db使用Sample进行更新:

更新策略是表中1/16的数据发生变化 Sample:随机抽取多个叶子节点，统计每个页不同的个数 ANALYZE TABLE and SHOW TABLE STATUS and SHOW INDEX 会更新Cardinality

什么情况会让索引失效

字段加函数，如sum()
使用join，连接的表id之间字符集不同(实际上默认是用字符转换函数)
取反操作，如!= not in not exists这种都不发命中索引

Innodb学习之索引