Oracle 不允許同一個字段存在兩個相同索引，但這個和 MySQL 的設計不太相同，通過實驗，瞭解一下 MySQL 這種場景的情況。

作者：劉晨，網名 bisal ，具有十年以上的應用運維工作經驗，目前主要從事數據庫應用研發能力提升和技術管理相關的工作，Oracle ACE（Alumni），騰訊雲TVP，擁有 Oracle OCM & OCP 、EXIN DevOps Master 、SCJP 等國際認證，國內首批 Oracle YEP 成員，OCMU 成員，《DevOps 最佳實踐》中文譯者之一，CSDN & ITPub 專家博主，公眾號”bisal的個人雜貨鋪”，長期堅持分享技術文章，多次在線上和線下分享技術主題。

愛可生開源社區出品，原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。

本文約 900 字，預計閲讀需要 3 分鐘。

同事問了個問題，MySQL 的某個測試庫，發現有這種情況：

1. 給已設置為主鍵的列又加了一次索引，如下前兩條 SQL 語句。
2. 給同一個字段加了 2 次索引，如下後兩條 SQL 語句。

# 情況 1
ALTER TABLE test ADD PRIMARY KEY USING BTREE(ID);
ALTER TABLE test ADD INDEX idx_test01 USING BTREE(ID);

# 情況 2
ALTER TABLE test ADD INDEX idx_test02 USING BTREE(UPDATED):
ALTER TABLE test ADD INDEX idx_test03 USING BTREE(UPDATED);

正常情況只需要一條 SQL 就行？

這種情況是不是沒有意義？

這兩個問題考察的都是關於索引的基礎知識，如果對此很熟悉，答案不言自明，即使不熟悉，只需要做些簡單的測試，就可以瞭解，加深印象。

測試一

數據庫版本：MySQL 8.0，為表 t 設置主鍵，再對同字段加個索引可以執行成功。

alter table t add primary key using btree(id);
alter table t add index idx_t_id using btree(id);

對字段 c1 創建兩個索引，都可以執行成功。

alter table tbl add index idx_t_001 using btree(c1);
alter table tbl add index idx_t_002 using btree(c1);

以上實驗説明：MySQL 中可以對相同的字段創建多次相同的索引。

測試二

通過 explain，可以驗證出對於同時存在 PRIMARY KEY 和普通索引的字段作為檢索條件時，優化器會選擇 PRIMARY KEY 作為 key，這種選擇應該和 MySQL 以索引組織表存儲的形式有關，對於同時存在兩個索引名稱的相同字段作為檢索條件時，優化器會選擇先創建的索引作為 key，這倒是很像 Oracle 中 RBO 對於索引選擇的順序判斷邏輯（可能有些不嚴謹，但是因為完全是兩個相同的索引（Oracle 終不會允許此種情況），cost 應該完全一致，所以選擇誰，好像無所謂）。

bisal@mysqldb 13:02:  [test]> explain select * from tbl where id=1;
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys    | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tbl   | NULL       | const | PRIMARY,idx_t_id | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.07 sec)

bisal@mysqldb 13:03:  [test]> explain select * from tbl where c1='a';
+----+-------------+-------+------------+------+---------------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys       | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tbl   | NULL       | ref  | idx_t_001,idx_t_002 | idx_t_001 | 7       | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

以上實驗説明 MySQL 對於相同字段的相同索引選擇方面的邏輯。

測試三

從效果上看，這兩個索引，保留一個即可，因為這兩個索引只是名稱不同，索引字段相同的，實際上就是相同的索引。

ALTER TABLE test ADD INDEX idx_test02 USING BTREE(UPDATED):
ALTER TABLE test ADD INDEX idx_test03 USING BTREE(UPDATED);

但對於主鍵和索引的這兩個，需要用主鍵這個。因為這兩個最主要的區別就是主鍵除了包含索引外，還需保證唯一，而此處的索引，就是普通索引，不是唯一索引，因此從邏輯上，這兩個是不等價。但是由於主鍵包含了索引，因此可以刪除第二個索引，它屬於重複的，主鍵的定義包含了索引的定義。

ALTER TABLE test ADD PRIMARY KEY USING BTREE(ID);
ALTER TABLE test ADD INDEX idx_test01 USING BTREE(ID);

MySQL 之所以存在上面的這些問題，因為它允許創建不同名稱相同索引字段的索引，但是如果是 Oracle，情況會是相同？

Oracle 19c，在主鍵字段上創建索引，會提示 此列列表已索引 的錯誤。在相同字段上創建第二個索引，也是提示 此列列表已索引 的錯誤。説明 Oracle 中根本不允許同一個字段存在兩個相同索引的情況。

總結

因此只能説不同的數據庫，設計理念不同，Oracle 更嚴謹些，MySQL 的容錯性魯棒性更突出（可能不太準確）。使用的時候，需要對這些基礎能夠有所瞭解，才可以針對合適的場景選擇合適的操作。