读书笔记：MySQL45讲

基础篇

一条SQL查询语句是如何执行的

• 连接器建立连接

  查缓存（MySQL8.0之后移除了缓存）

  分析器分析

  优化器优化

  执行器执行

• 连接器

  • mysql -h $ip -P $port -u $user -p

    密码建议在交互对话框中输入，减少密码泄露风险。

  • 用户名密码校验通过后就会去权限表中检查用户所拥有的权限

  • 连接完成后，如果没有执行其他操作，这个连接就处于空闲状态。可以通过show processlist命令查看（Command显示Sleep的为一个空闲连接）。

  • MySQL中使用的是长连接，因为建立连接的过程是比较复杂的。

    一个连接处于空闲时间超过一定时间就会被断开（默认是8小时）

    使用长连接，有时候会发现MySQL占用内存涨得特别快，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的，这些资源在连接断开的时候才释放。因此长时间积累下了，就可能导致内存占用太大，被系统强杀（看起来就像MySQL异常重启）。

    解决方案：

    • 定期断开长连接，或者当程序执行过一个占用内存大的查询后就断开该连接。

    • 如果使用的MySQL版本在5.7或以上，那么当程序执行过一个占用内存大的查询后可以通过mysql_reset_connection来重新初始化连接资源。

• 查缓存

  • 当请求语句过来时，会首先去缓存中查找是否已经处理过，发现已经处理过则直接返回结果。

  • 缓存对于MySQL来说往往弊大于利。因为每次更新操作都会导致缓存失效。

  • 按需查找：你可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

    mysql> select SQL_CACHE * from T where ID=10；

  • MySQL8.0之后移除了查询缓存功能

• 分析器

  • 分析语法的准确性，是否符合语法规则，否则提示错误

• 优化器

  • 比如在查询语句中有多个索引的时候，优化器会尽量选择最优的索引组合方案，达到效率上的提高

• 执行器

  • 执行语句，在执行前会校验是否有相应表操作的权限（如果是命中缓存，那么在返回缓存结果前会进行权限校验）

一条SQL更新语句是如何执行的

• 同查询语句它也需要经过连接、查缓存、分析器、优化器、执行器。此外更新流程还涉及到两个重要的日志模块：redo log和bin log。

• redo log（引擎层日志，InnoDB独有）

  • WAL技术：Write-AHead Logging。其关键点就是先写日志再写磁盘。

    具体来说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新到内存，此时就算完成了。同时，InnoDB会在适当的时候将这个记录操作更新到磁盘（往往是在系统比较空闲的时候）。

    当然如果redo log写满了，则会主动将其中一部分记录同步到磁盘，释放空间。

    InnoDB的redo log是固定大小的，他们的组成方式类似环形队列。

  • 可以保证数据库异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe

• bin log（Server层日志）

  • bin log日志只能用于归档

    所有引擎都能使用（redo log是InnoDB引擎独有的）

  • bin log是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2的这一行的C字段加1”（redo log是物理日志，记录的是“在某个数据页上做了什么修改”）

  • bin log是追加写，指bin log文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。（redo log是循环写，空间固定，会被用完）

• 执行器和InnoDB引擎在执行这个简单的update语句的内部流程 update T set c=c+1 where ID=2;

  • 执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在数据页本来又在内存中，就返回给执行器；否则，需要从磁盘读入内存，然后再返回。

  • 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1。得到一行新数据，再调用引擎接口写入这行新数据。

  • 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成可，随时可以提交事务。

  • 执行器生成这个操作的bin log，并把bin log写入磁盘。

  • 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交(commit)状态，更新完成。

• 两阶段提交

  • 使用两阶段提交的目的是为了让两份日志之间（redo log和bin log）逻辑一致

  • 为什么需要两阶段提交： 由于redo log和bin log是两个独立的逻辑，如果不用两阶段提交，要么先写完redo log在写bin log，或者采用反过来的顺序。这两种方式会存在这些问题

    • 先写redo log后写bin log。假设在redo log写完，bin log还没有写完的时候，MySQL进程异常重启，此时redo log仍然能够把数据恢复为c+1，而bin log没有写完就crash了，这时候bin log里面就没有记录这条语句。此后如果需要通过bin log恢复临时数据库的时候就会少了这一条数据更新操作，那么将与原数据库数据不一致。

    • 先写bin log后写redo log。假设在bin log写完，redo log还没写的时候crash，在恢复的时候这次更新操作不会生效，这一行的值仍然是原来的值。此后如果需要通过bin log恢复临时数据库，那么就会多出这一条数据更新操作，那么将与原数据库数据不一致。

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

