公号：码农充电站pro

主页：https://codeshellme.github.io

这 5 篇文章是我在学习 MySQL 的过程中，总结的笔记：

  • 第一篇 MySQL 学习笔记1-基础篇
    • 1，关于 SQL
    • 2，一条 SQL 的执行步骤
    • 3，MySQL 存储引擎
    • 4，数据库的基本操作
    • 5，关于自增主键
    • 6，SELECT 语句顺序
    • 7，WHERE 子句
    • 8，DISTINCT 去重
    • 9，关于 COUNT(*) 操作
    • 10，MYSQL 函数
    • 11，GROUP BY 数据分组
    • 12，子查询(嵌套查询)
    • 13，JOIN 连接查询
    • 14，VIEW 视图
    • 15，存储过程
    • 16，临时表
    • 17，MySQL 权限管理
    • 18，Python 操作 MySQL 的库
  • 第二篇 MySQL 学习笔记2-进阶篇-上
    • 19，MySQL 的基础架构
    • 20，数据库缓冲池
    • 21，数据库中的存储结构
    • 22，InnoDB 中表数据的存储
  • 第三篇 MySQL 学习笔记3-进阶篇-中
    • 23，事务处理
    • 24，事务的隔离级别
    • 25，MySQL 中的锁
    • 26，MVCC 多版本并发控制
    • 27，MySQL 传输数据的原理
  • 第四篇 MySQL 学习笔记4-进阶篇-下
    • 28，Join 语句的原理
    • 29，MySQL 如何进行排序
    • 30，MySQL 中 kill 命令的原理
    • 31，MySQL 中的 mysqldump 命令
    • 32，MySQL 主从同步
    • 33，MySQL 主备原理
  • 第五篇 MySQL 学习笔记5-调优篇
    • 34，关于 MySQL 索引
    • 35，定位数据库 SQL 性能问题

28，Join 语句的原理

三种常用的 JOIN 操作：

在这里插入图片描述

28.1，join 的执行过程

1，被驱动表走索引的情况-NLJ

比如下面语句：

-- t1 中有 100 条数据
-- t2 中有 1000 条数据
-- 这两个表都有一个主键索引 id 和一个索引 a，字段 b 上无索引
select * from t1 straight_join t2 on (t1.a=t2.a);

如果直接使用 join 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表。 straight_join 让 MySQL 使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去 join。在这个语句里，t1 是驱动表，t2 是被驱动表。

这个语句的执行流程是：

  • 从 t1 中读入一行数据 R
  • 从数据行 R 中，取出 a 字段到 t2 里去查找
  • 取出 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分
  • 重复执行步骤 1 到 3，直到 t1 的末尾循环结束

在这个 join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索（字段 a 上有索引，使用了索引）。

这种算法叫做 Index Nested-Loop Join(NLJ) 。

2，被驱动表不走索引的情况-BNL

对于下面的查询，被驱动表是无法使用索引的（因为 b 字段上没有索引）：

select * from t1 straight_join t2 on (t1.a=t2.b);

这种算法叫做 Block Nested-Loop Join(BNL)。

执行过程是这样的：

  • 把 t1 的数据读入线程内存 join_buffer 中
  • 扫描 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回

流程图如下：

在这里插入图片描述

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，就会分段放。

join_buffer_size 越大，一次可以放入的行越多，分成的段数也就越少，对被驱动表的全表扫描次数就越少。因此，如果你的 join 语句很慢，就把 join_buffer_size 改大些。

分段的执行过程是这样的：

  • 扫描表 t1，顺序读取数据行放入 join_buffer 中，放完第 88 行 join_buffer 满了，继续第 2 步；
  • 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
  • 清空 join_buffer；
  • 继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步。

执行流程图如下：

在这里插入图片描述

图中的步骤 4 和 5，表示清空 join_buffer 再复用。

28.2，是否要使用 Join 操作

分两种情况：

  • 如果 Join 时，能利用上被驱动表的索引，Join 操作是很快的
  • 如果 Join 时，不能利用被驱动表的索引，扫描行数就会过多。
    • 尤其是在大表上的 join 操作，会扫描被驱动表很多次，占用大量的系统资源，所以这种 join 尽量不要用。
    • 所以在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现Block Nested Loop字样。
    • 在这里插入图片描述
    • 如果有 Block Nested Loop，表示无法使用被驱动表的索引，则此时的 Join 操作的性能会很差。

