开发者学堂课程【MySQL 实战进阶：MySQL 表和索引优化实战】学习笔记，与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/83/detail/1310
MySQL 表和索引优化实战
内容介绍：
一、主键索引
二、二级索引
三、数学分析
四、索引的作用
一、主键索引
本课程和大家分享 RDS for MySQL 表和索引的优化经验。
作为数据库的使用者来说，避免不了会接触两个概念，一个是表，一个是索引。在日常的思维中表是用来存储表中的数据，索引一般是用来加速查询访问。
RDS for MySQL 在 innodb 引擎下的数据真正物理组织，是怎么组织的？首先有这样一张表
它的主键是一个 mp 四个字节的整形的 id 作为主键，后面跟着一个单字符的clumn1，还有一个 int 类型的 column 2，单字符的 column 3。在c1字段上会有一个索引，就是一个很简单的一张表。
下面看它的数据是如何组织的。
首先说明一下在 innodb 引擎下实际上数据是存储在主键中的，数据是通过主键来物理组织的。它跟 airkong 本身默认的堆表不一样，airkong 本身默认创建的表如果不特定指定的话，是一个堆表，它真的有一个对象数据结构堆的数据结构来存储数据，同时主键是另外一个数据结构，是真的是这样两份的数据。对于 MySQL 下 innodb 的引擎，它本身的数据是存储在主键的叶子节点中的，column1、column2、column3这三列数据都存储在主键的叶子节点中。
主键本身从数据结构存储的数据结构上来看是 B+TRee，说明一下 B+TRee 本身是一个 b 树，b 指的是 balance TRee，就是平衡树，完整的名字叫做多路平衡树。多路平衡树本身是一个平衡树，而不是binary TRee，binary TRee 是一个二叉树，这是作为数据库 DBA 的基本功。多路平衡树和二叉树的区别在于二叉树只有左分支和右分支，而且不限定左分支和右分支的深度，也可以理解为树的高度，不限定左分支和右分支的高度必须一致的；多路平衡树首先是一个平衡树，
最上面的节点叫做根节点，从根节点到任何一个叶子节点，通走的节点数是要一致的，树高从任何一个维度来看，从任何一个叶子节点到根，从根到任何一个叶子节点必须得是一致的，就是左右是平衡的。多路的意思是每一个节点的分支节点也好，根节点也好，下面可以有多个子节点，而不只限定只有左节点、右节点。
同时在整个的结构里面，如果对 oracle 比较熟，oracle 有一个叫 block size，在 oracle 体系中对于每一个存储的基础来源叫做block。
在 MySQL 当中叫做叶，叫做配置，实际上是一样的概念。在 MySQL 里面，如果不特意指定的话，默认16KB 作为叶。如果从磁盘上访问一行数据，哪怕需要访问一个字节，也要这16 KB 的磁盘块，注意这是一个区的，不是真正的这个磁盘的，大小磁盘可能是4KB 的。四个4 KB 的组成一个16KB 的逻辑块，需要读16 KB 的数据到内存中。
这个结构里面，数据组织结构之后有几个关键的地方，第一个是数据是存储在主键中的，创建这张表的时候，最佳时间就是必须要显示的定义主键。
不显示定义主键的话，会出现两种情况，一种情况是在做数据传输的时候，没有办法决定这张数据是否重复，是否是唯一的，主键的定义是非空、唯一。另外一种情况是当 RDS for MySQL 的备份还原到线下的开源版本中，没有主键的表，读取的时候会发现字段对不上的情况。因为内部阿里的 sql ，实际上 RDS for MySQL 在 MySQL 的生态体系中是正式的阿里 sql，是一个 MySQL 的分支。对于这个分支来说，为了避免出现没有定义主键，导入了很多问题，如果这张表不显示定义主键，默认的会隐式的给它增加一个字段，这个字段对应用和用户来说都是不可见的，但是把物理备份还原到本地的时候，会发现访问这张表多出一个字段，这个隐式的字段会导致恢复的时候这张表不可访问。
所以最佳时间，既然是一棵树，数据要存储在树里面，存储在主键里面，就要显示定义主键。
这张表每个数据块的大小都是16 KB，这个分支节点也是16 KB，是不是这个分支节点下胯的节点数越多，这棵树可以越扁，就是树高可以越小。树高是从根节点到叶子节点中间走过多少配置，就是它的树高，树高跟性能相关。
日常访问的表，如果不是非常频繁访问的表，而且内存容量比较有限的情况下，根和分支节点都是在内存中的。但是需要访问数据块的话，需要从根访问到分支，再到叶子节点。
如果树高很高的话，而且内存很紧张，分支如果不在内存中的话，需要把分支节点先读进来，在到读叶子节点。数高越高，需要读的16 KB的块数就越多，也就是说，同样访问一行数据，需要比树高小的索引，要访问额外多的数据量，这种承载IO本身紧张的资源的情况下，就会导致查询更慢。因为访问数据花费的代价要比别人多，树高越高，访问叶子节点的代价就越大。这导致了在块的尺寸固定的情况下，如果里面可呆的条目数越多，下胯的节点数就越多，节点数越多，就可以让叶子节点数量在固定的情况下，已知的情况下，树高就越小，这个数据越扁平。
大家可以想一想，一个公司组织架构里面，如果中层领导越强，下面呆的部门越多，整个公司的组织结构会越扁平化。
第三件事情，在块的尺寸固定的情况下，这里面的条目数越多，树高越小，访问数据的代价就低。这取决于本身对主键的数据类型是有要求的，如果在 int 类型四个字节，big int 八个字节，如果这是一个个 ychar，但是它会保留固定的长度。如果使用一个字符串，是在 utf8 这个字符集的情况下，像column 1 这个类型，utf8 至少需要三个字节，utf8mb4 需要4个字节，所以最佳的时间就是主键尽量是 int 或者是 big int，最终整形。因为它本身很小，四个字节、八个字节，通常情况下，一个16 KB 的块能放几百个这样的条目。正常情况下，能放几百个条目，树高就很容易控制在三或者四，三是常见的，四已经不小了，三或者四这个水平量。或者是数据的分析，这是第三件事情。
第四件事情，它是一个平衡树。平衡树的要求就在于左边的分支，从根到任何一个叶子节点，树高都要固定。当这棵树不断的被修改，不断的增删改的情况下，这棵树实际上是在时刻变化的。为了保证根始终到树高是一致的，保证树跟着数据修改随时在变化，这个时候会带来一个问题，如果每次插入的数据都是在中间，而这棵树本身已经很大，一亿行记录了一张表，每次插入的数据都不是可预期的，都是随机的插到树的不同的地方。是不是经常需要把链表打开，把结构打开，到内存里把链表打开，然后把它重新组织成需要的合理的树，然后再拼在一起。这个对写的代价会非常大，尤其对于 insert 来说，写的代价非常大。
因为每次插入的数据都不是可预期的，不是朝着一个方向来变化的，每次都是随机的，就会导致写入的 rt 响应时间变得不可预期，总体指令会比较高。第四件事情就是主键除了数据类型有要求以外，建议用 or to improvement 正向递增，或者使用 SQL 字段，比如说有 RDS 或 polar x 的场景下，或者是 SQL 引擎的情况下，sql 或者or to improvement 保证它单向递增，保证每次写入或插入的操作性能比较一致，避免总要把一棵树拆开，再拼在一起。这是这张表在做表设计的时候，它本身是一个主键，根据主键存储的物理存储的数据结构，引出了四个。第一个是必须定义主键，第二个主键数据类型要尽量的小，第三个介绍了树高、16 KB 的块，第四个auto increment 要正向的去插。
二、二级索引
MySQL 的 key 跟 index 是同义词，除了主键以外的索引都称之为二级索引，看下图所示的索引
这个索引跟主键一样，也是 B+TRee 索引，它下面的每一个链表，跟叶子节点之间是双向链表，也是一颗多路平衡树。但是它跟 oracle 本身在设计上有一点不一样，c1 字段做了一个索引，但实际上在存储值的时候，因为数据是存储在主键中的，所以数据在停止的时候没有必要放数据的真正的物理地址，在 oracle 里放的是物理地址，但是在 MySQL 里面这里直接放的是主键的值，因为知道主键的值，就能唯一的定位到这行记录。实际上，虽然在这里放了c1，但是真正在存储的时候是把叶子节点值存起来的，反过来也就是说，主键的数据类型，导致了存储长度要尽量的小，如果主键真的很大的话，会出现问题。
补充一下，主键除了之前说的单向递增数据类型要小以外，比如说像 uuid 这种不建议做主键，因为太长了。如果字段太长的话，磁盘块里的方子会很小，数额会变得很臃肿。存储相同的数据量索引会占更大的空间，这对 IO 来说会产生很大的影响，相同硬件条件下比别人要慢，访问数据速度要慢，因为开销大，成本高。
