[[410364]]

本文轉(zhuǎn)載自微信公眾號(hào)「MySQL技術(shù)」，作者M(jìn)ySQL技術(shù)。轉(zhuǎn)載本文請(qǐng)聯(lián)系MySQL技術(shù)公眾號(hào)。

前言：

在 MySQL 中，建表時(shí)一般都會(huì)要求有主鍵。若要求不規(guī)范難免會(huì)出現(xiàn)幾張無(wú)主鍵的表，本篇文章讓我們一起揪出那個(gè)無(wú)主鍵的表。

1.無(wú)主鍵表的危害

以 InnoDB 表為例，我們都知道，在 InnoDB 中，表都是根據(jù)主鍵順序以索引的形式存放的，這種存儲(chǔ)方式的表稱為索引組織表。一張 InnoDB 表必須有一個(gè)聚簇索引，當(dāng)有主鍵時(shí)，會(huì)以主鍵作為聚簇索引;如果沒(méi)有顯式定義主鍵，InnoDB 會(huì)選擇一個(gè)唯一的非空索引代替。如果沒(méi)有這樣的索引，則 MySQL 自動(dòng)為 InnoDB 表生成一個(gè)隱含字段作為主鍵。

也就是說(shuō)，最好我們可以顯式定義主鍵，那么無(wú)主鍵表可能會(huì)產(chǎn)生哪些危害呢?首先沒(méi)有主鍵就意味著無(wú)法用到主鍵索引，可能影響查詢效率。其次是對(duì)維護(hù)不友好，比如想升級(jí)為 MGR 集群或使用某些開(kāi)源工具時(shí)，都會(huì)要求表要有主鍵。還有一點(diǎn)，對(duì)于無(wú)主鍵的表批量更新或刪除，極易引起很長(zhǎng)時(shí)間的主從延遲。

這里也順便提下，當(dāng)主庫(kù)對(duì)于無(wú)主鍵表(特別是既無(wú)主鍵又無(wú)索引的表)大量更新或刪除時(shí)，從庫(kù)會(huì)發(fā)生極大的主從延遲，甚至?xí)恢笨ㄖ鴪?zhí)行不下去，別問(wèn)我怎么知道的，前段時(shí)間遇到過(guò)。發(fā)生這種情況的現(xiàn)象是從庫(kù)延遲不斷增大，且正在執(zhí)行的主庫(kù) binlog pos 位點(diǎn)一直不變，這個(gè)時(shí)候需要去主庫(kù)解析下從庫(kù)卡著的 binlog pos 位點(diǎn)，發(fā)現(xiàn)是對(duì)某個(gè)無(wú)主鍵表的操作，這時(shí)若想從庫(kù)盡快趕上，可以手動(dòng)設(shè)置下忽略該表的同步，處理 SQL 如下：

# 假設(shè)檢查發(fā)現(xiàn)是 testtb 表導(dǎo)致了主從延遲 可以再?gòu)膸?kù)忽略該表的同步 
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) 

忽略掉該表的同步后，從庫(kù)很快就會(huì)追上主庫(kù)了。后續(xù)可以為該表增加主鍵，然后再手動(dòng)同步下并解除忽略即可。

2.找到無(wú)主鍵的表

言歸正傳，當(dāng)我們的數(shù)據(jù)庫(kù)實(shí)例中有好多好多張表時(shí)，又應(yīng)該如何查找是否有無(wú)主鍵的表呢?總不能一個(gè)個(gè)找吧，聰明的你可能想到了，可以從 MySQL 自帶的系統(tǒng)表中查找，因?yàn)槲覀兊乃薪ū硇畔⒍即鎯?chǔ)在系統(tǒng)庫(kù) information_schema 中。下面 SQL 可以查找出無(wú)主鍵的表：

# 查找某個(gè)庫(kù)中無(wú)主鍵的表（有唯一鍵無(wú)主鍵的表也會(huì)被查出） 
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'; 
 
# 查找整個(gè)實(shí)例中無(wú)主鍵的表 
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' 
); 

找到了無(wú)主鍵的表，下一步就是為表新增主鍵了，無(wú)論你使用自增 id ，uuid ，或其他算法生成的主鍵字段，都建議為表新增主鍵。以自增 id 為例，我們可以為無(wú)主鍵的表這樣新增主鍵：

# 為表 tb1 新增自增ID字段作為主鍵 
ALTER TABLE tb1 ADD COLUMN inc_id INT UNSIGNED NOT NULL auto_increment COMMENT '自增主鍵' PRIMARY KEY FIRST; 
 
# 查找到的無(wú)主鍵表 拼接出新增主鍵的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' 
) ; 

總結(jié)：

本篇文章主要介紹了無(wú)主鍵表可能會(huì)產(chǎn)生的危害及如何查找是否存在無(wú)主鍵的表。文中的一些 SQL 都是根據(jù)系統(tǒng)表來(lái)查找的，各位可以保存下到自己的環(huán)境試試看哦。MySQL 中的表還是強(qiáng)制要求有主鍵才好，人要有主見(jiàn)，表也要有主鍵!