mysql

数据库事务的四大特性(ACID)

原子性

• 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。因此事务的操作如果成功就必须完全应用到数据库，如果操作失败不能对数据库有任何影响。

一致性

• 一致性要求事务执行前后数据库的状态保持一致。事务执行过程中可能涉及多个操作，这些操作的结果必须满足数据库的约束和规则。

隔离性

• 隔离性是指当多个用户并发访问数据库时，比如操作同一张表时，数据库为每个用户开启的事务，不能被其他事务的操作干扰，多个并发事务要互相隔离。

持久性

• 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变是永久的，即便是在数据库系统中遇到故障的情况下也不会丢失提交事务的操作。

数据库事务的隔离机制

读未提交（READ UNCOMMITTED）

• 定义：最低的隔离级别，一个事务在执行过程中可以读取到其他事务未提交的数据，即允许读取 “脏数据”。
• 优点：并发性能最高，因为事务之间几乎没有相互阻塞，读取操作不会加锁。
• 缺点：会产生脏读问题，即一个事务读取到了另一个事务尚未提交的数据，如果另一个事务最终回滚，那么读取到的数据就是无效的，会导致数据不一致。

读已提交（READ COMMITTED）

• 定义：一个事务只能读取到其他事务已经提交的数据，避免了脏读问题。在该隔离级别下，每次读取操作都会生成一个新的快照。
• 优点：解决了脏读问题，数据的一致性相对较高。
• 缺点：可能会出现不可重复读问题，即同一个事务中多次读取同一数据时，由于其他事务对该数据进行了修改并提交，导致每次读取的结果不一致。

可重复读（REPEATABLE READ）

• 定义：这是 MySQL 的默认隔离级别，一个事务在执行过程中，多次读取同一数据时，结果始终保持一致，避免了不可重复读问题。在该隔离级别下，事务在启动时会生成一个快照，在整个事务期间都会使用这个快照进行数据读取。
• 优点：解决了脏读和不可重复读问题，保证了事务内数据的一致性。
• 缺点：可能会出现幻读问题，即一个事务在执行过程中，按照某个条件查询数据，在多次查询之间，其他事务插入了满足该条件的新数据，导致该事务再次查询时发现结果集多了一些记录。不过，InnoDB 存储引擎通过间隙锁等机制在一定程度上解决了幻读问题。

串行化（SERIALIZABLE）

• 定义：这是最高的隔离级别，所有事务串行执行，即一个事务执行完成后，另一个事务才能开始执行。在该隔离级别下，读操作会加共享锁，写操作会加排他锁，避免了所有的数据不一致问题。
• 优点：提供了最高的数据一致性，不会出现脏读、不可重复读和幻读问题。
• 缺点：并发性能最低，因为事务之间会相互阻塞，可能会导致大量的等待时间，降低系统的吞吐量。

数据库事务的隔离机制实现原理

事务的隔离机制是通过锁机制和多版本并发控制(MVCC)实现的.

锁机制（按模式区分）

1. 共享锁(S锁，读锁)

• 特点：多个事务可以同时对同一数据对象加共享锁。加共享锁的事务只能读取数据，不能修改数据。
• 原理：当一个事务对数据对象加上共享锁后，其他事务也可以对该数据对象加共享锁进行读取操作，但如果有事务想要对该数据对象加排他锁进行修改操作，则必须等待所有共享锁释放。例如，事务 T1 对表中的某一行数据加了共享锁进行读取，此时事务 T2 也可以对这一行数据加共享锁进行读取，但事务 T3 如果想要对这一行数据加排他锁进行修改，就需要等待 T1 和 T2 的共享锁释放。
• 应用场景：适用于读多写少，因为如果出现大量的写操作，写冲突的可能性就会增大，业务层需要不断重试，会大大降低系统性能。

2. 排他锁(X锁，写锁)

