MySQL中快速找出无显式主键的表

怼烎@ 2022-10-29 03:17 18阅读 0赞

大家好，我是知数堂SQL 优化班老师 网名：骑龟的兔子

版本 ：Server version: 8.0.22 MySQL Community Server - GPL

目标：想要查找没有显示主键的表

第一步：查询所有用户表

第二步 ： 查找所有包含显示主键的表

第三步：两个结果集进行JOIN ，找到差异

SELECT
    a.*, b.*
    FROM
    (
    SELECT
    TABLE_SCHEMA,
    TABLE_NAME
    FROM
    information_schema.TABLES t
    WHERE
    TABLE_SCHEMA NOT IN (
    'mysql',
    'sys',
    'information_schema',
    'performance_schema'
    ) AND
    TABLE_TYPE = 'BASE TABLE'
    ) AS a
    LEFT JOIN (
    SELECT
    TABLE_SCHEMA,
    TABLE_NAME
    FROM
    information_schema.TABLE_CONSTRAINTS c
    WHERE
    TABLE_SCHEMA NOT IN (
    'mysql',
    'sys',
    'information_schema',
    'performance_schema'
    ) AND CONSTRAINT_TYPE = 'PRIMARY KEY'
    ) AS b USING (TABLE_SCHEMA, TABLE_NAME)
    WHERE
    b.TABLE_NAME IS NULL;
    
    
    Empty set (0.00 sec)

同样的思路我们在

Server version:   5.7.28-log MySQL Community Server (GPL)

可以看到在5.7中就可以得到我们想要的答案，但8.0.22中就不行。

我们现在开始分析其原因，首先我们来比较下不同版本之间的SQL的执行计划。

5.7

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                                      |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
    |  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using where; Open_frm_only; Scanned all databases                                          |
    |  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned all databases; Using join buffer (Block Nested Loop) |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
    2 rows in set, 1 warning (0.01 sec)
    root@mysql3357.sock>[employees]>show warnings\G
    *************************** 1. row ***************************
      Level: Note
       Code: 1003
    Message: /* select#1 */ select `t`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`t`.`TABLE_NAME` AS `TABLE_NAME`,`c`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`c`.`TABLE_NAME` AS `TABLE_NAME` 
    from `information_schema`.`tables` `t` 
    left join (`information_schema`.`table_constraints` `c`) on(((`c`.`TABLE_NAME` = `t`.`TABLE_NAME`) and (`c`.`TABLE_SCHEMA` = `t`.`TABLE_SCHEMA`) 
    and (`t`.`TABLE_SCHEMA` not in ('mysql','sys','information_schema','performance_schema')) and (`c`.`CONSTRAINT_TYPE` = 'PRIMARY KEY'))) 
    where (isnull(`c`.`TABLE_NAME`) and (`t`.`TABLE_SCHEMA` not in ('mysql','sys','information_schema','performance_schema')) and (`t`.`TABLE_TYPE` = 'BASE TABLE'))
    1 row in set (0.00 sec)

