19 | 查一行SQL语句慢的原因分析

一、抛砖引玉

warning：如果 MySQL 数据库本身就有很大的压力，导致数据库服务器 CPU 占用率很高或 ioutil（IO 利用率）很高，这种情况下所有语句的执行都有可能变慢，不属于我们今天的讨论范围。

现有背景：构造一个表，这个表有两个字段 id 和 c，并且我在里面插入了 10 万行记录。

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

二、实战经验（分为两大类）

1.查询长时间不返回

🏁：1.1.等待表锁MDL锁。

现象：在表 t 执行mysql> select * from t where id=1;查询结果长时间不返回。

模拟：5.7以前可以通过sessionA做ddl变更，然后sessionB做查询。为什么5.7以前可以？因为onlie DDL

解决思路：查看show processlist 命令，看看当前语句处于什么状态，找到谁持有 MDL 写锁，然后把它 kill 掉。怎么查找呢？⬇️⬇️⬇️
由于在 show processlist 的结果里面，session A 的 Command 列是“Sleep”，导致查找起来很不方便。不过有了 performance_schema 和 sys 系统库以后，就方便多了。

💡：课外知识，performance_schema 主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息。（MySQL 启动时需要设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失)

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

🏁：1.2.等 flush。
操作：在表 t 上，执行下面的 SQL 语句，mysql> select * from information_schema.processlist where id=1;
现象：

分析：这个状态表示的是，现在有一个线程正要对表 t 做 flush 操作。👋：MySQL 里面对表做 flush 操作的用法：flush table with read lock;
但是，正常这个语句执行起来很快，除非他们被别的线程卡住。
复现：

下图是这个复现步骤的 show processlist 结果。

🏁：1.3.等行锁。
操作：mysql> select * from t where id=1 lock in share mode【共享锁，for update 是排他锁】;
复现：

现象：

解决策略：
如果是 MySQL 5.7 版本，可以通过 sys.innodb_lock_waits 表查到。
比如：mysql> select * from t sys.innodb_lock_waits where locked_table=’test.t’\G

⚠️：这里KILL 4 才有效，也就是说直接断开这个连接。这里隐含的一个逻辑就是，连接被断开的时候，会自动回滚这个连接里面正在执行的线程，也就释放了 id=1 上的行锁。不能KILL QUERY 4，因为这个命令表示停止 4 号线程当前正在执行的语句，而这个方法其实是没有用的。因为占有行锁的是 update 语句

2.查询慢

🏁：2.1.坏查询不一定是慢查询，没有加索引的查询在数据量不大时很快就会返回。

🏁：2.2.加S锁为当前读和不加锁为一致性读，用一致性读遇到大事务会存在问题。
举例：

解释：session B 更新完 100 万次，生成了 100 万个回滚日志 (undo log)。

三、课后题
我们在举例加锁读的时候，用的是这个语句，select * from t where id=1 lock in share mode。由于 id 上有索引，所以可以直接定位到 id=1 这一行，因此读锁也是只加在了这一行上。
但如果是下面的 SQL 语句，
begin;
select * from t where c=5 for update;
commit;
这个语句序列是怎么加锁的呢？加的锁又是什么时候释放呢？

答案1：
在 Read Committed 隔离级别下，会锁上聚簇索引中的所有记录；
在 Repeatable Read 隔离级别下，会锁上聚簇索引中的所有记录，并且会锁上聚簇索引内的所有 GAP；
在上面两个隔离级别的情况下，如果设置了 innodb_locks_unsafe_for_binlog 开启 semi-consistent read 的话，对于不满足查询条件的记录，MySQL 会提前放锁，不过加锁的过程是不可避免的
答案2:
RC隔离级别下，对非索引字段更新，有个锁全表记录的过程，不符合条件的会及时释放行锁，不必等事务结束时释放；而直接用索引列更新，只会锁索引查找值和行。update产生的X锁在不释放的情况下，DELETE语句无法执行，但是UPDATE语句能更新不符合之前X锁的记录。 RR隔离级别下，为保证binlog记录顺序，非索引更新会锁住全表记录，且事务结束前不会对不符合条件记录有逐步释放的过程。DELETE和UPDATE语句都不能执行。

四、经典踩坑经验

现有表结构：

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执行规则：

最理想的情况是，MySQL 看到字段 b 定义的是 varchar(10)，那肯定返回空呀。可惜，MySQL 并没有这么做。
执行流程如下，

• 在传给引擎执行的时候，做了字符截断。因为引擎里面这个行只定义了长度是 10，所以只截了前 10 个字节，就是’1234567890’进去做匹配；
• 这样满足条件的数据有 10 万行；
• 因为是 select *， 所以要做 10 万次回表；
• 但是每次回表以后查出整行，到 server 层一判断，b 的值都不是’1234567890abcd’;
• 返回结果是空。

五、评论出人才

🏁：1.表数据每天230万，一条语句的查询条件有1—40个，最坏情况下40。
应对这种大数据量的多条件的查询的话换种思路，将mysql复杂的组合查询条件导入到es中作为key，主键id作为value，复杂的查询经过es后得到主键id，之后走mysql会好很多