28.3，小表驱动大表

使用 Join 操作时，总是应该使用小表做驱动表，性能会更好。

那么到底什么是小表呢？

两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

28.4，MySQL 对 Join 语句的优化

BKA 算法

BKA 算法即 Batched Key Access，该算法是在 MySQL 5.6 之后引入的，它是对 NLJ 算法的优化。其原理与 22.12 节中的 MRR 算法类似，尽量使用顺序读盘。

NLJ 算法的流程如下：

在这里插入图片描述

NLJ 算法：从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。对于表 t2 来说，每次都是匹配一个值。

BKA 算法的流程如下：

在这里插入图片描述

为了一次性地多传些值给表 t2，BKA 算法将表 t1 的数据放到 join_buffer 中。

join_buffer 中放入的数据是 P1~P100，表示只取查询需要的字段。如果 join_buffer 放不下 P1~P100 的所有数据，就会把这 100 行数据分成多段，然后执行上图的流程。

注意，要使用 BKA 优化算法，需要在执行 SQL 语句之前，先设置：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

前两个参数的作用是启用 MRR，因为 BKA 算法依赖 MRR。

28.5，LEFT JOIN ON 后的 AND 和 WHERE

示例：

表1 app_notification_user 里面有两条数据：

在这里插入图片描述

表2 app_notification 里面有一条数据：

在这里插入图片描述

语句 1：

SELECT anu.id, anu.app_notification_id, anu.`status`, an.*  
FROM app_notification_user anu 
LEFT JOIN app_notification an ON anu.app_notification_id=an.id

结果如下，左表有的数据全都有：

在这里插入图片描述

语句2： LEFT JOIN ON AND 和 LEFT JOIN ON WHERE 对比：

SELECT anu.id, anu.app_notification_id, anu.`status`, an.*  
FROM app_notification_user anu 
LEFT JOIN app_notification an ON anu.app_notification_id=an.id AND anu.status=1

在这里插入图片描述

VS：

SELECT anu.id, anu.app_notification_id, anu.`status`, an.*  
FROM app_notification_user anu 
LEFT JOIN app_notification an ON anu.app_notification_id=an.id 
WHERE anu.status=1

在这里插入图片描述

放在 WHERE 里，会将不符合条件的过滤掉；

LEFT JOIN 里加的 AND，左表的数据一直会在，不符合条件的部分会填充为 NULL。

此处参考了这里。

29，MySQL 如何进行排序

当我们使用 order by 就是告诉 MySQL 返回的数据需要进行排序。

例如一个表结构：

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;

用 explain 命令查看该 SQL 的执行情况：

在这里插入图片描述

Extra 中的 Using filesort 表示需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

并且会用到 city 索引：

在这里插入图片描述

29.1，数据的排序过程

MySQL 的排序过程会用到临时表。

该 SQL 的执行流程：

  1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
  2. 从索引 city 找到第一个满足 city='杭州’ 条件的主键 id（ID_X）；
  3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到 city 的值不满足查询条件为止（ID_Y）；
  6. 对 sort_buffer 中的数据按照字段 name 做快速排序；按照排序结果取前 1000 行。

在这里插入图片描述

29.2，sort_buffer_size 参数

排序的过程可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。

sort_buffer_size 是 MySQL 为排序开辟的内存 sort_buffer 的大小：

  • 如果要排序的数据量小于 sort_buffer_size，排序就在内存中（采用快速排序）完成。
  • 如果要排序的数据量太大，内存放不下，则需用磁盘临时文件（采用归并排序）辅助排序。

29.3，max_length_for_sort_data 参数与 rowid 排序

上面的 SQL 查询要返回的字段有三个（city、name、age），在上面的排序过程中，这三个字段都放入了 sort_buffer 中，这种排序叫做 全字段排序。

如果要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

所以，如果单行的长度很大的话，全排序有可能导致性能较低。

max_length_for_sort_data 参数是控制排序的行数据的长度的，如果单行的长度超过这个值，MySQL 就认为单行太大，则会将全排序换成 rowid 排序。

