对数据库索引的一些理解

索引

什么是索引?
索引是一种提高查询速度的数据结构
提高查询速度的原理：对数据排序，排序之后二分查找，优于乱序全量查找
当然也会付出修改数据开销更大的代价，
索引需要占内存，内存开销也更大了。
InnoDB的索引数据结构采用B+树，因为B+树每层可以存储大量的数据，一般树高不会超过4层，最多只需要四次磁盘IO即可找到数据，性能十分高效。

索引提高查询速度的原理

• 无索引时，数据是乱序的，所以需要遍历所有数据直到找到结果
• 有索引时，会事先对数据排序，查找时从B+树的根节点开始，一层一层向下搜索。每层采用二分查找算法找到目标数据所在的目录项，然后进入相应的下一层节点，直到最终在叶子节点中找到目标数据，整个查找过程减少了遍历的数据量，从而提高查询速度。

索引这么多优点，是不是建的越多越好？
不是，虽然可以减少查询时间，但是增大了增删改的时间开销和内存占用
甚至有些情况下会降低查询效率，比如同时有很多个索引都可以用于查询，就会增加 MySQL确定使用那种索引进行查询的时间，降低查询性能。

索引特点

索引的优点：提高数据查询效率
缺点：增大修改数据的开销和内存占用

索引并不是越多越好，只为频繁查询、排序的列创建索引
频繁更新的字段，基数小的字段(性别)不适合建索引

建索引时的原则：
索引列的类型尽量小，尤其是主键的类型越小越好，能用TINYINT就不用INT。

InnoDB索引的底层数据结构选择B+Tree，因为B+树每层可以存储大量的数据，一般树高不会超过4层，最多只需要四次磁盘IO即可找到数据，性能十分高效。

索引类型：主键索引、二级索引

关于删除索引，删除主键索引，会同时造成普通索引失效，损失巨大。删除普通索引貌似影响不大，不过要注意在业务低谷期操作，避免影响业务。

联合索引中，注意要满足最左匹配原则、

字符字段建前缀索引

索引失效

以下情况会导致索引失效：
where中使用null、not、or、函数等都会导致索引失效
使用in会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描。
前导模糊查询
联合索引中不按顺序使用索引
函数操作
表达式操作(加减乘除比较大小等)
存在不同数据类型之间的比较比如1=‘1’
索引列之间对比
索引失效详解

索引的分类

按照数据的存储方式分类

主键索引：叶子结点存储用户的完整数据

二级索引：叶子结点只存储被索引列+主键

按照功能逻辑的分类

普通索引(Index) ：
唯一目的：快速查询数据
允许创建多个普通索引，
允许数据重复和 NULL。

唯一索引(Unique Key) ：
在普通索引的基础上不能出现重复的数据
但是允许数据为 NULL，
允许创建多个唯一索引。
目的：大部分时候都是为了该属性列的数据的唯一性

主键索引：
主键索引则是在唯一索引上添加了非NULL的约束，所以数据唯一非null
一张表只能有一个主键索引
存储完整数据

前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小。

按照字段个数划分

单一索引：为单独一个列来建立索引

联合索引：为多个列联合起来建立索引，本质上也是二级索引。需要符合最左匹配原则。

主键索引 vs 二级索引

主键索引和二级索引是两种不同的数据存储方式。

主键索引的优点：保存完整的数据一次查询即可无需回表
缺点：
1、存储完整的数据浪费空间
2、更新代价大，主键数据被修改时，对应的二级索引也将会被修改

二级索引优点：占用空间更小，更新代价更小 。
缺点是需要回表

即使二级索引不存完整的用户信息，也可以存数据的地址而不是主键，这样可以直接定位数据，也无需回表啊，为什么不？
MyISAM确实就是这样做的，但是有利有弊。
因为数据存放的地址是经常发生变化的，存地址会不断的修改性能并不高，而主键不会修改。

B+树

其实每建立一个索引，本质就是在建立一颗b+树

为什么选择B+？

主要有以下几个优点：

1、B+树本质是N叉树，这里的N可以非常大，所以一层可以存储大量的数据，所以树很矮，只需要很少次数的磁盘IO就可以拿到数据，性能十分高效。

2、B+树是所有的叶子节点存储数据，叶子节点之间有指针相连，所以支持范围查询，因为只需要找到起始节点，然后基于叶子节点的链表结构往下读取即可

3、通过唯一索引约束，可以保证数据表中每一行数据的唯一性

B+树相较B树的优点：

1、B+树的层级更少所以查询次数更少：
B+的每个非叶子节点只存目录项，所以可以存储的目录项更多，所需的内节点也就更少，树的层级更少，所以查询磁盘的次数更少，查询速度更快；

2、B+树查询速度更稳定：
B+所有数据都存在叶子节点上，所以每次查找的次数都相同所以更稳定;

3、B+树天然具备排序功能
B+树所有的叶子节点数据构成了一个有序链表，在范围查询数据时更方便，数据紧密性很高，缓存的命中率也会比B树高

为什么不选Hash索引？
1、不支持范围查询
2、Hash 冲突问题
3、能存储的数据量不如B+树索引

