MySQL之B+树与索引

B树

1、B树的定义

简记为m、m/2、2、有序
B树也称B-树，它是一颗多路平衡查找树，用于数据库索引的数据结构，一颗m阶的B树有如下性质：

  1. 每个结点最多m颗子树（最多 m-1个关键字）。
  2. 非根结点最少有Math.ceil(m/2)颗子树（向上取整）（最少有 Math.ceil(m/2) - 1 个关键字）。
  3. 根结点可以只有2颗子树（1个关键字）。
    • 如果插入导致当前的根结点发生了分裂，则新的根结点有左右2颗子树，key的数目是1，左右孩子的key的数目是Math.ceil(m/2) - 1。
  4. 每个结点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
  5. 所有叶子结点都位于同一层，或者说所有节点的左子树深度和右子树深度相同，平衡度为0。

在实际应用中的B树的阶数m都非常大（通常大于100），所以即使存储大量的数据，B树的高度仍然比较小，一般在2-4。

2、m阶B树的插入

  1. 插入一定是在叶子结点插入
  2. 插入后，判断当前结点key的个数是否 <= m-1，若满足则结束，否则进行第3步。
  3. 此时当前节点key的个数是m，以结点中间的key为中心分裂成左右两部分，然后将中间的key移动到父结点中。父节点中，中间key的左右子树分别指向分裂后的左半部分和右半部分，继续对父节点进行第3步。
    • 如果m是奇数，则左右子部分的节点数都为 (m - 1) / 2，符合 非根结点最少有 Math.ceil(m/2) - 1 个关键字 的条件。如果m是偶数，可以选择中间的两个key中的任意一个key，此时两个子部分的节点数分别是 m/2 和 m/2 - 1，符合 非根结点最少有 Math.ceil(m/2) - 1 个关键字 的条件。

插入会先导致某个叶子结点数目逐渐增加到最大值（m - 1），然后叶子结点会分裂为两个包含最少数目（Math.ceil(m/2) - 1 ）的节点，并导致父节点数目增加1，最终导致根节点达到最大值而分裂，根节点分裂后，树的高度加1，新的根结点的数目是1，新的根结点的左右孩子的数目是Math.ceil(m/2) - 1，然后新的根结点的key数目会从1开始逐渐增大到最大值m - 1。

可以证明，插入如果发生节点分裂，整颗树仍然满足所有节点的左子树深度和右子树深度相同，平衡度为0。

B树插入的空间使用率低问题：
对于m阶的B树，插入的时候，如果节点分裂或者需要新增节点，每个节点预先申请的存储空间一般都是 m - 1个关键字大小，但这个节点的存储空间分配以后，可能永远都不会用满。
比如我们按照1、2、3 ... N - 1、N的顺序往B树中插入，大部分节点的存储空间使用率只有50%，最差情况下整个B树存储使用率仅为50%。

3、m阶B树的删除

  1. 如果要删除的key位于非叶子结点上，则用后继key（当前key右边的指针指向的子树中key最小的，由于任何一个节点的左右两边都有指针，因此后继key一定存在，且位于叶子结点）覆盖要删除的key，然后将此问题转化成删除叶子结点的key，进入步骤2处理。
  2. key删除后，如果剩下的key个数大于等于Math.ceil(m/2) - 1，结束删除操作，否则进入步骤3。
  3. 如果左兄弟或右兄弟中存在key个数大于Math.ceil(m/2) - 1，则父结点中的key下移到该结点，兄弟结点中的一个key上移，删除操作结束。
  4. 如果左兄弟和右兄弟的key个数都等于Math.ceil(m/2) - 1，则选择任意一个兄弟节点，将父结点中的key下移与当前结点及它的兄弟结点中的key合并，形成一个新的结点。原父结点中的key的左右指针就变成了一个指针，指向这个新结点。
    • 如果出现合并，说明当前节点的key个数是Math.ceil(m/2) - 2，合并的兄弟节点的key个数是Math.ceil(m/2) - 1，加上父节点中的一个key，合并出来的新节点的key个数是 2 * Math.ceil(m/2) - 2，如果m是奇数，2 * Math.ceil(m/2) - 2 = m - 1，如果m是偶数 2 * Math.ceil(m/2) - 2 = m - 2，均小于规定的m-1个关键字。

删除会导致某个叶子结点数目逐渐减少到最小值 Math.ceil(m/2) - 1 ，然后导致其左右兄弟节点的数目也逐渐减少到最小值 Math.ceil(m/2) - 1 ，触发合并后，导致其父节点的数目减少1个，同时生成一个具有最大节点数m - 1的新的节点。最终会导致根节点的key数目减少到1，根节点的孩子减少到2，然后根节点与其左右两个孩子节点一起合并为一个具有最大节点数m - 1的新的节点根节点，树的高度减1。

B+树

1、B+树定义

B+树有两种定义方式，第一种定义方式和B树基本等价，即关键字个数比孩子结点个数小1。另一种定义方式是关键字个数和孩子结点个数相同（MySQL用的这种方式，索引结点的行记录中存储的是page number）。B+树的定义是：

  1. 每个结点最多m颗子树，最多有 m-1（或m）个关键字。
  2. 非根结点最少有Math.ceil(m/2)颗子树（向上取整），最少有 Math.ceil(m/2) - 1（或Math.ceil(m/2) ）个关键字。
    • innoDB中，叶子结点最少可以有两行数据
  3. 根结点可以只有2颗子树，1个（或2个）关键字。
    • 如果插入导致当前的根结点发生了分裂，则新的根结点有左右2颗子树，左右孩子各自包含Math.ceil(m/2)颗子树。
  4. 每个结点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于等于它，对于第二种定义方式：设key[i]为内部节点中的关键字，child[i]为该关键字对应的子树中的任意一个关键字，那么有 key[1] ≤ childNum[1] < key[2] ≤ childNum[2] < key[3] ≤ childNum[3] < … < key[n] ≤ childNum[n] 或者是 childNum[1] < key[1] ≤ childNum[2] < key[2] ≤ childNum[3] < … ≤ childNum[n] <key[n]（注意<与 ≤ 必须交替出现，不能全是 ≤ ）
  5. 内部节点只是索引，不包含数据，所有数据都在叶子结点，所有叶子结点都位于同一层且构成双向链表，每个叶子结点拥有前后指针。
    • 这个特性导致B+树相比B树更利于顺序查找和范围查询，B树必须用中序遍历的方法进行范围查询，B+树上一般有两个指针，一个指向根节点，一个指向最小的叶子结点。
    • B+树的所有随机查找都是从根节点查找到叶子结点

下面是两种类型的B+树

2、m阶B+树的插入（关键字个数比孩子结点个数小1）

  1. 插入一定是在叶子结点插入
  2. 插入后，判断当前结点key的个数是否 <= m-1，若满足则结束，否则进行第3步或第4步。
  3. 如果是叶子节点，此时当前节点key的个数是m，将当前节点拆分成相等的左右两个部分，然后将右边部分的第一个key复制到父结点中（父节点中新增的key仍然存在于右孩子中）。父节点中，新增key的左右子树分别指向分裂后的左半部分和右半部分，继续对父节点进行第3步。
    • 如果m是奇数，则左部分的节点数可以是 Math.ceil(m/2) 和 Math.ceil(m/2) - 1，右部分包含剩下的节点，左右部分都符合 非根结点最少有 Math.ceil(m/2) - 1 个关键字 的条件。如果m是偶数，左右部分的节点数都是 m/2，符合 非根结点最少有 Math.ceil(m/2) - 1 个关键字 的条件。
  4. 如果是索引节点，此时当前节点key的个数是m，此时的分裂方法和B树类似，以结点中间的key为中心分裂成左右两部分，然后将中间的key移动到父结点中（中间的key不会再存在于左右孩子中）。父节点中，中间key的左右子树分别指向分裂后的左半部分和右半部分，继续对父节点进行第3步。
    • 如果m是奇数，则左右子部分的节点数都为 (m - 1) / 2，符合 非根结点最少有 Math.ceil(m/2) - 1 个关键字 的条件。如果m是偶数，可以选择中间的两个key中的任意一个key，此时两个子部分的节点数分别是 m/2 和 m/2 - 1，符合 非根结点最少有 Math.ceil(m/2) - 1 个关键字 的条件。

