面试数据库（三）—— 索引（待补充）

primary key和Unique的区别

1）主键肯定是unique的，unique不一定是主键

主键并不需要unique关键字来修饰，但是具有唯一性的键却不一定是主键，可以在定义表时，指定任意一列是unique的，意为该列全部值不重复。

一张表中只能有一个主键，但是可以有多个unique

2）主键不能为null值，unique可以为null，但是只能有一个

有点想到HashMap可以put(null, null)（算hash时null返回0，所以存在table[0]中），但是仅仅只有一个null key

3）主键可以为多列组合而成（联合主键），unique只能修饰一列

一张表中只能有一个主键，但是可以有多个unique

索引

1）主键是自动建立索引

2）

翻译一下：

① 多个单列索引时空开销大

空间：

最好情况下，每一层节点数都是最大m，此时树最矮：m+m^2+m^3+...m^(n-1)+m^n 约为 m/(m-1) * m^n，而m^n就是叶子节点数N，故总空间：N * m/(m-1) ,如当m取4则为N*4/3（因为单列多个索引肯定不是主键索引，故叶子节点存储的是主键值和索引值，常量空间）。对于大小为N的表，建立k列的单独索引空间开销为：k*N*4/3，而建立k列的组合索引空间开销为N*4/3（每个叶子节点多个索引值相对于单个索引值多出类的空间可以忽略不计）。故是k倍的差距。

