MySQL笔记

引言与基础

如有谬误请联系博主

定义

数据库（Database）：是按照数据结构来组织、存储和管理数据的仓库。

数据库分为关系型数据库与非关系型数据库：

• 关系型数据库：基于关系模型，使用表格、行和列来组织数据

  • 特点：通过SQL进行数据操作，支持ACID（原子性、一致性、隔离性、持久性）事务，确保数据的完整性和一致性
  • 常见有：MySQL、PostgreSQL、Oracle Database

• 非关系型数据库：不依赖于表格模型，而是使用其他形式的数据模型，如键值对、文档、宽列存储、图形数据库等

  • 特点：通常是为了解决大规模数据集的存储和检索问题，可以提供更高的可扩展性和灵活性
  • 常见有：Redis键值存储、MongoDB文档存储

数据类型

MySQL有种多种数据类型：

类型	类型举例
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	DATE日期值、DATETIME混合日期和时间值、TIMESTAMP混合日期和时间值，时间戳、TIME时间值或持续时间、YEAR年份值
文本字符串类型	CHAR定长字符串、VARCHAR变长字符串、TINYTEXT、TEXT长文本数据、MEDIUMTEXT、LONGTEXT、BLOB二进制数据
枚举类型	ENUM枚举
集合类型	SET集合
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值类型：GEOMETRY、POINT、LINESTRING、POLYGON；
集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

约束

为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

• 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
• 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
• 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
• 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。

它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

根据约束起的作用，约束可分为：

• NOT NULL 非空约束

  规定某个字段不能为空

• UNIQUE 唯一约束

  规定某个字段在整个表中是唯一的

• AUTO_INCREMENT 自增

  规定某个字段的值自增，一个表最多只能有一个自增长列

• PRIMARY KEY 主键(非空且唯一)约束

  用来唯一标识表中的一行记录

• FOREIGN KEY 外键约束

  该字段的值必须匹配另一个表（称为父表或主表）的主键或唯一键的值，确保数据的引用完整性。

• DEFAULT 默认值约束

  给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

• CHECK 检查约束

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果

索引

索引（Index）是数据库中用于提高查询效率的一种数据结构，它类似于书籍的目录，允许数据库系统快速定位到表中的数据，而不需要扫描整个表。

索引可以极大地加快数据检索的速度，但也会增加写操作（如插入、删除、更新）的时间，因为索引本身也需要被更新。

查看索引

SHOW INDEX FROM table_name1;

删除索引

DROP INDEX index_name on table_name1;

索引的优点

• 提高查询速度：索引可以显著减少数据检索所需的时间。
• 加速排序和分组：索引可以加快基于某些列的排序和分组操作。

索引的缺点

• 增加存储空间：索引需要额外的存储空间。
• 影响写操作性能：索引需要在插入、删除和更新操作时被维护，这可能会降低这些操作的性能。

索引的类型

四种索引类型：FULLTEXT、HASH、BTREE、RTREE

• B-Tree 索引

  • 普通索引：仅加速查询
  • 唯一索引：加速查询 + 列值唯一（可以有null）
  • 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

• 全文索引：对文本的内容进行分词，进行搜索

• 索引合并：使用多个单列索引组合搜索

• 覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

• 聚簇索引：表数据是和主键一起存储的，主键索引的叶结点存储行数据(包含了主键值)，二级索引的叶结点存储行的主键值。

  使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容)

索引和约束（如主键、唯一约束、外键）是数据库中两个不同的概念，但它们之间存在联系：

1. 主键约束：每个表的主键列会自动创建一个唯一索引。主键保证表中每条记录的唯一性，并且主键列上的索引有助于快速检索记录。
2. 唯一约束：唯一约束确保列中的所有值都是唯一的。类似于主键，唯一约束列也会自动创建一个唯一索引，以保证数据的唯一性并加速查询。
3. 外键约束：外键约束用于维护两个表之间的关系，确保引用的数据完整性。虽然外键本身不直接创建索引，但是外键列通常也是索引的候选列，因为它们经常用于连接查询。删除外键约束后，必须手动删除对应的索引
4. 非唯一索引：除了由约束自动创建的索引外，还可以根据需要手动创建非唯一索引。这些索引不保证列值的唯一性，但可以用于加速查询。

索引是优化数据库查询性能的重要工具，而约束则是确保数据完整性和一致性的机制。

---

SQL语句实践

注意：

• SQL关键字不区分⼤⼩写，但是建议关键字⼤写，⽅便阅读
• 每个SQL语句需要以分号；结尾

开始：

SHOW DATABASES; -- 查看所有数据库
USE database_name; -- 切换到指定数据库
SHOW TABLES; -- 显示该数据库中的所有表
SELECT/UPDATE/DELETE/ALTER .... -- 伪代码，代表增删改查等操作

库基础

创建库：

CREATE DATABASE IF NOT EXISTS game -- 指定数据库名为game
DEFAULT CHARACTER SET utf8mb4 -- 指定数据库字符集为utf8mb4
COLLATE utf8mb4_0900_ai_ci; -- 指定排序规则
-- 上⾯的语句等价于：
CREATE DATABASE game;
-- 因为MySQL8.0以后默认字符集为utf8mb4，默认排序规则为utf8mb4_0900_ai_ci。

删除库：

DROP DATABASE game;

表基础

创建表