插入会先导致某个叶子结点数目逐渐增加到最大值（m - 1），然后叶子结点会分裂为两个包含最少数目（Math.ceil(m/2) - 1 ）的节点，并导致父节点数目增加1，最终导致根节点达到最大值而分裂，根节点分裂后，树的高度加1，新的根结点的key数目是1，新的根结点的左右孩子的数目是Math.ceil(m/2) - 1，然后新的根结点的key数目会从1开始逐渐增大到最大值m - 1并继续分裂。

可以证明，插入如果发生节点分裂，整颗树仍然满足所有节点的左子树深度和右子树深度相同，平衡度为0。

B+树也存在类似B树的空间使用率低问题

3、m阶B+树的删除（关键字个数比孩子结点个数小1）

  1. 不像B树，B+树的删除一定在叶子结点上
  2. key删除后，如果剩下的key个数大于等于Math.ceil(m/2) - 1，结束删除操作，否则进入步骤3。
  3. 如果左兄弟或右兄弟中存在key个数大于Math.ceil(m/2) - 1，对于索引结点，父结点中的key下移到该结点，兄弟结点中的一个key上移，删除操作结束，对于叶子结点，将兄弟节点中的key直接移动到当前节点，并更新索引结点。
  4. 如果左兄弟和右兄弟的key个数都等于Math.ceil(m/2) - 1，则选择任意一个兄弟节点，对于索引节点，将父结点中的key下移与当前结点及它的兄弟结点中的key合并，形成一个新的结点。对于叶子结点，将当前结点和兄弟结点合并成一个新的节点，删除父节点中的key。原父结点中的key的左右指针就变成了一个指针，指向这个新结点。
    • 如果出现合并，说明当前节点的key个数是Math.ceil(m/2) - 2，合并的兄弟节点的key个数是Math.ceil(m/2) - 1，索引节点会再加上父节点中的一个key，合并出来的新节点的key个数是 2 * Math.ceil(m/2) - 2，如果m是奇数，2 * Math.ceil(m/2) - 2 = m - 1，如果m是偶数 2 * Math.ceil(m/2) - 2 = m - 2，均小于规定的m-1个关键字。如果合并的是叶子结点，合并出来的新节点的key个数是 2 * Math.ceil(m/2) - 3，同样小于规定的m-1个关键字

删除会导致某个叶子结点数目逐渐减少到最小值 Math.ceil(m/2) - 1 ，然后导致其左右兄弟节点的数目也逐渐减少到最小值 Math.ceil(m/2) - 1 ，触发合并后，导致其父节点的数目减少1个，同时生成一个具有最大节点数m - 1的新的节点。最终会导致根节点的key数目减少到1，根节点的孩子减少到2，然后根节点与其左右两个孩子节点一起合并为一个具有最大节点数m - 1的新的节点根节点，树的高度减1。

注意，通过B+树的删除操作后，索引结点中存在的key，不一定在叶子结点中存在对应的记录。因为索引非叶子结点不是实时更新的，只有叶子结点数据是最准确的，因此查找数据时必须读取到叶子结点，比如找最小值的时候，根结点中的最小值可能并不存在。

B树和B+树总结

1、标准的插入没有类似删除那样的旋转操作，但其实插入也可以用旋转来优化，插入后如果当前节点超过最大值，检查左右兄弟节点中是否有小于最大值的，如果有，对于B树，将父节点中的一个key下移到兄弟节点中，将当前节点中的一个key上移到父节点。对于B+树，将当前节点中的一个key直接移动到兄弟节点，并更新父节点中的索引。这种方式可以优化插入导致的空间使用率低问题，同时可以减少节点的拆分操作（拆分涉及到较多磁盘IO，很耗时），降低树的高度的增长速度。另外，插入如果产生分裂，新申请的页和当前页在物理上极有可能不连续，这会导致B+树逻辑上的顺序查找变成物理上的随机查找。
2、分裂会将一个最大的节点分裂成两个最小的节点，并导致父节点个数加1，如果导致树的高度增加，那新的根节点个数就是1。B+树索引结点和叶子结点的分裂有区别。
3、删除会将两个最小的节点合并成一个最大的节点，并导致父节点个数减1，如果导致树的高度减少，那新的根节点个数就是最大值。

MySQL为什么使用 B+ 树来作索引？

  • 红黑树不适合做索引的原因：红黑树定义：根黑叶黑红生黑，黑黑相等。红黑树保证的是黑色节点相等，是弱平衡树，如果插入的数据是排好序的，则只会单边增长，树的高度较大，最小值的高度和最大值的高度相差接近2倍，查询效率依然不高效。
  • Hash表不适合做索引原因：哈希表对于范围查找和排序效率低，但对于单个数据的等值查询效率很高。
  • 有序数组适合等值查询（可以二分查找）和范围查询，但插入修改效率低，需要移动大量数据，保证物理有序。
  • B+树相对于B树的优势，也是B+树相比于二叉平衡查找树的优势：
  1. 内部索引节点不存储数据，存储的元素更多，使得树的高度更小，查询的IO次数更少，B+树更加矮胖。
  2. 所有的查询都要查找到叶子节点，查询性能是稳定的，而B树，每个节点都可以查找到数据，所以不稳定。
  3. 叶子节点形成了一个有序链表，更加便于范围查找和全节点扫描。中序遍历二叉搜索树每个结点需要遍历两次，对叶子结点链表顺序遍历每个结点只遍历一次，随机IO更少。如果逻辑上相邻的行在物理上也相邻（顺序插入），那范围查找在磁盘上就是顺序IO而不是随机IO。
    参考 http://blog.xuanweiyao.com/archives/mysqlwei-shen-me-shi-yong-bshu-lai-zuo-suo-yin--dui-bi-bshu-ta-de-you-dian-he-que-dian-shi-shen-me-#为什么红黑树不适合做索引

参考
https://www.cnblogs.com/nullzx/p/8729425.html
https://www.jianshu.com/p/6f68d3c118d6

MySQL中B+树

对于非叶子节点页的记录，聚集索引的记录包含其子节点page中的最小主键值及page no；二级索引记录包含的最小记录键值由二级索引键值和聚集索引键值构成（二级索引按照二级索引键值 + 聚簇索引值进行排序），二级索引记录也包含page no，这说明MySQL的B+树的索引结点中，每个节点都有一颗子树 http://mysql.taobao.org/monthly/2016/02/01/

  • B+树索引在数据库中是高扇出性，树的高度一般在2-4，索引节点和叶子结点的大小都是数据页的大小，每个页默认16KB，每读一个索引节点和叶子结点就有一次磁盘IO，读入索引节点和叶子结点的页后，在内存中先使用page directory进行二分查找，然后利用行记录头中的n_owned和next_record指针进行线性查找，找到需要的记录。
  • 聚簇索引的叶子结点的存储在物理上不是连续的（随机插入导致的），体现在数据页之间通过双向链表连接，每个页中的行记录是通过单向链表维护的。这种物理存储的乱序会导致很多随机查找，影响性能。可以通过optimize table 或者导出再导入的方式重新整理数据，清除数据碎片，让数据存储在物理上是顺序的。
  • 每张表只能有一个聚簇索引，聚簇索引的叶子结点存储了整行数据，辅助索引的叶子结点存储了索引列的值和不在索引列中的剩下主键列的值，通过辅助索引找到主键后，再通过聚簇索引找到具体的行数据

行记录格式

innoDB默认有一个系统共享表空间，如果用户启用了innodb_file_per_table，则每张表的数据单独放到一个表空间文件中。每个独立表空间都对应磁盘上的一个物理文件，命名形式为{table_name}.ibd，物理文件按page切分，建议将innodb_file_per_table设置为ON，因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。
http://mysql.taobao.org/monthly/2019/10/01/
http://mysql.taobao.org/monthly/2018/04/03/

1、compact行记录格式

1、建表时，如果某列可能为NULL（未指定NOT NULL），则在NULL标志位中有一个bit表示该列在当前行是否为NULL，如果为NULL，则变长字段长度列表和后面的列数据中不会再有这个列的数据，所以如果某列的值不为NULL，最好声明为NOT NULL
2、针对VARCHAR这种变长字段，会在变长字段长度列表中有1到2个字节表示其长度，好处是节约存储空间，坏处是字段变长时可能引起叶子结点分裂。

