阿龙的学习笔记---MySQL45讲的总结（二）

普通二级索引和唯一索引

• 这两个怎么选择呢？

  • 从查询的角度来说，差异很微乎其微，都是二分查找。普通索引找到后还需要往后按序再查找唯一索引可能不用往后再查找。
  • 从更新的角度，差异较大。关于change buffer。

• change buffer：

  • 更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。。（change buffer会持久化，所以能保证一致性）
  • 在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。
  • 但是！对唯一索引来说，更新数据要检查唯一性，那么还是需要搜索数据库，需要读入内存再判断，所以chage buffer没用上，效率低。
  • change buffer是将每次update的费用平摊了，读入一次时，change buffer存的操作比较多才划算。所以，如果在每次update之后都会再select，那么每次都需要读入内存，多了一次change buffer的调用，性能反而下降了。

• change buffer 和 redo log：

  • 例子：现在，我们要在表上执行这个插入语句：insert into t(id,k) values(id1,k1),(id2,k2)
  • 假设 k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 不在内存中。则下图所示是带 change buffer 的更新状态图。
    

    • 这条更新语句做了如下的操作（按照图中的数字顺序）：

      1. Page 1 在内存中，直接更新内存；
      2. Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息。
      3. 将上述两个动作记入 redo log 中（图中 3 和 4）。做完上面这些，事务就可以完成了。

    • 在读取这两个数据的时候：

      1. k1 会直接从内存中读取数据。
      2. k2 需要先将page读入内存，在回复 change buffer中的更改，返回给用户

    • 所以change buffer是为了节省读的时间，需要读入再更改的时候先存到buffer。而 redo log 可以节省写的时间，在需要将更改写入的时候，先存到 redo log 中。

---

优化器选择索引

• 这篇索引统计的更新机制，并提到了优化器存在选错索引的可能性。
• 对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决。
• 而对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。
• tips: 是将慢查询日志的阈值设置为 0，表示这个线程接下来的语句都会被记录入慢查询日志中；

---

怎么给字符串字段加索引

• 比如，用户注册信息会有email，可能会通过email查找。
• 如果 email 这个字段上没有索引，那么这个语句就只能做全表扫描。同时，MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串作为索引。
• 比如一个索引是email，另一个是email(6)。
• 在执行这条语句时：

  select id,name,email from SUser where email='zhangssxyz@xxx.com';
  

• 如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：
  • 从 索引树找到满足的这条记录，取得 ID2 的值；到主键上查到这行，判断 email 的值是正确的，将这行记录加入结果集；
  • 取 index1 索引树上下一条记录，发现已经不满足条件了，循环结束。
• 如果使用的是 index2（即 email(6) 索引结构）：
  • 依旧会用到索引，但是是前缀索引。
  • 从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
  • 然后去主键索引查找这个位置是否满足。
  • 然后重复找index2索引树下一个。
• tips：使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
• tips：遇到不好做索引的值，比如很长，或者前缀没有区分度（比如身份证号）。可以倒序排放做索引。也可以hash做索引。

---

查询忽然抖一下

• 忽然某一次很慢，很有可能是在刷脏页。平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。
• 有以下几种场景：
  1. InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进。
  2. 内存不够了，一些数据页写回硬盘。
  3. MySQL 认为系统“空闲”的时候，见缝插针地找时间，有机会就刷一点“脏页”。
  4. 手动关闭的时候。