开发者学堂课程【MySQL 实战进阶：MySQL 表和索引优化实战】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/852/detail/14064
MySQL 表和索引优化实战
内容介绍：
一、主键索引
二、二级索引
三、数学分析
四、索引的作用
五、规范
作为数据库的使用者，每天都需要跟数据库打交道，会接触两个概念，一个是表，一个是索引，在日常的思维中表是用来存储的表中的数据，索引一般是用来加速查询访问。
实际上来看 RDS for MySQL 在 innodb 数据真正是如何物理组织的。
一、主键索引
首先有一张表，主键索引为 Table: (id int primary key, c1 char(1), c2 int, c3 char(1), key idx_ c1 (1);看数据是如何组织的，
首先，在 innodb 引擎上的数据是存储在主键中的，数据是通过主键来物理组织的，跟默认的堆表不一样，它真的有一个对象、物理结构、数据结构，以堆的结构存储数据，同时主键是另外的数据结构，它真的是两份数据。
对于 MySQL 架的 innodb 引擎，它本身的数据是存储在叶子节点，column1、column2、column3三列的数据都是存储在主键的叶子节点上的，主键本身从存储的叶子节点来看是一个 B+Tree，B+Tree 首先是一个 B 数，这个 B 指的是 Balance tree,就是平衡树，完整的名字叫做多路平衡树，而不是 Binary tree，就是二叉树。
多路的平衡树和二叉树之间的区别在于二叉树只有左分支和右分支，而且它不限定左分支的深度和右分支的深度，也可以理解为树的高度，它不限定左分支右分支的高度是必须一致的。
多路平衡树首先它是一个平衡树就要求从最上面的节点叫做根节点，从根节点到任何一个叶子节点，走的节点数是一定要一致的，树高从任何维度来看，从任何一个叶子节点到根，从根到任何叶子节点必须得是一致的，左右是平衡的，多路的意思是每一个节点分支节点、根节点，下面可以把多个叶子节点，而不限定只有左节点、右节点。同时，在整个结构里，如果接触数据库，oracle 有一个 block size，在 oracle体系中，对于每一个存储数据的基础单元叫一个块，叫 block，在 MySQL 中叫叶，叫做配置，实际上是一样的概念。
在 MySQL 里如果不特意去指定，默认都是16KB作为叶，如果读取一行从磁盘上访问一行数据，哪怕要访问一个字节，那么也需要把16KB 的一个磁盘块大小四板块，注意这是一个虚的，不是真正的大小磁盘可能4KB 的，四个4KB 的组成一个16KB 的逻辑块，需要读16KB 的数据到内存中。
数据组织结构里有几个关键的地方，第一个是数据存储在主键中的，创建这张表的时候最佳时间就是必须要显式地定义主键，因为不显示的定义主键会出现两种情况，一种情况就是在做数据传输的时候，没有办法决定这条数据是不是重复的，是不是唯一的，主键的定义是非空，唯一是作为主键的，另外，当把 RDS for MySQL 的备份还原到线下的数据的时候，会发现没有主键的表读取的时候字段对不上的情况，因为内部阿里的 SQL是 MySQL 的分支，对于这个分支，为了避免出现没有定义主键导致了很多问题，如果这张表不显示定义主键，默认的会给隐式的增加一个字段，这个字段对应用和用户都是不可见的，但是把物理备份还原到本地的时候，会发现访问这张表会多出一个字段，这是隐式的字段会导致恢复时这张表不可访问，所以最佳时间就是既然是一颗树，数据要存储在树里，存储在主键里，那么就要显示的定义主键。第二，这张表每个数据块的大小都是16KB，这个分支节点也是16KB，是不是分支节点上的节点数越多，这棵树可以越扁，就是树高可以越小，树高就是从根节点到叶子节点中间走过多少个这种配置，树高跟性能相关，日常访问的表，如果不是非常频繁访问的表，而且内存如果是容量比较有限的情况下，至少根和分支节都是在内存中的，但是要访问数据块需要从根访问到分支，然后再到叶子，如果树高很高而内存很紧张，分支没有在存中，需要把分支节点先读进来，然后再读到的叶子，树高越高，需要读的16KB 的块数就越多。比如同样访问一行据，需要比树高小的索引，要访问额外多的数据量，在io本身是紧张的资源的情况下，会导致产品会更慢，而访问的代价比别人的多，别人做两步，读两个16KB的块就找到据，你可能读5个这样的块才找到这行数据，树高越高，访问叶子节点的代价越大，导致在块的尺寸已经固定的情下，如果里面可带的条目数越多，那下挂的节点数也就越多，可以在叶子节点数量已固定的情况下，已知的情况下树高就越小，树越扁平。可以想一个公司组织架构里面，如果中层领导能力越强，下面带的部门越多，需要的整个组织结构，公司的组织结构会越扁平化，中间只有一层下带 N 多个部门，如果一个块下面只能带两个部门、甚至只能带个部门，这个组织架构必然是叠加的，这个树高要高的。第三，在块的尺寸一定的情况下，带的条目数越多，树高越小，访问数据的代价越低，取决于本身是不是对于组件的数据类型是有要求的。在 int 里四个字节，B 个 int 8个字节，如果使的是 char，是会保留固定的长度的，如果使的是字符串，在 utm8情况下，c1这样的类型至少要3个字节，utm8mb4需要4个字节，虽然最佳的实践就是主键尽量使 int 或者 B 个 int 这种整形，因为它本身很小，4个
字节、8个字节，通常情况下一个16KB 的块能放几百个条目，树高很容易控制在3或4这个水平上，3是常见的，4已经不小了。第四，平衡树的要求就在于左边的分支，这是从跟到任何一个叶子节点度都要一样，树高是固定值，在这棵树不断地被修改，不断的增、删、改的情况下，这棵树是时刻在变化的，为了保证根始终是到树高度一致，
这棵树根据数据修改随时在变化，会带来一个问题，如果每次比如插入的数据都是在中间，而这棵树本身已经很大，比如一行记录的一张表，每次插入的数据都不是可预期的、都是随机的，插到树的不同的地方，有时候经常需要把这些链表打开，把结构打开，读到内存里，把它简列表打开，然后把它重新组织成需要的合理的树，再把它再拼在一起，这个代价就会非常大，有时候对写的代价会非常大，尤其对于 insert 来说，写的代价非常大，因为每次插入的数据都不是可预期的，不是朝着一个方向变化的，每次都是随机，就会导致写入的 RT 响应时间第一不可预期，第二可能总体水平比较高。第四，主键除了数据类型有要求以外，建议用 out in equipment，正向递增，或者使用sequence，保证单向递增，保证每次的写入操作就是插入操作，它的性能是比较一致的，避免把一棵树拆开拼在一起，总出现这种操作。在做表设计的时候，它本身是一个主键，根据主键物理存储的数据结构，引出了4个，第一必须定义主键，第二主键数据类型尽量要小，第三介绍树高，第四 out in equipment 正向的插。
二、二级索引
MySQL 的 key 跟 index 是同义词，除了主键以外的索引，都称为二级索引。
Table: (id int primary key, c1 char(1), c2 int, c3 char(1), key idx_ cI (1);这个索引跟主键一样，也是一个 B+Tree。
下面的每一个链表就是叶子节点之间具有双向链表，所以叫 B+Tree，也是一棵多路平衡树，但是 oracle 本身在设计上有一点不一样，在对 C1字段做了一个索引，实际上在存储值的时候，因为的数据是存储在主键中的，数据在寻址的时候没有必要放数据的真正物理地址，在 oracle 这里放的是物理地址，在 MySQL 里面直接放的是主键的值，因为知道主键的值就能唯一的定位到这行记录，所以虽然在这里放的是 C1，但是真正的在存储的叶子节点中是把主键的值得存起来的，反过来就是主键的数据类型导致存储长度要尽量的保持要小，否则，如果主键很大会有问题。
主键除了单向递增、数据类型要小以外，像 UUID、MD5值就不建议用来做主键，因为长度太长，磁盘块会很小，树会变得很臃肿，存储相同数据量，索引会占很大的空间，对 IO 会产生很大的影响，比如相同硬件条件下比别人要慢，访问数据速度要慢，因为开销大、成本高。
三、数学分析
假设这张表里有一亿行记录，每一行16KB 的磁盘页配置能放的条目数叫平衡因子 Balance Factor 或者分支因子，16KB 分支节点的存储的索引字段的个数取两个比较极限的情况，一个是每一个16KB 的块里头只能放两条记录，类似于二叉树，第二个是每一个16KB 记录里能放100条记录。如果 int 或者 b 个int，正常情况下应该是几百，100取得比较小，B 个 int 八个字节，int 是四个字节。如果表不是真正存储数量特别大，B 个 int 肯定能够满足的，如果不出现某些特殊情况，最大值可以非常大。
树高是影响到物理 IO，如果数据都不在内存中的情况下，真正从磁盘获取一个数据到内存需要花费多少个物理 IO 访问一个数据块，数据库的增、删、改、查所有的操作都是发生在内存中的，磁盘实际上只做持久化，在将来的某一天，内存能支持持久化，可能整个从体系架构上就会有一个翻天覆地的变化。
树高有一个固定的计算公式，跟 B 和 N 相关的取对数，b 就是平衡因子，在 b 等于2的情况下，树高27，在 b 等于100的情况下，树高4，实际上正常情况应该是3左右，如果是 int 这种类型应该是3，从定位一行记录，这个开销就需要读27个16KB 的内存，那么这边只要读4个16KB 的块，这个是很明显的开销的差异，已经差不多快要七倍的差距了。
第二，最重要的还是存储尺寸，不在包括任何二级索引的情况下，只说主键，主键里头包含这个表的数据，它大概需要花费781个 GB 的存储容量，一般 RDS 内存正常情况下都是可能比较大的，最大的能到470，470个 GB 内存在70%到75%左右，用来缓存磁盘块的或者磁盘页。这个尺寸是用来缓存数据的，如果是一张很核心的表，15个 GB 的数据，对于一个470乘以75%或者乘以70%这样的尺寸是可以保证数据是完全缓存在内存中的，只要数据使用的足够频繁，表访问的足够频繁，那么基本上所有的数据都是缓存在内存中的，即便这张表选最大的规格，因为这里只考虑主键不考虑二级索引，在正常情况下对核心表来说是不可能的，也需要781个 GB，基本经常会需要出现物理 io 来换出数据，比如同样的存储两面的代价是完全不一样，性能表现是截然不同的，物理 io 是非常慢的。
四、索引的作用
介绍一个图书馆的模型，先说一个传统的套路，去图书馆里找一本书，正常的情况下需要一个目录，要进去查目录，目录告诉这本书在哪个书架上的哪个位置上，这样可以快速的走到那个书架上，把这个数据拿出来，就是把这本书找到，定位到我的数据，所以目录就相当于索引，它是从空间换时间，如果要存储目录得花一定代价，首先得组织好空间提前做准备来缓解访问的时间，这个模型里有几个点需要注意一下。首先，访问的只是其中很少的一个数据，可能几十亿的书库只借几本书，极小的一部分，那么目录是有用的。
反过来比如要搬家，进去之后要拿一半的书走，找不着目录，没有意义，50个架子拿走25个就好了，这种情况就是要访问的数据量需要获取的数据量占总数据量的一定比例之后，量变会引起质变。索引能提到访问速度，优化实际上是一个偷懒的艺术，就是能不干的尽量不干，干15个动作才能完成一件事和干一个动作就能完成一件事，效率必然是不一样的，所以真正的优化是在提高访问效率，要想清楚如何提高访问数据的效率。
先介绍两个概念，TR、TS。TR 是随机的访问一个16KB 的块，随机访问 random access、random read，随机的去读一个数据块花费的时间，顺序的去读一个16KB 的块需要时间，这两个基本上是1000倍的差距，这是在硬件条件下，它会有随机访问和数据访问，随机访问就是不知道下一次需要跳到哪去读这个数据块，数据访问的就是知道下一次读的是下一块，就这点区别。SSD 也分随机访问和数据访问。这张表如果忽略树高，假设数据都在内存里，第一件事是要读到树高，划一个随机读，因为不知道上一次读取到哪，下一次对根来说就是一个随机读，读到根，认为树高也是合理的，这种情况下，需要花费 n-1个顺序读把这张表编一下，谓词条件下建索引首先考虑谓词条件，where 后面的条件是什么，根据条件访问数据就要根据规则优化访问。age 上没有索引，就只有全表扫描，看一下不同条件下全表扫描的时间是多少。根据公式如果表里有90万行数据需要9秒钟，如果只有9千行数据100毫秒还可以接受，如果只有9行数据，随机读会占最大的部分，10毫秒。全表扫描不一定不是好事，取决于访问的目的是什么，还有当前的数据量是多少，如果表里没有什么数据，不用建索引，网上建议表创建后每个字段都要建索引，这不是一个好的建议，表里没有太大的数据量，没有必要访问索引。访问的谓词上没有索引，全表扫描来获取数据，全表扫描不一定不好，这个不好的代价是表里的数据量，随着数据量的上升，TS 访问的代价会快速的增加，导致时间快速增加，随着数据量增加，上线时间长了之后，表的数据量越来越多，发现查询越来越慢，可以在上面加一个索引。
在 age 上面加了一个索引，因为获取的数据量是其中很少的一部分，只有三行数据，首先需要扫这索引找到这三行之后根据主键回表，因为索引里没有 fname，需要的是 fname，而不是 age，age 是已知的，根据 age 只能找到主线，索引里没有 fname，必然有三个随机读，不能保证这三个主键值都是挨着的，有可能是不挨着的，一个随机读读到索引的根，再加三个顺序读，读到这三行数据，同时会有三个随机读会回表真正的 fname 三个值。公式就是四个随机读加上三个顺序读，成本就是40毫秒，只加了一个索引，就从90万行数据的情况下，从9秒钟的查询时间变成了索引扫描，效率提升了225倍。要在谓词条件对应的情况下建索引，有一个前提就是获取的数据量是占总表的数据量很小的一部分，获取的数据片占总数据量很小的一部分索引才是生效的。如果占50%，随机读会非常多，因为扫索引这个表里90万行数据，占一半，会有45万行随机读，是很惊人的时间。
不管是 oracle 还是 MySQL 优化器，它会考虑根据统计信息，统计信息描述一张表里存储了多少数据，每一种数据会大概估算多少，它会根据查询先去估一下访问的数据量大概会占总表的数据量的多少，oracle 根据版本不一样，比如超过20%、超过30%，超过一定限量的之后，如果查询获取的数据量超过了总表数据量的一定比例之后，就不能再走索引的访问形式了，因为如果走索引访问形式，会带来大量的回表访问，大量的回表访问会引起大量的随机读。随机读是1000的比例，40万行每个10毫秒，差不多4000多秒才能完成这个查询，一个多小时跑这个基本不可能接受，一般 TP 类型的业务要求都是秒级或者毫秒级返回。
归结几点，第一谓词条件上尽量建索引加速访问，减少 CPU，跟逻辑读有关系，为了从时间来衡量，就是减少访问时间、查询的处理时间，要保证建索引，索引前提下是访问的数据要占总数据量很小的一部分。这张表之前里头最大的开销在四个随机读上面，四个随机读来源是回表了三个随机读，可以把这三个随机读干掉，进一步优化，因为产生这三个随机读的原因是随机读里没有 fname，可以把 fname 放到索引里头去。
age 建完之后，加一个字段，索引里包括 fname，这时访问就会变成一个随机读访问到根，然后三个顺序读找到这三个数据，没必要回表，因为索引里就有 fname，通过扫索引的这三行数据就满足了查询，公式就变成一个随机读加三个顺序读，出来就是10毫秒，这个索引叫覆盖性索引，能够完全满足查询，不用回表的索引就叫覆盖性索引，对于查询是最高效的访问形式，从9秒钟直接优化到10毫秒，900倍的性能提升。
什么样的查询建覆盖性索引，最核心的表、最频繁的查询，比如表上30%以上都是这类查询，前面带的字段也差不多，带来这种查询，就建这种覆盖性的索引。
为什么网上好多建议不能 select*，或者把表里的所有字段列在这，开发时框架最容易干的一件事情，就是要么放 select*，要么把所有字段都列在上面，会导致查询没有办法建覆盖性索引，因为如果把所有的都列在这，写入的开销非常大，覆盖性索引对应的字段只要一改，对面的索引还是要跟着变的，这种写入的材料会非常大。所以第一个不能 select*或者所有的字段
第二个有一个最左原则，匹配的时候 age 在最前面，如果后面有多个字段，首先会根据最左边字段去找，有一个原则是建这种组合索引的时候，要把区分度越高的字段要放在最前面，区分度越高获取的中间索引片越小、越薄。只有选择很小量数据的时候，索引才是高效的，反推过来，中间生成的结果集或者索引片中间越薄，包含的越小，这个索引对查询的加速是越高的，越适合这个查询。
也就是把区分度越放前面，比如性别，性别区分度就太低了，放在前面之后，一次只能过滤出50%的数据，过滤出来结果集中间的索引就太厚了，是50%的数据。
第二，最左要求能匹配到的这个位置条件不能出现在第二个，最左原则就是在匹配索引跟查询的谓词的时候，按照从左到右来匹配，如果已经有查询，在建组合索引的时候，一定要把这几个条件里区分度最高的放在最左边，还要考虑字段经常会变，放在第一位，会导致这个数会经常掰开，这种情况下，对 update 语句是不友好的，会影响 update 的 RT 响应时间，如果还有跟区分度类似的字段，尽量往前放，经常被更新的字段尽量往后放。
第三个原则，如果相同的区分度都差不多，怎么考虑建组合索引？尽量把 age 看成谓词条件，如果是等值的，就带等号的，不管是大于等于、小于等于，只要带等号的尽量往前放，如果带着等号，这个条件会被用来生成中间的临时索引片，或者叫中间的结果集，它后面的条件依然能被用来生成中间的结果集，那么这个条件能不能被用来生成结果集需要看它的谓词条件了，首先看第一个的谓词条件，如果是等值带等号，那么第二个字段肯定能被用来生成中间结果集，至于第三个能不能被用来，看第二个是不是带等值条件，如果带等值条件，尽量往前放。
归结起来就是，首先区分度越高的谓词条件尽量往前放，如果不是被经常被更新的，第二个带等值条件的尽量往前放，举个例子，如果不等于 d，那么 age 可以用来生成中间结果集，lname 也能用来生成中间索引片，但是 fname 就不能用来生成索引片，只能用来做引擎下推的过滤，或者是 sever 层的过滤，都是要花 cpu 的，通过已经存储好的索引去生成中间结果集时，基本上对 cpu 消耗是非常低的，但如果靠 cpu 做过滤生成结果集是非常花的 cpu 的，这两种完全不同的访问方式。
而且还有一个，就是如果前面全是等值条件，这里有 order by,有排序，如果排序之前所有的索引前置的字段都是等值的情况下，索引首先按 age 排，按年龄排，然后按 lname、fname 排，，这种情况下，如果是 age 和 lname 都是长值，那 fname 一定是顺序的，如果前面排序的字段全是等值条件，就可以直接通过索引来避免它的排序，排序不管是哪种方法都是要花费 cpu 的，都是要求 cpu 来做数据的数据化。
这是一个覆盖索引并且避免排序的例子，看一下，分析一下。
五、规范
在日常开发和使用 RDS for MySQL 数据库的日常运维开发的过程中积累的一些开发和使用的规范、使用的建议，可以根据实际情况和真正的开发过程参考这些建议，并不是说一定要这样，但是如果可以，遵循其中的这些规则，会避免踩到很多使用过程中的坑，尤其是跟性能相关的、跟数据的安全性相关的、跟数据一致性相关的，可以避免掉坑里，可以节约很多开发运维的精力。
