mysql索引探究

什么是mysql的索引

mysql索引是为了加速对表中数据行的检索而创建的一种分散处理的数据结构；（存在磁盘或者内存中）

为什么使用索引

1. 索引能极大的减少存储引擎需要扫描的数据量
2. 索引可以把随机io转为顺序io
3. 索引可以帮助我们在进行分组。排序等操作时避免使用临时表

mysql的索引为什么使用B+Tree

1. 二叉查找树 顺序插入可退化成线性的
2. 平衡二叉树 高度不能相差1（采用左旋右旋保持平衡） 不会退化成线性 为什么不行？因为 他太深了（高度太深） 每个关键字太小了 磁盘块 是以页保存的数据量太小了 计算机一次io读磁盘一次交换数据以页为单位大约为 4kb空间没法利用计算机的这种特性
3. 为了利用计算机io特性（每次io预读4kb） 采用多路平衡查找树B-Tree 就是有多个子节点即多路，每个几点最多有路数减一的关键字 插入数据时有数据调整影响性能索引的调整
4. 加强版的多路平衡查找树 B+Tree 关键字采用左闭合区间 采用加强版原因叶子节点保存数据，非叶子节点只保存关键字，相同的空间可以保存更多的关键字

B+Tree和B-Tree的区别

1. B+节点关键字采用左闭合区间的形式
2. B+非叶子节点不保存数据相关信息，只保存关键字和子节点的引用
3. B+关键字对应的数据保存在叶子节点中 好处：节省空间不包含数据的话4k存储更多的关键字节点
4. B+叶子节点是顺序排列的，并且相邻节点有顺序引用的关系 可以快速做排序 子节点数据是顺序的链表

为什么用B+Tree

1. B+是B的plus版本，多路绝对平衡查找树，他拥有B树的优势
2. B+树扫库扫表能力更强
3. B+树磁盘读写能力更强
4. B+树排序能力更强
5. B+树查询效率更稳定

mysqlB+Tree索引的体现-myisam搜索引擎

myisam搜索引擎在创建一个表时会产生三个文件id索引和其他索引没有主次之分

1. frm文件是表元数据
2. MYD文件表示存储数据
3. MYI文件表示存储索引 非聚集索引

mysqlB+Tree索引的体现-innodb搜索引擎

innodb搜索引擎是以主键为索引来组织数据存储的 比如以name为列建立的索引叶子节点是主键然后再通过主键索引查找数据 没有主键会自己创建主键索引

innodb 为什么以主键索引为基础

设计理念：他认为主键才是经常使用的索引

为什么辅助索引保存的是主键而不是数据

因为防止数据移动后会调整索引的叶子节点，这样只调整主键索引的叶子节点即可，省去了维护辅助节点数据消耗

mysql索引列的离散型

离散型越大选择性越好

mysql索引最左匹配原则 联合索引

对索引中关键字进行对比，一定是从左到右依次进行且不可跳过

联合索引选择原则

1. 经常用的列优先，【最左匹配原则】
2. 离散度高的列优先，【离散度高原则】
3. 宽度小的列优先，【最少空间原则】

mysql覆盖索引

如果查询的列可以通过索引节点的关键字直接返回，则该索引称为覆盖索引 .覆盖索引可以减少数据库io将随机io变为顺序io

mysql索引建立规则

1. 索引列的数据长度能少则少
2. 索引一定不是越多越好，一定建的合适
3. 匹配列前缀可用到索引，like 888% 像like%8888 like %888%用不到索引
4. where条件中not in 和 <>用不到索引
5. 匹配范围值 order by group by 也用不到索引
6. 多用指定列查询，只返回自己想要的数据列，少用select *
7. 联合索引中如果不是按照索引最左列开始查找，将不会使用索引
8. 联合索引中精确匹配最左列，并范围匹配另外一列可以用到索引
9. 联合索引中如果查询中有某个列的范围查询，则其右边的所有列都将无法使用索引