数据库事务

事务的四个特性（ACID）

一般来说，衡量事务必须满足四个特性：ACID，即 原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • 原子性（Atomicity）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable），下面会详细说明。
  • 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，会持久化到硬盘上，即便系统故障也不会丢失。

事务隔离级别

事务隔离级别是数据库系统中用于控制并发事务之间相互影响的一种机制，以确保数据的一致性和可靠性。SQL标准定义了四种事务隔离级别，分别是：读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。这些级别通过控制事务对数据的访问和修改，来避免并发访问时可能出现的几种问题：脏读、不可重复读和幻读。

  • 读未提交（Read Uncommitted）：这是最低的事务隔离级别，允许一个事务读取另一个事务未提交的数据。这种隔离级别下，并发性最高，但由于可能读取到未提交的数据，可能导致脏读问题。脏读是指一个事务读取到了另一个事务未提交的数据，如果另一个事务后来回滚，那么之前读取的数据就是无效的。
  • 读已提交（Read Committed） ：在这个隔离级别下，一个事务只能读取已经提交的数据，避免了脏读问题。但是，由于其他事务可能在此期间修改数据并提交，同一个事务在不同时间读取同一数据可能会得到不同的结果，导致不可重复读问题。
  • 可重复读（Repeatable Read） ：这是MySQL的默认隔离级别。在这个级别下，事务在执行期间多次读取同一数据总是得到相同的结果，避免了不可重复读问题。然而，它仍然允许其他事务插入新数据，可能导致幻读问题。
  • 串行化（Serializable） ：这是最高的事务隔离级别，通过强制事务串行执行来避免脏读、不可重复读和幻读问题。但这种级别的并发性能最低，因为每个事务都需要等待前一个事务完成。

脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。

不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。通俗点就是已提交事务B对事务A产生的影响，导致B执行有误，这个影响叫做“幻读”。幻读和不可重复读都是指的一个事务范围内的操作受到其他事务的影响了。只不过幻读是重点在插入和删除，不可重复读重点在修改。

  • 不可重复读是读取了其他事务更改的数据，针对update操作。 解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。
  • 幻读是读取了其他事务增删的数据，针对insert与delete操作。解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务增删数据。

image-20240831155832564

参考：

https://baijiahao.baidu.com/s?id=1782906740878129621

存储引擎

MySQL 存储引擎采用的是插件式架构，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

可插拔存储引擎的 MySQL 架构：

image-20241010143151059

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

MyISAM 的性能还行，各种特性也还不错（比如全文索引、压缩、空间函数等）。但是，MyISAM 不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。

InnoDB 使用缓冲池（Buffer Pool）缓存数据页和索引页，MyISAM 使用键缓存（Key Cache）仅缓存索引页而不缓存数据页。这就导致只要内存足够大，MySQL大量索引和数据其实都是在内存中的。

不要轻易相信“MyISAM 比 InnoDB 快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB 的速度都可以让 MyISAM 望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用。

总结：

  • InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度
  • MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别
  • MyISAM 不支持外键，而 InnoDB 支持
  • MyISAM 不支持 MVCC，而 InnoDB 支持
  • 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样
  • MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持
  • InnoDB 的性能比 MyISAM 更强大

索引

在mysql中索引类型包括这几种B + Tree索引、hash索引、全文索引、空间索引。

其中B+Tree索引是默认索引类型。且B + Tree（平衡树）索引大致分为两类聚簇索引和非聚簇索引（指MyISM的非聚簇索引）。

聚簇索引（Clustered Index）

  • a. InnoDB引擎要求必须有聚簇索引。索引采用B + Tree索引结构实现。聚簇索引是按照表主键顺序构建一个B+Tree结构。其叶子节点存储了是行数据（包含主键值）
  • b. 叶子节点中行数据和主键值紧凑的存储在一起，按照主键顺序存储整张表的数据，占用的空间就是整张表的大小
  • c. 聚簇索引是通过主键聚集数据，若定义了主键，则主键索引就为聚簇索引。若没定义主键，则表中第一个非空唯一的列作为聚簇索引。都不满足时，InnoDB会建一个隐藏列row-id作为聚簇索引。所以InnoDB引擎的表要求必须有聚簇索引（主键索引）
  • d. 二级索引（辅助索引或非主键索引）是在聚簇索引之上创建。是根据索引列构建的 B+Tree结构，在其叶子节点只存储索引列对应的数据值和主键值。二级索引占用的空间比聚簇索引小很多，通常创建二级索引就是为了提高查询效率。一个InnoDB表只能创建一个聚簇索引，可以创建多个二级索引。当索引覆盖扫描时可以直接从B+Tree中获取这些值，不会通过主键查询主键索引（回表查询）获取数据