3、记录头结束后，是主键字段，主键可以是多列组成的联合主键（PRIMARY KEY(a, b)），如果建表时没有显式指定主键，则选取表中第一个非空的唯一索引（UNIQUE NOT NULL）所在的列的值作为主键，如果没有非空唯一索引，每行自动创建一个6字节rowId表示主键。如果有主键，则这个位置放主键数据，长度是主键长度，主键的数据将不会出现在后面的列数据中，如果没有主键，这个位置放rowId。

InnoDB维护了一个全局的dict_sys.row_id值，所有无主键的InnoDB表，每插入一行数据，都将当前的dict_sys.row_id值作为要插入数据的row_id，然后把dict_sys.row_id的值加1。row_id的范围是丛 0 开始到 2^48 - 1。达到上限后，下一个值就是0，然后继续循环。插入数据申请到row_id=N后，就将这行数据写入表中；如果表中已经存在row_id=N的行，新写入的行就会覆盖原有的行，这样会发生数据丢失。因此建议InnoDB表最好主动创建自增主键。因为，表自增id到达上限，再申请时自增id保持不变，这样插入数据时报主键重复错误，是更能被接受的。

4、主键结束后是6字节的事务ID字段(transactionID)和7字节的回滚指针字段（Roll pointer），这两个字段只在聚簇索引中出现，二级索引中没有http://mysql.taobao.org/monthly/2015/04/01/

2、redundant行记录格式

compact相比redundant更节约空间，但更耗费CPU，compact和redundant的区别：

  1. redundant行首部记录了所有列（不论是否为NULL，是否为变长字段）的长度，包括隐藏列rowID, transID, roll pointer的长度也会记录，而compact只会记录非NULL的变长列的长度。
  2. redundant没有NULL标志位，定长字段如果为NULL，占用的空间不会减少，变长字段如果为NULL，数据部分不占用空间，但还是需要一个字节存储长度。
  3. CHAR(N)的优化 , N代表字符最大数量
redundant compact
CHAR(N)，使用定长字符集(latin) 定长存储，长度是N * 字符宽度，如果超过768字节变长存储，off-page存储 定长存储，长度是N * 字符宽度，如果超过768字节变长存储，off-page存储
CHAR(N)，使用变长字符集(utf8mb3, GBK) 定长存储，长度是N * 最大字符宽度，如果超过768字节变长存储，off-page存储 变长存储，有单独的长度字段，如果超过768字节off-page存储

3、行溢出数据

对于compact和redundant格式，如果定长列长度超过768字节，也可能行溢出。

建表时，MySQL对行的限制是不能超过65535字节，VARCAHR会用可能的最大长度来计算，注意VARCHAR(N)中的N表示字符的个数，如果单个字符最多3个字节，需要保证 3 * N < 65532（由于每行都有元数据信息，所以VARCHAR实际不能存16KB）。但对于BLOB和TEXT，只计算为 9 至 12 个字节。innoDB的限制是不能超过8126字节(大约page size的一半)，innoDB对于VARCHAR的计算和MySQL不一样，VARCHAR最多计算为768+20字节

innoDB页最少存放2行数据，最多存放 16KB/2-200 = 7992行。

  • 行溢出的判断标准：某行存储的数据总和超过了大约8126字节，进行行溢出，否则不会行溢出。https://dev.mysql.com/blog-archive/externally-stored-fields-in-innodb/
  • 发生行溢出后，从长度最长的变长字段开始选择，直到该行存储的数据小于8126字节，列的长度小于768字节的，不会被选择为行溢出存储，超过768字节的，可能会被选择为行溢出存储。
  • 当某个列被选为行溢出存储后，如果是定长列（不仅仅是CHAR(N)），会采用变长格式进行编码（record header中有单独的长度字段表示长度），将变长字段长度列表中表示该列的长度中的外部存储flag设为1，将该变长的列的前768字节和20字节的指针存在叶子结点中(因此变长列的长度一般是768+20)，其余数据存储在一个专门的overflow page中。
  • 发生行溢出后为什么还要将一部分prefix(768字节)存在行记录中？
    这是为了新建二级索引时可以不用去off_page页取溢出的数据，因为二级索引需要通过key的值计算索引。
    如果是单字段索引，则字段长度不应超过 767 字节。如果是联合索引，则每个字段长度都不应超过767 字节，且所有字段长度合计不应超过 3072 字节。

4、compact格式下，使用变长字符集的CHAR(N)的优化

  • A minimum of N bytes is reserved for CHAR(N). N最大255，保留至少N字节的原因是在节约空间的前提下尽量保证修改时不产生页分裂。
  • 插入时，尾部的空格会先被全部去掉，如果去掉以后不足N字节，用空格(0x20)填充至N字节，查询时，尾部的空格也会被去掉。
  • 变长存储，在变长字段长度列表中，有单独的字段表示长度
  • 对于VARCHAR，插入时尾部的空格不会被去掉，不会使用空格填充，查询时尾部的空格不会被去掉。但修改VARCHAR的值很可能会造成页分裂或者产生空间碎片
  • 变长存储节约空间。定长存储性能更好，当字段长度变大时可以不用移动数据，不会导致页分裂。

5、Compressed 和 Dynamic格式

如果行数据超过阈值(8K左右)，将需要溢出的列全部放到off_page页，页内只有一个20字节的指针。如果行数据没有超过阈值，还是in-page存储。

compressed格式会使用zlib算法进行压缩

页结构

数据页和索引页，页的大小都是一样的，默认16KB，但数据页和索引页的格式会不一样

1、File Header

2、Page Header

  • PAGE_N_DIR_SLOTS : Page directory中的slot个数。一个新建的空数据页，会有supreme 和 infimum 两个行记录(这两种行记录只有一列，类型是char(8)，分别存储supreme、infimum两个字符串的值)，会有2个slot，分别指向最大记录和最小记录。在一个非空的数据页中，第一个目录永远指向最小记录，最后一个目录永远指向最大记录。当增加目录的时候，会递增这个值，通过这个字段可以计算出Page Directory的起始位置。
  • PAGE_HEAP_TOP : 这个指向数据页中的空闲空间的起始地址。大于这个地址的且小于数据目录的空间都是未分配的，可以被后续使用。但是由于空闲记录链表(PAGE_FREE)的存在，小于这个地址的也可能被重用。
  • PAGE_FREE : 删除记录的链表。innodb中为了加快删除操作，事务删除时只是将行首部中的deleted_bit设为删除（逻辑删除，链表的指针都没有修改，这样事务提交时flush的数据很少，page header中有个字段记录deleted_bit为1的记录数量），目的是事务可以快速回滚，或者新插入数据的唯一键(主键)的值在逻辑删除记录的唯一键值所在的前后区间中（主键1、4、7，删除4后，插入1-7可以复用） 时可以重用。purge线程周期性清理（物理删除，同时修改链表指针）后移入page_free链表。如果这个页上有记录要插入，需要先找到插入位置，然后看是否有deleted_bit为1的相同主键的记录空间可以重用，否则从page_free链表分配空间，仅仅比较page_free链表头的第一个记录，如果这个记录的空间大于需要插入的记录的空间，则复用这块空间，否则从空闲地址(PAGE_HEAP_TOP)分配，如果空间还是不够，会做一次page内的重整理，对碎片空间进行合并。
  • Page directory：为了加快页内的数据查找，会按照记录的主键顺序，每隔4~8(行记录header中的n_owned)个记录分配一个slot，每个slot占用2个字节，存储记录的页内偏移量（没有在page directory区域存储主键的值，只有指针），可以理解为在页内构建的一个很小的索引(sparse index)来实现二分查找。infimum的n_owned总是1，supremum的n_owned是1-8，其他记录的n_owned是4-8。n_owned含义:当该值为非0时，表示当前记录占用page directory里一个slot，对于记录N，这个记录管理记录N-1(不包括)和记录N(包括)之间的记录，我们称记录N own 这些记录。
  • PAGE_N_DIRECTION，PAGE_LAST_INSERT，PAGE_DIRECTION：插入需要先查找到插入位置。正常的查找方式是根据Page Directory二分查找，再顺序查找。InnoDB针对主键顺序插入的场景做了优化。因为如果按照主键顺序插入的话，能保证每次都插入在这个数据页的最后，所以只需要直接把插入位置定位在数据页的最后(PAGE_LAST_INSERT)就可以了。另外当插入导致页满需要分裂的时候，在顺序插入的场景下，比如插入1、2、3、4、5、6、7，8，9，... 100。插入7后需要分裂（假设页最多6个记录），如果从4的位置分裂，则1、2、3所在的页的空间会有一半的处于浪费，虽然之前讲到一种插入分裂时旋转的优化方式，将当前需要分裂的页的一个记录调整到相邻的未满的页中，但这样还是会有很多磁盘IO。innoDB的优化算法会判断当前 PAGE_DIRECTION 是否大于5，如果大于5说明是顺序插入，然后从当前查找到的插入点位置分裂，在这个例子中，插入7后会从7的位置开始分裂，前面1、2、3、4、5、6会在同一页中，避免了空间浪费。

