MySQL高阶优化思维导图模板_ProcessOn思维导图、流程图

Lock

相关知识点

Current Read

SQL 语句

select .. for update

select ... lock in share mode

insert

update

delete

实现方式

Next-Key 临键锁

Gap间隙锁

行记录锁

Snapshot Read

SQL语句

基本的 select ... 语句

快照生成

Read Commited

Read Repeatable

实现方式

行数据隐藏列

示意图

锁范围分类

全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都select出来存成文本

表锁

加表锁

读锁

允许多个会话同时持有读锁

持有读锁的会话可以读表，但不能写表

其他会话就算没有给表加读锁，也是可以读表的，但是不能写表

其他会话申请该表写锁时会阻塞，直到锁释放。

写锁

持有写锁的会话既可以读表，也可以写表

只有持有写锁的会话才可以访问该表，其他会话访问该表会被阻塞，直到锁释放

其他会话无论申请该表的读锁或写锁，都会阻塞，直到锁释放

释放表锁

使用 UNLOCK TABLES 语句可以显示释放表锁

如果会话在持有表锁的情况下执行 LOCK TABLES 语句，将会释放该会话之前持有的锁

如果会话在持有表锁的情况下执行 START TRANSACTION 或 BEGIN 开启一个事务，将会释放该会话之前持有的锁

如果会话连接断开，将会释放该会话所有的锁

示例

行锁

读锁（S锁，共享锁）

允许事务读一行数据。在select语句后面加上lock in share mode可以显式获取共享锁

写锁（X 锁，排他锁）

常见的 INSERT、UPDATE、DELETE 会自动对操作的数据行加写锁

select语句需要在语句后加上for update显式加排他锁

算法实现

Record Lock（记录锁）

记录锁是在行上设置的锁，用于保证在事务中不会有其他事务对同一行进行修改。
在事务中对某一行进行修改时，会对该行加上记录锁，其他事务需要对该行进行修改时，必须等待该记录锁被释放。

事务 A：

BEGIN;
SELECT * FROM user WHERE id=1 FOR UPDATE;
-- do some updates
COMMIT;

事务 B：

BEGIN;
SELECT * FROM user WHERE id=1 FOR UPDATE;
-- do some updates
COMMIT;

当事务 A 执行 SELECT * FROM user WHERE id=1 FOR UPDATE; 语句时，会将 id=1 的行加上行锁。因此，当事务 B 执行 SELECT * FROM user WHERE id=1 FOR UPDATE; 时，会被阻塞，直到事务 A 释放行锁

Gap Lock（间隙锁）

Gap Lock是在索引记录之间设置的锁，用于防止其他事务在这些索引记录之间插入新的索引记录。
在事务中对索引进行修改时，会对索引记录之间的间隙加上间隙锁，其他事务需要在这些间隙之间插入新的索引记录时，必须等待间隙锁被释放。

事务 A：

BEGIN;
INSERT INTO user (id, name, age) VALUES (5, 'Tom', 25);
COMMIT;

事务 B：

BEGIN;
SELECT * FROM user WHERE id > 4 AND id < 6 FOR UPDATE;
-- do some updates
COMMIT;
当事务 A 执行 INSERT INTO user (id, name, age) VALUES (5, 'Tom', 25); 语句时，会加上 Gap Lock，锁定 id > 4 AND id < 6 这个范围。因此，当事务 B 执行 SELECT * FROM user WHERE id > 4 AND id < 6 FOR UPDATE; 时，会被阻塞，直到事务 A 释放 Gap Lock。

Next-Key Lock（下一键锁）

Next-Key Lock是Record Lock和Gap Lock的结合体，同时锁住了索引记录和索引记录之间的间隙。在事务中对索引进行修改时，会对索引记录及其间隙加上下一键锁，其他事务需要对这些索引记录及其间隙进行修改时，必须等待下一键锁被释放。

意向锁

LOCK_IS

IS(读意向锁)

LOCK_IX

IX(写意向锁)

悲观锁与乐观锁

乐观锁

实现方式

版本号

CAS

1. 读取内存位置 V

2. 进行比较原预期值 A

3. 拟写入新值 B

ABA问题

解决方式

自旋问题

只能保证一个共享变量的原子操作

悲观锁

实现方式

Java 的 synchronized 和 ReentrantLock

MySQL 的锁

select ...for update

死锁

案例

