MySQL對(duì)相同字段創(chuàng)建不同索引解析

更新時(shí)間：2023年11月22日 10:37:03 作者：愛可生開源社區(qū)

這篇文章主要為大家介紹了MySQL?對(duì)相同字段創(chuàng)建不同索引解析,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪

MySQL 可以對(duì)相同字段創(chuàng)建不同索引？

Oracle 不允許同一個(gè)字段存在兩個(gè)相同索引，但這個(gè)和 MySQL 的設(shè)計(jì)不太相同，通過實(shí)驗(yàn)，了解一下 MySQL 這種場(chǎng)景的情況。

同事問了個(gè)問題，MySQL 的某個(gè)測(cè)試庫，發(fā)現(xiàn)有這種情況：

給已設(shè)置為主鍵的列又加了一次索引，如下前兩條 SQL 語句。
給同一個(gè)字段加了 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 就行？

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

這兩個(gè)問題考察的都是關(guān)于索引的基礎(chǔ)知識(shí)，如果對(duì)此很熟悉，答案不言自明，即使不熟悉，只需要做些簡單的測(cè)試，就可以了解，加深印象。

測(cè)試一

數(shù)據(jù)庫版本：MySQL 8.0，為表 t 設(shè)置主鍵，再對(duì)同字段加個(gè)索引可以執(zhí)行成功。

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

對(duì)字段 c1 創(chuàng)建兩個(gè)索引，都可以執(zhí)行成功。

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

以上實(shí)驗(yàn)說明：MySQL 中可以對(duì)相同的字段創(chuàng)建多次相同的索引。

測(cè)試二

通過 explain，可以驗(yàn)證出對(duì)于同時(shí)存在 PRIMARY KEY 和普通索引的字段作為檢索條件時(shí)，優(yōu)化器會(huì)選擇 PRIMARY KEY 作為 key，這種選擇應(yīng)該和 MySQL 以索引組織表存儲(chǔ)的形式有關(guān)，對(duì)于同時(shí)存在兩個(gè)索引名稱的相同字段作為檢索條件時(shí)，優(yōu)化器會(huì)選擇先創(chuàng)建的索引作為 key，這倒是很像 Oracle 中 RBO 對(duì)于索引選擇的順序判斷邏輯（可能有些不嚴(yán)謹(jǐn)，但是因?yàn)橥耆莾蓚€(gè)相同的索引（Oracle 終不會(huì)允許此種情況），cost 應(yīng)該完全一致，所以選擇誰，好像無所謂）。

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)

以上實(shí)驗(yàn)說明 MySQL 對(duì)于相同字段的相同索引選擇方面的邏輯。

測(cè)試三

從效果上看，這兩個(gè)索引，保留一個(gè)即可，因?yàn)檫@兩個(gè)索引只是名稱不同，索引字段相同的，實(shí)際上就是相同的索引。

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

但對(duì)于主鍵和索引的這兩個(gè)，需要用主鍵這個(gè)。因?yàn)檫@兩個(gè)最主要的區(qū)別就是主鍵除了包含索引外，還需保證唯一，而此處的索引，就是普通索引，不是唯一索引，因此從邏輯上，這兩個(gè)是不等價(jià)。但是由于主鍵包含了索引，因此可以刪除第二個(gè)索引，它屬于重復(fù)的，主鍵的定義包含了索引的定義。

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

MySQL 之所以存在上面的這些問題，因?yàn)樗试S創(chuàng)建不同名稱相同索引字段的索引，但是如果是 Oracle，情況會(huì)是相同？

Oracle 19c，在主鍵字段上創(chuàng)建索引，會(huì)提示 此列列表已索引 的錯(cuò)誤。在相同字段上創(chuàng)建第二個(gè)索引，也是提示 此列列表已索引 的錯(cuò)誤。說明 Oracle 中根本不允許同一個(gè)字段存在兩個(gè)相同索引的情況。

總結(jié)

因此只能說不同的數(shù)據(jù)庫，設(shè)計(jì)理念不同，Oracle 更嚴(yán)謹(jǐn)些，MySQL 的容錯(cuò)性魯棒性更突出（可能不太準(zhǔn)確）。使用的時(shí)候，需要對(duì)這些基礎(chǔ)能夠有所了解，才可以針對(duì)合適的場(chǎng)景選擇合適的操作。

以上就是MySQL 對(duì)相同字段創(chuàng)建不同索引解析的詳細(xì)內(nèi)容，更多關(guān)于MySQL字段創(chuàng)建索引的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: