mysql优化基础三(锁机制)

TrendyBot

于 2021-08-24 13:14:21 发布

阅读量119

点赞数

分类专栏： MySQL优化基础

原文链接：https://passport.weibo.com/visitor/visitor?entry=miniblog&a=enter&url=https%3A%2F%2Fweibo.com%2F&domain=.weibo.com&sudaref=https%3A%2F%2Fntp.msn.cn%2F&ua=php-sso_sdk_client-0.6.36&_rand=1629782046.811

版权

MySQL优化基础专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一.查询优化

1.小表驱动大表

原则：小的数据集驱动大的数据集。
原因：解释原因博客链接
原理：
1.当B表数据集小于A表数据集时，用in优于exists。

可以假设B表是部门表，A表是员工表，显然B表的部门少于A表的员工。根据上图的for语句，就是先从小表B中查询出id值，再从大表A中查询出其id与B表的id相同的数据。
2.当A表数据集小于B表数据集时，用exists优于in。

可以假设A表是部门表，B表是员工表，显然A表的部门少于B表的员工。根据上图的for语句，表示先从小表A中查询出数据，再与大表B中的id进行匹配。需要注意exists语句，它意为将主查询A的数据，放到子查询B中做条件验证，根据验证结果(true或false)来决定主查询的数据结果是否得以保留。
注意：对于exists后的子查询，由于返回的结果是true或false，所以select 1 或select ‘x’都可以。

2.order by关键字优化

1.尽量使用index方式排序。

order by子句，尽可能使用index方式排序，避免使用filesort方式排序。
解释说明：由于索引本身既起到查询作用，同时还有排序作用。所以，如果order by后的子句能够满足使用索引排序的条件，那表示order by的排序功能已经通过索引排序完成了(using index)，也就不需要自己再排序一次(using filesort)。
示例分析是否使用filesort

由上图示例得出：
避免using filesort的情况如下：
1.order by后的条件遵照索引建的最佳左前缀。
2.order by后的条件字段与建立的索引顺序相同。

3.根据上图，order by后的条件字段排序方式要一致。

2.使用filesort的两种算法

单路排序：一次性取出所有字段，然后在sort_buffer中进行排序，排序结束后直接返回结果集。
双路排序：（又叫回表排序模式）取出id和排序字段，放入到sort_buffer中，然后排序，排序后需要回表再次查询，然后返回结果集。
纠正误区：如果全部使用sort_buffer内存排序(单路排序)一般情况下效率会高于磁盘文件排序(双路排序)，但注意单路由于是一次性加入所有字段，则占用空间更大，就有可能超出sort_buffer的容量，从而导致每次只能取sort_buffer容量大小的数据进行排序，排完再取sort_buffer容量大小，再排…从而使用多次I/O。即本来想比双路排序省一次I/O操作，此时反而导致了更多I/O操作，得不偿失。

3.max_length_for_sort_data和sort_buffer

max_length_for_sort_data：MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。
1.如果字段的总长度小于max_length_for_sort_data ，那么使用单路排序模式;
2.如果字段的总长度大于max_length_for_sort_data ，那么使用双路排序模式。
sort_buffer：如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

4.提高order by效率

order by时，前面的select *尽量少用，只查询需要的字段。
尝试提高sort_buffer_size。
尝试提高max_length_for_sort_data。

5.补充索引排序相关情况(以复合索引a,b,c为例)

如果where使用索引的最左前缀定义为常量，则order by能使用索引。
1.where a = const order by b,c
2.where a = const and b = const order by c
3.where a = const and b > const order by b,c
不能使用索引进行排序
1.where g = const order by b,c
2.where a = const order by c

二.慢查询日志

1.说明简介

mysql的慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的sql，就会被记录到这个日志中。
默认情况没有开启，需要手动设置。如果不是调优需要，一般不建议启动，会对性能有影响。

2.查看是否开启及如何开启

默认：show variables like ‘%slow_query_log%’;
开启：set global slow_query_log = 1;

这种开启方式是暂时，如果重启mysql，它又是关闭的。如果要永久生效，必须修改配置文件my.cnf，如下：

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/xxx.log ##指定慢查询日志文件存放路径，系统默认会给一个缺省文件host_name-slow.log(如果没有指定参数slow_query_log_file的话)

