乐趣区

关于mysql:MySQL无主键表查找

jiezi

前言：

在 MySQL 中，建表时个别都会要求有主键。若要求不标准难免会呈现几张无主键的表，本篇文章让咱们一起揪出那个无主键的表。

1. 无主键表的危害

以 InnoDB 表为例，咱们都晓得，在 InnoDB 中，表都是依据主键程序以索引的模式寄存的，这种存储形式的表称为索引组织表。一张 InnoDB 表必须有一个聚簇索引，当有主键时，会以主键作为聚簇索引；如果没有显式定义主键，InnoDB 会抉择一个惟一的非空索引代替。如果没有这样的索引，则 MySQL 主动为 InnoDB 表生成一个隐含字段作为主键。

也就是说，最好咱们能够显式定义主键，那么无主键表可能会产生哪些危害呢？首先没有主键就意味着无奈用到主键索引，可能影响查问效率。其次是对保护不敌对，比方想降级为 MGR 集群或应用某些开源工具时，都会要求表要有主键。还有一点，对于无主键的表批量更新或删除，极易引起很长时间的主从提早。

这里也顺便提下，当主库对于无主键表（特地是既无主键又无索引的表）大量更新或删除时，从库会产生极大的主从提早，甚至会始终卡着执行不上来，别问我怎么晓得的，前段时间遇到过。产生这种状况的景象是从库提早一直增大，且正在执行的主库 binlog pos 位点始终不变，这个时候须要去主库解析下从库卡着的 binlog pos 位点，发现是对某个无主键表的操作，这时若想从库尽快赶上，能够手动设置下疏忽该表的同步，解决 SQL 如下：

# 假如查看发现是 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 能够查找出无主键的表：

# 查找某个库中无主键的表（有惟一键无主键的表也会被查出）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 为例，咱们能够为无主键的表这样新增主键：

# 为表 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 中的表还是强制要求有主键才好，人要有主意，表也要有主键！

退出移动版