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

02/08 00:00
阅读数 59



大家好,我是知数堂SQL 优化班老师 网名:骑龟的兔子
版本 :Server version: 8.0.22 MySQL Community Server - GPL
目标:想要查找没有显示主键的表


第一步:查询所有用户表 

SELECTTABLE_SCHEMA,TABLE_NAMEFROMinformation_schema.TABLES tWHERETABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema') ANDTABLE_TYPE = 'BASE TABLE';
+--------------+------------------+| TABLE_SCHEMA | TABLE_NAME |+--------------+------------------+| employees | departments || employees | dept2 || employees | dept_emp || employees | dept_emp2 || employees | dept_emp3 || employees | dept_manager || employees | emp1 || employees | emp2 || employees | emp3 || employees | employees || employees | salaries || employees | salaries2 || employees | salaries3 || employees | salaries4_up_20w || employees | t0522 || employees | t1 || employees | t11 || employees | t11_1 || employees | t12 || employees | t4 || employees | t_g1 || employees | t_group || employees | t_group2 || employees | t_group21 || employees | t_group3 || employees | t_group4 || employees | t_group5 || employees | t_group6 || employees | t_order || employees | t_time || employees | test1 || employees | titles || employees | txt_t1 || test | clone_progress || test | customer || test | district || test | history || test | item || test | new_orders || test | order_line || test | orders || test | stock || test | t1 || test | t2 || test | t3 || test | tb_task || test | tb_task_order || test | warehouse |+--------------+------------------+48 rows in set (0.00 sec)


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


SELECTTABLE_SCHEMA,TABLE_NAMEFROMinformation_schema.TABLE_CONSTRAINTS cWHERETABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema') AND CONSTRAINT_TYPE = 'PRIMARY KEY'
+--------------+---------------+| TABLE_SCHEMA | TABLE_NAME |+--------------+---------------+| employees | departments || employees | dept_emp || employees | dept_manager || employees | emp1 || employees | emp3 || employees | employees || employees | salaries || employees | salaries3 || employees | t1 || employees | t11 || employees | t12 || employees | t_group2 || employees | t_group21 || employees | t_group6 || employees | t_time || employees | titles || test | customer || test | district || test | item || test | new_orders || test | order_line || test | orders || test | stock || test | t1 || test | t3 || test | tb_task || test | tb_task_order || test | warehouse |+--------------+---------------+28 rows in set (0.00 sec)


第三步:两个结果集进行JOIN ,找到差异

SELECTa.*, b.*FROM(SELECTTABLE_SCHEMA,TABLE_NAMEFROMinformation_schema.TABLES tWHERETABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema') ANDTABLE_TYPE = 'BASE TABLE') AS aLEFT JOIN (SELECTTABLE_SCHEMA,TABLE_NAMEFROMinformation_schema.TABLE_CONSTRAINTS cWHERETABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema') AND CONSTRAINT_TYPE = 'PRIMARY KEY') AS b USING (TABLE_SCHEMA, TABLE_NAME)WHEREb.TABLE_NAME IS NULL;
Empty set (0.00 sec)


同样的思路我们在


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

SELECTTABLE_SCHEMA,TABLE_NAMEFROMinformation_schema.TABLES tWHERETABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema') ANDTABLE_TYPE = 'BASE TABLE';+--------------+-------------------------+| TABLE_SCHEMA | TABLE_NAME              |+--------------+-------------------------+| employees    | departments             || employees    | dept2                   || employees    | dept_emp                || employees    | dept_emp2               || employees    | dept_emp3               || employees    | dept_manager            || employees    | emp1                    || employees    | emp2                    || employees    | emp3                    || employees    | employees               || employees    | salaries                || employees    | salaries2               || employees    | salaries3               || employees    | salaries4_up_20w        || employees    | salaries5               || employees    | salaries6               || employees    | t1                      || employees    | t11                     || employees    | t11_1                   || employees    | t4                      || employees    | t_g1                    || employees    | t_group                 || employees    | t_group2                || employees    | t_group3                || employees    | t_group4                || employees    | t_group5                || employees    | t_order                 || employees    | t_time                  || employees    | test1                   || employees    | titles                  || employees    | tmp1                    || employees    | txt_t1                  || test         | clone_progress          || test         | disk_free               || test         | l                       || test         | l2                      || test         | t1                      || test         | t2                      || test         | t_msg_history_2017_06_1 || test         | tb_task                 || test         | tb_task_order           || test         | ts_lock                 |+--------------+-------------------------+42 rows in set (0.00 sec)
SELECTTABLE_SCHEMA,TABLE_NAMEFROMinformation_schema.TABLE_CONSTRAINTS cWHERETABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema') AND CONSTRAINT_TYPE = 'PRIMARY KEY'+--------------+-------------------------+| TABLE_SCHEMA | TABLE_NAME |+--------------+-------------------------+| employees | departments || employees | dept_emp || employees | dept_manager || employees | emp1 || employees | emp3 || employees | employees || employees | salaries || employees | salaries3 || employees | salaries5 || employees | salaries6 || employees | t1 || employees | t11 || employees | t_time || employees | titles || test | disk_free || test | l || test | l2 || test | t_msg_history_2017_06_1 || test | tb_task || test | tb_task_order || test | ts_lock |+--------------+-------------------------+21 rows in set (0.00 sec)
SELECTa.*, b.*FROM(SELECTTABLE_SCHEMA,TABLE_NAMEFROMinformation_schema.TABLES tWHERETABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema') ANDTABLE_TYPE = 'BASE TABLE') AS aLEFT JOIN (SELECTTABLE_SCHEMA,TABLE_NAMEFROMinformation_schema.TABLE_CONSTRAINTS cWHERETABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema') AND CONSTRAINT_TYPE = 'PRIMARY KEY') AS b USING (TABLE_SCHEMA, TABLE_NAME)WHEREb.TABLE_NAME IS NULL;
+--------------+------------------+--------------+------------+| 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 | t11_1 | NULL | NULL || employees | t4 | NULL | NULL || employees | t_g1 | NULL | NULL || employees | t_group | NULL | NULL || employees | t_group2 | 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 | tmp1 | NULL | NULL || employees | txt_t1 | NULL | NULL || test | clone_progress | NULL | NULL || test | t1 | NULL | NULL || test | t2 | NULL | NULL |+--------------+------------------+--------------+------------+21 rows in set (0.01 sec)


可以看到在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: 1003Message: /* 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)


扫码直达宝藏课程



本文分享自微信公众号 - 老叶茶馆(iMySQL_WX)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部