我们可以看到解析到的SQL 中包含 isnull(\`c\`.\`TABLE\_NAME\`)

8.0

+----+-------------------+------------+------------+--------+-----------------------+-------------+---------+-------------------------------+------+----------+-----------------------------------------+
    | id | select_type       | table      | partitions | type   | possible_keys         | key         | key_len | ref                           | rows | filtered | Extra                                   |
    +----+-------------------+------------+------------+--------+-----------------------+-------------+---------+-------------------------------+------+----------+-----------------------------------------+
    |  1 | PRIMARY           | cat        | NULL       | index  | PRIMARY               | name        | 194     | NULL                          |    1 |   100.00 | Using index                             |
    |  1 | PRIMARY           | sch        | NULL       | ref    | PRIMARY,catalog_id    | catalog_id  | 8       | mysql.cat.id                  |    6 |   100.00 | Using where; Using index                |
    |  1 | PRIMARY           | tbl        | NULL       | ref    | schema_id,type,type_2 | schema_id   | 8       | mysql.sch.id                  |   47 |    67.78 | Using where                             |
    |  1 | PRIMARY           | col        | NULL       | eq_ref | PRIMARY               | PRIMARY     | 8       | mysql.tbl.collation_id        |    1 |   100.00 | Using index                             |
    |  1 | PRIMARY           | ts         | NULL       | eq_ref | PRIMARY               | PRIMARY     | 8       | mysql.tbl.tablespace_id       |    1 |   100.00 | Using index                             |
    |  1 | PRIMARY           | stat       | NULL       | eq_ref | PRIMARY               | PRIMARY     | 388     | mysql.sch.name,mysql.tbl.name |    1 |   100.00 | Using where; Using index                |
    |  1 | PRIMARY           | cat        | NULL       | index  | PRIMARY               | name        | 194     | NULL                          |    1 |   100.00 | Using index                             |
    |  1 | PRIMARY           | sch        | NULL       | eq_ref | PRIMARY,catalog_id    | catalog_id  | 202     | mysql.cat.id,func             |    1 |   100.00 | Using where; Using index                |
    |  1 | PRIMARY           | tbl        | NULL       | eq_ref | schema_id             | schema_id   | 202     | mysql.sch.id,func             |    1 |   100.00 | Using where; Rematerialize (<derived6>) |
    |  1 | PRIMARY           | <derived6> | NULL       | ref    | <auto_key0>           | <auto_key0> | 35      | const                         |    1 |   100.00 | NULL                                    |
    |  6 | DEPENDENT DERIVED | idx        | NULL       | ref    | table_id              | table_id    | 8       | mysql.tbl.id                  |    1 |    40.00 | Using where                             |
    |  7 | DEPENDENT UNION   | fk         | NULL       | ref    | table_id              | table_id    | 8       | mysql.tbl.id                  |    2 |   100.00 | Using index                             |
    |  8 | DEPENDENT UNION   | cc         | NULL       | ref    | table_id              | table_id    | 8       | mysql.tbl.id                  |    1 |   100.00 | NULL                                    |
    +----+-------------------+------------+------------+--------+-----------------------+-------------+---------+-------------------------------+------+----------+-----------------------------------------+
    13 rows in set, 5 warnings (0.00 sec)
    
    
    Message: /* select#1 */ select (`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`,(`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME`,(`mysql`.`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`
    ,(`mysql`.`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME` 
    from `mysql`.`tables` `tbl` 
    join `mysql`.`schemata` `sch` 
    join `mysql`.`catalogs` `cat` 
    left join `mysql`.`collations` `col` on((`col`.`id` = `tbl`.`collation_id`)) 
    left join `mysql`.`tablespaces` `ts` on((`ts`.`id` = `tbl`.`tablespace_id`)) 
    left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`))) 
    left join (`mysql`.`tables` `tbl` 
    join `mysql`.`schemata` `sch` 
    join `mysql`.`catalogs` `cat` 
    join lateral (/* select#6 */ select `mysql`.`idx`.`name` AS `CONSTRAINT_NAME`,if((`mysql`.`idx`.`type` = 'PRIMARY'),'PRIMARY KEY',`mysql`.`idx`.`type`) AS `CONSTRAINT_TYPE`,'YES' AS `ENFORCED` 
    from `mysql`.`indexes` `idx` 
    where ((`mysql`.`idx`.`table_id` = `mysql`.`tbl`.`id`) and (`mysql`.`idx`.`type` in ('PRIMARY','UNIQUE')) and (0 <> is_visible_dd_object(`mysql`.`tbl`.`hidden`,`mysql`.`idx`.`hidden`))) 
    union all /* select#7 */ select (`mysql`.`fk`.`name` collate utf8_tolower_ci) AS `CONSTRAINT_NAME`,'FOREIGN KEY' AS `CONSTRAINT_TYPE`,'YES' AS `ENFORCED`
    from `mysql`.`foreign_keys` `fk` where (`mysql`.`fk`.`table_id` = `mysql`.`tbl`.`id`) 
    union all /* select#8 */ select `mysql`.`cc`.`name` AS `CONSTRAINT_NAME`,'CHECK' AS `CONSTRAINT_TYPE`,`mysql`.`cc`.`enforced` AS `ENFORCED` 
    from `mysql`.`check_constraints` `cc` 
    where (`mysql`.`cc`.`table_id` = `mysql`.`tbl`.`id`)) `constraints`) on(((`mysql`.`tbl`.`schema_id` = `mysql`.`sch`.`id`) 
    and (`mysql`.`sch`.`catalog_id` = `mysql`.`cat`.`id`) and (`constraints`.`CONSTRAINT_TYPE` = 'PRIMARY KEY') 
    and ((`mysql`.`sch`.`name` collate utf8_tolower_ci) not in ('mysql','sys','information_schema','performance_schema')) 
    and ((`sch`.`name` collate utf8_tolower_ci) = (`mysql`.`sch`.`name` collate utf8_tolower_ci)) 
    and ((`tbl`.`name` collate utf8_tolower_ci) = (`mysql`.`tbl`.`name` collate utf8_tolower_ci)) and (0 <> can_access_table(`mysql`.`sch`.`name`,`mysql`.`tbl`.`name`)) 
    and (0 <> is_visible_dd_object(`mysql`.`tbl`.`hidden`)))) where ((`tbl`.`schema_id` = `sch`.`id`) and (`sch`.`catalog_id` = `cat`.`id`) and (`tbl`.`type` = 'BASE TABLE') 
    and ((`mysql`.`tbl`.`name` collate utf8_tolower_ci) is null) and ((`sch`.`name` collate utf8_tolower_ci) not in ('mysql','sys','information_schema','performance_schema')) 
    and (0 <> can_access_table(`sch`.`name`,`tbl`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`)))
    5 rows in set (0.00 sec)

从解析到的SQL中我们没有发现 TABLE\_NAME IS NULL;

相关的内容！说明在解析的过程中由于优化器的问题 解析错误了 ！

我们把视图合并功能先关下看看

set session optimizer_switch='derived_merge=off'
    
    
    Message: /* select#1 */ select `a`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`a`.`TABLE_NAME` AS `TABLE_NAME`,`b`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`b`.`TABLE_NAME` AS `TABLE_NAME` 
    from (/* select#2 */ select `information_schema`.`t`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`t`.`TABLE_NAME` AS `TABLE_NAME` from `information_schema`.`TABLES` `t`) `a` 
    left join (/* select#3 */ select `information_schema`.`c`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`c`.`TABLE_NAME` AS `TABLE_NAME` 
    from `information_schema`.`TABLE_CONSTRAINTS` `c`) `b` on(((`b`.`TABLE_NAME` = `a`.`TABLE_NAME`) and (`b`.`TABLE_SCHEMA` = `a`.`TABLE_SCHEMA`))) 
    where (`b`.`TABLE_NAME` is null)
    5 rows in set (0.00 sec)
    
    
    +--------------+------------------+--------------+------------+
    | TABLE_SCHEMA | TABLE_NAME       | TABLE_SCHEMA | TABLE_NAME |
    +--------------+------------------+--------------+------------+
    | employees    | dept2            | NULL         | NULL       |
    | employees    | dept_emp2        | NULL         | NULL       |
    | employees    | dept_emp3        | NULL         | NULL       |
    | employees    | emp2             | NULL         | NULL       |
    | employees    | salaries2        | NULL         | NULL       |
    | employees    | salaries4_up_20w | NULL         | NULL       |
    | employees    | t0522            | NULL         | NULL       |
    | employees    | t11_1            | NULL         | NULL       |
    | employees    | t4               | NULL         | NULL       |
    | employees    | t_g1             | NULL         | NULL       |
    | employees    | t_group          | NULL         | NULL       |
    | employees    | t_group3         | NULL         | NULL       |
    | employees    | t_group4         | NULL         | NULL       |
    | employees    | t_group5         | NULL         | NULL       |
    | employees    | t_order          | NULL         | NULL       |
    | employees    | test1            | NULL         | NULL       |
    | employees    | txt_t1           | NULL         | NULL       |
    | test         | clone_progress   | NULL         | NULL       |
    | test         | history          | NULL         | NULL       |
    | test         | t2               | NULL         | NULL       |
    +--------------+------------------+--------------+------------+
    20 rows in set (0.00 sec)

结果发现 TABLE\_NAME IS NULL;

又出现了！！最终发现是视图合并搞的鬼 ！视图合并功能是5.7添加的，非常好，但是有时候 就会出现一些问题，但是掌握解决问题的方法和思路，就会从容应对！

我的新一轮的SQL 优化课 即将在春节后开课

我是知数堂SQL 优化班老师~ ^^

如有关于SQL优化方面疑问和一起交流的请加 并且 @兔子@知数堂SQL优化

高性能MySQL,SQL优化群 有叶金荣，吴炳锡 两位大神坐镇 ：579036588

欢迎加入 知数堂大家庭。

我的微信公众号：SQL开发与优化(sqlturning)

**扫码直达宝藏课程**

![format_png][]

[format_png]: /images/20221024/db8fdafe0caf4b83a468d9986a027705.png