Mysql索引

目录

一、myisam 和 innodb的区别

二、mysql的索引有哪些

三、覆盖索引和回表

四、索引灵魂拷问

---

一、myisam 和 innodb的区别

myisam引擎是5.1版本之前的默认引擎，支持全文检索、压缩、空间函数等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用，而且myisam不支持外键，并且索引和数据是分开存储的。系统奔溃后，MyISAM恢复起来很困难。

innodb是基于聚簇索引建立的，和myisam相反它支持事务、外键，并且通过MVCC来支持高并发，索引和数据存储在一起。

所有类型的存储引擎之所以支持表锁，是因为表锁是在server层实现的，而行锁是在存储引擎层实现的，不同类型的存储引擎实现方式不一样，innodb实现了行锁。

说了很多，最需要记住的InnoDB不同于MyISAM最大的两个特点就是：一是支持事务，二是支持行锁；毋庸置疑，因为这两个特性大部分都采用InnoDB引擎，其中的支持行锁就是InnoDB适合多并发优势所在。

二、mysql的索引有哪些

索引按照数据结构来说主要包含B+树和Hash索引。

• 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
• 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

聚簇索引查询相对会更快一些，因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询（这个过程叫做回表, 也就是查了两个索引树）。

B+树和hash的区别

hash结构的特点：检索效率非常高，索引的检索可以一次到位，O(1)。B树需要从根节点到枝节点，最后才能到叶节点进行多次I/O操作，所以hash的效率远远高于B树的效率。

那么为什么数据库索引还是用B树结构呢？

1、hash索引仅满足“=”、“IN”和“<=>”查询，不能使用范围查询

因为hash索引比较的是经常hash运算之后的hash值，因此只能进行等值的过滤，不能基于范围的查找，因为经过hash算法处理后的hash值的大小关系，并不能保证与处理前的hash大小关系对应。

2、hash索引无法被用来进行数据的排序操作

由于hash索引中存放的都是经过hash计算之后的值，而hash值的大小关系不一定与hash计算之前的值一样，所以数据库无法利用hash索引中的值进行排序操作。

3、对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

4、Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

（因此：键值重复率低的适合用B树索引）

hash相当于把key通过hash函数计算，得到key的hash值,再用这个hash值做指针，查找hash表中是否存在key，如果存在就返回 key所对应的value，选定一个好的hash函数很重要，好的hash函数可以使计算出的hash值分布均匀，降低冲突，只有冲突减小了，才会降低 hash表的查找时间。

b-tree完全基于key的比较，和二叉树相同的道理，相当于建个排序后的数据集，使用二分法查找算法，实际上也非常快，而且受数据量增长影响非常小。

三、覆盖索引和回表

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

而要确定一个查询是否是覆盖索引，我们只需要explain sql语句看Extra的结果是否是“Using index”即可。

1.最左前缀原则

对多个字段同时建立的索引(有顺序，ABC，ACB是完全不同的两种联合索引。)以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引。

所以在建立联合索引的时候，如何安排索引内的字段顺序？
这里我们的评估标准是，索引的复用能力。因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

2.索引下推

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能

四、索引灵魂拷问

1.索引为什么要放在磁盘中

Innodb引擎会在内存引入buffer pool，但是由于内存容易丢失，一般而言会配合各种日志和刷盘策略，将数据持久化在磁盘文件中，但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。如果采用树这种数据结构作为索引的数据结构，那每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块。

2.为什么不用平衡二叉树（B-Tree）作为索引

平衡二叉树每个节点只存储一个键值和数据的，这就意味着每个磁盘块仅仅存储一个键值和数据！存的越多，二叉树的节点也会越多，并且高度也会极其高，查找数据时也会进行很多次磁盘 IO，效率也非常低下！为了解决这个问题，就可以采用 B 树结构来解决层级过高。

B树的每个节点称为页，页就是前面提到的磁盘块，B 树相对于平衡二叉树，每个节点存储了更多的键值（key）和数据（data），并且每个节点拥有更多的子节点，子节点的个数一般称为阶。 基于这个特性，B 树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。

3.为什么要用B+树

• B 树节点中不仅存储键值，也会存储数据，而B+ 树非叶子节点上是不存储数据的，仅存储键值，如果不存储数据，那么就会存储更多的键值，层高基本不会因为数据扩大而增高（三层树结构大概可以存放两千万数据量）。
• B+树有利于磁盘的IO，数据越多，只需要提升树的阶数（节点的子节点树）即可，树就会更矮更胖，IO次数就更少。
• B+树的所有数据都在叶子节点上，所以B+树的查询效率稳定，一般都是查询3次（根节点存放在内存中）。B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。范围查找，排序查找，分组查找以及去重查找变得异常简单，而B 树因为数据分散在各个节点，要实现这一点是很不容易的。
• B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。在 InnoDB 中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

4.底层的索引结构

MySQL的数据是以页为基本单位组合而成的，页的大小是16KB，里面包含我们的多条数据，它还有指向下一页的指针和指向上一页的指针。Mysql取页还有一个预读机制（数据库在查询到一条数据的时候会把页中相邻的数据也取出来）。

将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。假设B+Tree的高度为h，一次检索最多需要h-1次I/O（根节点常驻内存），复杂度O(h) = O(logmN)。实际应用场景中，M通常较大，常常超过100，因此树的高度一般都比较小，通常不超过3。

5.Mysql底层的B+树结构

Mysql会采用页目录的目录项来指向一行数据，这条数据就是存在于这个目录项中的最小数据，那么就可以通过页目录来查找所需数据。非叶子节点是双向链表，叶子节点是单向链表。

每个节点就可以理解为是一个页，而叶子节点也就是数据页，除了叶子节点以外的节点就是目录页。非叶子节点只存放了索引，而只有叶子节点中存放了真实的数据。