MySQL总结
MySQL 执行一条查询语句的流程
连接建立
- 建立连接
- 管理连接
- 校验用户身份
Serve层
查询缓存
- 查询语句如果命中查询缓存则直接返回,否则继续往下执行。MySQL 8.0 已删除该模块;
进行词法分析
进行语法分析
构建语法树
执行SQL
- prepare 预处理
- 检查 SQL 查询语句中的表或者字段是否存在;
- 将
select *
中的*
符号,扩展为表上的所有列;
- optimize 优化
- 优化器主要负责将 SQL 查询语句的执行方案确定下来,比如在表里面有多个索引的时候,优化器会基于查询成本的考虑,来决定选择使用哪个索引。
可以使用
explain
来解释 查询语句的执行过程1
explain select * from table_name where id=1;
- execute 执行
- 主键索引查询
- 全表扫描
- 索引下推
- prepare 预处理
mysql中的一行记录是如何存储的
其中,库的数据存储于/var/lib/mysql/databaseName
.opt
文件用来存储当前数据库的默认字符集和字符校验规则.frm
文件用来保存表的结构.ibd
文件用来存储表中的数据
表中间的文件的结构
innodb 的数据是以页为单位来读写的。默认的页大小为 16KB;page是innoDB存储引擎磁盘管理的最小单位
为了保证链表中相邻的物理位置也相邻,按照extent为单位进行分配,其大小为1M,对于16KB的page来说,连续的64个page为一个extent
表空间是由各个段(segment)组成的,段是由多个区(extent)组成的。段一般分为数据段、索引段和回滚段等。
- 索引段:存放 B + 树的非叶子节点的区的集合;
- 数据段:存放 B + 树的叶子节点的区的集合;
- 回滚段:存放的是回滚数据的区的集合,之前讲事务隔离 (opens new window)的时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据。
B+树如何进行查询数据
- 只有叶子节点存放了数据;非叶子节点仅用来存放目录项作为索引
- 非叶子节点分为不同的层次,通过分层开降低每一层的搜索量
- 所有的节点按照索引键大小排序,构成一个双向链表,便于范围查询
通过二分法快速定位到包含该记录的page。定位到page后,又会在该page中进行二分法快速定位记录所在的分组,最后在分组中进行遍历查询
其中,又分为 主键索引+二级索引
- 主键索引中是直接查找到对应行的数据
- 在使用二级索引进行查找数据时,如果查询的数据能在二级索引找到,那么就是「索引覆盖」操作,如果查询的数据不在二级索引里,就需要先在二级索引找到主键值,需要去聚簇索引中获得数据行,这个过程就叫作「回表」
mysql的写操作的执行流程 todo
mysql如何分库分表 todo
分库
分库同样是为了应对超大数据带来的巨大的IO需求,如果不拆库,那么单库所能支持的吞吐能力和磁盘空间,就会成为制衡业务发展的瓶颈。分库的主要目的是为突破单节点数据库服务器的I/O能力限制,解决数据库水平扩展性问题。
分区和分表可以把单表分到不同的硬盘上,但不能分配到不同服务器上。一台机器的性能是有限制的,用分库可以解决单台服务器性能不够,或者成本过高问题。
可以将一个库分成多个库,并在多个服务器上部署,就可以突破
分表
水平分表
- 按照范围进行切分
- hash取模
- .按照某个字段进行分表(国籍、地理位置、安卓端|IOS端)
- 按照时间进行分表
垂直分表
一般是表中的字段较多,或者有数据较大长度较长(比如text,blob,varchar(1000)以上的字段)的字段时,我们将不常用的,或者数据量大的字段拆分到“扩展表”上
这样避免查询时,数据量太大造成的“跨页”问题。
为什么选择B+树作为innoDB的索引数据结
- B+ 树的非叶子节点不存放实际的记录数据,仅存放索引,因此数据量相同的情况下,相比存储即存索引又存记录的 B 树,B+树的非叶子节点可以存放更多的索引,因此 B+ 树可以比 B 树更「矮胖」,查询底层节点的磁盘 I/O次数会更少。 (实际存储的数据角度)
- B+ 树有大量的冗余节点(所有非叶子节点都是冗余索引),这些冗余索引让 B+ 树在插入、删除的效率都更高,比如删除根节点的时候,不会像 B 树那样会发生复杂的树的变化;(增加删除数据的角度)
- B+ 树叶子节点之间用链表连接了起来,有利于范围查询,而 B 树要实现范围查询,因此只能通过树的遍历来完成范围查询,这会涉及多个节点的磁盘 I/O 操作,范围查询效率不如 B+ 树。(范围查询的角度)
三范式
数据库范式有 3 种:
- 1NF(第一范式):属性不可再分。
- 2NF(第二范式):1NF 的基础之上,且非主属性完全依赖于主属性
- 3NF(第三范式):3NF 在 2NF 的基础之上,消除了非主属性对于码的传递函数依赖 。
基本数据类型
MySQL 中有多种数据类型,每种类型用于存储不同类型的数据。以下是一些常见的 MySQL 数据类型:
- 整数类型(Integer Types):
TINYINT
: 1 字节,范围为 -128 到 127 或 0 到 255。SMALLINT
: 2 字节,范围为 -32,768 到 32,767 或 0 到 65,535。MEDIUMINT
: 3 字节,范围为 -8,388,608 到 8,388,607 或 0 到 16,777,215。INT
或INTEGER
: 4 字节,范围为 -2^31 到 2^31-1 或 0 到 2^32-1。BIGINT
: 8 字节,范围为 -2^63 到 2^63-1 或 0 到 2^64-1。
- 浮点数类型(Floating-Point Types):
FLOAT
: 单精度浮点数,4 字节。DOUBLE
或REAL
: 双精度浮点数,8 字节。
- 定点数类型(Fixed-Point Types):
DECIMAL
或NUMERIC
: 存储固定精度的小数,通常用于货币。
- 字符串类型(String Types):
CHAR
: 固定长度字符串。VARCHAR
: 变长字符串。TEXT
: 较大的文本字段。BINARY
: 固定长度二进制字符串。VARBINARY
: 变长二进制字符串。BLOB
: 较大的二进制对象。
- 日期和时间类型(Date and Time Types):
DATE
: 日期,格式为 ‘YYYY-MM-DD’。TIME
: 时间,格式为 ‘HH:MM:SS’。DATETIME
: 日期和时间,格式为 ‘YYYY-MM-DD HH:MM:SS’。TIMESTAMP
: 时间戳,与DATETIME
类似,但受时区影响。YEAR
: 年份,以四位数表示。
- 枚举和集合类型(Enum and Set Types):
ENUM
: 用于存储枚举类型的值。SET
: 用于存储多个枚举类型的值。
- 其他类型:
BOOLEAN
或BOOL
: 布尔类型,MySQL 中通常用 TINYINT(1) 实现。JSON
: 存储 JSON 数据。
NULL 和 ‘’ 的区别是什么?
NULL
跟 ''
(空字符串)是两个完全不一样的值,区别如下:
NULL
代表一个不确定的值,就算是两个NULL
,它俩也不一定相等。例如,SELECT NULL=NULL
的结果为 false,但是在我们使用DISTINCT
,GROUP BY
,ORDER BY
时,NULL
又被认为是相等的。''
的长度是 0,是不占用空间的,而NULL
是需要占用空间的。NULL
会影响聚合函数的结果。例如,SUM
、AVG
、MIN
、MAX
等聚合函数会忽略NULL
值。COUNT
的处理方式取决于参数的类型。如果参数是*
(COUNT(*)
),则会统计所有的记录数,包括NULL
值;如果参数是某个字段名(COUNT(列名)
),则会忽略NULL
值,只统计非空值的个数。- 查询
NULL
值时,必须使用IS NULL
或IS NOT NULLl
来判断,而不能使用 =、!=、 <、> 之类的比较运算符。而''
是可以使用这些比较运算符的。
drop、delete、truncate的区别
- drop(丢弃数据) :
drop table table_name
直接将表删除 - truncate(清空数据):
truncate table table_name
只删除表中的数据,但表的结构不发生变化。插入数据从第一行开始,自增id从1开始 - delete(删除数据):
delete from tanble_name where coloum=xxx
删除行数据,如果不加where条件和truncate 作用类似,但id是从之前最大的开始增长
数据库设计通常分为哪几步?
- 需求分析 : 分析用户的需求,包括数据、功能和性能需求。
- 概念结构设计 : 主要采用 E-R 模型进行设计,包括画 E-R 图。
- 逻辑结构设计 : 通过将 E-R 图转换成表,实现从 E-R 模型到关系模型的转换。
- 物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径。
- 数据库实施 : 包括编程、测试和试运行
- 数据库的运行和维护 : 系统的运行与数据库的日常维护。
事务
ACID
- atomicity 原子性 用 undoLog (回滚)实现
一个事务中的所有操作,要么全部完成,要么全都不完成,不回结束在中间某个状态。
- consistency 一致性 用 原子性+隔离性+持久性 实现
是指事务操作前后,数据满足完整性约束,数据库保持一致性。比如,用户 A 和用户 B 在银行分别有 800 元和 600 元,总共 1400 元,用户 A 给用户 B 转账 200 元,分为两个步骤,从 A 的账户扣除 200 元和对 B 的账户增加 200 元。一致性就是要求上述步骤操作后,最后的结果是用户 A 还有 600 元,用户 B 有 800 元,总共 1400 元,而不会出现用户 A 扣除了 200 元,但用户 B 未增加的情况(该情况,用户 A 和 B 均为 600 元,总共 1200 元)。
- isolation 隔离性 用MVCC (多版本并发控制)实现
数据库允许多个并发事务同时对同一行数据进行读写,隔离性可以防止多个事务并发执行时由于交叉执行的数据不一致。
- durability 持久性 用redoLog(重做)实现
事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
并发事务会引发什么问题
脏读
如果一个事务「读到」了另一个「未提交事务修改过的数据」,就意味着发生了「脏读」现象。
不可重复读
在一个事务内多次读取同一个数据,如果出现前后两次读到的数据不一样的情况,就意味着发生了「不可重复读」现象。
幻读
在一个事务中,由于其他事务的插入或删除操作,导致同一个查询在事务执行期间返回不同的行数;虽然可重复读可以保证同一事物内数据的一致性,但不能阻止其他满足要求事务对数据表进行插入和删除操作。要解决这个问题,可以使用更高级别的隔离级别,如串行化(Serializable),但这可能会导致性能下降。在实际应用中,需要权衡事务的隔离级别和性能需求,选择合适的隔离级别。
此外,一些数据库系统还提供了特定的机制,如行级锁或MVCC(多版本并发控制),以减轻幻读的影响。
隔离级别
SQL 标准提出了四种隔离级别来规避这些现象,隔离级别越高,性能效率就越低,这四个隔离级别如下:
- 读未提交
☞ 事务未提交,它的变更其他事务能看到
- 读提交
☞ 事务提交后,它的变更其他事务才能看到
- 可重复读
☞ 事务执行过程中看到的数据和这个事务一开始是一致的 ,这也是MySQL InnoDB引擎的默认隔离级别
- 串行化
☞ 对记录加上读写锁,多 个事 务对这条记录进行读写操作时,如果发送了冲突,后访问的事务必须等待前一个事务执行完成,阻塞,才能继续执行
MySQL InnoDB 引擎的可重复读隔离级别(默认隔离级),根据不同的查询方式,分别提出了避免幻读的方案:
- 针对快照读(普通 select 语句),是通过 MVCC 方式解决了幻读。
- 针对当前读(select … for update 等语句),是通过 next-key lock(记录锁+间隙锁)方式解决了幻读。
我举例了两个发生幻读场景的例子。
第一个例子:对于快照读, MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录,那么事务 A 前后两次查询的记录条目就不一样了,所以就发生幻读。
第二个例子:对于当前读,如果事务开启后,并没有执行当前读,而是先快照读,然后这期间如果其他事务插入了一条记录,那么事务后续使用当前读进行查询的时候,就会发现两次查询的记录条目就不一样了,所以就发生幻读。
所以,MySQL 可重复读隔离级别并没有彻底解决幻读,只是很大程度上避免了幻读现象的发生。
要避免这类特殊场景下发生幻读的现象的话,就是尽量在开启事务之后,马上执行 select … for update 这类当前读的语句,因为它会对记录加 next-key lock,从而避免其他事务插入一条新记录。
日志篇
undoLog
- 是 Innodb 存储引擎层生成的日志,实现了事务中的原子性,主要用于事务回滚和 MVCC。
每当 InnoDB 引擎对一条记录进行操作(修改、删除、新增)时,要把回滚时需要的信息都记录到 undo log 里,比如:
在插入一条记录时,要把这条记录的主键值记下来,这样之后回滚时只需要把这个主键值对应的记录删掉就好了;
在删除一条记录时,要把这条记录中的内容都记下来,这样之后回滚时再把由这些内容组成的记录插入到表中就好了;
在更新一条记录时,要把被更新的列的旧值记下来,这样之后回滚时再把这些列更新为旧值就好了
一条记录里面每次更新都会产生的undolog格式里 都有一个roll_pointer 和trx_id
- 通过trx_id 可以知道记录是被哪些事务修改的
- roll_pointer可以将这些 undolog 串成一个链表,这个链表即使版本链
undo log
记录的格式:
Undo Log的物理格式在不同版本的MySQL中可能会有所不同,但它通常包含以下几个关键部分:
- 事务ID(Transaction ID):每个事务都会被分配一个唯一的事务ID。
- 记录类型(Record Type):标识undo记录的类型,例如插入、更新或删除。
- 表ID(Table ID):标识被修改的表。
- 行ID(Row ID):标识被修改的行。
- 旧值(Old Value):记录被修改之前的值。
- 新值(New Value):记录被修改之后的值。
- 指针(Pointer):指向下一个或前一个undo记录的指针。
1 |
|
undo log的另一个作用:通过ReadView +undoLog 实现MVCC
undoLog 和数据页的刷盘策略一样,都需要通过redoLog实现持久化
其中,buffer pool中也有 undo页,对于undo的修改页也都会记录到redoLog中。redoLog 会每秒刷盘,同时,提交事务时也会刷盘
数据页和
redoLog
- redoLog 是Innodb引擎特有的日志,实现了事务的持久性,主要用于断电后的故障修复,实现了 crash-saafe的功能
- 将写操作从 【随机写】 变为 【顺序写】
- redoLog 是物理日志,记录的是
在某个数据页上做了什么修改
- redo log是循环写的,空间固定会用完
#todo
redolog什么时候落盘
binlLog
- Server特有的日志,binLog实现了数据的备份和主从复制
- binLog记录的是 逻辑日志,记录的是这个语句的原始逻辑
- binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
Binlog有两种模式,statement 格式的话是记sql语句, row格式会记录行的内容,记两条,更新前和更新后都有。
GPT的解释
以下是一个简单的 Binlog 记录的示例,假设我们执行了一个插入操作:
1 |
|
对应的 Binlog 记录可能类似于:
1 |
|
在这个示例中:
- Event Type(事件类型): 表示执行的操作类型,这里是一个插入操作(INSERT)。
- SQL Statement(SQL 语句): 表示实际执行的 SQL 语句。
与 Redo Log 不同,Binlog 记录的是逻辑层面的数据库更改。这使得 Binlog 具有以下特点:
- 跨数据库平台可用: Binlog 记录的是 SQL 语句,因此它可以更轻松地在不同的数据库平台之间迁移和复制数据。
- 易读性: Binlog 记录可以相对容易地阅读,因为它记录的是实际执行的 SQL 语句,而不是物理层面的更改。
- 灵活性: Binlog 记录的是逻辑操作,可以更好地支持对数据库的灵活操作,例如数据定义语言(DDL)操作。
在 MySQL 中,Binlog 主要用于以下方面:
- 主从复制: Binlog 用于将数据从一个 MySQL 服务器复制到另一个,以实现主从复制的功能。
- 数据恢复: Binlog 记录允许在发生故障时从备份中进行恢复,以最小化数据丢失。
- 数据审计: Binlog 记录的 SQL 语句可以用于审计数据库的更改历史。
MVCC
**multi-veriosn concurency control **
对于「读提交」和「可重复读」隔离级别的事务来说,它们是通过 Read View 来实现的,它们的区别在于创建 Read View 的时机不同:
- 「读提交」隔离级别是在每个 select 都会生成一个新的 Read View,也意味着,事务期间的多次读取同一条数据,前后两次读的数据可能会出现不一致,因为可能这期间另外一个事务修改了该记录,并提交了事务。
- 「可重复读」隔离级别是启动事务时生成一个 Read View,然后整个事务期间都在用这个 Read View,这样就保证了在事务期间读到的数据都是事务启动前的记录。
这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列」的比对,来控制并发事务访问同一个记录时的行为,这就叫 MVCC(多版本并发控制)。
如何避免幻读
- 针对快照读(普通 select 语句),是通过 MVCC 方式解决了幻读,因为可重复读隔离级别下,事务执行过程中看到的数据,一直跟这个事务启动时看到的数据是一致的,即使中途有其他事务插入了一条数据,是查询不出来这条数据的,所以就很好了避免幻读问题。
- 针对当前读(select … for update 等语句),是通过 next-key lock(记录锁+间隙锁)方式解决了幻读,因为当执行 select … for update 语句的时候,会加上 next-key lock,如果有其他事务在 next-key lock 锁范围内插入了一条记录,那么这个插入语句就会被阻塞,无法成功插入,所以就很好了避免幻读问题。
索引
索引的分类
- 数据结构
- B+ 树、HASH索引、FULL-TEXT索引
- 物理存储
- 主键索引(聚簇索引)、二级索引(辅助索引)
- 字段特性
- 主键索引、唯一索引、普通索引、前缀索引
- 字段个数
索引的模式
哈希表
有序表
搜索树
B+ 树
从图中不难看出,根据叶子节点的内容,索引类型分为主键索引和非主键索引。
主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。
非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。
- 如果语句是select * from T where ID=500,即主键查询方式,则只需要搜索ID这棵B+树;
- 如果语句是select * from T where k=5,即普通索引查询方式,则需要先搜索k索引树,得到ID的值为500,再到ID索引树搜索一次。这个过程称为回表。
建立索引
1 |
|
索引覆盖
覆盖索引是指 SQL 中 query 的所有字段,在索引 B+Tree 的叶子节点上都能找得到的那些索引,从二级索引中查询得到记录,而不需要通过聚簇索引查询获得,可以避免回表的操作。
最左前缀原则
使用联合索引时,存在最左匹配原则,也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候,如果不遵循「最左匹配原则」,联合索引会失效,这样就无法利用到索引快速查询的特性了。
比如,如果创建了一个 (a, b, c)
联合索引,如果查询条件是以下这几种,就可以匹配上联合索引:
- where a=1;
- where a=1 and b=2 and c=3;
- where a=1 and b=2;
需要注意的是,因为有查询优化器,所以 a 字段在 where 子句的顺序并不重要
但是,如果查询条件是以下这几种,因为不符合最左匹配原则,所以就无法匹配上联合索引,联合索引就会失效:
- where b=2;
- where c=3;
- where b=2 and c=3;
上面这些查询条件之所以会失效,是因为(a, b, c)
联合索引,是先按 a 排序,在 a 相同的情况再按 b 排序,在 b 相同的情况再按 c 排序。所以,b 和 c 是全局无序,局部相对有序的,这样在没有遵循最左匹配原则的情况下,是无法利用到索引的。
索引下推
对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表的次数
什么时候需要/不需要索引
索引是数据集合的目录
索引的最大好处是提高查询的速度,但索引也是有缺点的,比如:
- 需要占用物理空间,数量越大,占用空间越大
- 创建索引和维护索引选哟耗费时间,这种时间随数据量的增大而增大
- 会影响数据表的增删改,因为每次增删改都需要维护B+ 树的有序性,这需要动态的维护
适合索引的场景
- 字段唯一
- 经常用于
where
查询条件的字段 - 经常用于
group by
order by
的字段,这样查询的时候就可以直接利用 B+ tree 内部字段的有序性的特点
不适合实用索引的场景
- 使用不到
where
order by
group by
的场景 - 字段中存在大量的重复
- 数据量不够的场景,没必要去,费空间
- 经常用于更新的字段 ,比如不要对电商项目的用户余额建立索引,因为索引字段频繁修改,由于要维护 B+Tree的有序性,那么就需要频繁的重建索引,这个过程是会影响数据库性能的。
什么时候发生索引失效
- 当我们使用左或者左右模糊匹配的时候,也就是
like %xx
或者like %xx%
这两种方式都会造成索引失效; - 当我们在查询条件中对索引列做了计算、函数、类型转换操作,这些情况下都会造成索引失效;
- 联合索引要能正确使用需要遵循最左匹配原则,也就是按照最左优先的方式进行索引的匹配,否则就会导致索引失效。
- 在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效。
mysql为什么有时候会选错索引
优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的CPU资源越少。
当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
那么,MySQL是怎样得到索引的基数的呢?这里,我给你简单介绍一下MySQL采样统计的方法。
为什么要采样统计呢?因为把整张表取出来一行行统计,虽然可以得到精确的结果,但是代价太高了,所以只能选择“采样统计”。
采样统计的时候,InnoDB默认会选择N个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。
而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过1/M的时候,会自动触发重新做一次索引统计。
在MySQL中,有两种存储索引统计的方式,可以通过设置参数innodb_stats_persistent的值来选择:
- 设置为on的时候,表示统计信息会持久化存储。这时,默认的N是20,M是10。
- 设置为off的时候,表示统计信息只存储在内存中。这时,默认的N是8,M是16。
由于是采样统计,所以不管N是20还是8,这个基数都是很容易不准的。
字符串如何添加索引
- 直接创建完整索引,这样可能比较占用空间;
- 创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;
- 倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题;
- 创建hash字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都不支持范围扫描
如何优化索引
防止索引失效
- 对于前缀索引,字符串不要模糊匹配
- 对于联合索引,满足最左前缀原则
- 对于索引,不要对其进行计算、函数、类型转换等操作
- 在where条件中,如果OR前的条件是索引列,而OR后的条件不是索引列,索引会失效。
前缀索引优化
主键索引是自增的
索引不为NULL
锁
全局锁
全局锁(主要用于全库逻辑备份)
1 |
|
1 |
|
既然备份数据库数据的时候,使用全局锁会影响业务,那有什么其他方式可以避免?
有的,如果数据库的引擎支持的事务支持可重复读的隔离级别,那么在备份数据库之前先开启事务,会先创建 Read View,然后整个事务执行期间都在用这个 Read View,而且由于 MVCC 的支持,备份期间业务依然可以对数据进行更新操作。
因为在可重复读的隔离级别下,即使其他事务更新了表的数据,也不会影响备份数据库时的 Read View,这就是事务四大特性中的隔离性,这样备份期间备份的数据一直是在开启事务时的数据。
备份数据库的工具是 mysqldump,在使用 mysqldump 时加上 –single-transaction
参数的时候,就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。
InnoDB 存储引擎默认的事务隔离级别正是可重复读,因此可以采用这种方式来备份数据库。
但是,对于 MyISAM 这种不支持事务的引擎,在备份数据库时就要使用全局锁的方法。
表锁
1 |
|
行锁
1 |
|
日志
为什么要两阶段提交
优化
主从分离
主从复制的过程
mysql的主从复制依赖于 binglog,也就是记录在mysql上的所有变化会以二进制的格式存储在磁盘上。而mysql的主从复制 就是将 主节点上的binlog传输到从节点上。
- 首先从库在连接到主节点时会创建一个 IO 线程,用以请求主库更新的 binlog,并且把接收到的 binlog 信息写入一个叫做 relay log 的日志文件中
- 而主库也会创建一个 log dump 线程来发送 binlog 给从库;
- 同时,从库还会创建一个 SQL 线程读取 relay log 中的内容,并且在从库中做回放,最终实现主从的一致性。这是一种比较常见的主从复制方式。
随着从库数量增加,从库连接上来的 IO 线程比较多,主库也需要创建同样多的 log dump 线程来处理复制的请求,对于主库资源消耗比较高,同时受限于主库的网络带宽,所以在实际使用中,一般一个主库最多挂 3~5 个从库。
问题
主从之间有数据的延迟。
假设一种场景,数据写入主库后,有异步的场景去读取相应的新数据,此时读从库可能发现数据还没同步过来。
方案
- 数据冗余:异步的场景直接把所有需要的数据发过去,不去从库读数据了
- 使用缓存:直接把数据写到Memcache中,既可以保证数据一致性,还可以读到对应的数据
- 查询主库
分库分表
两个维度: 垂直 和水平
经历过分库分表后的系统,才能够突破单机的容量和请求量的瓶颈
水平分库分表的策略
- 按范围分片(Range Sharding):
- 根据数据的某个字段的值范围进行分片,例如按照用户ID范围将数据分片。
- 优点:实现简单。
- 缺点:数据容易倾斜,导致某些分片的负载过高。
- 按哈希分片(Hash Sharding):
- 根据数据的某个字段的哈希值进行分片,例如按照用户ID的哈希值将数据分片。
- 优点:数据分布较均匀。
- 缺点:增加节点时需要重新分片,数据迁移成本较高。
垂直分库分表
垂直分库分表(Vertical Partitioning)是将不同的表或不同字段的数据分散到不同的数据库实例中。目的是将逻辑上相关的数据放在一起,从而减小单个数据库的表的数量和大小,提高查询性能。
垂直分库分表的策略
- 按业务模块分库:
- 不同的业务模块放在不同的数据库中,例如用户信息放在一个数据库中,订单信息放在另一个数据库中。
- 优点:模块化清晰,不同业务的数据库负载相互独立。
- 缺点:跨库操作复杂。
- 按表分库:
- 将不同的表放在不同的数据库中,例如将大表分离到独立的数据库中。
- 优点:减少单个数据库的负载,提高查询性能。
- 缺点:管理多个数据库实例较复杂。