MySQL从入门到高级 --- 12.事务 && 13.锁机制 && 14.日志

文章目录

• • 第十二章 && 第十三章 && 第十四章：
  • • 12.事务
    • • 12.1 特性
      • 12.2 隔离级别
    • 13.锁机制
    • • 13.1 各存储引擎对锁的支持状况：
      • 13.2 锁特性
      • 13.3 MyISAM表锁
      • • 13.3.1 加表锁
      • 13.4 InnoDB行锁
      • • 13.4.1 行锁特点
        • 13.4.2 行锁模式
    • 14.日志
    • • 14.1 错误日志
      • 14.2 二进制日志
      • • 14.2.1 日志格式
      • 14.3 查询日志
      • 14.4 慢查询日志

第十二章 && 第十三章 && 第十四章：

12.事务

• 可用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么都不执行

• 事务用来管理DDL 、DML 、DCL操作，如insert、update、delete语句，默认自动提交

• MySQL中事务由存储引擎实现，在MySQL只有InnoDB存储引擎才支持事务

操作：

1. 开启事务：BEGIN或START TRANSACTION，任何一条DML语句执行，标志着事务的开启

2. 提交事务：COMMIT TRANSACTION，成功的结束，将所有DML语句操作历史记录和底层硬盘数据来一次同步

3. 回滚事务：ROLLBACK TRANSACTION，失败的结束，将所有DML语句操作历史记录全部清空

改变MySQL自动提交模式

语法：set autocommit=0 – 禁止自动提交

        set autocommit=1 -- 开启自动提交

12.1 特性

• 原子性

• 一致性

• 隔离性

• 持久性

12.2 隔离级别

隔离：将事务与另一个事务隔离开

作用：一个事务正在操作的数据被另一事务修改或删除，最后执行结果可能无法达到预期。如果没有隔离性还会导致其他的问题。

• 读未提交(read uncommitted)：一个事务可以读取到另一个未提交事务的数据，最低级别，任何情况都无法保证，会造成脏读。

• 读已提交(committed)：一个事务要等另一个事务提交后才能读取数据，可避免到脏读的发生，会造成不可重复读。

• 可重复读(repeatable read)：在开始读取数据时，不再允许修改操作，可避免脏读、不可重复值的发生，会造成幻读。

• 串行化(erializable)：最高的事务隔离级别，在该级别下，事务串行化顺序执行，可避免脏读、不可重复读和幻读。但该隔离级别效率低，耗数据库性能，一般不用。

MySQL默认隔离级别是可重复读。

13.锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制

锁冲突也是影响数据库并发访问性能的一个重要因素

按数据操作的粒度分类：

• 表锁：操作时，会锁定整个表

• 行锁：操作时，会锁定当前操作行

按数据操作的类型分类：

读锁 (共享锁) ：针对同一份数据，多个读操作可同时进行而不会互相影响

写锁 (排它锁) ：当前操作没完成前，它会阻断其他写锁和读锁

13.1 各存储引擎对锁的支持状况：

13.2 锁特性

• 表级锁适用于以查询为主，只有少量按索引条件更新数据的应用

• 行级锁适用于有大量按索引条件并发跟新少量不同数据，同时又有并查询的应用

13.3 MyISAM表锁

13.3.1 加表锁

• MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁

lock table table_name read;  -- 加读锁

lock table table_name write; -- 加写锁

13.4 InnoDB行锁

13.4.1 行锁特点

• 偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高

InnoDB与MyISAM最大不同的两点：

1. 支持事务

2. 采用行级锁

两种类型行锁：

• 共享锁 (S) ：又称读锁，简称S锁，共享锁就是多个事务对于同一数据可共享一把锁，都能访问数据，但只能读不能修改

• 排他锁 (X) ：又称写锁，简称X锁，排他锁就是不能与其他锁并存

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁

13.4.2 行锁模式

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE -- 共享
SELECT * FROM table_name WHERE ... FOR UPDATE -- 排他锁

• 显示给记录集加共享锁或排他锁

14.日志

作用：记录着数据库工作的方方面面，帮助数据库管理员追踪数据库曾经发生过的各种事件

分类：

• 错误日志

• 二进制日志

• 查询日志

• 慢查询日志

14.1 错误日志

它记录了mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息

错误日志默认开启，默认存放目录为mysql的数据目录，默认日志文件名为hostname.err(hostname 主机名)

语法：show variables like ‘log_error%’;

• 该指令查看日志位置

14.2 二进制日志

它记录了所有的DDL语句和DML语句，但不包括数据查询语句，该日志对灾难时的数据恢复起着重要作用，MySQL主从复制，就是通过该日志实现

二进制文件，8.0版本默认已开启，低版本需通过配置文件开启，并配置MySQL日志的格式

Windows：my.ini Linux：my.cnf

-- 配置开启binlog日志，日志文件前缀为mysqlbin
log_bin=mysqlbin

-- 配置二进制日志的格式
binlog_format=STATEMENT


-- 在my.ini文件中进行配置，配置完后需重启服务

14.2.1 日志格式

• STATEMENT：记录的都是SQL语句，每一条对数据进行修改的SQL都会被记录在日志文件中

• ROW：记录的是每一行的数据变更

• MIXED：STATEMENT和ROW两种格式的混合

14.3 查询日志

查询日志中记录了客户端的所有操作语句

默认情况下，查询日志未开启，若需要开启，可进行配置

-- 该选项用作开启查询日志 1：开启 0：关闭
general_log=1

-- 设置日志的文件名，若没指定，默认文件名为host_name.log
general_log_file=file_name

14.4 慢查询日志

它记录了所有执行事件超过参数long_query_time 设置值并且扫描记录不小于min_examined_row_limit 的所有QL语句的日志。

long_query_time默认为10秒，最小为0，精度可达到微秒

-- 控制慢查询日志是否开启 1：开启 0：关闭
slow_query_log=1

-- 指定慢查询日志的文件名
slow_query_log_file=slow_query.log

-- 配置查询的时间限制，超过该时间将被认定慢查询，将需要进行日志记录，默认10秒
long_query_time=10