Mysql索引

一、索引

说说你对 MySQL 索引的理解？

索引(Index)是帮助MySQL高效获取数据的排好序的快速查找数据结构
索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上

优势
提高数据检索效率，降低数据库IO成本
降低数据排序的成本，降低CPU的消耗
劣势
索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，
都会调整因为更新所带来的键值变化后的索引信息

B树

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个键将数据划分成的三个范围域，对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35
模拟查找关键字29的过程：

1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2
3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2
5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6. 在磁盘块8中的关键字列表中找到关键字29

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作，由于内存中的关键字是一个有序表结构，可以利用二分法快速定位到目标数据，而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。
可以看出使用B-树定位某个值还是很快的(10亿数据中3次io操作+内存中二分法)，但是也是有缺点的：B-不利于范围查找，比如上图中我们需要查找[15,36]区间的数据，需要访问7个磁盘块（1/2/7/3/8/4/9），io次数又上去了，范围查找也是我们经常用到的，所以b-树也不太适合在磁盘中存储需要检索的数据。

b+树

说一下b+树的几个特点：

1. 叶子节点（最下面的一层）存储关键字（索引字段的值）信息及对应的data，叶子节点存储
   了所有记录的关键字信息
2. 其他非叶子节点只存储关键字的信息及子节点的指针
3. 每个叶子节点相当于mysql中的一页，同层级的叶子节点以双向链表的形式相连
4. 每个节点（页）中存储了多条记录，记录之间用单链表的形式连接组成了一条有序的链表，
   顺序是按照索引字段排序的
5. b+树中检索数据时：每次检索都是从根节点开始，一直需要搜索到叶子节点
6. InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读取一条记录的时候，并不是将这个记
   录本身从磁盘读取出来，而是以页为单位，将整个也加载到内存中，一个页中可能有很多记录，然后在
   内存中对页进行检索。在innodb中，每个页的大小默认是16kb

B-树和B+树的区别

1.B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。
如下所示B-树/B+树查询节点 key 为 50 的 data。

B-树：从上图可以看出，key 为 50 的节点就在第一层，B-树只需要一次磁盘 IO 即可完成查找。所以说B-树的查询最好时间复杂度是 O(1)。

B+树：由于B+树所有的 data 域都在根节点，所以查询 key 为 50的节点必须从根节点索引到叶节点，时间复杂度固定为 O(log n)。

点评：B树的由于每个节点都有key和data，所以查询的时候可能不需要O(logn)的复杂度，甚至最好的情况是O(1)就可以找到数据，而B+树由于只有叶子节点保存了data，所以必须经历O(logn)复杂度才能找到数据

2. B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。

根据空间局部性原理：如果一个存储器的某个位置被访问，那么将它附近的位置也会被访问。
B+树可以很好的利用局部性原理，若我们访问节点 key为 50，则 key 为 55、60、62 的节点将来也可能被访问，我们可以利用磁盘预读原理提前将这些数据读入内存，减少了磁盘 IO 的次数。
当然B+树也能够很好的完成范围查询。比如查询 key 值在 50-70 之间的节点。

点评：由于B+树的叶子节点的数据都是使用链表连接起来的，而且他们在磁盘里是顺序存储的，所以当读到某个值的时候，磁盘预读原理就会提前把这些数据都读进内存，使得范围查询和排序都很快

3.B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确

这个很好理解，由于B-树节点内部每个 key 都带着 data 域，而B+树节点只存储 key 的副本，真实的 key 和 data 域都在叶子节点存储。前面说过磁盘是分 block 的，一次磁盘 IO 会读取若干个 block，具体和操作系统有关，那么由于磁盘 IO 数据大小是固定的，在一次 IO 中，单个元素越小，量就越大。这就意味着B+树单次磁盘 IO 的信息量大于B-树，从这点来看B+树相对B-树磁盘 IO 次数少。

B-Tree和B+Tree该如何选择？

1. B-Tree因为非叶子结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。而B+Tree
   所有的数据都在叶子结点，每次查找都得到叶子结点。所以在同样高度的B-Tree和B+Tree中，BTree
   查找某个关键字的效率更高。
2. 由于B+Tree所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于
   某个关键字的数据的时候，B+Tree只需要找到该关键字然后沿着链表遍历就可以了，而B-Tree还
   需要遍历该关键字结点的根结点去搜索。
3. 由于B-Tree的每个结点（这里的结点可以理解为一个数据页）都存储主键+实际数据，而B+Tree非
   叶子结点只存储关键字信息，而每个页的大小有限是有限的，所以同一页能存储的B-Tree的数据会
   比B+Tree存储的更少。这样同样总量的数据，B-Tree的深度会更大，增大查询时的磁盘I/O次数，
   进而影响查询效率。

Mysql的存储引擎和索引

mysql内部索引是由不同的引擎实现的，主要说一下InnoDB和MyISAM这两种引擎中的索引，这两种引
擎中的索引都是使用b+树的结构来存储的。
InnoDB中的索引
Innodb中有2种索引：主键索引（聚集索引）、辅助索引（非聚集索引）。

B+树索引可分为聚簇索引和非聚簇索引?
主索引就是聚簇索引（也称聚集索引，clustered index） 辅助索引（有时也称非聚簇索引或二级索引，secondary index，non-clustered index）。

聚集索引（主键索引）

每个表一定会有一个聚集索引，整个表的数据存储以b+树的方式存在文件中，b+树叶子节点中的key为主键值，data为完整记录的信息；非叶子节点存储主键的值。通过聚集索引检索数据只需要按照b+树的搜索过程，即可以检索到对应的记录。

