關(guān)于MySQL復(fù)合索引的使用方法

MySQL的復(fù)合索引可以創(chuàng)建多個(gè)，每個(gè)復(fù)合索引可以包含一列或多列。復(fù)合索引使用的基本原則是左側(cè)對(duì)齊原則。例如，復(fù)合索引包含A，B，C字段，實(shí)際相當(dāng)于創(chuàng)建了5個(gè)索引，即：

創(chuàng)新互聯(lián)長期為上千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺(tái)，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為清流企業(yè)提供專業(yè)的網(wǎng)站建設(shè)、成都網(wǎng)站建設(shè)，清流網(wǎng)站改版等技術(shù)服務(wù)。擁有十年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。

那么問題來了，如果我們創(chuàng)建兩個(gè)復(fù)合索引，復(fù)合索引1：包含A，B，C列和復(fù)合索引2：包含B，C列，MySQL如何執(zhí)行呢？

按照正常的邏輯，和復(fù)合索引的原則，應(yīng)該能命中的索引是A_B_C_index，讓我們拭目以待吧！

結(jié)果：和上次測(cè)試的不一致，這次雖然包含ABC三個(gè)列，但命中的索引是B_C_index

重要結(jié)論：當(dāng)命中兩個(gè)或者多個(gè)不同的復(fù)合索引時(shí)，按照創(chuàng)建順序不同，MySQL會(huì)有不同策略來選取其中的一個(gè)復(fù)合索引。

什么是mysql的聯(lián)合索引，如何建立mysql的索引

聯(lián)合索引是由多個(gè)字段組成的索引。CREATE

[UNIQUE|FULLTEXT|SPATIAL]

INDEX

index_name[USING

index_type]ON

tbl_name

(index_col_name,...)index_col_name:col_name[(length)]

[ASC

|

DESC]如果你經(jīng)常要用到多個(gè)字段的多條件查詢，可以考慮建立聯(lián)合索引，一般是除第一個(gè)字段外的其它字段不經(jīng)常用于條件篩選情況，比如說a,b

兩個(gè)字段，如果你經(jīng)常用a條件或者a+b條件去查詢，而很少單獨(dú)用b條件查詢，那么可以建立a,b的聯(lián)合索引。

Mysql：聯(lián)合索引和索引合并有關(guān)系嗎

你有索引index1（a,b,c)，這是是聯(lián)合索引，也叫復(fù)合索引、多列索引；

你有索引index2（a），index3（b），index4（c），然后你的sql中有and、or或union之類的條件對(duì)三個(gè)索引分別進(jìn)行條件掃描，然后將各自的結(jié)果進(jìn)行合并，這叫索引合并，這時(shí)你用explain查看執(zhí)行計(jì)劃會(huì)看到有“index merge”字樣。

至于兩者有沒有關(guān)系，這怎么講呢，一般來說，聯(lián)合索引（復(fù)合索引）的效率要比索引合并要高，在能使用聯(lián)合索引時(shí)盡量使用聯(lián)合索引，當(dāng)然這要根據(jù)具體情況，sql中寸在“or”這樣的連接詞時(shí)，那就可能必須要使用索引合并了。

mysql索引（九）索引合并

索引合并是mysql底層為我們提供的智能算法。了解索引合并的算法，有助于我們更好的創(chuàng)建索引。

索引合并是通過多個(gè)range類型的掃描并且合并它們的結(jié)果集來檢索行的。僅合并來自單個(gè)表的索引掃描，而不是跨多個(gè)表的索引掃描。合并會(huì)產(chǎn)生底層掃描的三種形式：unions（合并）、intersections（交集）、unions-of-intersections（先取交集再合并）。

以下四個(gè)例子會(huì)產(chǎn)生索引合并：

索引合并有以下已知的局限性：

1、如果查詢語句包含一個(gè)帶有嚴(yán)重AND/OR嵌套的復(fù)雜的WHERE子句而MySQL沒有選擇最佳計(jì)劃，那么可以嘗試使用以下的標(biāo)志符轉(zhuǎn)換：

