**前言：**

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

#### 1.無主鍵表的危害

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

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

這裡也順便提下，當主庫對於無主鍵表（特別是既無主鍵又無索引的表）大量更新或刪除時，從庫會發生極大的主從延遲，甚至會一直卡著執行不下去，別問我怎麼知道的，前段時間遇到過。發生這種情況的現象是從庫延遲不斷增大，且正在執行的主庫 binlog pos 位點一直不變，這個時候需要去主庫解析下從庫卡著的 binlog pos 位點，發現是對某個無主鍵表的操作，這時若想從庫儘快趕上，可以手動設定下忽略該表的同步，處理 SQL 如下：

```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 可以查詢出無主鍵的表：

```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 為例，我們可以為無主鍵的表這樣新增主鍵：

```sql
# 為表 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 中的表還是強制要求有主鍵才好，人要有主見，表也要有主鍵！

揪出那個無主鍵的表

相關文章