前言

Github:https://github.com/HealerJean

博客:http://blog.healerjean.com

1、分页查询优化

1.1、数据准备

CREATE TABLE `demo_entity`
(
    `id`      bigint(20) NOT NULL AUTO_INCREMENT,
    `name`    varchar(128)        DEFAULT NULL,
    `age`     bigint(20)          DEFAULT '0',
    `country` varchar(50)         DEFAULT NULL,
    `a`   varchar(20)         DEFAULT NULL,
    `b`   varchar(20)         DEFAULT NULL,
    `c`   varchar(20)         DEFAULT NULL,
    `d`   varchar(20)         DEFAULT NULL,
    `e`   varchar(20)         DEFAULT NULL,
    `f`   varchar(20)         DEFAULT NULL,
    `g`   varchar(20)         DEFAULT NULL,
    `h`   varchar(20)         DEFAULT NULL,
    `i`   varchar(20)         DEFAULT NULL,
    `j`   varchar(20)         DEFAULT NULL,
    `k`   varchar(20)         DEFAULT NULL,
    `l`   varchar(20)         DEFAULT NULL,
    `m`   varchar(20)         DEFAULT NULL,
    `n`   varchar(20)         DEFAULT NULL,
    `o`   varchar(20)         DEFAULT NULL,
    `p`   varchar(20)         DEFAULT NULL,
    `q`   varchar(20)         DEFAULT NULL,
    `r`   varchar(20)         DEFAULT NULL,
    `s`   varchar(20)         DEFAULT NULL,
    `t`   varchar(20)         DEFAULT NULL,
    `u`   varchar(20)         DEFAULT NULL,
    `v`   varchar(20)         DEFAULT NULL,
    `w`   varchar(20)         DEFAULT NULL,
    `x`   varchar(20)         DEFAULT NULL,
    `y`   varchar(20)         DEFAULT NULL,
    `z`   varchar(20)         DEFAULT NULL,
    `cdate`   timestamp  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `udate`   timestamp  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ;



create procedure aa()
begin
  DECLARE i INT DEFAULT 1;
  SET AUTOCOMMIT=0;
    WHILE (i <= 10000000) DO
    INSERT INTO demo_entity
    (name, age, country, a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u,
     v, w, x, y, z)
    VALUES (concat('name',i ), i, concat('country',i ) , ROUND(10*rand()), ROUND(10*rand()), ROUND(100*rand()), ROUND(100*rand()), ROUND(1000*rand()), ROUND(1000*rand()), ROUND(1000*rand()), ROUND(10000*rand()), ROUND(10000*rand()), ROUND(100000*rand()), ROUND(100000*rand()), ROUND(100000*rand()), ROUND(1000000*rand()), ROUND(10000000*rand()), ROUND(10000000*rand()), ROUND(100000000*rand()), ROUND(10000000000*rand()), ROUND(10000000000*rand()), ROUND(100000000*rand()),
            '1', '1', '1', '1', '1', '1', '1');
    SET i = i + 1;
    END WHILE;
  SET AUTOCOMMIT=1;
end;

call aa(); 


1.2、问题SQL

解释:表中的字段越多下面的优化越明显,否则即使使用了下面的优化,也可能没有那么明显

通过下面的可以观察到 当达到1000万的时候,查询时间到了37s,太可怕了

select * from tb_ams_inf_repay_stat limit 0,10 ; 
#  0.003s

select * from tb_ams_inf_repay_stat  limit 10000,10 ;  
# 1 0.023s

select * from tb_ams_inf_repay_stat  limit 100000,10 ;
# 10 0.191s

select * from tb_ams_inf_repay_stat limit 1000000,10 ;
# 100 1.942s

select * from tb_ams_inf_repay_stat limit 10000000,10 ;
# 1000 37.323s

1.2.1、简单优化

1.2.1.1、方法1(子查询)

0.23s 简直要飞起来了

0.23s 

select *
from tb_ams_inf_repay_stat
where id > (select id from tb_ams_inf_repay_stat limit 1000000, 1)
limit 0,10 ;


--推荐使用 
select * from table where id>243800 order by id limit 10;

原理:

1、先使用覆盖索引index查询 ,我们只查询id索引这一个字段,比select * 或者多个字段快多了,因为只要我们写上这些字段,我们只需要10个,但是从第一条开始到 1000万条其实是都要去扫描的

2、然后再进行索引范围内range查询

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARYtb_ams_inf_repay_statrangePRIMARYPRIMARY8NULL3258410Using where
2SUBQUERYtb_ams_inf_repay_statindexNULLidx_orgcd_loannum216NULL19753500Using index

1.2.1.2、方法2(Join连接)

1.3.2、优化2: 0.31 jon

SELECT *
FROM tb_ams_inf_repay_stat a
       JOIN (select id from tb_ams_inf_repay_stat limit 1000010, 10) b ON a.ID = b.id
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARY<derived2>ALLNULLNULLNULLNULL1000020NULL
1PRIMARYaeq_refPRIMARYPRIMARY8b.id1NULL
2DERIVEDtb_ams_inf_repay_statindexNULLidx_orgcd_loannum216NULL19753500Using index

1.3、其他优化

1.3.1、带有条件的,id连续的查询(between)

0.03s 
select * from tb_ams_inf_repay_stat  where id  between 1000000 and 1000010  	 ;

1.3.2、带有条件,id不连续的查询,考虑建立索引

20s 慢死了
select * from tb_ams_inf_repay_stat  	where org_cd = 'xmsd'  	limit 1000000,10 ;
-- org_cd建立索引

select *
from tb_ams_inf_repay_stat
where org_cd = 'xmsd'
  and id > (select id from tb_ams_inf_repay_stat where org_cd = 'xmsd' limit 1000000,1)
limit 0,10 ;

0.2s 可以说相当的快了 
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARYNULLNULLNULLNULLNULLNULLNULL~~~~
2SUBQUERYtb_ams_inf_repay_statrefidx_orgcd_loannumidx_orgcd_loannum93const1Using where; Using index

1.3.2、 是否有必要进行left join查询

from scf_credit_bill  cb
left join scf_user_company credit on  cb.ref_credit_company_id = credit.id
left join scf_user_company core on  cb.ref_core_company_id = core.id
left join scf_user_company buyer on cb.buyer_company_id = buyer.id
left join scf_user_company seller on cb.seller_company_id = seller.id

项目中遇到一种情况,有一个表要和另外一张表做4从Join查询,比如我们要的scf_credit_bill 的行数有100行, 虽然要每行有4个字段是 scf_user_company 中的,但是 scf_user_company 只需要50个id就够了,我们没有必要每次都要对这100行做leftjoin查询,因为这样会查询 100 * 4 = 400 次,而其实我们只要50个,那么解决方法是

select * from scf_credit_bill 

查询池涉及企业
 List<Long> idList = 
Stream.concat(
	data.stream().map(CreditBill::getRefCoreCompanyId), 			
    data.stream().map(CreditBill::getRefCreditCompanyId))
.collect(Collectors.toList());
           
Map<Long,String > companyName = companyService.queryCompanyNameList(idList);


//让遍历data,讲取出对应的值再放入

2、Mysql慢查询

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10S以上的语句。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。

默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。

2.1、慢查询日志相关参数

MySQL 慢查询的相关参数解释:

long_query_time :慢查询阈值,当查询时间多于设定的阈值时,记录日志。

slow_query_log :是否开启慢查询日志,1表示开启,0表示关闭。

slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log

log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。

log_output:日志存储方式。

log_output='FILE'表示将日志存入文件,默认值是'FILE'

log_output='TABLE‘表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。

MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'日志记录到系统的专用日志表中,比记录到文件耗费更多的系统资源因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。

2.2、慢查询日志配置

2.2.1、查看慢查询是否开启以及日志目录

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效,如果MySQL重启后则会失效。

如果要永久生效,就必须修改配置文件my.cnf(其它系统变量也是如此)。例如如下所示:

mysql>  show variables  like '%slow_query_log%';
+------------------------------------+-------------------------------+
| Variable_name                      | Value                         |
+------------------------------------+-------------------------------+
| slow_query_log                     | ON                            |
| slow_query_log_always_write_time   | 10.000000                     |
| slow_query_log_file                | /home/work/mysql/log/slow.log |
| slow_query_log_timestamp_always    | OFF                           |
| slow_query_log_timestamp_precision | second                        |
| slow_query_log_use_global_control  |                               |
+------------------------------------+-------------------------------+
6 rows in set (0.02 sec)

mysql> 

2.2.2、查看慢查询时间

默认情况下long_query_time的值为10秒,可以使用命令修改,也可以在my.cnf参数里面修改。关于运行时间正好等于long_query_time的情况,并不会被记录下来。也就是说,在mysql源码里是判断大于long_query_time,而非大于等于。

MySQL 5.1开始,long_query_time开始以微秒记录SQL语句运行时间,之前仅用秒为单位记录。如果记录到表里面,只会记录整数部分,不会记录微秒部分。

mysql> show variables like 'long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.200000 |
+-----------------+----------+
1 row in set (0.02 sec)

mysql> 

修改了变量long_query_time,但是查询变量long_query_time的值还是10,难道没有修改到呢?

注意:使用命令 set global long_query_time=4修改后,需要重新连接或新开一个会话才能看到修改值。

2.3、日志分析工具:mysqldumpslow

-s, 是表示按照何种方式排序,
    c: 访问计数
    l: 锁定时间
    r: 返回记录
    t: 查询时间
    al:平均锁定时间
    ar:平均返回记录数
    at:平均查询时间
-t, 是top n的意思,即为返回前面多少条的数据;
-g, 后边可以写一个正则匹配模式,大小写不敏感的;

2.3.1、常用命令

2.3.1.1、得到返回记录集最多的10个SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

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

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

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

mysqldumpslow -s t -t 10 -g left join /database/mysql/mysql06_slow.log

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

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

3、数据库卡死

博主在使用使用大量定时器任务对数据库操作的时候,中间接到一个任务,需要直接对库里的字段进行修改,随性使用了一个alter table name drop column 命令结果卡主了。

卡主,我们可以使用命令来看看到底是哪个操作卡主了,然后将它kill掉

3.1、解决方法

3.1.1、命令查看,是哪台服务器上运行了什么命令

select id, db, user, host, command, time, state, info
from information_schema.processlist
where command != 'Sleep'
order by time desc ;

WX20180717-155913

mysql查看正在执行的sql

show full processlist;

WX20180910-134650

3.1.2、查看哪个线程运行了命令和命令执行的开始时间

innodb_trx         ## 当前运行的所有事务
innodb_locks       ## 当前出现的锁
innodb_lock_waits  ## 锁等待的对应关系

select * from information_schema.innodb_trx;

WX20180726-151803

3.1.3、杀死线程Id

kill 29832

3.2、锁和锁等待


root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks;
+————-+———————+——+—–+———+——-+
| Field       | Type                | Null | Key | Default | Extra |
+————-+———————+——+—–+———+——-+
| lock_id     | varchar(81)         | NO   |     |         |       |#锁ID
| lock_trx_id | varchar(18)         | NO   |     |         |       |#拥有锁的事务ID
| lock_mode   | varchar(32)         | NO   |     |         |       |#锁模式
| lock_type   | varchar(32)         | NO   |     |         |       |#锁类型
| lock_table  | varchar(1024)       | NO   |     |         |       |#被锁的表
| lock_index  | varchar(1024)       | YES  |     | NULL    |       |#被锁的索引
| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的表空间号
| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的页号
| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的记录号
| lock_data   | varchar(8192)       | YES  |     | NULL    |       |#被锁的数据
+————-+———————+——+—–+———+——-+
10 rows in set (0.00 sec)
   
root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;
+——————-+————-+——+—–+———+——-+
| Field             | Type        | Null | Key | Default | Extra |
+——————-+————-+——+—–+———+——-+
| requesting_trx_id | varchar(18) | NO   |     |         |       |#请求锁的事务ID
| requested_lock_id | varchar(81) | NO   |     |         |       |#请求锁的锁ID
| blocking_trx_id   | varchar(18) | NO   |     |         |       |#当前拥有锁的事务ID
| blocking_lock_id  | varchar(81) | NO   |     |         |       |#当前拥有锁的锁ID
+——————-+————-+——+—–+———+——-+
4 rows in set (0.00 sec)
   
root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;
+—————————-+———————+——+—–+———————+——-+
| Field                      | Type                | Null | Key | Default             | Extra |
+—————————-+———————+——+—–+———————+——-+
| trx_id                     | varchar(18)         | NO   |     |                     |       |#事务ID
| trx_state                  | varchar(13)         | NO   |     |                     |       |#事务状态
| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |#事务开始时间
| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |#innodb_locks.lock_id
| trx_wait_started           | datetime            | YES  |     | NULL                |       |#事务开始等待的时间
| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |#事务线程ID
| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |#具体SQL语句
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |#事务当前操作状态
| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |#事务中有多少个表被使用
| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |#事务拥有多少个锁
| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |#事务锁住的内存大小B
| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |#事务锁住的行数
| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |#事务更改的行数
| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |#事务并发票数
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |#事务隔离级别
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |#是否唯一性检查
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |#是否外键检查
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |#最后的外键错误
| trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |#
| trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |#
+—————————-+———————+——+—–+———————+——-+
22 rows in set (0.01 sec)

4、死锁的查看

​ 所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

4.2、死锁日志的内容

4.2.1、模拟死锁

CREATE TABLE t (i INT) ENGINE = InnoDB;
INSERT INTO t (i) VALUES(1);
\ TX1 TX2
1 BEGIN;  
2 SELECT * FROM t WHERE i = 1 FOR SHARE; BEGIN;
3   DELETE FROM t WHERE i = 1;
4 DELETE FROM t WHERE i = 1;  

当TX2走到3的时候,此时删除语句会被阻塞,然后我们在TX1中也执行删除语句,此时死锁发生,显示在TX2面板上,如下所示:

mysql> DELETE FROM t WHERE i = 1;ERROR 1213 
(40001): Deadlock found when trying to get lock; try restarting transaction

上面死锁产生的原因是:TX1需要一个X锁来删除行。但是,这个锁请求不能被授予,因为TX2已经有了一个X锁的请求,并且正在等待TX1释放它的S锁。TX1持有的S锁也不能因为TX2之前请求X锁而升级为X锁,这里InnoDB选择回滚其中的一个事务,从操作来看,回滚了事务TX1,TX2报出了死锁(回滚是Mysql的机制,往后看)。

简单来说,就是TX1的删除请求等待TX2的删除请求完成,而TX2的删除请求又在等待TX1的S锁释放,导致TX1的删除请求在等待TX1的S锁释放,这根本就释放不了的,在同一个事务自己等自己,造成死锁。

4.2.2、查看死锁日志

SHOW ENGINE INNODB STATUS;
=====================================
2019-10-11 16:43:14 0x3c7c INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 31 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 9 srv_active, 0 srv_shutdown, 6899 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 19
OS WAIT ARRAY INFO: signal count 19
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-10-11 16:42:19 0x1cfc
*** (1) TRANSACTION:
TRANSACTION 47884, ACTIVE 16 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 15, OS thread handle 2372, query id 93 localhost ::1 root updating
DELETE FROM t WHERE i = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 47884 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000009910; asc       ;;
 2: len 7; hex 81000001050110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 47885, ACTIVE 119 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 13, OS thread handle 7420, query id 94 localhost ::1 root updating
DELETE FROM t WHERE i = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 38 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 47885 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000009910; asc       ;;
 2: len 7; hex 81000001050110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 47885 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000009910; asc       ;;
 2: len 7; hex 81000001050110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 47890
Purge done for trx's n:o < 47890 undo n:o < 0 state: running but idle
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283133240156560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283133240154800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283133240153920, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 47885, ACTIVE 174 sec
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 13, OS thread handle 7420, query id 94 localhost ::1 root
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1051 OS file reads, 222 OS file writes, 30 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 2267, node heap has 2 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 7 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 2 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          23994432
Log buffer assigned up to    23994432
Log buffer completed up to   23994432
Log written up to            23994432
Log flushed up to            23994432
Added dirty pages up to      23994432
Pages flushed up to          23994432
Last checkpoint at           23994432
34 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8585216
Dictionary memory allocated 406902
Buffer pool size   512
Free buffers       244
Database pages     256
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1026, created 133, written 169
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 256, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=4352, Main thread ID=000000000000170C , state=sleeping
Number of rows inserted 51, updated 313, deleted 2, read 16575
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

LATEST DETECTED DEADLOCK表示InnoDB引擎上次发生的死锁,我们知道,死锁是至少需要有两个事务参与的,所以在其内部包含了发生死锁的具体两个事务

我们先看(1) TRANSACTION事务下

*** (1) TRANSACTION:
TRANSACTION 47884, ACTIVE 16 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 15, OS thread handle 2372, query id 93 localhost ::1 root updating
DELETE FROM t WHERE i = 1

(1) TRANSACTION下,首先我们可以看到这样一句话:TRANSACTION 47884, ACTIVE 16 sec starting index read,47884是当前事务的ID,活跃时间16s,starting index read当前当前事务正在进行索引读,注意删除和更新InnoDB内部也是要进行读操作的。接着可以看到事务1的状态:LOCK WAIT,并且有一条记录被锁上了。再接着我们看到当前事务被阻塞的SQL语句:DELETE FROM t WHERE i = 1,申请将i = 1的索引记录加上X锁。

`*** (1) WAITING FOR THIS LOCK TO BE GRANTED`: 
RECORD LOCKS space id 38 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 47884 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000009910; asc       ;;
 2: len 7; hex 81000001050110; asc        ;;
 3: len 4; hex 80000001; asc     ;;   
 

*** (1) WAITING FOR THIS LOCK TO BE GRANTED: 等待获取行锁,在这个小标题下,我们可以看到下面的日志: 我们可以看出,事务1走的是隐藏的聚集索引GEN_CLUST_INDEX,等待的锁类型是lock_mode X,说明是Next-Key Locks

接着看在(2) TRANSACTION下,事务下

*** (2) TRANSACTION:
TRANSACTION 47885, ACTIVE 119 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 13, OS thread handle 7420, query id 94 localhost ::1 root updating
DELETE FROM t WHERE i = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 38 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 47885 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

和事务1一样的分析,其正在执行的SQL:DELETE FROM t WHERE i = 1

*** (2) HOLDS THE LOCK(S):表示事务2正在持有的锁,注意在该小标题下,事务2持有的锁是:lock mode S(虽然能够持有,但是在TX1的事务也持有s锁,导致事务2不能持有X锁,继续往下看)

这是怎么回事呢?我们知道事务1持有的是S锁,S锁与S锁之间是相容的,事务2也能获取S锁,只是不能修改。

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 47885 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 000000009910; asc       ;;
 2: len 7; hex 81000001050110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

接着在(2) WAITING FOR THIS LOCK TO BE GRANTED下,我们可以看到事务2正在等待被授予Next-Key Locks

事务1和事务2的信息看完了,接着是WE ROLL BACK TRANSACTION (1),表名InnoDB回滚了事务1,至于为什么回滚事务1,我们用事务等待图来表示事务1和事务2的等待情况,事务等待图是一张有向图

可以清楚的看到,事务1和事务2在同一个记录上形成了环,也就是造成了死锁。MySQL的InnoDB引擎有死锁检测机制(等待图 wait-for-graph),如果检测到在同一个记录事务之间存在环,就会立即报出死锁,并且回滚相应事务,具体实现细节以后再说吧。

image-20201217192408647

4.2.3、查询原因

通过上面分析之后,其实大概知道自己持有哪些锁,然后导致别人获取不到锁。项目中其实一般只分析死锁的两个sql语句应该就差不多。

4.3、避免死锁方案

1、注意程序的逻辑,根本的原因是程序逻辑的顺序,最常见的是交差更新 ,这类问题要从程序上避免,所有的更新需要按照一定的顺序

Transaction 1: 更新表A -> 更新表B   

Transaction 2: 更新表B -> 更新表A     

2、一个锁定记录集的事务,其操作结果集应尽量短,以免一次占用太多资源,与其他事务处理的记录冲突,这样发生死锁的几率就越小

3、 尽量快提交事务,减少持有锁的时间

ContactAuthor