笔记：Mysql45讲（部分）

原 作者 林楷 | 发表于 | 分类于 2019级

00 前言

前段时间花了点时间看了一小部分Mysql45讲的内容，初看感觉有些地方晦涩难懂，由于时间原因也没看完，先把做的部分笔记放上来，日后有时间学习更新。

01 基础架构：一条SQL查询语句是如何执行的？

  • MySQL可以分为Server层和存储引擎层两部分。
    • Server层包括连接器、查询缓存、分析器、优化器、执行器等。
      • 连接器负责跟客户端建立连接、获取权限、维持和管理连接。
        • 连接的权限判断逻辑，都将依赖于初始时读到的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
        • 建立连接的过程通常是比较复杂的，尽量使用长连接。
        • 使用长连接后（临时使用的内存在连接断开释放），MySQL异常重启（OOM）解决方案：
          • 定期断开长连接。
          • 在每次执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源，不需要重连和重新做权限验证（5.7或更高版本）。
      • 查询缓存往往弊大于利，只有静态表的查询才适合使用查询缓存。对于更新压力大的数据库来说，查询缓存的命中率会非常低（8.0删除了改功能）。
      • 分析器：对SQL语句进行词法分析、语法分析。
      • 优化器：执行计划生成，索引选择。
      • 执行器：操作引擎，返回结果。
        • 引擎扫描行数跟rows_examined并不是完全相同的。
    • 存储引擎层负责数据的存储和提取，其架构模式是插件式的，默认使用InnoDB。

02 日志系统：一条SQL更新语句是如何执行的？

  • 在一个表上有更新的时候，跟这个表有关的查询缓存会失效。
  • 更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）。
    • redo log用于实现crash-safe能力：
      • WAL（Write-Ahead Logging）技术，它的关键点就是先写日志，再写磁盘。当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log（粉板）里面，并更新内存。InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘（账本）里面。
    • binlog用于恢复到某一时刻。
    • 两种日志的不同：
      • redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
      • redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
      • redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
  • 更新流程：
    • 执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
    • 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
    • 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
    • 执行器生成这个操作的binlog，并把binlog写入磁盘。
    • 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。
  • 两阶段提交：将redo log的写入拆成了prepare和commit，让事务提交状态（redo log和binlog的）保持逻辑上的一致。

03 事务隔离：为什么你改了我还看不见？

  • 事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在MySQL中，事务支持是在引擎层实现的。
  • ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）。
  • 当数据库上有多个事务同时执行的时候，就可能出现脏读、不可重复读、幻读的问题。
  • SQL标准的事务隔离级别包括：读未提交、读提交、可重复读和串行化。
  • 在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。
    • 在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
    • 在“读提交”隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。
    • “读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；
    • 而“串行化”隔离级别下直接用加锁的方式来避免并行访问。
  • 在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。不同时刻启动的事务会有不同的read-view。
  • 同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。
  • 是当系统里没有比这个回滚日志更早的read-view的时候，回滚日志会被删除。
  • 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。
  • 建议总是使用set autocommit=1, 通过显式语句的方式来启动事务。

04 深入浅出索引（上）

  • 索引的常见模型：哈希表、有序数组和搜索树。
    • 哈希索引做区间查询的速度是很慢的。
    • 有序数组索引只适用于静态存储引擎。
    • 由于索引不止存在内存中，还要写到磁盘上，实际使用N叉树而不是二叉树（访问尽量少的数据块）。
    • 跳表、LSM树等数据结构也被用于引擎设计中。
  • InnoDB的索引模型：
    • InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。
    • 每一个索引在InnoDB里面对应一棵B+树。
    • 根据叶子节点的内容，索引类型分为主键索引和非主键索引。
      • 主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。
      • 非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。
      • 基于非主键索引的查询需要多扫描一棵索引树（这个过程叫回表），应该尽量使用主键查询。
      • 主键长度越小，普通索引的叶子节点就越小，占用的空间也就越小。因此，自增主键往往是更合理的选择。
        • KV场景只有一个索引，且必须是唯一索引，适合用业务字段做主键。
  • 为什么要重建索引：索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。
  • 不论是删除主键还是创建主键，都会将整个表重建。

