mysql 主鍵索引，聯(lián)合索引，單列索引使用場(chǎng)景

表button?

創(chuàng)新互聯(lián)主要從事網(wǎng)頁(yè)設(shè)計(jì)、PC網(wǎng)站建設(shè)（電腦版網(wǎng)站建設(shè)）、wap網(wǎng)站建設(shè)（手機(jī)版網(wǎng)站建設(shè)）、成都響應(yīng)式網(wǎng)站建設(shè)公司、程序開(kāi)發(fā)、網(wǎng)站優(yōu)化、微網(wǎng)站、小程序制作等，憑借多年來(lái)在互聯(lián)網(wǎng)的打拼，我們?cè)诨ヂ?lián)網(wǎng)網(wǎng)站建設(shè)行業(yè)積累了豐富的成都做網(wǎng)站、成都網(wǎng)站制作、網(wǎng)站設(shè)計(jì)、網(wǎng)絡(luò)營(yíng)銷經(jīng)驗(yàn)，集策劃、開(kāi)發(fā)、設(shè)計(jì)、營(yíng)銷、管理等多方位專業(yè)化運(yùn)作于一體。

CREATE TABLE `button` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,? --主鍵索引

`button_name` varchar(45) NOT NULL COMMENT '功能名稱',

`app_id` bigint(20) NOT NULL,

`permission_id` bigint(20) DEFAULT NULL,? -- permission_id 和 app_id 聯(lián)合索引。

`api_id` bigint(20) NOT NULL, --api_id單獨(dú)索引

PRIMARY KEY (`id`),

KEY `index_app_permission_lianhe` (`permission_id`,`app_id`) USING BTREE,

