高并发下MySQL出现checking permissions

 在某些数据访问层框架中，会使用show full tables from test like 'demo'，来检查数据库的状态。当数据库中表的数量较少时，并没有出现严重的问题。但是当数据库中的表数量多余千个时，且并发数较高时，通过show processlist就会发现show full tables语句绝大部分处于checking permissions状态

| 2 | test | 192.168.15.128:57656 | NULL | Query | 0 | checking permissions | show full tables from test like '%demo%' |
| 3 | test | 192.168.15.128:57657 | NULL | Query | 0 | checking permissions | show full tables from test like '%demo%' |
| 4 | test | 192.168.15.128:57658 | NULL | Sleep | 0 | | NULL |
| 5 | test | 192.168.15.128:57659 | NULL | Query | 0 | Sending to client | show full tables from test like '%demo%' |
| 6 | test | 192.168.15.128:57662 | NULL | Query | 0 | checking permissions | show full tables from test like '%demo%' |
| 7 | test | 192.168.15.128:57661 | NULL | Query | 0 | checking permissions | show full tables from test like '%demo%' |
| 8 | test | 192.168.15.128:57660 | NULL | Query | 0 | checking permissions | show full tables from test like '%demo%' |
| 9 | test | 192.168.15.128:57663 | NULL | Query | 0 | checking permissions | show full tables from test like '%demo%' |
| 10 | test | 192.168.15.128:57664 | NULL | Query | 0 | checking permissions | show full tables from test like '%demo%' |
| 11 | test | 192.168.15.128:57665 | NULL | Query | 0 | checking permissions | show full tables from test like '%demo%' |
| 12 | test | 192.168.15.128:57666 | test | Query | 0 | starting | show processlist |

从给出的状态信息来看，很多人会误以为这是在该SQL需要做复杂的权限检查。google一下之后，发现也有人遇到类似的问题，并认为是mysql.user表中的条目数过多。但是我本机中，显然不是这个原因。

mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 5           |
+----------+
1 row in set (0.00 sec)

由于show full tables from test like '%demo%'需要检查test数据库下的所有frm文件，因此表的数量越多，就越有可能出现这个问题。为了验证，我在两个实例上分别创建一个表和创建2000个表

一个表时，

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show full tables from test like 'demo';
+-----------------------+------------+
| Tables_in_test (demo) | Table_type |
+-----------------------+------------+
| demo | BASE TABLE |
+-----------------------+------------+
1 row in set (0.00 sec)

mysql> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000227 |
| checking permissions | 0.000020 |
| checking permissions | 0.000023 |
| Opening tables | 0.000452 |
| init | 0.000078 |
| System lock | 0.000051 |
| optimizing | 0.000030 |
| statistics | 0.000179 |
| preparing | 0.000134 |
| executing | 0.000069 |
| checking permissions | 0.000557 |
| Sending data | 0.000136 |
| end | 0.000036 |
| query end | 0.000032 |
| closing tables | 0.000042 |
| removing tmp table | 0.000110 |
| closing tables | 0.000060 |
| freeing items | 0.000122 |
| cleaning up | 0.000028 |
+----------------------+----------+
19 rows in set, 1 warning (0.01 sec)

 

2000个表时

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show full tables from test like 'demo';
+-----------------------+------------+
| Tables_in_test (demo) | Table_type |
+-----------------------+------------+
| demo | BASE TABLE |
+-----------------------+------------+
1 row in set (0.01 sec)

mysql> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000083 |
| checking permissions | 0.000004 |
| checking permissions | 0.000002 |
| Opening tables | 0.000032 |
| init | 0.000008 |
| System lock | 0.000005 |
| optimizing | 0.000003 |
| statistics | 0.000104 |
| preparing | 0.000024 |
| executing | 0.000004 |
| checking permissions | 0.002991 |
| Sending data | 0.000014 |
| end | 0.000003 |
| query end | 0.000005 |
| closing tables | 0.000002 |
| removing tmp table | 0.000003 |
| closing tables | 0.000002 |
| freeing items | 0.000047 |
| cleaning up | 0.000013 |
+----------------------+----------+
19 rows in set, 1 warning (0.01 sec)

 

0.002991/0.000557=5.36，其所消耗时间增长了5倍以上，在高并发下，其现象更明显。

那么标注为红色的checking permission是否真正表示在做权限检查了，答案是否定的。

通过检查MySQL5.6源码，可以发现，在执行show full tables from test like '%demo%'时，checking permission期间其实做了两部分工作，

1、检查权限

2、遍历test数据库下所有的frm文件，并获取相关信息。根据获得的信息，经过like条件过滤后，写入到一个临时表（memory引擎表）中

在sending data阶段，从这个临时表，把数据发送给用户

因此在某些SQL语句下，状态为checking permission时，并不一定真的在做权限检查。 

所以不要频繁的向数据库发送show full tables from test like '%demo%'，尤其是在表的数量很多时。