• 特点：一个数据对象上只能有一个排他锁。加排他锁的事务既可以读取数据，也可以修改数据。
• 原理：当一个事务对数据对象加上排他锁后，其他事务不能再对该数据对象加任何类型的锁，直到排他锁被释放。这样可以保证在事务对数据进行修改时，不会有其他事务同时对该数据进行读取或修改操作，从而避免数据不一致的问题。例如，事务 T1 对表中的某一行数据加了排他锁进行修改，此时事务 T2 想要读取或修改这一行数据，就必须等待 T1 的排他锁释放。
• 应用场景：适用于并发量不大、写入操作比较频繁、数据一致性比较高的场景。

3. 意向锁

• 特点：意向锁是一种表级别的锁，用于表示事务对表中某些行或页有某种类型的锁（共享锁或排他锁）。意向锁分为意向共享锁（IS 锁）和意向排他锁（IX 锁）。
• 原理：意向锁的引入是为了在加表级锁时能够快速判断表中是否有行级锁存在，从而提高加锁的效率。例如，当一个事务想要对整个表加排他锁时，如果表中已经有行加了共享锁，那么加表级排他锁的操作会被阻塞。通过意向共享锁，数据库可以快速知道表中是否有行加了共享锁，避免了对表中每一行进行扫描。

锁机制（按粒度区分）

1. 全局锁

(1) 概念：

全局锁就是对整个数据库实例加锁。

(2) 应用场景：

全库逻辑备份（mysqldump）

(3) 实现方式：

MySQL 提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)。

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

(4) 缺点：

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就能停止。

如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

(5) 优化方法：

mysqldump使用参数--single-transaction，启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

2. 表级锁

(1) 概念：

当前操作的整张表加锁，最常使用的 MyISAM 与 InnoDB 都支持表级锁定。

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

(2) 实现方式：

lock tables … read/write；

lock tables t1 read， 读锁，当前会话可以对 t1 进行读操作，同时其他会话也可以对 t1 加读锁并进行读操作。读锁是共享锁，加了读锁的表在锁定期间， 任何会话（包括当前会话）都不能对 t1 进行写操作（如 INSERT、UPDATE、DELETE 等）。如果其他会话尝试对 t1 加写锁，会被阻塞，直到读锁被释放。

lock tables t2 write, 写锁，当前会话可以对 t2 进行读和写操作，其他会话在表 t2 被锁定期间，既不能对 t2 加读锁也不能加写锁，所有对 t2 的读写操作都会被阻塞， 直到写锁被释放。写锁是排他锁，它保证了在同一时间只有一个会话可以对表进行读写操作，从而避免了数据冲突。

(3) 缺点：

并发性能问题，当一个事务对表加上读锁时，其他事务可以对该表加读锁，但不能加写锁；当一个事务对表加上写锁时，其他事务既不能加读锁也不能加写锁。这就导致在高并发场景下，读写操作会相互阻塞。 当有一个事务对商品表加写锁进行库存更新时，其他事务无法同时对该表进行读操作来展示商品信息，从而降低了系统的并发处理能力。

死锁问题，虽然表级锁相对于行级锁来说，死锁的概率较低，但在某些复杂的业务场景下仍然可能发生死锁。例如，有两个事务 t1 和 t2，t1 先对表 A 加写锁，然后尝试对表 B 加写锁；而 t2 先对表 B 加写锁， 然后尝试对表 A 加写锁。此时，如果两个事务都不释放已经持有的锁，就会形成死锁，导致两个事务都无法继续执行。

元数据锁：MDL 不需要显式使用，在访问一个表的时候会被自动加上，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

(4) 优化方法：

降低锁粒度，尽量使用行级锁或页级锁代替表级锁，如果必须要用表锁需要优化查询和事务，减少锁持有时间

针对MDL锁问题，如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。这也是为什么需要在低峰期做ddl 变更。

3. 页级锁

(1) 概念：

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。基本没有使用场景。

