MySQL_查询一条SQL慢的原因
查询一条SQL慢的原因
学习总结
-
等待MDL锁，5.7版本之后MDL过程
- 获取MDL写锁
- 降级为MDL读锁
- 真正MDL操作
- 升级为写锁
- 释放锁
-
等待flush操作
flush tables t with read lock； 只锁定表t lush tables with read lock; 关闭mysql所有表 -
等行锁 如果一个行记录有行锁就会被锁住
- 写锁，只有本线程可以读、写，其他线程读写都不行 X排它锁
- 读锁，本线程可以读，其他线程可以读，不能写 S共享锁
- 查看行锁情况
mysql> select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G 直接kil blocking_pid 就可以 -
lock in share mod 和 select … for update 是当前读
-
最后的问题很精点，仔细看一下
未提交事务导致查询慢
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();
查询语句
mysql> select * from t where c=50000 limit 1;
由于字段 c 上没有索引，这个语句只能走 id 主键顺序扫描，因此需要扫描 5 万行。
作为确认，你可以看一下慢查询日志。注意，这里为了把所有语句记录到 slow log 里，我在连接后先执行了 set long_query_time=0，将慢查询日志的时间阈值设置为 0。
Rows_examined 显示扫描了 50000 行。你可能会说，不是很慢呀，11.5 毫秒就返回了，我们线上一般都配置超过 1 秒才算慢查询。但你要记住：坏查询不一定是慢查询。我们这个例子里面只有 10 万行记录，数据量大起来的话，执行时间就线性涨上去了。
扫描行数多，所以执行慢，这个很好理解。
但是接下来，我们再看一个只扫描一行，但是执行很慢的语句。
如图 所示，是这个例子的 slow log。可以看到，执行的语句是
mysql> select * from t where id=1；
虽然扫描行数是 1，但执行时间却长达 800 毫秒。
看上去是不是更奇怪了？按理说 lock in share mode 还要加锁，时间应该更长才对啊。
图 是这两个语句的执行输出结果。
第一个语句的查询结果里 c=1，带 lock in share mode 的语句返回的是 c=1000001。看到这里应该有更多的同学知道原因了。如果你还是没有头绪的话，也别着急。我先跟你说明一下复现步骤，再分析原因。
你看到了，session A 先用 start transaction with consistent snapshot 命令启动了一个事务，之后 session B 才开始执行 update 语句。session B 执行完 100 万次 update 语句后，id=1 这一行处于什么状态呢？你可以从图 16 中找到答案。
session B 更新完 100 万次，生成了 100 万个回滚日志 (undo log)。
**带 lock in share mode 的 SQL 语句，是当前读，**因此会直接读到 1000001 这个结果，所以速度很快；而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回。
注意，undo log 里记录的其实是“把 2 改成 1”，“把 3 改成 2”这样的操作逻辑，画成减 1 的目的是方便你看图。
查询结果为空，为什么返回这么慢
表结构
mysql> CREATE TABLE `table_a` (
`id` int(11) NOT NULL,
`b` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `b` (`b`)
) ENGINE=InnoDB;
假设现在表里面，有 100 万行数据，其中有 10 万行数据的 b 的值是’1234567890’， 假设现在执行语句是这么写的:
mysql> select * from table_a where b='1234567890abcd';
最理想的情况是，MySQL 看到字段 b 定义的是 varchar(10)，那肯定返回空呀。
可惜，MySQL 并没有这么做。那要不，就是把’1234567890abcd’拿到索引里面去做匹配，肯定也没能够快速判断出索引树 b 上并没有这个值，也很快就能返回空结果。
但实际上，MySQL 也不是这么做的。这条 SQL 语句的执行很慢，
流程是这样的：
- 在传给引擎执行的时候，做了字符截断。因为引擎里面这个行只定义了长度是 10，所以只截了前 10 个字节，就是’1234567890’进去做匹配；
- 这样满足条件的数据有 10 万行；
- 因为是 select *， 所以要做 10 万次回表；
- 但是每次回表以后查出整行，到 server 层一判断，b 的值都不是’1234567890abcd’;
- 返回结果是空。