非聚集索引

每个表可以有多个非聚集索引，b+树结构，叶子节点的key为索引字段字段的值，data为主键的值；非叶子节点只存储索引字段的值。通过非聚集索引检索记录的时候，需要2次操作，先在非聚集索引中检索出主键，然后再到聚集索引中检索出主键对应的记录，该过程比聚集索引多了一次操作。

如上图，主键索引的叶子节点保存的是真正的数据。而辅助索引叶子节点的数据区保存的是主键索引关键字的值

• 假如要查询name = C 的数据，其搜索过程如下：
• a) 先在辅助索引中通过C查询最后找到主键id = 9;
• b)在主键索引中搜索id为9的数据，最终在主键索引的叶子节点中获取到真正的数据。所以通过辅助索引进行检索，需要检索两次索引。
• 之所以这样设计，一个原因就是：如果和MyISAM一样在主键索引和辅助索引的叶子节点中都存放数据行指针，一旦数据发生迁移，则需要去重新组织维护所有的索引。

为什么是B+Tree?

为了减少磁盘读取次数，决定了树的高度不能高，所以必须是先B-Tree； 以页为单位读取使得一次 I/O 就能完全载入一个节点，且相邻的节点也能够被预先载入；所以数据放在叶子节点，本质上是一个Page页； 为了支持范围查询以及关联关系， 页中数据需要有序，且页的尾部节点指向下个页的头部；

通常说的这个查询走索引了是什么意思？

当我们对某个字段的值进行某种检索的时候，如果这个检索过程中，我们能够快速定位到目标数据所在的页，有效的降低页的io操作，而不需要去扫描所有的数据页的时候，我们认为这种情况能够有效的利用索引，也称这个检索可以走索引，如果这个过程中不能够确定数据在那些页中，我们认为这种情况下索引对这个查询是无效的，此查询不走索引。

从根上理解SQL的like查询%在前为什么不走索引?

在使用like的时候，如果使用‘%%’，会不会用到索引呢？
上面的结果是全表扫描(type == ALL)，并没有使用到索引。
只是使用一个%的查询结果： EXPLAIN SELECT * FROM user WHERE username LIKE ‘ptd_%’;
这个使用到了索引(type == range)。

（explain 的作用是获取 select 语句的执行计划）

综上，mysql在使用like查询的时候只有不以%开头的时候，才会使用到索引。

这个问题，其实是和 B+Tree 有些关系，索引树从左到右都是有顺序的。对于索引中的关键字进行对比的时候，一定是从左往右以此对比，且不可跳过。
为什么是最左匹配原则？这个其实很好理解。比如，我们要比较一个字符串。xttblog 与 xmtblog，我们肯定是先从第一个字符开始比较吧，第一个相同后，再比较第二个字符，以此类推。所以要从左边开始，并且是不能跳过的。SQL 索引也是这样的。
然后，我们再来看标题中的问题。% 在前，就代表，我前面的内容不确定。不确定，我们怎么比较？只能一个一个的比较，那就相当于，全匹配了，全匹配就不需要索引，还不如直接全表扫描。

最左匹配原则

下图中是3个字段(a,b,c)的联合索引，索引中数据的顺序是以a asc,b asc,c asc 这种排序方式存储在节点中的，索引先以a字段升序，如果a相同的时候，以b字段升序，b相同的时候，以c字段升序，节点中每个数据认真看一下。
查询a=1的记录
由于页中的记录是以a asc,b asc,c asc 这种排序方式存储的，所以a字段是有序的，可以通过二分法
快速检索到，过程如下：

1. 将P1加载到内存中
2. 在内存中对P1中的记录采用二分法找，可以确定a=1的记录位于{1,1,1}和{1,5,1}关联的范围内，这两个值子节点分别是P2、P4
3. 加载叶子节点P2，在P2中采用二分法快速找到第一条a=1的记录，然后通过链表向下一条及下一页开始检索，直到在P4中找到第一个不满足a=1的记录为止
   查询a=1 and b=5的记录
   方法和上面的一样，可以确定a=1 and b=5的记录位于{1,1,1}和{1,5,1}关联的范围内，查找过程和a=1查找步骤类似。
   查询b=1的记录
   这种情况通过P1页中的记录，是无法判断b=1的记录在那些页中的，只能加锁索引树所有叶子节点，对所有记录进行遍历，然后进行过滤，此时索引是无效的。
   按照c的值查询
   这种情况和查询b=1也一样，也只能扫描所有叶子节点，此时索引也无效了。
   按照b和c一起查
   这种也是无法利用索引的，也只能对所有数据进行扫描，一条条判断了，此时索引无效。
   按照[a,c]两个字段查询
   这种只能利用到索引中的a字段了，通过a确定索引范围，然后加载a关联的所有记录，再对c的值进行过
   滤。
   查询a=1 and b>=0 and c=1的记录
   这种情况只能先确定a=1 and b>=0所在页的范围，然后对这个范围的所有页进行遍历，c字段在这个查询的过程中，是无法确定c的数据在哪些页的，此时我们称c是不走索引的，只有a、b能够有效的确定索引页的范围。
   类似这种的还有>、<、between and，多字段索引的情况下，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
   上面说的各种情况，大家都多看一下图中数据，认真分析一下查询的过程，基本上都可以理解了。
   上面这种查询叫做最左匹配原则。
   顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

最左匹配原则的原理

最左匹配原则都是针对联合索引来说的，所以我们有必要了解一下联合索引的原理。了解了联合索引，那么为什么会有最左匹配原则这种说法也就理解了。
我们都知道索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。