3、File Trailer

File Trailer只有一个FIL_PAGE_END_LSN部分，占用8字节，前4字节是该页的checksum，后4字节和File Header中的FIL_PAGE_LSN（但File Header中这个字段是8字节）相同。将这两个值与File Header中的FIL_PAGE_SPACE_OR_CHECKSUM（4字节） 和 FIL_PAGE_LSN进行比较，看是否一致（checksum的比较需要通过innoDB的checksum函数来进行，不是简单的等值比较），以此来检测页是否已经完整地写入磁盘，防止写入过程中可能发生的磁盘损坏，机器关机等情况，保证页的完整性。

innoDB索引

1、查看索引的统计信息

create table `t` (
  `a` int not null,
  `b` varchar(8000),
  `c` int not null,
  primary key (`a`)
) ENGINE=InnoDB;

alter table t add index  idx_b (b(100));
alter table t add index  idx_a_c (a, c);

可以看到共有3个索引，分别是主键索引，指定前缀的辅助索引，联合辅助索引

其中有个统计参数 cardinality：表示索引中不重复的值的数目（放入set去重后，set的size）， 区分度 = cardinality / 行数 应该接近1，才表示这个索引有创建的必要。这个值并不是每次表中数据有修改就会更新，而是会统计表中数据的修改次数，大于一定阈值后才会更新，更新时只会随机找8个叶子结点，统计其中不重复的值之和（distinct_sum），cardinality = distinct_sum / 8 * 叶子结点总数（distinct_sum / 8 表示平均每个叶子结点中的不重复的数目）。执行 analyze table，show index等命令时会重新计算这个值，但由于是抽样，所以每次计算出来的值可能不一样。

mysql辅助索引的创建和删除会对整个表加读锁，意味着索引创建的过程中，其他的写操作无法执行。Online DDL对这个问题进行了优化，在创建索引的过程中不对表加读锁，使用缓存将写操作日志记录下来，等索引创建结束后再重放写操作日志。与redis的AOF类似，AOF fork出进程重写AOF文件时，会使用缓存记录写命令，然后再重放缓存中的命令。
MySQL之重建表与Online DDL

2、索引的使用

create table `t` (
  `pKey1` int not null,
  `pKey2` int not null,
  `a` int,
  `b` int,
  `c` char(10),
  primary key (`pKey1`, `pKey2`),
  key idx_a_b_c (`a`, `b`, `c`)
) ENGINE=InnoDB;
联合索引

MySQL只能从左开始依次使用联合索引，可以使用联合索引 idx_a_b_c 的语句：

  • select * from t where a = 1 order by b
    这条语句没有使用联合索引中列c的信息，如果此时有另外一个联合索引 key idx_a_b (a, b)， 则会优先用这个索引，因为idx_a_b的索引中每行的数据更少，每页包含的行数更多，查找更快
  • select * from t where b = 2 and d = 4 order by a, c, pKey1, pKey2
    等值条件可以和order by中的列构成索引的最左前缀，这样可以使用索引避免额外的排序。注意b = 2 and d = 4只能用作过滤条件，没法直接使用索引查询到b = 2 and d = 4 的记录，还是需要全索引扫描。
    辅助索引中的主键也是排序的
  • select * from t where a = 1 and b = 2 and c like 'abc%'
  • select a, b, count(*) from t group by a, b;
  • select * from t where a = 1 and ( b >= 2 and b <= 4 ) and c = 8 或者 select * from t where a = 1 and ( b >= 2 and b <= 4 ) and ( c >= 6 and c <= 10 )
    这条语句由于b列使用了范围查询，因此c列无法使用索引来快速定位，c列的条件只能作为查找后的过滤条件。联合索引中，如果某个列使用了范围查询，后面的列将无法使用索引来快速定位。这里如果b列的范围较小，尽量将范围条件拆成多个等值条件的或组合，如 (b = 2 and c = 8 ) or ( b = 3 and c = 8 ) or ( b = 4 and c = 8 )，这样c列也可以使用索引，最后取多个或条件的并集。
  • select * from t where a = 1 and b in (2, 3, 4 ) and c = 8
    IN 条件一般等效于多个等值查询的或组合，这个SQL与 select * from t where (a= 1 and b = 2 and c = 8 ) or ( a = 1 and b = 3 and c = 8 ) or ( a= 1 and b = 4 and c = 8 ) 一样可以使用索引，然后再union。
    但注意 where a in (2, 3, 4) and b in (5, 6, 7) and c in (8, 9, 10)会产生很多条件组合
    如果有一个索引是idx_sex_country(sex, country)，但查询条件只有 where country = 'China'， 可以优化为where sex IN ('male', 'female') and country = 'China' 进而可以使用索引。

不能使用联合索引 idx_a_b_c 的语句：

  • select * from t where b = 2
    这条SQL可以利用后面的松散索引扫描来避免对全索引扫描
  • select * from t where a like '%abc%'
  • select * from t where a = 1 order by c
    a = 1可以使用联合索引，但order by c需要额外的排序，不能利用索引避免排序
  • select * from t where a = 1 order by b DESC c ASC
    MySQL官方文档表述因为列b和列c的排序方向不一样，无法直接使用索引，需要排序的列都是升序或者降序
    但其实由于辅助索引已经按照列b和列c的增序排序，其实可以利用这个特性，最终也可以不排序来实现order by b DESC c ASC
  • select * from t order by a ASC b DESC c ASC d DESC e ASC
    与上面类似的，这个查询不能直接使用索引索引idx_a_b_c_d_e(a, b, c, d, e)，但其实可以利用索引已经按列a, b, c, d, e的顺序排序的特点，对索引idx_a_b_c_d_e的叶子结点进行一次遍历即可实现按照a ASC b DESC c ASC d DESC e ASC的顺序排列，不用再做额外的排序。
    如果需要按不同方向做排序，可以存储该列值的反转串或者相反数
  • select * from t where a > 1 order by b, c
    由于列a是范围查询，对列b、c排序无法使用索引。注意，通过索引找到第一个 a > 1 的记录后，索引是按照列 a、b、c的顺序排列，不是按照b、c的顺序排列的，所以无法使用索引来避免对列b、c的排序。 select * from t where a > 1 order by a, b, c 是可以直接使用索引的。
    范围查询会导致后面的等值条件和order by条件无法继续使用联合索引
  • select * from t where a = 1 and b in (2, 3, 4) order by c;
    IN条件会导致对列c的排序无法直接使用索引，将IN条件拆成多个等值条件后，在a、b的值确定后，索引是按照列b、c的顺序排序的，不是只按照列c的顺序排列的。select * from t where a = 1 and b in (2, 3, 4) order by b, c; 这条语句对列b, c的排序是可以直接使用索引的。
    注意 select * from t where a = 1 and b in (2, 3, 4 ) and c = 8 将IN条件拆成多个等值的OR条件后，可以先利用二级索引查询，最后union没有其他成本。但对于 b in (2, 3, 4) order by c，将IN条件拆成多个等值的OR条件后，最后union还需要再做一次列c的排序，没法利用二级索引避免排序。
    联合索引中，IN 条件后的等值条件可以继续使用索引，IN 条件后的 order by条件无法继续使用索引
    范围条件和IN条件不能和order by中的列构成联合索引的最左前缀
  • select * from t where a + 1 = 5
    • 这条语句等价于 select * from t where a = 4，但MySQL没法解析这个表达式去使用索引。对索引字段做函数操作（比如 select * from t where month(update_time) = 7 ），即便有索引idx_update_time，优化器会放弃走B+树搜索功能，而是走B+树扫描。
    • select * from tracelog where trace_id = 110717，这里虽然trace_id有索引但如果trace_id字段是varchar，也无法使用索引。通过 select “10” > 9 的结果是1可以发现，如果是字符串与数字进行比较，MySQL默认将字符串隐式转换成数字来比较。因此上述SQL其实等效于 select * from tradelog where CAST(tradid AS signed int) = 110717; 由于需要对索引字段做类型转换操作导致无法使用索引的搜索能力，但是 select * from t where id = '3' 是可以走索引查找的，因为字符串默认转换成整数后于主键的类型一致。
    • 多表join时，对于驱动表上的每一行，都要根据关联字段查询被驱动表上的索引，但是如果两张表的关联字段的类型不同，需要做字段类型隐式提升（长度短的类型向长度长的类型转换,int -> long，utf8 -> utf8mb4），如果是驱动表上的类型提升到被驱动表的类型，那没有问题，还是可以使用被驱动表的索引，但如果是被驱动表上的类型提升为驱动表的类型，对索引字段做类型转换操作会导致到被驱动表上查询时无法使用索引搜索功能，只能对被驱动表进行扫描。优化方法是修改被驱动表关联字段的类型，或者强制将驱动表(table1)的关联字段的类型转换为被驱动表(table2)的类型 select * from table1 inner join table2 on CONVERT(table1.name using utf8) = table2.name where table1.id = 3;
