Mysql索引及其常见问题

首先要了解一下索引知识：

索引

• 概念
  索引是为了加快对数据的搜索速度而设立的，索引是方案中的一个数据库对象，与表独立存放，索引是帮助Mysql高效获取数据的数据结构，存储在文件系统中，索引的文件存储形式与存储引擎有关，索引文件的结构为：hash、二叉树、B树、B+树

• 作用
  在数据库中用来加速对表的查询，通过使用快速路径访问方法快速定位数据，减少了磁盘的I/O

• 分类

  • 主键索引
    主键是一种唯一性索引，但它必须指定位PRIMARY KEY,不允许有空值
  • 唯一索引
    索引列的所有值都只能出现一次，即必须唯一，值可以为空，唯一索引不会发生回表

  		create unique index i_ename on emp(ename);
  		
  

  • 普通索引
    基本的索引类型，值可以为空，没有唯一性的限制，普通索引会发生回表

  		create index i_ename on emp(ename);
  

  • 全文索引
    全文索引的索引类型为FULLTEXT，全文索引可以在varchar、char、text类型的列上创建，就是在一堆文字中查找某个关键字

  • 组合索引
    多个字段组合创建的一个索引，专门用于组合搜索，使用组合索引时遵循最左匹配原则

create index i_msg on emp(ename,age);

• 重点
  索引是存放在电脑的磁盘中的，而不是存放在内存中的，只是在进行sql查询的时候，把磁盘中的索引加载到内存中

开发中使用索引的要点：

1、索引改善检索操作的性能，但降低数据插入，修改和删除的性能。在执行这些操作的时候，DBMS必须动态地更新索引
2、索引数据可能要占用大量的存储空间
3、并非所有的数据都适合于创建索引，唯一性不好的数据（如省）从索引得到的好处不比具有更多可能值的数据（如姓名）从索引得到的好处多
4、索引用于数据过滤和数据排序，如果你经常以某种特定的顺序排序数据，则该数据可能是索引的备选
5、可以在索引中定义多个列（如省加城市），这样的索引只在以省加城市的顺序排序时有用，如果想按城市排序，则这种索引没有用处。

什么场景不适合创建索引

1. 那些查询次数比较少的数据，因为有无索引，都不能提高查询速度，反而，创建了索引，浪费了存储空间
2. 那些只有很少数据值的列不应该创建索引，本来的查询结果就相当于全表查询了
3. 定义为text、image、bit数据类型的列不应该创建索引，这些列要么取值很大，要么取值很少
4. 当修改性能远远大于检索性能时，不应该创建索引。当创建索引，就会降低了修改性能。

索引的相关操作

删除索引

drop i_msg on emp;

alter TABLE emp  drop index  i_msg ;

查看索引

show index from emp

Mysql索引的数据结构 —— B+Tree

B+Tree是在BTree的基础之上做的一种优化，变化如下：
1、B+Tree每个节点可以包含更多的节点，这个做的原因有两个，第一个原因是为了降低树的高度，第二个原因是将数据范围变为多个区间，区间越多，数据检索越快
2、非叶子检点存储key，叶子节点存储key和数据
3、叶子节点两两指针相互连接（符合磁盘的预读特性），顺序查询性能更高
结构图如下：

可以看到只有叶子节点存储数据，而非叶子节点都是存储记录中的主键的，所以非叶子节点存储的主键值范围比较大，三层的B+Tree就可以存储大量的数据，而且读取的数据也会相对快

为什么InnoDB索引不使用hash表（哈希表）

• 哈希表可以完成索引的存储，每次在添加索引的时候需要计算指定列的hash值，取模运算后计算出下标，将元素插入下标位置即可，适合场景：等值查询
• hash表中的数据是无序的，一旦遇到范围查找的适合比较浪费时间，需要挨个进行遍历操作，在企业中多数的查询都是范围查询，所以hash表不是特别适合
• hash表在使用的时候，需要将全部的数据加载到内存中，比较耗费内存的空间

为什么mysql索引不能使用多叉树，二叉树，avl树，红黑树？

多叉树，是可以存储mysql索引的，不过多叉树每个节点都有key，同时也包含data，而每个页存储空间是有限的，如果data比较大的话会导致每个节点存储的key数量变小，当存储的数据量很大的时候，会导致深度较大，增大查询时磁盘io次数，进而影响查询性能

二叉树，二分查找，查询的时间比较快，当如果子树永远都是一个方向延申，与没有使用二叉树的效率是相近的，所以也不太适合使用二叉树。

avl树（平衡树），是一颗严格意义上的平衡树，最高子树跟最低子树高度之差不能超过1，因此在进行元素插入的时候，会进行1到N次旋转，严重影响插入性能

红黑树：基于AVL树，损失了部分查询的性能，来提升插入的性能，在红黑树中最低子树跟最高子树只差小于2倍即可，在插入的时候，不需要进行N多次的旋转操作，而且还加入了变色的特性，来满足插入和查询性能的平衡

总结：二叉树及其N多的变种，都不能支撑索引，原因是树的深度无法控制或者插入数据的性能比较低，造成io次数变多，影响数据读取效率

多叉树的基础知识：又称为B树（B-树），所有键值分布在整棵树中，搜索有可能在非叶子结点结束，在关键字全集内做一次查找，性能逼近二分查找，每个节点最多拥有m个子树，根节点至少有2个子树，分支节点至少拥有m/2子树（除根节点和叶子节点外都是分支节点），所有叶子节点都在同一层，每个节点最多可以有m-1个key，并且以升序排列。每次io读取都是4k的大小，所以一个磁盘块相当于一次读取的大小。
多叉树的结构：

常见问题

回表

指的就是搜索引擎根据普通索引，第一次查询的是普通索引的B+Tree，第一次查找出的id或者是主键索引，再到主键索引创建的B+Tree进行搜索数据的这个过程

覆盖索引

本来要进行一次回表，不过根据搜索条件，第一次查询就可以得到答案了，不发生回表操作就是所谓的覆盖索引
如：select id from emp;

最左匹配

在创建组合索引的过程中，会先按照最左的关键字进行创建，所以创建组合索引的时候十分注重顺序
如：在emp表中有id,name,age属性，创建了一个组合索引(name,age)
select * from emp where name = ? and age = ?;(此sql中与索引的最左元素匹配)
select * from emp where age = ?; (此sql中没与索引最左元素匹配，没有利用索引进行检索)

拓展：上面的例子中如果我想两条语句都能发生索引，那么我创建索引时，是创建(name,age) + (age) or (age,name) +(name)？
在前面我们都知道索引是要占用磁盘空间的，索引创建索引时，索引占用的空间越小越好，从题目中得知索引(name,age) 与索引 (age,name)占用的空间是一样的，比较两种方案的后者，可以得知，第一种方案更加合适

索引下推

在建立普通索引的时候，通常都会先在普通索引的B+Tree中遍历一遍，然后找出对应的id再发生回表，到主键中的B+Tree中进行查找，一般情况下，在普通索引下的所有id都会发生回表，而索引下推则是根据sql语句，在第一次查找的时候，筛选出与条件相关的id，再发生回表，这样大大减少了回表查询的数据，加快了查表的效率