mysql checking_高并发下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%'，尤其是在表的数量很多时。