MySQL有时候简单语句查询慢的问题分析

为何有些查询一条记录的语句也很慢

借用丁奇老师的建表语句

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();

这个语句会构建一个10w行记录的简单表

我们有时候在系统中运行最简单的查询语句
mysql> select * from t where id=1;
有时候也会发现执行的很慢，这中情况可能有多种可能。

第一种： 等待获取MDL锁

如果执行很慢可以看下show processlist 命令。 借用下丁奇的图

可以看到这句话：
Waiting for table metadata lock
这表示有个事务持有了MDL写锁，导致查询堵塞了。 很有可能有人正在进行增加数据表字段的操作。

第二种： 等flush

flush tables t with read lock; 
flush tables with read lock;

上面两个语句 会清空表的缓存内容并关闭表， 对表或者全局的表增加了读锁。 当然上面两个指令会等待所有其他事务执行完成后运行。

如果表进行这种操作中，查询也会被堵住。

第三种 ： 等行锁

刚好你要操作的记录被其他的事务进行行锁锁住了， 这个时候你查询自然会被堵住。 注意这里的查询是只你的加锁查询
比如：mysql> select * from t where id=1 lock in share mode;

第四种 ： 查询慢

mysql> select * from t where c=50000 limit 1;
这个语句t表中c字段没有加索引， 所以会全表扫描， 会扫描5w1行数据。 自然扫描很慢

第五种： 其他事务进行了太多的修改操作

mysql> select * from t where id=1
这个语句很简单， 也会使用主键索引，应该不存在查询慢的问题， 但是有一种情况。
比如在这个简单查询A事务执行中， 另外的事务B对id=1这行记录进行了100万次的自增修改。 又由于RR的隔离级别， 事务A读取到的数据还是1。 那么这个1值是怎么计算来的呢。 它是根据最新的值和undo log往前推100w个版本后得出的。 这个往前推100w次就是一个很慢的操作。

所以最好不要在业务中进行长事务和大事务

总结

查询慢的原因有很多，这里丁奇老师列出了五种， 我进行理解加工后有这篇文章。 我们在日常碰到问题的时候要多加思考下。

下面语句的加锁流程：

begin; 
	select * from t where c=5 for update; 
commit;

因为C字段没有索引， 索引需要全表扫描找到c=5的字段， 扫描到哪一行就会对这行进行加锁。 如果是RR的隔离级别还会加上GAP锁。

如果在RC的隔离级别下， 扫描到某一行后发现不符合c=5的条件会立即释放锁的， 但是RR级别并不会马上释放锁。 因为RR还要保证一致性读， 如果这个时候其他事务对这行修改了导致又符合c=5的条件了怎么办， 所以干脆不释放锁。