MySQL-19：查询一行数据却很慢的情况

心已赠人 2022-12-27 04:46 34阅读 0赞

#### 19.1 查询长时间不返回 ####

这里先说下基础：元数据锁(MDL)是server层的表级锁，主要用于隔离DML和DDL操作之间的干扰。DDL操作需要MDL写锁，DML操作需要MDL读锁，MDL加锁过程是系统自动控制，无法直接干预，且读读共享，读写互斥，写写互斥，当事务确定需要DML锁时，会在事务开始的时候获取锁，事务结束释放锁。

先在有表t，有三个字段id，a，b，如下：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

我们依次执行如下的操作，先执行Session A操作，再执行Session B

# Session A
    # 语句的意思为获取写锁，限制其他线程对改变的读写
    # 使用unlock tables 释放锁，或者客户端关闭的时候会释放锁
    lock table t write;
    
    # Session B
    select * from t where id = 1

我们用`show precesslist`命令会看到如下结果：查询数据的Session B在等到MDL锁，我们只要Session A使用unlock tables释放锁

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]

通过手动kill线程来解决锁问题→[手动kill线程直通车][kill]

这里通过MySQL 5.7引入的新表 `performance_schema.metadata_locks` 来解决该问题，该表可对外展示MDL的相关信息，包括其作用对象，类型及持有等待情况。

**第一步：**

表默认没有开启，使用如下命令临时开启：

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
    WHERE NAME = 'wait/lock/metadata/sql/mdl';

配置永久开启：

[mysqld]
    performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

**第二步：**

通过如下sql来查看DML锁的使用情况，就像下面这样

select * from performance_schema.metadata_locks;

![在这里插入图片描述][20201208001755288.jpg_pic_center]

**第三步：**

模拟场景，并找到线程id把它kill掉，表结果如下：我们发现47号线程在等待62号线程

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 2]

然后我们在结合`performance_schema.threads`表来查询出线程id

select * from performance_schema.threads WHERE THREAD_ID in(47,62);

![在这里插入图片描述][20201208001816487.jpg_pic_center]

结合上图，就发现22号线程在等待锁，37号线程是持有锁的，所以 kill 37；便解决该问题。

#### 19.2 等待 flush ####

当我们需要备份的时候，就需要把内存中的数据flush到磁盘上。

flush table t /flush table t with read lock ：等待正在运行的SQL请求，增加全局读锁，直到执行完毕或者unlock tables。

下面我们来模拟场景，示意图如下：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 3]

我们来用两个cmd窗口和一个Navicat窗口来模拟场景，我们通过`show processlist`命令很容易看出39号线程有问题，kill掉。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 4]

#### 19.3 等待行锁 ####

先来基础：

# 两锁都是行级锁，目的为了保证当前事务锁定的行不会被其他事务所更新或删除。
    # 行锁在需要的时候再会加上，等待事务结束才会放弃。
    # 添加共享
    select lock in share mode
    # 添加排它锁
    select for update

模拟如下场景，看视图：

![在这里插入图片描述][20201208001851299.jpg_pic_center]

我们可以通过sys.innodb\_lock\_waits 表查到，在看这个表的时候，我们着重注意四个参数：

*  waiting\_pid：等待线程id
 *  waiting\_query：等待线程要执行的语句
 *  blocking\_pid：阻塞线程的id
 *  blocking\_query ：阻塞线程执行的语句

select * from sys.innodb_lock_waits

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 5]

我们只需要kill blocking\_pid线程。[innodb\_lock\_waits教程][innodb_lock_waits]

#### 19.4 查询慢 ####

**第一种：** 在某字段上没有建索引，导致走全表扫描。

**第二种：** 回表次数过多，导致单条查询过慢。在lock in share mode下为当前读。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 6]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/b71f77448a6a4dc79c503ebddbbfc7b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/667cfe50ac7947b79af754515429affa.png
[kill]: https://www.cnblogs.com/ivictor/p/9460147.html
[20201208001755288.jpg_pic_center]: /images/20221120/9657fde057ab4a2c9737d4bdfb608ffe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/d9aee5c3ef904edb99616f5e9f055a9c.png
[20201208001816487.jpg_pic_center]: /images/20221120/63a434ee070647109fa8faaaba556680.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221120/e442e048c66647bc800686376ff5a003.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221120/563fc4f6853d4489ad2660de2e528178.png
[20201208001851299.jpg_pic_center]: /images/20221120/b153ac5f97294009ac9fe5f97e487ebd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221120/81751dc7ed4743cfab919b3738cf61d3.png
[innodb_lock_waits]: http://blog.itpub.net/28916011/viewspace-2158012/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221120/01fd9ec29e5d4c699c190d13548914d9.png