【mysql】索引類型的劃分

了解mysql的索引類型的時候，我覺得按照以下4中方式劃分邏輯是比較清晰的。

創(chuàng)新互聯(lián)建站主要從事成都網(wǎng)站建設(shè)、網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)鹽津,十年網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):13518219792

1.存儲結(jié)構(gòu) 2.物理存儲 3.作用字段 4.功能

按照數(shù)據(jù)存儲的結(jié)構(gòu)可以分B樹索引和hash索引。

又稱為 BTREE 索引，目前大部分的索引都是采用 B-樹索引來存儲的。B-樹索引是一個典型的數(shù)據(jù)結(jié)構(gòu)。

基于這種樹形數(shù)據(jù)結(jié)構(gòu)，表中的每一行都會在索引上有一個對應(yīng)值。因此，在表中進行數(shù)據(jù)查詢時，可以根據(jù)索引值一步一步定位到數(shù)據(jù)所在的行。

查詢必須從索引的最左邊的列開始。

查詢不能跳過某一索引列，必須按照從左到右的順序進行匹配。

存儲引擎不能使用索引中范圍條件右邊的列。

也稱為散列索引或 HASH 索引。MySQL 目前僅有 MEMORY 存儲引擎和 HEAP 存儲引擎支持這類索引。

其中，MEMORY 存儲引擎可以支持 B-樹索引和 HASH 索引，且將 HASH 當成默認索引。

HASH 索引不是基于樹形的數(shù)據(jù)結(jié)構(gòu)查找數(shù)據(jù)，而是根據(jù)索引列對應(yīng)的哈希值的方法獲取表的記錄行。

不能使用 HASH 索引排序。

HASH 索引只支持等值比較，如“=”“IN()”或“=”。

HASH 索引不支持鍵的部分匹配，因為在計算 HASH 值的時候是通過整個索引值來計算的。

聚集索引是按照所以把數(shù)據(jù)排好序了，所以一個表只能存在一個聚集索引，其它的都是非聚集索引。

因這個特性，聚集索引是查詢數(shù)據(jù)范圍的時候有很大的性能優(yōu)勢。

但是也需要注意的是如果頻繁更新的列不適合設(shè)置為聚集索引，

原因很簡單，每次更新都需要從新排序，頻繁的更新給的壓力也大。

如果不指定的話，默認主鍵為聚集索引。

一個表里除了一個聚集索引外其他的都是非聚集索引，雖然不能把數(shù)據(jù)按照索引排序，但是索引數(shù)據(jù)是可以排序的。

所以非聚集索引查詢范圍的時候是先找索引列的范圍，再通過這個索引查詢行的值。

單列索引即一個索引只包含單個列。

組合索引指在表的多個字段組合上創(chuàng)建的索引，只有在查詢條件中使用了這些字段的左邊字段時，索引才會被使用。使用組合索引時遵循最左前綴集合

Primary Key（聚集索引）：InnoDB存儲引擎的表會存在主鍵（唯一非null），如果建表的時候沒有指定主鍵，則會使用第一非空的唯一索引作為聚集索引，否則InnoDB會自動幫你創(chuàng)建一個不可見的、長度為6字節(jié)的row_id用來作為聚集索引。

Key（普通索引）：是MySQL中的基本索引類型，允許在定義索引的列中插入重復值和空值

Unique（唯一索引）：索引列的值必須唯一，但允許有空值。若是組合索引，則列值的組合必須唯一。

主鍵索引是一種特殊的唯一索引，不允許有空值。

既不是主鍵索引也不是唯一索引的一般索引。

FULLTEXT（全文索引）：全文索引類型為FULLTEXT，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復值和空值。

全文索引可以在CHAR、VARCHAR或者TEXT類型的列上創(chuàng)建。

空間索引主要用于地理空間數(shù)據(jù)類型 GEOMETRY。

下面是 mysql官網(wǎng)給出的幾個存儲引擎和索引之間的關(guān)系 。

歡迎大家的意見和交流

email: li_mingxie@163.com

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

表button?

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單獨索引

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

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

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

button_name 無索引

查詢where條件中：

主鍵索引：

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

2、主鍵索引與單個索引同時存在，使用主鍵索引

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

聯(lián)合索引 ：

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

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

3、聯(lián)合索引實行最左側(cè)原則，即：單獨查詢條件中只有permission_id可以使用聯(lián)合索引，單獨查詢條件中只有app_id不實用聯(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)先級：主鍵索引，單列索引，組合索引

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

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

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

