mysql体系架构
MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。
一、网络连接层
客户端连接器(Client Connectors):提供与MySQL服务器建立的支持。目前几乎支持所有主流 的服务端编程技术,例如常见的 Java、C、Python、.NET等,它们通过各自API技术与MySQL建立 连接。
二、服务层
服务层是MySQL Server的核心,主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优 化器和缓存六个部分。
· 连接池(Connection Pool):负责存储和管理客户端与数据库的连接,一个线程负责管理一个 连接。
· 系统管理和控制工具(Management Services & Utilities):例如备份恢复、安全管理、集群 管理等
· SQL接口(SQL Interface):用于接受客户端发送的各种SQL命令,并且返回用户需要查询的结 果。比如DML、DDL、存储过程、视图、触发器等。
·解析器(Parser):负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步 检查解析树是否合法。
·查询优化器(Optimizer):当“解析树”通过解析器语法检查后,将交由优化器将其转化成执行计 划,然后与存储引擎交互。
·缓存(Cache&Buffer): 缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,权限缓 存,引擎缓存等。如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。
三、存储引擎层(Pluggable Storage Engines)
存储引擎负责MySQL中数据的存储与提取,与底层系统文件进行交互。MySQL存储引擎是插件式的, 服务器中的查询执行引擎通过接口与存储引擎进行通信,接口屏蔽了不同存储引擎之间的差异 。现在有 很多种存储引擎,各有各的特点,最常见的是MyISAM和InnoDB。
四、系统文件层(File System)
该层负责将数据库的数据和日志存储在文件系统之上,并完成与存储引擎的交互,是文件的物理存储层。主要包含日志文件,数据文件,配置文件,pid 文件,socket 文件等。
日志文件
·错误日志(Error log)
默认开启,show variables like '%log_error%'
·通用查询日志(General query log) 记录一般查询语句,show variables like '%general%';
·二进制日志(binary log)
记录了对MySQL数据库执行的更改操作,并且记录了语句的发生时间、执行时长;但是它不 记录select、show等不修改数据库的SQL。主要用于数据库恢复和主从复制。
show variables like '%log_bin%'; //是否开启
show variables like '%binlog%'; //参数查看
show binary logs;//查看日志文件
·慢查询日志(Slow query log)
记录所有执行时间超时的查询SQL,默认是10秒。
show variables like '%slow_query%'; //是否开启
show variables like '%long_query_time%'; //时长
配置文件
用于存放MySQL所有的配置信息文件,比如my.cnf、my.ini等。
数据文件
db.opt 文件:记录这个库的默认使用的字符集和校验规则。
frm 文件:存储与表相关的元数据(meta)信息,包括表结构的定义信息等,每一张表都会 有一个frm 文件。 MYD 文件:MyISAM 存储引擎专用,存放 MyISAM 表的数据(data),每一张表都会有一个 .MYD 文件。
MYI 文件:MyISAM 存储引擎专用,存放 MyISAM 表的索引相关信息,每一张 MyISAM 表对 应一个 .MYI 文件。 ibd文件和 IBDATA 文件:存放 InnoDB 的数据文件(包括索引)。InnoDB 存储引擎有两种 表空间方式:独享表空间和共享表空间。独享表空间使用 .ibd 文件来存放数据,且每一张 InnoDB 表对应一个 .ibd 文件。共享表空间使用 .ibdata 文件,所有表共同使用一个(或多 个,自行配置).ibdata 文件。
ibdata1 文件:系统表空间数据文件,存储表元数据、Undo日志等 。
ib_logfile0、ib_logfile1 文件:Redo log 日志文件。
pid 文件
pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件,和许多其他 Unix/Linux 服务 端程序一样,它存放着自己的进程 id。
socket 文件
socket 文件也是在 Unix/Linux 环境下才有的,用户在 Unix/Linux 环境下客户端连接可以不通过 TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL。
用一条 SQL SELECT 语句的执行轨迹来说明客户端与 MySQL 的交互过程,如下图所示。
①通过客户端/服务器通信协议与 MySQL 建立连接。 ②查询缓存,这是 MySQL 的一个可优化查询的地方,如果开启了 Query Cache 且在查询缓存过程中查询到完全相同的 SQL 语句,则将查询结果直接返回给客户端;如果没有开启Query Cache 或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析,并生成解析树。 ③预处理器生成新的解析树。 ④查询优化器生成执行计划。 ⑤查询执行引擎执行 SQL 语句,此时查询执行引擎会根据 SQL 语句中表的存储引擎类型,以及对应的 API 接口与底层存储引擎缓存或者物理文件的交互情况,得到查询结果,由MySQL Server 过滤后将查询结果缓存并返回给客户端。若开启了 Query Cache,这时也会将SQL 语句和结果完整地保存到 Query Cache 中,以后若有相同的 SQL 语句执行则直接返回结果。
存储引擎
InnoDB 存储引擎的具体架构如下图所示。上半部分是实例层(计算层),位于内存中,下半部分是物理层,位于文件系统中。
实例层分为线程和内存。InnoDB 重要的线程有 Master Thread,Master Thread 是 InnoDB 的主线程,负责调度其他各线程。
物理层在逻辑上分为系统表空间、用户表空间和 Redo日志。
系统表空间里有 ibdata 文件和一些 Undo,ibdata 文件里有 insert buffer 段、double write段、回滚段、索引段、数据字典段和 Undo 信息段。
用户表空间是指以 .ibd 为后缀的文件,文件中包含 insert buffer 的 bitmap 页、叶子页(这里存储真正的用户数据)、非叶子页。InnoDB 表是索引组织表,采用 B+ 树组织存储,数据都存储在叶子节点中,分支节点(即非叶子页)存储索引分支查找的数据值。
Redo 日志中包括多个 Redo 文件,这些文件循环使用,当达到一定存储阈值时会触发checkpoint 刷脏页操作,同时也会在 MySQL 实例异常宕机后重启,InnoDB 表数据自动还原恢复过程中使用。
内存和物理结构如下图所示:
用户读取或者写入的最新数据都存储在 Buffer Pool 中,如果 Buffer Pool 中没有找到则会读取物理文件进行查找,之后存储到 Buffer Pool 中并返回给 MySQL Server。Buffer Pool 采用LRU 机制。
Buffer Pool 决定了一个 SQL 执行的速度快慢,如果查询结果页都在内存中则返回结果速度很快,否则会产生物理读(磁盘读),返回结果时间变长,性能远不如存储在内存中。但我们又不能将所有数据页都存储到 Buffer Pool 中,比如物理 ibd 文件有 500GB,我们的机器不可能配置能容得下 500GB 数据页的内存,因为这样做成本很高而且也没必要。在单机单实例情况下,我们可以配置 Buffer Pool 为物理内存的 60%~80%,剩余内存用于 session 产生的 sort 和 join 等,以及运维管理使用。如果是单机多实例,所有实例的buffer pool总量也不要超过物理内存的80%。开始时我们可以根据经验设置一个 Buffer Pool 的经验值,比如 16GB,之后业务在 MySQL 运行一段时间后可以根据 show global status like '%buffer_pool_wait%' 的值来看是否需要调整 Buffer Pool 的大小。
Redo log 是一个循环复用的文件集,负责记录 InnoDB 中所有对 Buffer Pool的物理修改日志,当 Redo log文件空间中,检查点位置的 LSN 和最新写入的 LSN 差值(checkpoint_age)达到 Redo log 文件总空间的 75% 后,InnoDB 会进行异步刷新操作,直到降至 75% 以下,并释放 Redo log 的空间;当 checkpoint_age 达到文件总量大小的 90% 后,会触发同步刷新,此时 InnoDB 处于挂起状态无法操作。
这样我们就看到 Redo log 的大小直接影响了数据库的处理能力,如果设置太小会导致强行 checkpoint 操作频繁刷新脏页,那我们就需要将 Redo log 设置的大一些,5.6 版本之前 Redo log 总大小不能超过 3.8GB,5.7 版本之后放开了这个限制。那既然太小影响性能,是不是设置得越大越好呢,这个问题留给你课后自己思考。
事务提交时 log buffer 会刷新到 Redo log 文件中,具体刷新机制由参数控制,你可以课后学习并根据自身业务特点进行配置。
若参数 innodb_file_per_table=ON,则表示用户建表时采用用户独立表空间,即一个表对应一组物理文件,.frm 表定义文件和 .ibd 表数据文件。
当然若这个参数设置为 OFF,则表示用户建表存储在 ibdata 文件中,不建议采用共享表空间,这样会导致 ibdata 文件过大,而且当表删除后空间无法回收。独立表空间可以在用户删除大量数据后回收物理空间,执行一个 DDL 就可以将表空间的高水位降下来了。
在 MySQL 5.6 版本之前,默认的存储引擎都是 MyISAM,但 5.6 版本以后默认的存储引擎就是 InnoDB 了。
· InnoDB 支持 ACID 的事务 4 个特性,而 MyISAM 不支持; · InnoDB 支持 4 种事务隔离级别,默认是可重复读 Repeatable Read 的,MyISAM 不支持; ·InnoDB 支持 crash 安全恢复,MyISAM 不支持; · InnoDB 支持外键,MyISAM 不支持; · InnoDB 支持行级别的锁粒度,MyISAM 不支持,只支持表级别的锁粒度; · InnoDB 支持 MVCC,MyISAM 不支持;
InnoDB 表最大还可以支持 64TB,支持聚簇索引、支持压缩数据存储,支持数据加密,支持查询/索引/数据高速缓存,支持自适应hash索引、空间索引,支持热备份和恢复等,如下图所示。
InnoDB 存储引擎
InnoDB 存储引擎的核心特性包括:MVCC、锁、锁算法和分类、事务、表空间和数据页、内存线程以及状态查询。
ARIES 三原则
ARIES 三原则,是指 Write Ahead Logging(WAL)。
先写日志后写磁盘,日志成功写入后事务就不会丢失,后续由 checkpoint 机制来保证磁盘物理文件与 Redo 日志达到一致性; 利用 Redo 记录变更后的数据,即 Redo 记录事务数据变更后的值; 利用 Undo 记录变更前的数据,即 Undo 记录事务数据变更前的值,用于回滚和其他事务多版本读。
show engine innodb status\G 的结果里面有详细的 InnoDB 运行态信息,分段记录的,包括内存、线程、信号、锁、事务等,请你多多使用,出现问题时从中能分析出具体原因和解决方案。
MySQL事务与锁机制
事务及其特性
事务是指作为单个逻辑工作单元执行的一系列操作,这些操作要么全做,要么全不做,是一个不可分割的工作单元。
一个逻辑工作单元要成为事务,在关系型数据库管理系统中,必须满足 4 个特性,即所谓的 ACID:原子性、一致性、隔离性和持久性。
· 一致性:事务开始之前和事务结束之后,数据库的完整性限制未被破坏。 · 原子性:事务的所有操作,要么全部完成,要么全部不完成,不会结束在某个中间环节。 · 持久性:事务完成之后,事务所做的修改进行持久化保存,不会丢失。 · 隔离性:当多个事务并发访问数据库中的同一数据时,所表现出来的相互关系。
ACID 及它们之间的关系如下图所示,比如 4 个特性中有 3 个与 WAL 有关系,都需要通过 Redo、Undo 日志来保证等。
一致性 首先来看一致性,一致性其实包括两部分内容,分别是约束一致性和数据一致性。
· 约束一致性:数据库中创建表结构时所指定的外键、Check、唯一索引等约束。可惜在 MySQL 中,是不支持 Check 的,只支持另外两种,所以约束一致性就非常容易理解了。 · 数据一致性:是一个综合性的规定,或者说是一个把握全局的规定。因为它是由原子性、持久性、隔离性共同保证的结果,而不是单单依赖于某一种技术。
原子性 接下来看原子性,原子性就是前面提到的两个“要么”,即要么改了,要么没改。也就是说用户感受不到一个正在改的状态。MySQL 是通过 WAL(Write Ahead Log)技术来实现这种效果的。
原子性和 WAL 到底有什么关系呢?其实关系非常大。举例来讲,如果事务提交了,那改了的数据就生效了,如果此时 Buffer Pool 的脏页没有刷盘,如何来保证改了的数据生效呢?就需要使用 Redo 日志恢复出来的数据。而如果事务没有提交,且 Buffer Pool 的脏页被刷盘了,那这个本不应该存在的数据如何消失呢?就需要通过 Undo 来实现了,Undo 又是通过 Redo 来保证的,所以最终原子性的保证还是靠 Redo 的 WAL 机制实现的。 持久性 所谓持久性,就是指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的,接下来的操作或故障不应该对其有任何影响。前面已经讲到,事务的原子性可以保证一个事务要么全执行,要么全不执行的特性,这可以从逻辑上保证用户看不到中间的状态。但持久性是如何保证的呢?一旦事务提交,通过原子性,即便是遇到宕机,也可以从逻辑上将数据找回来后再次写入物理存储空间,这样就从逻辑和物理两个方面保证了数据不会丢失,即保证了数据库的持久性。 隔离性 谓隔离性,指的是一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对其他的并发事务是隔离的。锁和多版本控制就符合隔离性。
并发事务控制
单版本控制-锁:锁用独占的方式来保证在只有一个版本的情况下事务之间相互隔离,所以锁可以理解为单版本控制。
在 MySQL 事务中,锁的实现与隔离级别有关系,在 RR(Repeatable Read)隔离级别下,MySQL 为了解决幻读的问题,以牺牲并行度为代价,通过 Gap 锁来防止数据的写入,而这种锁,因为其并行度不够,冲突很多,经常会引起死锁。现在流行的 Row 模式可以避免很多冲突甚至死锁问题,所以推荐默认使用 Row + RC(Read Committed)模式的隔离级别,可以很大程度上提高数据库的读写并行度。
多版本控制-MVCC:是指在数据库中,为了实现高并发的数据访问,对数据进行多版本处理,并通过事务的可见性来保证事务能看到自己应该看到的数据版本。
那个多版本是如何生成的呢?每一次对数据库的修改,都会在 Undo 日志中记录当前修改记录的事务号及修改前数据状态的存储地址(即 ROLL_PTR),以便在必要的时候可以回滚到老的数据版本。例如,一个读事务查询到当前记录,而最新的事务还未提交,根据原子性,读事务看不到最新数据,但可以去回滚段中找到老版本的数据,这样就生成了多个版本。
多版本控制很巧妙地将稀缺资源的独占互斥转换为并发,大大提高了数据库的吞吐量及读写性能。
原子性背后的技术
每一个写事务,都会修改 Buffer Pool,从而产生相应的 Redo 日志,这些日志信息会被记录到 ib_logfiles 文件中。因为 Redo 日志是遵循 Write Ahead Log 的方式写的,所以事务是顺序被记录的。
在 MySQL 中,任何 Buffer Pool 中的页被刷到磁盘之前,都会先写入到日志文件中,这样做有两方面的保证。
如果 Buffer Pool 中的这个页没有刷成功,此时数据库挂了,那在数据库再次启动之后,可以通过 Redo 日志将其恢复出来,以保证脏页写下去的数据不会丢失,所以必须要保证 Redo 先写。 因为 Buffer Pool 的空间是有限的,要载入新页时,需要从 LRU 链表中淘汰一些页,而这些页必须要刷盘之后,才可以重新使用,那这时的刷盘,就需要保证对应的 LSN 的日志也要提前写到 ib_logfiles 中,如果没有写的话,恰巧这个事务又没有提交,数据库挂了,在数据库启动之后,这个事务就没法回滚了。所以如果不写日志的话,这些数据对应的回滚日志可能就不存在,导致未提交的事务回滚不了,从而不能保证原子性,所以原子性就是通过 WAL 来保证的。
持久性背后的技术
一个“提交”动作触发的操作有:binlog 落地、发送 binlog、存储引擎提交、flush_logs, check_point、事务提交标记等。这些都是数据库保证其数据完整性、持久性的手段。
那这些操作如何做到持久性呢?前面讲过,通过原子性可以保证逻辑上的持久性,通过存储引擎的数据刷盘可以保证物理上的持久性。这个过程与前面提到的 Redo 日志、事务状态、数据库恢复、参数 innodb_flush_log_at_trx_commit 有关,还与 binlog 有关。这里多提一句,在数据库恢复时,如果发现某事务的状态为 Prepare,则会在 binlog 中找到对应的事务并将其在数据库中重新执行一遍,来保证数据库的持久性。
隔离性背后的技术
接下来看隔离性,InnoDB 支持的隔离性有 4 种,隔离性从低到高分别为:读未提交、读提交、可重复读、可串行化。
· 读未提交(RU,Read Uncommitted)。它能读到一个事务的中间过程,违背了 ACID 特性,存在脏读的问题,所以基本不会用到,可以忽略。 · 读提交(RC,Read Committed)。它表示如果其他事务已经提交,那么我们就可以看到,这也是一种最普遍适用的级别。但由于一些历史原因,可能 RC 在生产环境中用的并不多。 · 可重复读(RR,Repeatable Read),是目前被使用得最多的一种级别。其特点是有 Gap 锁、目前还是默认的级别、在这种级别下会经常发生死锁、低并发等问题。 · 可串行化,这种实现方式,其实已经并不是多版本了,又回到了单版本的状态,因为它所有的实现都是通过锁来实现的。
读提交每一条读操作语句都会获取一次 Read View,每次更新之后,都会获取数据库中最新的事务提交状态,也就可以看到最新提交的事务了,即每条语句执行都会更新其可见性视图。而反观下面的可重复读,这个可见性视图,只有在自己当前事务提交之后,才去更新,所以与其他事务是没有关系的。
这里需要提醒大家的是:在 RR 级别下,长时间未提交的事务会影响数据库的 PURGE 操作,从而影响数据库的性能,所以可以对这样的事务添加一个监控。
最后我们来讲下可串行化的隔离级别,前面已经提到了,可串行化是通过锁来实现的,所以实际上并不是多版本控制,它的特点也很明显:读锁、单版本控制、并发低。
一致性背后的技术
一致性可以归纳为数据的完整性。根据前文可知,数据的完整性是通过其他三个特性来保证的,包括原子性、隔离性、持久性,而这三个特性,又是通过 Redo/Undo 来保证的,正所谓:合久必分,分久必合,三足鼎力,三分归晋,数据库也是,为了保证数据的完整性,提出来三个特性,这三个特性又是由同一个技术来实现的,所以理解 Redo/Undo 才能理解数据库的本质。
MVCC 实现原理
MySQL InnoDB 存储引擎,实现的是基于多版本的并发控制协议——MVCC,而不是基于锁的并发控制。
MVCC 最大的好处是读不加锁,读写不冲突。在读多写少的 OLTP(On-Line Transaction Processing)应用中,读写不冲突是非常重要的,极大的提高了系统的并发性能,这也是为什么现阶段几乎所有的 RDBMS(Relational Database Management System),都支持 MVCC 的原因。
快照读与当前读
在 MVCC 并发控制中,读操作可以分为两类: 快照读(Snapshot Read)与当前读 (Current Read)。
· 快照读:读取的是记录的可见版本(有可能是历史版本),不用加锁。 · 当前读:读取的是记录的最新版本,并且当前读返回的记录,都会加锁,保证其他事务不会再并发修改这条记录。
注意:MVCC 只在 Read Commited 和 Repeatable Read 两种隔离级别下工作。
如何区分快照读和当前读呢? 可以简单的理解为:
快照读:简单的 select 操作,属于快照读,不需要加锁。** 当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。**
MVCC 多版本实现
事务对某行记录更新的过程的案例来讲解 MVCC 中多版本的实现。
假设 F1~F6 是表中字段的名字,1~6 是其对应的数据。后面三个隐含字段分别对应该行的隐含ID、事务号和回滚指针,如下图所示。
· 隐含 ID(DB_ROW_ID),6 个字节,当由 InnoDB 自动产生聚集索引时,聚集索引包括这个 DB_ROW_ID 的值。 · 事务号(DB_TRX_ID),6 个字节,标记了最新更新这条行记录的 Transaction ID,每处理一个事务,其值自动 +1。 · 回滚指针(DB_ROLL_PT),7 个字节,指向当前记录项的 Rollback Segment 的 Undo log记录,通过这个指针才能查找之前版本的数据。
具体的更新过程,简单描述如下。
首先,假如这条数据是刚 INSERT 的,可以认为 ID 为 1,其他两个字段为空。
然后,当事务 1 更改该行的数据值时,会进行如下操作,如下图所示。
· 用排他锁锁定该行;记录 Redo log; · 把该行修改前的值复制到 Undo log,即图中下面的行; · 修改当前行的值,填写事务编号,使回滚指针指向 Undo log 中修改前的行。
接下来,与事务 1 相同,此时 Undo log 中有两行记录,并且通过回滚指针连在一起。因此,如果 Undo log 一直不删除,则会通过当前记录的回滚指针回溯到该行创建时的初始内容,所幸的是在 InnoDB 中存在 purge 线程,它会查询那些比现在最老的活动事务还早的 Undo log,并删除它们,从而保证 Undo log 文件不会无限增长,如下图所示。
并发事务问题及解决方案
那么随着数据库并发事务处理能力的大大增强,数据库资源的利用率也会大大提高,从而提高了数据库系统的事务吞吐量,可以支持更多的用户并发访问。但并发事务处理也会带来一些问题,如:脏读、不可重复读、幻读。下面一一解释其含义。
脏读
一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象的叫作"脏读"(Dirty Reads)。
不可重复读
一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象就叫作“ 不可重复读”(Non-Repeatable Reads)。
幻读
一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”(Phantom Reads)。
解决方案
产生的这些问题,MySQL 数据库是通过事务隔离级别来解决的,上文已经详细讲解过,这里再进行简单的说明。
在上文讲 MySQL 事务特性的隔离性的时候就已经详细地讲解了事务的四种隔离级别。这里要求大家能够记住这种关系的矩阵表;记住各种事务隔离级别及各自都解决了什么问题,如下图所示。
MySQL 中默认的事务隔离级别是 RR,这里设置成 RC 隔离级别,此时提交事务 B 修改 id=1 的数据之后,事务 A 进行同样的查询操作,后一次和前一次的查询结果不一样,这就是不可重复读(重新读取产生的结果不一样了)。这里事务 A 读到了事务 B 提交的数据,即是“脏读”。
下面我们来看看在RR隔离级别下的情况。当 teacher_id=1时,事务 A 先进行一次读取操作,事务 B 中间修改了 id=1 的数据并提交,事务 C 也插入了一条数据并提交。事务 A 第二次读到的数据和第一次完全相同。所以说它是可重读的。
这里我们举个例子来说明“幻读”的问题。
在 RC 事务隔离级别下,这时事务 B INSERT 了一条数据,并提交,而事务 A 读到了事务 B 新插入的数据。这也是幻读,如下图所示。
不可重复读重点在于 UPDATA 和 DELETE,而幻读的重点在于 INSERT。它们之间最大的区别是如何通过锁机制来解决它们产生的问题。这里说的锁只是使用悲观锁机制。
那么在 RR 隔离级别下,事务 A 在 UPDATE 后加锁,事务 B 无法插入新数据,这样事务 A在 UPDATE 前后读的数据保持一致,避免了幻读。
跟上面的案例一样,也是在 RR 事务隔离级别下,事务 A 在 UPDATE 后加锁,对于其他两个事务,事务 B 和事务 C 的 INSERT 操作,就必须等事务 A 提交后,才能继续执行。这里就用到了“锁”,这里使用的是 Gap 锁,后面会详细讲解。它和上面的情况一样,解决了“幻读”的发生,如下图所示。
MySQL锁分类
在 MySQL 中有三种级别的锁:页级锁、表级锁、行级锁。
· 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。 会发生在:MyISAM、memory、InnoDB、BDB 等存储引擎中。 · 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高。会发生在:InnoDB 存储引擎。 · 页级锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。会发生在:BDB 存储引擎。
三种级别的锁分别对应存储引擎关系如下图所示。
注意:MySQL 中的表锁包括读锁和写锁。只需记住这个表锁模式兼容矩阵即可。
InnoDB 中的锁
在 MySQL InnoDB 存储引擎中,锁分为行锁和表锁。其中行锁包括两种锁。
· 共享锁(S):多个事务可以一起读,共享锁之间不互斥,共享锁会阻塞排它锁。 · 排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。
另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB 还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。表锁又分为三种。
意向共享锁(IS):事务计划给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。 自增锁(AUTO-INC Locks):特殊表锁,自增长计数器通过该“锁”来获得子增长计数器最大的计数值。
在加行锁之前必须先获得表级意向锁,否则等待 innodb_lock_wait_timeout 超时后根据innodb_rollback_on_timeout 决定是否回滚事务。
InnoDB 自增锁
在 MySQL InnoDB 存储引擎中,我们在设计表结构的时候,通常会建议添加一列作为自增主键。这里就会涉及一个特殊的锁:自增锁(即:AUTO-INC Locks),它属于表锁的一种,在 INSERT 结束后立即释放。我们可以执行 show engine innodb status\G 来查看自增锁的状态信息。
在自增锁的使用过程中,有一个核心参数,需要关注,即 innodb_autoinc_lock_mode,它有0、1、2 三个值。保持默认值就行。具体的含义可以参考官方文档,这里不再赘述,如下图所示。
InnoDB 行锁
InnoDB 行锁是通过对索引数据页上的记录(record)加锁实现的。主要实现算法有 3 种:Record Lock、Gap Lock 和 Next-key Lock。
Record Lock 锁:单个行记录的锁(锁数据,不锁 Gap)。 Gap Lock 锁:间隙锁,锁定一个范围,不包括记录本身(不锁数据,仅仅锁数据前面的Gap)。 Next-key Lock 锁:同时锁住数据,并且锁住数据前面的 Gap。
排查 InnoDB 锁问题
排查 InnoDB 锁问题通常有 2 种方法。
· 打开 innodb_lock_monitor 表,注意使用后记得关闭,否则会影响性能。 · 在 MySQL 5.5 版本之后,可以通过查看 information_schema 库下面的 innodb_locks、innodb_lock_waits、innodb_trx 三个视图排查 InnoDB 的锁问题。
InnoDB 死锁
在 MySQL 中死锁不会发生在 MyISAM 存储引擎中,但会发生在 InnoDB 存储引擎中,因为 InnoDB 是逐行加锁的,极容易产生死锁。那么死锁产生的四个条件是什么呢?
· 互斥条件:一个资源每次只能被一个进程使用; · 请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放; · 不剥夺条件:进程已获得的资源,在没使用完之前,不能强行剥夺; · 循环等待条件:多个进程之间形成的一种互相循环等待资源的关系。
在发生死锁时,InnoDB 存储引擎会自动检测,并且会自动回滚代价较小的事务来解决死锁问题。但很多时候一旦发生死锁,InnoDB 存储引擎的处理的效率是很低下的或者有时候根本解决不了问题,需要人为手动去解决。
既然死锁问题会导致严重的后果,那么在开发或者使用数据库的过程中,如何避免死锁的产生呢?这里给出一些建议:
· 加锁顺序一致; · 尽量基于 primary 或 unique key 更新数据。 · 单次操作数据量不宜过多,涉及表尽量少。 · 减少表上索引,减少锁定资源。 · 相关工具:pt-deadlock-logger。
资源征用产生的死锁:
session1 首先拿到 id=1 的锁,session2 同期拿到了 id=5 的锁后,两者分别想拿到对方持有的锁,于是产生死锁。
元数据锁
session1 和 session2 都在抢占 id=1 和 id=6 的元数据的资源,产生死锁。
查看 MySQL 数据库中死锁的相关信息,可以执行 show engine innodb status\G 来进行查看,重点关注 “LATEST DETECTED DEADLOCK” 部分。
给大家一些开发建议来避免线上业务因死锁造成的不必要的影响。
· 更新 SQL 的 where 条件时尽量用索引; · 加锁索引准确,缩小锁定范围; · 减少范围更新,尤其非主键/非唯一索引上的范围更新。 · 控制事务大小,减少锁定数据量和锁定时间长度 (innodb_row_lock_time_avg)。 · 加锁顺序一致,尽可能一次性锁定所有所需的数据行。
高性能库表设计
范式与反范式:
优秀的库表设计是高性能数据库的基础。如何才能设计出高性能的库表结构呢?这里必须要提到数据库范式。范式是基础规范,反范式是针对性设计。
范式是关系数据库理论的基础,也是我们在设计数据库结构过程中所要遵循的规则和指导方法。数据库的设计范式是数据库设计所需要满足的规范。只有理解数据库的设计范式,才能设计出高效率、优雅的数据库,否则可能会设计出低效的库表结构。
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,还又称完美范式)。
满足最低要求的叫第一范式,简称 1NF。在第一范式基础上进一步满足一些要求的为第二范式,简称 2NF。其余依此类推。各种范式呈递次规范,越高的范式数据库冗余越小。通常所用到的只是前三个范式,即:第一范式(1NF),第二范式(2NF),第三范式(3NF)。
第一范式 第一范式无重复的列,表中的每一列都是拆分的基本数据项,即列不能够再拆分成其他几列,强调的是列的原子性.。
如果在实际场景中,一个联系人有家庭电话和公司电话,那么以“姓名、性别、电话”为表头的表结构就没有达到 1NF。要符合 1NF 我们只需把电话列拆分,让表头变为姓名、性别、家庭电话、公司电话即可。 第二范式 第二范式属性完全依赖于主键,首先要满足它符合 1NF,另外还需要包含两部分内容:
表必须有一个主键; 没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分。即要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
第三范式 第三范式属性不传递依赖于其他非主属性,首先需要满足 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况。
第二范式和第三范式的区别
第二范式:非主键列是否依赖主键(包括一列通过某一列间接依赖主键),要是有依赖关系就是第二范式; 第三范式:非主键列是否直接依赖主键,不能是那种通过传递关系的依赖。要是符合这种依赖关系就是第三范式。
范式优缺点 经过前面的讲解和案例分析可知范式具备以下优点:
· 避免数据冗余,减少维护数据完整性的麻烦; · 减少数据库的空间; · 数据变更速度快。
同时,也有如下缺点:
· 按照范式的规范设计的表,等级越高的范式设计出来的表数量越多。 · 获取数据时,表关联过多,性能较差。
表的数量越多,查询所需要的时间越多。也就是说所用的范式越高,对数据操作的性能越低。
反范式
范式是普适的规则,满足大多数的业务场景的需求。对于一些特殊的业务场景,范式设计的表,无法满足性能的需求。此时,就需要根据业务场景,在范式的基础之上进行灵活设计,也就是反范式设计。
反范式设计主要从三方面考虑:
· 业务场景; · 相应时间; · 字段冗余。
反范式设计就是用空间来换取时间,提高业务场景的响应时间,减少多表关联。主要的优点如下。
· 允许适当的数据冗余,业务场景中需要的数据几乎都可以在一张表上显示,避免关联; · 可以设计有效的索引。
范式与反范式异同 范式化模型:
· 数据没有冗余,更新容易; · 当表的数量比较多,查询数据需要多表关联时,会导致查询性能低下。
反范式化模型:
· 冗余将带来很好的读取性能,因为不需要 join 很多表; · 虽然需要维护冗余数据,但是对磁盘空间的消耗是可以接受的。
MySQL 使用原则和设计规范
MySQL 虽然具有很多特性并提供了很多功能,但是有些特性会严重影响它的性能,比如,在数据库里进行计算,写大事务、大 SQL、存储大字段等。
想要发挥 MySQL 的最佳性能,需要遵循 3 个基本使用原则。
1、 首先是需要让 MySQL 回归存储的基本职能:MySQL 数据库只用于数据的存储,不进行数据的复杂计算,不承载业务逻辑,确保存储和计算分离; 2、 其次是查询数据时,尽量单表查询,减少跨库查询和多表关联; 3、 还有就是要杜绝大事务、大 SQL、大批量、大字段等一系列性能杀手。
· 大事务,运行步骤较多,涉及的表和字段较多,容易造成资源的争抢,甚至形成死锁。一旦事务回滚,会导致资源占用时间过长。 · 大 SQL,复杂的 SQL 意味着过多的表的关联,MySQL 数据库处理关联超过 3 张表以上的 SQL 时,占用资源多,性能低下。 · 大批量,意味着多条 SQL 一次性执行完成,必须确保进行充分的测试,并且在业务低峰时段或者非业务时段执行。 · 大字段,blob、text 等大字段,尽量少用。必须要用时,尽量与主业务表分离,减少对这类字段的检索和更新。
下面具体讲解数据库的基本设置规则:
1、 必须指定默认存储引擎为 InnoDB,并且禁用 MyISAM 存储引擎,随着 MySQL 8.0 版本的发布,所有的数据字典表都已经转换成了 InnoDB,MyISAM 存储引擎已成为了历史。 2、 默认字符集 UTF8mb4,以前版本的 UTF8 是 UTF8mb3,未包含个别特殊字符,新版本的 UTF8mb4 包含所有字符,官方强烈建议使用此字符集。 3、 关闭区分大小写功能。设置 lower_case_tables_name=1,即可关闭区分大小写功能,即大写字母 T 和小写字母 t 一样。
MySQL 数据库提供的功能很全面,但并不是所有的功能性能都高效。
1、 存储过程、触发器、视图、event。为了存储计算分离,这类功能尽量在程序中实现。这些功能非常不完整,调试、排错、监控都非常困难,相关数据字典也不完善,存在潜在的风险。一般在生产数据库中,禁止使用。 2、 lob、text、enum、set。这些字段类型,在 MySQL 数据库的检索性能不高,很难使用索引进行优化。如果必须使用这些功能,一般采取特殊的结构设计,或者与程序结合使用其他的字段类型替代。比如:set 可以使用整型(0,1,2,3)、注释功能和程序的检查功能集合替代。
规范命名
命名规范如下,命名时的字符取值范围为:a~z,0~9 和 _(下画线)。
· 所有表名小写,不允许驼峰式命名; · 允许使用 -(横线)和 (空格);如下图所示,当使用 -(横线),后台默认会转化成 @002d; · 不允许使用其他特殊字符作为名称,减少潜在风险。
数据库库名的命名规则必须遵循“见名知意”的原则,即库名规则为“数据库类型代码 + 项目简称 + 识别代码 + 序号”。
这样包含了更多的业务信息,比如:
· 出入系统业务生产库:AOCT、AOCT1、AOCT2; · 出入系统业务开发库:AOCTDEV、AOCTDEV1、AOCTDEV2; · 出入系统业务测试库:AOCTTEST、AOCTTEST1、AOCTTEST2; · 只有一个数据库,则不加序号,否则末尾增加序号; · 生产库不加识别代码,否则需要增加识别代码 DEV 或 TEST; · 如果只作历史库,则只需要项目简称 +H+ 序号; · 图例为常用的识别代码。
表名的命名规则分为:
· 单表仅使用 a~z、; · 分表名称为“表名__编号”; · 业务表名代表用途、内容:子系统简称业务含义_后缀。
常见业务表类型有:
· 临时表,tmp; · 备份表,bak; · 字典表,dic; · 日志表,log。
字段名精确,遵循“见名知意”的原则,格式:名称_后缀。
· 避免普遍简单、有歧义的名称。
用户表中,用户名的字段为 UserName 比 Name 更好。
· 布尔型的字段,以助动词(has/is)开头。
用户是否有留言 hasmessage,用户是否通过检查 ischecked 等。
常见后缀如下:
· 流水号/无意义主键,后缀为 id,比如 task_id; · 时间,后缀为 time,insert_time。
程序账号与数据库名称保持一致。如果所有的程序账号都是 root@‘%’,密码也一样,很容易错连到其他的数据库,造成误操作。
索引命名格式,主要为了区分哪些对象是索引:
· 前缀表名(或缩写)字段名(或缩写); · 主键必须使用前缀“pk_”; · UNIQUE 约束必须使用前缀“uk”; · 普通索引必须使用前缀“idx_”。
数据库规范库表字段的命名,能够提高数据库的易读性,为数据库表设计打下基础。下面我们具体看看表设计的一些规则。
· 显式指定需要的属性;
创建表时显示指定字符集、存储引擎、注释信息等。
· 不同系统之间,统一规范;
不同表之间的相同字段或者关联字段,字段类型/命名要保持一致;库表字符集和前端程序、中间件必须保持一致的 UTF8mb4。
InnoDB 表的注意事项
· 主键列,UNSIGNED 整数,使用 auto_increment;禁止手动更新 auto_increment,可以删除。 · 必须添加 comment 注释。 · 必须显示指定的 engine。 · 表必备三字段:id、 xxx_create、 xxx_modified。
· id 为主键,类型为 unsigned bigint 等数字类型; · xxx_create、xxx_modified 的类型均为 datetime 类型,分别记录该条数据的创建时间、修改时间。
备份表/临时表等常见表的设计规范
1 备份表,表名必须添加 bak 和日期,主要用于系统版本上线时,存储原始数据,上线完成后,必须及时删除。 2 临时表,用于存储中间业务数据,定期优化,及时降低表碎片。 3 日志类表,首先考虑不入库,保存成文件,其次如果入库,明确其生命周期,保留业务需求的数据,定期清理。 4 大字段表,把主键字段和大字段,单独拆分成表,并且保持与主表主键同步,尽量减少大字段的检索和更新。 大表,根据业务需求,从垂直和水平两个维度进行拆分。
垂直拆分:按列关联度。
水平拆分:
· 按照时间、地域、范围等; · 冷热数据(历史数据归档)。
字段的设计
1 根据业务场景需求,选择合适的类型,最短的长度;确保字段的宽度足够用,但也不要过宽。所有字段必须为 NOT NULL,空值则指定 default 值,空值难以优化,查询效率低。比如:人的年龄用 unsigned tinyint(范围 0~255,人的寿命不会超过 255 岁);海龟就必须是 smallint,但如果是太阳的年龄,就必须是 int;如果是所有恒星的年龄都加起来,那么就必须使用 bigint。 2 表字段数少而精,尽量不加冗余列。 3 单实例表个数必须控制在 2000 个以内。 4 单表分表个数必须控制在 1024 个以内。 5 单表字段数上限控制在 20~50 个。
禁用 ENUM、SET 类型。
· 兼容性不好,性能差。
解决方案:使用 TINYINT,在 COMMENT 信息中标明被枚举的含义。is_disable
TINYINT UNSIGNED DEFAULT '0' COMMENT '0:启用 1:禁用 2:异常’。
禁用列为 NULL。
· MySQL 难以优化 NULL 列; · NULL 列加索引,需要额外空间; · 含 NULL 复合索引无效。
解决方案:在列上添加 NOT NULL DEFAULT 缺省值。
禁止 VARBINARY、BLOB 存储图片、文件等。
· 禁止在数据库中存储大文件,例如照片,可以将大文件存储在对象存储系统中,数据库中存储路径。
不建议使用 TEXT/BLOB:
· 处理性能差; · 行长度变长; · 全表扫描代价大。
解决方案:拆分成单独的表。
存储字节越小,占用空间越小。尽量选择合适的整型,如下图所示。
1 主键列,无负数,建议使用 INT UNSIGNED 或者 BIGINT UNSIGNED;预估字段数字取值会超过 42 亿,使用 BIGINT 类型。 2 短数据使用 TINYINT 或 SMALLINT,比如:人类年龄,城市代码。 3 使用 UNSIGNED 存储非负数值,扩大正数的范围。
N的解释
字符集都为 UTF8mb4,中文存储占三个字节,而数据或字母,则只占一个字节。
下面看一下字符类型中 N 的解释。
CHAR(N) 和 VARCHAR(N) 的长度 N,不是字节数,是字符数。 username 列可以存多少个汉字,占用多少个字节 username 最多能存储 40 个字符,占用 120 个字节。
数字类型
主键
用自增整型做主键,一律使用 BIGINT,而不是 INT。不要为了节省 4 个字节使用 INT,当达到上限时,再进行表结构的变更。
当达到int上限后,再次进行自增插入时,会报错(out of range value for column 'a' at row 1),mysql数据库不会自动将其重置为1.
主键自增
CREATE TABLE t
( a
int NOT NULL AUTO_INCREMENT, PRIMARY KEY (a
) ) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
注意:
升级 MySQL 版本到 8.0 版本,每张表的自增值会持久化;
若无法升级数据库版本,则强烈不推荐在核心业务表中使用自增数据类型做主键。
为了之后更好的分布式架构扩展性,不建议使用整型类型做主键,更为推荐的是字符串类型
资金字段设计
在用户余额、基金账户余额、数字钱包、零钱等的业务设计中,由于字段都是资金字段,通常程序员习惯使用 DECIMAL 类型作为字段的选型,因为这样可以精确到分,如:DECIMAL(8,2)。
在海量互联网业务的设计标准中,并不推荐用 DECIMAL 类型,而是更推荐将 DECIMAL 转化为 整型类型。也就是说,资金类型更推荐使用用分单位存储,而不是用元单位存储。如1元在数据库中用整型类型 100 存储。
类型 DECIMAL 是通过二进制实现的一种编码方式,计算效率远不如整型来的高效。因此,推荐使用 BIG INT 来存储金额相关的字段。
字符串类型
CHAR(N) 用来保存固定长度的字符,N 的范围是 0 ~ 255,请牢记,N 表示的是字符,而不是字节。VARCHAR(N) 用来保存变长字符,N 的范围为 0 ~ 65536, N 表示字符。
在超出 65536 个字符的情况下,可以考虑使用更大的字符类型 TEXT 或 BLOB,两者最大存储长度为 4G,其区别是 BLOB 没有字符集属性,纯属二进制存储。
MySQL 数据库的 VARCHAR 字符类型,最大能够存储 65536 个字符,所以在 MySQL 数据库下,绝大部分场景使用类型 VARCHAR 就足够了。
推荐把 MySQL 的默认字符集设置为 UTF8MB4,否则,某些 emoji 表情字符无法在 UTF8 字符集下存储,比如 emoji 笑脸表情,对应的字符编码为 0xF09F988E。
不同的字符集,CHAR(N)、VARCHAR(N) 对应最长的字节也不相同。比如 GBK 字符集,1 个字符最大存储 2 个字节,UTF8MB4 字符集 1 个字符最大存储 4 个字节。所以从底层存储内核看,在多字节字符集下,CHAR 和 VARCHAR 底层的实现完全相同,都是变长存储!
插入表情:insert into emo_test values(0xF09F988E);将字符集设置成UTF8MB4.
鉴于目前默认字符集推荐设置为 UTF8MB4,所以在表结构设计时,可以把 CHAR 全部用 VARCHAR 替换,底层存储的本质实现一模一样。
账号密码的设计
可以对 MD5 进行暴力破解,计算出所有可能的字符串对应的 MD5 值。
在设计密码存储使用,还需要加盐(salt),每个公司的盐值都是不同的,因此计算出的值也是不同的。若盐值为 psalt,则密码 12345678 在数据库中的值为: password = MD5(‘psalt12345678’)
这样的密码存储设计是一种固定盐值的加密算法,其中存在三个主要问题:
若 salt 值被(离职)员工泄漏,则外部黑客依然存在暴利破解的可能性;
对于相同密码,其密码存储值相同,一旦一个用户密码泄漏,其他相同密码的用户的密码也将被泄漏;
固定使用 MD5 加密算法,一旦 MD5 算法被破解,则影响很大。
所以一个真正好的密码存储设计,应该是:动态盐 + 非固定加密算法。
$salt$cryption_algorithm$value
其中:
$salt:表示动态盐,每次用户注册时业务产生不同的盐值,并存储在数据库中。若做得再精细一点,可以动态盐值 + 用户注册日期合并为一个更为动态的盐值。
$cryption_algorithm:表示加密的算法,如 v1 表示 MD5 加密算法,v2 表示 AES256 加密算法,v3 表示 AES512 加密算法等。
$value:表示加密后的字符串。
业务隐私信息,如密码、手机、信用卡等信息,需要加密。切记简单的MD5算法是可以进行暴力破解,并不安全,推荐使用动态盐+动态加密算法进行隐私数据的存储。
订单状态使用整型,因为可能值会变,如果使用枚举或check约束( 字符串 + CHECH约束),需要修改表结构用户头像直接使用varchar存URL
CREATE TABLE User ( id BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, sex CHAR(1) NOT NULL, password VARCHAR(1024) NOT NULL, regDate DATETIME NOT NULL, CHECK (sex = 'M' OR sex = 'F'), PRIMARY KEY(id) );
日期类型
MySQL 数据库中常见的日期类型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。因为业务绝大部分场景都需要将日期精确到秒,所以在表结构设计中,常见使用的日期类型为DATETIME 和 TIMESTAMP。
我们使用 MySQL 内置的函数(FROM_UNIXTIME(),UNIX_TIMESTAMP()),可以将日期转化为数字,用 INT UNSIGNED 存储日期和时间。
DATETIME
类型 DATETIME 最终展现的形式为:YYYY-MM-DD HH:MM:SS,固定占用 8 个字节。
从 MySQL 5.6 版本开始,DATETIME 类型支持毫秒,DATETIME(N) 中的 N 表示毫秒的精度。例如,DATETIME(6) 表示可以存储 6 位的毫秒值。同时,一些日期函数也支持精确到毫秒,例如常见的函数 NOW、SYSDATE。
CREATE TABLE User ( id BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, sex CHAR(1) NOT NULL, password VARCHAR(1024) NOT NULL, money INT NOT NULL DEFAULT 0, register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), CHECK (sex = 'M' OR sex = 'F'), PRIMARY KEY(id) );
表 User 中,列 register_date 表示注册时间,DEFAULT CURRENT_TIMESTAMP 表示记录插入时,若没有指定时间,默认就是当前时间。 列 last_modify_date 表示当前记录最后的修改时间,DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) 表示每次修改都会修改为当前时间。
TIMESTAMP
其实际存储的内容为‘1970-01-01 00:00:00’到现在的毫秒数。在 MySQL 中,由于类型 TIMESTAMP 占用 4 个字节,因此其存储的时间上限只能到‘2038-01-19 03:14:07’。
同类型 DATETIME 一样,从 MySQL 5.6 版本开始,类型 TIMESTAMP 也能支持毫秒。与 DATETIME 不同的是,若带有毫秒时,类型 TIMESTAMP 占用 7 个字节,而 DATETIME 无论是否存储毫秒信息,都占用 8 个字节。
类型 TIMESTAMP 最大的优点是可以带有时区属性,因为它本质上是从毫秒转化而来。如果你的业务需要对应不同的国家时区,那么类型 TIMESTAMP 是一种不错的选择。比如新闻类的业务,通常用户想知道这篇新闻发布时对应的自己国家时间,那么 TIMESTAMP 是一种选择。
DATETIME vs TIMESTAMP vs INT,怎么选?
在做表结构设计时,对日期字段的存储,开发人员通常会有 3 种选择:DATETIME、TIMESTAMP、INT。 INT 类型就是直接存储 '1970-01-01 00:00:00' 到现在的毫秒数,本质和 TIMESTAMP 一样,因此用 INT 不如直接使用 TIMESTAMP。
当然,有些同学会认为 INT 比 TIMESTAMP 性能更好。但是,由于当前每个 CPU 每秒可执行上亿次的计算,所以无须为这种转换的性能担心。更重要的是,在后期运维和数据分析时,使用 INT 存储日期,是会让 DBA 和数据分析人员发疯的,INT的可运维性太差。
但若要将时间精确到毫秒,TIMESTAMP 要 7 个字节,和 DATETIME 8 字节差不太多。另一方面,现在距离 TIMESTAMP 的最大值‘2038-01-19 03:14:07’已经很近。
总的来说,我建议你使用类型 DATETIME。 对于时区问题,可以由前端或者服务这里做一次转化,不一定非要在数据库中解决。
显式指定时区的性能要远远好于直接使用操作系统时区。所以,日期字段推荐使用 DATETIME,没有时区转化。即便使用 TIMESTAMP,也需要在数据库中显式地配置时区,而不是用系统时区。
表结构设计规范:每条记录都要有一个时间字段
在做表结构设计规范时,强烈建议你每张业务核心表都增加一个 DATETIME 类型的 last_modify_date 字段,并设置修改自动更新机制, 即便标识每条记录最后修改的时间。
这样设计的好处是: 用户可以知道每个用户最近一次记录更新的时间,以便做后续的处理。比如在电商的订单表中,可以方便对支付超时的订单做处理;在金融业务中,可以根据用户资金最后的修改时间做相应的资金核对等。
状态列的最优设计是用字符串+CHECK约束,这样能避免脏数据的插入。
create table (
status CHAR(1)
constraint 'check_status'
check(status in('s','d','t','u'))
)
对于图片的存储,一般不建议使用二进制类型BLOB存储,图片存储在对象存储上,数据库中只是存储图片对应的链接。
索引设计和工作原理
数据库索引是一种数据结构,它以额外的写入和存储空间为代价来提高数据库表上数据检索操作的速度。通俗来说,索引类似于书的目录,根据其中记录的页码可以快速找到所需的内容。
MySQL 官方对索引(Index)的定义是存储引擎用于快速查找记录的一种数据结构。
· 索引是物理数据页,数据库页大小(Page Size)决定了一个页可以存储多少个索引行,以及需要多少页来存储指定大小的索引。 · 索引可以加快检索速度,但同时也降低索引列插入、删除、更新的速度,索引维护需要代价。
索引涉及的理论知识有二分查找法、哈希表及 B+Tree。
索引原理
对于索引数据结构的选择其本质是贴合当前数据读写的硬件环境选择一个优秀的数据结构进行数据存储及遍历,在数据库中大部分索引都是通过 B+Tree 来实现的。当然也涉及其他数据结构,在 MySQL 中除了 B+Tree 索引外我们还需要关注下 Hash 索引。
Hash 索引
哈希表是数据库中哈希索引的基础,是根据键值 <key,value> 存储数据的结构。简单说,哈希表是使用哈希函数将索引列计算到桶或槽的数组,实际存储是根据哈希函数将 key 换算成确定的存储位置,并将 value 存放到该数组位置上。访问时,只需要输入待查找的 key,即可通过哈希函数计算得出确定的存储位置并读取数据。
哈希索引是如何实现的?数据库中哈希索引是基于哈希表实现的,对于哈希索引列的数据通过 Hash 算法计算,得到对应索引列的哈希码形成哈希表,由哈希码及哈希码指向的真实数据行的指针组成了哈希索引。哈希索引的应用场景是只在对哈希索引列的等值查询才有效。
因为哈希索引只存储哈希值和行指针,不存储实际字段值,所以其结构紧凑,查询速度也非常快,在无哈希冲突的场景下访问哈希索引一次即可命中。但是哈希索引只适用于等值查询,包括 =、IN()、<=> (安全等于, select null <=> null 和 select null=null 是不一样的结果) ,不支持范围查询。
Hash 碰撞如何处理?Hash 碰撞是指不同索引列值计算出相同的哈希码,如上图所示, 表中 name 字段为 John Smith 和 Sandra Dee 两个不同值根据 Hash 算法计算出来的哈希码都是 152,这就表示出现了 Hash 碰撞。 对于 Hash 碰撞通用的处理方法是使用链表,将 Hash 冲突碰撞的元素形成一个链表,发生冲突时在链表上进行二次遍历找到数据。
· Hash 碰撞跟选择的 Hash 算法有关系,为了减少 Hash 碰撞的概率,优先选择避免 Hash 冲突的 Hash 算法,例如,使用 Percona Server 的函数 FNV64() ,其哈希值为 64 位,出现 Hash 冲突的概率要比 CRC32 小很多。 · 其次是考虑性能,优先选择数字类型的 Hash 算法
综合建议 Hash 算法使用优先级为:FNV64 > CRC32 (大数据量下 Hash 冲突概率较大)> MD5 > SHA1。
最后再看看,MySQL 中如何使用 Hash 索引?在 MySQL 中主要是分为 Memory 存储引擎原生支持的 Hash 索引 、InnoDB 自适应哈希索引及 NDB 集群的哈希索引3类。
InnoDB 自适应哈希索引是为了提升查询效率,InnoDB 存储引擎会监控表上各个索引页的查询,当 InnoDB 注意到某些索引值访问非常频繁时,会在内存中基于 B+Tree 索引再创建一个哈希索引,使得内存中的 B+Tree 索引具备哈希索引的功能,即能够快速定值访问频繁访问的索引页。
为什么要为 B+Tree 索引页二次创建自适应哈希索引呢?这是因为 B+Tree 索引的查询效率取决于 B+Tree 的高度,在数据库系统中通常 B+Tree 的高度为 3~4 层,所以访问数据需要做 3~4 次的查询。而 Hash 索引访问通常一次查找就能定位数据(无 Hash 碰撞的情况),其等值查询场景 Hash 索引的查询效率要优于 B+Tree。
自适应哈希索引的建立使得 InnoDB 存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页建立哈希索引来加速访问。另外 InnoDB 自适应哈希索引的功能,用户只能选择开启或关闭功能,无法进行人工干涉
B+Tree 索引
对于 MySQL 存储引擎而言,其实际使用的 B+Tree 索引是为了满足数据读写性能,以及适配磁盘访问模式优化后的数据结构,每一个叶子节点都包含指向下一个叶子节点的指针。
在 MySQL 中,索引是在存储引擎层而非服务器层实现的,所以不同存储引擎层支持的索引类型可以不同。例如,虽然 MyISAM 和 InnoDB 的索引都是使用 B+Tree 实现的,但是其实际数据存储结构有不少差异。下图中 B+Tree 示例一共2层,图中每个页面都已经被随机编号(编号可以认定为页面号),其中页面号为 20 的页面是 B+Tree 的根页面(根页面通常是存放在内存中的),根页面存储了 <key+pageno>,pageno 是指向具体叶子节点的页面号。其他页面都是叶子节点,存放了具体的数据 <key+data>。
B+Tree 索引能够快速访问数据,就是因为存储引擎可以不再需要通过全表扫描来获取数据,而是从索引的根结点(通常在内存中)开始进行二分查找,根节点的槽中都存放了指向子节点的指针,存储引擎根据这些指针能够快速遍历数据。例如,通过页面号为 20 的根节点可以快速得知 Key<10 的数据在 pageno 33 的页面,key在 [10,16) 范围的数据在 pageno 56 的页面。
叶子节点存放的 <key+data> ,对于真正要存放哪些数据还得取决于该 B+Tree 是聚簇索引(Clustered Index)还是辅助索引(Secondary Index)。
聚簇索引和辅助索引
聚簇索引是一种数据存储方式,它表示表中的数据按照主键顺序存储,是索引组织表。InnoDB 的聚簇索引就是按照主键顺序构建 B+Tree,B+Tree 的叶子节点就是行记录,数据行和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身,它按主键顺序存放了整张表的数据。
而 InnoDB 辅助索引(也叫作二级索引)只是根据索引列构建 B+Tree,但在 B+Tree 的每一行都存了主键信息,加速回表操作。
聚簇索引占用的空间就是整个表数据量的大小,而二级索引会比聚簇索引小很多, 通常创建辅助索引就是为了提升查询效率。
InnoDB 只能创建一个聚簇索引(假想下如果能支持多个聚簇索引,那就意味着一张表按不同排序规则冗余存储多份全表数据了),但可以创建多个辅助索引。
在 MySQL InnoDB 中索引通常可以分为两大类:主键索引(即聚簇索引)和辅助索引(非聚簇索引) 。
对于没有指定主键的表,InnoDB 会自己选择合适字段为主键,其选择顺序如下:
1 显式主键; 2 第一个唯一索引(要求唯一索引所有列都非 NULL); 3 内置的 6 字节 ROWID。
建议使⽤ UNSIGNED 自增列显示创建主键,大家也可以考虑为什么这样建议?
根据索引列个数和功能描述不同索引也可以分为:联合索引和覆盖索引。
· 联合索引是指在多个字段联合组建索引的。 · 当通过索引即可查询到所有记录,不需要回表到聚簇索引时,这类索引也叫作覆盖索引。 · 主键查询是天然的覆盖索引,联合索引可以是覆盖索引。
那么如何看 SQL 语句是否使用到覆盖索引了呢?
通常在查看执行计划时, Extra 列为 Using index 则表示优化器使用了覆盖索引。
我们通常建议优先考虑使用覆盖索引,这是因为如果 SQL 需要查询辅助索引中不包含的数据列时,就需要先通过辅助索引查找到主键值,然后再回表通过主键查询到其他数据列(即回表查询),需要查询两次。而覆盖索引能从索引中直接获取查询需要的所有数据,从⽽避免回表进行二次查找,节省IO,效率较⾼。
例如,SELECT email,uid FROM user_email WHERE uid=xx,如果 uid 不是主键,那可以将索引添加为 index(uid,email),以获得查询性能提升。
索引使用细节
先是创建索引后如何确认 SQL 语句是否走索引了呢?创建索引后通过查看执行 SQL 语句的执行计划即可知道 SQL 语句是否走索引。执行计划重点关注跟索引相关的关键项,有 type、possible_keys、key、key_len、ref、Extra 等。
其中,possible_keys 表示查询可能使用的索引,key表示真正实际使用的索引,key_len 表示使用索引字段的长度。
执行计划中 Extra 选项也值得关注,例如 Extra 显示 use index 时就表示该索引是覆盖索引,通常性能排序的结果是 usd index > use where > use filsort。
当索引选择组合索引时,通过计算 key_len 来了解有效索引长度对索引优化也是非常重要的,接下来重点讲解 key_len 计算规则。
key_len 表示得到结果集所使用的选择索引的长度[字节数],不包括 order by,也就是说如果 order by 也使用了索引则 key_len 不计算在内。
key_len 计算规则从两个方面考虑,一方面是索引字段的数据类型,另一方面是表、字段所使用的字符集。
-
索引字段的数据类型,根据索引字段的定义可以分为变长和定长两种数据类型:
索引字段为定长数据类型,比如 char、int、datetime,需要有是否为空的标记,这个标记需要占用 1 个字节; 对于变长数据类型,比如 Varchar,除了是否为空的标记外,还需要有长度信息,需要占用 2 个字节;(备注:当字段定义为非空的时候,是否为空的标记将不占用字节)。
-
表所使用的字符集,不同的字符集计算的 key_len 不一样,例如,GBK 编码的是一个占用 2 个字节大小的字符,UTF8 编码的是一个占用 3 个字节大小的字符。 举例说明:在四类字段上创建索引后的 key_len 如何计算呢?
· Varchr(10) 变长字段且允许 NULL:10(Character Set:utf8=3,gbk=2,latin1=1)+1(标记是否为 NULL 需要 1 个字节)+ 2(变长字段存储长度信息需要 2 个字节)。 · Varchr(10) 变长字段且不允许 NULL:10(Character Set:utf8=3,gbk=2,latin1=1)+2(变长字段存储长度信息需要2个字节),非空不再需要占用字节来标记是否为空。 · Char(10) 固定字段且允许 NULL:10(Character Set:utf8=3,gbk=2,latin1=1)+1(标记是否为 NULL 需要 1 个字节)。 · Char(10) 固定字段且不允许 NULL:10(Character Set:utf8=3,gbk=2,latin1=1),非空不再需要占用字节来标记是否为空。
最左前缀匹配原则
通过 key_len 计算也帮助我们了解索引的最左前缀匹配原则。
最左前缀匹配原则是指在使用 B+Tree 联合索引进行数据检索时,MySQL 优化器会读取谓词(过滤条件)并按照联合索引字段创建顺序一直向右匹配直到遇到范围查询或非等值查询后停止匹配,此字段之后的索引列不会被使用,这时计算 key_len 可以分析出联合索引实际使用了哪些索引列。
创建一个 test 表。 在 a、b、c 上创建索引,执行表中的 SQL 语句,快速定位语句孰好孰坏。
首先分析 key_len, 因为 a、b、c 不允许 NULL 的 varchar(50),那么,每个字段的 key_len 为 50×4+2=202,整个联合索引的 key_len 为 202×3=606。
SQL1 可以使用覆盖索引,性能好; SQL2 可以使用覆盖索引同时可以避免排序,性能好; SQL3 可以使用覆盖索引,但是需要根据 where 字句进行过滤; SQL4 可以使用部分索引 a,但无法避免排序,性能差; SQL5 完全使用覆盖索引,同时可以避免排序,性能好; SQL6 可以使用覆盖索引,但无法避免排序,这是因为 MySQL InnoDB 创建索引时默认asc升序,索引无法自动倒序排序; SQL7 可以使用覆盖索引,但是需要根据 where 子句进行过滤(非定值查询)。
创建索引规范
· 命名规范, 各个公司内部统一。 · 考虑到索引维护的成本,单张表的索引数量不超过 5 个,单个索引中的字段数不超过 5 个。 ·表必需有主键,推荐使⽤ UNSIGNED 自增列作为主键。表不设置主键时 InnoDB 会默认设置隐藏的主键列,不便于表定位数据同时也会增大 MySQL 运维成本(例如主从复制效率严重受损、pt 工具无法使用或正确使用)。 · 唯一键由 3 个以下字段组成,并且在字段都是整形时,可使用唯一键作为主键。其他情况下,建议使用自增列或发号器作主键。 · 禁止冗余索引、禁止重复索引,索引维护需要成本,新增索引时优先考虑基于现有索引进行 rebuild,例如 (a,b,c)和 (a,b),后者为冗余索引可以考虑删除。重复索引也是如此,例如索引(a)和索引(a,主键ID) 两者重复,增加运维成本并占用磁盘空间,按需删除冗余索引。 · 联表查询时,JOIN 列的数据类型必须相同,并且要建⽴索引。 · 不在低基数列上建⽴索引,例如“性别”。 在低基数列上创建的索引查询相比全表扫描不一定有性能优势,特别是当存在回表成本时。 · 选择区分度(选择率)大的列建立索引。组合索引中,区分度(选择率)大的字段放在最前面。 · 对过长的 Varchar 段建立索引。建议优先考虑前缀索引,或添加 CRC32 或 MD5 伪列并建⽴索引。 · 合理创建联合索引,(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。 · 合理使用覆盖索引减少IO,避免排序。
MySQL 执行计划分析“三步曲”。
-
查看 SQL 执行计划:
explain SQL; desc 表名; show create table 表名。
-
通过 Profile 定位 QUERY 代价消耗:
set profiling=1; 执行 SQL; show profiles; 获取 Query_ID。 show profile for query Query_ID; 查看详细的 profile 信息。
-
通过 Optimizer Trace 表查看 SQL 执行计划树:
set session optimizer_trace='enabled=on'; 执行 SQL; 查询 information_schema.optimizer_trace 表,获取 SQL 查询计划树; set session optimizer_trace=‘enabled=off';开启此项影响性能,记得用后关闭。
如何优化SQL?
-
全表扫描还是索引扫描。对于小表来说,二者 IO 调用次数和返回时间相差不大;但对于大表,如果全表扫描,那么查询返回的时间就会很长,就需要使用索引扫描加快查询速度。但并不是要求 DBA 根据每一种查询条件组合都要创建索引,索引过多也会降低写入和修改的速度,而且如果导致表数据和索引数据比例失调,也不利于后期的正常维护。
-
如何创建索引,在哪些列上建立索引适合业务需求?一般情况下,你可以在选择度高的列上创建索引,也可以在 status 列上创建索引。创建索引时,要注意避免冗余索引,除非一些特殊情况外。如 index(a,b,c) 和 index(a),其中 a 的单列索引就是冗余索引。
-
创建索引以后,尽量不要过频修改。业务可以根据现有的索引情况合理使用索引,而不是每次都去修改索引。能在索引中完成的查找,就不要回表查询。比如 SELECT 某个具体字段,就有助于实现覆盖索引从而降低 IO 次数,达到优化 SQL 的目的。
-
多表关联的 SQL,在关联列上要有索引且字段类型一致,这样 MySQL 在进行嵌套循环连接查找时可以使用索引,且不会因为字段类型不匹配而发生隐式转换进而导致无法使用索引的情况发生。在现实情况中,开发经常会出现 SQL 中关联列字段类型不一致或者传入的参数类型与字段类型不匹配的情况,这样就会导致无法使用索引,在优化 SQL 时需要重点排查这种情况。另外索引列上使用函数也不会涉及索引。 多表关联时,尽量让结果集小的表作为驱动表,注意是结果集小的表,不是小表。
-
在日常中你会发现全模糊匹配的查询,由于 MySQL 的索引是 B+ 树结构,所以当查询条件为全模糊时,例如‘%**%’,索引无法使用,这时需要通过添加其他选择度高的列或者条件作为一种补充,从而加快查询速度。
当然也可以通过强制 SQL 进行全索引扫描,但这种方式不好,尽量不要在 SQL 中添加hints。对于这种全模糊匹配的场景,可以放到 ES 或者 solr 中解决。尽量不要使用子查询,对子查询产生的临时表再扫描时将无索引可查询,只能进行全表扫描,并且 MySQL 对于出现在 from 中的表无所谓顺序,对于 where 中也无所谓顺序,这也是可以优化 SQL 的地方。
-
另外 order by/group by 的 SQL 涉及排序,尽量在索引中包含排序字段,并让排序字段的排序顺序与索引列中的顺序相同,这样可以避免排序或减少排序次数。
-
除此之外,复杂查询还是简单查询?貌似总会面临这样的疑问和选择。不要总想着用一个SQL 解决所有事情,可以分步骤来进行,MySQL 也十分擅长处理短而简单的 SQL,总体耗时会更短,而且也不会产生臃肿的 SQL,让人难以理解和优化。
MySQL 自身优化 SQL
另外 MySQL 自身也对 SQL 自动进行了优化处理。MySQL 能够处理的优化类型有下面这些。
1 重新定义表的关联顺序。多表关联查询时,MySQL 日益强大的优化器会自动选择驱动表,以及表的连接顺序,基于 cost 规则极大减少 SQL 执行的时间。 2 使用等价变化规则。MySQL 可以合并或减少一些比较,还可以移除一些恒成立或恒不成立的判断。 3 优化 count()、min() 和 max()。索引和列是否可为空通常可以帮助 MySQL 优化这类表达式,如查找最小值只需找到索引树最左边的第一条记录即可。
其中常用的 SQL 编写规范如下所示。
· SELECT 只获取必要的字段,禁止使用 SELECT *。这样能减少网络带宽消耗,有效利用覆盖索引,表结构变更对程序基本无影响。 · 用 IN 代替 OR。SQL 语句中 IN 包含的值不宜过多,应少于 1000 个。过多会使随机 IO 增大,影响性能。 · 禁止使用 order by rand()。order by rand() 会为表增加几个伪列,然后用 rand() 函数为每一行数据计算 rand() 值,最后基于该行排序,这通常都会生成磁盘上的临时表,因此效率非常低。建议先使用 rand() 函数获得随机的主键值,然后通过主键获取数据。 · SQL 中避免出现 now()、rand()、sysdate()、current_user() 等不确定结果的函数。在语句级复制场景下,引起主从数据不一致;不确定值的函数,产生的 SQL 语句无法使用 QUERY CACHE。 · 重要SQL必须被索引:update、delete 的 where 条件列、order by、group by、distinct 字段、多表 join 字段。 · 禁止使用 % 前导查询,例如:like “%abc”,⽆法利⽤到索引。 · 禁止使⽤负向查询,例如:not in、!=、<>、not like。 · 使⽤ EXPLAIN 判断 SQL 语句是否合理使用索引,尽量避免 extra 列出现:Using File Sort、Using Temporary 等。 · 减少与数据库交互次数,尽量采用批量 SQL 语句。 · 获取⼤量数据时,建议分批次获取数据,每次获取数据少于 5000 条,结果集应⼩于 1M。 · 拆分复杂 SQL 为多个 小SQL,避免⼤事务。简单的 SQL 容易使用到 MySQL 的 QUERY CACHE;减少锁表时间特别是 MyISAM;可以使用多核 CPU。
Redo Log 第一个参数是控制 Redo Log 刷盘策略的 innodb_flush_log_at_trx_commit,它有三个取值策略,如下图所示。
· 当取值为 0 ,表示事务提交时,MySQL 不会去处理日志缓存区(Log Buffer)的内容,也不会去处理日志文件的刷盘操作,由 MySQL 的后台 Master 线程每隔 1s 将缓存区的文件刷新到日志文件中。 · 当取值为 1 ,表示事务提交时,会将日志缓冲区的日志写入文件中,同时会刷新到磁盘中,保证数据库事务完全不会丢失。这种设置影响数据库性能。 · 当取值为 2,表示事务提交时,会将日志缓存区日志写入到文件中,但是不会刷新到磁盘中。由 MySQL 的后台 Master 线程每隔 1s 将系统缓存的日志文件刷新到磁盘中。
如下图,可以看到其不同取值时对应与日志缓冲区、OS cache、日志文件(ib_logfile)之间的关系。
Binlog
第二个参数是控制 Binlog 刷盘策略的 sync_binlog,其取值分为 0、1、N(N>1)三类,如下图。
1 当取值为 0 时,事务提交时,MySQL 将 Binlog 信息写入 Binlog 文件(OS Cache)中,但是 MySQL不控制 Binlog 的刷磁盘操作,由文件系统自己控制其缓存的刷新。这是最危险的,一旦操作系统宕机,在 Binlog cache 中的所有 Binlog 都会丢失。如果只是数据库宕机,而操作系统未宕机,那么数据库所生成的 Binlog 都不会丢失。 2 当取值为 1 时,每一个事务提交时,MySQL 都会把 Binlog 刷新到磁盘中。这样,数据库安全性最高,但是性能损耗也是最大的。如果这样设置的话,在数据库或操作系统宕机的情况下,二进制日志中缺少的任何事务也只能处于准备阶段,那么导致服务器自动恢复时,会回滚这些事务,保证无数据丢失。虽然 Binlog 是顺序 IO,但是多个事务同时提交,同样会对 MySQL 和 IO 的性能带来很大影响,不过 MySQL 可以通过 Group Commit 来缓解这种压力。 3 当取值为 N 时,表示每 N 次事务提交,MySQL 调用文件系统的刷新操作将缓存刷新到磁盘中。如果数据库或操作系统在这个时候宕机,数据库可能会丢失一些事务。
Replication
下图是 MySQL 复制的基本原理图,它描述了 Replication 的过程。
如图,它整体分为三个步骤:
· 主库 Master 将数据库的变更操作记录在二进制日志 Binary Log 中。 · 备库 Slave 读取主库上的日志并写入到本地中继日志 Relay Log 中。 · 备库读取中继日志 Relay Log 中的 Event 事件在备库上进行重放 Replay。
整个过程涉及三个 Thread,分别是 Master 的 Binlog Dump Thred,和 Slave 的 I/O Thread 和 SQL Thread。
其具体参与主从复制的数据同步过程如下。
· Master 服务器上对数据库的变更操作记录在 Binlog 中。 · Master 的 Binlog Dump Thread 接到写入请求后读取 Binlog 推送给 Slave I/O Thread。 · Slave I/O Thread 将读取的 Binlog 写入到本地 relay log 文件。 · Slave SQL thread 检测到 relay log 的变更请求,解析 relay log 并在从库上进行应用。
以上整个复制过程都是异步操作,所以主从复制俗称异步复制,存在数据延迟。
Master 数据变更后记录 Binlog,只是通知 Binlog Dump Thread 有数据写入后就告诉存储引擎提交事务,并不会关注 Slave 是否接受并落地 Binlog Event。下图是异步复制的时序图。
考虑到一个场景,主库正常写入数据并提交事务 T1,但是 Slave1 和 Slave2 由于某种原因(例如网络原因)一直无法接受到 Binlog Dump Thread Event 的推送请求,如果这时候 Master Crash,Slave 提升为 Master 后导致事务 T1 数据丢失。为了提升数据安全,MySQL 让 Master 在某一个时间点等待 Slave 节点的 ACK 消息后才进行事务提交,这也是半同步复制的基础。
MySQL 从 5.5 版本开始引入了半同步复制机制来降低数据丢失的概率,为什么是降低呢?原因除了 MySQL 半同步复制会退化成异步复制之外,还需要讲下半同步复制after commit 和增强半同步 after-sync 的实现。
介绍半同步复制之前先快速过一下 MySQL 事务写入碰到主从复制时的完整过程,主库事务写入分为 4 个步骤:
InnoDB Redo File Write (Prepare Write); Binlog File Flush & Sync to Binlog File; InnoDB Redo File Commit(Commit Write); Send Binlog to Slave。
当 Master 不需要关注 Slave 是否接受到 Binlog Event 时,即为传统的主从复制。 当 Master 需要在第三步等待 Slave 返回 ACK 时,即为 after-commit。 当 Master 需要在第二步等待 Slave 返回 ACK 时,即为 after-sync。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性