操作不同表的相同记录

事务A持有表table1的id=2记录行锁，等待表table2的id=1的记录行锁，
事务B持有表table2的id=1记录行锁，等待表table1的id=2记录行锁，两者互相等待，出现死锁

操作同一张表的相同记录

事务在批量更新的时候，如果一个事务更新的顺序是[1,2]，
另一个事务更新的顺序是[2,1]，就可能出现死锁

不同索引造成锁冲突

事务A在执行时，除了在二级索引加锁外，还会在主键索引上加锁，在主键索引上加锁的顺序是[2,5]，
事务B执行时，只在主键索引上加锁，加锁顺序是[2]。[2]存在环路，有发生死锁的可能。

间隙锁冲突

事务A和事务B都持有gap锁，插入数据时都要等待对方的gap锁释放，发生死锁。

如何避免

以固定的顺序访问表和行，避免循环等待

大事务更容易发生死锁，如果业务允许，将大事务拆小。

在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

降低隔离级别。如果业务允许，将隔离级别从RR调整为RC，可以避免掉很多因为间隙锁造成的死锁。

为表添加合理的索引。如果不走索引将会为表的所有行都加锁，增大了死锁的概率。

事务

事务4大特性ACID

Atomic 原子性

Undo log: 当一个事务需要修改一行数据时，InnoDB 首先将该行数据的原始值拷贝到 undo log 中，然后执行修改操作。如果事务需要回滚，可以使用 undo log 中的原始值将数据恢复到修改前的状态。如果事务提交，则可以将 undo log 中的信息删除。

undo log

回滚日志文件，主要用于事务中执行失败，进行回滚，以及MVCC中对于数据历史版本的查看。
由引擎层的InnoDB引擎实现,是逻辑日志,记录数据修改被修改前的值 ex: "把id='B' 修改为id = 'B2' ，undo日志就会用来存放id ='B'的记录”
如果这个修改出现异常,,则会使用undo日志来实现回滚操作,保证事务的一致性。
当事务提交之后，undo log并不能立马被删除,而是会被放到待清理链表中,待判断没有事物用到该版本的信息时才可以清理相应undolog。

Consistency 一致性

事务开始前和结束后，数据库的完整性约束没有被破坏。

Isolution 隔离性

事务隔离级别

Read Uncommitted 读未提交

Read Committed 读已提交

Repeatable Read 可重复读

Serialized 串行化

使用事务隔离级别带来的问题

性能

数据

脏读 Dirty Read

如何解决脏读

在事务A读取某个数据之前，可以为该数据加上共享锁，表示其他事务可以读取但不能修改。

使用事务隔离级别中的“读提交”（Read Committed）隔离级别

多版本并发控制（MVCC）来解决脏读问题

Unrepeatable Read

如何解决不可重复读

在事务A读取某个数据之前，为该数据加上共享锁或排他锁，以阻止其他事务对该数据进行修改

"可重复读"隔离级别

乐观并发控制：通常使用版本号或时间戳等机制，MVCC

Phantom Read

幻读

一个事务按照相同的查询条件两次查询，但是得到的结果集却不同。
因为其他事务对该表进行了新增或删除操作，导致当前事务查询到的结果集不一致

如何解决幻读

在事务A执行范围查询之前，为该范围的数据加上间隙锁或共享锁，以防止其他事务在此期间插入符合条件的新数据。这样可以确保事务A在查询期间范围内的数据不会发生变化。

"串行化"隔离级别下

Lost Update

提交覆盖(一类丢失更新)

回滚覆盖(二类丢失更新)

隔离级别关系

分支主题

一致性非锁定读

实现

锁

MVCC

undo log + 回滚指针 = 版本链

readview

作用：select时在版本链中如何选择记录

m_ids = {1, 2, 3} min_trx_id =1. max_rex_id=4
IF trx_id == creator_trx_id 可以访问：该记录是事务本身
IF trx_id < min_trx_id 可以访问：该版本的事务已经提交
IF min_trx_id <= trx_id <= max_trx_id, 如果trx_id 属于 m_ids 则不能访问，反正则可以

子主题

RC: 读已提交：一个读事务中每次select都会生成一个readview
RR: 可重复读：一个读事务中只会生成一个readview
如何解决幻读？间隙锁：锁定范围内的数据

Durability（持久性）