这里单行的长度指的是，所有要返回的字段的定义时的数据类型的长度。 比如上面的 SQL 要返回的字段是（city、name、age）， 那么单行的长度就是 16 + 16 + 4 = 36

rowid 排序只会将要排序的字段(而不是所有要返回的字段)和 rowid 放入 sort_buffer 中，从而避免占用 sort_buffer 过大的空间，进而尽可能少的使用外部排序，最终达到加快排序速度的目的。

如果使用 rowid 排序，那么对于上面的 SQL，放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和 rowid，那么排序过程将是：

  1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
  2. 从索引 city 找到第一个满足 city='杭州'条件的主键 id(ID_X)；
  3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到不满足 city='杭州’条件为止(ID_Y)；
  6. 对 sort_buffer 中的数据按照字段 name 进行排序；
  7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

在这里插入图片描述

请注意对比全字段排序和 rowid 排序的不同。

29.4，全字段排序与rowid 排序

两种排序的选择：

  • 如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
  • 如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

对于 InnoDB 引擎，执行全字段排序会减少磁盘访问，因此会被优先选择。 而对于 Memory 引擎，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，不会导致多访问磁盘。所以 Memory 引擎会优先选择 rowid 排序。

29.5，所有的 order by 都需要排序过程吗

并不是所有的 order by 语句，都需要排序操作的，如果需要排序的字段本来就是有序的，那么就不需要排序了。

比如 order by 主键id，MySQL 就不需要执行排序过程，因为主键id 本来就是有序的。

除了主键id 外，联合索引也会使得字段是有序排列的。

所以，如果建立了 city, name 的联合索引：

alter table t add index city_user(city, name);

索引结构将变成下面这样：

在这里插入图片描述

这时将不再需要排序过程：

在这里插入图片描述

29.6，order by rand() 的执行过程

当我们想要对一个表的数据进行随机排序的时候会用到 order by rand()：

select * from table_name order by rand() limit N;

用 Explain 分析 SQL 如下：

在这里插入图片描述

Extra 字段中的 Using temporary，表示需要使用临时表；Using filesort，表示需要执行排序操作。

该 SQL 的执行流程图：

在这里插入图片描述

由上图可知：order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序。

并不是所有的临时表在内存中，也可以在磁盘上。

tmp_table_size 参数限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。

结论：不论使用哪种类型的临时表，order by rand() 这种写法都会让计算过程非常复杂，需要大量的扫描行数，因此排序过程的资源消耗也会很大。

29.7，order by 对索引的使用

order by 对索引的使用类似 select ... where 查询。

在这里插入图片描述

30，MySQL 中 kill 命令的原理

MySQL 中有两个 kill 命令：

  • kill query + 线程 id，表示终止这个线程中正在执行的语句
  • kill connection + 线程 id，表示断开这个线程的连接
    • 如果这个线程有语句正在执行，要先停止正在执行的语句
    • 这里 connection 可缺省

show processlist; 命令可以查看进程状态 innodb_thread_concurrency 参数设置的是并发线程数

当执行 Kill 命令时，线程并不会马上停止，被 kill 的线程，需要执行到判断状态的“埋点”，才会开始进入终止逻辑阶段。并且，终止逻辑本身也是需要耗费时间的。

有两种情况会导致线程不会马上终止：

  • 线程没有执行到判断线程状态的逻辑
  • 终止逻辑耗时较长
    • 这时候，从 show processlist 结果上看是 Command=Killed，需要等到终止逻辑完成，语句才算真正完成
    • 这类情况，比较常见的场景有以下几种：
      • 超大事务执行期间被 kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。
      • 大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待 IO 资源，导致耗时较长。
      • DDL 命令执行到最后阶段，如果被 kill，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久。

31，MySQL 中的 mysqldump 命令

mysqldump 命令用于备份数据，如下：

mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

该命令将结果输出到文件。参数含义：

  • –single-transaction：在导出数据时，不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法
    • 导数据之前就会启动一个事务，来确保拿到一致性视图。
    • 由于 MVCC 的支持，这个过程中数据是可以正常更新的。
  • –add-locks 设置为 0：表示在输出的文件结果里，不增加LOCK TABLES t WRITE;
  • –no-create-info：表示不需要导出表结构
  • –set-gtid-purged=off：表示不输出跟 GTID 相关的信息
  • –result-file：指定输出文件的路径