KEY `index_api_id_dange` (`api_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

主鍵索引，單獨(dú)索引，組合索引使用場(chǎng)景及優(yōu)化

表button 有3個(gè)索引，分別是：id主鍵，聯(lián)合索引(permission_id,app_id)，api_id(單列索引)

button_name 無(wú)索引

查詢where條件中：

主鍵索引：

1、主鍵索引與聯(lián)合索引同時(shí)存在，使用主鍵索引

2、主鍵索引與單個(gè)索引同時(shí)存在，使用主鍵索引

結(jié)論：只要主鍵索引在，使用主鍵索引。

聯(lián)合索引 ：

1、聯(lián)合索引與單列索引列 同時(shí)存在，使用單列索引

2、聯(lián)合索引中列順序顛倒無(wú)影響。

3、聯(lián)合索引實(shí)行最左側(cè)原則，即：?jiǎn)为?dú)查詢條件中只有permission_id可以使用聯(lián)合索引，單獨(dú)查詢條件中只有app_id不實(shí)用聯(lián)合索引。

4、如果查詢條件中只有app_id，但是select 條件中有 permission_id，則也使用聯(lián)合索引。

5、select id,app_id from button where app_id=1001; 使用聯(lián)合索引

6、explain select id,app_id,button_name from button where app_id=1001;不使用聯(lián)合索引

結(jié)論：索引優(yōu)先級(jí)：主鍵索引，單列索引，組合索引

聯(lián)合索引中遵從最左側(cè)列原則。

當(dāng)查詢條件和返回結(jié)果中僅僅包含聯(lián)合索引中索引項(xiàng)，也使用聯(lián)合索引。如第4條。

當(dāng)查詢條件中出現(xiàn)聯(lián)合索引中非最左側(cè)索引列，返回結(jié)果中含義聯(lián)合索引中的列或者主鍵則也使用聯(lián)合索引。

單個(gè)索引：

1、查詢條件中有單列索引，則使用，無(wú)不使用。

事例：

MySQL中如何設(shè)置唯一索引，聯(lián)合索引？

1.UNIQUE　關(guān)鍵字建唯一索引\x0d\x0amysql CREATE TABLE `wb_blog` ( \x0d\x0a - `id` smallint(8) unsigned NOT NULL, \x0d\x0a - `catid` smallint(5) unsigned NOT NULL DEFAULT '0', \x0d\x0a - `title` varchar(80) NOT NULL DEFAULT '', \x0d\x0a - `content` text NOT NULL, \x0d\x0a - PRIMARY KEY (`id`), \x0d\x0a - UNIQUE KEY `catename` (`catid`) \x0d\x0a - ) ; \x0d\x0a如果建好表了，可以用以下語(yǔ)句建\x0d\x0a mysql CREATE UNIQUE INDEX catename ON wb_blog(catid); \x0d\x0a\x0d\x0a2.聯(lián)合索引\x0d\x0aALTER TABLE `tasks`\x0d\x0aADD INDEX `testabc` (`title`, `created`) ;\x0d\x0a\x0d\x0a3聯(lián)合唯一索引（假設(shè)有這個(gè)需求，在同一天內(nèi)不能建兩個(gè)tiltle一樣的任務(wù)）\x0d\x0aALTER TABLE `tasks`\x0d\x0aADD UNIQUE INDEX `testabc` (`title`, `created`) ;\x0d\x0a\x0d\x0a數(shù)據(jù)庫(kù)建索引的科學(xué)性事關(guān)數(shù)據(jù)庫(kù)性能，索引也不是越多越好。

深入淺析Mysql聯(lián)合索引最左匹配原則

之前在網(wǎng)上看到過(guò)很多關(guān)于mysql聯(lián)合索引最左前綴匹配的文章，自以為就了解了其原理，最近面試時(shí)和面試官交流，發(fā)現(xiàn)遺漏了些東西，這里自己整理一下這方面的內(nèi)容。

最左前綴匹配原則

在mysql建立聯(lián)合索引時(shí)會(huì)遵循最左前綴匹配的原則，即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開(kāi)始匹配，示例：

對(duì)列col1、列col2和列col3建一個(gè)聯(lián)合索引

KEY test_col1_col2_col3 on test(col1,col2,col3);

聯(lián)合索引 test_col1_col2_col3 實(shí)際建立了 (col1)、(col1,col2)、(col,col2,col3) 三個(gè)索引。

SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”

上面這個(gè)查詢語(yǔ)句執(zhí)行時(shí)會(huì)依照最左前綴匹配原則，檢索時(shí)會(huì)使用索引(col1,col2)進(jìn)行數(shù)據(jù)匹配。

注意

索引的字段可以是任意順序的，如：

SELECT * FROM test WHERE col1=“1” AND clo2=“2”

SELECT * FROM test WHERE col2=“2” AND clo1=“1”

這兩個(gè)查詢語(yǔ)句都會(huì)用到索引(col1,col2)，mysql創(chuàng)建聯(lián)合索引的規(guī)則是首先會(huì)對(duì)聯(lián)合合索引的最左邊的，也就是第一個(gè)字段col1的數(shù)據(jù)進(jìn)行排序，在第一個(gè)字段的排序基礎(chǔ)上，然后再對(duì)后面第二個(gè)字段col2進(jìn)行排序。其實(shí)就相當(dāng)于實(shí)現(xiàn)了類似 order by col1 col2這樣一種排序規(guī)則。

有人會(huì)疑惑第二個(gè)查詢語(yǔ)句不符合最左前綴匹配：首先可以肯定是兩個(gè)查詢語(yǔ)句都保函索引(col1,col2)中的col1、col2兩個(gè)字段，只是順序不一樣，查詢條件一樣，最后所查詢的結(jié)果肯定是一樣的。既然結(jié)果是一樣的，到底以何種順序的查詢方式最好呢？此時(shí)我們可以借助mysql查詢優(yōu)化器explain，explain會(huì)糾正sql語(yǔ)句該以什么樣的順序執(zhí)行效率最高，最后才生成真正的執(zhí)行計(jì)劃。

減少開(kāi)銷 。建一個(gè)聯(lián)合索引(col1,col2,col3)，實(shí)際相當(dāng)于建了(col1),(col1,col2),(col1,col2,col3)三個(gè)索引。每多一個(gè)索引，都會(huì)增加寫操作的開(kāi)銷和磁盤空間的開(kāi)銷。對(duì)于大量數(shù)據(jù)的表，使用聯(lián)合索引會(huì)大大的減少開(kāi)銷！

覆蓋索引 。對(duì)聯(lián)合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通過(guò)遍歷索引取得數(shù)據(jù)，而無(wú)需回表，這減少了很多的隨機(jī)io操作。減少io操作，特別的隨機(jī)io其實(shí)是dba主要的優(yōu)化策略。所以，在真正的實(shí)際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一。

效率高 。索引列越多，通過(guò)索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設(shè)假設(shè)每個(gè)條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過(guò)該索引能篩選出1000W10%=100w條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù)，然后再排序，再分頁(yè)；如果是聯(lián)合索引，通過(guò)索引篩選出1000w10% 10% *10%=1w，效率提升可想而知！

引申

對(duì)于聯(lián)合索引(col1,col2,col3)，查詢語(yǔ)句 SELECT * FROM test WHERE col2=2; 是否能夠觸發(fā)索引？

大多數(shù)人都會(huì)說(shuō)NO，實(shí)際上卻是YES。

原因：

EXPLAIN SELECT * FROM test WHERE col2=2;

EXPLAIN SELECT * FROM test WHERE col1=1;

觀察上述兩個(gè)explain結(jié)果中的type字段。查詢中分別是：

index： 這種類型表示mysql會(huì)對(duì)整個(gè)該索引進(jìn)行掃描。要想用到這種類型的索引，對(duì)這個(gè)索引并無(wú)特別要求，只要是索引，或者某個(gè)聯(lián)合索引的一部分，mysql都可能會(huì)采用index類型的方式掃描。但是呢，缺點(diǎn)是效率不高，mysql會(huì)從索引中的第一個(gè)數(shù)據(jù)一個(gè)個(gè)的查找到最后一個(gè)數(shù)據(jù)，直到找到符合判斷條件的某個(gè)索引。所以，上述語(yǔ)句會(huì)觸發(fā)索引。

ref： 這種類型表示mysql會(huì)根據(jù)特定的算法快速查找到某個(gè)符合條件的索引，而不是會(huì)對(duì)索引中每一個(gè)數(shù)據(jù)都進(jìn)行一一的掃描判斷，也就是所謂你平常理解的使用索引查詢會(huì)更快的取出數(shù)據(jù)。而要想實(shí)現(xiàn)這種查找，索引卻是有要求的，要實(shí)現(xiàn)這種能快速查找的算法，索引就要滿足特定的數(shù)據(jù)結(jié)構(gòu)。簡(jiǎn)單說(shuō)，也就是索引字段的數(shù)據(jù)必須是有序的，才能實(shí)現(xiàn)這種類型的查找，才能利用到索引。

以上所述是我給大家介紹的Mysql聯(lián)合索引最左匹配原則，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，我會(huì)及時(shí)回復(fù)大家的。

《 兩個(gè)月拿到N個(gè)offer，看看我是如何做到的 》

《 面試總結(jié)：2019年最全面試題資料學(xué)習(xí)大全—（含答案） 》

《 淘寶面試回來(lái)，想對(duì)程序員們談?wù)? 》

《 看過(guò)太多大廠面試題，其實(shí)考的無(wú)非是這 3 點(diǎn)能力 》