简单查询执行慢解析

样例

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000) do
    insert into t values(i,i);
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

第一类：查询长时间不返回

select * from t where id = 1;查询结果长时间不返回。

一般碰到这种情况，大概率是表t被锁住了。
使用show processlist命令查看当前语句处于什么状态。

show processlist;
或者select * from information_schema.processlist;

等MDL锁

Id	User	Host	db	Command	Time	State	Info
5	root	localhost:61558	test	Query	0	init	show processlist
7	root	localhost:63852	test	Sleep	31		NULL
8	root	localhost:63870	test	Query	25	Waiting for table metadata lock	select * from t where id = 1

Waiting for table metadata lock；表示有一个线程正在表t上请求或者持有MDL写锁，把select语句堵住了。（MySQL5.7修改了MDL的加锁策略，尽量避免此类问题。)

复现

sessionA	sessionB
lock table t write;
	select * from t where id=1;

sessionA通过lock table命令持有表t的MDL写锁(MySQL5.5，阻止其他线程访问)(InnoDB中除非明确些lock table，否则不会锁表)，sessionB的查询需要获取MDL读锁。sessionB进入等待状态。

解决

找到谁持有MDL写锁，kill掉。通过performance_schema和sys系统库处理。(MySQL启动时设置performance_schema=on；相比于设置为off会有10%左右的性能损失)。

select blocking_pid from sys.schema_table_lock_waits;
select * from performance_schema.metadata_locks;

通过查询sys.schema_table_lock_waits这张表，直接找出造成阻塞的process id（blocking_pid），把这个连接用kill命令断开即可。

等flush

Id	User	Host	db	Command	Time	State	Info
4	root	localhost:49548	test	Query	38	User sleep	select sleep()1 from t
5	root	localhost:49604	test	Query	35	Waiting for table flush	sflush tables t
6	root	localhost:49634	test	Query	30	Waiting for table flush	select * from t where id=1
7	root	localhost:49726	test	Query	0	starting	show processlist

Waiting for table flush：表示有一个线程正要对表t做flush操作。有一个flush tables命令被别的语句堵住了(flush之前必须等待所有语句执行完成(包括select))，然后它又堵住了select语句。MySQL对表做flush操作有两种用法

flush tables t with read lock;– 只关闭表t；
flush tables with read lock;– 关闭MySQL里所有打开的表(FTWRL加全局读锁，让整个库处于只读状态。flush过程中(关闭表)，查询也不行???)；

复现

sessionA	sessionB	sessionC
select sleep(1) from t;
	flush tables t;
		select * from t where id=1;

sessionA中每行都调用一次sleep(1)，这个语句默认要执行10W秒，在这期间表t一直是被sessionA“打开”着。sessionB的flush tables t命令要去关闭表t，就需要等sessionA的查询结束。sessionC再次查询的话，会被flush命令堵住。

解决

同上

等行锁(InnoDB引擎)

Id	User	Host	db	Command	Time	State	Info
4	root	localhost:65224	test	Query	0	starting	show processlist
8	root	localhost:10354	test	Query	1	statistics	select * from t where id=1 lock in share mode
10	root	localhost:11276	test	Sleep	52		NULL

访问id=1这个记录时要加读锁，如果这时已经有一个事务在这行记录上持有一个写锁，select语句就会被堵住。

复现

sessionA	sessionB
begin;
update t set c=c+1 where id=1;
	select * from t where id=1 lock in share mode;

sessionA启动了事务，占有写锁，还不提交，导致sessionB被堵住。

解决

MySQL5.7，通过sys.innodb_lock_waits表查到谁占着写锁，kill掉链接。

select blocking_pid from t sys.innodb_lock_waits where locked_table=’test.t‘;

假设4号线程是造成堵塞的罪魁祸首，”kill query 4”命令表示停止4号线程当前正在执行的语句，这个方法没有用。占行锁的是update语句，这个语句已经是之前执行完成了的，执行kill query无法让这个事务去掉id=1上的行锁。
“kill 4”命令表示直接断开这个链接。隐含的逻辑是，连接被断开的时候，会自动回滚这个链接里面正在执行的线程，也就释放了id=1上的行锁。

第二类：查询慢

扫描行数多

set long_query_time=0;– 慢查询日志的时间阈值设置为0;

select * from t where c=50000 limit 1;

Query_time:0.011543 Lock_time:0.000104 Rows_sent:1 Rows_examined:50000 SET timestamp=1544723147;– slow log慢查询日志

字段c上没有索引，这个语句只能走id主键顺序扫描，需要扫描5w行。

扫描一行，一致性读事务期间大量更新

select * from t where id=1;

Query_time:0.804400 Lock_time:0.000132205 Rows_sent:1 Rows_examined:1 SET timestamp=1544728393;

select * from t where id=1 lock in share mode;

Query_time:0.000258 Lock_time:0.000132 Rows_sent:1 Rows_examined:1 SET timestamp=1544728398;

sessionA	sessionB
start transaction with consistent snapshot
	update t set c=c+1 where id=1;//执行100W次
select * from t where id=1;
select * from t where id=1 lock in share mode;

sessionA先用start transaction with consistent snapshot命令启动了一个事务，之后sessionB才开始执行update语句。
sessionB更新完100W次，生成了100W个回滚日志(undo log)。

带lock in share mode的SQL语句，是当前读，直接读到1000001这个结果，所以速度很快；而select * from t where id=1这个语句，是一致性读，需要从当前1000001开始，依次执行undo log，执行100W次后，才将1这个结果返回，所以速度慢。

扩展

只要访问表，增删改查就要加MDL读锁。快照读没有读锁。

read-commited隔离级别下，update语句有一个“semi-consistent”read优化:
一个update语句，如果读到一行已经加锁的记录，此时InnoDB返回记录最近提交的最新版本，由MySQL上层判断此版本是否满足update的where条件。若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本并加锁(锁等待)；对于扫描返回的不满足条件的记录，会提前释放锁。

(如果update语句碰到一个已经被锁了的行，会读入最新的版本，然后判断一下是不是满足查询条件，如果不满足则直接跳过，如果满足就进入锁等待。在语句执行完成后，只有行锁，而且语句执行完成后，InnoDB就会把不满足条件的行的行锁去掉，满足条件的行锁等到commit的时候才会释放)

repeatable-read隔离级别下，对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不锁定其前面的间隙。对于update或delete语句，InnoDB只对其更新或者删除的行持有锁。在MySQL评估where条件之后，将释放非匹配行的记录锁，大大降低了死锁的可能性，但死锁仍可能发生。
为了保证binlog记录顺序，非索引更新则会锁住全表记录(无索引字段，一行一行逐一给所有行加行锁(在主键索引上)，不会加表锁)，且事务结束前不会对不符合条件记录有逐步释放的过程。