MySQL對相同字段創(chuàng)建不同索引解析
MySQL 可以對相同字段創(chuàng)建不同索引？
Oracle 不允許同一個字段存在兩個相同索引，但這個和 MySQL 的設(shè)計不太相同，通過實(shí)驗，了解一下 MySQL 這種場景的情況。
同事問了個問題，MySQL 的某個測試庫，發(fā)現(xiàn)有這種情況：
- 給已設(shè)置為主鍵的列又加了一次索引，如下前兩條 SQL 語句。
- 給同一個字段加了 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 就行？
這種情況是不是沒有意義？
這兩個問題考察的都是關(guān)于索引的基礎(chǔ)知識，如果對此很熟悉，答案不言自明，即使不熟悉，只需要做些簡單的測試，就可以了解，加深印象。
測試一
數(shù)據(jù)庫版本：MySQL 8.0，為表 t 設(shè)置主鍵，再對同字段加個索引可以執(zhí)行成功。
alter table t add primary key using btree(id); alter table t add index idx_t_id using btree(id);
對字段 c1 創(chuàng)建兩個索引，都可以執(zhí)行成功。
alter table tbl add index idx_t_001 using btree(c1); alter table tbl add index idx_t_002 using btree(c1);
以上實(shí)驗說明：MySQL 中可以對相同的字段創(chuàng)建多次相同的索引。
測試二
通過 explain，可以驗證出對于同時存在 PRIMARY KEY 和普通索引的字段作為檢索條件時，優(yōu)化器會選擇 PRIMARY KEY 作為 key，這種選擇應(yīng)該和 MySQL 以索引組織表存儲的形式有關(guān)，對于同時存在兩個索引名稱的相同字段作為檢索條件時，優(yōu)化器會選擇先創(chuàng)建的索引作為 key，這倒是很像 Oracle 中 RBO 對于索引選擇的順序判斷邏輯（可能有些不嚴(yán)謹(jǐn)，但是因為完全是兩個相同的索引（Oracle 終不會允許此種情況），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í)驗說明 MySQL 對于相同字段的相同索引選擇方面的邏輯。
測試三
從效果上看，這兩個索引，保留一個即可，因為這兩個索引只是名稱不同，索引字段相同的，實(shí)際上就是相同的索引。
ALTER TABLE test ADD INDEX idx_test02 USING BTREE(UPDATED): ALTER TABLE test ADD INDEX idx_test03 USING BTREE(UPDATED);
但對于主鍵和索引的這兩個，需要用主鍵這個。因為這兩個最主要的區(qū)別就是主鍵除了包含索引外，還需保證唯一，而此處的索引，就是普通索引，不是唯一索引，因此從邏輯上，這兩個是不等價。但是由于主鍵包含了索引，因此可以刪除第二個索引，它屬于重復(fù)的，主鍵的定義包含了索引的定義。
ALTER TABLE test ADD PRIMARY KEY USING BTREE(ID); ALTER TABLE test ADD INDEX idx_test01 USING BTREE(ID);
MySQL 之所以存在上面的這些問題，因為它允許創(chuàng)建不同名稱相同索引字段的索引，但是如果是 Oracle，情況會是相同？
Oracle 19c，在主鍵字段上創(chuàng)建索引，會提示 此列列表已索引 的錯誤。在相同字段上創(chuàng)建第二個索引，也是提示 此列列表已索引 的錯誤。說明 Oracle 中根本不允許同一個字段存在兩個相同索引的情況。
總結(jié)
因此只能說不同的數(shù)據(jù)庫，設(shè)計理念不同，Oracle 更嚴(yán)謹(jǐn)些，MySQL 的容錯性魯棒性更突出（可能不太準(zhǔn)確）。使用的時候，需要對這些基礎(chǔ)能夠有所了解，才可以針對合適的場景選擇合適的操作。
以上就是MySQL 對相同字段創(chuàng)建不同索引解析的詳細(xì)內(nèi)容，更多關(guān)于MySQL字段創(chuàng)建索引的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
最近我接觸了一本php 與 mysql,老外寫的一本書，書中有個tshirtshop網(wǎng)店代碼，其中操作數(shù)據(jù)庫的大多用的是mysql存儲過程2009-06-06
索引合并是mysql底層為我們提供的智能算法。本文就介紹了mysql 索引合并的使用，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-08-08
MySQL5.6解壓版服務(wù)無法啟動之系統(tǒng)錯誤1067問題
這篇文章主要介紹了MySQL5.6解壓版服務(wù)無法啟動—系統(tǒng)錯誤1067問題及解決方法，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價值,需要的朋友可以參考下2019-08-08
MySQL9.0的兩種部署模式及各個版本發(fā)布的新功能
本文主要介紹了MySQL9.0的兩種部署模式及各個版本發(fā)布的新功能,文中通過圖文示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-08-08
mysql死鎖(dead lock)與鎖等待(lock wait)的出現(xiàn)解決
死鎖和鎖等待是數(shù)據(jù)庫運(yùn)維中常見的問題,區(qū)別在于死鎖會自動解除,而鎖等待需要手動處理,本文就來介紹一下mysql死鎖(dead lock)與鎖等待(lock wait),感興趣的可以了解一下2024-09-09
node連接mysql數(shù)據(jù)庫遇到的問題和解決方案
這篇文章主要介紹了node連接mysql數(shù)據(jù)庫遇到的問題和解決方案,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-10-10
通過Query Profiler查看MySQL語句運(yùn)行時間的操作方法
這篇文章主要介紹了通過Query Profiler查看MySQL語句運(yùn)行時間的操作方法,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-03-03
下載安裝的是最新版的mysql5.7.x版本，默認(rèn)是開啟了 only_full_group_by 模式的，但開啟這個模式后，原先的 group by 語句就報錯，然后又把它移除了2020-02-02
這篇文章主要介紹了SQL之各種join小結(jié)詳細(xì)講解,本篇文章通過簡要的案例,講解了該項技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-08-08
淺析一個MYSQL語法(在查詢中使用count)的兼容性問題
本篇文章是對MYSQL語法(在查詢中使用count)的兼容性問題進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-07-07