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 协议》，转载必须注明作者和本文链接