05 深入浅出索引（下）

  • 回表：回到主键索引树搜索的过程。当查询结果所需要的数据只在主键索引上有，不得不回表。
  • 覆盖索引：在某个查询中，索引k已经“覆盖了”我们的查询需求。
    • 覆盖索引可以减少树的搜索次数，显著提升查询性能，是常用的性能优化手段。
    • 对于高频请求，使用联合索引充当覆盖索引，可以减少语言的执行时间。
  • 最左前缀原则（复用索引）：可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。
    • 不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。
    • 如果通过调整顺序，可以少维护一个索引，优先考虑之。
  • 索引下推：InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。

06 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

  • 根据加锁的范围，MySQL的锁可以分成：全局锁，表级锁和行锁。
    • 全局锁（FTWRL）的典型使用场景是，做全库备份，也就是把整库每个表都select出来存成文本。
    • MySQL里面表级别的锁有两种：一种是表锁（针对表数据），一种是元数据锁（meta data lock，MDL)（自动加的读写锁，针对表结构，也就是元数据）。
      • 表锁的lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象（只能操作前面上锁的表）。
      • 元数据锁MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。
      • 当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
      • 表锁一般是在数据库引擎不支持行锁的时候才会被用到的。
  • 安全的给热点表加字段：DDL NOWAIT/WAIT。
    • 在alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。

07 行锁功过：怎么减少行锁对性能的影响？

  • 两阶段锁协议：在InnoDB事务中，行锁是需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。
    • 因此要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
  • 行锁死锁解决方案：
    • 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
    • 另一种策略是，发起死锁检测（要耗费大量的CPU资源），发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。
    • 死锁检测带来的热点行更新性能问题解决方案：
      • 确保业务不会出现死锁，关闭死锁检测（业务有损的）。
      • 控制并发度，在MySQL或中间件进行排队。

08 事务到底是隔离的还是不隔离的？

  • begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用start transaction with consistent snapshot 这个命令。
  • MySQL两个“视图概念”：
    • 一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view…，而它的查询方法与表一样。
    • 另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。
  • “快照”在MVCC里是怎么工作的？
    • InnoDB里面每个事务有一个唯一的事务ID，叫作transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。
    • V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。
    • 在实现上， InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就是，启动了但还没提交。
    • 数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。
  • InnoDB利用了“所有数据都有多个版本”的特性，实现了“秒级创建快照”的能力。
  • 事务去更新数据的时候，就不能再在历史版本上更新了，否则旧事务的更新就丢失了。
  • “当前读”：更新数据都是先读后写的，而这个读，只能读当前的值。
    • 除了update语句外，select语句如果加锁，也是当前读。
  • 可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。
  • 读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：
    • 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
    • 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。
    • “start transaction with consistent snapshot; ”的意思是从这个语句开始，创建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于普通的start transaction。

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

  • 普通索引和唯一索引：
    • 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
    • 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
  • change buffer：
    • 当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
    • 实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。
    • 将change buffer中的操作应用到原数据页，得到最新结果 的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。
    • 唯一索引的更新就不能使用change buffer（必须先读入判断是否唯一），实际上也只有普通索引可以使用（好处体现在更新不存在内存的数据页上）。
    • 对于写多读少的业务来说，change buffer的使用效果最好。
    • redo log主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

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

  • 通过慢查询日志（slow log）来查看一下具体的执行情况。
  • 扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
  • 一个索引上不同的值（基数）越多，这个索引的区分度就越好。
  • 索引选择异常和处理：
    • force index。
    • 新建更合适的索引，或删掉误用的索引。

11 怎么给字符串字段加索引？

  • 前缀索引（指定字符串前缀长度）占用的空间会更小，但可能导致查询语句读数据的次数变多。
  • 使用前缀索引就用不上覆盖索引对查询性能的优化了。
  • 身份证存储优化：
    • 倒序存储（正序前六位区分度低）。
    • hash字段（hash函数得到结果可能相同，还得判断是否身份证是否相同，但有索引下降优化）。

