mysql正在执行的事务_查看Mysql正在执行的事务、锁、等待

一、关于锁的三张表(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)

二、查看锁的情况

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)

三、杀死进程

# 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)

四、SQL分析

explain使用方法

EXPLAIN命令，用于显示SQL语句的查询执行计划。EXPLAIN为用于SELECT语句中的每个表返回一行信息。

EXPLAIN EXTENDED命令：显示SQL语句的详细的查询执行计划；之后可以通过"SHOW WARNINGS"命令查看详细信息。

SHOW WARNINGS命令：可以查看MySQL优化器优化后的SQL语句。

EXPLAIN PARTITIONS命令：显示SQL语句的带有分区表信息的查询执行计划。

EXPLAIN命令的输出格式：

TRADITIONAL：传统类型，按行隔离，每行标识一个自操作。

JSON：JSON格式。

使用方法为在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)

各列功能如下：

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)