Redo log：InnoDB 在执行事务时，会将事务的修改操作记录在 redo log 中，以保证事务的持久性。
redo log 记录了每个事务对数据所做的修改，包括修改的行、列和修改前后的值等信息。
当事务提交时，会将 redo log 写入到磁盘中，以保证数据的持久性

Redo log

是重做日志文件。记录数据修改之后的值，用于持久化到磁盘中。
redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。
由引擎层的InnoDB引擎实现,是物理日志,记录的是物理数据页修改的信息,比如“某个数据页上内容发生了哪些改动”。当一条数据需要更新时,InnoDB会先将数据更新，然后记录redoLog 在内存中，然后找个时间将redoLog的操作执行到磁盘上的文件上。
不管是否提交成功我都记录，你要是回滚了，那我连回滚的修改也记录。它确保了事务的持久性

隔离级别配置

查询当前会话 select @@tx_isolution;
查询全局 select @@global.tx_isolution;

设置会话隔离级别
set transaction isolation level << read uncommitted | read committed | repeatable read | serializable>;

性能优化

索引层优化

索引基础概念

类型

· INDEX（普通索引）：ALTER TABLE 'table_name' ADD INDEX index_name('col') 最基本的索引，没有任何限制
· UNIQUE（唯一索引）：ALTER TABLE 'table_name' ADD UNIQUE('col')：索引列的值必须唯一，但允许有空值。
· PRIMARY KEY（主键索引）：ALTER TABLE 'table_name' ADD PRIMARY KEY('col') 是一种特殊的唯一索引，不允许有空值。
· FULLTEXT（全文索引）：ALTER TABLE 'table_name' ADD FULLTEXT('col')，仅可用于MyISAM和InoDB，针对较大的数据，生成全文索引很耗时耗空间
· 组合索引：ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')

使用注意事项

1. 选择适当的列作为索引：选择频繁用于查询条件或连接条件的列作为索引可以提高查询性能。考虑到查询的选择性和频率，选择具有高基数（不同值数量多）的列作为索引更为有效。
2. 避免过多索引：过多的索引不仅占用存储空间，还会增加写操作的开销。
3. 注意索引列的顺序：多列索引的顺序很重要。根据查询的特点，将最频繁使用的列放在索引的前面，以提高查询的效率。
4. 索引覆盖：尽量设计覆盖索引，即索引包含了查询所需的所有列。这样可以减少磁盘IO操作，提高查询效率。
5. 避免过长的索引：较长的索引可能会导致较大的索引文件和较低的性能。选择适当长度的索引可以平衡存储和查询性能。
6. 定期维护索引：索引需要定期维护以保持其性能。删除不再使用的索引，重建或重新组织索引可以帮助提高查询性能。
7. 谨慎使用索引提示：某些数据库系统提供索引提示（index hints）的功能，允许手动指定使用特定的索引。但要谨慎使用，确保对性能有实际的提升，避免过度干预优化器的决策。
8. 注意索引和数据更新的平衡：索引可以提高查询性能，但会增加数据更新的开销。在设计索引时，要权衡查询和更新的需求，避免过度索引导致更新操作变慢。
9. 监测索引性能：定期监测索引的使用情况和性能，通过数据库的性能分析工具或查询执行计划来了解索引的效果，及时进行优化和调整。

索引数据结构

B+Tree

一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）
· 真实的数据存在于叶子节点
· 非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO。
在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针
通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO。
29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询。总计三次IO。真实的情况是，3层的b+树可以表示两千万的数据，如果千万的数据查找只需要三次IO，性能提高将是巨大的

.InnoDB一棵B+树可以存放约2千万行数据

在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512字节，而文件系统（例如XFS/EXT4）的最小单元是块，一个块的大小是4k。

而对于我们的InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。InnoDB的所有数据文件（后缀为ibd的文件），他的大小始终都是16384（16k）的整数倍。

假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。

一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。
一棵度为3的B+树可以存放：1170*1170*16=21902400条这样的记录。

选择索引的数据类型

1. 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和cpu缓存中都需要更少的空间，处理起来更快。
2. 简单的数据类型更好：整形数据比起字符，处理开销更小。在MySQL中，应用内置的日期和时间数据类型，而不是字符串来存储时间；以及用整形数据存储IP地址。
3. 尽量避免NULL：应该制定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为他们使得索引、索引的统计信息以及比较运算更加复杂。

