407 views
Mysql

11.分析SQL语句及锁

文章目录

分析SQL语句思路

1.观察,至少跑一天,看看生产的慢SQL情况
2.开启慢查询日志,设置阈值,比如超过5秒钟的就是慢SQL,并抓取出来
3.explain + 慢SQL分析
4.show profile
5.进行SQL数据库服务器的参数调优(运维orDBA来做)

总结
1.慢查询的开启并捕获
2.explain+慢SQL分析
3.show profile查询SQL在MySQL服务器里面的执行细节
4.SQL数据库服务器的参数调优

Show Profile进行SQL分析

Show Profile是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量
默认情况下,参数处于关闭状态,并保持最近15次的运行结果。
1.Show Profile分析步骤
- 是否支持,看看当前MySQL版本是否支持
- 开启功能,默认是关闭,使用前需要开启

2.查看状态:
show variables like 'profiling%';

3.开启命令:
set profiling = 1;

4.查看多个具体的SQL的执行耗时:
show profiles
#show profiles 展示最近执行的多个SQL的执行耗时情况,具体能收集多少个SQL,
#由参数 profiling_history_size决定,默认值为15,最大值为100。
#如果设置为0,等同于关闭profiling

5.查看SQL语句的执行耗时细节:
show profile cpu,ipc for query 5;  
#这里的5是show profiles中的id
#show profile参数类型如下:
all         显示所有的开销信息
block io    显示块IO相关开销
cpu         显示CPU相关开销信息
ipc         显示发送和接收相关开销信息
memory      显示内存相关开销信息
page faults 显示页面错误相关开销信息

#参数注意
converting HEAP to MyISAM查询结果太大,内存都不够用了往磁盘上搬
Creating tmp table 创建临时表
Copying to tmp table on disk 把内存中临时表复制到磁盘,危险
locked

6.全局查询日志开启命令(记录所有敲过的命令)
set global general_log = 1;

将SQL语句写到表中
set global log_output = 'TABLE';

你所编写的SQL语句,会记录到MySQL库里的genral_log表
select * from mysql.general_log;

数据库锁

锁是计算机协调多个进程或线程并发访问某一资源的机制,数据库中有:
- 表锁
- 行锁
- 间隙锁
--------------------------------------------------------------
1. 表锁(偏读操作)
偏向MyISAM存储引擎,开销小,加锁快;无死锁,锁定粒度大,发送锁冲突的概率最高,并发度低

1.1 表锁案例
create table mylock(
    id int not null primary key auto_increment,
    name varchar(20)
)engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

1.2 手动增加表锁
lock table 表名字 read(write),表名字2 read(write);

1.3释放表锁
unlock tables;

1.4 表锁总结
MyISAM在执行查询语句(select)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁
- 对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,
- 但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其他进行的写操作
- 对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,
- 只有当写锁释放后,才会执行其他进程的读写操作
--------------------------------------------------------------
2. 行锁(偏写操作)
偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁。
锁定粒度最小,发生锁冲突的概率最低,并发度也最高

InnoDB与MyISAM的最大不同点,支持事务,采用了行级锁

2.1 行锁案例
create table test_innodb_lock(a int(11),b varchar(16))engine=innodb;

insert into test_innodb_lock values(1,'b2');
insert into test_innodb_lock values(3,'3');
insert into test_innodb_lock values(4,'4000');
insert into test_innodb_lock values(5,'5000');

create index idx_test_innodb_a on test_innodb_lock(a);

create index idx_test_innodb_b on test_innodb_lock(b);

2.2 如何分析行锁定
通过检查innodb_row_lock状态变量来分析系统上的行锁争夺情况
show status like '%innodb_row_lock%';

各个状态量的说明
Innodb_row_lock_current_waits       当前正在等待锁定的数量
- Innodb_row_lock_time            从系统启动到现在锁定的总时间长度
- Innodb_row_lock_time_avg        每次等待所花费平均时间
- Innodb_row_lock_time_max        从系统启动到现在等待最长的一次所花费的时间
- Innodb_row_lock_waits           系统启动后到现在总共等待的次数
--------------------------------------------------------------
3. 间隙锁
- 当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,
- innodb会给符合条件的已有数据记录的索引项加锁, 对于键值在条件范围内但并不存在的记录,叫做“间隙”;
- innodb也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁

3.1 间隙锁的危害
- 因为SQL执行过程中通过范围查找的话,他会锁定整个范围内所有的索引值,即使这个键值并不存在
- 间隙锁有一个比较致命的弱点,就是当锁定以为范围键值之后,即使某些不存在的键值也会被无辜的锁定,
- 而造成在锁定的时候无法插入锁定键值范围内的任何数据。
- 在某些场景下这可能会对性能造成很大的危害
--------------------------------------------------------------
4. 如何锁定一行
select * from test_innodb_lock where a = 8 for update;  

影子专属博客 赣ICP备17013143号