非聚簇索引（Secondary Index 或 Non-clustered Index）

这里介绍的非聚簇索引是指MyISM引擎中的非聚簇索引，不是InnoDB引擎中的非聚簇索引（InnoDB二级索引也为非聚簇索引）。

  • a. MyISM采用的是非聚簇索引，其索引文件结构为B+Tree结构。索引文件和数据文件是分离的。索引文件存储B+Tree结构，数据文件存储表中的数据行
  • b. 索引文件是按照索引键值和表数据内存地址构建的B+Tree，其结构的叶子节点存储了索引列的值和指向数据文件中记录的物理位置（通常磁盘地址）的指针
  • c. 每个索引对应一个B+Tree结构的索引文件，索引文件是独立的。通过辅助索引检索时，无需访问主键索引树
  • d. 执行查询时，会利用非聚簇索引中的索引列值对B+Tree从根节点逐层查找，找到叶子节点。从叶子节点中获取记录的物理位置（磁盘地址）找到数据文件，从数据文件中获取响应的记录。当索引覆盖扫描时，可以直接从索引文件中返回这些值，无需再访问数据文件

说白了最大区别是B+树叶子节点直接记录数据和只记录数据地址的差别。

InnoDB性能优化

transaction_isolation

解读：事务隔离级别，Oracle, SQL Server等商业知名数据库默认级别为READ-COMMITTED，而MySQL默认为REPEATABLE-READ，它利用自身独有的Gap Lock解决了"幻读"。但也因为Gap Lock的缘故，相比于READ-COMMITTED级别的Record Lock，REPEATABLE-READ的事务并发插入性能受到很大的限制。

设置：隔离级别的选择取决于实际的业务需求（安全与性能的权衡），如果不是金融、电信等事务级别要求很高的业务，完全可以设置成transaction_isolation=READ-COMMITTED。

innodb_buffer_pool_size

解读：InnoDB缓冲池大小，它决定了MySQL可以在内存中缓存多少数据和索引，而不是每次都从磁盘上读取。我们都知道Redis的读写很快，其最重要的原因是它的所有数据都缓存在内存中。试想一下如果innodb_buffer_pool_size足够大，MySQL所有表数据和索引都能被缓存，那将是一种什么体验？

设置：如果是专用的MySQL服务器，一般设置为操作系统内存的75%左右，但至少保留2G内存用于操作系统维护和MySQL异常事件处理。

innodb_buffer_pool_instances

解读：InnoDB缓冲池实例个数，InnoDB缓冲池是通过一整个链表的方式来管理页面（段、簇、页）的，由于互斥锁的存在（保护链表中的页面），高并发事务下，页面的读取和写入就需要锁的竞争和等待。通过设置innodb_buffer_pool_instances，将一整个链表划分为多个，每个缓冲池实例管理自己的页面和互斥，从而提高效率。

设置：如果缓冲池比较大（8G以上），可以按照innodb_buffer_pool_size / innodb_buffer_pool_instances = 1G进行设置，但如果缓冲池特别大（32G以上），可以按照每个实例2~3G进行划分，实例数不是越多越好，多实例代表多线程，线程的开销（CPU、MEM）也得考虑。

innodb_log_file_size

解读：InnoDB日志文件大小（Redo Log），它将事务对InnoDB表的修改记录保存在ib_logfile0、ib_logfile1中。innodb_log_file_size越大，缓冲池中的脏数据需要检查点（checkpoint）进行刷盘的频率就越少，从而减少磁盘IO来降低高并发负载造成的峰值。但日志文件也不是越大约好，由于内存中脏数据刷盘的频率减少，一旦数据库发生异常崩溃，数据库重启时从innodb_log_file中读取数据进行恢复的时间越长。

设置：一般选取业务高峰期一个小时的日志量作为标准，计算过程如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

