数据结构

• innodb 应用了 b + 树作为索引
• 主键索引的叶子节点存的是整行数据，也被称为聚簇索引
• 非主键索引的叶子节点存的是主键的值，也被称为二级索引

• 基于非主键索引的查问，先搜寻树失去主键的值，再到主键的索引树搜寻一次，这个过程称为回表，要多扫描一棵索引树，在利用中应该尽量应用主键查问

  索引保护

• B+ 树为了保护索引有序性，在插入新值的时候须要做必要的保护
• 当插入一个两头值，可能会波及到数据页的移动，包含页决裂、页合并
• 自增主键是指自增列上定义的主键，在建表语句中个别是这么定义的：NOT NULL PRIMARY KEY AUTO_INCREMENT，它每次插入都是追加操作，不波及到移动其余记录，不会触发叶子节点决裂
• 有业务逻辑的字段做主键，则往往不容易保障有序插入，这样写数据老本绝对较高（性能）
• 主键应用自增主键，长度比应用字符串更小，一般索引的叶子节点更小，占用的空间也更小（存储空间）
• 当业务场景是只有一个索引、且必须是惟一索引，能够应用业务字段做主键

笼罩索引

• 在查问中，索引曾经笼罩了查问所需的所有字段，称为笼罩索引
• 笼罩索引能够缩小树的搜寻次数，显著晋升查问性能，应用笼罩索引是一个常见的优化伎俩

• 最左前缀准则

  • 最左前缀能够是联结索引的最左 N 个字段，也能够是字符串索引的最左 M 个字符
  • 索引下推优化，如果一个查问条件蕴含笼罩索引的最左字段与其余字段，查问时会通过笼罩索引去比对其余字段是否满足要求，若不满足则不会去回表

  惟一索引和一般索引

• 对于一般索引来说，查找到满足条件的第一个记录 后，须要查找下一个记录，直到碰到第一个不满足一般索引条件的记录
• 对于惟一索引来说，因为索引定义了唯一性，查找到第一个满足条件的记录后，就会进行持续检索
  InnoDB 的数据是按数据页为单位来读写的，对于整型字段，一个数据页能够放近千个 key，因而呈现这种状况的概率会很低。所以，咱们计算均匀性能差别时，仍能够认为这个操作老本对于当初的 CPU 来说能够忽略不计

change buffer

• 当须要更新一个数据页时，如果数据页在内存中就间接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不须要从磁盘中读入这个数据页了
• 在下次查问须要拜访这个数据页的时候，将数据页读入内存，而后执行 change buffer 中与这个页无关的操作。通过这种形式就能保障这个数据逻辑的正确性
• 将 change buffer 中的操作利用到原数据页，失去最新后果的过程称为 merge。除了拜访这个数据页会触发 merge 外，零碎有后盾线程会定期 merge。在数据库失常敞开（shutdown）的过程中，也会执行 merge 操作
• 显然，如果可能将更新操作先记录在 change buffer，缩小读磁盘，语句的执行速度会失去显著的晋升。而且，数据读入内存是须要占用 buffer pool 的，所以这种形式还可能防止占用内存，进步内存利用率

Change Buffer 的相干设置

下面就是写缓存（Change Buffer）的相干常识，写缓存（Change Buffer）咱们也是能够应用命令参数来管制，MySQL 数据库提供了两个对写缓存（Change Buffer）的参数。
Change Buffer 参数

1. innodb_change_buffer_max_size

innodb_change_buffer_max_size 示意 Change Buffer 最大大小占 Buffer Pool 的百分比，默认为 25%。最大能够设置为 50%。

2. innodb_change_buffering

innodb_change_buffering 参数用来管制对哪些操作启用 Change Buffer 性能，默认是：all

change buffer 实用场景

change buffer 并不是实用于所有场景，以下两种状况不适宜开启 change buffer：

1. 数据库都是惟一索引
   如果数据库都是惟一索引，那么在每次操作的时候都须要判断索引是否有抵触，势必要将数据加载到缓存中比照，因而也用不到 Change Buffer
2. 写入一个数据后，会立即读取它
   写入一个数据后，会立即读取它，那么即便满足了条件，将更新先记录在 change buffer，但之后因为马上要拜访这个数据页，会立刻触发 merge 过程。这样随机拜访 IO 的次数不会缩小，反而减少了 change buffer 的保护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用

以下几种状况开启 Change Buffer，会使得 MySQL 数据库显著晋升：

1. 数据库大部分是非惟一索引
2. 业务是写多读少
3. 写入数据之后并不会立刻读取它

change buffer 和 redo log