覆盖索引

只通过辅助索引即可完成SQL的所有操作，不需要再查找聚簇索引。辅助索引每页存储的行记录更多，其B+树相比聚簇索引更加矮胖，IO次数更少。
下面的语句可以使用覆盖索引idx_a_b_c：

  • select b, c, pKey1, pKey2 from t where a = 1
  • select b, c, pKey2 from t where a > 0 and a < 3 and pKey1 > 2 and pKey1 < 4
  • select a, b, count(*) from t group by a, b，这条语句会优先用联合索引idx_a_b_c全表扫描，而不是对聚簇索引全表扫描;
  • select count(*) from t, 这条语句会优先用联合索引idx_a_b_c全表扫描，而不是对聚簇索引全表扫描，因为联合索引一个叶子结点中存储的行数更多
  • select count(*) from t where b > 2 and b < 10, 这条语句会优先用联合索引idx_a_b_c全表扫描，而不是对聚簇索引全表扫描
不使用辅助索引的情况
  • select * from t where a > 1 and a < 10000
    这条语句可能不使用联合索引 idx_a_b_c，而是直接对聚簇索引进行全表扫描，因为通过辅助索引查到主键后，还需要通过聚簇索引再查找一次，虽然联合索引 idx_a_b_c 是按照列a的值顺序存放的，但查出来的多个主键是无序的，每个主键去聚簇索引查找其实是离散读，主键太多离散读就会很多(mysql的Multi Range Read优化会把通过辅助索引找出来的一批主键排序后，再去聚簇索引中对叶子结点尽可能顺序遍历，尽量多地通过顺序查找到行记录，减少随机读，explain的Extra会有Using MRR)， 因此如果位于 a > 1 and a < 10000 范围内的行记录太多，通过这些行的主键去聚簇索引中查找反而更慢，还不如直接对聚簇索引全表顺序扫描。因此，如果不是覆盖索引，使用辅助索引再使用聚簇索引仅限于查找出来的主键较少的情况
多个单列索引

select * from t where a = 1 or b = 2 or c= 3 或者 select * from t where a = 1 and b = 2 and c= 3
多个AND条件可以通过多列联合索引进行优化，多个OR条件和多个AND条件也可以通过对每个列分别建立单列索引进行优化。这里可以建立3个单列索引，idx_a(a), idx_b(b), idx_c(c) ，MySQL的索引合并优化会分别使用idx_a, idx_b, idx_c来做查询，然后取3个辅助索引的结果的并集（交集），再回表查询聚簇索引。当然也可以通过手动拆分SQL来指定MySQL对3个单列索引的查询结果取并集：select * from t where a = 1 UNION select * from t where b = 2 UNION select * from t where c= 3， 注意MySQL没有 INTERSECT 运算符

松散索引扫描（Loose Index Scan）和紧凑索引扫描（Tight Index Scan）

如果有联合索引idx_a_b_c，对于 select a, max(b) from t group by a，并不需要扫描联合索引所有的记录，对于每个group只需扫描一条记录。因为在B+树的叶子结点中，如果连续出现多个相同值的a，可以通过向上查找索引结点快速找到a的下一个不同值的起始位置，而不用在叶子结点上一直顺序遍历。可以认为有这样一个API，对于联合索引idx_a_b_c，可以快速找到第一个大于等于值(a, b, c)的位置，类似二分查找

  • 紧凑索引扫描：扫描范围内或者整个索引的全部记录。
    比如 select a, count(b) from t group by a
  • 松散索引扫描：不需要连续地扫描索引中的每一条记录，仅扫描一部分记录。比如group by时，读取的索引记录的个数和group的数量相同。
    松散索引扫描的条件：group by的聚集函数是min()和max()，并且指定的是同一列（如果min和max同时存在），这一列必须在索引中，且紧跟在group by指定的列后面。
    新版本的松散索引扫描支持 COUNT(DISTINCT ), SUM(DISTINCT), AVG(DISTINCT) 等聚集函数。

松散索引扫描和紧凑索引扫描的最大区别是是否需要扫描整个索引或者整个范围内的所有记录。

group by的分组操作其实转化为了order by操作，在不能使用索引时，需要先读取需要的数据并创建一个临时表，然后按照group by指定的列进行排序，排好序后，可以发现所有的group，每个group的记录是连续的，最后执行聚集函数。可以看到，在没有使用索引的时候，需要创建临时表和排序。执行计划Extra中通常可以看到“Using temporary; Using filesort”。但在有合适索引时，可以利用索引避免创建临时表和排序。

group by(order by)使用索引避免分组和排序的条件：group by的列（需要考虑顺序）和where中等值条件的列构成联合索引的最左前缀，比如对于索引idx_a_b_c_d_e，比如 select max(e) where b = 2 and d = 4 group by a, c; 可以使用索引避免分组和排序，可以使用松散索引扫描

松散索引扫描的例子：

  • select a, b from t1 group by a,b
    相同的a, b只会显示为一行a,b，这条sql等价于 select distinct (a, b) from t1。group by和distinct内部的处理都使用了排序
  • select a, count(distinct b ) from t1 group by a
    distinct可以通过索引快速找到下一个不同值来避免对叶子结点顺序遍历
  • select a, avg(distinct b, c) from t1 group by a
  • select * from t where b > 2 and b < 4
    这条sql在对索引的叶子结点扫描时，先找到起始位置的第一个a的值，然后等价于where a = x1 and b > 2 and b < 4，利用索引范围查找找到满足b范围的记录，然后利用索引跳到a的下一个值的起始位置，等价于 where a = x2 and b > 2 and b < 4，再利用索引找到b范围的记录。

参考：
http://mdba.cn/2014/03/25/mysql松散索引扫描与紧凑索引扫描/?utm_source=tuicool&utm_medium=referral

前缀索引

对很长的字符串(char(255)、varchar(1024))上建索引会导致索引体积很大，且查找性能不够高。

一种优化方式是对长字符串的哈希值(CRC32)建索引或者是建联合索引(idx_crc32_content(crc32, content)，其实联合索引性能也不太好，因为单个记录太大了)，查询时 where crc32 = 1720248 and content = '我想吃肯德基'。这种方式索引体积较小，查询较快，但无法用于范围查询和排序。可以使用触发器实现在insert和update时自动更新哈希值。

或者是建立前缀索引 idx_content(content(6))，使用前6个字符建立索引，要求前6个字符的区分度接近于对完整字符串建索引的区分度，区分度 = cardinality / 行数。

前缀索引的缺点是无法用于order by和 group by，也无法用于覆盖索引。因为order by 和 group by是对整个列进行排序或分组。当然，理论上前缀索引对order by和 group by还是会有帮助，因为排序一个部分有序的数组一定比排序一个完全无序的数组快。

后缀索引

mysql不支持后缀索引，可以使用触发器将字符串反转后存储（倒序存储），并基于此建立前缀索引。

索引对NULL值的处理

上述表t中，列a可以为NULL，如果对列a建立唯一索引，由于NULL不等于NULL，与NULL值进行条件判断都是false，因此即便是唯一索引，也可以插入多行列a为NULL的值。
索引列为NULL的记录会存储在辅助索引B+树叶子结点的最左边，select * from t where a is null; 也会使用列a上的辅助索引来加速查询， a is null 和 a = 3 对于辅助索引的查找其实是一样的，都是等值查找。