t.sql 文件内容如下：

在这里插入图片描述

如果希望生成的文件中一条 INSERT 语句只插入一行数据，可以在执行 mysqldump 命令时，加上参数 –skip-extended-insert。

导出数据后，通过如下命令导入数据：

mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"

source 并不是一条 SQL 语句，而是一个客户端命令，客户端执行这个命令的流程：

  • 打开文件，默认以分号为结尾读取一条条的 SQL 语句
  • 将 SQL 语句发送到服务端执行

物理拷贝表的方法

直接把 db1.t 表的 .frm 文件和 .ibd 文件拷贝到 db2 目录下，是不可行的。

因为，一个 InnoDB 表，除了包含这两个物理文件外，还需要在数据字典中注册。直接拷贝这两个文件的话，因为数据字典中没有 db2.t 这个表，系统是不会识别和接受它们的。

在 MySQL 5.6 版本引入了可传输表空间的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能。

32，MySQL 主从同步

提升数据库高并发访问效率的三种方式：

  • 首先需要考虑的是优化 SQL 和索引，这种方式简单有效。
  • 其次是采用缓存的策略，比如使用 Redis。
  • 最后是对数据库采用主从架构，进行读写分离。

32.1，主从同步的作用

  • 达到读写分离的效果：
    • 将同一份数据被放到多个数据库中，其中一个数据库是 Master 主库，其余的多个数据库是 Slave 从库。
    • 当主库进行更新的时候，会自动将数据复制到从库中。
    • 当读取数据的时候，会从从库中进行读取，也就是采用读写分离的方式。
  • 达到数据备份的效果
  • 达到高可用性：当服务器出现故障或宕机的情况下，可以切换到从服务器上，保证服务的正常运行。

32.2，主从同步的原理

主从同步是基于 Binlog 进行数据同步的，它是二进制日志文件，存储的是一个个的事件（Event），这些事件分别对应着数据库的更新操作，比如 INSERT、UPDATE、DELETE 等。

需要注意的是，不是所有版本的 MySQL 都默认开启服务器的二进制日志，在进行主从同步的时候，我们需要先检查服务器是否已经开启了二进制日志。

在主从复制过程中，会基于 3 个线程来操作：

  • 一个主库线程：即 Binlog 转储线程，当从库线程连接的时候，主库可以将 Binlog 日志发送给从库。
  • 两个从库线程：
    • 一个是从库 I/O 线程：向主库发送请求更新 Binlog，并拷贝到本地形成中继日志（Relay log）。
    • 一个是从库 SQL 线程：读取从库中的中继日志，并执行日志中的事件，从而将从库中的数据与主库保持同步。

在这里插入图片描述

32.3，主从同步的数据一致性问题

进行主从同步的内容是二进制日志，在进行网络传输的过程中就一定会存在延迟，这样就可能造成用户在从库上读取的数据不是最新的数据。

比如我们对一条记录进行更新，这个操作是在主库上完成的，而在很短的时间内又对同一个记录进行了读取，这时候从库还没有完成数据的更新，那么我们通过从库读到的数据就是一条旧的记录。

32.4，如何解决数据一致性问题

主从数据库之间数据复制有 3 种方式，按照数据一致性从弱到强来进行划分：

  • 异步复制：主库写成功后，及返回成功，而不保证从库中写成功。

在这里插入图片描述

  • 半同步复制：MySQL5.5 版本之后开始支持半同步复制的方式。
    • 原理是在客户端提交 COMMIT 之后不直接将结果返回给客户端，而是等待至少有一个从库接收到了 Binlog，并且写入到中继日志中，再返回给客户端。
    • 这样做的好处是提高了数据的一致性，当然相比于异步复制来说，至少多增加了一个网络连接的延迟，降低了主库写的效率。
    • 在 MySQL5.7 版本中增加了一个rpl_semi_sync_master_wait_for_slave_count 参数，我们可以对应答的从库数量进行设置，默认为 1，也就是说只要有 1 个从库进行了响应，就可以返回给客户端。
    • 如果将这个参数调大，可以提升数据一致性的强度，但也会增加主库等待从库响应的时间。
  • 组复制：简称 MGR（MySQL Group Replication），是 MySQL 在 5.7.17 版本中推出的一种新的数据复制技术，这种复制技术是基于 Paxos 协议的状态机复制。
    • MGR 将 MySQL 带入了数据强一致性的时代，是一个划时代的创新，其中一个重要的原因就是 MGR 是基于 Paxos 协议的。