12 为什么我的MySQL会“抖”一下？

  • “抖“：刷脏页（flush）。
    • InnoDB的redo log写满了。
    • 系统内存不足（常态）。
      • InnoDB用缓冲池（buffer pool）管理内存。
    • 系统”空闲“。
    • MySQL正常关闭。
  • InnoDB刷脏页的控制策略：
    • InnoDB的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是redo log写盘速度。
    • innodb_flush_neighbors：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉。
      • 这个优化在机械硬盘时代可以减少很多随机IO。
      • SSD时代往往IOPS不是瓶颈。

13 为什么表数据删掉一半，表文件大小不变？

  • innodb_file_per_table：表数据既可以存在共享表空间里，也可以是单独的文件。
    • OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
    • ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。
    • 推荐设置为ON。一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。
  • 如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。
  • delete命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。
  • 如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。
  • 重建表：alter table A engine=InnoDB。
    • 为了把表中存在的空洞去掉。
    • Online DDL：在重建表的过程中，允许对表A做增删改操作。
      • 建立一个临时文件，生成临时文件的过程中，将所有的操作记录在一个日志文件（row log）中。
  • Online和inplace：
    • inplace：根据表A重建出来的数据是放在“tmp_file”里的，这个临时文件是InnoDB在内部创建出来的。整个DDL过程都在InnoDB内部完成。对于server层来说，没有把数据挪动到临时表，是一个“原地”操作，
  • 全文索引：
    • 先定义一个词库，然后在文章中查找每个词条(term)出现的频率和位置，把这样的频率和位置信息按照词库的顺序归纳，这样就相当于对文件建立了一个以词库为目录的索引，这样查找某个词的时候就能很快的定位到该词出现的位置。
    • “分词”成为全文索引的关键技术。

14 count(*)这么慢，我该怎么办？

  • 可重复读是InnoDB默认的隔离级别。
  • 对于count(*)这样的操作，MySQL优化器会找到最小的那棵普通索引树来遍历（仍需要遍历全表）。
  • 用缓存系统（如Redis）保存计数：不准确。
    • 解决方案：把计数直接放到数据库里单独的一张计数表中，利用事务的特性解决。
  • count(*)、count(主键id)和count(1)：
    • count(*)：InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。
    • count(1)：InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
    • count(1)执行得要比count(主键id)快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。
    • 引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。
    • count(字段)<count(主键id)<count(1)≈count(*)（前面两个会解析行数据）。

15 答疑文章（一）：日志和索引相关问题

  • 更新计数表涉及到行锁的竞争，先插入再更新能最大程度地减少事务之间的锁等待，提升并发度。
  • 一个事务的binlog是有完整格式的：
    • statement格式的binlog，最后会有COMMIT；
    • row格式的binlog，最后会有一个XID event。
  • 处于prepare阶段的redo log加上完整binlog，重启就能恢复。

16 “order by”是怎么工作的？

  • Extra字段中的“Using filesort”表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序（快速排序），称为sort_buffer。
  • 如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序（归并排序）。
  • rowid排序：
    • max_length_for_sort_data，是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。
    • rowid排序相比全字段排序多访问了一次表t的主键索引。
  • MySQL设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。
  • 联合索引：index(city、name)能保证从city索引上取出来的行，天然按照name递增排序。
    • 覆盖索引：索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。
      • 优化：index(city、name、age)。
  • 如果MySQL要修改的值跟原来的值是相同的，MySQL真的会去执行修改，而不是看到值相同就返回。
  • 对于InnoDB表来说，执行全字段排序会减少磁盘访问，因此会被优先选择。

17 如何正确地显示随机消息？

  • 对于内存表，回表过程只是简单地根据数据行地位置，直接访问内存得到数据，根本不会导致多访问磁盘。
  • order by rand()使用了内存临时表（随机数组织的），内存临时表排序的时候使用了rowid排序方法。
  • 如果你创建的表没有主键，或者把一个表的主键删掉了，那么InnoDB会自己生成一个长度为6字节的rowid来作为主键。
  • 如果临时表大小超过了tmp_table_size，那么内存临时表就会转成磁盘临时表。
  • 优先队列算法可以精确地只得到三个最小值（维护大小为3的最大堆）。
  • order by rand()这种写法都会让计算过程非常复杂。
  • 随机排序方法（因为ID中间可能有空洞，不是正常的随机）：
    • 取得这个表的主键id的最大值M和最小值N;
    • 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() +N;
    • 取不小于X的第一个ID的行（用索引快速定位）。
  • 尽量将业务逻辑写在业务代码中，让数据库只做“读写数据”的事情。