4.行级锁

(1) 概念：

行级锁是粒度最低的锁，发生锁冲突的概率也最低、并发度最高。但是加锁慢、开销大，容易发生死锁现象。行级锁分为共享锁和排他锁。

(2) 实现方式：

在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。 在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

(3) 缺点：

锁开销大：行级锁的加锁和解锁操作需要消耗一定的系统资源，尤其是在高并发场景下，频繁的加锁和解锁操作会增加系统的开销，影响数据库的性能。

死锁风险：由于行级锁的粒度较小，多个事务可能会同时对不同的行加锁，从而形成死锁。例如，事务 A 对行 1 加锁并尝试对行 2 加锁，而事务 B 对行 2 加锁并尝试对行 1 加锁，此时就会形成死锁。

实现复杂：使用行级锁需要考虑索引的使用、事务的管理等因素，实现起来相对复杂。如果使用不当，可能会导致锁的范围扩大，甚至退化为表级锁。

(4) 优化方法：

减少锁持有时间：尽量缩短事务的执行时间，减少锁的持有时间。将不必要的操作放在事务之外执行，只将必须保证原子性的操作放在事务内。

优化查询语句：确保查询语句使用了合适的索引，避免全表扫描。全表扫描会导致行级锁的范围扩大，甚至可能退化为表级锁。

预防死锁： 按相同顺序访问资源：在多个事务中，让它们按照相同的顺序访问行，避免循环等待。设置事务的锁等待超时时间，当事务等待锁的时间超过这个阈值时，自动回滚事务。可以通过设置 innodb_lock_wait_timeout 参数来实现。

简化事务逻辑：尽量简化事务的逻辑，减少事务中涉及的操作和锁的数量。可以将一个大事务拆分成多个小事务，降低死锁的发生概率。

MVCC

MVCC 是一种在不使用锁的情况下实现事务隔离性的技术，它通过为数据的每个版本创建一个快照，使得不同事务可以同时访问数据的不同版本，从而提高并发性能。

实现原理：

• 版本号：每个事务在启动时会被分配一个唯一的事务 ID（版本号），数据库会为每一行数据记录多个版本，每个版本都关联一个创建它的事务 ID 和一个删除它的事务 ID。
• 读取规则：当一个事务读取数据时，它只会读取创建版本号小于等于当前事务 ID，并且删除版本号大于当前事务 ID 或者没有删除版本号的数据版本。这样可以保证事务读取到的数据是在它启动之前已经提交的数据版本，避免了读取到未提交的数据。

mysql锁升级

在可重复读隔离级别下，SELECT ... FOR UPDATE 如果查询语句使用了索引（包括主键索引、唯一索引、普通索引等），通常会加行级锁，也就是只锁定满足查询条件的行。 如果查询语句没有使用索引，MySQL 可能会进行全表扫描，此时 SELECT ... FOR UPDATE 会将锁升级为表级锁，会锁定整个表。

优化慢sql

1. 在my.cnf中添加配置：

[mysqld]
slow_query_log = ON      # 启用慢查询日志 
long_query_time = 2      # 定义执行时间超过2秒的语句为慢查询
slow_query_log_file = /var/log/mysql/slow.log   # 定义慢查询日志记录文件
log_queries_not_using_indexes = ON      # 记录未使用索引的查询语句

2. 使用慢SQL分析工具(Percona Toolkit)

# 安装
sudo yum install percona-toolkit
# 分析，该命令将从指定的慢查询日志文件中读取内容，并生成一个分析文件analysis.txt。通过分析文件，我们可以很容易地找出哪些SQL语句执行较慢，以及导致这种情况的原因。
pt-query-digest /path/to/slow/query/log > analysis.txt

mysql内网访问配置

#连接数据库
mysql -u root -p
use mysql;
# 生产环境不建议%
update user set host = '%' where user ='root'; 
select host, user from user;
flush privileges;

索引