JavaJava架构技术进阶

面试必知必会：一文彻底搞懂查询截取分析+MySQL锁机制+主从复

2020-04-17  本文已影响0人  傻姑不傻

作者：蔡不菜丶

链接：https://juejin.im/post/5e985bea518825736512e1d4

来源：掘金

一、查询截取分析

1）慢查询日志

MySQL 的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。 我们可以查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，可以结合之前explain进行全面分析。

开始使用：默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。通过show variables like '%slow_query_log' 查看是否开启了慢查询日志

设置方法：

# 以下方式只对当前数据库有效，MySQL重启后失效
set global slow_query_log = 1;
set global long_query_time = 1.0;
# 主要重新连接或者新开一个会话才能看到修改值
set session long_query_time = 1.0;

永久生效就得修改 my.cnf

slow_query_log = 1

#指定生成位置，如果没有指定默认生成 host_name-slow.log
slow_query_log_file=/var/lib/mysql/cbuc_slow.log

开启后如果long_query_time没有指定，默认为10秒，那么假如运行时间正好等于long_query_tie的情况，并不会被记录下来，也就是说在mysql源码里面的判断是大于long_query_time,而非大于等于实验：

# 手动制造一条慢SQL
select sleep(9)

跟踪日志文件 ： tail -50f cbuc_slow.log

查询当前系统中有多少条慢查询：

show global status like '%Slow_queries%'

【配置小结】在 my.ini或者my.cnf配置文件下的配置

show_query_log = 1;
show_query_log_file = /var/lib/mysql/cbuc_slow.log
long_query_time = 3;
log_output = FILE

日志分析工具mysqldumpslow查看mysqldumpslow的帮助信息:

  1. s：是表示按照何种方式排序；
  2. c：访问次数
  3. l：锁定时间
  4. r：返回记录
  5. t：查询行数
  6. al：平均锁定时间
  7. ar：平均返回记录数
  8. at：平均查询时间
  9. t：即为返回前面多少条的数据
  10. g：后边搭配一个正则匹配模式，大小写不敏感

【使用参考】1、 得到返回记录集最多的10个SQL

mysqldumpslow -s -t 10 /var/lib/mysql/cbuc_slow.log

2、 得到访问次数最多的10个SQL

mysqldumpslow -s -c -t 10 /var/lib/mysql/cbuc_slow.log

3、 得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s -t -t 10 -g "left join"  /var/lib/mysql/cbuc_slow.log

4、 另外建议在使用这些命令是结合 | 和 more使用, 否则有可能出现爆屏的情况

mysqldumpslow -s r -t 10 /var/lib/mysql/cbuc_slow.log | more

2）Show Profile

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量 默认情况下，参数处于关闭状态,并保存最近15次的运行结果

【分析步骤】

# 默认是关闭，使用前需要开启
show variables like 'profiling';

set profiling = 1;

# 运行两个SQL查看
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id

查看结果 ：

参数说明：

3）全局查询日志

# 开启
general_log = 1

# 记录日志文件的路径
general_log_file = /path/logfile

# 输出格式
log_output = FILE

</pre>

二、Mysql锁机制

1）概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显的尤其重要，也更加复杂。

【案例理解】一件商品这个时候只有一件库存，但是同时用A、B两个人要下单，那么是A下单成功还是B下单成功。这种时候就要使用到事务，我们要先从库存表中取出物品数量，然后生成订单，付款成功后生成付款信息，再更新商品数量。这个流程中，我们需要使用到锁对有限的资源进行保护，解决隔离和并发问题。 【锁的分类】

  1. 读锁（共享锁）： 针对同一份数据，多个读操作可以同时进行而不会互相影响。
  2. 写锁（排它锁）： 当前写操作没有完成前，它会阻断其他写锁和读锁。
  1. 表锁
  2. 行锁

2）三级锁

【表锁】特点：（偏读）

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大；发生锁冲突的概率高，并发度最低。

手动加锁：

lock table <table_name1> <read/write>,<table_name2> <read/write>

show open tables;

unlock tables;