(x AND y) OR z = (x OR z) AND (y OR z)

(x OR y) AND z = (x AND z) OR (y AND z)

2、索引合并不適用于全文索引。

在 EXPLAIN 語句輸出的信息中，索引合并在type列中表現(xiàn)為“index_merge”，在這種情況下，key列包含使用的索引列表。

索引合并訪問方法有幾種算法，表現(xiàn)在 EXPLAIN 語句輸出的Extra字段中：

下面將更詳細(xì)地描述這些算法。優(yōu)化器根據(jù)各種可用選項(xiàng)的成本估計(jì)，在不同的索引合并算法和其他訪問方法之間進(jìn)行選擇。

Index Merge Intersection算法

Index Merge Intersection算法對(duì)所有使用的索引執(zhí)行同步掃描，并生成從合并的索引掃描接收到的行序列的交集。

這種算法適用于當(dāng)WHERE子句被轉(zhuǎn)換成多個(gè)使用AND連接的不同索引key上的范圍條件，且條件是以下兩種之一：

一、這種形式的N部分表達(dá)式，索引正好包括N個(gè)字段（所有索引字段都被覆蓋），N=1，N如果大于1就是復(fù)合索引：

二、InnoDB表主鍵上的任何范圍條件。

例子：

Index Merge Union算法

該算法類似于Index Merge Intersection算法，適用于當(dāng)WHERE子句被轉(zhuǎn)換成多個(gè)使用OR連接的不同索引key上的范圍條件，且條件是以下三種之一：

一、這種形式的N部分表達(dá)式，索引正好包括N個(gè)字段（所有索引字段都被覆蓋），N=1，N如果大于1就是復(fù)合索引：

二、InnoDB表主鍵上的任何范圍條件。

三、符合Index Merge Intersection算法的條件。

例子：

Index Merge Sort-Union算法

該算法適用于當(dāng)WHERE子句被轉(zhuǎn)換成多個(gè)使用OR連接的不同索引key上的范圍條件，但是不符合 Index Merge Union算法的。Index Merge Sort-Union和Index Merge Union算法的區(qū)別在于，Index Merge Sort-Union必須首先獲取所有行的行id并在返回任何行之前對(duì)它們進(jìn)行排序。

例子：

有好的建議，請(qǐng)?jiān)谙路捷斎肽愕脑u(píng)論。

歡迎訪問個(gè)人博客

mysql 聯(lián)合索引原理詳述

在一個(gè)市民信息表上，是否有必要將身份證號(hào)

和名字建立聯(lián)合索引？

假設(shè)這個(gè)市民表的定義是這樣的：

CREATE TABLE `tuser` (

`id` int(11) NOT NULL,

`id_card` varchar(32) DEFAULT NULL,

`name` varchar(32) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`ismale` tinyint(1) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `id_card` (`id_card`),

KEY `name_age` (`name`,`age`)

) ENGINE=InnoDB

我們知道，身份證號(hào)是市民的唯一標(biāo)識(shí)。也就是說，如果有根據(jù)身份證號(hào)查詢市民信息的需求，

我們只要在身份證號(hào)字段上建立索引就夠了。而再建立一個(gè)（身份證號(hào)、姓名）的聯(lián)合索引，是

不是浪費(fèi)空間？

如果現(xiàn)在有一個(gè)高頻請(qǐng)求，要根據(jù)市民的身份證號(hào)查詢他的姓名，這個(gè)聯(lián)合索引就有意義了。它

可以在這個(gè)高頻請(qǐng)求上用到覆蓋索引，不再需要回表查整行記錄，減少語句的執(zhí)行時(shí)間。

當(dāng)然，索引字段的維護(hù)總是有代價(jià)的。因此，在建立冗余索引來支持覆蓋索引時(shí)就需要權(quán)衡考慮

