mysql#

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

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中（change buffer），就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中 (change buffer)，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

redo log 的写入有两个步骤，第一次是 praper 状态，事务 commit 后才会更新为 commit 状态。

redo log#

redo log 是 InnoDB 引擎特有的，redo log 是物理日志，记录的是 “在某个数据页上做了什么修改” , 会有专门的 pure 线程处理 redo log

undo log#

回滚日志，在数据修改的时候，不仅记录了 redo，还记录了相对应的 undo，如果因为某些原因导致事务失败或回滚了，可以借助该 undo 进行回滚。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。MVCC 中，一个记录有多个版本，要得到某个版本的值，就是通过 undo log 计算到的。

事务与 mvcc#

Innodb 实现事务隔离，是通过 mvcc 和锁实现的。一致性读的时候（就是快照读），不需要加锁，直接通过 mvcc 实现。

快照读（所有的 select 语句）

在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间（事务启动是指第一次查询 sql 时，不是 start transtion），当前正在 “活跃” 的所有事务 ID。“活跃” 指的就是，启动了但还没提交。这个数组中，最小值称为低水位，最大值加 1 称为高水位。读取一行数据得到 row trx_id，通过这个数组来判断是否可见，如果不可见，通过 undo log 得到上一个版本的数据。

当前读

如果是当前读，就必须是读最新的版本信息，就要加上行锁锁住这行。一直到当前的事务提交。当前读的情况有：update t set k=k+1 where id=1 ….

为什么要重建表#

innodb 经过大量的增删改之后，数据页会留下大量空洞，浪费磁盘空间，重建表可以解决这个问题。

页合并和页分裂#

innodb 删除某个记录，其实就是标记这个位置可复用。 当页中删除的记录达到 MERGE_THRESHOLD（默认页体积的 50%），InnoDB 会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。 当插入或者更新记录时，当前页已经满了，需要在这个页中间插入一个记录，产生页分裂，同时产生页空洞。

重建表#

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

联合索引的使用#

index (name, age), 先按照 name 排序，然后是 age 排序，索引上保留 name 和 age 的值

覆盖索引#

查询的字段已经在索引上获取，不用回表查询。

最左前缀原则#

索引下推#

select * from tuser where name like ‘张 %’ and age=10

会从索引找到张开头的数据，然后过滤 age=10 后，再去回表查询。就是说会尽力在索引中过滤完条件再去回表，而不是回表完在过滤其他 where 条件。

Order By 使用索引#

innodb 的索引有两大功能，快速定位记录，还有就是排序功能。尽力保证 order by 的字段利用到索引

Change Buffer 和 redo log#

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

写入或者更新一条数据

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

对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。（如果马上有读取这个记录，还是需要去读取这行记录到内存，然后 merge，所以说如果说 change bufer 更适合在写多读少的情况，并且没有用到唯一索引）

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

Mysql 与客户端如何交互#

我们查询一个大数据的时候，需要担心把 mysql 内存爆掉吗？

实际上，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：
1. 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
2. 重复获取行，直到 net_buffer 写满，调用网络接口发出去 (socket)。如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
3. 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

也就是说，MySQL 结果是 “边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。

如果 net_buffer 写满了，mysql 执行 show processlist 显示 Sending to client

mysql 客户端接收服务端返回结果的方式有两种：#

一种是本地缓存，也就是在本地开一片内存，先把结果存起来。如果你用 API 开发，对应的就是 mysql_store_result 方法。

另一种是不缓存，读一个处理一个。如果你用 API 开发，对应的就是 mysql_use_result 方法。

Mysql 内部临时表#

union#

会有去重操作，所以会生成一个临时表保存数据，每次获取一行，需要判断是否存在。union all 不需要去重，所以不会生成临时表，直接返回数据即可。

group by#

select a,count(1) as num from t group by a

1. 创建内存临时表，表里有两个字段 a 和 num，主键是 a； //group by 字段就是主键

2. 从 t 中扫描数据（可能会选择一个索引），写入到内存临时表中。由于 a 是主键，所以写入第一条后，后面的只会修改聚合的字段，比如 num+1.

3. 遍历完后，再根据 a 字段排序，得到结果集返回客户端。 如果你的需求并不需要对结果进行排序，那你可以在 SQL 语句末尾增加 order by null

explain 结果，extra 字段包含，Using temporary; Using filesort

磁盘临时表#

内存临时表的大小是有限制的，参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。 这时候就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是 InnoDB

group by 优化方法 – 索引#

不论是使用内存临时表还是磁盘临时表，group by 逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。 首先看看临时表的作用，因为扫描的数据得到的 a 是无序的，所以需要构造一个主键为 a 的临时表，好随时修改对应主键的聚合值。如果扫描出来就是 a 有序的，就不需要临时表了。

explain 语句，extra 显示，Using index，表示 group by 索引字段，不用临时表，不用排序

group by 优化方法 – 直接排序#

如果没有办法避开临时表和排序，可以估算数据，直接走磁盘临时表。不然会先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”，看上去就有点儿傻。

Explain 的解读#

ID：#

ID 值越大，越先被执行，如果 Id 值相同，就从上往下执行。

如上图所示，在 id 为 1 时，table 显示的是 <derived2> , 这里指的是指向 id 为 2 的表，即 t3 表的衍生表。

type#

const 表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量。
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
range 只检索给定范围的行，使用一个索引来选择行，key 列显示使用了哪个索引，一般就是在你的 where 语句中出现 between、<、>、in 等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
index Full Index Scan，Index 与 All 区别为 index 类型只遍历索引树。这通常比 ALL 快，因为索引文件通常比数据文件小。（也就是说虽然 all 和 Index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘读取的）
all Full Table Scan 将遍历全表以找到匹配的行

mysql 的主从#

备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的：
1. 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
2. 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，io_thread 和 sql_thread。
3. 其中 io_thread 负责与主库建立连接。主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
4. 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。sql_thread 读取中转日志，解析出日志里的命令，并执行。

binlog 的格式#

statement
当 binlog_format=statement 时，binlog 里面记录的就是 SQL 语句的原文，由于执行过程可能使用到的索引不同，可能会造成主从不一致。
row
当 binlog_format 使用 row 格式的时候，binlog 里面记录了所有变化的行的 id，所以两边执行的 sql 修改的一定是同一行。
mix
两种方式并存

本作品采用《CC 协议》，转载必须注明作者和本文链接