数据库锁
参考的，这里仅作备份记录
https://cloud.tencent.com/developer/article/1401617
一、关于锁的三张表（MEMORY引擎）
当前运行的所有事务
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 613963
trx_state: LOCK WAIT #事务状态
trx_started: 2019-02-22 10:48:48
trx_requested_lock_id: 613963:460:3:4
trx_wait_started: 2019-02-22 11:08:41
trx_weight: 2
trx_mysql_thread_id: 140
trx_query: update tx1 set c1='heyfffff',c2='heyffffff' where id =3
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 7 #事务锁住的行数
trx_rows_modified: 0
trx_concurrency_tickets: 0 #事务并发票数
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 613962
trx_state: RUNNING
trx_started: 2019-02-22 10:46:29
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 138
trx_query: select * from information_schema.innodb_trx
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
当前出现的锁
mysql> select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
lock_id: 613963:460:3:4
lock_trx_id: 613963
lock_mode: X
lock_type: RECORD
lock_table: test.tx1
lock_index: PRIMARY
lock_space: 460
lock_page: 3
lock_rec: 4
lock_data: 3
*************************** 2. row ***************************
lock_id: 613962:460:3:4
lock_trx_id: 613962
lock_mode: X
lock_type: RECORD
lock_table: test.tx1
lock_index: PRIMARY
lock_space: 460
lock_page: 3
lock_rec: 4
lock_data: 3
2 rows in set, 1 warning (0.00 sec)
锁等待的对应关系
mysql> select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 613963
requested_lock_id: 613963:460:3:4 #请求锁的锁ID
blocking_trx_id: 613962 #当前拥有锁的事务ID
blocking_lock_id: 613962:460:3:4
1 row in set, 1 warning (0.00 sec)</pre>
二、查看锁的情况
mysql> show status like 'innodb_row_lock_%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 1 |
| Innodb_row_lock_time | 479764 |
| Innodb_row_lock_time_avg | 39980 |
| Innodb_row_lock_time_max | 51021 |
| Innodb_row_lock_waits | 12 |
+-------------------------------+--------+
5 rows in set (0.00 sec)
解释如下：
Innodb_row_lock_current_waits : 当前等待锁的数量
Innodb_row_lock_time : 系统启动到现在，锁定的总时间长度
Innodb_row_lock_time_avg : 每次平均锁定的时间
Innodb_row_lock_time_max : 最长一次锁定时间
Innodb_row_lock_waits : 系统启动到现在总共锁定的次数
查询是否锁表
mysql> show OPEN TABLES where In_use > 0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test | tx1 | 1 | 0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)</pre>
三、杀死进程
1.查询进程
mysql> show processlist\G # \G 结构旋转90度变成纵向
*************************** 1. row ***************************
Id: 138
User: root
Host: localhost:55106
db: test
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 2. row ***************************
Id: 140
User: root
Host: localhost:56158
db: test
Command: Sleep # 正在等待客户端向它发送执行语句
Time: 145
State:
Info: NULL
2 rows in set (0.00 sec)
2.杀死对应进程ID
mysql> kill 140;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist\G
*************************** 1. row ***************************
Id: 138
User: root
Host: localhost:55106
db: test
Command: Query #该线程正在执行一个语句 Sleep：线程正在等待客户端向其发送新的语句。
Time: 0
State: starting
Info: show processlist
1 row in set (0.00 sec)</pre>
四、SQL分析
explain使用方法
EXPLAIN命令，用于显示SQL语句的查询执行计划。EXPLAIN为用于SELECT语句中的每个表返回一行信息。
EXPLAIN EXTENDED命令：显示SQL语句的详细的查询执行计划；之后可以通过"SHOW WARNINGS"命令查看详细信息。
SHOW WARNINGS命令：可以查看MySQL优化器优化后的SQL语句。
EXPLAIN PARTITIONS命令：显示SQL语句的带有分区表信息的查询执行计划。
EXPLAIN命令的输出格式：
TRADITIONAL：传统类型，按行隔离，每行标识一个自操作。
JSON：JSON格式。</pre>
使用方法为在SQL语句前加explain
得到结果如下：
mysql> explain select id,c1 from t1 where c1=4398825;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4992210 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)</pre>
各列功能如下：
- id: 按照sql语法解析后分层后的编号，可能重复
- select_type：
- SIMPLE，简单的select查询,不使用union及子查询
- PRIMARY，最外层的select查询
- UNION，UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集
- DEPENDENT UNION，UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集
- SUBQUERY，子查询中的第一个 select 查询,不依赖于外部查询的结果集
- DEPENDENT SUBQUERY，子查询中的第一个 select 查询,依赖于外部查询的结果集
- DERIVED，用于 from子句里有子查询的情况。 MySQL会递归执行这些子查询, 把结果放在临时表里。
- UNCACHEABLE SUBQUERY，结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估。
- UNCACHEABLE UNION，UNION 中的第二个或随后的 select 查询,属于不可缓存的子查询
- table：涉及的表，如果SQL中表有赋别名，这里出现的是别名
- type：
- system，从系统表读一行。这是const联接类型的一个特例。
- const，表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
- eq_ref，查询条件为等于
- ref，条件查询不等于
- ref_or_null，同ref(条件查询),包含NULL值的行。
- index_merge，索引联合查询
- unique_subquery，利用唯一索引进行子查询
- index_subquery，用非唯一索引进行子查询
- range，索引范围扫描
- index，索引全扫描
- ALL，全表扫描。
- possible_keys：可能使用的索引
- key：sql中使用的索引
- key_len：索引长度
- ref：使用哪个列或常数与key一起从表中选择行。
- rows：显示MYSQL执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引
- Extra：该列包含MySQL解决查询的详细信息。
- Distinct,去重，返回第一个满足条件的值
- Not exists 使用not exists查询
- Range checked for each record,有索引，但索引选择率很低
- Using filesort,有序查询
- Using index,索引全扫描
- Using index condition,索引查询
- Using temporary,临表表检索
- Using where,where条件查询
- Using sort_union,有序合并查询
- Using union,合并查询
- Using intersect,索引交叉合并
- Impossible WHERE noticed after reading const tables,读取const tables,查询结果为空
- No tables used,没有使用表
- Using join buffer (Block Nested Loop),使用join buffer(BNL算法)
- Using MRR(Multi-Range Read ) 使用辅助索引进行多范围读
五、数据库的一些默认设置
查看数据库默认存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
查看当前数据库默认隔离级别
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
默认自动提交事务
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)</pre>
执行的语句
-- 查询 正在执行的事务：
SELECT * FROM information_schema.INNODB_TRX
-- 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 查看哪个表有事务（不确认）
show OPEN TABLES where In_use > 0;
-- 看 sql 执行时间 ID 为 sql 线程ID
select * from information_schema.`PROCESSLIST` WHERE db = 'xxl-job' and ID = 1671933;
-- 800w的数据
SELECT count(id) from xxl_job_log WHERE trigger_time <= '2020-05-03 14:52:54.048';
-- 369s 查询800w
SELECT id from xxl_job_log WHERE trigger_time <= '2020-05-03 14:52:54.048' LIMIT 0, 20;
-- 进行数据删除，但是发现会报错
-- 2020-06-02T09:12:34.855260Z 1671933 [Warning] InnoDB: Over 67 percent of the buffer pool is occupied by lock heaps or the adaptive hash index! Check that your transactions do not set too many row locks. Your buffer pool size is 127 MB. Maybe you should make the buffer pool bigger?. Starting the InnoDB Monitor to print diagnostics, including lock heap and hash index sizes.
DELETE from xxl_job_log WHERE trigger_time <= '2020-05-03 14:52:54.048';
-- 134217728=128M 查看 innodb_buffer_pool_size 的大小
show variables like '%buffer%';
-- 设置临时大小 20G
set global innodb_buffer_pool_size = 21474836480;