索引条件下推(Index Condition Pushdown, ICP)

MySQL5.6后支持ICP，比如在联合索引中，如果左边的列是范围查询，右边的列是等值查询，且需要回表查询。比如select * from t where 1 < a and a < 9 and b = 3，可以MySQL server层通过ICP可以将b = 3这个条件也给innoDB，这样可以减少innoDB回表的次数

哈希索引

innoDB会自动将常访问的行记录放到缓存中，并将主键放到hasn表中，对于等值查询(例如 select * from t where a = 1)，hash索引可以加速查找，但对于范围查询就没什么用了。
hash索引的建立和维护是innoDB自己控制的，DBA不能干预。
hash索引类似redis，redis对缓存的支持更好。
哈希索引不会存储索引列的值，因此无法使用覆盖索引

倒排索引

B+树索引并不支持类似 select * from t where content like '%xxx%'。innoDB目前通过倒排索引（全文检索功能）来实现上述功能。不过Elastic Search对全文检索的支持会更好些。倒排索引不会存储索引列的值，因此无法使用覆盖索引。

唯一索引与一般索引的性能对比

对于 select age from person where name = '张三'，现在需要对name字段加索引优化查询速度。如果表person中的name字段满足唯一性，此时既可以创建唯一索引，也可以创建一般索引。

查询过程性能对比：

  • 普通索引：查找到满足条件的第一个 name = '张三' 的记录后，需要继续查找下一个记录，直到碰到第一个不满足 name = '张三 的记录。
  • 唯一索引：由于索引具有唯一性，查找到第一个满足条件的记录后，就会停止检索。

查询过程性能差距非常小，数据是以页为单位来读写的。当找到 name = '张三' 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录是否满足条件”的操作，就只需要一次指针寻找和一次计算。当然，如果 name = '张三'这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会耗时一些，但这个概率很小，因为一个数据页一般可以存放很多行记录。

更新过程性能对比：
唯一索引上的insert操作（update是先delete再insert）需要判重，需要先将数据页读到Buffer Pool中，不能通过change buffer优化，因此如果insert的Page不在Buffer Pool中：

  • 对于唯一索引，需要将数据页读入Buffer Pool，判断是否冲突，然后插入，语句执行结束；
  • 对于普通索引，只是将insert记录在change buffer中，语句执行就结束了。

因此一般索引的更新性能优于唯一索引。如果insert的Page在Buffer Pool中，两者性能差距非常小。

IN vs OR

  • 如果in和or所在列有索引或者主键的话，or和in没啥差别，执行计划和执行时间都几乎一样。

  • 如果in和or所在列没有 索引的话，性能差别就很大了。在没有索引的情况下，随着in或者or后面的数据量越多，in的效率不会有太大的下降，但是or随着记录增加性能下降很多。

    • SELECT * FROM tbName WHERE KEY IN (a1, a2, ...... , an);
      in 先将 a1, a2, …, an 变成二叉搜索树，用二叉树查找，时间复杂度为O(LogN);
    • SELECT * FROM tbName WHERE KEY = a1 OR KEY = a2 OR ...... OR KEY = an;
      or 是从 a1 匹配，匹配失败，去匹配 a2，直到匹配成功或者一个都匹配不上,时间复杂度是 O(N);

3、innoDB索引总结

  • 使用FORCE INDEX可以强制MySQL使用某个索引
  • 如果表较小，全表扫描可能更快，而不是使用索引。比如 2.3、不使用辅助索引的情况 中的例子
  • 如果有几个查询条件，在每次查询都会用到，应按照列的选择性从高到低设计索引列的顺序；如果并不是每次查询都用到，可以按照列的使用频率从高到低设计索引列顺序。
  • 索引太多，插入删除就会很慢
  • B+树相比于B树和二叉平衡查找树的优势是更加利于范围查找和顺序遍历，但随机插入和随机删除会使得逻辑上相邻的行可能在物理上存储在不同的位置，导致逻辑上的顺序读变成物理上的随机读，造成B+树的顺序遍历代价很大。虽然都是B+树，但innoDB是索引组织表，其聚簇索引会导致innoDB的随机插入性能不如MyISAM。innoDB的主键最好设置为AUTO_INCREMENT实现顺序插入，但在高并发插入场景下容易导致生成主键的锁冲突。
  • innoDB相比于MyISAM的优点在于，当回收已删除数据的空间导致行数据移动时，innoDB无须更新二级索引，对二级索引的维护将变得很简单，MyISAM需要更新所有索引中存储的行数据的指针，使其指向新的地址。
  • 多列联合索引中，范围查询后面的列将无法使用索引，因此设计索引时应将范围查询的列放到最后，比如年龄可能范围查询，因此应设计为 idx(sex, city, age) 而不是 idx(sex, age, city)
  • MySQL子查询生成的临时表没有索引。
4、innoDB索引 VS myisam索引

innoDB 是聚簇索引 + 辅助索引。辅助索引也称非聚簇索引或二级索引。聚簇索引将行数据放到与索引的叶子结点上，行数据是索引的一部分。行数据的存储是按照主键排序的。辅助索引叶子结点存储的是主键的值

myisam 使用的是 非聚簇索引。在MyISAM中，主键索引和辅助索引在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。行数据与索引分开存储，行数据存储在独立的地方，行数据可以是完全无序的。所有非聚簇索引叶子结点存储的都是行数据的指针

innodb索引的优势

  • 不管是myisam还是innoDB，一般其索引是加载在内存中，行数据是在磁盘上。innoDB叶子结点中的行数据是近乎有序的，数据的存储是以Page为单位的，Page中的行数据的主键一般是相邻有序的，访问某个行记录时，已经把整个页加载到了Buffer中，再次访问相邻的行记录时（空间局部性原理），可以在内存中完成访问，不必访问磁盘。而myisam的行数据并不要求是有序的，这样后续读取相邻行的数据时可能还是需要访问磁盘。
  • innoDB辅助索引使用主键而不是行数据的地址值，减少了行移动时（比如删除行数据后的空间整理操作）多个辅助索引的维护工作。
  • innoDB索引更加适合用在需要根据主键对行数据排序和根据主键对行数据进行范围查找。

innoDB索引的劣势

  • 每次使用辅助索引检索都要经过两次B+树查找。而通过myisam的辅助索引可以直接访问到行数据，不需要通过主键索引查找。
  • innoDB的聚簇索引在插入和删除时需要维护行数据的有序性，按照主键的顺序插入是最优的方式，如果是随机插入代价昂贵。
    • 如果在中间插入新的行数据，特别是行数据较大时，可能频繁地导致页分裂和数据移动，容易产生空间碎片。
    • myisam非聚簇索引的插入一般是在行数据区域的最后插入，行数据区域不会发生页分裂和数据移动，但索引的叶子结点可能出现页分裂，只是出现的概率很低。类似地，当行数据较大时，聚簇索引上的删除操作可能频繁地引起页的合并。
  • 更新主键的代价很高，因为会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
  • 辅助索引会包含主键列（比如联合主键），如果主键定义的比较大，辅助索引也将很大

Join

Index Nested-Loop Join算法

MySQL对多张表的关联执行的是嵌套循环关联，先从第一张表（驱动表）中循环取出单条数据，然后到下一个表（被驱动表）中寻找所有匹配的行（因此被驱动表最好有索引，利用索引等值查询），依次下去，直到找到所有表中匹配的行，MySQL的查询优化器会调整多表关联查询中表的顺序（straight_join可以强行指定join顺序）

比如 select * from actor inner join film_actor on actor.id = film_actor.actor_id

两张表进行关联，假如关联字段在两张表上都有索引，对于驱动表上的每一行数据，都要去查询被驱动表上的索引，因此查询被驱动表上的索引的次数其实是驱动表的行数， 应该选择符合条件的行数最少的表作为驱动表。

多表join时，如果order by或者group by的列都是驱动表上的列，则会先对驱动表排序后（可以使用驱动表的索引）再开始嵌套循环关联，否则需要对最终join出来的临时表进行排序，由于临时表不会有索引，因此会有额外的filesort