B+树索引原理

原文
mysql中数据是以页为单位存储的
各个数据页之间组成一个双向链表
每个数据页内部的记录之间又组成一个单向链表
每个数据页都会为里面的记录生成一个页目录
就像这样：

由目录项记录来标记用户记录的区间范围。即用一个目录页将上面四个页面记录下来；如下：

如果我们表中的数据太多了，一个目录项存不下，所以采用多级目录：

N叉树的N怎么计算？

N叉树中非叶子节点存放的是索引，索引包含Key和Point指针。Point指针固定为6个字节，假如Key为10个字节，那么单个索引就是16个字节。如果B+树中页大小为16K，那么一个页就可以存储1024个索引，此时N就等于1024。

联合索引&最左匹配原则&覆盖索引&索引下推

什么是联合索引？
同时为多个列建立索引，以多个列的大小作为排序规则，比如一个联合索引(a,b,c)，先按a排序，再按b排序，再按c排序的，联合索引本质上还是一个二级索引。

什么是最左匹配原则？
在联合索引中，必须按照最左优先的顺序进行匹配查询，不能跳过索引键，且前面的索引建不能范围查询，否则后面的索引建失效。

为什么要遵守最左匹配原则？为什么前面的索引键不能范围查询？

因为 联合索引中，后面的索引列的有序性是基于前面索引列的有序性的。
前面的索引范围查询，就没有按索引顺序查找，前面无序后面跟着无序。

建联合索引时，索引键的顺序安排的建议如下：
区分度最高的建在索引最左侧
使用最为频繁的列建在最左侧
长度小的字段放在最左侧

覆盖索引
当联合索引恰好包含了所有要查询的字段时，就成了覆盖索引，通过这个覆盖索引可以得到所有想要的数据，所以无需回表，所以覆盖索引可以减少回表次数，提升查询性能。

当然，索引字段的维护是有代价的，索引的字段越多，节点占用的空间也就越大。此间利弊也是需要权衡的。

什么是索引下推？
没有索引下推时，通过主键索引回表拿到全部数据之后，再用过滤条件筛选数据，
有索引下推后，把用过滤条件筛选数据提前。在查询出整行数据之前过滤掉无效的数据。只有有效数据才会回表，减少了回表的次数。

mysql为什么会选错索引？

mysql可能不走索引：
比如mysql判断是否走 c 字段索引时，系统会预测走 c 字段索引大概需要扫描多少行。如果预测到要扫描的行数很多，它可能就不走索引而直接扫描全表了。

系统是怎么预测的呢？
系统是通过索引的区分度来判断的，区分度越高，基数越大，
一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着利用索引查询的次数越少。
所以，一个索引的基数越大，意味着走索引查询越有优势。
即基数越大越好，性别由于基数太小就不适合建索引。

msyql怎么知道这个索引的基数呢？
系统当然是不会遍历全部行来获得一个索引的基数的，代价太大了，索引系统是通过遍历部分行，通过采样的方式，来预测索引的基数的。

重点来了，因为是采样，所以必然存在误差，比如c 这个索引的基数实际上是很大的，但是采样的时候，却很不幸，把这个索引的基数预测成很小。然后就误以为索引的基数很小。所以系统就不走 c 索引了，直接走全部扫描了。于是就选错了索引。

结论：由于统计的失误，导致系统没有走索引，而是走了全表扫描，选错索引导致我们 SQL 语句执行的很慢

选错索引该怎么办？
1、考虑修改语句，引导 MySQL 使用我们期望的索引。
2、删掉误用的索引

自己的一点思考

B+树怎么存储相同的索引值？

• 如果有唯一性约束会报错
• 对于没有唯一性约束的索性，当索引值相同的时候，B+树非叶子节点根据主键id来唯一标识一个索引记录。

普通索引和唯一索引该如何选择？
查询过程二者性能差不多
更新过程，由于唯一索引需要额外判断索引数据是否唯一，所以效率比普通索引低，所以推荐使用普通索引

当table T出现以下数据时: (300, 3) (400, 3)时，通个非主键索引3查到对应ID有两个，分别是300和400，那么这里是回表一次还是两次？
答案是两次，因为回表的依据是非主键索引，和主键id无关，有多少个非主键索引数据就回表多少次

这个b+树的根节点是固定的还是动态变化的?
动态变化，随着不断的插入数据会进行页分裂等操作，根节点会随之进行相应的调整、

索引表在innodb中是怎么存储的？
InnoDB只需记录每棵树的根节点的位置即可。

如果我新建一张表，不加任何约束，也就是没有主键。当插入一定量的数据库后，我再给一个字段加上主键，那么这个主键会包含某一行中的所有的数据吗？
加主键就会重建整张表，所以会包含完整的数据。

between 和 in 的比较

1.select * from T where k in(1,2,3,4,5) 
2.select * from T where k between 1 and 5

第一个要树搜索5次
第二个搜索一次

mysql的联合索引是怎么储存的？
联合索引是依次按照联合字段的先后顺序，依次进行排序。如a,b,c三个字段是联合索引，则叶子节点存储的是三个字段的数据，且按照先后顺序进行排序；