MySQL

查询语句执行流程

首先客户端需要连接到服务端，并获取权限信息。
MySQL会先查询缓存，如果命中缓存就直接返回缓存中的数据。
如果没有命中缓存就会由分析器进行语法分析。以及验证表、字段是否存在。
验证通过后，优化器会对sql进行优化（比如选择索引），并把优化后的结果交给执行器。
执行器会调用存储引擎执行sql语句，并将结果返回给客户端，如果开启了查询缓存，还会把结果添加到缓存中。

MySQL是 “边读边发” 的，一个完整的查询流程是这样的：

读取一行数据，写到net_buffer中（默认是16k）。

net_buffer的大小可以通过参数net_buffer_length修改。

继续读取数据，直到net_buffer写满，然后调用网络接口发送出去。

如果发送成功，就清空net_buffer，然后继续前面的流程。

更新语句执行流程

对于一条更新语句：

如果对应数据页在内存中，就直接更新内存，然后把操作记录写到[redo log](#redo log)里面，这样就算执行完成了。然后MySQL会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。
如果对应的数据页不在内存中，就把操作记录到[change buffer](#change buffer)中，然后再写[redo log](#redo log)。之后如果有查询语句把该数据页加载到内存，就会执行[change buffer](#change buffer)中对应的操作。

⭐日志

⭐RedoLog (重做日志)

redo log是InnoDB引擎特有的。

如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的记录，然后再更新，整个IO成本就太高了。为了解决这个问题，MySQL的设计者使用redo log来提高更新效率。

具体来说，当有一条记录需要更新的时候，MySQL就会先把记录写到[redo log](#redo log)里面，并更新内存，这样时候更新就算完成了。然后，MySQL会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

redo log的大小是固定的，由两个指针控制日志的写入和擦除。写指针记录的是当前记录的位置，写到末尾就又回到开头循环写。擦除指针是已经被更新到磁盘的位置，擦除记录前会把记录更新到磁盘上。有了redo log，就可以保证就算数据库异常重启，之前提交的记录都不会丢失。

⭐BinLog(归档日志)

binlog有三种格式，一种是statement格式，一种是row格式，还有mixed格式。

可以通过参数binlog_format来设置binlog的格式。

⭐Statement格式

statement格式中，binlog里面记录的就是SQL语句的原文，这种格式的优点是binlog需要保存的数据量比较少。但是有可能会导致主从数据不一致，比如delete语句带有limit的情况：

delete from t where a=1 or b=2 limit 1;
如果这条delete语句走的是索引a，那就会根据索引a找到第一个满足条件的行，删除的就是id=1这一行。
如果使用的是索引b，那就会根据索引b找到第一条满足的行，删除的可能就是id=2这一行了。

⭐Row格式

binlog是row格式时，binlog里面记录的是具体的数据内容，所以不会出现数据不一致的情况。

而且row格式恢复数据时很方便，如果要恢复delete语句，只需要把binlog中记录的数据转成insert语句就可以了。如果要恢复update语句，binlog也会记录修改前和修改后的数据。

row格式也是有缺点的，因为row格式记录的是数据内容，所以会占用很大的空间。

比如某一条delete语句删除了10万条数据，如果是statement格式的话，只需要把sql语句记录到binlog中就好了。如果是row格式的话，就需要把这10万条记录都记录到binlog中，会占用更多空间。

⭐Mixed格式

mixed格式其实就是row格式和statement格式的混合版本，因为statement格式可能会出现数据不一致，row格式会占用更大的空间。所以MySQL提出了一个折中的方案。

mixed格式会判断SQL语句是否会引发数据不一致的问题，如果会引发数据不一致就用row格式存储，否则就用statment格式存储。

⭐两阶段提交

两阶段提交就是将[redo log](#redo log)的写入拆成两个步骤：prepare（预提交）和commit（提交）两个阶段，主要是为了保证两份日志之间的一致性。因为[redo log](#redo log)和binlog是两个独立的日志，不管是先写[redo log](#redo log)还是先写binlog，都有可能出现数据不一致。

对于一条insert语句：

如果先写[redo log](#redo log)后写binlog，[redo log](#redo log)写完之后MySQL异常重启，binlog中就会少记录一条insert语句，如果之后用这个binlog恢复数据的话，数据库中就会少一条数据。
如果先写binlog后写[redo log](#redo log)，binlog写完之后MySQL异常重启，因为[redo log](#redo log)还没写，MySQL恢复以后这个事务是无效的。但是binlog已经写完了，之后如果用这个binlog恢复数据的话，就会多一条数据。

如果不使用“两阶段提交”，那么数据库当前的状态就有可能和用日志恢复出来的状态不一致。

对于两阶段提交来说，假设[redo log](#redo log)处于预提交状态，MySQL恢复以后会判断对应的binlog记录是否完整，如果是完整的就提交[redo log](#redo log)，否则就回滚事务。

[redo log](#redo log)和binlog有一个共同的字段，叫XID。MySQL异常重启之后，会先扫描[redo log](#redo log)中的记录。

如果包含commit的记录，就直接提交。

如果某一条记录只有prepare，没有commit，就通过XID去找对应的binlog，并判断binlog是否完整，如果binlog是完整的就提交。

一个事务的binlog是有完整格式的：

statement格式的binlog，结尾会有commit。

row格式的binlog，结尾会有XID event。

两个日志的区别

[redo log](#redo log)是InnoDB引擎才有的；binlog是MySQL实现的，所有引擎都可以使用。
[redo log](#redo log)的大小是固定的，是循环写的；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

⭐Undo log

undo log是回滚日志，用来记录数据被修改前的信息，它跟[redo log](#redo log)正好相反，[redo log](#redo log)记录的是数据被修改后的信息。
undo log记录的是修改之前的数据信息，假如需要回滚操作的话，可以根据undo log的信息来回滚到没被修改前的状态。

比如：当我们执行一条insert语句时，Undo Log就记录一条相反的delete语句。

⭐索引

索引是在引擎层实现的，不同引擎的索引实现方式是不一样的。InnoDB使用的是B+树模型，每一个索引都对应一个B+树。

根据B+树叶子节点的内容，索引又分为主键索引和非主键索引。

主键索引的叶子节点存的是数据页。对于主键索引的查询语句，只需要搜索主键对应的B+树。

如果创建表时没有指定主键，InnoDB会自己生成一个长度为6个字节的rowid作为主键
非主键索引的叶子节点内容是主键的值。对于非主键索引的查询语句，需要先搜索索引对应的B+树，得到主键值，再到主键索引搜索一次。这个过程叫回表。

⭐覆盖索引

如果索引中包含了查询语句需要查询的所有字段，这种情况可以直接返回结果，不需要回表。

⭐索引下推

MySQL5.6引入了索引下推机制，可以在遍历普通索引时，对索引中包含的字段先做判断，直接过滤掉不符合条件的记录，减少回表次数。

MySQL5.6之前需要先通过普通索引查询到主键值，然后到主键索引上找出对应的数据行，再判断数据行是否符合条件。

⭐唯一索引

对于查询语句来说：

如果要搜索的是普通索引，在查询到第一个满足条件的数据后，需要继续查找，直到碰到第一个不满足条件的记录为止。
如果要搜索的是唯一索引，由于索引定义了唯一性，查找到第一条满足条件的记录后，就会停止查找。

也就是说，唯一索引可以提高查询效率，不过这种效率的提升是非常低的。因为InnoDB是以数据页为单位进行读写的，也就是说，如果需要查询一条记录的时候，会把该记录所在的数据页都读入到内存中。所以普通索引后续的查找和判断都是在内存中进行的，效率会很高。

对于更新/插入语句来说：

如果是普通索引，如果要操作的数据页在内存中，就直接更新内存中的数据，然后写日志；如果要操作的数据页不在内存中，就把操作记录在[change buffer](#change buffer)中。
如果是唯一索引，如果要操作的数据页在内存中，就判断是否存在冲突，如果没有冲突就更新内存中的数据，然后写日志；如果要操作的数据页不在内存中，需要先把数据页加载到内存中，再进行操作。

⭐change buffer

对于普通索引来说，当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页没有在内存中的话，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。查询这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。这样就能保证数据的正确性。

对于唯一索引来说，需要先判断数据是否违反唯一约束，所以必须先把数据加载到内存之后才能判断。这样就没必要使用change buffer了。

将更新操作记录在change buffer中，可以减少磁盘访问次数，而且数据读入内存是需要占用buffer pool的，所以这种方式还能避免占用内存。

change buffer是可以持久化的数据，也会被写入到磁盘上。

将change buffer中的操作应用到数据页，这个过程称之为merge。

除了访问数据页会触发merge以外，系统有后台线程会定期触发merge。在数据库正常关闭的时候，也会触发merge。

所以change buffer更适合写多读少的业务。merge是数据真正更新的时刻，所以在一个数据页merge之前，change buffer记录的变更越多，收益就越大。

⭐最左前缀原则

最左前缀可以匹配联合索引上的最左N个字段，也可以匹配字符串索引的最左N个字符。

⭐索引失效的情况

对索引字段做函数操作，会导致无法使用索引。
隐式类型转换，会导致无法使用索引，假如字段是varchar类型的，但参数却是整型，就需要做类型转换。
隐式字符集编码转换，会导致无法使用索引。假如表1是utf-8编码，表2是utf-8mb4编码。当表1和表2关联查询的时候会导致无法使用索引。

为什么选择B+树？

磁盘的最小读写单位是扇区，目前比较新的硬盘，一个物理扇区的大小是4K个字节。操作系统一次会读写多个扇区，操作系统的最小读写单位是块 (Block)。当我们要从磁盘上读取一个数据时，操作系统会一次性把整个块都读出来。

B+树的一个关键设计，就是让一个节点的大小等于一个块的大小，节点内部维护一个有序数组。这样就可以让磁盘读取效率最大化。

B+树还有另一个设计，就是把所有节点分为枝干节点和叶子节点。枝干节点只保存key和维持树形结构的指针，并不存储具体的数据，这样枝干节点就能存储更多的索引数据。

B+树还把同一层级的所有节点串成一个有序的双向链表，这样就可以满足范围查询。

优化器是如何选择索引的

优化器主要会根据扫描行数来决定选择哪个索引。

但是在执行语句前，并不能精确的知道满足条件的记录有多少，只能通过索引的区分度来估算，一个索引上不同的值越多，就说明这个索引的区分度越高。

可以通过show index命令查看索引的区分度
1
show index from table_name;

MySQL是通过统计采样的方法来统计索引的区分度，因为把整张表取出来一行行统计代价太高了。

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

因为数据是会持续更新的，索引统计信息也不会固定不变，当变更数据行数超过一定数量的时候，会触发一次索引统计。

可以使用force index的方式强制选择一个索引。

1	select * from t force index(index_name)

⭐事务

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

⭐事务的特性

原子性：是指一个事务操作不可被分割，要么成功，要么失败。

事务的原子性是通过undo log来实现的。
隔离性：是指一个事务内部的操作对其他运行中的事务是隔离的，并发执行的多个事务之间也是互不干扰的。

事务的隔离性是通过MVCC实现的。
持久性：是指一个事务一旦提交完成，后续的其他操作和故障都不会对事物的结果产生影响。

事务的持久性是通过[redo log](#redo log)实现的。
一致性：是指事务不管成功还是失败，必须保证数据的正确性。

比如表中有一个字段有唯一约束，如果事务提交之后导致这一列有相同的数据，这就破坏了数据的正确性。

事务的原子性、隔离性、持久性都是用来保证一致性的。

⭐隔离级别

当数据库上有多个事务同时执行的时候，就有可能出现脏读、不可重复读、幻读的问题，为了解决这些问题，就有了“隔离级别”的概念。

读未提交（read uncommitted）：一个事务可以读取到另一个事务未提交的数据。会产生脏读。

实现方式：直接返回内存中最新的数据。
读提交（read committed）：一个事务只能读取到其它事务已经提交的数据。可以解决脏读的问题。

因为读提交隔离级别下，事务中的每一条sql语句都会生成一个最新的一致性视图（Read View），对于同一个事务执行两次相同的select语句，如果两次select语句之间有其它事务提交，可能会看到不一样的结果，会产生不可重复读的问题。
可重复读（repeatable read）：一个事务在执行过程中看到的数据，总是和启动时看到的数据是一致的。

可重复读隔离级别是事务开始时创建一个一致性视图（Read View），所以可以解决不可重复读的问题。
串行化（serializable ）：对于同一行记录，写会加写锁，读会加读锁。当出现读写锁冲突时，后访问的事务会进入阻塞状态，这种隔离级别会导致数据库效率降低。

实现方式：直接通过加锁的方式避免并行访问。

读提交和可重复读隔离级别都是通过MVCC实现的，主要区别是：

在可重复读隔离级别下，事务开始时会创建一个一致性视图（Read View），事务中所有sql语句都是基于这个视图执行的。

在读提交隔离级别下，事务中的每一条sql语句都会生成一个新的一致性视图（Read View）。

⭐MVCC

MVCC主要用来实现读提交和可重复读这两种隔离级别，它可以让同一条记录在系统中可以存在多个版本。

具体的实现逻辑是这样的，InnoDB里面每个事务都有一个唯一的事务ID。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。

事务更新数据的时候，都会生成一个新的数据版本，并且把当前事务的ID赋值给这个数据版本的事务ID。也就是说，表中的一行记录，有多个版本，每个版本有自己的事务ID。

在实现上，InnoDB为每个事务构造了一个数组，用来保存这个事务启动时，所有已经启动但是还没有提交的事务ID。
数组中事务ID的最小值记为低水位，事务ID最大值记为高水位。
小于低水位的事务ID代表已提交的事务，对当前事务可见。
大于高水位的事务ID代表还未启动的事务，对当前事务不可见。
如果在高水位和低水位之间的事务那就包括两种情况：
1. 如果事务ID在数组中，表示这个版本是由未提交的事务生成的，不可见。
2. 如果事务ID不在数组中，表示这个版本是已经提交了的事务生成的，可见。

假设连续启动三个事务：事务A、B、C的版本号分别是100、101、102。这三个事务都会访问x字段；事务A会查询x的值，事务B和事务C会更新x的值。

这三个事务开始之前，x=1，x对应的事务ID是99。

事务A的视图数组就是[100]，事务B的视图数组就是[100, 101]，事务C的视图数组就是[100, 101, 102]

对于查询语句来说：

事务C执行x+1，这个时候x的值就是2，最新版本是102，99就变成历史版本了。

事务B执行x+1，这个时候x的值就是3，最新版本是101，102就变成历史版本了。

最后事务A查询x的时候
- 在可重复读的隔离级别下，事务A查询语句的流程是这样的：
  1. 当前x=3，事务ID=101，比高水位大，不可见。
  2. 然后再去找上一个版本，x=2，事务ID=102，比高水位大，不可见。
  3. 再往前找，x=1，事务ID=90，比低水位小，可见。所以事务A查询到的x的值就是1
- 在读提交隔离级别下：
  1. 如果事务B提交了，事务A查询到的x=3；
  2. 如果事务B没提交，事务C提交了，事务A查询到的x=2；
  3. 如果都没提交，事务A查询到的x=1。

对于更新语句来说：

更新数据都是要先读后写的，这个读，是当前读，因为必须要保证读到的当前最新的值。

所以事务B更新数据的时候，就不能以事务启动时的数据为基础上更新，必须在事务C的更新结果的基础上进行，否则事务C的更新就丢失了。

所以事务B执行后x=3。

如果把事务A的查询语句加上lock in share mode或者for update，也可以读到事务ID=101的版本，x=3。

如果事务C更新完之后，并没有立刻提交事务，事务B就会进入阻塞状态。

当前读：

加锁的select语句、update、insert、delete语句都是当前读。当前读查询的是数据的最新版本，执行时还要保证其它事务不能修改当前记录，会对读取的记录加锁。

快照读：

不加锁的select语句就是快照读。快照读查询的是数据的可见版本，所以查询到的数据有可能不是最新的。

⭐锁

全局锁

全局锁就是对整个数据库加锁，通常是对数据库做备份时才会用到。命令如下：

1	flush table with read lock;

⭐表级锁（元数据锁）

表级锁有两种：表锁、元数据锁。

表锁：

1 2	-- 对t1加读锁，t2加写锁 lock tables t1 read, t2 write

表锁除了会限制其它线程的读写外，也会导致当前线程只能访问被加锁的表，

如果对t1加读锁，对t2加写锁，会导致当前线程只能执行读t1和读写t2的操作，不允许访问其它表。

元数据锁（metadata lock）：

元数据锁是MySQL 5.5版本引入的，当对一个表做增删改查操作的时候，会自动加元数据读锁。如果要对表结构做变更的时候，会自动加元数据写锁。

元数据读锁之间不互斥
元数据读锁和元数据写锁之间是互斥的。

⭐行锁

行锁是在引擎层实现的，MyISAM是不支持行锁的。

InnoDB中的行锁是通过索引实现的，如果SQL语句的条件字段没有索引，就会进行全表扫描，扫描过的行都会被加上行锁，对应的主键索引也会加上行锁。这种情况就相当于是表锁了。

需要注意的是，在一个事务中，行锁是在需要的时候加上的，但并不是不需要了就会立刻释放，而是要等到事务结束后才释放。

如果事务中会给多行数据加锁，要把可能造成锁冲突的语句尽量往后放，这样就可以减少事务之间的锁等待，可以提升并发度。

假设有一个交易系统，顾客A要在商家B购买商品，假如这个业务涉及到以下操作：

从顾客A账户上扣除商品价格。

给商家B账户上增加商品价格。

记录一条交易日志。

为了保证原子性，需要把这三个操作放到同一个事务中。

如果同一时刻顾客C也要购买商品，那么这两个事务冲突的部分就是，更新商家的账户余额。

根据两阶段锁协议，行锁是在事务提交的时候才释放，所以如果把更新商家余额的语句安排在最后面，那么商家账户余额这一行的锁时间就最少，这样就可以降低事务之间的锁等待，提升了并发度。

死锁

两个事务都持有对方需要的行锁，并且都等待对方释放行锁，就会产生死锁。

MySQL有两种处理死锁的策略：

第一种策略是事务超时退出，默认是50秒，可以通过innodb_lock_wait_timeout调整。

这种策略如果发生死锁，需要等待50秒之后才会超时退出，就会导致其它事务等待锁释放的事务也阻塞比较长的时间。

如果把超时时间设置的比较短，又很容易出现误伤。
第二种策略是死锁检测，默认是开启的。死锁检测发现死锁后，会主动回滚某一个事务，让其他事务继续执行。

死锁检测的逻辑是：每当一个事务进入锁等待状态的时候，就会检查当事务需要依赖的行锁是否被其它事务锁住。

幻读

幻读是指同一个事务在前后两次执行相同的查询语句，后一次查询看到了前一次查询没有看到的数据。

在可重复读隔离级别下，普通的select语句是快照读，不会看到别的事务插入的数据，幻读只会在当前读才会出现。

SessionA: begin;
SessionA: select * from t where key = 10 for update;
SessionA: update t set key = 100 where key = 10;

SessionB: insert into t(id,key) values(1, 10);

SessionA: commit;

幻读会破坏数据的一致性，假设线程A要把数据库中所有key=10的记录改成key=100，首先线程A执行update语句会对key=10的记录加写锁，线程A执行update语句的过程中，线程B新插入了一条key=10的记录。

等事务A提交之后，binlog中sql的顺序就是：

线程B插入一条key=10的记录。
线程A将key=10的记录修改为key=100。

如果把这个binlog发送给从库去执行，或者用这个binlog来恢复数据，就会出现数据不一致了。

其实问题产生的原因就是在给key=10的记录加锁时，新数据还不存在，不存在也就加不上锁。

如何解决幻读：

幻读产生的原因是，行锁只能锁住数据库中已经存在的记录，但是插入数据这个动作，需要更新的是记录之间的“间隙”，因此InnoDB引入了间隙锁来解决幻读的问题。

⭐间隙锁

间隙锁是在可重复读隔离级别下才有的，它跟其它锁都不太一样，间隙锁的含义是保护这个间隙不会被插入新的数据，所以间隙锁之间是不冲突的，间隙锁只会和 “往间隙锁要保护的间隙中插入新的记录” 这个操作存在冲突关系。

间隙锁通常是和行锁一起使用的，假设表中只有key=5和10两条记录，现在要修改key=5这条记录，就会对key=5这条记录加上一个行锁和两个间隙锁，锁范围就会变成 [0, 5]。也就是说间隙锁会导致锁的粒度变大，这其实是会影响并发度的。

刷脏页

当内存中的数据页和磁盘数据页内容不一致的时候，这个内存页称之为“脏页”，内存数据写入到磁盘后，称之为“干净页”。

通常有四种情况会导致MySQL刷脏页：

第一种情况是，当[redo log](#redo log)写满了，这个时候系统就会阻塞所有更新操作，把[redo log](#redo log)中的操作记录应用到磁盘上，直到[redo log](#redo log)有足够的空闲区域。

第二种情况是，系统内存不足，因为MySQL的更新操作需要先把数据加载到内存中，如果内存放不下新的数据页，就会把最久没有使用过的数据页淘汰掉。如果淘汰的数据页是“脏页”，就会先将脏页写到磁盘上。如果这个脏页旁边的数据页也是脏页，也会把这个脏页一起刷掉，而且这个逻辑可能还会一直蔓延。如果一次查询需要淘汰的脏页太多，就会导致MySQL响应时间变长。

第三种情况是，MySQL认为系统比较空闲的时候，也会刷脏页。

第四种情况是，当MySQL正常关闭的时候，会把所有的脏页都刷新到磁盘上。

⭐读写分离

主从模式基本原理

主库和从库之间会维持一个长连接，主库内部有一个线程，专门用来服务从库这个长连接的。

首先在从库上执行change master命令，设置主库的IP、用户名、密码、binlog文件名、日志偏移量。

1	change master to master_host='xxx' master_port=xxx master_user='xxx' master_password='xxx' master_log_file='binlog.0001' master_log_pos='0'

然后在从库上执行start slave命令，这个时候从库会启动两个线程，一个负责和主库建立连接，另一个负责读取和解析中转日志。
主库校验完用户名和密码之后，就会按照传过来的binlog偏移量，从本地读取日志并发送给从库。
从库收到binlog后，会写到本地的中转日志。
从库中负责读取中转日志的线程，就会解析出日志里面的命令并执行。

双主模式

双主模式就是两个节点互为主从关系，双主模式会有循环复制的问题。就是节点A执行了一条更新语句，会把生成的binlog发送给节点B，节点B执行完binlog中的内容后也会生成一条binlog再发送给节点A。这就会导致节点A和节点B会不断的循环执行这条更新语句。

解决办法是：

要规定两个节点的server id必须不一样。
一个节点接收到binlog执行之后，生成的binlog的server id与原binlog的server id相同。
每个节点收到binlog后，先判断server id跟自己的是否相同，如果相同则表示这个日志是自己生成的，就直接丢弃这个日志。

⭐主从切换

主要有两种方案：基于位点的主从切换，基于GTID的主从切换。

基于位点的主从切换：

假设有A、B、C，D四个节点，其中A节点和B节点互为主从，C节点和D节点是A节点的从节点。也就是双主多从架构。

如果A节点宕机，就需要让C节点和D节点重新指向B节点。

首先切换主库需要在从库上执行change master命令，这个命令需要指定binlog的偏移量。

1	change master to master_host='xxx' master_port=xxx master_user='xxx' master_password='xxx' master_log_file='binlog.0001' master_log_pos='0'

这个偏移量可以在B节点上执行show master status得到当前最新的binlog文件名和偏移量。

基于GTID的主从切换：

首先需要在启动MySQL服务的时候，加上gtid_mode=on和enforce_gtid_consistency=on这两个参数。

在GTID模式下，每个事务都和一个GTID一一对应，这个GTID由MySQL实例的server_id加事务ID组成（GTID=server_id:transaction_id）

MySQL服务会维护一个GTID集合，用来记录这个实例执行过的所有事务，同步数据时会自动跳过已经执行过的事务。

在GTID模式下切换主库，只需要把change master命令中的MASTER_LOG_FILE和MASTER_LOG_POS参数替换为master_auto_position=1就可以了。

1	change master to master_host='xxx' master_port=xxx master_user='xxx' master_password='xxx' master_auto_position=1

⭐主从延迟

其实所有主从架构都有主从延迟的问题，如果从节点同步数据的速度低于主节点生成数据的速度，就会出现延迟。常见的原因有：网络延迟、或者从库服务器性能比较低，都有可能出现过期读的问题。

MySQL中过期读的解决方案主要有：强制走主库、sleep、判断主备无延迟、semi-sync方案、等待主库位点、等待GTID。

强制走主库：

就是对查询请求分类，对于可以接受过期读的场景就在从库上查询，对于无法接受过期读的场景就在主库上查询。

但是对于数据实时性特别高的项目，可能大部分查询都不能是过期读，这样就只能放弃读写分离了。

sleep方案：

就是主库更新之后，查询从库的时候先slepp一下，不过这种方案没办法保证一定不会出现过期读，而且效率太低。

判断主备无延迟：

在从库执行show slave status命令，返回结果里面会显示second_behind_master字段，这个字段表示当前备库延迟了多少秒。

second_behind_master的计算方法是这样的：

首先每个事务的binlog都有一个时间字段，记录的是binlog的生成时间。

从库会读取binlog的生成时间，与系统时间做比较，就可以得到主从库延迟了多久。

每次执行查询请求之前，先执行show slave status命令，判断second_behind_master是否为0，如果为0就在从库上查询，否则就在主库上查询。

semi-sync方案：

semi-sync是半同步复制，它的逻辑是这样的：

首先事务提交的时候，主库会把binlog发送给从库。
从库收到binlog以后，给主库发送一个确认信息，表示收到了。
主库收到确认信息以后，才会给客户端返回“事务完成”的确认。

也就是说，如果开启了semi-sync，就表示所有给客户端返回成功的事务，从库都已经收到了。这样就可以避免过期读。

semi-sync开启方式：

# 1.安装插件
install plugin rpl_semi_sync_master SONAME 'semisync_master.so'; 
# 2.开启半同步
set global rpl_semi_sync_master_enabled = 1;

但是在一主多从的场景下，主库只要等到一个从库的确认消息，就会给客户端响应成功。如果查询请求落到了没有收到binlog的从库上，还是会有过期读的问题。

等待主库位点：

主库更新完数据后，马上执行show master status命令，得到当前主库最新的binlog文件和偏移量（file和position的值）。
然后在从库上执行select master_pos_wait(file, pos)命令，将查询主库得到的binlog文件和偏移量传递进去。

select master_pos_wait(file, pos, [timeout])命令会返回一个数字，表示从指定的binlog位置与当前最新的binlog位置，执行了多少事务。如果没有执行到指定的binlog位置，就返回0，超时返回-1。
如果返回值是大于等于0的数字，就说明从库已经执行过这个binlog了。就可以在从库上查询，否则就到主库上查询。

等待GTID：

主库更新完数据后，马上执行show master status命令，得到最新的GTID（executed_gtid_set）。
然后在从库上执行select wait_for_executed_gtid_set(gtid, 1)，并且把主库的GTID传递进去。

select wait_for_executed_gtid_set(gtid, 1)这条命令逻辑是：等这个数据库执行完GTID对应的事务之后，返回0，超时返回1。
如果返回值是0，就可以在从库上查询。

开启GTID：

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = ON;

⭐全表扫描的影响

InnoDB内存中的数据页是通过Buffer Pool管理的，Buffer Pool可以起到加速查询的作用，具体的加速效果，主要依赖于内存命中率。

Buffer Pool的大小可以通过参数innodb_buffer_pool_size修改，一般可以设置为物理内存的60%~80%.

可以通过show engine innodb status查看当前系统的内存命中率。

其中Buffer pool hit rate 990 / 1000显示的就是内存命中率（990 / 1000表示内存命中率为99%）

Buffer Pool是通过LRU算法来管理内存的，这个算法的核心就是淘汰最久没有使用的数据。

LRU算法通常是用链表实现的，链表的头部代表最常使用的数据，链表的尾部代表最不经常使用的数据。在删除数据的时候，会从链表的尾部开始删除。

如果使用传统的LRU算法，假设我们要全表扫描一个历史表，就会导致Buffer Pool中的数据页全部被淘汰掉。这个时候Buffer Pool的内存命中率就会急剧下降，磁盘压力也会增加，SQL语句的响应会变慢。

所以InnoDB对LRU算法做了改进：

InnoDB按照5:3的比例把整个LRU链表分成了young区域和old区域（链表头部是young区域，链表尾部是old区域）

如果要访问处于young区域的数据页，就会把该数据页移到链表头部。
如果要访问一个不在链表中的数据页，就会淘汰掉链表尾部的数据页，但是新插入的数据页是存在old区域的头部位置。
处于old区域的数据页，每次访问的时候都要判断：
- 如果这个数据页在LRU链表中存活时间超过1秒，就把它移动到链表头部。
- 如果这个数据页在链表中存活时间小于1秒，位置就保持不变。
  
  数据页的存活时间可以通过参数innodb_old_blocks_time调整，默认是1000，单位是毫秒。

改进后的LRU算法应对大量冷数据扫描时的逻辑是这样的：

扫描过程中，需要插入新的数据页，都会被放到old区域。
一个数据页中会有多条记录，也就是说这个数据页会被访问多次，但是数据页内部的记录是有序的，所以数据页从上到下访问第一条记录到最后一条记录之间的时间间隔不会超过1秒，所以这个数据页不会进入到young区域，很快就会被淘汰出去。

改进后的LRU算法可以把大部分比较热的数据继续保留在内存中，从而保证内存命中率。

Join有哪些问题

join的执行流程是这样的：

首先从驱动表（最左边的表）读取一行数据。
然后从数据行中取出关联字段，到被驱动表中去查询符合条件的记录。
重复前面的步骤，直到驱动表循环结束。

在这个过程中，对被驱动表的访问次数主要取决于驱动表的扫描行数，所以应该让小表作为驱动表。

这里的小表按照各自的过滤条件，过滤完成之后，实际参与join关联的数据量。数据量小的就是小表。

其次，如果被驱动表的关联字段没有索引，join算法会先把驱动表中的数据暂存到join_buffer中，然后对被驱动表做全表扫描，和join_buffer中的数据做比对，join的流程就会变成这样：

首先把驱动表的数据存到join_buffer中，直到join_buffer存满。

join_buffer默认是256k，可以通过参数join_buffer_size修改。
对被驱动表做全表扫描，把每一行数据都跟join_buffer中的数据做对比，判断是否满足条件
清空join_buffer
重复执行前面的步骤，直到驱动表循环结束。

在这个过程中，对被驱动表的扫描次数取决于驱动表需要写几次join_buffer，如果join_buffer能够放下驱动表的所有数据，只需要扫描一次被驱动表就可以了。所以对于join操作比较多的数据库，可以增加join_buffer的大小，还应该在关联字段上建立索引。

如果没有join_buffer，就会导致驱动表中的每一条数据都会被驱动表做一次全表扫描，假设驱动表有100条数据，从表有1万条数据，就会对被驱动表做100次全表扫描，总扫描行数就是100万行。

分区表有哪些问题

Range分区语法：

CREATE TABLE `t` (
`ftime` datetime NOT NULL,
`c` int(11) DEFAULT NULL,
KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

MySQL有三种分区：Range分区、Hash分区、List分区。

分区表的优点是可以方便的清理历史数据，如果是按照时间分区的分区表，可以直接删除掉整个分区，从而删除历史数据。

alter table t drop partition ...

需要注意的是，如果需要跨多个分区查询数据，性能会比较慢，这种情况需要重新考虑分区的维度。

另外分区表通常都很大，DDL会非常耗时，尽量避免修改表结构。

Explain

explain的结果集包含如下信息：

1
2
3

+----+-------------+-------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------+-------+

⭐id：用来表示select语句的顺序。

id越大的越先执行。
如果id相同，从上往下依次执行。

⭐select_type：

simple：简单的select查询，没有union或者子查询。
primary：最外层的select查询。
union：union中的第二个或随后的select查询，不依赖于外部查询的结果集
dependent union：union中的第二个或随后的select查询，依赖于外部查询的结果集
subquery：子查询中的第一个select查询，不依赖与外部查询的结果集
dependent subquery： 子查询中的第一个select查询，依赖于外部查询的结果集
derived：用于from子句中有子查询的情况，mysql会递归执行这些子查询，此结果集放在临时表中

⭐table：表示输出行所引用的表名

⭐type：效率从上到下依次排序。

system： 表仅有一行，是const类型的一个特例。
const： 确定只有一行匹配的时候，mysql优化器会在查询前读取它并且只读取一次，速度非常快。
eq_ref： 对于每个来自于前面的表的行组合，从该表中读取一行，常用在一个索引是unique key或者primary key。
ref： 对于来自前面的表的行组合，所有有匹配索引值的行都从这张表中读取,如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。

ref可以用于使用=或<=>操作符的带索引的列
ref_or_null： 类似ref，但是添加了可以专门搜索null值的行。
index_merge： 该访问类型使用了索引合并优化方法，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素

查询条件都有单列索引，如果出现index_merge，并且这类SQL后期使用较频繁，可以考虑把单列索引换为组合索引，这样效率更高
range： 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。

当查询条件有索引，并且使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，才会使用range
index： 在进行统计时非常常见，这种类型实际上会扫描索引树。（比如count、sum、avg之类的函数）
all： 全表扫描

通常可以通过对查询条件字段添加索引的方式避免全表扫描。

⭐possible_keys：mysql可以使用这个索引去辅助查找记录。若为空则表示没有可以使用的索引，可以通过检查where语句看是否可以引用某些列或者新建索引来提高性能。

⭐key：key列显示的是当前表实际使用的索引，如果没有选择索引，则此列为null。

要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

⭐key_len：表示MySQL决定使用的键长度。如果KEY键是NULL，则长度为NULL。在不损失精确性的情况下，长度越短越好。

key len的长度还和字符集有关,latin1一个字符占用1个字节,gbk一个字符占用2个字节,utf8一个字符占用3个字节。

⭐ref：用来显示使用哪个列或常数与key一起从表中选择相应的行。它显示的列的名字（或const），此列多数时候为null

⭐rows：显示的是mysql解析器认为执行此SQL时必须扫描的行数。此数值为一个预估值，不是具体值，通常比实际值小

⭐filtered：mysql 5.7 新的加参数，指的是返回结果的行数所占需要读到的行（rows的值）的比例对于使用join时，前一个表的结果集大小直接影响了循环的行数。

⭐Extra：extra表示不在其他列并且也很重要的额外信息。

using index： 该值表示这个SQL语句使用了覆盖索引，效率很高。
using where： 表示存储引擎搜到记录后进行了后过滤，如果查询未能使用索引，using where的作用只是提醒我们mysql要用where条件过滤结果集。
using temporary：表示mysql需要使用临时表来存储结果集，常见于排序和分组查询。
using filesort：是指mysql无法利用索引直接完成排序（排序的字段不是索引字段），此时会用到缓冲空间来进行排序。
using join buffer：在获取连接条件时没有用到索引，并且需要连接缓冲区来存储中间结果。（性能可以通过添加索引或者修改连接字段改进）

Block Nested Loop是指Block Nested-Loop Join算法：将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数.
impossible where： 表示where条件导致没有返回的行。