|
|
|
|
公众号矩阵

我们一起揪出那个无主键的表

在 MySQL 中,建表时一般都会要求有主键。若要求不规范难免会出现几张无主键的表,本篇文章让我们一起揪出那个无主键的表。

作者:MySQL技术来源:MySQL技术|2021-07-09 10:36

本文转载自微信公众号「MySQL技术」,作者MySQL技术。转载本文请联系MySQL技术公众号。

前言:

在 MySQL 中,建表时一般都会要求有主键。若要求不规范难免会出现几张无主键的表,本篇文章让我们一起揪出那个无主键的表。

1.无主键表的危害

以 InnoDB 表为例,我们都知道,在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。一张 InnoDB 表必须有一个聚簇索引,当有主键时,会以主键作为聚簇索引;如果没有显式定义主键,InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引,则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键。

也就是说,最好我们可以显式定义主键,那么无主键表可能会产生哪些危害呢?首先没有主键就意味着无法用到主键索引,可能影响查询效率。其次是对维护不友好,比如想升级为 MGR 集群或使用某些开源工具时,都会要求表要有主键。还有一点,对于无主键的表批量更新或删除,极易引起很长时间的主从延迟。

这里也顺便提下,当主库对于无主键表(特别是既无主键又无索引的表)大量更新或删除时,从库会发生极大的主从延迟,甚至会一直卡着执行不下去,别问我怎么知道的,前段时间遇到过。发生这种情况的现象是从库延迟不断增大,且正在执行的主库 binlog pos 位点一直不变,这个时候需要去主库解析下从库卡着的 binlog pos 位点,发现是对某个无主键表的操作,这时若想从库尽快赶上,可以手动设置下忽略该表的同步,处理 SQL 如下:

  1. # 假设检查发现是 testtb 表导致了主从延迟 可以再从库忽略该表的同步 
  2. mysql> STOP SLAVE SQL_THREAD; 
  3. Query OK, 0 rows affected (0.00 sec) 
  4.  
  5. mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (db.testtb); 
  6. Query OK, 0 rows affected (0.00 sec) 
  7.  
  8. mysql> START SLAVE SQL_THREAD; 
  9. Query OK, 0 rows affected (0.01 sec) 

忽略掉该表的同步后,从库很快就会追上主库了。后续可以为该表增加主键,然后再手动同步下并解除忽略即可。

2.找到无主键的表

言归正传,当我们的数据库实例中有好多好多张表时,又应该如何查找是否有无主键的表呢?总不能一个个找吧,聪明的你可能想到了,可以从 MySQL 自带的系统表中查找,因为我们的所有建表信息都存储在系统库 information_schema 中。下面 SQL 可以查找出无主键的表:

  1. # 查找某个库中无主键的表(有唯一键无主键的表也会被查出) 
  2. SELECT 
  3.  t1.table_schema, 
  4.  t1.table_name 
  5. FROM 
  6.  information_schema.TABLES t1 
  7. LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA 
  8. AND t1.table_name = t2.TABLE_NAME 
  9. AND t2.CONSTRAINT_NAME IN ('PRIMARY'
  10. WHERE 
  11.  t2.table_name IS NULL 
  12. AND t1.table_type = 'BASE TABLE' 
  13. AND t1.TABLE_SCHEMA = 'testdb'
  14.  
  15. # 查找整个实例中无主键的表 
  16. SELECT 
  17.  t1.table_schema, 
  18.  t1.table_name 
  19. FROM 
  20.  information_schema.TABLES t1 
  21. LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA 
  22. AND t1.table_name = t2.TABLE_NAME 
  23. AND t2.CONSTRAINT_NAME IN ('PRIMARY'
  24. WHERE 
  25.  t2.table_name IS NULL 
  26. AND t1.table_type = 'BASE TABLE' 
  27. AND t1.TABLE_SCHEMA NOT IN ( 
  28.  'information_schema'
  29.  'performance_schema'
  30.  'mysql'
  31.  'sys' 
  32. ); 

找到了无主键的表,下一步就是为表新增主键了,无论你使用自增 id ,uuid ,或其他算法生成的主键字段,都建议为表新增主键。以自增 id 为例,我们可以为无主键的表这样新增主键:

  1. # 为表 tb1 新增自增ID字段作为主键 
  2. ALTER TABLE tb1 ADD COLUMN inc_id INT UNSIGNED NOT NULL auto_increment COMMENT '自增主键' PRIMARY KEY FIRST
  3.  
  4. # 查找到的无主键表 拼接出新增主键的SQL 
  5. SELECT 
  6. CONCAT('ALTER TABLE ',t1.table_schema,'.',t1.table_name,' ADD COLUMN inc_id INT UNSIGNED NOT NULL auto_increment COMMENT \'自增主键\' PRIMARY KEY FIRST;'
  7. FROM 
  8.  information_schema.TABLES t1 
  9. LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA 
  10. AND t1.table_name = t2.TABLE_NAME 
  11. AND t2.CONSTRAINT_NAME IN ('PRIMARY'
  12. WHERE 
  13.  t2.table_name IS NULL 
  14. AND t1.table_type = 'BASE TABLE' 
  15. AND t1.TABLE_SCHEMA NOT IN ( 
  16.  'information_schema'
  17.  'performance_schema'
  18.  'mysql'
  19.  'sys' 
  20. ) ; 

总结:

本篇文章主要介绍了无主键表可能会产生的危害及如何查找是否存在无主键的表。文中的一些 SQL 都是根据系统表来查找的,各位可以保存下到自己的环境试试看哦。MySQL 中的表还是强制要求有主键才好,人要有主见,表也要有主键!

【编辑推荐】

  1. 鸿蒙官方战略合作共建——HarmonyOS技术社区
  2. MySQL 高级性能优化指南
  3. MySQL 中的表级锁很差劲吗?
  4. JavaEE企业级开发实战-Maven+SSM+MySQL权限管理系统
  5. 讲讲MySQL Innodb ACID 的实现原理
  6. 比MySQL快801倍!ClickHouse这么牛吗?是的,简直开挂!
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

带你轻松入门 RabbitMQ

带你轻松入门 RabbitMQ

轻松入门RabbitMQ
共4章 | loong576

20人订阅学习

数据湖与数据仓库的分析实践攻略

数据湖与数据仓库的分析实践攻略

助力现代化数据管理:数据湖与数据仓库的分析实践攻略
共3章 | 创世达人

11人订阅学习

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

40人订阅学习

视频课程+更多

Web安全入门视频课程

Web安全入门视频课程

讲师:曲广平308073人学习过

渗透测试之信息收集

渗透测试之信息收集

讲师:Margin3129人学习过

Docker+kubernetes(k8s)+DevOps企业级架构师实战培训-2021新版

Docker+kubernetes(k8s)+DevOps企业级架构师

讲师:先超2632人学习过

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微