即(a, b, c)联合索引建的是一颗B+数，(a）, (b)，(c)建立的是3棵树，因为树节点总数一样，所以三棵树用的总空间是一样。

时间：每次写操作，要同时更新3棵树，开销巨大

② 多个单列索引就算符合最左匹配原则，也只有第一列有用

组合索引一次访问即可拿到记录（叶子节点），单列索引在进行过第一列a的匹配后，会返回主键id序列（按列a是有序的，但按主键id大概率是无序的），用一次IO取出磁盘上这些id序列对应的row到内存，利用b和c作为“条件”过滤（就跟没加任何索引的列一样）

当然，下面提到的第③点说明这个在实际中不一定成立（示MySQL自己的优化而定）。

③  MySQL的合并索引优化

以(a）, (b)，(c)为例，where a = 1 and b = 2 and c = 3，即使可能有个索引存在，真的只会用一个吗？

原理上，只能用一个index，回表后，后面的单列索引只能作为“条件”过滤（就跟没加任何索引的列一样），但是实际使用中，MySQL给了一种index-merge，即合并索引的策略。

以上为例，其实查过a之后不用直接回表IO，直接再去b的索引树中找到对应的id序列，再去c中找到索引树中找到对应的id序列，将他们分别按照id排序，然后merge求交集（如果where条件是or，就求并集）

在explain上的type上，有“index_merge”：

 但是这个依赖于MySQL自己的优化，用户自己设定索引时，还是要尽量避免使用多个单列索引的情况。

什么情况下使用多个单列索引而非联合索引？

1）read远远大于写

这样更新索引的开销可以被查询带来的收益抵消

2）read时，单个查询条件的read比较多

如果仅仅是read多，联合索引也可能满足需求

不仅是read多，而且是单个筛选条件如仅仅是a = 1，b = 1，c = 1的其中一项

此时既想加快查询速度，联合索引又用不着（不符合最左匹配原则），只能用多个单列索引了

但是这种情况下，是否考虑使用其他的DB如ES？

作为遗留问题吧。。
 

Memory引擎：哈希索引

 1）Memory支持显式哈希索引

其他的引擎底层就算支持，也不是显式的，是否用哈希索引用户是控制不了的。

2）必然导致两次IO

3）用户自定义的哈希索引不是真的hash

用户自定义哈希索引（自定义哈希函数）时——其实这是个伪哈希索引——还是由B+树来实现，但是可以明显缩小中间节点空间，，具体实现来说即，删去原来加在某长度非常长的列上的索引，而在右侧添加一个“索引”列，该列专门存储该行长列对应的哈希值，并在该列上建立索引，具体来说

① 使用触发器在insert update时设置/修改相应的hash值

② 在select时和delete时添加条件hash(rawKey) = 索引列的值

当然，为了避免select时最好利用原数据和hash值两个条件，这样可以返回发生冲突时其他同key的值：

（crc是对应的自己算出来的hash值，CRC32是自己设计的或者至少MySQL不支持的hash算法）

索引并不是所有场景的解决方案

小，特大表都不适合做索引，中到大型表适合：

优化器可能会做出自己的判断：到底是使用索引好，还是走全表扫描好

一个最简单的例子：只有10行的记录表，一次IO把整个表加载到内存，直接过滤

如果使用索引，至少两次IO：一次索引页的IO，一次数据页的IO

联合索引

1）最左匹配原则：where中的顺序并不影响，是否出现会影响

是什么，为什么，略。

注意：where中的顺序并不影响，是否出现会影响

Where字句中存在组合索引时，MySQL会使用最左匹配原则先找组合索引中最左列，如果没找到则不会使用该组合索引，如果找到，则使用，但是注意，mysql会自动优化顺序，如 where name = "Tom" and id = 8 虽然id是组合索引的最左列，MySQL一样会使用该组合索引，最左匹配原则是从集合中从右往左找——where中的顺序并不影响，是否出现会影响。

2）索引命中截止位置

即范围查询，or 缺失，这会导致索引命中截止

截止后查询如下：

select * from tableName where col1 = 5 and col3 =6;

只命中了col1，带着col1=5的所有ids，回表查询，col3 =6作为筛选条件过滤（跟没有任何所以你的列作为条件一样）

3）如何最大程度命中联合索引

列匹配条件中：

① 最好等值（如果区间值较小也建议使用IN，可以尽量延长索引命中距离）

IN相当于用或连接的等号

② 不要使用任何库函数或算符进行计算：

对于varchar text等长文本域，可以建立前缀索引：

语法简单，但是要注意这个最佳长度的选择，可以经测试确定最短长度——科学术语叫不同前缀的选择性：

当这个选择性基本达到列本身的选择性——COUNT(DISTINCT city)/COUNT(city) from sakila.city_demo时，就够了

在组合索引中，确定组合索引的顺序：

但是由于频率最高这个形容其实过于理想化，实际中依赖于where后面的条件：

即不同的具体值而言，查询出来的结果不一样。

⑤ 组合索引的列insert和update时不能是NULL值，如果有则不会将该列加入到索引项中（不会为该行建立索引），即所有涉及到该行的select都会忽略该行。

（奇怪的是经实测，是可以向组合索引列插入NULL值的）

故组合索引的列都要设置NOT NULL

⑤ 哈希索引和B+索引

1）where后面的条件不同

直接对比哈希表和B+树（本质：m叉查找平衡树）：哈希只是单值，通过直接寻址返回key的value，没有记录任何大小的信息，即哈希索引只适用于where后面的条件是 = 或 IN（OR连接的=）。而B+树是一棵排序树，记录着所有排序信息，实际上B+树在最后一层放置了一个排好序的链表，而上层更像是索引，这个角度来说B+树更像是更为严格的跳跃表。。。但是每一层节点位置固定，节点内部key个数有限制。它的结构决定了B+索引不仅可以单值查询——m叉树的查找问题，也可以应用于范围查询——利用边界值能确定在最后一层的区间，返回从start到end指针之间的值即可。故B+树可以应用于where后面的条件是 = ,<=，>=，>，<  BETWEEN IN（OR连接的=）的索引。

2）对于组合索引的适应性

我们知道，对于组合索引，B+索引能够使用最左匹配原则，举个例子，如果一个组合索引由n个列组成，那么最左匹配可以支持where后 2^(n-1)（后面的n-1可以出现，也可以不出现）种情况，但是哈希索引不支持最左匹配原则，即如果使用哈希索引建立n列的组合索引，则每次where 后面必须给出n列每一列的值，还必须是等号“=”时，哈希索引才生效。

究其原因：还是从哈希和B+的数据结构实现来看

哈希是根据原始值通过一定的哈希算法计算出一个key，然后将值存进这个key为地址的空间中。若使用组合索引，insert update时，哈希需要将每一列的值都纳入哈希算法的计算中，即Hash(col1,col2..coln)计算出地址，然后插入或更新，删除同理，Hash(col1,col2..coln)找到位置删除。查找时同理，如果少了一项，hash值是没法计算的，也就不存在根据哈希值查找。

但是B+可以实现部分利用：

假设建立索引时是3列：col1 int，col2 int，col3 int

先让我们回顾一下B+索引的能力：能够支持范围查询，如：

select * from tablename where col1 >1 and (col2 >= 1 and col2 =< 10) and col3 >7;

而select * from tablename where col1 >1 and col3 >7;其实可以翻译为转化为：

select * from tablename where col1 >1 and (col2 >= min(col2) and col2 =< max(col2)) and col3 >7;

即对于没有出现的列我们认为该列的取值范围是：(col >= min(col) and col =< max(col))

OK，BTW，其实关于不等号——<>或 != 以及NOT会不会使B+索引失效的问题，不同地方有不同结果，但是本人实验是不会失效的：

应该是以前的版本没有支持，后来的版本支持了。

本人用的是MySQL 8。

究其原因，其实这是个数学问题：

1）B+索引支持所有的关系运算符号：=, >, <, >=,<= —— 

2）取非同样可以运用于所有运算符号之上：=号的非即 >或者 <，后面几个的非不用多说

(学过逻辑表达式的就更懂我的意思了)

利用这两个条件，则where后面的所有关系NOT后都可以转换为正常的“不带NOT”的关系式。此时又可以使用索引了。

具体来说:

where id != 5;则可以翻译为where id < 5 OR id >5;

where NOT id > 5;则可以翻译为where id >= 5;

NOT后面的表达式经过NOT运算以后还是“正常”的表达式，从这个角度来说，MySQL从不支持到支持，其实只用在语法解析和转换上下点功夫——NOT运算，根本不用在底层数据结构方便改变什么。

最左匹配原则

最左匹配的实战：使用 explain 查看查询的具体策略：

mysql索引最左匹配原则的理解

其实关于最左匹配原则我有一点想法：

1）为什么要有最左匹配原则，而不是像哈希索引对待组合索引那样：必须提供所有列的条件

很明显，增加灵活性，最大限度利用已建立的B+树信息。

2）最左匹配原则为什么能够实现，而不像哈希索引对待组合索引那样：必须提供所有列的条件？

其实上面已经提到——可以将省略的列的条件翻译为：(col >= min(col) and col =< max(col))

3）为什么最左匹配原则的最左列不可省？

这个就涉及到B+树的具体实现了，这里仅做猜测，利用索引检索（select）是建立在已经建好的B+树上的，所以还是要回到建树和调整树上来：回忆B+树的结构，特别是最后一层的有序链表，insert建树时需要第一列来确定所属的子空间。比如我们想一下B+树insert第一个节点时（其实就是为第一个记录建立索引的过程），不考虑其实条件，其实是可以随便放置的——链表的唯一一个节点，但是insert第二个节点时就要进行比较大小比较：是插在第一个后面还是前面，对于nbc我们是不知道插在_bc前面还是后面的，甚至xxx我们也是不知道插在_bc前面还是后面——我们至少要判断出第一列的大小关系，这样才能确定其所在有序链表的子区间。

4）like 模糊检索时，like col2 = "%bc"或者 col2= “_bc”是无法使用索引查询的

这个其实在进行了上面的分析之后是很好理解的：第一个字符省略没法进行大小比较的。此时这是在一个列的内部，之前是所有列的组合的大小。

这个问题很容易陷入误区：col2 = "%bc"不能利用索引我可以理解，因为前面不知道有多少个字符，但是col2= “_bc”为什么不行？之前分析过最左匹配原则为什么后面列可以省略——默认为(col >= min(col) and col =< max(col))，这里的col2= “_bc”难道不能认为是col2 >= “abc” and col2 >= “zbc”?

不能，abd符合col2 >= “abc” and col2 >= “zbc，但是符合col2= “_bc”吗？

明显不行，因为col2= “_bc”没法转化另外一个或有限个区间的表示——NOT可以，所以不能使用最左匹配原则——不能使用索引。

OR会使组合索引失效

由此再看一个结论：在组合查询中，where字句中，用OR连接的不同字段的多个条件，会使索引失效：

test表建立了组合索引：index(a,b,c)

可以看到type为all

在看过上面最左匹配原则的原因后，很好解释：

若是 select * from test where a = 1 AND b = 1; —— 在a确定的子区间内再次递归查找b子区间

select * from test where a = 1 OR b = 1; —— OR连接的b=1并不会受a=1的限制，换言之，查找到a = 1的区间后，b不仅要在此子区间查找b = 1，还要到左右子区间寻找，即a的约束没有了任何效果。此时 select * from test where a = 1 OR b = 1; 可以看做是select * from test where a = 1；和select * from test where b = 1; 的并集，是两个独立的查询，不能够使用组合索引。

这么理解了以后，看下面这个：

也使用了OR，但是不是和 a = 1 平行的OR，是b内部的OR，很明显这只是在a子区间内进行的多支分叉而已。并不会使a的判断条件失效，故仍然可用。

再看：

后面再加c =1，竟然key_len还是15 = 5*3，即c继续命中，换言之，使用（b = 1 OR b = 2）后索引没有停止。

看个反例：

当 b > 1这个条件时，c就无法命中了。

对于上面（b = 1 OR b = 2）改成IN和 >= AND <=都还是可以命中的：

IN：

>= AND <=

区间增大到10000竟然都还是15：

这个还蛮奇妙的，按说区间范围比较小时候可以多路下沉到下层节点，继续最小粒度的查看。但是区间大到一定程度，比如说10000，不应该下沉到1到10000对应的叶子节点开始遍历（range）吗，那应该key_len是10啊？

不太清楚。。。

另外，如果存在多个组合索引，OR可能会使本来不相关的组合索引失效：

来源：MySQL 千万 级数据量根据（索引）优化 查询 速度 - phpdragon - 博客园

由possible_keys可以看出，存在index1(username)，  index2(username，password)和index3(username，email，password)三个索引，按说 RO email = ‘800001@qq.com’会使index3失效，但是index2仍可以使用，但是最终结果type

尚不清楚机制：是真的直接走全表扫描（优化器很笨），还是先利用index2甚至index1找到其所在B+树的叶子节点的位置，然后对符合条件的区间进行遍历，判断每一个记录是否符合 email = ‘800001@qq.com’（优化器很聪明，只是现在显示的type写的是all而已）

BTW，这里插播一个知识：对于一个组合索引，命中完毕以后的操作

组合索引从最左列开始，一直到命中截止，此时可以确定在B+树上的区间（简单点考虑就是临界点左侧还是右侧）

对于没有建立组合索引的多列筛选条件——直接从表的第一项开始（其实是从表的B+树的最左叶子开始）遍历到最右叶子，对于每条记录，判断是否同时满足where的多个条件即可？

[MySQL高级](一) EXPLAIN用法和结果分析