首页  思维导图  详情

MySQL高阶优化

2023-10-18 21:45:03   22  举报





AI智能生成

MySQL高阶优化

MySQL

作者其他创作

大纲/内容

select .. for update

select ... lock in share mode

insert

update

delete

SQL 语句

Next-Key 临键锁

Gap间隙锁

行记录锁

实现方式

Current Read

基本的 select ... 语句

SQL语句

Read Commited

Read Repeatable

快照生成

行数据隐藏列

示意图

Snapshot Read

相关知识点

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

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

全局锁

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

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

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

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

读锁

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

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

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

写锁

加表锁

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

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

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

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

释放表锁

示例

表锁

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

读锁（S锁，共享锁）

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

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

写锁（X 锁，排他锁）

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

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

Record Lock（记录锁）

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

Gap Lock（间隙锁）

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

Next-Key Lock（下一键锁）

算法实现

行锁

锁范围分类

IS(读意向锁)

LOCK_IS

IX(写意向锁)

LOCK_IX

意向锁

版本号

1. 读取内存位置 V

2. 进行比较原预期值 A

3. 拟写入新值 B

解决方式

ABA问题

自旋问题

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

CAS

乐观锁

Java 的 synchronized 和 ReentrantLock

select ...for update

MySQL 的锁

悲观锁

悲观锁与乐观锁

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

操作不同表的相同记录

操作同一张表的相同记录

不同索引造成锁冲突

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

间隙锁冲突

案例

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

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

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

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

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

如何避免

死锁

Lock

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

回滚日志文件，主要用于事务中执行失败，进行回滚，以及MVCC中对于数据历史版本的查看。span style=\"font-size: inherit;\

undo log

Atomic 原子性

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

Consistency 一致性

Read Uncommitted 读未提交

Read Committed 读已提交

Repeatable Read 可重复读

Serialized 串行化

事务隔离级别

性能

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

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

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

如何解决脏读

脏读 Dirty Read

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

\"可重复读\"隔离级别

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

如何解决不可重复读

Unrepeatable Read

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

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

\"串行化\"隔离级别下

如何解决幻读

幻读

Phantom Read

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

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

Lost Update

分支主题

隔离级别关系

数据

一致性非锁定读

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

锁

undo log + 回滚指针 = 版本链

子主题

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

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

readview

MVCC

实现

Isolution 隔离性

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

是重做日志文件。font color=\"#e74f4c\

Redo log

Durability（持久性）

事务4大特性ACID

查询当前会话 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')：font color=\"#e74f4c\

类型

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

使用注意事项

一颗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条这样的记录。

B+Tree

索引数据结构

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

选择索引的数据类型

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

聚簇索引与非聚簇索引

索引基础概念

id相同执行顺序从上到下

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

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

select_type

table

partitions

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

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

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

const

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

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

eq_ref

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

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

ref

EXPLAIN SELECT * FROM street WHERE cityCode BETWEEN 5108 and 5500

range

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

index

all

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

type

possible_keys

key

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

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

key_len

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

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

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

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

rows

filtered

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

using filesort

using temporary

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

Using index condition

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

Using index

使用了where过滤

Using where

使用了链接缓存

Using join buffer

extra

性能分析explain

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

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

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

面试题案例

索引失效

SELECT

JOIN ON

ORDER BY

WHERE

COUNT

语法细节优化

配置方法

MySQL 自带分析工具 mysqldumpslow

mysqlsla

分析工具

慢查询日志

Query Profiler

MySQL 5.7 四个基本的库

Performance Schema

索引层优化

提高Buffer容量

手动内存预热

自动内存预热

Buffer Pool

innodb_log_ﬁle_size 设置成 innodb_buﬀer_pool_size * 0.25

设置 Redo Log 大小

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

更改 Force Log at Commit 机制

降低磁盘IO

增加磁盘

使用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

事务日志

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

其他参数

参数优化