了。這正是業(yè)務(wù) DBA，或者稱為業(yè)務(wù)數(shù)據(jù)架構(gòu)師的工作。

最左前綴原則

看到這里你一定有一個(gè)疑問，如果為每一種查詢都設(shè)計(jì)一個(gè)索引，索引是不是太多了。如果我現(xiàn)

在要按照市民的身份證號(hào)去查他的家庭地址呢？雖然這個(gè)查詢需求在業(yè)務(wù)中出現(xiàn)的概率不高，但

總不能讓它走全表掃描吧？反過來說，單獨(dú)為一個(gè)不頻繁的請(qǐng)求創(chuàng)建一個(gè)（身份證號(hào)，地址）的

索引又感覺有點(diǎn)浪費(fèi)。應(yīng)該怎么做呢？

這里，我先和你說結(jié)論吧。B+ 樹這種索引結(jié)構(gòu)，可以利用索引的“最左前綴”，來定位記錄。

為了直觀地說明這個(gè)概念，我們用（name，age）這個(gè)聯(lián)合索引來分析。

? ? ? ? ? ? ? ? ?圖 2 （name，age）索引示意圖

可以看到，索引項(xiàng)是按照索引定義里面出現(xiàn)的字段順序排序的。

當(dāng)你的邏輯需求是查到所有名字是“張三”的人時(shí)，可以快速定位到 ID4，然后向后遍歷得到所有

需要的結(jié)果。

如果你要查的是所有名字第一個(gè)字是“張”的人，你的 SQL 語句的條件是"where name like ‘張

%’"。這時(shí)，你也能夠用上這個(gè)索引，查找到第一個(gè)符合條件的記錄是 ID3，然后向后遍歷，直

到不滿足條件為止。

可以看到，不只是索引的全部定義，只要滿足最左前綴，就可以利用索引來加速檢索。這個(gè)最左

前綴可以是聯(lián)合索引的最左 N 個(gè)字段，也可以是字符串索引的最左 M 個(gè)字符。

基于上面對(duì)最左前綴索引的說明，我們來討論一個(gè)問題：在建立聯(lián)合索引的時(shí)候，如何安排索引

內(nèi)的字段順序。

這里我們的評(píng)估標(biāo)準(zhǔn)是，索引的復(fù)用能力。因?yàn)榭梢灾С肿钭笄熬Y，所以當(dāng)已經(jīng)有了 (a,b) 這個(gè)

聯(lián)合索引后，一般就不需要單獨(dú)在 a 上建立索引了。因此，第一原則是，如果通過調(diào)整順序，可

以少維護(hù)一個(gè)索引，那么這個(gè)順序往往就是需要優(yōu)先考慮采用的。

所以現(xiàn)在你知道了，這段開頭的問題里，我們要為高頻請(qǐng)求創(chuàng)建 (身份證號(hào)，姓名）這個(gè)聯(lián)合索

引，并用這個(gè)索引支持“根據(jù)身份證號(hào)查詢地址”的需求。

那么，如果既有聯(lián)合查詢，又有基于 a、b 各自的查詢呢？查詢條件里面只有 b 的語句，是無法

使用 (a,b) 這個(gè)聯(lián)合索引的，這時(shí)候你不得不維護(hù)另外一個(gè)索引，也就是說你需要同時(shí)維護(hù)

(a,b)、(b) 這兩個(gè)索引。

這時(shí)候，我們要考慮的原則就是空間了。比如上面這個(gè)市民表的情況，name 字段是比 age 字段

大的 ，那我就建議你創(chuàng)建一個(gè)（name,age) 的聯(lián)合索引和一個(gè) (age) 的單字段索引。

網(wǎng)站題目：mysql怎么合并索引 索引合并怎么回事
轉(zhuǎn)載來于：http://m.rwnh.cn/article8/doohdop.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)公司、網(wǎng)頁設(shè)計(jì)公司、App開發(fā)、企業(yè)建站、軟件開發(fā)、網(wǎng)站策劃

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)