建站
咨詢
售后
建站咨詢
新聞中心
索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分)，它們包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針。
更通俗的說(shuō)，數(shù)據(jù)庫(kù)索引好比是一本書(shū)前面的目錄，在查找內(nèi)容之前可以先在目錄中查找索引位置，以此快速定位查詢數(shù)據(jù)。
索引的分類
單值（列）索引：即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引。
唯一索引：索引列的值必須唯一，但允許有空值。 -> 主鍵是特殊的唯一索引，因?yàn)槠洳辉试S有空值。
復(fù)合（組合）索引：即一個(gè)索引包含多個(gè)列。
全文索引：FULLTEXTl類型索引,可以在CHAR，VARCHAR，或者TEXT類型的列上創(chuàng)建，僅MyISAM支持。
空間索引：對(duì)空間數(shù)據(jù)庫(kù)的支持，GIS系統(tǒng)什么的。。。。哎呀，不看這里了，看起來(lái)比較吊。
索引結(jié)構(gòu)
各存儲(chǔ)引擎支持的索引結(jié)構(gòu)如下表（摘自MariaDB的KB）：
其中BTREE是默認(rèn)的索引結(jié)構(gòu)，而對(duì)于MEMORY存儲(chǔ)引擎，HASH則是默認(rèn)的。
B-TREE：支持 >, >=, =, >=, 操作符，
R-tree：空間索引上的數(shù)據(jù)結(jié)構(gòu)，不看了…..太難。
哪些情況需要?jiǎng)?chuàng)建索引
-
主鍵自動(dòng)建立唯一索引
-
頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
-
查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
-
頻繁更新的字段不適合建立索引，因?yàn)槊看胃虏粏螁问歉铝擞涗涍€會(huì)更新索引
-
WHERE條件里用不到的字段不創(chuàng)建索引
-
單鍵/組合索引的選擇問(wèn)題，who?(在高并發(fā)下傾向創(chuàng)建組合索引)
-
查詢中排序的字段，排序的字段若通過(guò)索引去訪問(wèn)將大大提高排序速度
-
查詢中統(tǒng)計(jì)或者分組字段
哪些情況不要?jiǎng)?chuàng)建索引
-
表記錄太少
-
經(jīng)常增刪改的表
提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE、和DELETE。
因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。
數(shù)據(jù)重復(fù)且分布平均的表字段，因此應(yīng)該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)建立索引。
注意，如果某個(gè)數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒(méi)有太大的實(shí)際效果。
最左前綴原則
這里找到一篇好文章，摘自知乎：mysql索引最左匹配原則的理解? 作者：沈杰
表結(jié)構(gòu)如下：有三個(gè)字段，分別是id,name,cid
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_cid_INX` (`name`,`cid`),
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
索引方面：id是主鍵，(name,cid)是一個(gè)多列索引。
下面是你有疑問(wèn)的兩個(gè)查詢：
EXPLAIN SELECT * FROM student WHERE cid=1;
Mariadb學(xué)習(xí)總結(jié)（六）：索引Mariadb學(xué)習(xí)總結(jié)（六）：索引
EXPLAIN SELECT * FROM student WHERE cid=1 AND name='小紅';
Mariadb學(xué)習(xí)總結(jié)（六）：索引Mariadb學(xué)習(xí)總結(jié)（六）：索引
你的疑問(wèn)是：sql查詢用到索引的條件是必須要遵守最左前綴原則，為什么上面兩個(gè)查詢還能用到索引？
講上面問(wèn)題之前，我先補(bǔ)充一些知識(shí)，因?yàn)槲矣X(jué)得你對(duì)索引理解是狹隘的： 上述你的兩個(gè)查詢的explain結(jié)果中顯示用到索引的情況類型是不一樣的。,可觀察explain結(jié)果中的type字段。
你的查詢中分別是： \1. type: index \2. type: ref
解釋： index：這種類型表示是mysql會(huì)對(duì)整個(gè)該索引進(jìn)行掃描。要想用到這種類型的索引，對(duì)這個(gè)索引并無(wú)特別要求，只要是索引，或者某個(gè)復(fù)合索引的一部分，mysql都可能會(huì)采用index類型的方式掃描。但是呢，缺點(diǎn)是效率不高，mysql會(huì)從索引中的第一個(gè)數(shù)據(jù)一個(gè)個(gè)的查找到最后一個(gè)數(shù)據(jù)，直到找到符合判斷條件的某個(gè)索引。
所以：對(duì)于你的第一條語(yǔ)句：
EXPLAIN SELECT * FROM student WHERE cid=1;
判斷條件是cid=1,而cid是(name,cid)復(fù)合索引的一部分，沒(méi)有問(wèn)題，可以進(jìn)行index類型的索引掃描方式。explain顯示結(jié)果使用到了索引，是index類型的方式。
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)這種類型的查找，才能利用到索引。
有些了解的人可能會(huì)問(wèn)，索引不都是一個(gè)有序排列的數(shù)據(jù)結(jié)構(gòu)么。不過(guò)答案說(shuō)的還不夠完善，那只是針對(duì)單個(gè)索引，而復(fù)合索引的情況有些同學(xué)可能就不太了解了。
下面就說(shuō)下復(fù)合索引：
以該表的(name,cid)復(fù)合索引為例,它內(nèi)部結(jié)構(gòu)簡(jiǎn)單說(shuō)就是下面這樣排列的：
mysql創(chuàng)建復(fù)合索引的規(guī)則是首先會(huì)對(duì)復(fù)合索引的最左邊的，也就是第一個(gè)name字段的數(shù)據(jù)進(jìn)行排序，在第一個(gè)字段的排序基礎(chǔ)上，然后再對(duì)后面第二個(gè)的cid字段進(jìn)行排序。其實(shí)就相當(dāng)于實(shí)現(xiàn)了類似 order by name cid這樣一種排序規(guī)則。
所以：第一個(gè)name字段是絕對(duì)有序的，而第二字段就是無(wú)序的了。所以通常情況下，直接使用第二個(gè)cid字段進(jìn)行條件判斷是用不到索引的，當(dāng)然，可能會(huì)出現(xiàn)上面的使用index類型的索引。這就是所謂的mysql為什么要強(qiáng)調(diào)最左前綴原則的原因。
那么什么時(shí)候才能用到呢?當(dāng)然是cid字段的索引數(shù)據(jù)也是有序的情況下才能使用咯，什么時(shí)候才是有序的呢？觀察可知，當(dāng)然是在name字段是等值匹配的情況下，cid才是有序的。發(fā)現(xiàn)沒(méi)有，觀察兩個(gè)name名字為 c 的cid字段是不是有序的呢。從上往下分別是4 5。 這也就是mysql索引規(guī)則中要求復(fù)合索引要想使用第二個(gè)索引，必須先使用第一個(gè)索引的原因。（而且第一個(gè)索引必須是等值匹配）。
所以對(duì)于你的這條sql查詢：
EXPLAIN SELECT * FROM student WHERE cid=1 AND name='小紅';
沒(méi)有錯(cuò)，而且復(fù)合索引中的兩個(gè)索引字段都能很好的利用到了！因?yàn)檎Z(yǔ)句中最左面的name字段進(jìn)行了等值匹配，所以cid是有序的，也可以利用到索引了。
你可能會(huì)問(wèn)：我建的索引是(name,cid)。而我查詢的語(yǔ)句是cid=1 AND name=’小紅’; 我是先查詢cid，再查詢name的，不是先從最左面查的呀？
好吧，我再解釋一下這個(gè)問(wèn)題：首先可以肯定的是把條件判斷反過(guò)來(lái)變成這樣 name=’小紅’ and cid=1; 最后所查詢的結(jié)果是一樣的。那么問(wèn)題產(chǎn)生了？既然結(jié)果是一樣的，到底以何種順序的查詢方式最好呢？所以，而此時(shí)那就是我們的mysql查詢優(yōu)化器該登場(chǎng)了，mysql查詢優(yōu)化器會(huì)判斷糾正這條sql語(yǔ)句該以什么樣的順序執(zhí)行效率最高，最后才生成真正的執(zhí)行計(jì)劃。
所以，當(dāng)然是我們能盡量的利用到索引時(shí)的查詢順序效率最高咯，所以mysql查詢優(yōu)化器會(huì)最終以這種順序進(jìn)行查詢執(zhí)行。
贊一個(gè)~不請(qǐng)自轉(zhuǎn)，感謝原作者沈杰，如有侵權(quán)請(qǐng)告知。
創(chuàng)建索引的語(yǔ)法
創(chuàng)建表的時(shí)候創(chuàng)建索引
CREATE TABLE tbl_name(Column_def1,Column_def2,Columndef3,.....index_def);
index_def:
{INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
例子，創(chuàng)建一張表，三個(gè)字段：id，name，gid 為name創(chuàng)建索引：
MariaDB [mydb]> CREATE TABLE test(
-> id int(10) PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(10) NOT NULL,
-> gid int(3),
-> INDEX name_idx (name(5))
-> );
其中需要說(shuō)明的是，name(5)就是為name列創(chuàng)建索引，且僅取前5個(gè)字符進(jìn)行索引。
更多創(chuàng)建表時(shí)的SQL語(yǔ)法可參考：https://mariadb.com/kb/en/library/create-table/#indexes
為存在的表創(chuàng)建索引
CREATE INDEX語(yǔ)法如下：
CREATE [OR REPLACE] [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX
[IF NOT EXISTS] index_name
[index_type]
ON tbl_name (index_col_name,...)
[WAIT n | NOWAIT]
[index_option]
[algorithm_option | lock_option] ...
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH | RTREE}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
algorithm_option:
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
lock_option:
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
先查看下上面創(chuàng)建的索引：
MariaDB [mydb]> SHOW INDEX FROM test\G;
*************************** 1. row *************************** //主鍵會(huì)創(chuàng)建一個(gè)索引
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row *************************** //這個(gè)是我們自己創(chuàng)建的索引
Table: test
Non_unique: 1
Key_name: name_idx
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: 5
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
現(xiàn)在，我們把之前創(chuàng)建的索引刪掉，然后創(chuàng)建一個(gè)多列索引(name,gid)
DROP INDEX name_idx ON test;//刪除索引
CREATE INDEX name_gid_idx ON test (name(5),gid DESC); //創(chuàng)建了多列索引，且name取前5，而gid則倒敘排列
CREATE OR REPLACE INDEX name_gid_idx ON test(name(5),gid); //修改了上面創(chuàng)建的這個(gè)索引
唯一索引
創(chuàng)建唯一約束時(shí)會(huì)自動(dòng)創(chuàng)建唯一索引，但創(chuàng)建唯一索引時(shí)則不會(huì)創(chuàng)建唯一約束，且唯一索引能做到和唯一約束一樣的效果。
新聞標(biāo)題：詳解Mariadb索引
URL網(wǎng)址：http://m.5511xx.com/article/cdcdopp.html