18 为什么这些SQL语句逻辑相同，性能却差异巨大？

  • 条件字段函数操作：
    • 如果对字段做了函数计算（有可能破坏索引值的有序性），就用不上索引了。
    • 对于不改变有序性的函数，也不会考虑使用索引。
  • 隐式类型/隐式字符编码 转换：
    • 有数据类型转换，就需要走全索引扫描（同上）。
    • 有隐式字符编码转换，就需要走全索引扫描（同上）。

19 为什么我只查一行的语句，也执行这么慢？

  • 查询长时间不返回：
    • 执行showprocesslist命令，看看当前语句处于什么状态。
    • 这类问题的处理方式，就是找到谁持有MDL写锁，然后把它kill掉。
    • 等flush：
      • 出现Waiting for table flush状态的可能情况是：有一个flush tables命令被别的语句堵住。
    • 等行锁：lock in share mode。
  • 查询慢：
    • 可能是事务A启动后，事务B更新了多次，事务A查询该值，需要用到多次回滚日志。

20 幻读是什么，幻读有什么问题？

  • 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
  • update的加锁语义和select … for update 是一致的。
  • 幻读：
    • 幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
    • 幻读仅专指“新插入的行”（update不算）。
    • 幻读的问题：
      • 语义被破坏：假设只在id=5这一行加行锁–语义被破坏。
      • 数据一致性：
        • 锁的设计是为了保证数据的一致性。而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。
    • 即使把所有的记录都加上锁，还是阻止不了新插入的记录（在给所有行加锁的时候，新插入的行还不存在）。
    • 间隙锁（Gap Lock）：
      • 产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。
      • 跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间不存在冲突关系。它们有共同的目标，即：保护这个间隙，不允许插入值。
      • 间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。
      • 为间隙锁在可重复读隔离级别下才有效。

21 为什么我只改一行的语句，锁这么多？

  • 加锁规则：
    • 原则1：加锁的基本单位是next-key lock。
    • 原则2：查找过程中访问到的对象才会加锁。
    • 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
    • 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
    • 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
  • 锁是加在索引上的。
  • 这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么session B的update语句可以执行完成

22 MySQL有哪些“饮鸩止渴”提高性能的方法？

  • 短连接风暴：
    • max_connections参数，用来控制一个MySQL实例同时存在的连接数的上限。
    • 第一种方法，先处理掉那些占着连接但是不工作的线程。
      • 应该优先断开事务外空闲的连接（不用回滚）的连接。
      • 一个客户端处于sleep状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道。
    • 第二种方法，减少连接过程的损耗。
      • 让数据库跳过权限验证阶段：–skip-grant-tables。
  • 慢查询性能问题：
    • 索引没有设计好：直接执行alter table语句，比较理想的是能够在备库先执行。
    • 语句没写好：用查询重写（query_rewrite）改写语句，把输入的一种语句改写成另外一种模式。
    • MySQL选错了索引：使用查询重写功能，加上force index。
  • 预先发现问题：打开slow log，观察row_examined字段。
  • QPS突增问题：
    • 新功能使用的是单独的数据库用户：用管理员账号把这个用户删掉，然后断开现有连接。
    • 新增的功能跟主体功能是部署在一起的：把压力最大的SQL语句直接重写成"select 1"返回（可能误伤）。
  • 连接异常断开是常有的事，你的代码里要有正确地重连并重试的机制。

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

  • 只要redo log和binlog保证持久化到磁盘，就能确保MySQL异常重启后，数据可以恢复。
  • binlog的写入机制：
    • 事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。
    • 一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。
  • 参数 binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
  • 每个线程有自己binlog cache，但是共用同一份binlog文件。
    • write，指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。
    • fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占磁盘的IOPS。
  • 将sync_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。
  • redo log buffer里面的内容，不需要每次生成后都要直接持久化到磁盘（事务还没提交）。
  • redo log写入磁盘：
    • InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘。
    • redo log buffer占用的空间即将达到innodb_log_buffer_size一半的时候，后台线程会主动写盘。
    • 并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘。