聚簇索引与非聚簇索引

· 聚簇索引决定了数据的物理存储顺序。聚簇索引将数据行物理上按照索引键的顺序存储在磁盘上。一个表只能有一个聚簇索引。
· 聚簇索引的叶子节点包含了完整的数据行，可以满足覆盖索引的需求
· 聚簇索引对于范围查询和顺序遍历非常高效，因为相关的数据行物理上存储在相邻的位置，可以减少磁盘IO操作。

· 非聚簇索引的叶子节点不包含完整的数据行，而是包含索引键和指向数据行的物理地址（或指针）。
· 非聚簇索引的叶子节点按照索引键的顺序存储在磁盘上，但数据行的物理存储顺序与索引键的顺序无关。
· 非聚簇索引可以在一个表上有多个，每个非聚簇索引都维护着一份独立的索引结构。
· 非聚簇索引可以提供快速的索引查找，但在范围查询和顺序遍历时需要进行额外的磁盘IO操作。
区别总结：
聚簇索引决定了数据的物理存储顺序，非聚簇索引不决定数据的物理存储顺序。
聚簇索引的叶子节点包含完整的数据行，非聚簇索引的叶子节点包含索引键和指向数据行的指针。
聚簇索引对于范围查询和顺序遍历效果较好，非聚簇索引在单值查找时效果较好。
一个表只能有一个聚簇索引，但可以有多个非聚簇索引。
需要根据具体的业务需求和查询模式来选择使用聚簇索引还是非聚簇索引。聚簇索引适合范围查询和顺序遍历的场景，而非聚簇索引适合单值查找和覆盖索引的场景。

性能分析explain

id

id相同执行顺序从上到下

id不同，id值越大执行顺序优先级越高

同时存在，id值越大先执行，相同的再根据上到下顺序

select_type

table

partitions

type

访问类型，最好到最坏依次为： system > constant > eq_ref > ref（最好达到) > range (至少达到)> index > all

system 表中只有一行记录，等于系统表

const

mysql能对查询的某部分进行优化并将其转化成一个常量。用于primary key 或 unique key的所有列与常量比较时，所有表最多有一个匹配行，读取1次，速度比较快。

explain select * from (select * FROM village where CODE = 110101001001) tmp;

eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条数据与之匹配。常见primary key或unique key
索引的所有部分被连接使用，最多只会返回一条符合条件的记录。简单的select查询不会出现这种type。

explain SELECT * FROM street inner join village on village.streetCode=street.code

ref

不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

EXPLAIN SELECT * FROM `village` WHERE `name` = '银闸社区居委会'

range

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
范围扫描通常好于全表扫描，通常开始与索引某个点到结束于另一个点

EXPLAIN SELECT * FROM street WHERE cityCode BETWEEN 5108 and 5500

index

index扫描全表索引，只遍历索引树。这通常比ALL快一些。（index是从索引中读取的，而all是从硬盘中读取）

all

possible_keys

key

key_len

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

key_len只显示索引字段的最大可能长度，并非实际实际长度

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名

rows

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数

Musql查询优化器根据统计信息，估算SQL要查找到结果需要扫描读取的数据行数。

仍然可以从此字段判断索引的执行效率情况。

filtered

extra

using filesort

mysql 会对结果使用一个外部索引排序，而不是按表内的索引次序从表里读取行
无法利用索引的排序

using temporary

mysql需要创建一张临时表来处理查询结果排序。常见于group by, order by

Using index condition

表示没有达到索引覆盖，查询语句通过索引过滤出一部分记录，但是查询内容超出了索引范围，需要读取完整的数据行。

Using index

查询的列被索引覆盖，并且where筛选条件是索引的前导列(最左侧索引)，是性能高的表现。
一般是使用了覆盖索引(索引包含了所有查询的字段)。对于innodb来说，如果是辅助索引性能会有不少提高

Using where

使用了where过滤

Using join buffer

使用了链接缓存

索引失效

1. 未使用最佳左前缀原则，查询从最左开始，并不跳过中间的列。索引是(column1, column2)，但查询条件只包含WHERE column2 = value。

2. 使用（计算，函数，自动或手动类型转换）对列进行操作

3. 列类型不匹配，例如，索引列是整数类型，但查询条件中的列是字符串类型。

4. 数据库选择全表扫描的执行计划，在某些情况下，数据库优化器可能认为全表扫描比使用索引更有效。这可能发生在小表上或查询返回大部分数据行时。