• 事务就是要保证一组数据库操作要么全部成功，要么全部失败。在MySQL中事务支持是在引擎层实现的。InnoDB支持事务，MyISAM不支持事务。

  事务的四大性质：A（原子性）、C（一致性）、I（隔离性）、D（持久性）

• 隔离性与隔离级别

  • 读未提交：一个事务还没提交，它做的变更就能被别的事务看见

  • 读已提交：一个事务提交之后，它做的变更才能被别人看见

  • 可重复读：一个事务执行过程中看见的数据，总是跟这个事务在启动时看见的数据是一致的。

    （可重复读是MySQL的默认隔离级别）

    （可重复读隔离级别可能出现幻读）

  • 可串行化：对于同一行记录，读写都会加锁。当读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

• 事务隔离的实现（待补充）

• 事务的启动方式： 如果客户端连接框架会默认连接成功后先执行一个set autocommit=0的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。所以一般设置autocommit=1，然后通过显式语句的方式来启动事务。

  • 显式启动事务语句，begin或start transaction来启动事务，使用commit提交，使用rollback回滚。

  • set autocommit=0，通过次命令将该线程的自动提交关闭。那么当你只执行一个select语句时事务就启动了，而且并不会自动提交。这个事务持续存在知道你主动执行commit或者rollback，或断开连接。

• 事务隔离级别要实际解决的问题

  • 脏读：指的是读到了其他事务未提交的数据，未提交意味着这些数据可能回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了最终并不一定存在的数据，这就是脏读。

  • 可重复读：指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。

  • 不可重复读：不可重复读指的是在同一事务内，不同的时刻读到的同一批数据的内容可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。

  • 幻读：一个事务多次操作结果（行数）与预想的不一致。

索引

• 三种索引模型

  • 哈希表这种结构适用于只有等值查询的场景

  • 有序数组索引只适用于静态存储引擎

  • 平衡树

• InnoDB选择B+树

• 索引类型：分为主键索引和非主键索引

  • 基于主键索引和普通索引的查询有什么区别？

    主键查询方式，则只需要搜索主键这棵 B+ 树，而普通索引查询方式，则需要先搜索该非主键索引树，得到主键值，再到主键索引树搜索一次。这个过程称为回表。

    也就是说基于非主键索引的查询需要多扫描一颗索引树。

• 索引维护：主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

• 索引覆盖

  • 需要回表查询：select * from T where k between 3 and 5

  • 索引覆盖了需要查询的值，不需要回表查询：select ID from T where k between 3 and 5

    由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

• 联合索引

  • 目的是字段组合进行索引覆盖，这样就可以减少回表查询，从而提高查询效率。

• 最左前缀原则（前缀索引）：如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的

• 索引下推

锁

• 全局锁：顾名思义，就是对整个数据库实例加锁。

  • MySQL加全局锁方法：

    命令是Flush tables read lock（FTWRL）

  • 加全局锁之后整个库处于只读状态

  • 典型应用：

    做全库逻辑备份，也就是把整库每个表都select出来存成文本。

  • 如果是为了设置只读，为什么不使用mysqldump同参数–single-transaction或者使用set global readonly=true方式呢？

    • 不使用mysqldump single-transaction 是因为该方法只适用于所有的表使用事务引擎的库。对于像MyISAM等引擎则不支持

      （如果数据库引擎全部都是InnoDB，那么选择此方法对应用会更友好）

    • 不通过set global readonly=true来设置只读的原因是：

• 在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此修改global变量的方式影响面更大，不建议使用

• 在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

• 表级锁（不支持行锁的引擎，需要做并发控制只能使用表级锁）

  • 表锁

    • 语法是lock tables ... read/write

      可以通过unlock tables主动释放锁，也可以在客户端断开的时候自动释放。

  • 元数据锁

    • MDL不需要显式使用，在访问一个表的时候会被自动加上。

    • 当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

    • 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

      读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

• 行锁（InnoDB支持行锁）

  • 行锁减少锁粒度

  • 两阶段锁协议：

    在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立即释放，而是要等到事务结束时才释放。

所以如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

• 死锁：

  当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待状态，称为死锁。

• 死锁避免：

  1. 设置等待超时时间，超时失败（InnoDB默认为50s）。缺点：超时时间过长会导致其他线程长时间阻塞，超时时间过短则会误判很多处于锁等待状态的事务。

  2. 死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。

• 死锁检测：

  采用死锁检测需要注意并发控制，假设有1000个个并发线程要同时更新同一行，那么死锁检测操作就是100万量级的。虽然最终检测的结果没有死锁，但是这期间要占用大量的CPU资源，降低了事务执行效率。

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

