mysql查询一行数据也很慢？

在使用mysql的过程中，当遇到查询语句执行缓慢时，你首先会想到什么？执行的sql语句很复杂，没有走索引？查询语句返回的数据量很大？或者，此时数据库实例自身负载压力很大或者ioutil很高。

当然这些因素都会导致一条查询语句执行效率降低。除了上面列举的三个之外，你还能想到其他导致查询变慢的原因吗？

下面小编结合日常工作中遇到的场景以及学习资料，总结了一些其他导致查询语句变慢的原因。总结来说分为两类：

1.查询长时间不返回。

2.查询慢。

为了方便下面描述，创建一下测试表：

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `a` int(11)  DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB;

创建表 table1，id为主键，a为普通数据列。

同时向表中插入10000行数据。

create procedure idata()
begin
	declare i int;
	set i=1;
	while(i<=10000)do
		insert into `table1` (id,a) values(i,i);
		set i=i+1;
	end while;
end;;
delimiter ;
call idata();

查询长时间不返回

查询长时间不返回的可能原因，有如下几种情况

1.等待MDL锁

MDL全名是：meatdata lock 也就是元数据锁，主要是为了保证"数据表的访问"和"数据表结构调整"之间的互斥，防止两者同时执行导致出现数据不一致的问题。

MDL锁机制是在msyql5.5引入的：

当对一个表进行增删改查时，会添加MDL读锁。

对一个表的表结构进行调整时，会添加MDL写锁。

读锁之间不互斥，因此多个线程可以同时对一张表进行增删改查。

读写锁之间，写锁之间是互斥的，用来保证变更表结构的安全性。

不过在mysql5.7中，修改了MDL的加锁策略，也就是我们常说的的online DDL。所以对于MDL锁阻塞其他sql语句执行的场景，在mysql 5.7及以上的版本中，比较难以复现。

不过在mysql5.7中可以用其他方式复现该场景：

sessionA通过 lock table 命令持有表 table1 MDL写锁，而sessonB的查询语句需要获取MDL读锁，所以sessionB会进入等待状态。

对于这类问题的处理方式，最终的解决方案就是找到持有MDL写锁的process id，然后把它kill掉。但是，通过show processlist 命令，能看到 sessionA的command列是sleep，并没有显示任何其持有锁的相关信息。

不过在mysql中，为了方便问题的排查，会有一些记录sql语句执行过程的相关表，在问题排查的过程中，可以借助这些表来协助问题的排查，比如像 sys库中的schema_table_lock_waits表，就记录了造成阻塞的process id。

要想使用这些方便问题排查的表信息，需要在mysql启动时设置参数 performance_schema 的值为 on。这样mysql才会记录相关的sql语句执行信息，不过记录这些信息也是有代价的，大概会损耗mysql 10%的性能。所以打开改参数前还是要三思的。

2.行锁

行锁是我们日常开发中经常遇到的一种锁，在数据行被修改的过程中，mysql会在该数据行上添加行锁，此时如果其他事务也需要在该数据行上添加行锁的话，那么后者就会被阻塞，直到前者释放行锁。

那么有哪些操作会添加行锁呢？

1.修改数据行，会在被修改的数据行上添加行锁。

2.当前读。也就是在普通的查询语句后添加for update，如：select * from table1 where id = 1 for update;

3.主动添加行锁，在普通查询语句后，添加 lock in share mode;如：select * from table1 where id = 1 in share mode.

如下所示的操作：

sessionB会一直阻塞，直到sessionA释放其占用的行锁，或者获取锁超时。

对于这种问题的处理，重点就是找到是哪个precess id占用着行锁，和查找占用MDL锁的process id的方式相同，这里不再赘述

3.并发查询

并发查询是指：mysql当前正在执行的语句。它和并发连接是完全不同的概念，并发连接是指：show processlist 的结果，也就是当前发出执行sql语句请求的连接。

通常情况下一个mysql实例的并发连接数可以达到几千个，无非就是多占用一些内存(每个连接都需要一定的内存来完成sql语句的执行和数据传输等)，但是对数据库实例影响不大。而并发查询如果过多的话，会直接影响cpu的使用率。

并发查询计数的阈值由参数 innodb_thread_concurrency控制。

也就是说， 一个mysql实例，最多可以同时执行 innodb_thread_concurrency个语句，如果待执行的sql很多，且比较耗时的话，而innodb_thread_concurrency又比较小，那么并发查询数，很快会被占满，导致很多待执行的sql语句被阻塞。

不过需要注意的是：如果正在执行sql语句的线程，由于等待锁而迟迟不能执行完毕，那么这个线程会释放占用的并发执行计数。这种设计是为了防止：由于一个占用锁的事务迟迟没有提交，导致其他与这个锁有冲突的查询全部被阻塞，导致并发查询计数很快被占满，使整个mysql实例处于不可用的状态，实际上mysql实例却处于一个很闲的状态。

但是，如果有些语句的确在执行，只不过执行的很慢，那么他占用的并发查询计数，是不会释放的，如果这样的语句很多的话，那么当并发查询计数被占满后，其他sql语句就只能等待了。

和等待锁时释放并发查询计数不同，如果sql语句是类似下面的语句：

select sleep(100) from table1;

虽然sql语句执行线程也在处于等待状态，但是却不会释放并发查询计数。

查询慢

在RR事务隔离级别下的长事务

大部分情况下，一条查询语句自身执行效率低的原因在于，查询语句没有使用合适的索引，导致查询语句执行过程中扫描了过多的数据行，导致查询语句执行较慢。

如果排除查询语句扫描行数过多的原因，你还能想到那些情况会导致查询语句执行慢的原因？下面就说一下，小编在工作中遇到的一个场景：仅仅扫描一行数据，但是执行也很慢的场景。

先说明下，我使用数据库的事务隔离级别是RR。

在一个事务内，多次执行：

select * from table1 where id =1;

时，sql语句执行时长在不断变长。

但是如果执行类似的业查询：

select * from table1 where id =1 for update;

sql语句执行时长却很短；

产生这个问题的原因主要是：我手动开启了一个事务后，忘记了提交，导致这个事务变成了一个长事务，在此期间，很多其他事务不断的更新 id=1 的数据行。而在RR事务隔离级别下，为了能够实现事务内"可重复读机制"，每次数据更新都会记录 undo log ，方便从最新的数据回溯到某个事务开启时候的数据值。查询机制如下：

在长事务提交前，对id=1的数据行操作越多，中间的undo log就会变得越长，执行快照读就越耗时，而对于当前读的查询语句，会直接读取最新的数据值，不会执行回溯的操作，因此同样的业务查询，当前读执行的效率更快。

总结

上面这些"倒霉"情况，你在工作中有遇到吗，除此之外，你还遇到过哪些更"坑"的场景？