# 命令
$ mysql -uuser -p -e 'show engine innodb status\G'|grep 'Log sequence number' \
&& sleep 60 \
&& mysql -uuser -p -e 'show engine innodb status\G'|grep 'Log sequence number'

# 输出
Log sequence number 149949388055
Log sequence number 149959622102

# 计算
( 149959622102 - 149949388055 ) / 1024 / 1024 = 10M
10 / 60 * 3600 = 600M
600 / 2 = 300M

# 解释
Log sequence number代表InnoDB运行至今写入日志的总字节数，两次打印之间线程休眠60秒
得到一分钟之内事务日志记录的总量10M，再转换成一个小时的总量600M
因为`ib_logfile0、ib_logfile1`两个文件循环写入，一个文件为300M
最终，innodb_log_file_size=300M

innodb_flush_log_at_trx_commit

解读：InnoDB事务日志刷盘时机

  • 当0时，事务提交到日志缓冲区，后台Write线程每隔一秒将缓冲区的日志写入系统缓冲区，实际写入物理日志文件的时机取决于操作系统。
  • 当1时，事务提交到日志缓冲区，Master线程同步将缓冲区的日志直接写入物理日志文件，这完全符合InnoDB ACID事务标准，数据不会丢失。
  • 当2时，事务提交到系统缓冲区，Master线程每隔一秒将系统缓冲区的日志写入物理日志文件。

设置：安全1 > 2 > 0，速度0 > 2 > 1，根据实际业务需求（安全与速度权衡）选择合理的刷盘时机。

sync_binlog

解读：二进制事务日志刷盘时机，需要配合log-bin选项才能记录二进制日志。区别于InnoDB事务日志，二进制事务日志是针对整个MySQL Server的，而InnoDB事务日志只针对InnoDB存储引擎。二进制事务日志的作用一是用于主从复制，二是用于数据恢复。区别于InnoDB事务日志恢复，二进制事务日志是用于误操作的数据恢复，而InnoDB事务日志是用于InnoDB存储引擎的崩溃恢复。

  • 当0时，将由操作系统控制binlog_cache的刷盘时机。

  • 当1时，所有事务开始、提交阶段，都会同步写入磁盘，这是最安全的方式。如果设置innodb_flush_log_at_trx_commit = 1, sync_binlog = 1，这是使用InnoDB事务最安全可靠的方式。

  • 当N时，事务每提交N次，同步写入一次二进制日志。设置：如果MySQL是单机，可以考虑sync_binlog=0；如果是主从，且每秒事务并发量低，考虑sync_binlog=1；事务并发量很高，考虑sync_binlog=N，N的选取可以通过统计业务正常时期的OPS。

image-20220111154059081

innodb_file_per_table

解读：InnoDB独立表空间，innodb_file_per_table = ON表示每张表在独立的物理文件中（.ibd）存储数据和索引，innodb_file_per_table = OFF表示所有表都共享表空间即一个物理文件（ibdata1）。如果通过drop/truncate table操作，独立表空间的物理存储会立即被回收（删除/初始化），而共享表空间不会被回收且只会一直增大。

设置：innodb_file_per_table = ON，但需要注意的是，独立表空间只存储数据和索引，如回滚日志缓冲（Undo Log）、插入索引缓冲（Insert Buffer）、二次写缓冲（Doublewrite Buffer）等还是放在共享表空间。

query_cache_size

解读：查询缓存大小，它是为了在追踪表的数据未发生变化时，本次查询命中之前的查询语句，从而跳过解析、优化、执行阶段，直接返回缓冲池中的数据。但实际在OLTP系统中，极少能命中查询缓存（前提是数据库中的数据变化频率很小），因为一旦数据有变则缓存失效。且因为查询缓存会跟踪所有表的变化，它也会成为整个数据库的瓶颈（资源竞争点）。

设置：query_cache_size = 0，同时配合设置query_cache_type = 0，MySQL5.7.20以上、MySQL8.0会直接弃用所有查询缓存配置项。

max_connections

解读：最大连接数，当max_connections设置太小时（默认151），MySQL可能会报错Too many connections。当max_connections设置太大时（1000以上），操作系统可能忙于线程间的切换而失去响应。

