前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >揪出那个无主键的表

揪出那个无主键的表

作者头像
MySQL技术
发布2021-07-29 12:05:50
1.2K0
发布2021-07-29 12:05:50
举报
文章被收录于专栏:MySQL技术

前言:

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

1.无主键表的危害

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

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

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

代码语言:javascript
复制
# 假设检查发现是 testtb 表导致了主从延迟 可以再从库忽略该表的同步
mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = (db.testtb);
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)

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

2.找到无主键的表

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

代码语言:javascript
复制
# 查找某个库中无主键的表(有唯一键无主键的表也会被查出)
SELECT
 t1.table_schema,
 t1.table_name
FROM
 information_schema.TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
 t2.table_name IS NULL
AND t1.table_type = 'BASE TABLE'
AND t1.TABLE_SCHEMA = 'testdb';

# 查找整个实例中无主键的表
SELECT
 t1.table_schema,
 t1.table_name
FROM
 information_schema.TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
 t2.table_name IS NULL
AND t1.table_type = 'BASE TABLE'
AND t1.TABLE_SCHEMA NOT IN (
 'information_schema',
 'performance_schema',
 'mysql',
 'sys'
);

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

代码语言:javascript
复制
# 为表 tb1 新增自增ID字段作为主键
ALTER TABLE tb1 ADD COLUMN inc_id INT UNSIGNED NOT NULL auto_increment COMMENT '自增主键' PRIMARY KEY FIRST;

# 查找到的无主键表 拼接出新增主键的SQL
SELECT
CONCAT('ALTER TABLE ',t1.table_schema,'.',t1.table_name,' ADD COLUMN inc_id INT UNSIGNED NOT NULL auto_increment COMMENT \'自增主键\' PRIMARY KEY FIRST;')
FROM
 information_schema.TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
 t2.table_name IS NULL
AND t1.table_type = 'BASE TABLE'
AND t1.TABLE_SCHEMA NOT IN (
 'information_schema',
 'performance_schema',
 'mysql',
 'sys'
) ;

总结:

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

- End -

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-07-09,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 MySQL技术 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 1.无主键表的危害
  • 2.找到无主键的表
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档