对于被驱动表上有索引的情况：设被驱动表的行数是M。每次在被驱动表查一行数据，要先搜索辅助索引，再搜索聚簇索引，共搜索两次。每次搜索一棵树近似复杂度是以2为底的M的对数，记为log2M，所以在被驱动表上查一行的时间复杂度是 2log2M 。假设驱动表的行数是N，执行过程就要扫描驱动表N行，然后对于每一行，到被驱动表上匹配一次。因此整个执行过程，近似复杂度是 N + N2*log2M。显然，N对扫描行数的影响更大，因此应该让小表来做驱动表。

Block Nested-Loop Join算法

如果被驱动表上没有索引，此时MySQL用的算法是Block Nested-Loop Join（Block是分块的意思），可以在explain的extra中看到using join buffer(Block Nested-Loop Join)：将驱动表中符合where条件的行放入内存的join_bufer中（join_buffer不是临时表），每行的字段是最终需要返回的字段；如果join_buffer满了，或者驱动表符合条件的行已经全部放入（此时只有1轮），则依次取出被驱动表中符合where条件的行，由于join_buffer的数据是无序的，对于被驱动表中的一行，需要和当前join_buffer中的所有行依次进行join条件判断，找出能join上的行；然后清空join_buffer，从驱动表中继续取数据填充join_buffer，进行下一轮。

假设驱动表符合where条件的行数是N，需要分K(K = N * row_size /join_buffer_size, 1<=K<=N)轮才能完成join，被驱动表符合where条件的行数是M。在这个算法的执行过程中：需要扫描的总行数是N + K * M = N + N * row_size * M / join_buffer_size，内存中的判断共 N* M次。显然，内存判断次数是不受选择哪个表作为驱动表影响的。而考虑到扫描行数，在M和N大小确定的情况下，N小一些，整个算式的结果会更小，所以结论还是应该让小表当驱动表。当然，K也是影响扫描行数的关键因素，这个值越小越好，因此join_buffer_size越大越好。

这个算法性能差的原因：

  • 实际中join_buffer_size一般不够大，导致K会大于1，导致这个算法需要重复扫描被驱动表，占用磁盘IO。
  • 判断join条件需要在内存中执行M* N次对比（M、N分别是两张表的行数），如果是大表会消耗非常多的CPU资源；
  • 重复多次扫描被驱动表会导致被驱动表的数据进入Buffer Pool的Young区，淘汰Young区中真正的热数据被淘汰，影响内存命中率。

优化的方式是建立一个带索引的临时表，比如表t1和t2的字段b上都没有索引，对于如下SQL：

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;  
### 这个SQL可以优化为：
create temporary table temp_t(id int primary key, b int, index(b))engine=memory;  
// 如果数据量小优先选择内存临时表，并建立hash索引，否则选择innoDB磁盘临时表，建立B+树索引
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

left outer join的关联顺序

对于 select * from a left join b on a.f1=b.f1; MySQL的查询优化器也可以调整多表关联的顺序，表a可能不是驱动表。如果采用Block Nested-Loop Join算法，即便选择表b作为驱动表，可以在join的过程中记录表a中的哪些行没有和表b join成功，来找出所有表a中没有和表b join成功的行，将这些行放入最终的结果集。

对于如下SQL：
select * from a left join b on (a.f1=b.f1) where (a.f2=b.f2);
// 由于 NULL 和 任何值 都不相等，这里left join的结果等于inner join
select * from a join b on(a.f1=b.f1) and (a.f2=b.f2);
select * from a join b on(a.f1=b.f1) where (a.f2=b.f2);
// INNER JOIN的过滤条件放在ON或WHERE里， 执行结果没有区别。但对于left outer join，on 和where中的过滤条件是有区别的，此时的where是对left outer join 的最终结果做过滤的

通过show warnings命令可以看到，MySQL会将上面3条SQL都改写为：
select * from a join b where (a.f1=b.f1) and (a.f2=b.f2);

MySQL排序原理

Extra中出现filesort表示需要额外的排序，但不能区分排序是在内存中完成还是使用了磁盘

当MySQL需要对innoDB表某个字段排序时，如果排序可以在内存中完成，使用快速排序；否则会将数据分块，对每个块使用快速排序，然后将每个块的排序结果存到磁盘临时文件中（不是磁盘临时表，没有Using temporary），最后对各个排好序的块的结果进行merge并返回（归并排序），MySQL5.6版本引入了堆排序（filesort_priority_queue_optimization），比如从10万个行记录中取最小的前3个，堆的大小是3，堆就可以在内存中，堆排序不会使用磁盘临时文件。

MySQL有两种排序方法，优先选择全字段排序，然后是rowid排序：

  1. 全字段排序
    sort_buffer包含所有需要返回的列和排序字段，排好序后，应用LIMIT N OFFSET M 返回。
  2. rowid排序
    sort_buffer只包含行指针（对于innoDB表是主键id，对memory和MyISAM是行指针）和排序字段（如果二级索引中没有排序字段，需要根据主键回表查），排好序后，应用LIMIT N OFFSET M，再通过行指针回表读出需要返回的列，这一步可能会产生大量随机IO

如果是内存临时表，一定使用rowid排序。
如果需要返回的列和排序字段超过 max_length_for_sort_data，选择rowid排序（rowid排序结束后会多一个回表查的步骤），否则选择全字段排序；接下来，如果排序的总数据量小于sort_buffer_size，在内存中使用快排，否则，看堆的大小是否可以在内存中（小于sort_buffer_size），如果堆可以在内存中使用堆排序，否则使用磁盘归并排序。MySQL的设计思想是，如果内存够，就要多利用内存，尽量减少磁盘访问。全字段 + 全内存快速排序 性能最好。

select word from words order by rand() limit 3 的执行流程

  • 创建一个没有索引的内存临时表，表中有word和rand（存储rand() 随机函数生成的值 ）两个字段。
  • 扫描words表，依次取出所有的word值，对于每一个word值，调用rand()函数生成一个大于0小于1的随机小数，并把word和这个随机小数插入内存临时表。如果内存临时表大小超过tmp_table_size，会创建innoDB磁盘临时表，将内存临时表数据插入到磁盘临时表中。
  • 接下来是对临时表按word字段排序，如果是内存临时表，总是选择rowid排序方法，便于在临时表根据数组下标直接寻址。如果是innoDB表要判断选择全字段排序还是rowid排序。
  • 初始化 sort_buffer。如果选择rowid排序算法，sort_buffer中有两个字段，一个存储rand()，另一个是临时表的行指针
  • 扫描临时表，取出rand字段和行指针（innoDB表主键 或者 memory表数组下标），存入sort_buffer。
  • 对sort_buffer根据rand的值进行排序
  • 排序完成，取出sort_buffer中前三个结果的行指针，到临时表中取出word值，返回给客户端

如果使用了临时表，explain的extra会有using temporary，session结束后会通过DROP TEMPORARY TABLE自动删除临时表。

内存临时表的引擎是memory，数据都在内存中，表定义在磁盘，重启后数据会被清空。MEMORY和MyISAM都不是索引组织表，可以不指定主键，索引和行数据分离，行数据存储类似数组，每行大小固定（不支持Blob 和 Text字段，并且即使定义了varchar(N)，实际也当作char(N)），物理空间连续，每行有个指针，可以通过指针对行数据随机寻址。行数据按照插入顺序存放的，插入性能较好，行数据被删除以后，空出的这个位置可以被接下来要插入的数据复用。行数据位置发生变化的时候，需要修改所有索引的行指针；MyISAM表索引是B+树，memory表索引默认是hash索引，只适合等值查询，memory表也可以创建B+树索引。所有索引地位相同，都可以直接找到行数据，不像innoDB那样需要回表。MEMORY和MyISAM只有表锁，不支持行锁。

innoDB是索引组织表，数据存在磁盘，如果不指定主键会自动生成rowid。

order by rand() 相比一般的order by多了一次临时表全表扫描。需要先扫描原表生成一个临时表，为每行增加一列随机数，然后扫描临时表填充sort_buffer，最后对sort_buffer排序。

MySQL group by原理

select customer_id, sum(order_amount) as `sum` from `order` group by customer_id

如果不能利用索引避免排序，默认的执行流程是创建临时表(Using temporary)，先分组聚合，然后排序(Using filesort)：

  1. 创建memory引擎内存临时表，表里有两个字段customer_id, sum，指定customer_id是主键（默认hash索引）
  2. 扫描表的索引（包含customer_id和order_amount字段）或者全表扫描，依次取出customer_id，order_amount的值。
  3. 利用主键索引查找内存临时表中是否已经有customer_id的行，如果没有插入(customer_id, order_amount)，如果有，更新对应行的 sum += order_amount。
  4. 如果往内存临时表插数据的时候，发现内存临时表大小已经超过tmp_table_size。新起一张磁盘临时表（默认是innoDB引擎，指定主键为customer_id（默认B+树索引）），将内存临时表的数据挪到磁盘临时表中，这一步涉及到磁盘IO。
  5. 根据customer_id字段对临时表排序（扫描临时表填充到sort_buffer，然后排序），将结果集返回给客户端。可以通过order by null来避免这一步。另外如果使用了磁盘临时表，本身就是按照customer_id排序的，排序可以忽略。

默认方案的特点是尽量减少内存使用，因此不能将所有数据读到内存再排序，而是一边读一边聚合。而SQL_BIG_RESULT是暗示优化器可以使用大数据量的方式，即先全部读到内存，再排序聚合
having 不直接参与到执行计划中去，它是对结果集进行过滤的

group by的四个优化方案：

  1. 分组字段加索引，避免排序和创建临时表，没有Using temporary和 Using filesort，可以使用覆盖索引。
    比如存在idx_customer_id_order_amount 二级索引时的执行流程：通过扫描idx_customer_id_order_amount 索引，累加order_amount保存到sum变量，当遍历到新的customer_id时，将原来的customer_id和sum保存到结果集，清空sum，继续遍历
  2. order by null，避免最后对group by字段的排序，但order by null在MySQL 8.0以后已经不需要了
  3. 通过增大tmp_table_size尽量使用内存临时表
  4. SQL_BIG_RESULT，直接使用排序算法(Using filesort)，不需要创建临时表（没有Using temporary）
    对于默认的流程，如果数据量过大，大到内存临时表不够用了，这时会将内存临时表数据拷贝到磁盘临时表，这个拷贝过程也是很耗时的。SQL_BIG_RESULT 会提示 MySQL优化器，这个语句涉及的数据量较大，请直接用磁盘临时表。MySQL的优化器一看，磁盘临时表是B+树存储，存储效率不如数组来得高。所以，既然数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。

SQL_BIG_RESULT

select SQL_BIG_RESULT customer_id, sum(order_amount) as `sum` from `order` group by customer_id

加入SQL_BIG_RESULT后的执行流程变成先排序，再分组和聚合：

  1. 初始化 sort_buffer，里面有customer_id, order_amount字段；
  2. 扫描表的索引（包含customer_id和order_amount字段）或者全表扫描，依次取出customer_id，order_amount插入sort_buffer中，如果sort_buffer 内存不够用，会利用磁盘临时文件辅助排序；
  3. 扫描完成后，对 sort_buffer 按照 customer_id 做排序，得到一个有序数组。
  4. 遍历有序数组，找到分组，应用聚合条件，返回结果。

https://mp.weixin.qq.com/s/igGKepCsf-SUKChco4P6Lw

distinct VS group by

如果表t的字段a上没有索引，那么下面这两条语句：

select a from t group by a order by null;
select distinct a from t;

的性能是不是相同的?

标准的group by语法，是需要在select部分加一个聚合函数的，但是如果group by中没有聚集函数，其实distinct 和group by这两条语句的语义和执行流程是相同的，因此执行性能也相同。

这两条语句的执行流程如下：

  1. 创建一个临时表，临时表有一个字段a，并且在这个字段a上创建一个唯一索引；
  2. 遍历表t，依次取数据插入临时表中：
    • 如果发现唯一键冲突，就跳过；
    • 否则插入成功；
  3. 遍历完成后，将临时表作为结果集返回给客户端。

MySQL优化经验

  • MySQL优化器的目标是使得扫描行数和随机查找次数最小。
  • 建表时尽量指定列为 NOT NULL。主键字段尽量使用自增主键，插入时是顺序插入，插入性能高，主键的长度尽量短，减少二级索引的空间占用
  • date只能存日期，类似'YYYY-MM-DD'，MySQL 5.6.4以后 datetime和 timestamp 定义时可以包含一个最多6位的小数部分，意味着精度最多可以到微秒，此时其存储空间字节数不再固定，与小数部分的位数有关。MySQL 5.6.4之前因为没有小数部分，timestamp是4字节，datetime是8字节，两者只能精确到秒
CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  dt DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

##这样就可以插入 
insert into t1 (ts, dt) values ('2022-03-10 17:20:56.109018', '9999-1-10 17:20:56.509018');
##注意 current_timestamp(6) 会返回微秒级精度

timestamp存储时会将当前时区的时间转化为UTC时区的时间进行存储，查询时会将UTC时区的时间转化为当前时区的时间，因此，如果在插入数据后，修改服务器时区，查询出来的时间将不一样。TIMESTAMP has a range of '1970-01-01 00:00:01' to '2038-01-19 03:14:07'.

  • 【强制】表必备三字段：id, create_time, update_time。说明：其中id必为主键，类型为bigint unsigned、单表时自增、步长为 1。create_time, update_time的类型均为datetime
  • INT(11) 和 INT: 11表示显示宽度，当列定义为 b INT(11) UNSIGNED ZEROFILL NOT NULL,时，INT(11)才有意义，表示如果显示的数字少于11个字符，则这些数字将在左侧补零。
  • FLOAT 和 DOUBLE 使用CPU原生支持的浮点运算进行近似计算，因此速度较快，DECIMAL 类型用于存储和计算精确小数，DECIMAL的精确计算是由MySQL自己实现的，因此性能较差。DECIMAL可以指定小数点前后可以允许的最大位数，每4个字节存9个数字。例如DECIMAL(18,9)指定共可以存18个数字，小数点后可存储9个数字，小数点占用一个字节，一共用9个字节。
  • 【强制】小数类型为 decimal，禁止使用 float 和 double。在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的 结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。
  • 下面情况适合使用varchar：字符串列的最大长度远大于平均长度，列的更新较少，使用了像utf8mb3这样的可变长度字符集。【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据 实际文本区分度决定索引长度。
  • 对于经常变更的列，char比varchar更合适。【强制】如果存储的字符串长度几乎相等，使用 char 定长字符串类型。
  • 枚举在mysql内部是作为整数存储的，会根据列表值的数量压缩为1到2个字节
  • 【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致，包括像unsigned这样的属性；多表关联查询时， 保证被关联的字段需要有索引。
  • 建议把distinct优化为group by，把 where field IN (1, 3, 4, ...,) 子查询优化为join。
  • union 的优化：建议将最外层的where、limit、order by等条件下推到union的各个子查询中。尽量使用union all。
  • 【推荐】利用延迟关联或者子查询优化超多分页场景。
    参考 https://www.cnblogs.com/zoo-keeper/articles/16020080.html
select * from product where city = 'beijing' order by create_time limit 10 offset 10000; 
#应该优化为：
select * from product  inner join 
( select id from product where city = 'beijing' order by create_time limit 10 offset 10000 ) t 
where product.id = t.id;

  • count()的原理

    • MyISAM引擎把一个表的总行数存在了磁盘上，因此执行没有where条件的count(*)的时候会直接返回这个数，效率很高；innoDB由于MVCC的原因，同一时刻不同事务执行相同的count(*) 得到的结果不同，innoDB需要遍历每行数据做可见性判断，对可见的行累积计数。innoDB遍历时会尽量选择辅助索引而不是聚簇索引。
    • 如果括号中指定列或者列的表达式，则统计这个表达式不为NULL的数量，由于需要计算表达式，因此要慢些。
    • 如果MySQL确认括号内的表达式不可能为NULL，则直接统计行数，比如 count(*)，这里的通配符 * 并不会展开为所有列，实际上会忽略所有列而直接统计行数。

  • MySQL explain 字段说明：

type字段 consts ref range index all
查询主键或者唯一索引，表中最多只有一个匹配行 索引等值查询 索引范围查询 索引扫描 全表扫描
Extra字段说明 Using index Using where Using filesort
覆盖索引，只用二级索引即可完成查询 不是覆盖索引，需要回表查询数据，然后利用where条件过滤不满足条件的记录 需要额外排序， 排序可能在内存或者磁盘中

参考

http://mysql.taobao.org/monthly/2018/04/03/

https://jinglingwang.cn/archives/mysql-explain

posted @ 2022-03-27 21:42  zoo-keeper  阅读(269)  评论(0)    收藏  举报