[[410364]]

本文轉載自微信公眾號「MySQL技術」，作者MySQL技術。轉載本文請聯系MySQL技術公眾號。

前言：

在 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 中的表還是強制要求有主鍵才好，人要有主見，表也要有主鍵!