24 MySQL是怎么保证主备一致的？

  • binlog可以用来归档，也可以用来做主备同步。
  • 当binlog_format使用row格式的时候，binlog里面记录了真实删除行的主键id，这样binlog传到备库去的时候，就肯定会删除id=4的行，不会有主备删除不同行的问题。
  • binlog三种格式：
    • 有些statement格式（记录完整SQL语句，占用空间小）的binlog可能会导致主备不一致，所以要使用row格式。
    • row格式的缺点是，很占空间。比如你用一个delete语句删掉10万行数据，用statement的话就是一个SQL语句被记录到binlog中，占用几十个字节的空间。但如果用row格式的binlog，就要把这10万条记录都写到binlog中。这样做，不仅会占用更大的空间，同时写binlog也要耗费IO资源，影响执行速度。
    • mixed格式的意思是，MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式。

25 MySQL是怎么保证高可用的？

  • 正常情况下，只要主库执行更新生成的所有binlog，都可以传到备库并被正确地执行，备库就能达到跟主库一致的状态，这就是最终一致性。
  • MySQL要提供高可用能力，只有最终一致性是不够的。
  • 所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值。
  • 主备延迟的来源：
    • 备库所在的机器性能要比主库所在的机器性能差。
    • 备库的压力大。
    • 大事务：为主库上必须等事务执行完成才会写入binlog，再传给备库。
    • 大表DDL。

26 备库为什么会延迟好几个小时？

  • 在官方的5.6版本之前，MySQL只支持单线程复制，由此在主库并发高、TPS高时就会出现严重的主备延迟问题。
  • 所有的多线程复制机制，都是要把图1中只有一个线程的sql_thread，拆成多个线程。
  • coordinator就是原来的sql_thread, 不过现在它不再直接更新数据了，只负责读取中转日志和分发事务。真正更新日志的，变成了worker线程。
  • coordinator在分发的时候，需要满足以下这两个基本要求：
    • 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个worker中。
    • 同一个事务不能被拆开，必须放到同一个worker中。
  • 并行复制策略：
    • 按表分发策略：
      • 按表分发事务的基本思路是，如果两个事务更新不同的表，它们就可以并行。
      • 如果有跨表的事务，还是要把两张表放在一起考虑的。
    • 按行分发策略：
      • 按行复制的核心思路是：如果两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求binlog格式必须是row。

27 主库出问题了，从库怎么办？

  • 一主多从的设置，一般用于读写分离，主库负责所有的写入和一部分读，其他的读请求则由从库分担。
  • GTID的全称是Global Transaction Identifier，也就是全局事务ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。
  • 建议尽量使用GTID模式来做一主多从的切换。

28 读写分离有哪些坑？

  • 客户端直连和带proxy的读写分离架构：
    • 客户端直连方案，因为少了一层proxy转发，所以查询性能稍微好一点儿，并且整体架构简单，排查问题更方便。但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。
    • 带proxy的架构，对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由proxy完成的。但这样的话，对后端维护团队的要求会更高。而且，proxy也需要有高可用架构。因此，带proxy架构的整体就相对比较复杂。
  • “过期读”：在从库上会读到系统的一个过期状态。
    • 强制走主库方案：
      • 对于必须要拿到最新结果的请求，强制将其发到主库上。
      • 对于可以读到旧数据的请求，才将其发到从库上。
    • sleep方案：
      • 大多数情况下主备延迟在1秒之内，类似于执行一条select sleep(1)命令。
    • 判断主备无延迟方案。
      • 每次从库执行查询请求前，先判断seconds_behind_master是否已经等于0。如果还不等于0 ，那就必须等到这个参数变为0才能执行查询请求。
      • 对比位点确保主备无延迟。
      • 对比GTID集合确保主备无延迟。
    • 配合semi-sync方案。
    • 等主库位点方案。
    • 等GTID方案。