MySQL對相同字段創(chuàng)建不同索引解析
MySQL 可以對相同字段創(chuàng)建不同索引？
Oracle 不允許同一個字段存在兩個相同索引，但這個和 MySQL 的設(shè)計不太相同，通過實(shí)驗(yàn)，了解一下 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í)驗(yàn)說明：MySQL 中可以對相同的字段創(chuàng)建多次相同的索引。
測試二
通過 explain，可以驗(yàn)證出對于同時存在 PRIMARY KEY 和普通索引的字段作為檢索條件時，優(yōu)化器會選擇 PRIMARY KEY 作為 key，這種選擇應(yīng)該和 MySQL 以索引組織表存儲的形式有關(guān)，對于同時存在兩個索引名稱的相同字段作為檢索條件時，優(yōu)化器會選擇先創(chuàng)建的索引作為 key，這倒是很像 Oracle 中 RBO 對于索引選擇的順序判斷邏輯（可能有些不嚴(yán)謹(jǐn)，但是因?yà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í)驗(yàn)說明 MySQL 對于相同字段的相同索引選擇方面的邏輯。
測試三
從效果上看，這兩個索引，保留一個即可，因?yàn)檫@兩個索引只是名稱不同，索引字段相同的，實(shí)際上就是相同的索引。
ALTER TABLE test ADD INDEX idx_test02 USING BTREE(UPDATED): ALTER TABLE test ADD INDEX idx_test03 USING BTREE(UPDATED);
但對于主鍵和索引的這兩個，需要用主鍵這個。因?yàn)檫@兩個最主要的區(qū)別就是主鍵除了包含索引外，還需保證唯一，而此處的索引，就是普通索引，不是唯一索引，因此從邏輯上，這兩個是不等價。但是由于主鍵包含了索引，因此可以刪除第二個索引，它屬于重復(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，情況會是相同？
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)文章
mysql千萬級數(shù)據(jù)分頁查詢性能優(yōu)化
本文給大家分享的是作者在使用mysql進(jìn)行千萬級數(shù)據(jù)量分頁查詢的時候進(jìn)行性能優(yōu)化的方法，非常不錯的一篇文章，對我們學(xué)習(xí)mysql性能優(yōu)化非常有幫助2017-11-11
mysql8報錯:ERROR?1410?(42000):?You?are?not?allowed?to?
電腦新裝的mysql,版本為8.0以上,分配權(quán)限時直接帶密碼和賬號會報錯,這篇文章主要給大家介紹了關(guān)于mysql8報錯:ERROR?1410?(42000):?You?are?not?allowed?to?create?a?user?with?GRANT的解決辦法,需要的朋友可以參考下2022-06-06
阿里云ECS云服務(wù)器(linux系統(tǒng))安裝mysql后遠(yuǎn)程連接不了(踩坑)
這篇文章主要介紹了阿里云ECS云服務(wù)器（linux系統(tǒng)）安裝mysql后遠(yuǎn)程連接不了(踩坑)，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2019-04-04
使用mysql workbench自動生成ER圖的實(shí)現(xiàn)步驟
MySQL Workbench是一款專為MySQL設(shè)計的ER/數(shù)據(jù)庫建模工具,它是著名的數(shù)據(jù)庫設(shè)計工具DBDesigne4的繼任者,可以通過MySQL Workbench設(shè)計和創(chuàng)建新的數(shù)據(jù)庫圖示,本文給大家介紹了使用mysql workbench自動生成ER圖的實(shí)現(xiàn)步驟,需要的朋友可以參考下2024-06-06
centos7.2離線安裝mysql5.7.18.tar.gz
這篇文章主要為大家詳細(xì)介紹了centos7.2離線安裝mysql5.7.18.tar.gz，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-06-06
這篇文章主要為大家詳細(xì)介紹了MySQL數(shù)據(jù)庫中表常用的一些操作方法，文中的示例代碼講解詳細(xì)，?對我們學(xué)習(xí)MySQL有一定幫助，需要的可以參考一下2022-08-08
關(guān)于Win10下MySQL5.7.17免安裝版基本配置教程(圖文詳解)
這數(shù)據(jù)庫應(yīng)用是一個應(yīng)用系統(tǒng)不可或缺的部分，關(guān)系型數(shù)據(jù)庫應(yīng)用大同小異，這里選擇MySQL作為數(shù)據(jù)庫平臺。下面通過本文給大家介紹關(guān)于Win10下MySQL5.7.17免安裝版基本配置教程(圖文詳解),需要的朋友可以參考下2017-06-06
在mysql中timestamp數(shù)據(jù)類型是一個比較特殊的數(shù)據(jù)類型，他可以自動在你不使用程序更新情況下只要你更新了記錄timestamp會自動更新時間2013-06-06
數(shù)據(jù)庫Sql實(shí)現(xiàn)截取時間段和日期實(shí)例(SQL時間截取)
在許多情況下你也許只想得到日期和時間的一部分,而不是完整的日期和時間,下面這篇文章主要給大家介紹了關(guān)于數(shù)據(jù)庫Sql實(shí)現(xiàn)截取時間段和日期(SQL時間截取)的相關(guān)資料,需要的朋友可以參考下2023-05-05
從MySQL5.7平滑升級到MySQL8.0數(shù)據(jù)庫的最佳實(shí)踐教程
這篇文章主要給大家介紹了關(guān)于從MySQL5.7平滑升級到MySQL8.0數(shù)據(jù)庫的最佳實(shí)踐,升級MySQL版本需要進(jìn)行一系列操作,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2024-07-07