开发者学堂课程【MySQL 实战进阶：MySQL 表和索引优化实战】学习笔记，与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/83/detail/1310
MySQL 表和索引优化实战
三、数学分析
首先，假设这张表里有一亿行记录
在这里的每一行，每一个磁盘叶，16KB 的叶能放的条目数有一个专门的名词，叫做平衡因子（Balance factor）。也就是一个16 KB 的快能放多少个条目，这个条目数有一个专门的名词叫做Balance factor 平衡因子。平衡因子或分支因子不同的文献有不同的叫法，16 KB 分支节点里存储的索引字段的个数，取两个比较极限的情况，一个是16 KB的块里只能放两条记录，类似于二叉树，第二个是每一个16 KB 块里能放100条记录。如果是 anti 和big anti 来说，正常情况下应该是100，100是取得比较小的。
big anti 八个字节，anti 4个字节，如果表不是真正存储数据量特别大，big anti 肯定是能满足的。如果不出现某些特殊情况，它的最大值可以到非常非常大。
树高影响到物理 io，真正从磁盘上获取数据到内存，需要花费多少个物理 IO。如果数据都不在内存中的情况下，需要花费多少 OBL
扫描一行数据，访问一个数据块。数据库的增删改查是把select insert update delete replace 所有的操作都是发生内存中的，这个磁盘真正上只是做持久化。
如果在将来某一天内存能够支持持久化，甚至掉电持久化，整个从体积架构上就会有一个翻天覆地的变化。树高有一个固定的计算公式，是 log b 和 n 相关的取对数，b 是平衡因子。在 b=2的情况下，树高是27，在b=100的情况下，树高是4。正常情况下应该是3左右，如果是int的类型，应该是3。从定位一行记录，开销就需要读27个16 KB 的内存，这边只要读四个16 KB 的块。这是一个很明显的开销的差异，快有七倍的差距了。
最重要的还是存储尺寸，不在包括任何二级索引的情况下，只说主键，主键里包含了表的数据，它大概需要花费728个GB 的存储容量，它只有15个GB，RDS 内存正常情况下比较大的可能到470，470个GB 内存，470个GB 内存一般情况下75%，70%到75%左右，分配给 innodb double pull，用来缓存磁盘块，或者叫磁盘叶，到内存的 oracle 叫 double catch，这个尺寸是用来缓存数据的。
想象一下，如果是一张很核心的表，15个GB的数据，对于470×75%，或者是乘以70%这样的一个尺寸来说是完全可以保证数据在内存中的。只要可以保证数据使用的足够频繁，可以说它的所有数据都是缓存在内存中的。即便选择最大的规格，对于这张表来说，这里只考虑主键，不考虑任何二级索引，正常情况下，对核心表来说是不可能的，这也需要781个GB。经常需要出现物理 io 来换入换出数据，同样的存储，两边的代价完全不一样，性能是完全截然不同的。物理IO是非常慢的。
四、索引的作用
一出 MySQL 查询慢了，CPU 满了，首先考虑的是添加索引，提到索引就必须介绍图书馆的模型。首先，先说一个传统的套路，去图书馆里找一本书，后来的书库可能有几十亿本书，从里面找一本书或几本书，正常情况下是需要一个目录的，查目录得知需要找的书在哪个书架上的哪个位置，这样可以快速的把这本书找到，也就是定位数据。这个目录就相当于索引，用空间换时间，要存储这个目录需要花一定的代价，以空间提前做准备换取访问的时间。
这个模型有几点需要注意，第一点，访问的只是其中很少很少的一个数据，可能几十亿本书的书库只提了几本书，极小的一部分数据，目录是有用的。反过来说，现在要搬家，进去之后要拿一半的书走，找不到目录是没有意义的，找到目录以后，50个架子拿走25个就好了。这种情况如果需要访问的数据量占总数据量的一定比例后，可能是另外一个事情，量变会引起质变的。
索引到底是做什么用，为什么加索引可以提高访问速度？会经常提到优化，从我的理解上看，优化是一个偷懒的艺术。真正的优化在提高访问效率，真正去实现可能需要一些技巧。
介绍两个概念，一个 TR，一个 TS。TR 是随机的访问一个16 KB 的块，随机访问 random access 或 random read，这里叫 random class，随机的读一个数据块花费的时间。TS 是 spring show read，是顺序的读一个16 KB 的块需要的时间。这两个基本上是1000倍的差距。
在传统的磁碟访问里，会有随机访问和顺序访问，随机访问就是不知道下一次要跳到哪读取数据块，数据访问知道下一个块要读取哪，这就是区别。
1000倍已经是很保守的估计了，如果是磁盘的磁碟的话会有转速，为什么高转速的磁蝶更贵，平均访问时间会更小，因为转速快，可以快速的把需要的地址转到磁高下访问。
现在都用 SSD，考虑 SSD 是没有意义的，即便是 SSD 也会分随机访问和顺序访问，跟妥协的时间机制还是有关系的。不做过深的讨论，只假设在这种1000倍差距的硬件条件下随机访问和顺序访问。对于 user 表，ID 作为主键，有一个年龄，first name 是名字。
对于查询来说，select fname user 表，age=10，这样一个简单的查询。
如果忽略树高，假设数据已经都在内存里，第一件事情要花一个随机读到树高，因为不知道读取的位置在上一次的哪，下一个位置对根来说是随机读，读到根之后，假设忽略树高的情况下，认为树高是合理的，这时候需要花 n－1 一个顺序读，就能把这张表便利一下。因为没有索引，位次条件下，添索引的第一件事情是考虑位次条件，where 后的条件是什么，根据什么条件访问数据，就要根据规则优化访问。
age 上没有索引，没有索引是对全表扫描，在不同的情况下，全表扫描的时间是多少。如果这张表里有90万行数据，根据公式需要九秒钟，如果表里只有9000行数据，可能需要100毫秒，有的业务可以接受，有的业务不能接受，如果加上网络访问的时间，可能就是0.3秒，如果只导入九行数据，随机读会占很大的一部分，10.08毫秒。
全表扫描不一定不是好事，这取决于访问、目的、还有当前的数据量是多少。如果表里没有什么数据，不需要建索引，网上很多建议表创建完之后都要建索引，这不是一个好的建议。如果这张表里没有什么太大的数据量，没有必要去访问索引，根据公式推理。第一件事情，如果访问的位次上没有索引，只有一种方法全表扫描或者是全表扫描获取数据。全表扫描不一定不好，不好的代价在于表里的数量随着数据量的上升，TS访问的代价会快速增加，就会导致时间快速增加。
时间随着数据量增加，上线时间长了之后，表的数据量越来越多，发现查询越来越慢，可以在上面加一个索引，a制字段加一个索引，访问会变成如图所示的样子
首先去访问索引，数据量是在其中很少的一部分，只有三行。只有三行数据，首先需要经过扫描索引找到这三行之后，根据主键回表。是因为索引里没有 fname，需要的是 fname 而不是 age，age 是已知的。根据 age只能找到主健，这个索引里没有主键，必然会有三个随机读，因为不能保证三个组件都是 a 制的。肯定是三个随机读，一个随机读读到索引的根，然后加三个顺序读，读到这三行数据，同时，会有三个随机读会回表取真正的 fname 三个值，这样公式就很简单了，加在一起是四个随机读加三个顺序读。
成本是40个毫秒，只是加了一个索引，在九十万行的情况下，从九秒钟的查询时间变成了索引扫描，实际上不是索引扫描，不是 indx scan，是 indx seek，索引定位，40个毫秒效率提升了225倍，这时候推出的一件事情就是查询慢，要在位次条件对应的情况下，在上面建索引。有一个前提是获取的数据量是占总表的数据量很小的一部分，获取的结果片占总数据量很小的一部分，索引才是生效的。
比如说占50%，随机读会非常的多，因为扫描索引的话，90万行数据占一半扫描一半，扫描一半就会有45万行随机读，这四十五万行随机读出来是一个很惊人的时间。
不管是 oracle 的优化器也好，还是 MySQL 的优化器也好，它们会考虑的是，会根据统计信息，统计信息就是描述这张表里存储了多少数据，每一种数据会注册多少？会根据查询预估访问的数据量大概会占总表的多少？oracle 版本不一样，比如说超过20%、30%，版本不一样，会稍微有点变化。如果查询获取的数据量超过总表的数据量一定比例之后，就不能再走索引的访问形式了。如果走索引的访问形式，会带来大量的回表访问，大量的回表访问会引起大量的随机读。随机读是1000的比例，比如说有40万行，十毫秒就差了4000多秒，才能完成查询。这基本是不可能接受的，一般 TP 类型的业务要求都是秒级或者是毫秒级返回的，小时级别的返回是绝对不可能接受的。
归结为几点，第一个位次上面尽量建索引，加速访问，建索引是能减少CPU的。从时间来衡量的话，减少访问时间，减少查询的处理时间，要建索引，有很明显的比例，前提是访问的数据量占总数据量很小的一部分。之前公式最大的开销是在四个随机读上，
四个随机读的来源是三个回表的随机读，可以把这三个随机读进一步优化。产生这三个随机读的原因是因为索引里面没有 fname，没有需要的数据，可以把 fname 放到索引里面去。如果索引如下图所示写
age 建完以后，后面加一个字段 fname，让索引里面包括 fname，这个时候访问就会变成一个随机读访问到根，三个随机读找到三行数据，没必要回表了，因为索引里面就有 fname，通过扫索引的三行数据，这就满足了查询。这个时候公式就变成了一个随机读加三个顺序读，结果是十个毫秒。这个索引专门有一个名词叫做覆盖性索引，覆盖性索引能够完全满足查询不用回表的索引就叫覆盖性索引，对查询来说是最高效的访问形式。从九秒钟直接优化到十个毫秒，900倍的性能提升。最核心的表，最频繁的查询建覆盖性覆盖索引，比如说30%以上都是这类查询，都是 where、前面带的字段也差不多，这种查询建覆盖性的索引。
为什么网上很多建议这不能使用 select*，
或者把表里的所有字段列在这，开发最容易做的事情要不列 select*，要不把所有字段都放在这，这个时候会导致查询没有办法建覆盖性索引，如果把所有索引都建在这的话，如果建一个覆盖性索引变成一张 eco 索引表。这时候会带来一个问题，就是写入的开销非常大，覆盖性索引对应的字段只要一改，索引还是要跟着变的，这时候写入的开销会非常大，所以这里不能写*或所有的字段。最左原则，匹配的时候按 age 在最前面，where 条件是 age，如果后面有多个字段，
如图所示
有多个字段的情况下，首先第一个，可以根据最左边的字段去找，后边有多个条件的时候，首先按最左边的条件找。就会有一个原则，建这种组合索引的时候，区分度越高的字段放在最前面，因为区分度越高的话获得中间的索引片越小，中间结果的索引片会越小越薄。索引这种场景只有在选择很小量的数据的时候，索引是高效的；反之，中间生成的结果集，或中间的索引片越薄，数据越小，索引对查询的加速是越高的，越适合查询。像性别这种区分度太低了，一次只能过滤出50%的数据来，这样中间结果集的索引片就会太厚了，这是第一个原则。
第二个原则，最左能够匹配到位次条件不能出现在第二个，比如说有一个条件是 user id，user id 在最前面，是不生效的。后面的位次条件一定要匹配到第一个字段才可以，oracle 不一样，oracle 有 indx scan stap，它生效的条件会非常严格。最左原则是在匹配索引跟查询位次的时候是按照从左到右来匹配，如果已经有这个查询，在建组合索引的时候，一定要把几个条件里面区分度最高的放在最左边。是不是要经常考虑区分度？还有这个字段是否会经常改变？
像订单表 order status，status 会经常变，虽然它取值也比较多，有十几种取值，但是也经常的变。如果字段经常变的话，放在第一位，会导致数据被掰开，这种对 update 语句是不友好的，会影响 update 语句的 rt 响应时间。这种情况下，如果还有跟它区分度类似的字段，尽量的往前放，经常被更新的字段，尽量往后放，这是第二个原则。
第三个原则，如果相同的区分度都差不多，尽量把age 看它的位次条件，如果带等号的，不管是大于小于等于都可以，只要带等号的尽量往前放。因为带等号的话，这个条件会被用来生成中间的临时索引片，或者叫中间的结果集。
lname字段的条件依然能够被用来生成中间的结果集，fname 字段能不能被用来生成结果集，要看 lname 的位次条件。首先看第一个的位次条件，如果它等值带等号，第二个字段肯定能够被用来生成中间的结果集，至于第三个能不能用，看第二个是否带等值条件，带等值条件的话，尽量往前放。归结起来就是第一个区分度越高的，尽量往前放，如果它不是被经常更新的；第二个带等值条件的尽量往前放。举个例子，如果这写的是不等于 d，那么 age 可以用来生成中间的索引片，lname 可以用来生成中间索引片，如果还有 fname，fname 就不能用来生成索引片了，只能用来做引擎下推的过滤，或者是 server层的过滤。通过已经存储好的索引生成中间的结果集，基本上是对 CPU 消耗非常低的，如果靠 cpu 做过滤，是非常耗 CPU 的，这是两种完全不同的访问方式。
如果前面全是等值条件，注意有 order by 排序，排序之前所有的索引前置的字段，等值的情况下，首先按年龄排，年龄排完按 last name 排，last name 排完按 first name 排。在这种情况下，如果 age 和 last name 全都是长值的话，那 fname 一定是顺序的。如果排序的字段前面全是等值条件，必须是等值，大于等于不行，小于等于也不行，rhythm 等值条件的情况下，直接通过索引来避免它的排序，不管是哪种排序方法，都是要花费 CPU 的，都是靠 CPU来做数据的顺序化，所以这种情况下是很耗 CPU 的，这是覆盖性索引避免排序的例子。
这是在日常开发或使用 RDS MySQL SQL 中数据库常用开发积累的一些开发使用的规范和建议，
可以根据自己的实际情况和真正的开发过程来参考这些建议。如果可以的话，遵循其中的这些规则，可以避免踩到很多使用过程中的坑，尤其是跟性能相关的，跟数据的安全性相关的，数据一致性相关的很多事情，可以节约很多开发运维的精力。