• 视图

  • view：它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。

  • 一致性读视图（InnoDB在实现MVCC时用到）：consistent read view用于支持RC（Read Committed 读提交）和RR（Repeated Read 可重复读）隔离级别的实现。

• “快照”在MVCC里是怎么工作的？（待补充）

实践篇

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

• 从查询过程看： 假设，执行查询的语句是 select id from T where k=5。 对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

  • 那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微，这个操作成本对于现在的 CPU 来说可以忽略不计。

    InnoDB 的数据是按数据页为单位来读写的，默认每次是读取16KB。也就是说当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。于普通索引，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

• 从更新过程看： （可以先了解下change buffer） 如果要在这张表中插入一个新记录 (4,400) 的话

• 这个记录要更新的目标页在内存中

  对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；

  对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

• 这个记录要更新的目标页不再内存中

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

  对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的

• change buffer：

  当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的化，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。change buffer可以持久化到磁盘上。

  change buffer应用到旧的数据页，得到新的数据页的过程，称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge，在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

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

• 唯一索引不适合使用change buffer，因为如果数据都已经读取到内存中，直接更新内存更快。

• change buffer的使用场景：

  change buffer只限于在普通索引的场景下，而不适用于唯一索引。

  change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

  反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

• 索引选择和实践：

  如果主要考虑的是对更新性能的影响，那么选择普通索引更合适。

  如果使用的场景是在更新之后都马上伴随着对这个记录的查询，那么应该关闭change buffer

  在实际使用中，普通索引和change buffer的配合使用，对于数据量大的表的更新优化还是很明显的。

  出于成本考虑用的是机械硬盘时，那么应该尽量使用普通索引，然后把change buffer尽量开大。

• change buffer和redo log：

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

MySQL为什么会选错索引？

• 在MySQL中一张表其实是可以支持多个索引的，在SQL语句中没有主动指定使用哪个索引的话，MySQL就会自己选择索引，所以就可能会出现选错索引的情况。

  使用 force index(索引字段) 来让优化器强制使用索引例如：select * from t force index(a) where a between 10000 and 20000;

• 优化器逻辑：

  优化器选择索引的目的是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘的次数越少，消耗的CPU越少（影响因素还有：是否使用临时表、是否排序等）。

• 扫描行数是怎么判断的？

  在开始执行SQL之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。

  这个统计信息就是索引的“区分度”（一个索引上不同的值越多，这个索引的区分度就越好）。而一个索引上不同的值的个数，我们称之为“基数”。

  基数（可以使用show index查看）越大，索引的区分度越好。

• MySQL是怎样得到索引的基数的呢？

  使用采样统计（所得结果是很容易不准的），因为把整张表一行行统计代价过高（尽管所得结果非常精确）

• 采样统计：

  InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

  而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

  索引统计信息不准确导致的问题，我们可以用analyze table解决

• 索引选择异常和处理

  原本可以执行得很快的 SQL 语句，执行速度却比你预期的慢很多，该怎么办呢？

• 采用force index强行选择一个索引（不过很多程序员不喜欢使用 force index，一来这么写不优美，二来如果索引改了名字，这个语句也得改，显得很麻烦。而且如果以后迁移到别的数据库的话，这个语法还可能会不兼容。）

• 可以考虑修改语句，引导 MySQL 使用我们期望的索引。

• 在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删除误用的索引（这种场景一般比较少见）。

  因此建议修改SQL语句，引导MySQL使用我们期望的索引。

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

• 使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。（但如果长度过短，则可能会额外增加查询次数）

• 加索引示例：

  // 普通索引

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

  // 前缀索引

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

• 使用前缀索引的影响？

• 可能会增加扫描行数

• 导致覆盖索引失效，进行额外的回表查询

• 常见索引场景以及索引选择：

• 邮箱选择前缀索引

• 身份证号码倒序存储；对号码进行hash存储在一个字段，然后对该字段加索引。

• 小结：

  直接创建完整索引可能会比较占空间

  创建前缀索引，节省空间，但是会增加查询扫描次数，并且不能使用覆盖索引

  倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题（比如身份证的前6位为地区码），不支持范围扫描。

  创建额外的hash字段，查询性能稳定，有额外的存储和计算消耗，不支持范围扫描。

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

• 有种这样的场景，一条SQL语句，正常执行的时候特别快，但是有时候也不知道怎么回事，它就会变得特别慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短。

• 当内存数据页跟磁盘数据内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”