服务器层优化

性能优化

线性一致性

ZooKeeper实现的一致性

顺序一致性

因果一致性

读己所写一致性

会话一致性

单调读一致性

单调写一致性

最终一致性

弱一致性

Consistency

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

参与者在agree阶段Crash

参与者在commit阶段Crash

协调者在第二阶段Crash

协调者Crash，参与者Crash

Crash模型

二阶段提交协议

异常状态

图片示例

CanCommit

所有参与者反馈agree

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

PreCommit

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

DoCommit

Network Partition（网络分区情况）

三阶段提交协议

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

Try

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

Confirm

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

Cancle

TCC提交协议

假设A、B、C三个节点

1. Prepare 阶段

2. Accept 阶段

Paxos算法

分布式协议

ACID

Consistence（一致性）

Availability（可用性）

Partition Tolerance（分区容错性）

原因

重CA轻P

重CP轻A

重AP轻C

CAP 伪二分法

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

节点服务器的数据完整性

本质区别

ACID

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

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

可线性化

CAP

ACID与CAP一致性区别

Basically Available（基本可用）

Soft State（软状态）

Eventually Consistent（最终一致性）

BASE

分布式理论

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

使用场景

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

垂直分片

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

水平分片

方案路线

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

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

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

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

数据分片策略

概念

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

Inline

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

Standard

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

Complex

Hint

分片策略

SharedingSphere

MyCat

第三方插件

分库分表

分库分表（Sharding）

逻辑原理

技术实现

问题

Asynchronous Replication

逻辑实现

存在问题

Fully synchronous Replication

半同步退化机制

Semisynchronous Replication

半同步复制

无损复制

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

Lossless Replication

主从模式

数据一致性

事务并发冲突处理

节点故障自动检测

组成员自动管理

容错能力

特性

基于Paxos多主更新

单主模式

多主模式

冲突解决方式

Certify

仅支持 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 里面配置

限制条件

MGR使用

示图

Single-Primary Mode

Multi-Primary Mode

MGR模式

服务器间外部复制

binlog文件较小

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

优势

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

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

有AUTO_INCREMENT字段，会有阻塞

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

缺点

statement-based replication

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

更小锁粒度

支持多线程复制

优点

binlog日志量大

对大事务执行效率不高

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

不能直接查看binlog日志

row-based replication

mixed-based replication

基于Binlog复制

source_id

transaction_id

GTID

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复制

Master配置

Slave配置

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

服务器间内部复制

节点复制

MHA Manager

Master

Slave(M)

Slave(N)

架构

工作流程

无法保证数据不会丢失

Failover过程

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

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

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

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

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

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

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

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

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

只监控 master，未监控 slave 状态

MHA

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

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

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

存在单点故障

不支持基于 GTID 复制

MMM

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

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

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

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

Zookeeper + Proxy

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

MGR

支持多主写入

无脑裂问题

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

同 MGR

MySQL Innodb Cluster(基于MGR方案)

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

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

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

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

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

联表 JOIN 性能很差

MySQL NDB Cluster

同步复制

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

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

新节点加入数据自动复制

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

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

功能

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

不存在丢失事务的情况

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

需要打 wsrep 补丁

只支持 InnoDB 存储引擎

不同 Slave 的 binlog 可能不同

MariaDB Galera Cluster

基于分布一致性协议

高可用选型

高可用

架构图

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

Connectors

网络连接层

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

Management Service & Utilities

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

SQL Interface

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

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

Parser

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

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

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

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

Optimizer

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

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

缓存和缓冲组件

Caches & Buffers

SQL 处理层

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

B+Tree 索引

MyISAM

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

InnoDB

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

Hash 索引

Memory

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

不支持索引

CSV

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

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

只支持 READ COMMITTED 隔离级别

没有 MVCC

NDB

只支持INSERT/SELECT操作，MySQL5.1之后支持索引。

支持数据压缩，行锁