大家好,我是知数堂SQL 优化班老师 网名:骑龟的兔子
版本 :Server version: 8.0.22 MySQL Community Server - GPL
由于种种原因,需要把视图合并功能关掉,但是就碰到了,如下问题。
如果不关掉下面的问题就不会碰到。
set session optimizer_switch='derived_merge=off';
SELECT a.*, b.* FROM
( SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS
FROM information_schema.TABLES t
WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema','mysql','sys')
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_ROWS >= 10000 ) AS a
LEFT JOIN ( SELECT TABLE_SCHEMA,
TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS c
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
) AS b USING (TABLE_SCHEMA, TABLE_NAME)
WHERE b.TABLE_NAME IS NULL
ORDER BY TABLE_ROWS DESC;
ERROR 3566 (HY000): Access to native function 'internal_table_rows' is rejected.
官方bug 链接 : https://bugs.mysql.com/bug.php?id=101504
这个问题比较直接 internal_table_rows 这个内部函数不能使用导致。
也就是说视图合并关掉之后有个外部条件直接推进到里面,在生成 table_rows 之前 直接访问使用了。
我们看下8.0.22 版本中相应的参数
derived_condition_pushdown, https://dev.mysql.com/doc/refman/8.0/en/derived-condition-pushdown-optimization.html
正好符合我们的预期
set session optimizer_switch='derived_merge=off,derived_condition_pushdown=off';
SELECT a.*, b.* FROM
( SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS
FROM information_schema.TABLES t
WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema','mysql','sys')
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_ROWS >= 10000 ) AS a
LEFT JOIN ( SELECT TABLE_SCHEMA,
TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS c
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
) AS b USING (TABLE_SCHEMA, TABLE_NAME)
WHERE b.TABLE_NAME IS NULL
ORDER BY TABLE_ROWS DESC;
+--------------+------------------+------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | TABLE_SCHEMA | TABLE_NAME |
+--------------+------------------+------------+--------------+------------+
| employees | dept_emp2 | 331008 | NULL | NULL |
| employees | emp2 | 299590 | NULL | NULL |
| employees | salaries2 | 2837194 | NULL | NULL |
| employees | salaries4_up_20w | 249796 | NULL | NULL |
| employees | t11_1 | 99450 | NULL | NULL |
| employees | t_group4 | 2615040 | NULL | NULL |
| employees | t_group5 | 2165088 | NULL | NULL |
| test | history | 30020 | NULL | NULL |
+--------------+------------------+------------+--------------+------------+
这样,解决了这个奇葩问题。
新特性固然很好,但是还需要检验和掌握对应问题的解决方案,如果不是8.0.22版本碰到这个问题,也可以利用修改SQL的方法解决该问题。
这个问题在8.0.23已得到修复。
我的新一轮的SQL 优化课 即将在春节后开课
我是知数堂SQL 优化班老师~ ^^
如有关于SQL优化方面疑问和一起交流的请加 并且 @兔子@知数堂SQL优化
高性能MySQL,SQL优化群 有叶金荣,吴炳锡 两位大神坐镇 :579036588
欢迎加入 知数堂大家庭。
我的微信公众号:SQL开发与优化(sqlturning)
扫码直达宝藏课程