[[410364]]

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

前言：

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

1.無主鍵表的危害

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

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

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

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

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

2.找到無主鍵的表

言歸正傳，當(dāng)我們的數(shù)據(jù)庫實例中有好多好多張表時，又應(yīng)該如何查找是否有無主鍵的表呢?總不能一個個找吧，聰明的你可能想到了，可以從 MySQL 自帶的系統(tǒng)表中查找，因為我們的所有建表信息都存儲在系統(tǒng)庫 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' 
) ; 

總結(jié)：

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