4、《索引》
04 | 深入浅出索引（上）
05 | 深入浅出索引（下）
一、深入浅出索引（上）
索引的作用：提高数据查询效率，类似于书本的目录。
常见索引模型：哈希表、有序数组、搜索树。在MySQL的InnoDB引擎里，索引模型用的是B+树！
1、索引模型：哈希表
[x]哈希表：键 - 值(key - value)。
[x]哈希思路：把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置
[x]哈希冲突的处理办法：链表。（这和java中的hashmap的hash冲突处理方式是差不多的）
[x]哈希表适用场景：只有等值查询的场景。绝对不适合区间查询：由于采用的是hash，那么就不是有序的，我们在区间查找时候，就需要扫描全部才能获得结果。
2、索引模型：有序数组
[x]有序数组：按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))
[x]有序数组查询效率高，更新效率低
[x]有序数组的适用场景：静态存储引擎。比如某一年的所有学生成绩，（静态的数据）以后都不会修改。对等值查询和区间查询效率都很优秀，对于查询效率高，但是对于某个位置插入数据就必须得挪动该位置后面所有的记录，成本太高。
3、索引模型：搜索树
[x]二叉搜索树：每个节点的左儿子小于父节点，父节点又小于右儿子
[x]二叉搜索树：查询时间复杂度O(log(N))，更新时间复杂度O(log(N))。当然为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。
[x]数据库存储大多不适用二叉树，因为树高过高，会使用N叉树。
4、N叉树
[x]树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。
[x]你可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。
[x]以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。
[x]N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。
[x]不管是哈希还是有序数组，或者 N 叉树，它们都是不断迭代、不断优化的产物或者解决方案。数据库技术发展到今天，跳表、LSM 树等数据结构也被用于引擎设计中，这里我就不再一一展开了。
[x]为什么数据库存储使用b+树 而不是二叉树，因为二叉树树高过高，每次查询都需要访问过多节点，即访问数据块过多，而从磁盘随机读取数据块过于耗时。
5、InnoDB中的索引模型：B+Tree
6、索引类型：唯一索引（主键索引是一种特殊的唯一索引 NotNull）、普通索引
[x]唯一索引：命中即结束！（只存在一个）
[x]普通索引：命中后 继续查找下一个，直到下一个不命中则结束（因为有可能大于一个）。但是如果sql语句中指定了limit 1，那么就命中第一个就结束了。
[x]主键索引(聚簇索引)的叶子节点存的是整行的数据，非主键索引(二级索引)的叶子节点内容是主键的值
[x]主键索引和普通索引的区别：主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)
7、数据页
一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。
8、索引的维护
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以下面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。
如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。
当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。
自增主键防止页分裂，逻辑删除并非物理删除防止页合并。从性能和存储空间方面考量，自增主键往往是更合理的选择，或者我们的主键在业务上可以保证是递增的（不管是数字还是字符串）。
主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
9、思考题
[x]重建主键索引：
alter table T drop primary key; 不当的执行语句1
alter table T add primary key(id);不当的执行语句2
重建主键索引：如果安照上面两条语句删除主键，再新建主键索引，会同时去修改普通索引对应的主键索引，性能消耗比较大。解决办法： 使用alter table T engine=InnoDB； 替代上面的两条不当操作语句。
举个例子可以用到 alter table T engine=InnoDB；来重建主键索引。例子：某张表money会定期删除一些时间久远的数据，表的过期数据是删除了，但是 InnoDB 这种引擎不会自动删除过期数据的索引，所以可能表数据10g，但是其索引就40g（因为过期数据的索引没删除），那么为了解决，我们只有重建主键索引来释放空间，那么alter table money engine=InnoDB；就派上用场了。
[x]重建非主键索引：删除重建普通索引貌似影响不大，不过要注意在业务低谷期操作，避免影响业务。
[x]不建议使用长字段做主键索引，尽量选短字段做主键索引
详解：非主键索引会存储主键索引的值用来回表！比如：一张表中roleId为long类型的主键，userId为long类型的普通索引或者唯一索引。
那么userId的索引会存放roleId索引的值用来回表。如果roleId是个大字段，那么就会导致userId的索引存储的空间占用大。
所以我们一般建议：使用自增主键，或者使用小字段作为主键。这样可以让非主键索引占用的空间变得小一些。当然了，如果某张表中，
你只需要主键索引，不再需要其他索引，就无所谓你的主键是大字段还是小字段了。
[x]我们平时的表，除了一个主键外，还需不需要建立其他索引，如果建立主键外的其他索引，那选择唯一索引 or 普通索引？
答：（1）如果我们业务经常存在查询条件是该表中 除主键外的字段，就可以建立其他索引。
（tb_user表，主键userId，业务经常 where userName=xxx，这时userName可以建立索引）
（2）除了主键外，选择唯一索引 还是 普通索引，要看业务情况。
change buffer一般是普通索引的优化机制使用的，当所更改的数据所在页不再内存中时，它可以将数据暂时缓存起来，当触发merge时候，才会真正触发磁盘IO（change buffer只针对索引页的数据！！！只是优化索引页的增删改）。
对于 写多读少的业务，页面在写完后马上查询的概率很小，此时change buffer就使用效果最好，也就是推荐使用普通索引。
对于 写完后马上要做查询的业务，即使使用change buffer，但查询要触发merge，这样change buffer就没有了意义，反而增加change buffer的维护代价，这种业务模型，使用普通索引就起了反作用。
日志、账单类：（写多读少，一条数据写后立马访问的概率很小，change buffer就会发挥最大效果），推荐普通索引。
游戏中的玩家表tb_user，userId是主键，表中的userName字段如果要做索引，也推荐普通索引。（按道理来说，该表更新后肯定马上访问也多啊？为什么还使用普通索引，不是和前面说的矛盾了吗？哈哈哈，因为名字如果可以随便重复，那么肯定不能使用唯一索引了。
总结：普通索引 和 唯一索引 在查询上能力几乎没有差别，主要考虑的是更新的性能（唯一索引要判断冲突），所以我建议你尽量选择普通索引。 普通索引配合change buffer收益高，特别是机械硬盘，因为change buffer配合普通索引，可以更大程度的减少磁盘的IO。特别地，如果某个字段需要建立索引，我们业务要求该字段必须唯一，但是我们生成的字段无法保证唯一，那么建议使用唯一索引，保证表中不会存在该字段重复。
（1）普通索引与唯一索引，普通索引在更新时速度更快，尽量选普通索引。
（2)更新之后马上就是查询时，那么change buffer 意义不太大了。
写多读少使用 changebuffer 可以加快执行速度(减少数据页磁盘 io); 但是,如果业务模型是 写后立马会做查询, 则会触发 changebuff 立即 merge 到磁盘, 这样 的场景磁盘 io 次数不会减少,反而会增加 changebuffer 的维护代价。
(3)change buffer更适合普通索引。
小结：第4点的问题，从普通索引和唯一索引的选择开始，和你说明了 change buffer 的机制以及应用场景，最后讲到了索引选择的实践。由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发我建议你优先考虑普通索引。
二、深入浅出索引（下）
1、回表、覆盖索引
table：money，主键id，索引k。
语句1：select * from money where k=5;
流程1：由于k是存在索引的，所以 在k索引树上找到k=5的记录，取得其记录的主键id值，然后根据id值，再到id索引树取得对应id记录的该行数据（回表）。
语句2：如果存在一个业务：根据k的条件获取id即可，不需要获取整行数据，我们可以 select id from money where k=5;
流程2：此时k存在索引，所以在k索引树上找到k=5的记录，取得其记录的主键id值。此时我们 select id只需要id，则不需要回表。（ 覆盖索引）
覆盖索引：就是语句2中的情况，在k的索引树找到对应k的记录，就能获得我们的查询请求，就是覆盖索引。 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
覆盖索引 使用场景：玩家表！！！主键id，普通索引money，要查找玩家money>=100w的id集合！select id from player where money>=1000000;
（覆盖索引：我们除了主键索引外，如果还经常用到某个字段，就可以为其建立普通索引，这样根据条件只需要主键时，就可以不回表，提高效率。）
三、实践
例子：字段a和字段b，都是建立了普通索引的，我们现在的语句如下：
当我们的语句中，没有指定某个索引时候，MYSQL的优化器负责选择索引，第一条语句就是没有自己指定，第二条我们使用force index()强制指定了！
我们发现，没有使用force index()时候的查询效率很低下！
为什么会出现这种情况？因为mysql的优化器在选择索引时候，有时候会有bug。优化器没有选择正确的索引，force index 起到了“矫正”的作用。
浙公网安备 33010602011771号