• 写申请
• 如果要插入的行所对应的页在内存中，间接更新内存
• 如果要插入的行所对应的页不在内存中，就在内存的 change buffer 区域，记录下要往该页插入该行的信息
• 将上述操作记录在 redolog 中
• 以上操作写了两次内存（间接更细内存、更新 change buffer），写了一次磁盘（两次内存操作合起来写一次 redolog 到磁盘，并且是程序写）
• 读申请
• 读内存中存在的页所对应数据 的时候，间接从内存返回。
• 读内存中不存在的页所对应数据，存在于 change buffer 的数据的时候，须要把该页 从磁盘读入内存中，而后利用 change buffer 外面的操作日志，生成一个正确的版本并返回后果。能够看到，直到须要读内存不存在的页 的时候，这个数据页才会被读入内存。因为有了 change buffer，写之前，须要更新的行不在数据页，并不需要将硬盘里的数据读到数据页，只有写 change buffer。等下次读数据时，从硬盘读出数据页，而后利用 change buffer 去变更数据页

redo log 次要节俭的是随机写磁盘的 IO 耗费（转成程序写），而 change buffer 次要节俭的则是随机读磁盘的 IO 耗费。

索引优化器

• MySQL 在真正开始执行语句之前，并不能准确地晓得满足这个条件的记录有多少条，而只能依据统计信息来估算记录数
• 这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，咱们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好

• 咱们能够应用 show index 办法，看到一个索引的基数

  • MySQL 通过采样统计失去索引的基数
  • 采样统计的时候，InnoDB 默认会抉择 N 个数据页，统计这些页面上的不同值，失去一个平均值，而后乘以这个索引的页面数，就失去了这个索引的基数
  • 而数据表是会继续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会主动触发从新做一次索引统计
• explain 的后果中，rows 这个字段示意的是预计扫描行数
• 如果应用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的

• analyze table t 命令，能够用来从新统计索引信息，如果发现 explain 的后果预估的 rows 值跟理论状况差距比拟大，能够采纳这个办法来解决

  索引抉择异样和解决

• 采纳 force index 强行抉择一个索引

• 思考批改语句，疏导 MySQL 应用咱们冀望的索引

  字符串索引

• MySQL 是反对前缀索引的，能够定义字符串的一部分作为索引。默认地，如果你创立索引的语句不指定前缀长度，那么索引就会蕴含整个字符串

• 比方，这两个在 email 字段上创立索引的语句：

  mysql> alter table SUser add index index1(email);
  mysql> alter table SUser add index index2(email(6));

  第一个语句创立的 index1 索引外面，蕴含了每个记录的整个字符串；而第二个语句创立的 index2 索引外面，对于每个记录都是只取前 6 个字节

• 因为 email(6) 这个索引构造中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是应用前缀索引的劣势。但，这同时带来的损失是，可能会减少额定的记录扫描次数

• 如果应用的是 index1（即 email 整个字符串的索引构造），执行程序是这样的：

  1. 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，获得 ID2 的值；
  2. 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录退出后果集；
  3. 取 index1 索引树上刚刚查到的地位的下一条记录，发现曾经不满足 email=’zhangssxyz@xxx.com’的条件了，循环完结。
  4. 这个过程中，只须要回主键索引取一次数据，所以零碎认为只扫描了一行。

• 如果应用的是 index2（即 email(6) 索引构造），执行程序是这样的：

  1. 从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
  2. 到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录抛弃；
  3. 取 index2 上刚刚查到的地位的下一条记录，发现依然是’zhangs’，取出 ID2，再到 ID 索引上取整行而后判断，这次值对了，将这行记录退出后果集；
  4. 反复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环完结。
  5. 在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。通过这个比照，你很容易就能够发现，应用前缀索引后，可能会导致查问语句读数据的次数变多
• 对于这个查问语句来说，如果你定义的 index2 不是 email(6) 而是 email(7），也就是说取 email 字段的前 7 个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也可能间接查到 ID2，只扫描一行就完结了
• 应用前缀索引，定义好长度，就能够做到既节俭空间，又不必额定减少太多的查问老本
• 在建设索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着反复的键值越少

• 如果应用 index1（即 email 整个字符串的索引构造）的话，能够利用笼罩索引，从 index1 查到后果后间接就返回了，不须要回到 ID 索引再去查一次。而如果应用 index2（即 email(6) 索引构造）的话，就不得不回到 ID 索引再去判断 email 字段的值。即便你将 index2 的定义批改为 email(18) 的前缀索引，这时候尽管 index2 曾经蕴含了所有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为零碎并不确定前缀索引的定义是否截断了残缺信息

  参考资料

  极客工夫，mysql 实战 45 讲