tips: 内容颇长，排版不是很好
觉得值得一看的同志，建议有空接着看
感谢咕泡学院 青山老师扫盲
-
版本
目前企业级开发常用的版本：5.5, 5.6, 5.7
5.5发布与2010,从这个版本开始Innodb成为mysql默认的存储引擎,具有提交/回滚，兼容ACID,行级锁 等特性
5.6发布于2012,开始支持全文索引，并对explain语句优化(支持insert update delete语句)
5.7发布于2015,对性能和易用性进行优化
目前mysql官方主推MySql8.0,官方表示8.0版本比5.7快2备,并且进行大量改进和性能优化
-
InnoDB索引原理
数据库索引是什么？ 数据库索引是数据库管理系统中的一个排序的数据结构,以协助快速查询 更新数据库表中的数据
索引存储索引字段 并 指向业务数据在磁盘的位置
====================Innodb索引存储结构 = 始 ==================
猜想：在没有索引的情况下去数据表查询数据需要一行行扫描数据表
那么索引底层采用了一种什么存储结构来实现快速查询的呢？
===> 二叉查找树（Binary Search Tree）适合么？
1.二叉查找树在链表的基础上应用了二分查找算法
2.二叉查找树要求左子树的节点要小于父节点，右子树的节点要大于父节点
3.二叉查找树插入和查询的数据相较于一行行便利都比较快，
但是有一个问题查找的效率与深度相关，
在极端情况下(斜树，只有左节点或者右节点)复杂度会退化为o(n)，如图
结论：二叉查找树存在严重缺陷，不适合作为索引的存储结构
那么有没有一种左右子树深度相差不是那么大，更加平衡的一种树呢？
===> 平衡二叉树（AVL Tree，Balanced Binary Search Trees）
1.平衡二叉树在二叉查找树的基础上 约束左右子树的深度绝对值不能超过1 2.通过左旋或者右旋达到平衡的目的
假如用AVL平衡二叉树来存储索引，则每个树节点存储索引的一个行，如图（右子树有错误，忽略）
索引的数据也是存储在磁盘中的，如果要基于索引去查找业务数据，必须要将索引先加载进内存，然后在内存中进行查找，进行数据的比较。
在操作系统层面，从 磁盘加载数据到内存 遵循局部性原理
局部性原理是指CPU访问存储器时，无论是存取指令还是存取数据，
所访问的存储单元都趋于聚集在一个较小的连续区域中。
由于从 磁盘加载数据到内存 非常耗时，为了尽量减少I/O操作，操作系统一般采取预读的方式，**预读的长度一般为页（page，4kb；在Innodb存储引擎中的页为16kb）**的整倍数。
上图中，每次加载AVL树一个节点中的索引数据到内存都是一次IO，但是 键值+数据磁盘地址+子节点引用 基本不可能有16kb,白白浪费空间。而且，在机械硬盘时代，每次磁盘IO都会有10ms左右的寻址时间，访问的AVL树节点越多消耗的时间也随之增加，如果是几百万的数据其效率将是不可预见的低。
结论：如果用AVL平衡二叉树来存储索引效率还是不够好，IO次数随着树的深度增加而增加，而且树节点还浪费空间。
那么针对AVL平衡二叉树存在的问题,有没有一种树，它的深度没有那么深，每个树节点能多存储一些索引数据呢？
===>多路平衡查找树(Balanced Tree,B Tree,注意没有B减树)
1.B树节点存储的内容与AVL树一样,但B树的一个节点存储的是多个[键值+数据磁盘地址+子节点引用]
2.B树有一个特点: 节点拥有的子树的数量称之为度(Degree),假设每个节点中有N个键值,则 度= N + 1
3.AVL通过左旋/右旋保证平衡,B树则通过分裂/合并保持平衡
4.同等数据量的情况下相较于AVL树,B树的深度远不及AVL树,B数的节点存储索引数据更多
到目前来看假如把B数作为索引的存储结构已经比较需求了，树的深度不是特别大意味着IO次数减少，每个节点也不像AVL树那样只存一个索引数据浪费空间，可能只需要与磁盘交互三两次就找到了要找的数据了。那么MySql里面是不是就是用的B数作为索引存储结构呢？
实际上在Mysql里面，包括很多文件系统和其他数据库系统(oracle,sqlserver等)用的是B树的进化版本 : B+Tree
===>加强版多路平衡查找树(B+Tree)
1.B+树的度(子树数量)相等于节点存储的键值数量
2.B+树一个节点就是Innodb引擎中的一个页=16K
2.B+树的只有叶子节点存储数据，非叶子节点不存储数据(只存键值和子节点引用(子树的指针),没有业务数据的
磁盘地址);这意味着所有的数据查找都要发生同等次数的IO，到达叶子节点才能获取到;同时非叶子节点可以存放
更多索引数据([键值 + 子节点引用] )3.B+树叶子节点增加了相邻叶子节点的指针，形成了一个有序链表的结构,提升全表顺序扫描或者范围查询效率(比
如范围查询，不用每次都从根节点往下检索,通过叶子节点组成的有序链表即可完成)
B+Tree与B Tree相比：
- B Tree能解决的问题，B+Tree都能解决
- B+Tree扫库 扫表能力更强
- B+Tree磁盘读写能力更强
- B+Tree排序能力更强
- B+Tree效率更加稳定
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
假设1条记录的大小为1kb,主键使用bigint为8bytes
在Innodb里面指针(子树的指针)有6bytes，则每个索引单元:[键值+指针 = 14bytes]
以Innodb存储引擎中的页大小16kb为多路平衡查找树每个节点的大小，则非叶子节点可以存储索引单元(节点键值数量)=（16 * 1024）/ 14 = 16384 / 14 = 1170个，
度(子树数量)=节点键值数量 = 1170
，假如树的深度为2，就可以完成2000w数据的检索，如图：
Innodb数据的存放
1 .frm文件存放表的结构定义
2 .ibd存放数据与索引(在Innodb中 索引就是数据，数据就是索引)，
所有的数据存在与主键索引B+树的叶子节点，以主键为索引来组织数据的存储
B+树在Innodb中叶子节点会存储业务数据，而在MyIsam中则还是保存的业务数据的地址
聚集索引(聚簇索引)
1.索引键值逻辑顺序与完整数据的物理存放顺序一致，这种索引就是聚簇索引
2.在Innodb中主键索引就是聚集索引，其他都是非聚集索引
辅助索引(二级索引)
1.辅助索引的叶子节点存储的是主键索引的键值
2.当使用辅助索引查询数据时,
最终还是需要通过其叶子节点中的主键索引键值到主键索引中检索数据(回表)
列的离散度
离散度公式： count(distinct(column_name)):count(*)
离散度低(重复至太多的列，比如性别之类的 字段)的字段不需要去创建索引，即使创建了**优化器(Optimizer)**可能会判定不使用索引检索
联合索引-最左匹配原则
alter tabel t add INDEX `comidx_a_b_c` (a,b,c);
where a=?,
where a=? and b=?
where a=? and b=? and c=?
where c=? and a=? and b=?（acb,cba,优化器(Optimizer)对sql进行优化）
以上几种情况都是可以使用到联合索引的
带头大哥不能死，中间兄弟不能断
覆盖索引
查询的列包含在使用的索引中时直接从索引中获取，不用回表，提高了效率，这就是覆盖索引
使用explain语句查询sql语句执行计划时,如果explain语句执行结果中的extra字段出现“Using Index”就是sql语句发生了覆盖索引的现象
索引基本使用注意事项
索引列长度不宜过长
表索引不是越多越好，索引太多对更新表(新增，删除，更新)性能会有影响(多个索引树频繁分裂合并)
模糊匹配 like 条件前面带%，索引可能失效(Index Condition Pushdown,简称ICP)
在索引列使用函数 表达式 计算 ，索引可能失效
字符串不加引号，出现隐式转换，索引可能失效
负向查询: NOT Like, !=, <>, NOT IN, 索引可能失效
尽量指明查询的字段,便面使用select *
索引最终是否被使用到最终是由 MySql的优化器(Optimizer) 决定的，
实际使用中建议结合Explain语句查看业务sql的查询计划来进行检查和优化
=================== Innodb****索引存储结构 = 完 ==================