5. 使用!=或者<>, 以及is null, is not null， OR等关键词

面试题案例

语法细节优化

SELECT

LIKE

JOIN ON

ORDER BY

WHERE

OR

COUNT

慢查询日志

配置方法

分析工具

MySQL 自带分析工具 mysqldumpslow

mysqlsla

Query Profiler

Performance Schema

MySQL 5.7 四个基本的库

服务器层优化

Buffer Pool

提高Buffer容量

手动内存预热

自动内存预热

降低磁盘IO

设置 Redo Log 大小

innodb_log_ﬁle_size 设置成 innodb_buﬀer_pool_size * 0.25

关闭部分与数据不相关的日志

更改 Force Log at Commit 机制

提高磁盘读写能力

增加磁盘

使用SSD

参数优化

内存参数

线程独享

sort_buffer_size

join_buffer_size

read_buffer_size

read_rnd_buffer_size

线程共享

innodb_buffer_pool_size

key_buffer_size

tmp_table_size

max_head_table_size

事务日志

innodb_log_file_size

innodb_log_files_in_group

innodb_log_buffer_size

innodb_flush_log_at_trx_commit

IO

innodb_flush_method = O_DIRECT

innodb_file_per_table = 1

innodb_doublewrite = 1

delay_key_write

innodb_read_io_threads

innodb_io_capacity

innodb_flush_neighbors

sync_binlog

其他参数

expire_logs_days

max_allowed_packet

skip_name_resolve

read_only

skip_slave_start

sql_mode

max_connections

高可用

分布式理论

Consistency

线性一致性

顺序一致性

ZooKeeper实现的一致性

弱一致性

最终一致性

因果一致性

读己所写一致性

会话一致性

单调读一致性

单调写一致性

Consenus

分布式协议

分布一致性

分布式事务

推荐框架

JTA

seata

RocketMQ

X/Open Specification(XA规范)

本地事务

全局事务

二阶段提交协议

图示

正常状态

失败状态

Fail-stop Failures模型

角色

Coordinator（协调者）

Participant（参与者，也叫 Voter 投票者）

阶段

1. Commit request phase

2. Commit phase

细节描述

1. 提交请求阶段

2. 提交阶段

提交事务

中断事务

Crash模型

协调者Crash，参与者正常

协调者正常，参与者Crash

参与者无法恢复

参与者可恢复

协调者Crash，参与者Crash

协调者与参与者在第一阶段Crash

协调者在第二阶段Crash

参与者在agree阶段Crash

参与者在commit阶段Crash

Fail-stop Failures模型

三阶段提交协议

图片示例

正常状态

异常状态

细节描述

CanCommit

PreCommit

所有参与者反馈agree

有参与者响应超时，或者反馈aborted

DoCommit

所有参与者反馈agree

有参与者超时，或者反馈aborted

Network Partition（网络分区情况）

TCC提交协议

Try

完成业务检查、预留业务资源

Confirm

使用预留的资源执行业务操作（需要保证幂等性）

Cancle

取消执行业务操作，释放预留的资源（需要保证幂等性）

Paxos算法

假设A、B、C三个节点

1. Prepare 阶段

2. Accept 阶段

ACID

CAP

Consistence（一致性）

Availability（可用性）

Partition Tolerance（分区容错性）

CAP 伪二分法

原因

重CA轻P

重CP轻A

重AP轻C

ACID与CAP一致性区别

ACID

内部一致性注重于事务前后数据的完整性

节点服务器的数据完整性

本质区别

CAP

外部一致性则注重于读写数据或主从数据的一致性

分布式多服务器之间复制数据以取得这些服务器拥有同样的数据，这是一种分布式领域的一致性概念

本质区别

可线性化

BASE

Basically Available（基本可用）

Soft State（软状态）

Eventually Consistent（最终一致性）

分库分表（Sharding）

分库分表

使用场景

三年内单表业务数据记录数超过500万，ibd文件超过2G

方案路线

垂直分片

按照业务维度将表拆分到不同数据库中，专库专用

水平分片

按分片键(id)，分片规则(id%10)将单表数据拆分到多张表中

数据分片策略

取模分片：数据存放分布均匀，扩容需要大量数据迁移

范围分片：存放不均匀，扩容不需要数据迁移

