《高性能Mysql》重点总结（五）——慢查询日志、show profile、mysql锁以及主从复制

我会带着你远行 2022-05-09 15:14 99阅读 0赞

> 本篇将介绍慢查询日志、show profile、mysql锁以及主从复制。

# 一、慢查询日志 #

### 1. 是什么 ###

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long\_query\_time值的SQL，则会被记录到慢查询日志中。

具体指运行时间超过long\_query\_time值的SQL，则会被记录到慢查询日志中。long\_query\_time的默认值为10，意思是运行10秒以上的语句。

由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

### 2. 怎么用 ###

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。（当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。）

-- 查看开启情况
    SHOW VARIABLES LIKE '%slow_query_log%';
    
    -- 开启（只对当前数据库生效，如果要永久生效，就必须修改配置文件my.cnf）
    set global slow_query_log=1;

# 二、show profile #

### 1. 是什么 ###

mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量，相比explain，show profile展示的数据更加详尽。

### 2. 怎么用 ###

-- 查看是否开启
    show variables like 'profiling';
    
    -- 开启功能，默认是关闭，使用前需要开启
    set profiling=1;
    
    -- 查看结果
    show profiles;
    
    -- 诊断SQL
    show profile cpu,block io for query n;

# 三、Mysql锁机制 #

### 1. 按照对数据操作的类型（读\\写）分 ###

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

写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

### 2. 按照对数据操作的粒度分 ###

表锁：  
表锁以典型的MyISAM引擎为例，加S锁会阻塞整个表的写，但不会阻塞读。加X锁会阻塞整个表的读写。

行锁：  
行锁以InnoDB引擎为例，加S锁会阻塞操作行的写，但不会阻塞读。加X锁会阻塞操作行的读写。适合并发量大的场景。

页锁：  
开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

### 3. 死锁 ###

死锁：  
事务T1封锁了数据R1，事务T2封锁了数据R2，同时事务T1请求封锁数据R2，由于数据R2已被事务T2封锁，因为事务T1只能等待，事务T2请求封锁数据R1，此时双方陷入互相等待状态，造成了`死锁`。

解决：  
一次封锁法：要求每个事务必须一次性将所有要用到的数据加锁，否则就不能执行。这种方式虽然可以有效防止死锁的发生，但是增加了锁的粒度，降低了系统的并发性。并且数据库是不断变化的，很难精确地确定每个事务所需的数据对象，为此只能扩大封锁范围，将事务在执行过程中需要封锁的数据对象全部加锁，进一步降低了并发度。  
顺序封锁法：顺序封锁法是预先对一个数据对象规定一个封锁顺序，所有事务都按这个顺序实施封锁。但实施难度大。

数据库中不适合预防死锁，更适合进行死锁的诊断和解除。比如超时法和事务等待图法。

### 4. 活锁 ###

活锁：  
事务T1封锁了数据R，事务T2请求封锁数据R，事务T3也请求封锁数据R，事务T4…Tn都请求封锁数据R，当事务T1释放锁后，系统首先批准事务T3的请求，其它继续等待；事务T3释放锁后，系统批准了事务T4…Tn的请求，有可能事务T2永远在等待，这就是`活锁`。

解决：  
采用先来先服务的方式。当多个事务请求封锁同一数据时，系统按请求的先后次序对事务进行排队，优先批准请求队列中的第一个请求。

### 5. GAP锁 ###

间隙锁：行锁可能造成间隙锁。当我们用【范围条件】而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP Lock）。因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。GAP锁可以解决幻读问题。  
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

# 四、主从复制 #

### 1. MySQL主从复制过程分成三步 ###

1 master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；

2 slave将master的binary log events拷贝到它的中继日志（relay log）；

3 slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的。

### 2. 主从复制的基本原则 ###

1 每个slave只有一个master。

2 每个slave只能有一个唯一的服务器ID。

3 每个master可以有多个salve。