MySQL 实践篇

2. 实践篇

1. 普通索引和唯一索引，应该怎么选择？

1. 普通索引和唯一索引对更新语句性能的影响这个问题，先介绍一下change buffer。

   当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

   需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

   显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

2. 什么条件下能使用change buffer？

   1. 对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束，先判断现在表中是否已经存在这条记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那**直接更新内存会更快，就没必要使用change buffer 了。**因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

   2. 记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

      1. 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

      2. 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

   3. change buffer 的使用场景

      1. 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer 的使用效果最好。
      2. 反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用, 因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多(也就是这个页面上要更新的次数越多），收益就越大

3. 普通索引和唯一索引应该怎么选择

   这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引

4. change buffer 和 redo log

   redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗

2. MySQL为什么有时候会选错索引？

1. 优化器的逻辑：

   1. 优化器选择索引的目的： 是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

   2. 扫描行数是怎么判断的：MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好

   3. MySQL 是怎样得到索引的基数的呢？使用的是采样统计，为什么使用采样统计就是一张表中一行行的统计，这样的代价太高了，所以使用采样统计（这种方式在生活中也是常常的使用）

2. MySQL 选错索引，这件事儿还得归咎到没能准确地判断出扫描行数，原因就是上面采样统计的不准确。

3. 索引选择异常处理：

   1. 第一种解决方式：**采用 force index 强行选择一个索引。**MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行。如果 force index 指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。但是一般是不喜欢使用 force index 1. 不优雅 2. 索引改了名字，这个语句也需要改 比较麻烦。
   2. 第二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引，这样需要一定的SQL功底
   3. 第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

3. 怎么给字符串字段加索引?

1. 如何在邮箱这样的字段上建立合理的索引？

   MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

   1. 前缀索引的优劣势：
      1. 占用的空间会更小，这就是使用前缀索引的优势。
      2. 可能会增加额外的记录扫描次数，劣势，但是使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
      3. 这一点才是是否是使用前缀索引的原因：使用前缀索引就用不上覆盖索引对查询性能的优化
   2. 当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

2. 遇到前缀的区分度不够好的情况时，我们要怎么办呢？（如身份证号）

   1. **第一种方式是使用倒序存储。**如果你存储身份证号的时候把它倒过来存

      mysql> select field_list from t where id_card = reverse('input_id_card_string');
      

   2. **第二种方式是使用 hash 字段。**你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

      mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
      

      每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

   3. 使用倒序存储和使用 hash 字段这两种方法的异同点？

      1. 相同点：都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在 [ID_X, ID_Y] 的所有市民了。同样地，hash 字段的方式也只能支持等值查询。
      2. 不同点：
         1. 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
         2. 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
         3. 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

4. SQL 语句为什么变慢了

1. 当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”， MySQL 偶尔变慢可能就是在刷脏页（flush）

2. 什么情况会引发数据库的 flush 过程呢？四种情况：

   1. InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。Innodb 先将数据的更新写入redo log 在等待空闲的时候写入磁盘。

      

   2. 是系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

      为什么不能直接把内存淘汰掉，下次需要请求的时候，从磁盘读入数据页，然后拿 redo log 出来应用不就行了？这里其实是从性能考虑的。如果刷脏页一定会写盘，就保证了每个数据页有两种状态：

      1. 一种是内存里存在，内存里就肯定是正确的结果，直接返回；
      2. 另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。这样的效率最高。

   3. MySQL 认为系统“空闲”的时候。当然，MySQL会缝插针地找时间，只要有机会就刷一点“脏页”。

   4. MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

3. 上面的四种情况对性能的影响：

   1. 第一种是“redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。

   2. 第三种情况是属于 MySQL 空闲时的操作，这时系统没什么压力，而第四种场景是数据库本来就要关闭了。这两种情况下，你不会太关注“性能”问题。

   3. 第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。**InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：**第一种是，还没有使用的；第二种是，使用了并且是干净页；第三种是，使用了并且是脏页。InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

      1. 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长

      2. 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。

      所以，InnoDB 需要有控制脏页比例的机制，来尽量避免上面的这两种情况。

5. 数据库表的空间回收

1. 表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table 控制的：

   1. 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；

   2. 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了，同时建议设置为ON，方便管理，同时要是放到共享表空间中，即使表删除了，空间也不会回收。

2. 数据的删除流程：InnoDB 里的数据都是用 B+ 树的结构组织的，要是将某个记录进行删除，Innodb只是会将这个记录标记为删除（也就是逻辑删除），要是在进行记录的插入，可能会复用这个位置，这样的话，磁盘文件大小不发生变化，同样的要是删除一个数据页上的所有记录，那么整个数据页就能被重用了。但是，**数据页的复用跟记录的复用是不同的。**记录的复用，只限于符合范围条件的数据，符合条件才能复用，而当整个页从 B+ 树里面摘掉以后，可以复用到任何位置，这一点需要注意。

3. 表空间收缩怎么做：

   1. 可以使用 alter table A engine=InnoDB 命令来重建表，但是这个过程中，有新的数据要写入到表中的话，就会造成数据丢失。因此，在整个 DDL 过程中，表不能有更新。这个 DDL 不是 Online 的
   2. **MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。**和上面的过程的不同之处在于，由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表 A 做增删改操作。推荐使用

6. count(*) 是怎么实现的，统计表行数业务怎么设计

1. count(*)实现方式：在不同的 MySQL 引擎中，count(*) 有不同的实现方式：

   1. MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高，需要注意的是这里是没有过滤条件的 count(*)，如果加了where 条件的话，MyISAM 表也是不能返回得这么快的
   2. 而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数

2. 为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？

   这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。这和 InnoDB 的事务设计有关系，可重复读是它默认的隔离级别，在代码上就是通过多版本并发控制，也就是 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于 count(*) 请求来说，InnoDB 只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。

3. 统计表行数业务怎么设计?

   1. 用缓存系统保存计数：对于更新很频繁的库来说，你可能会第一时间想到，用缓存系统来支持，但是这样的形式会出现双写不一致问题。
   2. **在数据库保存计数：**可以使用事务的特性来解决数据不精确问题。
   3. 优化 count(*)使用：对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加，对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。对于 count(字段) 来说： 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；2. 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。

7. order by 是怎么工作的：

1. 全字段排序： 可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序，而外部排序一般使用归并排序算法。（拆分大文件 - 每一个小文件进行排序 - 合并成一个有序的大文件）

   

2. rowid 排序：就是如果查询要返回的字段很多的话，那么sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差，简单的说，Mysql中排序的单行长度太大会怎么做呢？使用 rowid 排序，排序的时候不是将所有的字段都放到sort_buffer中 而是 主键 + 需要排序的字段进行排序 order by之后 在将结果集按照符合条件的id，从主键索引中获取记录。

3. 全字段排序 VS rowid 排序

   1. 如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
   2. 如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
   3. 这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问， 对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择

4. 不是所有的order by 操作都是需要进行排序的，MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的，而索引天然就是排好序的，从索引中取数据，就可以不再需要进行排序了。创建索引之后，最好是能使用到覆盖索引：

   

8. 那些情况下不走索引：

1. 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃树搜索功能

2. 隐式类型转换：字段类型varchar类型 但是输入的参数是整型，因此需要类型转换 ，在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。

3. 隐式字符编码转换比如字符集 utf8mb4和 utf8，当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较

   这个设定很好理解，utf8mb4 是 utf8 的超集。类似地，在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。比如：Java中 int + long 返回的是long类型。

4. 总结：下面的两种情况，是上面的一种特例。

9. 幻读是什么？如何解决：

1. 什么是幻读：幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
2. 幻读会导致什么问题：会导致数据不一致问题。
3. 幻读产生的原因：，产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。
4. 如何解决幻读：
   1. 为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)，间隙锁就是不止是给数据库中已有的 记录加上了行锁，还同时在满足条件上的间隙加锁，这样就确保了无法再插入新的记录，间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]，因为 +∞是开区间。实现上，InnoDB 给每个索引加了一个不存在的最大值supremum，这样才符合我们前面说的“都是前开后闭区间”。间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题，但是同样也带来的问题：间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。
   2. 更加简单的解决方式：间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，需要解决可能出现的数据和日志不一致问题，这个时候需要把binlog 格式设置为 row。

10. 加锁规则：

1. 加锁规则：两个“原则”、两个“优化”和一个“bug”。
   1. 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
   2. 原则 2：查找过程中访问到的对象才会加锁。
   3. 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
   4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，nextkey lock 退化为间隙锁。
   5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

11. MySQL是怎么保证数据不丢的？

1. binlog 的写入机制： binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

   

   可以看到，每个线程有自己 binlog cache，但是共用同一份 binlog 文件。图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。write 和 fsync 的时机，是由参数 sync_binlog 控制的：

   1. sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；

   2. sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；

   3. sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才fsync。

   因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。

2. redo log 的写入机制：

   1. redo log 可能存在的三种状态：这三种状态分别是：

      1. 存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；

      2. 写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分；

      3. 持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分。