3.操作long_query_time参数

命令1：show variables like ‘long_query_time%’;

可知默认情况下，运行时间超过10秒就会被慢查询日志记录。long_query_time可以通过命令或配置文件修改。
命令2：set global long_query_time = xx;

由上图可知，修改long_query_time后，第一次查看没有变化，第二次才有变化，主要是区别在于第二次添加了global。还有一种方法就是重新连接或另外新开一个会话，这时使用第一个查看命令也能看到变化。
命令3：show global status like ‘%Slow_queries%’;(查询慢sql语句共有多少条)

4.日志分析工具mysqldumpslow

命令1：mysqldumpslow --help(查询相关使用命令)
帮助信息：
s：表示按照何种方式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：返回前面多少条的数据
g：后边搭配一个正则匹配模式，大小写不敏感。
命令使用示例如下：

三.批量插入数据脚本

建表
1.建表dept

2.建表emp
设置参数log_bin_trust_function_creators
创建函数并保证每条数据都不同
1.随机产生字符串(以下代码紫色和大写字符为sql专用字符)
2.随机产生部门编号;

1.随机产生字符串
delimiter $$ //定义$$来取代;作为语句结束符
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefg';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*7),1));
//floor(?)函数返回小于等于?的数，
//生成≥a且≤b的随机数,设x=a,y=(b-a)+1,则该随机数为floor(x+rand()*y);
//concat连接字符串
//substring(string,position),position为指定起始位置
//综上return_str则为生成的由‘abcdefg’中字符随机组合的字符串
set i = i + 1;
end while;
return return_str;
end $$

2.随机产生部门编号
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i = floor(100+rand()*10);
return i;
end $$

创建存储过程

delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10)) 
begin
declare i int default 0;
set autocommit = 0;//重点，关闭自动提交，相当于让批处理数据后再提交，防止提交次数过多
repeat
set i  = i + 1;
insert into emp(empno,ename,job,mgr,hiredate,sal,
comm,deptno) values ((start+i),rand_string(6),
'salesman',0001,curdate(),2000,400,rand_num());
UNTIL i = max_num;
//curdate()返回当前日期
end repeat;
commit;
end $$

delimiter $$
create procedure insert_dept(IN start int(10),IN max_num int(10)) 
begin
declare i int default 0;
set autocommit = 0;//重点，关闭自动提交，相当于让批处理数据后再提交，防止提交次数过多
repeat
set i  = i + 1;
insert into dept(deptno,dname,loc) values ((start+i),rand_string(4),rand_string(5));
UNTIL i = max_num;
end repeat;
commit;
end $$

调用存储过程

delimiter ;//切换回;作为结束符
call insert_dept(xx,xx);//调用
call insert_emp(xx,xx);

四.show profile

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

分析步骤

第一步：查看当前mysql版本是否支持

Show variables like 'profiling';
//查询结果默认是关闭的，需要手动开启

第二步：开启profiling

set profiling=on;

第三步：执行sql后查看profiles;

图中显示了执行过的9条sql语句的编号，所用时间，语句内容。
第四步：从不同性能方面查询profile;
不同性能查询参数表如下：

从cpu，block io方面查询3号语句的profile如下：

查询语句如下：

show profile cpu,block io for query 3;
//查询三号语句的cpu,block io

第五步：注意危险信息
1.converting HEAP to MyISAM：查询结果太大，内存不够用了往磁盘上搬。
2.Creating tmp table：创建临时表，同时要拷贝数据到临时表，且用完再删除。
3.Copying to tmp table on disk：把内存中临时表复制到磁盘。
4.locked：上锁。
示例情况如下：

五.mysql锁机制

1.锁的分类

从对数据操作的类型(读/写)来分：
读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而互不影响。
写锁(排他锁)：当前写操作没有完成前，它会阻断其他写锁和读锁。
从对数据操作的粒度来分：表锁和行锁。

2.三锁

1.表锁(偏读)

特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
案例分析：
1.建表sql(注意下图中的红字，因为接下来加的锁是myisam引擎的表锁)

2.查看表上加过的锁：show open tables;
3.在mylock表上加读锁

加锁命令：lock table tableName1 read/write, table tableName2 read/write…
4.从两个会话分析所加的读锁