设置：每个连接都会消耗一定内存，计算过程如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 计算每个线程平均消耗的内存
SELECT CONCAT(ROUND(SUM(VARIABLE_VALUE)/(1024*1024)),'M') AS 'per_connection' 
       FROM performance_schema.global_variables 
       WHERE VARIABLE_NAME IN ('read_buffer_size', 'read_rnd_buffer_size', 
       'sort_buffer_size', 'join_buffer_size', 'thread_stack', 
       'max_allowed_packet', 'net_buffer_length');  
       
# 当前连接数量
mysql> SHOW STATUS LIKE 'threads_connected'; 

# 内存
$ top -c|grep mysqld
PID    USER   PR  NI VIRT     RES    SHR  S  %CPU  %MEM TIME+     COMMAND
24411  mysql  20  0  8327348  6.458g 7680 S  36.7  85.4 831:56.14 /usr/sbin/mysqld

thread_cache_size

解读：线程缓存大小，客户端连接时，MySQL会为每个连接分配一个线程，通过设置thread_cache_size = N，MySQL可以重复利用保存在缓存中的N个线程，从而改善频繁的线程创建销毁的开销。同时，应用如果有连接池复用设置，那也会改善MySQL Server的线程开销。

设置：可以通过监控SHOW STATUS LIKE ‘threads_connected’的数量，确定每天的平均连接数。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

mysql> show status like '%thread%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Delayed_insert_threads                   | 0     |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
| Slow_launch_threads                      | 0     |
| Threads_cached                           | 2     |
| Threads_connected                        | 216   |
| Threads_created                          | 2076  |
| Threads_running                          | 3     |
+------------------------------------------+-------+
# 如果Threads_created很大，可以考虑加大thread_cache_size值

mysql> show variables like '%thread%';             
+-----------------------------------------+---------------------------+
| Variable_name                           | Value                     |
+-----------------------------------------+---------------------------+
| innodb_purge_threads                    | 1                         |
| innodb_read_io_threads                  | 4                         |
| innodb_thread_concurrency               | 0                         |
| innodb_thread_sleep_delay               | 10000                     |
| innodb_write_io_threads                 | 4                         |
| max_delayed_threads                     | 20                        |
| max_insert_delayed_threads              | 20                        |
| myisam_repair_threads                   | 1                         |
| performance_schema_max_thread_classes   | 50                        |
| performance_schema_max_thread_instances | -1                        |
| pseudo_thread_id                        | 17534                     |
| thread_cache_size                       | 13                        |
| thread_concurrency                      | 10                        |
| thread_handling                         | one-thread-per-connection |
| thread_stack                            | 262144                    |
+-----------------------------------------+---------------------------+

my.cnf参考配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

# /etc/my.cnf for mysql 5.6.x
# add by cd.net on 2022-01-11
# ---------------------------------
[mysqld]

datadir = /home/dbcenter/mysql-server

# 最好加上这个，否则连接会巨慢无比，大量超时待认证的连接
skip_name_resolve
skip_host_cache
# 连接数
max_connections = 500
max_connect_errors = 500
# 线程缓存池大小
thread_cache_size = 100

# 数据+索引 缓存大小
innodb_buffer_pool_size = 32G
join_buffer_size = 6G
sort_buffer_size = 4G
read_rnd_buffer_size = 4G

# 允许接收的数据包最大值，比如这么大的sql语句
max_allowed_packet = 128M

# 查询缓存，都关闭
query_cache_size = 0
query_cache_type = 0

# query slow
long_query_time = 1
slow-query-log = 1
slow-query-log-file = /home/dbcenter/mysql-server/query-slow.log

# replication +++++++++++++++++++++++++++++++++++++
# 不同服务器的server-id一定不能一样
server-id = 1158

# 开启binlog日志
log_bin = mysql-bin
binlog_format = mixed

# 这里的参数根据需要调节，控制MySQL高可用安全级别，根据业务特定在性能和数据一致性方面做决策
# InnoDB在复制中最大的持久性和一致性，[每次commit都 write cache & flush disk]
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
# 事务日志文件大小
innodb_log_file_size = 512M

# 忽略几个mysql自带的DB变动日志
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
# 同时也不复制这些表
replicate_wild_ignore_table = mysql.%
replicate_wild_ignore_table = information_schema.%
replicate_wild_ignore_table = performance_schema.%

# mysql 8.x 以上不能有下面这句
innodb_file_format = Barracuda
innodb_file_per_table = ON

performance_schema = OFF
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log

!includedir /etc/my.cnf.d

（完）