• “抖”一下可能是在刷脏页（flush）

  • redo log满了，需要将数据落盘，然后移除相应操作日志

  • 需要新的内存页，但是系统内存不足。需要淘汰旧页中的数据（如果是脏页则需要现落盘“洗干净”），让出空间

  • 当MySQL认为系统不忙的时候，会利用一切机会刷“脏页”

  • 在MySQL正常关闭的时候，MySQL会把内存的脏页数据都刷到磁盘上。

• InnoDB刷脏页的控制策略

  • 刷盘速度主要参考这两个因素：

    脏页比例

    redo log写盘速度

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

• 一个InnoDB表包含表结构定义和数据两部分。

• 简单地删除表数据达不到表空间回收的效果

• 参数 innodb_file_per_table

  • 设置为OFF，表的数据放在系统共享表空间，页就是跟数据字典放在一起。

  • 设置为ON，每个InnoDB表数据存储在一个以.ibd为后缀的文件中。在MySQL5.6.6版本开始，它的默认值就是ON了

  • 建议将此参数设置为ON

• 数据删除流程

  • 如果我们删除其中一条数据，那么它所在树的节点会被标记为“已删除”状态，如果下一次插入的数据恰好在其上一条记录至其下一条记录的范围之间，那么则可能复用该记录。

  • 如果我们删除的是整一个数据页，那么整个数据页都可以被复用。

  • 如果我们使用delete命令把整个表的数据删除，那么所有的数据页都会被标记为可复用。但是数据仍在磁盘文件中，大小不会改变。

    通过delete命令不能回收表空间的。

• 重建表

  • 目的是做空间收缩，把表中的“空洞”去掉

    可以使用alter table A engine=InnoDB 命令来重建表

• Online DDL

  • 1：建立一个临时文件，扫描表A主键的所有页

    2：用数据页中表A的记录生成B+树，存储到临时文件中

    3：生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中

    4：临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件。

    5：用临时文件替换表A的数据文件。

• 小结： 如果要收缩一个表，只是delete掉表里面不用的数据的话，表文件的大小是不会变的，还需要通过alter table命令重建表，才能达到表文件变小的目的。

重建表可能使一个表占用空间反而变大的原因

• 有可能这个表本身就已经没有空洞了，比如刚刚做过一次重建表操作

• 有可能早DDL期间，如果外部的DML在执行，这期间可能会引入一些新的空洞

• 在重建表的时候，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用。所以重建表之后空间利用率并不一定比重建前高。

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

• count(*)的实现方式

  • MyISAM引擎中把一个表的总行数存在了磁盘上，因此执行count(*)的效率很高（但如果加了where条件的话。MyISAM表返回得不会这么快乐）

  • InnoDB引擎则需要把数据一行行地从引擎里面读出来，然后累积计数

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

• InnoDB中count(*)的优化：

  InnoDB是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于count(*)这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL优化其会找到最小的那颗树来遍历。

  在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

• TABLE_ROWS 就是从这个采样估算得来的，因此它也很不准。有多不准呢，官方文档说误差可能达到 40% 到 50%。所以，show table status 命令显示的行数也不能直接使用

• 小结：

  MyISAM表虽然count(*)很快，但是不支持事务

  show table status命令虽然返回很快，但是不准确。

  InnoDB表直接count(*)会遍历全表，虽然结果准确，但会导致性能问题。

• 用缓存系统保存计数

• 在数据库中保存计数

不同的count用法

• count()是一个聚合函数，对于返回的结果集，一行行地判断，如果非NULL，累计值就加1，否则不加，最后得到累计值。

  count(*)、count(主键ID)和count(1)都表示返回满足条件的结果集的总函数；而count(字段)，则表示返回满足条件的数据行里面，参数“字段”非NULL的总个数。

• 对于count(主键ID)来说，InnoDB引擎会遍历整张表，把每一行的ID值都取出来，返回给server层，sever层拿到ID后，判断是不可能为空的直接按行累加。

• 对于count(1)来说，InnoDB遍历整张表但是不取值，server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的直接按行累加。

• 对于count(字段)来说：

  1：如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不可能为null，按行累加。

  2：如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

• 对于count(*)来说，InnoDB做了优化，不会取值。判断也是不可能为null的，直接累加

• 按照效率排序的话：

  count(*) > count(1) > count(主键) > count(字段)

"order by"是怎么工作的？

• 对于一个表：

  CREATE TABLE t (

  id int(11) NOT NULL,

  city varchar(16) NOT NULL,

  name varchar(16) NOT NULL,

  age int(11) NOT NULL,

  addr varchar(128) DEFAULT NULL,

  PRIMARY KEY (id),

  KEY city (city)

  ) ENGINE=InnoDB;

