Mysql索引底层数据结构与Explain详解

Mysql索引底层数据结构与Explain详解

一、数据结构：

1、MySQL使用的B+tree

1.1、B+tree在B-tree的基础上进行了改造。

1.1.1、B+tree的数据都在叶子节点(磁盘叶)上，B-tree的数据在各自的节点上；
1.1.2、B+tree的非叶子节点存储的叶子结点的冗余索引，B-tree的非叶子节点存储了数据和索引；
1.1.3、B+tree 叶子结点之间有双向指针；B-tree没有；
1.1.4、B+tree:

一个非叶子节点是16Kb, 一个索引占8byte,子节点的信息占6byte,所以一个节点有1170个左右的数据；一个叶子节点一行记录大概1kb，所以一颗B+tree撑满的时候是1170117016=2000W数据。

1.2为什么选B+tree还是B-tree？

因为B-tree 一个节点只能存16个左右的数据，那么当要和B+tree一样的效果时。他需要的树高是6左右 ==== 2000W/16/16/16/16/16/16

2、面试点

2.1、存储引擎是形容数据库表的
2.2、聚集索引就是innoDB这种叶子节点上有完整的数据
2.3、innoDB只有一个聚集索引，其他索引是辅助索引，辅助索引的叶子结点放的是聚集索引的主键ID

3、最左前缀原理

3.1、联合索引

按照组合字段顺序排序，轮流比对，然后回表查主键

二、Explain详解(实际用到哪个索引，要看是走辅助索引快还是走主键索引快，当二级索引包含所有信息时，走二级索引)

1.1、在下方加show warnings会展示sql在mysql优化后执行的实际语句
1.2、explian解析后对应表示含义
1.2.1、simple_type 表示对应行是简单还是复杂的查询。
simple 简单查询
primary 复杂查询中最外层select
subquery 包含在select中的子查询
derived 包含在from子句中的子查询
union 关联
1.2.2、table 索引的表
1.2.3、partitions分区
1.2.4、type 关联查询或者访问类型，及MySQL决定如何查找表中的行，查找数据行记录的大概范围
依次从最优到最差：system>const>eq_ref>ref>range>index>All
system 特殊的const，结果集就只有一条数据;
const 常量索引，唯一主键;
eq_ref唯一索引（小表驱动大表）;
ref不是使用唯一索引;
1.2.5、possible_key 可能用到的key
1.2.6、key 用到的索引
1.2.7、key_len 用到索引的长度(如索引是int类型 是4字节就是4，多出来的1可能是判断是否是null) 3n+2
1.2.8、ref 索引关联查询的字段
1.2.9、rows 查询需要扫描的大概行数
1.2.10、Extra 额外信息
using index 使用覆盖索引
using where
1.3、索引失效
1.3.1、在索引列上做任何操作会导致索引失效而全表扫描
1.3.2、字符串不加单引号
1.3.3、用!=