CREATE TABLE player ( -- 字段名、数据类型、约束
	id int DEFAULT NULL,
	name varchar(45) DEFAULT NULL,
	level int DEFAULT NULL,
	exp int DEFAULT NULL,
	gold decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

删除表

DROP TABLE player; -- 直接删除表，不检查是否存在

查看表

desc table_name1;

插入数据

INSERT INTO table_name1 (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
-- 例如
INSERT INTO users (username, email, birthdate, is_active)
VALUES ('test', 'test@runoob.com', '1990-01-01', true);

更新数据

可以更新一个或多个表的数据，格式：

UPDATE table_name1 SET column1 = value1, column2 = value2, ... WHERE condition;

案例：更新多个列的值

UPDATE orders SET status = 'Shipped', ship_date = '2023-03-01' WHERE order_id = 1001;

案例：更新所有行

UPDATE students SET status = 'Graduated';

案例：通过子查询计算每个Premium类型客户的总购买金额，并将该值更新到 total_purchases 列中

UPDATE customers
SET total_purchases = (
    SELECT SUM(amount)
    FROM orders
    WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';

查询数据

SELECT：用于查询表中所有数据，可以配合多种方式搜索不同字段、数据的结果。

SELECT * FROM table_name1; -- *表示所有字段
SELECT column1 FROM table_name1; -- 查询指定字段
SELECT DISTINCT column1 FROM table_name1; -- DISTINCT语句去掉重复值

WHERE：⽤来按照指定的条件过滤数据，其中有很多子句可以配合使用

SELECT column1, column2 -- SQL语句可以写在⼀⾏，也可以分成多⾏
FROM table_name1
WHERE condition; -- 伪代码，condition为某一条件，例如level >= 10;

AND、OR、NOT：⽤于组合WHERE⼦句中的多个条件。优先级: NOT > AND > OR

SELECT column1, column2 FROM table_name1 WHERE condition1 AND/OR condition2 AND/OR condition3;

IN：可以指定WHERE⼦句中的多个值

SELECT * FROM player WHERE level IN (1,3,5);

BETWEEN：⽤来选取介于数据范围内的值

SELECT * FROM player WHERE level BETWEEN 1 AND 10;

LIMIT：限制查询返回的条数

SELECT * FROM player LIMIT 3;

LIKE：可以满足某一匹配模式的值，"%"匹配不定长字符、"_"匹配单一字符

SELECT * FROM player WHERE name LIKE '张%';

REGEXP：正则，用"^"开头、"$"结尾，"a|b"表或，"."表任意等

SELECT * FROM player WHERE name LIKE '^张.?';

ORDER BY：⽤于升序ASC或者降序DESC排序结果集，默认升序

SELECT id, level FROM player ORDER BY level DESC; -- 查询id和等级，根据level等级降序

GROUP BY：将查询结果分组，通常结合聚合函数使用

SELECT sex, COUNT(*) from player group by sex; -- 查询性别并使用COUNT聚合函数计算性别数量，将性别分组

HAVING：可以筛选分组后的数据，配合GROUP BY语句使用

SELECT level, COUNT(level) FROM player GROUP BY level HAVING COUNT(level) > 4; -- 查询等级并使用COUNT聚合函数计算数量，输出数量大于4的结果

UNION（并集）：⽤于合并两个或多个SELECT⼦句的结果（并集），默认去重复，如果允许重复值，请使⽤UNION ALL。

SELECT * FROM player WHERE level BETWEEN 1 AND 5
UNION
SELECT * FROM player WHERE gold BETWEEN 1 AND 5;

INTERSECT（交集）：⽤于合并两个或多个SELECT⼦句的结果（交集）

SELECT * FROM player WHERE level BETWEEN 1 AND 5
INTERSECT
SELECT * FROM player WHERE gold BETWEEN 1 AND 5;

EXCEPT（差集）：⽤于合并两个或多个SELECT⼦句的结果（差集）

SELECT * FROM player WHERE level BETWEEN 1 AND 5
EXCEPT
SELECT * FROM player WHERE gold BETWEEN 1 AND 5;

聚合函数

用于计算表中数据，配合SELECT语句使用。有COUNT()、SUM()、AVG()、MAX()、MIN()、SUBSTR()等

条件分支

在SQL中为CASE WHEN，类似为IF函数，格式为：

CASE 
WHEN <求值表达式> THEN <表达式1>
WHEN <求值表达式> THEN <表达式2>
ELSE <表达式>
END

例子：

SELECT student_name,score,
  (CASE
    WHEN score < 60 THEN '不及格'
    WHEN score >= 60 AND score < 80 THEN '及格'
    WHEN score >= 80 THEN '优秀'
    ELSE '异常'
  END) AS "分数"
FROM table_name1;

连接

使用JOIN在在两个或多个表中查询数据

JOIN按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

SELECT column_name(s) FROM table1
(INNER) JOIN table2
ON table1.column_name = table2.column_name;

LEFT JOIN（左连接）：查找左表有，右表null的数据

SELECT * FROM player
LEFT JOIN equip
ON player.id = equip.player_id;

RIGHT JOIN（右连接）：查找右表有，左表null的数据

SELECT * FROM player
RIGHT JOIN equip
ON player.id = equip.player_id;

SQL例题

1、统计作答次数

有一个试卷作答记录表exam_record，表项如下：exam_record表（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

请从中统计出总作答次数total_pv、试卷已完成作答数complete_pv、已完成的试卷数complete_exam_cnt。

select
    COUNT(uid) AS total_pv, # 总共考试次数
    COUNT(score != '' or Null) AS complete_pv, # score非空，则已完成考试
    COUNT(DISTINCT CASE WHEN score IS NOT NULL THEN exam_id END) AS complete_exam_cnt # score非空，返回exam_id，distinct去重，返回已完成的试卷数
from exam_record

来源：腾讯音乐娱乐集团2024校园招聘-后台开发笔试（I）

---

数据库设计

范式理论

反范式设计

数据库设计的最佳实践

---

逻辑架构

逻辑执行过程

一般由连接器管理连接，当一个SQL请求由客户端发送到服务端过后：

1. 如果开启查询缓存，则查询缓存中是否有对应记录
2. 没有的话，由分析器对请求中的语句进行词法、语法分析，解析出关键字以及校验SQL的语法
3. 分析完成后，由优化器重写查询，选择最优的查询逻辑
4. 执行器则负责执行优化后的SQL语句，决定此时是继续读取下一行还是返回结果集

MySQL可以分为Server层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

从图中可以看出，不同的存储引擎共用一个Server 层，即连接器到执行器的部分

连接器

作用：负责跟客户端建立连接、获取权限、维持和管理连接。

$ mysql -h$ip -P$port -u$user -p # 即该命令

• 如果用户名或密码不对，你就会收到一个”Access denied for user”的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

即，用户A成功建立连接后，即使管理员账号对用户A的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

长短连接：

数据库中，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

可以使用 show processlist 命令中看到当前已建立的连接

查询缓存

作用：对查询请求直接缓存在内存中，为后续同样的查询语句直接提供查询。

但是查询缓存往往弊大于利。查询缓存的失效非常频繁，有以下几种表现：

1. 只要有对一个表的更新，这个表上所有的查询缓存都会被清空
2. 对于更新压力大的数据库来说，查询缓存的命中率会非常低

可以将参数query_cache_type设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。

而对于确定要使用查询缓存的语句，可以用SQL_CACHE显式指定：

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

MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

分析器

作用：对请求的 SQL 语句做解析，关键字分析和语法分析。

分析顺序：

1. 词法分析：输入是一个由多个字符串和空格组成的一条 SQL 语句，分析语句中的select、insert等关键字以确认是查询语句、插入语句等，以及识别表名、列ID等用户自行定义的字符。
2. 语法分析：根据词法分析的结果，语法分析器会根据语法规则，判断输入的 SQL 语句是否满足 MySQL 语法。

优化器

作用：选择最优的查询执行计划，以提高数据库查询的性能

优化器可以：

1. 查询重写：优化器可以重写查询，以简化或优化表达式，提高执行效率。
2. 成本估算：优化器根据统计信息评估不同执行计划的成本，选择预计最少资源消耗的方案。
3. 子查询优化：将某些子查询转化为连接操作，以提升查询性能。
4. 选择最优的执行策略：例如在使用UNION时，优化器会决定是使用UNION ALL还是UNION，以避免不必要的重复数据消除。
5. 聚合优化：在使用GROUP BY和ORDER BY时，优化器可以选择是否提前进行聚合操作以减少数据量。
6. 并行查询：优化器可以决定是否采用并行执行来加速查询。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

在后面的章节中会单独展开优化器，详细说明优化器的内容

执行器

作用：执行查询计划并返回结果集

开始执行的时候，要先判断一下你对表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误。

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

示例，在ID字段没有索引的情况下，语句select * from T where ID=10;的流程：

1. 调用 InnoDB 引擎接口读取数据：
   • 首先，InnoDB 引擎会从磁盘读取表的第一页（如果该页不在 buffer pool 中），将其加载到内存中的 buffer pool。
   • 由于 ID 字段没有索引，InnoDB 需要进行全表扫描。
   • 引擎接口取这个表的第一行，判断 ID 值是否为 10。如果不是，则跳过；如果是，则将这行存入结果集中。
2. 继续读取下一行：
   • 调用引擎接口取“下一行”：
     • 如果当前页的数据已经在内存中，可以直接访问；
     • 否则，需要从磁盘读取下一页的数据到 buffer pool。
   • 重复相同的判断逻辑，直到取到这个表的最后一行。
3. 返回结果集：
   • 执行器将上述遍历过程中所有满足条件的行组成的记录集，作为结果集返回给客户端。

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”接口，之后循环取“满足条件的下一行”接口。这些接口都在引擎中已定义好。

我们可以在数据库的慢查询日志中看到一个rows_examined的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行时累加的。

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的。

在后面的章节中会单独展开存储引擎的内部机制

redo log

背景：如果每一次的更新操作都需要写进磁盘，然后磁盘找到对应的那条记录并更新，那么整个过程 IO 成本、查找成本都很高。

那么对应的优化技术就是 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志（redo log），再写磁盘。

所以对于该技术，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 中，并将新值更新到对应内存，这个时候的更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。

在 InnoDB 中，redo log 是先写入到内存中的日志缓存，之后再异步地刷写到磁盘。这个日志缓冲区会定期被刷新到磁盘上的 redo log 文件中，以确保数据的持久性。

“将新值更新到对应内存”指的是 InnoDB 的 buffer pool，它是一个内存区域，用于缓存数据库的表和索引数据。它也会在合适的时机（例如，系统空闲时或者 buffer pool 满时）进行刷写（即通过一个过程称为“脏页刷写”）到磁盘。

不过InnoDB 的 redo log 是固定大小的，所有记录从头开始写，当写到末尾就又回到开头循环写。在擦除记录前会把记录更新到数据文件。

• write pos 是当前记录的位置，一边写一边后移
• checkpoint 是当前要擦除的位置，也是往后推移并且循环

write pos 和 checkpoint 之间的是日志上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示日志满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

bin log

上述的 redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

区别有：

1. 物理日志与逻辑日志

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

2. 循环与追加

   • redo log 是循环写的，空间固定会用完；

   • 而binlog 是可以追加写入的。

     “追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

此时了解了上述两个日志，再以更新语句UPDATE table_name SET column_name = column_name + 1 WHERE ID = 2;理解一下执行流程，以及存储引擎在其中的位置：

1. 查找数据：执行器请求引擎获取 ID=2 的行。由于 ID 是主键，引擎通过树搜索直接找到该行。
   • 若该数据页已在内存中，直接返回给执行器；
   • 否则，从磁盘读取到内存后再返回。
2. 更新数据：执行器获取到的行数据增加 1（如原值 N 变为 N+1），然后调用引擎接口写入新数据。
3. 记录更新：引擎将新数据更新到内存，并将更新操作记录到 redo log，处于准备状态（prepare），随后告知执行器可提交事务。
4. 生成并写入 binlog：执行器生成该操作的 binlog 并写入磁盘。
5. 提交事务：执行器调用引擎的提交事务接口，引擎将 redo log 状态改为提交（commit），完成更新。

图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的

在上述的redo log中有两个状态转变，prepare 和 commit，这是”两阶段提交”。

两阶段提交

两阶段提交是为了让两份日志之间的逻辑一致（redo log和bin log），它是跨系统维持数据逻辑一致性时常用的一个方案，即使我们不做数据库内核开发，日常开发中也有可能会用到。

通过两阶段提交，掉电后发现的未完成的事务将会被标记为需要回滚。通过这种方式，数据库系统能够确保所有参与节点在重启后，根据日志中的信息决定事务的最终状态。如果事务处于准备状态，数据库将会回滚所有未完成的操作，确保 redo log 和 bin log 的一致性。

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。

示例语句：UPDATE table SET c = 1 WHERE condition;

• 先写redo log

  1. 假设在 redo log 写入完成后，binlog 尚未写入完毕，MySQL 进程异常重启。

  2. 此时，尽管 redo log 已经写入，系统即使崩溃也能恢复数据，因此恢复后的行 c 的值仍为 1。

     然而，由于 binlog 尚未完成写入，这条语句并没有记录在 binlog 中。

  3. 因此，当使用 binlog 进行数据恢复时，备份日志中缺少了这条更新语句，恢复后的行 c 的值将会是 0，和原库的值不一致。

• 先写bin log

  1. 如果先写 binlog 后写 redo log，而在 binlog 写入完成后发生崩溃，由于 redo log 尚未写入，崩溃恢复后该事务将无效，此时行 c 的值为 0。

     不幸的是，binlog 中已经记录了“将 c 从 0 改为 1”的操作。

  2. 因此，在后续使用 binlog 进行恢复时，将会多出一个事务，恢复后的行 c 的值会是 1，依然与原库的值不一致。

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

当我们需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，而这个“不一致”就会导致线上出现主从数据库不一致的情况。

简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

change buffer

背景：在更新数据页时，如果每次都需要从磁盘读取数据页进行更新，整体的 IO 成本和查找成本将非常高。

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

在 InnoDB 中，每个数据页的大小默认是 16KB

在更新操作涉及的数据页不在内存中的情况下，InnoDB 引擎会首先将更新操作记录到 change buffer，并将新值更新到内存中的对应位置，这时更新操作即被视为完成。在后续访问该数据页时，InnoDB 会将数据页读入到内存，随后将存储在change buffer中的更新操作应用到该数据页，以确保数据的一致性。

InnoDB 会将更新操作缓存在 change buffer 中，而不是立即从磁盘读取数据页。

change buffer 中的操作会在合适的时机（如系统空闲时或访问该数据页时）进行 merge，将更新应用到实际的数据页。这样可以避免频繁的磁盘 IO，提高性能。

需要注意的是，change buffer 的操作同样是可以持久化的。虽然它在内存中有拷贝，但它的内容也会被写入到磁盘，以确保数据的可靠性和持久性。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。

change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

使用场景：

change buffer 主要适用于普通索引的更新操作。当对普通索引进行插入或更新时，如果相关的数据页不在内存中，InnoDB 可以直接将这些更新操作缓存在 change buffer 中，避免频繁的磁盘 IO，从而提升性能。

然而，对于唯一索引的更新操作，InnoDB 必须在执行操作前先验证唯一性约束。这意味着在插入记录（例如 (4, 400)）时，需要先检查表中是否已存在 k=4 的记录。这个过程需要将相关数据页读入内存，才能进行判断和确认，那都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新操作无法使用到 change buffer。

总结来说，change buffer 适用于普通索引的更新操作，以减少磁盘访问；而唯一索引由于需要验证约束，不能利用 change buffer 来提升性能。

我们理解了 change buffer 的机制，接下来讨论在表中插入新记录（4,400）时，InnoDB 的处理流程。

当目标页在内存中时

• 唯一索引：定位 3 和 5 之间，判断到没有冲突，插入记录，语句执行结束。
• 普通索引：定位 3 和 5 之间，插入记录，语句执行结束。

这种情况下，唯一索引和普通索引的差别主要体现在额外的判断，CPU 开销很小。

当目标页不在内存中时

• 唯一索引：读取数据页到内存，判断到没有冲突，插入记录，语句执行结束。
• 普通索引：更新记录在 change buffer，语句执行结束。

显然唯一索引多了读入内存的操作。而由于 change buffer 减少了磁盘访问，显著提升了该场景下普通索引的更新性能。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。因此，change buffer 在减少磁盘随机 I/O 访问的同时，极大地提升了更新操作的效率，尤其当目标页不在内存中的情况下表现尤为突出。

但是，不是所有场景普通索引使用 change buffer 都可以起到加速作用。

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。

对于写多读少常见的就是账单类、日志类的系统。

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

Innodb中的写入原理

内存的查询与写入效率远高于磁盘，所以一切的逻辑处理和读取写入都操作内存中的数据，于是诞生了buffer pool。

buffer pool：内存中的缓存区域，用来存放最近使用过的数据页。

当执行了更新操作：

1. 记录到undo log（回滚日志）

   undo log是Innodb的专属日志文件，用于事务回滚和MVVC

2. 更新内存中的数据页，即buffer pool

3. 记录到redo log（重做日志），标记为准备状态

   数据页更新到buffer pool后，为了保证能够掉电恢复，也写一份到redo log对应内存中

   后续会由其他线程将redo log buffer中的数据写入磁盘中的redo log，实现掉电后磁盘从redo log中恢复数据

4. 记录到bin log（归档日志）

   完整记录所有请求操作（完整语句或者受影响的行id），也可作为主从/主备架构中的关键日志传输给从/备库

5. 提交事务

   在此之中，主从/主备架构的策略（半同步、异步）也会影响当前作为主库的commit

Innodb中的buffer pool

todo

---

事务处理

事务的概念

事务就是要保证一组数据库操作，要么全部成功，要么全部失败

在 MySQL 中，事务支持是在引擎层实现的。MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。

比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

隔离性与隔离级别

ACID 是数据库管理系统中事务的四个基本特性，保证了事务操作的可靠性和一致性：

• Atomicity原子性

  保证事务中的所有操作要么完全成功，要么完全失败。换句话说，事务被视为一个“原子”操作，要么全部执行，要么全部不执行。

  假设一个银行转账操作，涉及从一个账户扣款和向另一个账户存款。如果在事务过程中发生了错误（如网络中断），那么这两个操作都会被回滚，确保没有账户在转账过程中部分变动。

• Consistency一致性

  一致性保证事务执行前后，数据库从一个一致性状态转变到另一个一致性状态。也就是说，事务完成时，数据库的所有规则（如完整性约束、数据类型等）都不会被破坏。

  如果一个银行账户的余额不能为负数，那么在事务开始和结束时，账户余额始终保持在合法的范围内。

  如果事务执行中发生了错误导致余额为负，则必须回滚。

• Isolation隔离性

  保证事务的执行不会受到其他事务的干扰。即使多个事务并发执行，每个事务都像是在独立执行一样，不会相互影响。事务的执行过程对于其他事务是不可见的，直到事务提交。

  假设有两个事务同时执行，一个是从账户A转账到账户B，另一个是查询账户A的余额。如果事务1正在执行，而事务2执行查询操作，事务2应该读取事务1提交前的账户余额（事务1的操作对事务2不可见，直到事务1提交）。

• Durability持久性

  保证一旦事务提交，对数据库的修改就永久保存。即使系统崩溃或电力故障，已经提交的事务的结果也不会丢失。

  假设用户从银行账户中转账并提交事务后，即使在事务提交后发生了系统崩溃，转账操作的结果（例如扣款和存款）应该已经被永久记录在数据库中。

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题

为了解决这些问题，就有了“隔离级别”的概念，即ACID中的I隔离性。不过隔离得越严实，效率就会越低。

事务隔离级别包括：

• 读未提交（read uncommitted）

  一个事务还没commit提交时，它做的变更就能被别的事务看到。

• 读提交（read committed）

  一个事务commit提交之后，它做的变更才会被其他事务看到。

• 可重复读（repeatable read）

  一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

• 串行化（serializable ）

  当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

锁机制

数据库锁设计的初衷是处理并发问题。

作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。

而锁就是用来实现这些访问规则的重要数据结构。

读锁（共享锁）：当一个线程对表或某个数据库对象加了读锁时，其他线程也可以加读锁，但不能加写锁。多个线程可以同时对表进行读操作，只要没有写锁的竞争。换句话说，读锁是共享的，多个线程可以同时获取读锁，互不干扰。

写锁（排他锁）：当一个线程对表或某个数据库对象加了写锁时，其他线程既不能加写锁，也不能加读锁。写锁是独占的，意味着只有一个线程可以执行表结构的变更操作，其他线程不能同时对表进行任何操作。

互斥：读锁之间是共享的，不互相干扰；但读锁和写锁之间是互斥的，写锁和写锁之间也是互斥的，确保数据一致性和操作的安全性。

全局锁

顾名思义，全局锁就是对整个数据库实例加锁。全局锁的典型使用场景是做全库的逻辑备份

这里有两个加锁的方法：

1. MySQL 提供了一个加全局读锁的方法（FTWRL），命令是Flush tables with read lock。

   • 当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的语句均会被阻塞

     如：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

2. 还有官方自带的逻辑备份工具mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

   有了这个功能，为什么还需要 FTWRL 呢？

   使用这个的前提是引擎要支持这个隔离级别。比如，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时就需要使用 FTWRL 命令了。

所以single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。

既然要全库只读，为什么不使用 set global readonly=true 的方式呢？

确实 readonly 方式也可以让全库进入只读状态，但还是会建议用 FTWRL 方式，主要有两个原因：

1. 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。

   因此，修改 global 变量的方式影响面更大，不建议使用。

2. 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。

   而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

业务的更新不只是增删改数据（DML)，还有可能是加字段等修改表结构的操作（DDL）。

不论是哪种方法，一个库被全局锁上以后，你要对里面任何一个表做加字段操作，都是会被锁住的。

表级锁

MySQL 里面表级别的锁有两种：

1. 表锁。语法是 lock tables … read/write。

   与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

   而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

2. 元数据锁（meta data lock，MDL）。不需要显式使用，在访问一个表的时候它会被自动加上。

   在 MySQL 5.5 版本中引入了 MDL，主要用于保护数据库的元数据（如表的结构信息）

   • 当对一个表做增删改查操作的时候，加 MDL 读锁；

     由于这些操作并不改变表的结构，只会操作数据，所以它们之间可以并行执行，不会互相阻塞。

     如果有两个线程同时执行 SELECT 查询操作，它们都可以获得读锁，彼此不会阻塞。

     如果有两个线程同时执行 INSERT 或 UPDATE 操作，它们也可以获得读锁，但会阻塞任何写锁的操作。

   • 当要对表做结构变更操作的时候，加 MDL 写锁。

     写锁会阻止任何其他线程对该表进行数据操作（如增、删、改、查），也会阻止其他线程对表进行结构变更，直到当前线程完成表结构的修改。

     如果两个线程同时执行 ALTER TABLE 操作（例如都要向表中添加字段），它们会互相阻塞，直到其中一个完成后，另一个才能开始执行。

     如果一个线程执行 ALTER TABLE，而另一个线程在执行数据操作（如 SELECT），后者会被阻塞，直到表的结构修改操作完成。

虽然 MDL 锁是系统默认会加的，但它却是一个不能忽略的机制。

我们知道给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候肯定会特别小心，以免对线上服务造成影响。而实际上即使是小表，操作不慎也会出问题。

小表的阻塞

如果此时的一个小表中，有着四个事务：A→B→C→D。

事务ACD均为select，而事务C为alter

1. session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。

   读锁之间不互斥

2. session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞

   读写锁之间、写锁之间是互斥的

3. 如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。

   而所有对表的增删改查操作，都需要先申请 MDL 读锁，等于这个表现在完全不可读写了。

   如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

   MDL 会直到事务提交才释放，在做表结构变更的时候，你一定要小心不要导致锁住线上查询和更新。

如何安全地给小表加字段

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的information_schema库的innodb_trx表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

但如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而我们不得不加个字段，该怎么做呢？

这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

所以表锁一般是在数据库引擎不支持行锁的时候才会被用到的。

如果你发现你的应用程序里有 lock tables 这样的语句，你需要追查一下，比较可能的情况是：

• 要么是你的系统现在还在用 MyISAM 这类不支持事务的引擎，那要安排升级换引擎；
• 要么是你的引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把 lock tables 和 unlock tables 改成 begin 和 commit，问题就解决了。

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的，并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。

主题：如何通过减少锁冲突来提升业务并发度

InnoDB 的行锁

行锁就是针对数据表中行记录的锁。

两阶段锁（协议）：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束（commit）时才释放，例如在A事务commit之前，其余的事务均会被阻塞。

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

死锁和死锁检测

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

例如：事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。

事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。

当出现死锁以后，有两种策略：

1. 直接进入等待直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。

   在 InnoDB 中，innodb_lock_wait_timeout的默认值是50s

   对于在线服务来说，这个等待时间往往是无法接受的

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

   将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

但是死锁检测是有负担的。比如每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。死锁检测要耗费大量的 CPU 资源

那怎么解决由这种热点行更新导致的性能问题呢？

1. 头痛医头：临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

2. 控制并发度：例如控制同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。

   并发控制要做在数据库服务端，基本思路就是，对于相同行的更新，在进入引擎之前排队。

   • 如果有中间件，可以考虑在中间件实现；
   • 如果有能修改 MySQL 源码的人，也可以做在 MySQL 里面。

小思考

1. 当备库用–single-transaction做逻辑备份的时候，如果从主库的 binlog 传来一个 DDL 语句会怎么样？

   由于先用–single-transaction做备份，所以备份线程会启动一个事务获取MDL读锁，文中也说了“MDL 会直到事务提交才释放”，所以要一直等到备份完成主库来的DDL才会在从库执行生效，且备份的数据里并不会有新增的这个列。

   由于主库来的DDL会等待MDL写锁，所以会导致之后从库上的读写请求都阻塞。

2. 如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：

   • 第一种，直接执行 delete from T limit 10000;

     单个语句占用时间长，需要锁资源多，持有锁时间最长；而且大事务还会导致主从延迟。

   • 第二种，在一个连接中循环执行 20 次 delete from T limit 500;

     串行化执行，将相对长的事务分成多次相对短的事务，则每次事务占用锁的时间相对较短，其他客户端在等待相应资源的时间也较短。这样的操作，同时也意味着将资源分片使用（每次执行使用不同片段的资源），可以提高并发性。

   • 第三种，在 20 个连接中同时执行 delete from T limit 500。

     多个事务会对同一行产生锁竞争，消耗cpu资源

---

性能优化

索引优化

索引模型

索引模型中的数据结构

• 哈希表、适用于只有等值查询的场景

  • 描述：哈希表是一种以键 - 值（key-value）存储数据的结构，通过哈希函数将键映射到数组索引，以实现key对应value的快速查找。
  • 哈希冲突：由于哈希函数可能将多个键映射到同一个数组索引，这种现象称为冲突。
  • 冲突解决：在MySQL的InnoDB存储引擎中，哈希索引通常是基于链地址法实现的。哈希冲突还可以使用开放寻址法、再散列和哈希表扩容

• 有序数组、适用于等值查询和范围查询场景

  • 描述：有序数组通过保持元素的顺序，支持二分查找，实现O(log(N))的查询复杂度，适合快速查找和范围查询。
  • 优缺点：有序数组在内存中连续存储，便于快速访问，但插入和删除操作的时间复杂度为O(N)，因为可能需要移动多个元素。

• 搜索树

  • 描述：搜索树（如二叉搜索树）通过节点的左小右大的特性，支持高效的查找、插入和删除操作。搜索树在平衡状态下，查询复杂度为O(log(N))。
  • 平衡性：为了保持 O(log(N)) 的性能，需使用自平衡搜索树（如 AVL 树、红黑树），确保树的高度保持在对数级别。
  • 优缺点：搜索树可以动态调整，支持高效的范围查询，但在不平衡的情况下，性能可能下降至O(N)。

索引类型

根据叶子节点的内容，索引类型分为主键索引和非主键索引：

1. 主键索引的叶子节点存的是整行数据。

   使用主键可以快速获取到整行的数据

   在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

2. 非主键索引的叶子节点内容是主键的值。

   由于存储的是主键的值，所以主键字段占用空间不宜过大。

   同时，其查找数据的过程称为“回表”，需要先查找自己得到主键值，再在主键索引上边查找数据内容。

   在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

回表：是指在使用二级索引查找时，找到的行号需要再去主键索引中查找完整的行数据。这会增加额外的 I/O 操作，影响性能。

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

• 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
• 如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

有没有可能经过索引优化，避免回表过程呢？

覆盖索引

如果只对二级索引的索引字段（如ID）进行select，而 ID 的值已经在该二级索引树上了，因此可以直接提供查询结果，不需要回表。

这个查询里面，对应索引查询已经“覆盖了”我们的查询需求，我们称为覆盖索引。

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

索引维护

InnoDB引擎使用了 B+ 树索引模型，所以其数据都是以 B+ 树的数据结构存储的。

假设我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。如下图

每一个索引在 InnoDB 里面对应一棵 B+ 树。而 B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。

在目前两个索引结构中，如果插入新值的主键：

1. 如果在中间插入一个新索引记录，还需要逻辑上挪动后面的数据，空出位置

2. 如果该索引结构中间的数据页满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。

   除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。

那么对于该场景：

• 自增主键：是指自增列上定义的主键，它的插入数据模式，正符合了我们前面提到的递增插入的场景。

  即：每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂

• 而在有业务逻辑的字段（例如身份证号、图书SNS号）做主键，则往往不容易保证有序插入，这样的有序插入数据成本相对较高。

  除了考虑性能外，我们还可以从存储空间的角度来看。例如用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节。

在很多情况下，最佳实践是将身份证号作为唯一索引，而使用自增字段作为主键。

它的业务的场景需求是这样的：

1. 只有一个索引；
2. 该索引必须是唯一索引。

这就是典型的 KV 场景，由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。

这样可以利用唯一索引的优势，简化查询和提高效率。

最左前缀原则

核心：当你在查询中使用联合索引时，查询条件必须从索引中最左边的字段开始，才能利用该索引来加速查询。换句话说，联合索引的字段顺序必须遵循查询条件中的顺序。

假设你有一个联合索引 (name, age)，这意味着数据库会首先根据 name 字段进行排序，若 name 相同，则按 age 排序。这个索引的字段顺序非常重要，查询时需要按照从左到右的顺序来使用这些字段才能利用索引。

为什么“最左前缀”有效：最左前缀原则背后的原因是，B+ 树索引的结构是有序的。B+ 树按照索引定义的顺序（从左到右）来存储数据。当你查询的条件从左到右依次符合索引的顺序时，数据库能够利用这个有序的结构高效地定位到符合条件的记录。而如果条件跳过了索引中的某些字段（如查询条件只涉及了 age，而没有涉及 name），数据库就无法利用索引的有序性来加速查询。

总结：

• 最左前缀原则意味着：查询条件必须从索引定义中最左边的字段开始才能利用索引。
• 只要查询条件涉及索引中的最左部分（一个或多个字段），数据库就可以高效地利用该索引加速查询。
• 如果查询跳过了索引的最左部分，数据库就无法使用索引，可能需要扫描整个表。

了解了基本概念后，在建立联合索引的时候，如何安排索引内的字段顺序？

评估标准是索引的复用能力。

第一原则为，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

• 当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。

• 但如果既有联合查询，又有基于 a、b 各自的查询情况下，查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的。

  这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。

  此时就建议我们创建一个 (a,b) 的联合索引和一个 (b) 的单字段索引

索引下推

在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。

MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

小思考

1. 在 MySQL 中，如果你创建了多个二级索引，但实际查询时只使用主键索引进行检索，二级索引会对性能产生影响吗？

   1. 查询性能：
      • 如果查询只使用主键索引，二级索引不会直接影响查询的速度。这是因为 MySQL 在执行查询时只会考虑使用的索引。
      • 但是，系统在执行查询时仍需维护所有索引，包括二级索引，这可能会导致性能开销，特别是在插入、更新或删除数据时，因为这些操作需要更新所有相关的索引。
   2. 存储利用率：
      • 每个创建的索引都占用额外的存储空间。即使不使用这些二级索引，它们仍然会消耗磁盘空间，导致整体存储利用率降低。
      • 如果二级索引数量过多，可能会增加数据库的管理和维护负担。
   3. 维护开销：
      • 每当进行数据修改（插入、更新、删除）时，所有相关索引都需要进行更新，这会增加系统的负担。即使查询时不使用二级索引，索引的存在依然会影响写入性能。

2. 二级索引都会有回表问题，那么为什么还要创建二级索引？

   如果查询条件使用的是普通索引（或者是联合索引的最左原则字段）

   查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取整行数据

   “数据量很大的时候，二级索引比主键索引更快”

   这个结论是只有在使用覆盖索引时才成立，非覆盖索引还是要回表查询

3. select ＊from table where id＝xx和select id from table where id＝xx的效率是一样的吗？（id是主键）

   在id为主键进行查询的情况下，不会回表。而select *要读和拷贝更多列到server，还要发送更多列给客户端，所以还是select id更快。

查询优化

服务器配置优化

该部分引用自：mysql8.0配置文件优化

目的：根据服务器状况，修改MySQL系统参数，达到合理利用服务器现有资源，最大合理的提高MySQL性能。

配置文件路径：/etc/my.cnf

核心参数含义：

# 注：缓冲池位于主内存中，InnoDB用它来缓存被访问过的表和索引文件，使常用数据可以直接在内存中被处理，从而提升处理速度；
innodb_buffer_pool
# 注：MySQL5.6.6之后可以调整为多个。表示InnoDB缓冲区可以被划分为多个区域，也可以理解为把innodb_buffer_pool划分为多个实例，可以提高并发性，避免在高并发环境下，出现内存的争用问题；
innodb_buffer_pool_instance
# 注：该参数可以指定系统表空间文件的路径和ibdata1文件的大小。默认大小是10MB，这里建议调整为1GB
innodb_data_file_path
# 注：MySQL数据库的事务隔离级别有四种，分别为READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ和SERIALIZABLE。默认采用REPEATABLE-READ（可重复读）
transaction_isolation
# 注：是日志缓冲的大小，InnoDB改变数据的时候，它会把这次改动的记录先写到日志缓冲中
innodb_log_buffer_size
# 注：是指Redo log日志的大小，该值设置不宜过大也不宜过小，如果设置太大，实例恢复的时候需要较长时间，如果设置太小，会造成redo log 切换频繁，产生无用的I/O消耗，影响数据库性能
innodb_log_file_size
# 注：redo log文件组中日志文件的数量，默认情况下至少有2个
innodb_log_files_in_group
# 该参数代表MySQL数据库的最大连接数
max_connections
# 注：该参数代表binlog的过期时间，单位是天
expire_logs_days
# 注：慢查询日志的开关，该参数等于1代表开启慢查询
slow_query_log
# 注：慢查询的时间，某条SQL语句超过该参数设置的时间，就会记录到慢查询日志中。单位是秒
long_query_time
# 注：该参数代表二进制日志的格式。binlog格式有三种statement、row和mixed。生产环境中使用row这种格式更安全，不会出现跨库复制丢数据的情况
binlog_format
# 注：表名是否区分大小的参数。默认是值为0。0代表区分大小写，1代表不区分大小写，以小写存储
lower_case_table_names
# 注：是服务器关闭交互式连接前等待活动的时间,默认是28800s（8小时）
interactive_timeout
# 注：是服务器关闭非交互式连接之前等待活动的时间，默认是28800s（8小时）
wait_timeout
# 注：这个参数影响InnoDB数据文件，redo log文件的打开刷写模式
innodb_flush_method
# 注：如果运行的SQL语句没有使用索引，则MySQL数据库同样会将这条SQL语句记录到慢查询日志文件中
log_queries_not_using_indexes

实际参数配置及其解释：

[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1 #表示是本机的序号为1,一般来讲就是master的意思
skip-name-resolve
# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，
# 则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求
#skip-networking
back_log = 600
# MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，
# 然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。
# 如果期望在一个短时间内有很多连接，你需要增加它。也就是说，如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，
# 以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
# 另外，这值（back_log）限于您的操作系统对到来的TCP/IP连接的侦听队列的大小。
# 你的操作系统在这个队列大小上有它自己的限制（可以检查你的OS文档找出这个变量的最大值），试图设定back_log高于你的操作系统的限制将是无效的。
max_connections = 1000
# MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。
max_connect_errors = 6000
# 对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。
open_files_limit = 65535
# MySQL打开的文件描述符限制，默认最小1024;当open_files_limit没有被配置的时候，比较max_connections*5和ulimit -n的值，哪个大用哪个，
# 当open_file_limit被配置的时候，比较open_files_limit和max_connections*5的值，哪个大用哪个。
table_open_cache = 128
# MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值64
# 假定系统有200个并发连接，则需将此参数设置为200*N(N为每个连接所需的文件描述符数目)；
# 当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上
max_allowed_packet = 4M
# 接受的数据包大小；增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当你发出长查询或MySQLd必须返回大的结果行时MySQLd才会分配更多内存。
# 该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。
binlog_cache_size = 1M
# 一个事务，在没有提交的时候，产生的日志，记录到Cache中；等到事务提交需要提交的时候，则把日志持久化到磁盘。默认binlog_cache_size大小32K
max_heap_table_size = 8M
# 定义了用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变
tmp_table_size = 16M
# MySQL的heap（堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。
# 大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。
# 如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果
read_buffer_size = 2M
# MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。
# 如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能
read_rnd_buffer_size = 8M
# MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，
# MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大
sort_buffer_size = 8M
# MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。
# 如果不能，可以尝试增加sort_buffer_size变量的大小
join_buffer_size = 8M
# 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享
thread_cache_size = 8
# 这个值（默认8）表示可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，
# 如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，
# 增加这个值可以改善系统性能.通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。(–>表示要调整的值)
# 根据物理内存设置规则如下：
# 1G  —> 8
# 2G  —> 16
# 3G  —> 32
# 大于3G  —> 64
query_cache_size = 8M
#MySQL的查询缓冲大小（从4.0.1开始，MySQL提供了查询缓冲机制）使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，
# 今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。
# 通过检查状态值'Qcache_%'，可以知道query_cache_size设置是否合理：如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，
# 如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，
# 这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲
query_cache_limit = 2M
#指定单个查询能够使用的缓冲区大小，默认1M
key_buffer_size = 4M
#指定用于索引的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，
# 系统将开始换页并且真的变慢了。对于内存在4GB左右的服务器该参数可设置为384M或512M。通过检查状态值Key_read_requests和Key_reads，
# 可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低，
# 至少是1:100，1:1000更好(上述状态值可以使用SHOW STATUS LIKE 'key_read%'获得)。注意：该参数值设置的过大反而会是服务器整体效率降低
ft_min_word_len = 4
# 分词词汇最小长度，默认4
transaction_isolation = REPEATABLE-READ
# MySQL支持4种事务隔离级别，他们分别是：
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
# 如没有指定，MySQL默认采用的是REPEATABLE-READ，ORACLE默认的是READ-COMMITTED
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30 #超过30天的binlog删除
log_error = /data/mysql/mysql-error.log #错误日志路径
slow_query_log = 1
long_query_time = 1 #慢查询时间 超过1秒则为慢查询
slow_query_log_file = /data/mysql/mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1 #不区分大小写
skip-external-locking #MySQL选项以避免外部锁定。该选项默认开启
default-storage-engine = InnoDB #默认存储引擎
innodb_file_per_table = 1
# InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间
# 独立表空间优点：
# 1．每个表都有自已独立的表空间。
# 2．每个表的数据和索引都会存在自已的表空间中。
# 3．可以实现单表在不同的数据库中移动。
# 4．空间可以回收（除drop table操作处，表空不能自已回收）
# 缺点：
# 单表增加过大，如超过100G
# 结论：
# 共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整：innodb_open_files
innodb_open_files = 500
# 限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300
innodb_buffer_pool_size = 64M
# InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.
# 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.
# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%
# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.
# 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,
# 所以不要设置的太高.
innodb_write_io_threads = 4
innodb_read_io_threads = 4
# innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4
# 注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里，修改完后重启MySQL服务,允许值的范围从 1-64
innodb_thread_concurrency = 0
# 默认设置为 0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量
innodb_purge_threads = 1
# InnoDB中的清除操作是一类定期回收无用数据的操作。在之前的几个版本中，清除操作是主线程的一部分，这意味着运行时它可能会堵塞其它的数据库操作。
# 从MySQL5.5.X版本开始，该操作运行于独立的线程中,并支持更多的并发数。用户可通过设置innodb_purge_threads配置参数来选择清除操作是否使用单
# 独线程,默认情况下参数设置为0(不使用单独线程),设置为 1 时表示使用单独的清除线程。建议为1
innodb_flush_log_at_trx_commit = 2
# 0：如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作（执行是由mysql的master thread线程来执行的。
# 主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交）默认的日志文件是ib_logfile0,ib_logfile1
# 1：当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。
# 2：如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。
# 每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有 缓存的，所以这个写入并不能保证数据已经写入到物理磁盘
# 默认值1是为了保证完整的ACID。当然，你可以将这个配置项设为1以外的值来换取更高的性能，但是在系统崩溃的时候，你将会丢失1秒的数据。
# 设为0的话，mysqld进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。
# 总结
# 设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2，如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能
innodb_log_buffer_size = 2M
# 此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据。MySQL开发人员建议设置为1－8M之间
innodb_log_file_size = 32M
# 此参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间
innodb_log_files_in_group = 3
# 为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
innodb_max_dirty_pages_pct = 90
# innodb主线程刷新缓存池中的数据，使脏数据比例小于90%
innodb_lock_wait_timeout = 120 
# InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒
bulk_insert_buffer_size = 8M
# 批量插入缓存大小， 这个参数是针对MyISAM存储引擎来说的。适用于在一次性插入100-1000+条记录时， 提高效率。默认值是8M。可以针对数据量的大小，翻倍增加。
myisam_sort_buffer_size = 8M
# MyISAM设置恢复表之时使用的缓冲区的尺寸，当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区
myisam_max_sort_file_size = 10G
# 如果临时文件会变得超过索引，不要使用快速排序索引方法来创建一个索引。注释：这个参数以字节的形式给出
myisam_repair_threads = 1
# 如果该值大于1，在Repair by sorting过程中并行创建MyISAM表索引(每个索引在自己的线程内) 
interactive_timeout = 28800
# 服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。默认值：28800秒（8小时）
wait_timeout = 28800
# 服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，
# 取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。参数默认值：28800秒（8小时）
# MySQL服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到MySQL Server处理完相应的操作后，
# 应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，
# 最终肯定会达到MySQL Server的连接上限数，这会报'too many connections'的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。
# 在系统运行一段时间后，可以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，
# 可以进行适当的调整小些。要同时设置interactive_timeout和wait_timeout才会生效。
[mysqldump]
quick
max_allowed_packet = 16M #服务器发送和接受的最大包长度
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

---

MySQL架构

主备&主从架构

我们可以从本章了解到：主备同步的内容是什么样的？为什么备库执行了 binlog 就可以跟主库保持一致？

MySQL 能够成为现下最流行的开源数据库，binlog 功不可没

它的几乎所有的高可用架构，都直接依赖于 binlog

主备架构

主备：客户端的读写都直接访问主库，主库存在一或多个备库。备库只是将主库的更新都同步到本地执行。

下面为切换，主备身份互换：

两个不同状态的备库都需要设置为readonly，因为：

1. 运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
2. 防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；
3. 可以用 readonly 状态，来判断节点的角色。

Q：那备库设置成只读了，怎么跟主库保持同步更新？

A：readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限。

主备复制流程

主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写 binlog。

备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。

同步的完整过程：

1. 在备库 B 上通过change master命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。

2. 在备库 B 上执行start slave命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。

   其中 io_thread 负责与主库建立连接。

3. 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。

4. 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。

5. sql_thread 读取中转日志，解析出日志里的命令，并执行。

后来由于多线程复制方案的引入，sql_thread 演化成为了多个线程

总的来说，MySQL主备复制的基本原理，就是通过主库生成bin log，由备库通过I/O和SQL线程将这些日志读入relay log并执行，从而实现数据的实时同步。

Q：binlog 里面到底是什么内容，为什么备库拿过去可以直接执行？

A：binlog 中不仅存储了数据变更的信息，还包含了执行这些变更所需的详细指令。备库通过读取主库的 binlog，获取这些变更后，能够以相同的顺序在自身上重放这些操作，确保数据的一致性和同步性。

更深入的讲，binlog有两种格式，他们格式的不同可能也会导致主备不一致的情况，详细见Q&A章节

主备切换

以下图开展主备切换的流程，以及互为主备的循环复制问题。图中虚线箭头表示的是主备关系，也就是 A 和 A’互为主备， 从库 B、C、D 指向的是主库 A。

循环复制：互为的主备A、A’两库，假如A更新并且A’同样更新生成了bin log，使得A也从io线程获取到了新的更新记录，随即A和A’之间不断地循环执行这个更新语句。

不过 MySQL 在 binlog 中记录了这个命令第一次执行时所在实例的 server id，即一个备库接到 binlog 并在重放的过程中，会生成与原 binlog 的 server id 相同的新的 binlog。于是每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

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

所以对于双M结构（互为主备结构），日志的执行流就会变成这样：

1. 从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；
2. 传到节点 A’ 执行一次以后，节点 A’ 生成的 binlog 的 server id 也是 A 的 server id；
3. 再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

回到一主多从中，其中从库B、C、D也需要切换主库作为从库继续同步，但问题是执行change master命令时的MASTER_LOG_FILE和MASTER_LOG_POS两个参数，即主库对应的文件名和日志偏移量。

节点 B 原来是 A 的从库，本地记录的也是 A 的位点。但是相同的日志，A 的位点和 A’的位点是不同的。因此，从库 B 要切换主库的时候，就需要先经过“找同步位点”这个逻辑。

在MySQL 5.6 版本引入的 GTID（Global Transaction Identifier），即全局事务 ID可以解决这个问题。

它由两部分组成，格式是GTID=server_uuid:gno

• server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值；
• gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。

在 GTID 模式下，每个事务都会跟一个 GTID 一一对应，每个 MySQL 实例都维护了一个 GTID 集合，用来对应“这个实例执行过的所有事务”。

于是，基于 GTID 的主备切换就可以从原先的MASTER_LOG_FILE和MASTER_LOG_POS参数，变更为只传递master_auto_position，表示这个主备关系使用的是 GTID 协议。

todo

主从架构

主备与主从只有一字之差，但是还是有区别，一个是为备份做出的同步架构，一个是为读写比不均、读多写少的服务设计出的架构。

读写分离：即通过主备复制确保从库也有完整的业务数据副本，然后将访问数据的流量分摊到主库和从库，分摊请求压力，实现负载均衡。

大多数的互联网应用场景都是读多写少，因此业务在发展过程中 很可能先会遇到读性能的问题

读写分离一般有客户端直连架构，以及中间加一层proxy代理的架构，如图：

由 proxy 根据请求类型和上下文来决定路由的分发，引导流量至合适的节点。

该proxy中间件除了大厂自研，目前开源的也有很多，如：Mycat2、Amoeba、proxysql、Atlas、Maxscale

不管使用那种架构，主从都会存在延迟。当客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到事务更新之前的状态，这样会导致过期读。

要尽量避免过期读，可以采用：

• 强制走主库方案；

  该架构针对请求进行分类，对于必须要拿到最新结果的请求，强制将其发到主库上，而其他路由到从库。

  不过当遇到“所有查询都不能是过期读”的需求，就只能放弃读写分离，所有读写压力都在主库，等同于放弃了扩展性。这种需求在金融类的业务中比较常见

• sleep 方案；

  主库更新后，读从库之前先 sleep 一下。具体的方案就是，类似于执行一条 select sleep(1) 命令。是直接在应用上避免。

• 判断主备无延迟方案；

  在执行查询请求之前，先判断从库是否同步完成

• 配合 semi-sync 半同步复制方案；

• 等主库位点方案；

• 等 GTID 方案。

• 写入后 从应用逻辑上先不触发读的京东下单方案

  例如在用户下单场景，在写入订单后，先把用户跳转到你成功下单再放个抽奖页面，点击查看详情后再触发对应订单的读请求，这样写读间隔大部分也能避免过期读

该部分可以参阅B站视频：数据库读写分离会碰到那些坑？，还是动画比较生动。

Q&A

为什么会出现不一致？

情况1-备库执行差异导致：在同步了bin log到relay log后，具体执行逻辑由bin log格式的不同而产生的效果也不同。

binlog 有两种格式，一种是 statement，一种是 row。

• statement保留完整SQL语句
• row保留数据前后状态

还有一个mixed，其实它就是前两种格式的混合。

以SQL例子介绍：

delete from t /*comment*/  where a>=4 and t_modified<='2018-11-10' limit 1;

假如bin log是 statement 格式，同步后，此时由于 statement 格式下，记录到 binlog 里的是语句原文，于是在主库执行 SQL 语句的时候，用的是索引 a；而在备库执行 SQL 语句的时候，却使用了索引 t_modified。导致主备、主从之间删除的数据都不相同，这就会导致不一致。

这是由于主备库的索引选择不一致导致的，主要归因于两个因素：优化器的决策和数据的不一致性。查询优化器会根据当前的数据状态来选择合适的索引。当主库和备库的数据存在差异时，优化器可能会得出不同的执行计划。所以 delete 带 limit，很可能会出现主备数据不一致的情况。

不过以 row 格式的时候，binlog 里面会记录真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除 id=4 的行，不会有主备删除不同行的问题。

集群与高可用

集群的概念与类型

高可用性解决方案

故障转移与负载均衡

---

权限与安全

用户账户管理

权限控制

数据备份与恢复