面试：根据业务场景，灵活制定分片策略

实现动态扩容，同时保证分布均匀

第三方插件

SharedingSphere

概念

分片策略

Inline

根据单一分片键进行精确分片

Standard

根据单一分片键进行精确或范围分片

Complex

根据多个分片键进行精确或范围分片

Hint

MyCat

节点复制

服务器间外部复制

主从模式

Asynchronous Replication

逻辑原理

技术实现

问题

Fully synchronous Replication

逻辑实现

技术实现

存在问题

Semisynchronous Replication

逻辑实现

技术实现

半同步退化机制

存在问题

Lossless Replication

逻辑实现

技术实现

存在问题

半同步复制与无损复制区别

半同步复制

无损复制

MGR模式

特性

数据一致性

事务并发冲突处理

节点故障自动检测

组成员自动管理

容错能力

基于Paxos多主更新

示例

分支主题

Certify

冲突解决方式

单主模式

多主模式

MGR使用

前置条件

仅支持 InnoDB 引擎

仅支持IPv4

必须打开GTID特性，二进制日志格式必须设置为ROW，用于选主与write set

每张表必须有主键，用于做write set冲突检测

限制条件

一个MGR集群最多支持9个节点

官方建议 READ COMMITTED 隔离级别

SELECT ... FOR UPDATE 可能造成死锁

不支持外键：多主不支持，单主模式不存在此问题

不支持外键于save point特性，无法做全局间的约束检测与部分部分回滚

二进制日志不支持 binlog event checksum

不支持 SERIALIZABLE 事务隔离级别

COMMIT可能会失败，类似于快照事务隔离级别的失败场景

DDL 无法作为事务处理，存在一些问题

不支持 Replication event checksums，需要在 my.cnf 里面配置

Single-Primary Mode

示图

Multi-Primary Mode

示图

服务器间内部复制

基于Binlog复制

statement-based replication

优势

binlog文件较小

主从数据库版本可以不一样

缺点

不定函数或者功能会让复制出问题

复制过程可能会有全表扫描

有AUTO_INCREMENT字段，会有阻塞

复杂语句如果执行出错会消耗更多资源

row-based replication

优点

对函数、存储过程、触发器等机制兼容

更小锁粒度

支持多线程复制

缺点

binlog日志量大

对大事务执行效率不高

UDF产生的大BLOB值影响复制效率

不能直接查看binlog日志

mixed-based replication

基于GTID复制

GTID

source_id

transaction_id

示例

工作原理

GTID在binlog中的结构

优点

更简单的实现 failover 做到主从切换自动化

更简单的搭建主从复制。

数据比传统的复制更加安全。

GTID 是连续的没有空洞的，保证数据的一致性，零丢失。

缺点

只支持 InnoDB 引擎

必须全 GTID 才可以复制

故障处理比较复杂，需要注入空事务

不支持 CREATE TABLE ... SELECT 语句，因为该语句会被拆成 CREATE TABLE 和 INSERT 两个事务，并且两个事务被分配同一个 GTID，导致 INSERT 操作直接 Skip

不支持CREATE TEMPORARY TABLE、DROP TEMPORARY TABLE 临时表操作

Errant transaction 问题：即从库不能进行任何事物型操作，会引入新的 GTID，当 binlog 被清除后，再进行主从切换，会导致其他从库找不到此 GTID，从而挂载不上

GTID配合SBR、RBR、MBR三者之一混合使用，保证数据一致性

Master配置

Slave配置

高可用选型

基于 MySQL 原生异步或半同步复制协议

MHA

架构

分支主题

MHA Manager

Master

Slave(M)

Slave(N)

工作流程

Failover过程

无法保证数据不会丢失

优点

可以进行故障的自动检测和转移

可扩展性较好，可以根据需要扩展MySQL的节点数量和结构

相比于双节点的 MySQL 复制，三节点/多节点的 MySQL 发生不可用的概率更低

支持基于日志点、GTID的复制方式

缺点

至少需要三节点，相对于双节点需要更多的资源

逻辑较为复杂，发生故障后排查问题，定位问题更加困难

数据一致性仍然靠原生半同步复制保证，仍然存在数据不一致的风险

可能因为网络分区发生脑裂现象

需要基于 SSH 免认证配置，存在一定的安全隐患

只监控 master，未监控 slave 状态

MMM

优点

缺点

