Mysql 如何限制创建非主键表

2023年 10月 4日 52.4k 0

表的主键对MySQL性能与稳定有重大影响,表设计强制要求每个表必须有主键,可以规避很多不必要的异常问题。

MySQL 主从延迟最常见的情况是,由于相关表上缺少主键,应用RBR event (ROW或MIXED for binlog_format)时出现长时间延迟。这是因为在源上执行事务的SQL可以利用任何可用的键或直接表扫描,而在副本上应用行事件时不使用SQL。相反,来自源二进制日志的行事件逐行应用于副本上匹配的行图像。当存在唯一标识每一行的主键时,可以将更改快速应用到副本上适当的行图像。但是,当没有定义主键时,对于源中每个受影响的行,必须逐行将整个行图像与副本中匹配表的数据进行比较。这可能非常昂贵和耗时,并且在副本服务器上应用事务时会产生很大的延迟。

查看没有主键的表 (For MySQL version >= 5.7)

SELECT t.table_schema,
t.table_name,
k.constraint_name,
k.column_name
FROM information_schema.tables t
LEFT JOIN information_schema.key_column_usage k
ON t.table_schema = k.table_schema
AND t.table_name = k.table_name
AND k.constraint_name = 'PRIMARY'
WHERE t.table_schema NOT IN ( 'mysql', 'information_schema',
'performance_schema' )
AND k.constraint_name IS NULL
AND t.table_type = 'BASE TABLE';

MySQL Server - Version 8.0.13 and later ,提供参数 sql_require_primary_key 限制非主键表的创建,创建新表或改变现有表结构的语句是否强制要求表具有主键。这个参数太实用了,赶紧添加到最佳实践标准参数里吧。

创建无主键表直接报错:

mysql> create table sample(id int);
ERROR 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set.
Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication,
so please consult your DBA before changing this setting.

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论