• 执行如下SQL： select city,name,age from t where city='杭州' order by name limit 1000; （全字段排序）

  • 初始化sort_buffer，确定放入name、city、age这三个字段。

  • 从索引city找到第一个满足city='杭州'条件的主键ID

  • 到主键ID索引取出整行，取name、city、age三个字段的值，存入sort_buffer中。（步骤3）

  • 从索引city取下一个记录的主键ID（步骤4）

  • 重复步骤3、步骤4直到city的值不满足查询条件为止。(city值不为杭州)

  • 对sort_buffer中数据按照字段name做快速排序

  • 按照快速排序结果取1000行返回给客户端

• 如果MySQL认为排序的单行长度太大会怎么做呢？（我们可以通过max_length_for_sort_data参数进行设置） 对于SQL：select city,name,age from t where city='杭州' order by name limit 1000;city、name、age 这三个字段的定义总长度是 36，因此会被认为单行长度太大。

此时会采用rowid排序方法，会多访问一次表t的主键索引，也就是步骤7

• 初始化sort_buffer，确定放入两个字段，即name、id

• 从索引city找到第一个满足city='杭州'条件的主键ID

• 到主键ID索引取出整行，取name、id这两个字段，存入sort_buffer（步骤3）

• 从索引city取下一个记录的主键ID（步骤4）

• 重复步骤3、4直到不满足city='杭州'条件为止

• 对于sort_buffer中的数据按照字段name进行排序

• 遍历排序结果，取前1000行，并按照ID的值回到原表中取出city、name和age三个字段返回给客户端。（步骤7）

• MySQL的一个设计思想是： 如果内存够，就要多利用内存，减少磁盘访问。

  • 认为内存够用的时候就会选择全字段排序

  • 认为内存不够用的时候则会选择rowid排序

• 通过建立联合索引来提高排序效率 （建立city，name） 在查询过程中既不需要临时表，也不需要排序。 （建立的恰好是覆盖索引，如果新增一个addr字段，那么我们应该建立索引(city，name，addr)）

  • 找到第一个满足city='杭州'条件的主键ID

  • 到主键ID索引取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回（步骤2、步骤3）

  • 从索引(city，name)取出下一记录主键ID

  • 重复步骤2、3，直到查到第1000条记录，或者是不满足city='杭州'条件时循环结束。

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

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