由于架构里只有一个写入点，所以扩展性是有限的，但是对一般中型企业够用了。解决方案：对于大应用可以采取垂直拆分到多个 MMM 架构的方式，使用 MMM Cluster 来管理

对于读写分离和读负载均衡还是要程序来开发或者使用其他工具完成。

数据不能保证抢一致性，但是保证了高可用

可能因为网络分区发生脑裂现象

存在单点故障

不支持基于 GTID 复制

Zookeeper + Proxy

优点

较好保证了整个系统的高可用

扩展性好，可以扩展为大规模集群

缺点

依然依赖于原生的半同步复制

引入 zk，使系统变得更加复杂

基于分布一致性协议

MGR

MySQL Innodb Cluster(基于MGR方案)

优点

支持多主写入

无脑裂问题

自动故障转移，自动添加、剔除节点，不依赖任何第三方工具

缺点

同 MGR

MySQL NDB Cluster

优点

全部使用官方组件，不依赖于第三方

当 NDB Cluster 关闭时，NDB Cluster 数据节点在 Memory 中保存的数据将写入磁盘，并在 Cluster 启动的下一个 time 重新加载到 Memory。

同步复制，在 NDB Cluster 所有数据节点都保持同步，可实现数据强一致性

缺点

基于内存，数据库集群规模受内存大小限制

配置复杂，必须使用 NDB 存储引擎，与常规存储引擎存在差异，只支持 READ COMMITTED 隔离级别，没有 MVCC

联表 JOIN 性能很差

MariaDB Galera Cluster

功能

同步复制

真正的 multi-master，即所有节点可以同时读写数据库

自动的节点成员控制，失效节点自动被清除

新节点加入数据自动复制

真正的并行复制，基于行级

用户可以直接连接集群，使用感受上与 MySQL 完全一致

优点

因为是多主，所以不存在 Slavelag（延迟）

不存在丢失事务的情况

同时具有读和写的扩展能力

节点间数据是同步的,而 Master/Slave 模式是异步的，不同 Slave 上的 binlog 可能是不同的

缺点

需要打 wsrep 补丁

只支持 InnoDB 存储引擎

不同 Slave 的 binlog 可能不同

MySQL 架构

架构图

分层结构

网络连接层

Connectors

该层一般基于 C/S 架构组件，对外提供交互的组件

SQL 处理层

Management Service & Utilities

管理服务组件和工具组件，提供对MySQL 集成管理，备份、恢复、安全管理等

SQL Interface

SQL 接口组件，接收用户 SQL 命令，如 DML，DDL 和存储过程等，并将最终结果返回给用户

Parser

查询分析器组件，首先分析 SQL 命令语法的合法性（是否符合 SQL 92 标准），并尝试将 SQL 命令分解成数据结构，若分解失败，则提示 SQL 语句不合理

Limit 属于 MySQL 自己的语法，不属于 SQL 92 标准

Optimizer

优化器组件，对 SQL 命令按照标准流程进行优化分析

针对索引，选择最优索引。

多表关联，将数据较小的表放在左边（嵌套循环，外层循环小提高性能）。

通过执行计划 Explain 执行 Where 从左到右寻找过滤力度最大的先执行（如有主键，先找主键）。

Caches & Buffers

缓存和缓冲组件

Map 结构： Key 存储 SQL 语句 hash 值，Value 存储 SQL 返回的结果。当该数据更新时，缓存层会删除。

MySQL 8.0 版本已经不再使用该功能

插件式存储引擎层

MyISAM

高速引擎，查询和插入顺序较高。5.6 版本支持了事务（与 InnoDB 事务不同），但不能同时与带有事务引擎使用（GTID 全局事务ID，可了解），但是仍然不支持行锁。

B+Tree 索引

InnoDB

5.5 版本以后默认引擎，支持事务处理、回滚、修复及 MVCC（多版本并发控制）和行锁及外键。

B+Tree 索引

Memory

内存存储引擎，无需磁盘 I/O。重启表结构会保留，数据会丢失。

Hash 索引

CSV

可以将 Excel 等彪哥数据存储为 CSV 文件。

不支持索引

NDB

集群存储引擎，类似 Oracle 的 RAC 集群，不同的是结构采用 share noting 集群架构。

NDB 数据全部放在内存中，但是 JOIN 查询速度慢。

只支持 READ COMMITTED 隔离级别

没有 MVCC