33，MySQL 主备原理

MySQL 通过 binlog 来完成主备之间的数据备份。

MySQL 主备流程图：

在这里插入图片描述

备库 B 跟主库 A 之间维持了一个长连接，一个事务日志同步的完整过程是这样的：

  • 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
  • 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
  • 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
  • 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
  • sql_thread 读取中转日志，解析出日志里的命令，并执行。
    • 在 MySQL 5.6 版本之前，MySQL 只支持单线程复制，由此在主库并发高、TPS 高时就会出现严重的主备延迟问题。
    • 在之后的版本，支持多线程复制，即由一个 sql_thread 变成多个，模型如下：
    • 在这里插入图片描述
    • coordinator 就是原来的 sql_thread，负责读取中转日志和分发事务。
    • work 线程的个数，由参数 slave_parallel_workers 决定。

33.1，binlog 的三种格式

binlog 的三种格式：

  • statement：基于 SQL 语句
    • 有些 statement 格式的 binlog 可能会导致主备不一致
  • row：基于行，缺点是很占空间
  • mixed：上面两种的混合方式
    • MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。
    • mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。

以下命令可以查看 binlog 的内容：

# 获取 binlog 文件列表
> show binary logs;
# 查看第一个文件的内容
> show binlog events;
# 查看指定文件的内容
> show binlog events 'binlog文件名'：

mysqlbinlog 命令可以查看 binlog 文件的内容：

mysqlbinlog binlog.000003（文件路径） --start-position=8900（开始位置） -vv | more

用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行：

# 将 master.000001 文件里面从第 2738 字节到第 2973 字节中间这段内容解析出来
mysqlbinlog master.000001  --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

33.2，双主架构

实际生产上使用比较多的是双 M 结构，其主备切换流程入下：

在这里插入图片描述

33.3，主备延迟

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值。

在备库上执行 show slave status 命令，返回结果里面的 seconds_behind_master，用于表示当前备库延迟了多少秒。

主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢。

33.4，主备切换步骤

安全的主备切换步骤：

  • 判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
  • 把主库 A 改成只读状态，即把 readonly 设置为 true；
  • 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
  • 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；把业务请求切到备库 B。

这个切换流程，一般是由专门的 HA 系统来完成的，我们暂时称之为可靠性优先流程。

33.5，一主多从架构

一主多从，一般用于读写分离，主库负责所有的写入和一部分读，其他的读请求则由从库分担。

在这里插入图片描述

其中，A 和 A’互为主备，B，C，D 为备库。

当主库发生故障，主备需要切换：

在这里插入图片描述

一主多从结构在切换完成后，A’会成为新的主库，从库 B、C、D 也要改接到 A’。

把节点 B 设置成节点 A’的从库的时候，需要执行一条 change master 命令：

CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
MASTER_LOG_FILE=$master_log_name 
MASTER_LOG_POS=$master_log_pos

其中 MASTER_LOG_FILE 和 MASTER_LOG_POS 合称为同步位点，也就是主库对应的文件名和日志偏移量。

33.6，读写分离存在的问题

由于主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态，这称为过期读。

应对过期读有以下方案：

  • 强制走主库方案：将查询请求做分类
    • 对于必须要拿到最新结果的请求，强制将其发到主库上
    • 对于可以读到旧数据的请求，才将其发到从库上
    • 如果所有查询都不能过期，则不能采用此方案
  • sleep 方案：主库更新后，读从库之前先 sleep 一下。
    • 这个方案假设：大多数情况下主备延迟在 1 秒之内，做一个 sleep 可以有很大概率拿到最新的数据。
  • 判断主备无延迟方案：有三种方式
    • 判断 seconds_behind_master 参数
    • 对比位点
    • 对比 GTID 集合
  • 配合 semi-sync 方案：半同步复制方案
  • 等主库位点方案
  • 等 GTID 方案

33.7，如何判断主库异常

在主从架构中，当主库出问题后，需要进行主从切换，那如何判断主库异常了呢？