读锁说明：新建两个session会话，session1 和session2此时在session1中对mylock表进行read 锁定，情况如下：

  1. session1可以查询该表的信息，session2也可以查询该表的记录
  2. session1中不能查询其他没有锁定的表，session2可以查询和更新其它没有锁定的表
  3. session1插入或更新锁定的表都会提示错误，session2插入或更新锁定的表会一直等待。
  4. 当session1释放锁后，session2之前插入或更新执行完成。

写锁说明：同样新建两个session会话，session1 和session2此时在session1中对mylock表进行write 锁定，情况如下：

  1. session1 对锁定表的查询+更新+插入操作都可以执行，session2 对锁定表的查询 被阻塞，需要等待锁的释放。但是如果session2之前有数据缓存，则可以读出缓存数据，一旦数据发生改变，缓存将失效，操作将被阻塞。

【小结】：MyISAM在执行查询语句的前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

锁类型 他人可读 他人可写 读锁 是 否 写锁 否 否

1、 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他进程的写操作。2、 对MyISAM表的写操作（加写锁），会阻塞其他线程对同一表的读和写操作，只用当写锁释放后，才会执行其他进程的读写操作。总结：读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞

【行锁】特点：（偏读）

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MyISAM的最大不同有两点：

支持事务（TRANSACTION） 采用了行级锁

事务复习：事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

并发事务处理带来的问题：

事务隔离级别：

# 查看事务的隔离级别
show variable like 'tx_isolate'

隔离级别 读数据一致性 脏读 不可重复读 幻读 未提交读（Read uncommitted） 最低级别，只能保证不读取物理上损坏的数据 是 是 是 已提交读（Read committed） 语句级 否 是 是 可重复读（Repeatable read） 事务级 否 否 是 可序列化（Serializable） 最高级别，事务级 否 否 否

写锁（排他锁）：加上排它锁后，其他事务不能再对A加任何类型的锁。已获取到排它锁的事务既能读数据，又能修改数据。

# 通过这段加锁，mysql会对查询结果中的每行都加排他锁
select ... for update;

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

  1. 尽可能让给所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
  2. 尽可能较少检索条件，避免间隙锁。
  3. 尽量控制事务大小，减少锁定资源量和时间长度。
  4. 锁住某行后，尽量不要去调别的行或表，赶紧处理被锁住的行然后释放掉锁。
  5. 涉及相同表的事务，对于调用表的顺序尽量保持一致。
  6. 在业务环境允许的情况下，尽可能低级别事务隔离。
    【页锁】
    开销和加锁时间介于表锁和行锁之间，会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

三、主从复制

1）复制的基本原理

slave 会从 master 读取binlog来进行数据同步

【三个步骤】

2）复制的基本原则

复制的最大问题： 延迟

3）主从常见配置

mysql 版本一致且后台以服务运行，主从配置都在[mysqld]结点下，都是小写 【主机修改my.ini配置文件】

server-id = 1

log-bin = 自己本地的路径/data/mysqlbin
log-bin = D:/devSoft/MySQLServer5.5/data/mysqlbin

log-err = 自己本地的路径/data/mysqlerr
log-err = D:/devSoft/MySQLServer5.5/data/mysqlerr

basedir = "自己本地路径"
basedir = D:/devSoft/MySQLServer5.5/

tmpdir = "自己本地路径"
tmpdir =  D:/devSoft/MySQLServer5.5/

datadir = "自己本地路径"
datadir =  D:/devSoft/MySQLServer5.5/data/

binlog-ignore-db = mysql

binlog-do-db = 需要复制的数据库的名字

【从机修改my.ini配置文件】

GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'从机的数据库IP'INDETIFIED BY '123456'

flush privileges;

show master status;
# 记录File和Position 的值

【在Linux从机上配置需要复制的主机】

change master to master_host = '主机IP'，
master_user='zhangsan',master_password = '123456',
master_log_file='file名字',
master_log_pos=position数字

start slave;

show slave status
#下面两个参数都是Yes，便说明主从配置成功
Slave_IO_Running：Yes
Slave_SQL_running:Yes

【主机新建库，新建表，insert记录，从机便会复制】【停止从服务复制功能】

stop slave;
上一篇 下一篇

猜你喜欢

热点阅读