关于mysql:第26问informationschemacolumns-表上做查询慢怎么办

问题

咱们有一个 SQL，用于找到没有主键 / 惟一键的表，然而在 MySQL 5.7 上运行特地慢，怎么办？

试验

咱们搭建一个 MySQL 5.7 的环境，此处省略搭建步骤。

写个简略的脚本，制作一批带主键和不带主键的表：

执行一下脚本：

当初执行以下 SQL 看看成果：

…

执行了 16.80s，感觉是十分慢了。

当初用一下 DBA 三板斧，看看执行打算：

感觉有点惨，因为 information_schema.columns 是元数据表，没有必要的统计信息。

那咱们来 show warnings 看看 MySQL 改写后的 SQL：

咱们格式化一下 SQL：

能够看到 MySQL 将

select from A where A.x not in (select x from B) // 非关联子查问 

转换成了

select from A where not exists (select 1 from B where B.x = a.x) // 关联子查问 

如果咱们本人是 MySQL，在执行非关联子查问时，能够应用很简略的策略：

select from A where A.x not in (select x from B where ...) // 非关联子查问:
1. 扫描 B 表中的所有记录，找到满足条件的记录，寄存在长期表 C 中，建好索引
2. 扫描 A 表中的记录，与长期表 C 中的记录进行比对，间接在索引里比对，

而关联子查问就须要循环迭代：

select from A where not exists (select 1 from B where B.x = a.x and ...) // 关联子查问
扫描 A 表的每一条记录 rA：扫描 B 表，找到其中的第一条满足 rA 条件的记录。

显然，关联子查问的扫描老本会高于非关联子查问。

咱们心愿 MySQL 能先 ” 缓存 ” 子查问的后果（缓存这一步叫物化，MATERIALIZATION），但 MySQL 认为不缓存更快，咱们就须要给予 MySQL 肯定领导。

…

能够看到执行工夫变成了 0.67s。

整顿

咱们诊断的关键点如下：

1. 对于 information_schema 中的元数据表，执行打算不能提供无效信息。
2. 通过查看 MySQL 改写后的 SQL，咱们猜想了优化器产生了误判。
3. 咱们减少了 hint，领导 MySQL 正确进行优化判断。

但目前咱们的试验仅限于猜想，猜中了高枕无忧，猜不中就无奈做出好的诊断。

下一期，咱们将试着用 optimizer trace 性能，对这次优化器的误判进行剖析。

对于 MySQL 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！