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

本文链接：https://blog.csdn.net/honhong1024/article/details/83097615

本篇将介绍慢查询日志、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锁可以解决幻读问题。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。