單個索引：

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

事例：

MySQL索引機制(詳細+原理+解析)

MySQL 前綴索引能有效減小索引文件的大小，提高索引的速度。但是前綴索引也有它的壞處：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引，也不能把它們用作覆蓋索引(Covering Index)。

集一個索引包含多個列（最左前綴匹配原則）

索引列的值必須唯一，但允許有空值

全文索引為FUllText，在定義索引的列上支持值的全文查找，允許在這些索引列中插入重復值和空值，全文索引可以在CHAR,VARCHAR,TEXT類型列上創(chuàng)建

設(shè)定主鍵后數(shù)據(jù)會自動建立索引，InnoDB為聚簇索引

即一個索引只包含單個列，一個表可以有多個單列索引

覆蓋索引是指一個查詢語句的執(zhí)行只用從所有就能夠得到，不必從數(shù)據(jù)表中讀取，覆蓋索引不是索引樹，是一個結(jié)果，當一條查詢語句符合覆蓋索引條件時候，MySQL只需要通過索引就可以返回查詢所需要的數(shù)據(jù)，這樣避免了查到索引后的回表操作，減少了I/O效率

查看索引

列名解析：

刪除索引

查看：

刪除前:

刪除后：

普通的索引，沒有什么介紹

查看:（注意和前綴索引Sub_part的區(qū)別）

當索引的列是unique的時候，會生成唯一索引，唯一索引關(guān)于null有下列兩種情況

SQLSERVER 下的唯一索引的列，允許null值，但最多允許有一個空值

MYSQL下的唯一索引的列，允許null值，并且允許多個空值

查看:

會建立兩個索引，一個非聚簇索引，一個是唯一索引

結(jié)果:

可以插入兩個空值（明人不說暗話，我喜歡MySQL）

一方面，它不會索引所有字段所有字符，會減小索引樹的大小.

另外一方面，索引只是為了區(qū)別出值，對于某些列，可能前幾位區(qū)別很大，我們就可以使用前綴索引。

一般情況下某個前綴的選擇性也是足夠高的，足以滿足查詢性能。對于BLOB，TEXT，或者很長的VARCHAR類型的列，必須使用前綴索引，因為MySQL不允許索引這些列的完整長度。

查看：

查看：

復合索引的最左前綴匹配原則 ：

對于復合索引，查詢在一定條件才會使用該索引

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

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

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

在模糊搜索中很有效，搜索全文中的某一個字段，可以參考這篇博文

:

我們先進行下面一個實驗看看InnoDB下的主鍵索引的一個現(xiàn)象。

查看:

我們插入進去的時候，數(shù)據(jù)的id都是亂序的，為什么這里最后select查詢出來的結(jié)果都是進行了排序？

這是因為InnoDB索引底層實現(xiàn)的是B+tree，B+tree具有下列的特點：

所以上面的排序是為了使用B+tree的結(jié)構(gòu) ，B+tree為了范圍搜索，將主鍵按照從小到大排序后，拆分成節(jié)點。后續(xù)還有新的節(jié)點進入的時候，和B-tree相同的操作，會進行分裂。

一般來說，聚簇索引的B+tree都是三層

InnoDB中主鍵索引一定是聚簇索引，聚簇索引一定是主鍵索引。

為什么這里輔助索引葉子結(jié)點不直接存儲數(shù)據(jù)呢？

MYISAM只有非聚簇索引，索引最終指向的都是物理地址。

Q:既然有回表的存在，那么聚簇索引的優(yōu)勢在哪里？

Q:主鍵索引作為聚簇索引需要注意什么

在查詢語句中使用LIke關(guān)鍵字進行查詢時，如果匹配字符串的第一個字符為"%"，索引不會使用。如果“%”不是在第一位，索引就會使用

多列索引是在表的多個字段上創(chuàng)建的索引，滿足最左前綴匹配原則，索引才會被使用

查詢語句只有Or關(guān)鍵字時候，如果OR前后的兩個條件都是索引，這這次查詢將會使用索引，否則Or前后有一個條件的列不是索引，那么查詢中將不使用索引

網(wǎng)站題目：mysql單列索引怎么用 mysql 多列索引
分享URL：http://www.chinadenli.net/article44/doddehe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供小程序開發(fā)、外貿(mào)建站、網(wǎng)站策劃、關(guān)鍵詞優(yōu)化、Google、做網(wǎng)站

廣告

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