以上图可知：添加读锁的当前session可以查询该表记录，由于读锁共享的特性，则第二个session也可以读这个表的数据。

以上图可知：添加读锁的当前session不可以查询其他表(book表)记录，第二个session可以查询或更新未锁定的表。

以上图可知：添加读锁的当前session对锁定的表不能进行插入或更新操作，同时其他session也不能对这个锁定表进行插入或更新操作，而是一直等待获得锁。

以上图可知：只有当添加锁的第一个session释放它获得的锁，使得第二个session获得锁，第二个session才能的插入操作才能从之前的等待变为操作完成。
5.从两个会话分析写锁

由以上图可知，添加写锁的session1对它锁定的表可以执行查询，更新和插入操作。但session2对session1锁定的表执行查询操作时会被阻塞，需要等待session2释放锁定的表才能查询成功。
分析表锁定：通过命令show status like 'table%';检查table_locks_waited和 table_locks_immediate状态变量来分析系统上的表锁定。
table_locks_waited：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
table_locks_immediate：出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在较严重的表级锁争用情况。

2.行锁(偏写)

特点：
1.偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度最高。
2.与MyISAM最大不同在于，它支持事务，并且采用行级锁。
行锁基本演示与分析
1.创建表添加索引

由上可知，InnoDB行锁是通过给索引上的索引项加锁来实现的，意味着只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
2.关闭两个会话的自动提交

3.更新同一行的情况第一个session更新后没有提交，此时第二个session也来更新，但被阻塞，只能等session1提交后才能执行更新命令。
4.更新不同行的情况

session1执行更新a = 4的行，session2执行更新a = 9的行，两者互不影响，都能执行成功。
5.一个更新，另一个读

由上图可知，左边的session1更新后，由于没有提交，则只有session1能读到修改的数据，而右边的session2不能读到修改的数据。注意，在此时session2还是不能读到修改的数据，因为它一开始也关闭了自动提交，所以需要再提交一次才能读到修改的数据。
索引失效导致行锁变表锁

首先明确图中的a是int类型，b是varchar类型，但左边的session1修改时使用了where b = 4000，即赋值类型不匹配，则底层会进行类型转换为int类型，这就会导致索引失效，从而右边的session2即使更新的是另一行，由于索引失效，原本的行锁变表锁，session2就只能等待session1提交后才能执行操作。
间隙锁：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，innoDB会给符合条件(范围条件)的已有数据记录的索引项加锁；如果在条件范围内但不存在的记录，就叫做“间隙”。innoDB也会对这个间隙加锁，就是间隙锁。

首先由第一张图可知，表中没有a=2的数据。然后看第二张图，session1执行的条件有1<a<5这个范围，之后session2再执行插入a=2的数据，虽然表中本来就没有这个数据，但依然发生了阻塞，说明innoDB对这个a=2的间隙也上了锁。
面试题：如何锁定一行

由图可知，通过左边session的两行sql语句后，右边session就不能再操作此表的数据了，需要等待左边session提交后才能执行操作。
案例结论：InnoDB由于实现了行级锁定，虽然在锁定机制实现方面带来的性能损耗可能比表级锁定更高，但在整体并发处理能力方面优于MyISAM的表级锁定。即当并发量较高时，InnoDB的整体性能比MyiSAM更有优势。
行锁分析
1.查看命令：show status like ‘innodb_row_lock%’;

2.状态量说明

当等待次数很高，而且每次等待时长也不小时，需要通过show profile分析进行优化。

3.页锁

说明：
1.开销和加锁时间介于表锁和行锁之间；
2.会出现死锁；
3.锁定粒度介于表锁和行锁之间，并发度一般。

TrendyBot

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql优化基础三(锁机制)

查询截取分析一.查询优化1.小表驱动大表2.order by关键字优化一.查询优化1.小表驱动大表原则：小的数据集驱动大的数据集。原因：解释原因博客链接原理：1.当B表数据集小于A表数据集时，用in优于exists。可以假设B表是部门表，A表是员工表，显然B表的部门少于A表的员工。根据上图的for语句，就是先从小表B中查询出id值，再从大表A中查询出其id与B表的id相同的数据。2.当A表数据集小于B表数据集时，用exists优于in。可以假设A表是部门表，B表是员工表，显然A表的部
复制链接

扫一扫