mysql存储引擎与索引区别_1 MySQL索引和存储引擎

MySQL索引和存储引擎

0 主要内容

1 MySQL索引

1.1 索引的理解

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息，就像一本书的目录一样，可以加快查询速度。InnoDB 存储引擎的索引模型底层实现数据结构为B+树，所有数据都是存储在 B+ 树中的。

1.2 聚簇索引和非聚簇索引

聚簇索引： 聚簇索引也称为主键索引，聚簇索引的索引和数据是放在一起的， 其索引树的叶子节点中存的是整行数据 。 一个表只能包含一个聚集索引，因为索引(目录)只能按照一种方法进行排序。

非聚簇索引：非聚簇索引也称作普通索引，非聚簇索引的索引和数据是分开放的， 非聚簇索引的叶子节点内容是主键的值。

1.3 mysql底层索引的原理

1.3.1 mysql的底层实现：B+树

B+树：B+树上层只放key值，将数据全部放到叶子节点层，这样存储的数据会更多；

结构：上层是树结构，由根到子节点，最后一层叶子节点是双向链表；

1.3.2 B树与B+树的区别

B树与B+树的结构大致相同，区别在于：

B树是将节点和数据放在一起的，每个节点上都存有数据；

B+树是对B树进行优化，将节点上的数据全部放到最后一层的叶子节点上，这样每一层可以存储更多节点，也就是可以保存更多的数据；

                    

B树                                                                                                                                       B+树

1.3.3 为什么mysql底层实现不用哈希表或者B树？

为什么不用哈希表？

1 会产生哈希碰撞；

2 哈希表适合等值查询，不适合范围查询；

为什么不用其他树？

无论是二叉树还是红黑树，都会因为数据过大导致树的深度过深，而造成IO次数变多，影响数据读取效率。(磁盘和内存交互的IO次数会过多)；

二叉树和红黑树每个节点只放一个值(节点)，当数据量很大时，树的深度就会很大；

为什么不用B树？

B树，每个节点上还存储有数据，会浪费空间，我们为了节省，干掉每个节点上存储的数据，这就变成了B+树，B+树上层只放key值，将数据全部放到叶子节点层，这样存储的数据会更多；

1.4 技术名词

回表、覆盖索引、最左匹配、索引下推

各概念的事例参考链接：https://www.cnblogs.com/Chenjiabing/p/12591343.html

1 回表

当用到普通索引/二级索引/辅助索引时，会查到两棵B+树时，称为回表；

这里要了解一下主键索引查询和普通索引查询(回表)的区别：

主键索引查询：主键索引的叶子节点存放的是整行的数据，只用到一次B+树；

非主键索引查询(回表)：叶子节点放的是主键的值，然后再根据主键的值找到数据， 用到两次B+树；

当要通过name字段查询age时，要先通过name查询到id(自动忽略age)，然后回表通过id再去查询到对应的age。

参考链接：https://www.cnblogs.com/Chenjiabing/p/12600926.html

(结合上述文章里的图表好理解)

2 索引覆盖

如果在普通索引树上的查询已经直接提供了结果，不需要回表操作，这样的普通索引叫做覆盖索引。覆盖索引的使用可以显著提高查询效率，是常见的MySQL性能优化手段。

3 最左匹配(最左前缀原则)

最左匹配是针对于联合索引而言，优先查找最左边的索引。如果左边的索引找不到，就不会触发索引；

例如abc联合索引，只有当a或者ab或者abc为条件时才能触发索引 ；

4 索引下推

针对在联合索引情况下：

没有索引下推： 只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值 ；

有索引下推：可以在索引遍历过程中，对索引中包含的其余字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，提升查询效率，减少IO；

2 存储引擎

2.1 两种存储引擎

MySQL中最常见的存储引擎有InnoDB和MyISAM ，两者都是采用B+树存储数据，只是底层data里存的数据不一样；

MyISAM的叶子节点中存放的不是数据，而是主键值，要通过回表才能查询到具体的数据，即：索引和数据是分离的；

InnoDB的叶子节点中存放的就是数据；

2.2 B+树节点中的key到底是什么？什么是聚簇索引和非聚簇索引？

聚簇索引和非聚簇索引的区别就在于你的索引和数据是不是放在一起的；

当索引和数据放在一起时(聚簇索引)，你的key可以是主键，也可以是唯一键，也可以是6字节的row_id(这个我们是看不到的)；