• 案例一：条件字段函数操作

  • DDL：

    CREATE TABLE tradelog (

    id int(11) NOT NULL,

    tradeid varchar(32) DEFAULT NULL,

    operator int(11) DEFAULT NULL,

    t_modified datetime DEFAULT NULL,

    PRIMARY KEY (id),

    KEY tradeid (tradeid),

    KEY t_modified (t_modified)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  • 假设，现在已经记录了从2016年初到2018年底的所有数据，运营部门有一个需求是，要统计发生在所有年份中7月份的交易记录总数。

  • 你的SQL语句可能是：

    select count(*) from tradelog where month(t_modified)=7;

    但是值得注意的是你对字段't_modified'做了函数计算，索引会失效，那么将会进行全表扫描，此时索引搜索会失效。

    对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树的搜索功能。（需要注意的是，优化器并不是放弃这个索引，还是有可能选择走这个索引）

  • 如果我们想继续走索引搜索，语句可以修改为：

    select count(*) from tradelog where (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

• 案例二：隐式类型转换

  • 交易编号tradeid上建有索引，是varchar(32)类型，对于SQL：

    select * from tradelog where tradeid=110717;将会走全表扫描，因为输入的是整型，需要进行类型转换。

• 案例三：隐式字符编码转换

  • 字符集不同，在表连接过程中要求在被驱动表的索引字段上加函数操作，然后导致对被驱动表做全表扫描。

• 小结： 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。 隐式类型转换、隐式字符编码转换都是因为要求在索引字段上做函数操作而导致了全索引扫描。

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

• DDL：

  CREATE TABLE t (

  id int(11) NOT NULL,

  c int(11) DEFAULT NULL,

  PRIMARY KEY (id)

  ) ENGINE=InnoDB;

• 查询长时间不返回

  • 对于SQL：

    select * from t where id=1;可能会长时间不返回查询结果，大概率是表t被锁住了。通常执行show processlist 命令，看看当前语句处于什么状态。

  • 可能是由于其他线程正在表t上请求或者持有MDL写锁，把select语句堵住了。

  • 可能是由于其他线程在对表进行flush操作

  • 可能是某个事务在这行记录上持有一个写锁

• 查询慢

  • 对于语句select * from t where c=50000 limit 1; 因为字段c没有建立索引而需要扫描5万行（坏查询不一定是慢查询）

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

• DDL：

  CREATE TABLE t (

  id int(11) NOT NULL,

  c int(11) DEFAULT NULL,

  d int(11) DEFAULT NULL,

  PRIMARY KEY (id),

  KEY c (c)

  ) ENGINE=InnoDB;

• 数据插入：

  insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

• 我们通过这样一个示例分析：

  begin;

  select * from t where d=5 for update;

  commit;

  可以这样理解，这条语句会命中d=5的这一行数据，对应主键id=5，因此在select语句执行完成后，id=5这一行会加一个写锁，而且由于两阶段锁协议，这个写锁会在执行commit语句的时候释放。

• InnoDB的默认事务隔离级别是可重复读，对于上面的案例，如果只在id=5的这一行加锁，而其他行不加锁的话，会怎么样？

  • 我们可以看见在session A里执行了三次查询，分别是Q1、Q2和Q3。他们的SQL语句都是'select * from t where d=5 for update'

  • Q1返回的是(5,5,5)

  • Q2返回的是(0,0,5),(5,5,5)，因为在查询之前，session B做了更新操作，将id=0的这一行的d字段更新为5（这里发生的不是幻读）

  • Q3返回的是(0,0,5),(1,1,5),(5,5,5)，因为在查询之前session C做了插入操作，insert into t values(1,1,5);（这里发生了幻读）

• 幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行（并且该行是新插入的行）。

• 幻读在“当前读”隔离级别下才会出现

  示例中session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

• 幻读破坏了语义，并且产生了数据不一致性问题。

为什么我只改一行语句，锁这么多（TODO）

• 加锁规则

  • 两个原则

    • 加锁的基本单位是next-key lock。next-key lock是前开后闭区间。

    • 查找过程中访问到的对象才会加锁。

  • 两个优化

    • 索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。

    • 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

  • 一个bug

    • 唯一索引上的范围查询会访问到不满足条件的第一个值为止。

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

• 短链接风暴

  • 正常的短链接模式就是连接到数据库后，执行很少的SQL语句就断开，下次需要的时候再重连。再业务高峰期的时候，就可能出现连接数突然暴涨的情况。

  • MySQL建立连接需要三次握手建立正常的网络连接、需要做登录权限判断以及获取这个连接的数据读写权限。（再数据库压力较小的时候，这些额外的成本并不明显）

  • 饮鸩止渴方法

    • 先处理掉哪些占着连接但是不工作的线程（可能是有损的），特别是如果客户端接收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄充实查询，那么从应用端看上去，“MySQL一直没恢复”

    • 减少连接过程的消耗。有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。

• 慢查询性能问题

  • 慢查询的出现可能是以下三种原因：

• 索引没有设计好

• SQL语句没有写好

• MySQL选错了索引

  • 最好的办法就是在上线前尽量做好验证，避免上线后调整

• QPS突增问题

  • 可能是业务突然出现高峰，又或者是应用程序bug导致某个语句的QPS突然暴增。都会导致MySQL压力过大，影响服务。

  • 如果是一个新功能的bug导致的，最理想的状态就是让业务下掉这个功能。

MySQL如何保证数据不丢的

• 只要redo log和binlog保证持久化到磁盘，就能保证MySQL异常重启后，数据就可以恢复。

MySQL是怎么保证主备数据一致的

• 主备切换流程：

  • 在状态1中：

    client所有读写都直接访问节点A，而节点B是A的备库，只是将A的更新都同步过来本地执行，保持节点B和节点A的数据是相同的。

  • 需要切换主备时，就如同状态1变为状态2:

    client所有读写都直接访问节点B，而节点A是B的备库。

• 备库设置只读（readonly）模式的原因

  • 一些运营类查询语句可能会被放到备库查询，设置为只读可以防止误操作。

  • 防止切换逻辑有bug，比如切换过程中出现双写，造成主备不一致。

  • 可以根据readonly判断节点的角色

• 循环复制问题

  • 对于互为主备的两个节点可能会出现循环复制

  • 问题： 业务逻辑在节点A上更新了一条语句，然后再把生成的binlog发给节点B，节点B执行完这条更新语句后也会生成binlog。那么如果节点A同时是节点B的备库，相当于又把节点B新生成的binlog拿过来执行了一次，然后节点A和B间会不断地循环执行这个更新语句，也就是循环复制。

  • 解决方案

    • 必要条件

      • 规定两个库的server id必须不同，如果相同，则他们之间不能设定为主备关系。

      • 一个备库接到binlog并重放的过程中，生成与员binlog的server id相同的新的binlog

      • 每个库在接收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

    • 处理流程： 从节点A更新的事务，binlog里面记的都是A的server id 传到节点B执行一次以后，节点B生成的binlog的server id也是A的server id 再回传给节点A，A判断到这个server id与自己的相同，就不会再处理这个日志，因此死循环在这里就断掉了。

MySQL是怎么保证高可用的

• 减少主备同步延迟

  • 主备同步延迟 (T3-T2) + (T2-T1)=T3-T1记为同步延迟时间

    • 主库A执行完成一个事务，写入binlog，我们把这个时刻记为T1

    • 之后传给备库B，我们把备库B接收完这个binlog的时刻记为T2

    • 备库B执行完成这个事务，我们把这个时刻记为T3

  • 主备延迟来源

    • 备库所在机器性能比主库要差

      （现在这种部署方式少了，因为主备随时可能发生切换，所以主备库选用相同规格的机器，并且做对称部署）

    • 备库负载压力大

      • 一主多从。除了备库往外，可以多连接几个从库，以此分担读的压力。

      • 通过binlog输出到外部系统，比如Hadoop这类系统，让外部系统提供统计类查询的能力。

    • 大事务

      • 因为主库上必须等事务执行完成才会写入binlog，再传给备库。那么一个主库上的语句执行10分钟，那这个事务很可能就会导致从库延迟10分钟。

      • 大事务场景

        一次性地用delete语句删除太多数据

        大表DDL

    • 备库的并行复制能力

• 可靠性优先策略

  • 判断同步延迟时间是否足够小：判断备库B现在的seconds_behind_master，如果小于某个值则继续下一步，否则持续重试这一步

  • 设置主库A为只读：把主库A改成只读状态，即把readonly设置为true

  • 同步完成：判断备库B的seconds_behind_master的值，直到这个值变成0为止

  • 设置备库B为可读写：把备库B改成可读写状态，也就是把readonly设置为false

  • 将库B作为主库：把业务请求切到备库B

• 可用性优先策略 （可能会出现数据不一致）

  • 直接设置备库B为可读写，然后将业务请求切过来，那么系统几乎就没有不可用时间了。但是可能会出现数据不一致。

  • 一个可用性优先导致数据不一致的场景

    • 在步骤2中，主库A执行完insert语句，插入了一行数据(4,4)，之后开始进行主备切换

    • 在步骤3中，由于主备之间有5秒的延迟，所以备库B在还没来得及应用“插入c=4”这个中转日志，就开始接收客户端“插入c=5”的命令

    • 在步骤4中，备库B插入了一行数据(4,5)，并且把这个binlog发给主库A。

    • 在步骤5中，备库B执行“插入c=4”这个中转日志，插入了一行数据(5,4)。而直接在备库B执行的“插入c=5”这个语句，传到主库A，就插入了一行新数据。

• 什么情况下采用可用性优先策略而不采用可靠性优先策略？

  • 场景：

    有一个库的作用是记录操作日志。这时候，如果数据不一致可以通过binlog来修补，而这个短暂的不一致也不会引发业务问题。

    同时，业务系统依赖于这个日志写入逻辑，如果这个库不可写，会导致线上的业务操作无法执行。

  • 按照可靠性优先思路，异常切换会是什么效果？

    • 假设主库A和备库B间的主备延迟是30分钟，这个时候主库A掉电了，HA系统要切换B作为主库。我们在主动切换的时候，可以等到主备延迟小于5秒的时候再启动切换，但这个时候以及别无选择了。

    • 采用可靠性优先策略，就必须等到备库B的 seconds_behind_master=0 之后，才能切换。这将导致整个系统处于完全不可用的状态。【因为主库A掉电后，我们的连接还没有切到备库B】

    • 是否可以直接切换到备库B，但是保持B只读呢？

      • 不行。因为在这段时间内，中转日志还没有应用完成，如果直接发起主备切换，客户端查询看不到之前执行完成的事务，就会认为有“数据丢失”。虽然随着中转日志的继续应用，这些数据会恢复回来，但是对于一些业务来说，查询到“暂时丢失数据的状态”也是不能被接受的。

• MySQL高可用系统的可用性，是依赖于主备延迟的。延迟时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高。

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

• 如果备库执行日志的速度持续低于主库生成日志的速度，那么延迟就有可能成了小时级别。

  而且对于一个压力持续比较高的主库来说，备库可能永远追不上主库的节奏。

• 解决方案：备库并行复制能力

  • 注意工作线程不能分配太多，还需要考虑备库得提供读查询。

  • 事务不能按照轮询方式分发给各个工作线程，因为CPU的调度策略，很可能第二个事务最终比第一个事务先执行，如果这两个事务更新的是同一行（或者说是更新内容有交集），将意味着同一行上的两个事务，在主库和备库上执行顺序相反，导致主备数据不一致。

  • 同一个事务的多个更新语句，不能分给不同的工作线程执行。可能会破坏事务逻辑的隔离性

  • coordinator在分发的时候需要遵守这两个要求

    • 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个worker中

    • 同一个事务不能被拆开，必须放在一个worker中

  • 并行复制分发策略

    • 按表分发【按库+表名hash】，如果两个事务更新不同的表，他们就可以并行。因为数据是存储在表里的，所以按表分发，可以保证两个worker不会更新同一行。当然，如果有跨表的事务，还是要把两张表一起考虑的。可能会出现热点表。

    • 按行分发【按库+表+主键hash】，可以解决热点表的并行复制复制问题，就需要一个按行并行复制方案。 如果两个事务没有更新相同的行，他们就可以在备库上并行执行。 注意如果单个事务更新行数超过一定阈值，耗费的内存CPU可能会比较大，因此需要暂时退化为单线程模式。退化过程如右：

      • coordinator 暂时先 hold 住这个事务

      • 等待所有 worker 都执行完成，变成空队列

      • coordinator 直接执行这个事务

      • 恢复并行模式

    • 按库并行（MySQL5.6版本支持）【按库名hash】 优点是构造hash值占用内存小并且DB数不会特别多，不要求使用binlog格式，因为statement格式的binlog也可以很容易拿到库名。 缺点是如果你的表都在一个DB里面这个策略就会失效，或者出现热点库问题效率也会降低。

    • MariaDB并行复制策略

      基于如下特性：

      能够在同一组里提交的事务，一定不会修改同一行。

      主库上可以并行执行的事务，备库上也一定可以并行执行。

      那么可以将commit_id相同的事务分发到多个worker执行。

      缺点是commit_id相同的多个事务中有一个事务相对于其他事务耗时较长，那么将有一段时间只有该事务线程在工作比较浪费资源。

    • MySQL5.7的并行复制策略，通过参数slave-parallel-type控制。配置为DATABASE表示使用MySQL5.6版本的按库并行策略；配置为LOGICAL_CLOCK表示采用类似MariaDB的并行复制策略，不同点是它会根据同时进入 prepare 和 commit 来判断是否可以并行。

    • MySQL5.7.22的并行复制策略

      新增了一个参数binlog-transaction-dependency-tracking用来控制是否启用这个新策略。

      COMMIT_ORDER，表示的是根据同时进入 prepare 和 commit 来判断是否可以并行的策略。

      WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行。

      WRITESET_SESSION，是在 WRITESET 的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序。

答疑篇

日志和索引相关问题

• 在写入redo log出于prepare阶段之后、写binlog之前崩溃（crash），由于此时binlog还没写，redo log也没有提交，所以崩溃恢复的时候，这个事务会回滚。需要业务方进行重试。

• 在写完binlog，redo log还没commit前发生崩溃（在恢复过程中事务会被提交）。

  如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交；如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整，若完整则提交事务，否则回滚事务。

• MySQL怎么知道binlog是完整的？

  一个事务的binlog是有完整格式的，于statement格式，最后会有COMMIT标志；于row格式，最后会有一个XID event。

  另外，在MySQL5.6.2版本之后，还引入了binlog-checksum参数，用来验证binlog内容的正确性。

• redo log和binlog是怎么关联起来的？

  通过XID关联，崩溃恢复的时候，会按顺序扫描redo log，如果碰到既有prepare、又有commit的redo log，就直接提交；如果碰到只有prepare、而没有commit的redo log，就拿着XID去binlog找对应的事务。

• 处于prepare阶段的redo log加上完整binlog，重启就能恢复，MySQL为什么要这样设计？

  redo log加上完整的binlog可以成功恢复事务。

  同时备库也可以通过这种方式同步数据，以此可以保证主备数据库数据的一致性。

• 两阶段提交是为了给所有人一个机会，当所有人都准备好了，再一起提交。

• 不能只用binlog来既支持崩溃恢复，又支持归档的原因是？

  binlog没有能力恢复“数据页”

  那么只用redo log行不行？

  只用redo log，历史日志没法保留，起不到归档作用。

• 正常运行中的实例，数据写入后的最终落盘，是从buffer pool更新过来的

  正常运行的实例，数据页被修改以后与磁盘的数据不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。

• redo log buffer是什么？是先修改内存还是先写redo log文件？

  在插入数据的过程中，生成的日志都得先保存起来，但又不能在还没commit的时候就直接写到redo log文件里，此时就会写到redo log buffer（用来存redo log的内存）。也就是说，是先修改内存，然后在执行commit时再写redo log文件。