建站
咨詢
售后
建站咨詢
新聞中心
索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分)，它們包含著對數(shù)據(jù)表里所有記錄的引用指針。
更通俗的說，數(shù)據(jù)庫索引好比是一本書前面的目錄，在查找內容之前可以先在目錄中查找索引位置，以此快速定位查詢數(shù)據(jù)。
索引的分類
單值（列）索引：即一個索引只包含單個列，一個表可以有多個單列索引。
唯一索引：索引列的值必須唯一，但允許有空值。 -> 主鍵是特殊的唯一索引，因為其不允許有空值。
復合（組合）索引：即一個索引包含多個列。
全文索引：FULLTEXTl類型索引,可以在CHAR，VARCHAR，或者TEXT類型的列上創(chuàng)建，僅MyISAM支持。
空間索引：對空間數(shù)據(jù)庫的支持，GIS系統(tǒng)什么的。。。。哎呀，不看這里了，看起來比較吊。
索引結構
各存儲引擎支持的索引結構如下表（摘自MariaDB的KB）：
其中BTREE是默認的索引結構，而對于MEMORY存儲引擎，HASH則是默認的。
B-TREE：支持 >, >=, =, >=, 操作符，
R-tree：空間索引上的數(shù)據(jù)結構，不看了…..太難。
哪些情況需要創(chuàng)建索引
-
主鍵自動建立唯一索引
-
頻繁作為查詢條件的字段應該創(chuàng)建索引
-
查詢中與其他表關聯(lián)的字段，外鍵關系建立索引
-
頻繁更新的字段不適合建立索引，因為每次更新不單單是更新了記錄還會更新索引
-
WHERE條件里用不到的字段不創(chuàng)建索引
-
單鍵/組合索引的選擇問題，who?(在高并發(fā)下傾向創(chuàng)建組合索引)
-
查詢中排序的字段，排序的字段若通過索引去訪問將大大提高排序速度
-
查詢中統(tǒng)計或者分組字段
哪些情況不要創(chuàng)建索引
-
表記錄太少
-
經常增刪改的表
提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE、和DELETE。
因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。
數(shù)據(jù)重復且分布平均的表字段，因此應該只為最經常查詢和最經常排序的數(shù)據(jù)建立索引。
注意，如果某個數(shù)據(jù)列包含許多重復的內容，為它建立索引就沒有太大的實際效果。
最左前綴原則
這里找到一篇好文章，摘自知乎：mysql索引最左匹配原則的理解? 作者：沈杰
表結構如下：有三個字段，分別是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)是一個多列索引。
下面是你有疑問的兩個查詢：
EXPLAIN SELECT * FROM student WHERE cid=1;
Mariadb學習總結（六）：索引Mariadb學習總結（六）：索引
EXPLAIN SELECT * FROM student WHERE cid=1 AND name='小紅';
Mariadb學習總結（六）：索引Mariadb學習總結（六）：索引
你的疑問是：sql查詢用到索引的條件是必須要遵守最左前綴原則，為什么上面兩個查詢還能用到索引？
講上面問題之前，我先補充一些知識，因為我覺得你對索引理解是狹隘的： 上述你的兩個查詢的explain結果中顯示用到索引的情況類型是不一樣的。,可觀察explain結果中的type字段。
你的查詢中分別是： \1. type: index \2. type: ref
解釋： index：這種類型表示是mysql會對整個該索引進行掃描。要想用到這種類型的索引，對這個索引并無特別要求，只要是索引，或者某個復合索引的一部分，mysql都可能會采用index類型的方式掃描。但是呢，缺點是效率不高，mysql會從索引中的第一個數(shù)據(jù)一個個的查找到最后一個數(shù)據(jù)，直到找到符合判斷條件的某個索引。
所以：對于你的第一條語句：
EXPLAIN SELECT * FROM student WHERE cid=1;
判斷條件是cid=1,而cid是(name,cid)復合索引的一部分，沒有問題，可以進行index類型的索引掃描方式。explain顯示結果使用到了索引，是index類型的方式。
ref：這種類型表示mysql會根據(jù)特定的算法快速查找到某個符合條件的索引，而不是會對索引中每一個數(shù)據(jù)都進行一 一的掃描判斷，也就是所謂你平常理解的使用索引查詢會更快的取出數(shù)據(jù)。而要想實現(xiàn)這種查找，索引卻是有要求的，要實現(xiàn)這種能快速查找的算法，索引就要滿足特定的數(shù)據(jù)結構。簡單說，也就是索引字段的數(shù)據(jù)必須是有序的，才能實現(xiàn)這種類型的查找，才能利用到索引。
有些了解的人可能會問，索引不都是一個有序排列的數(shù)據(jù)結構么。不過答案說的還不夠完善，那只是針對單個索引，而復合索引的情況有些同學可能就不太了解了。
下面就說下復合索引：
以該表的(name,cid)復合索引為例,它內部結構簡單說就是下面這樣排列的：
mysql創(chuàng)建復合索引的規(guī)則是首先會對復合索引的最左邊的，也就是第一個name字段的數(shù)據(jù)進行排序，在第一個字段的排序基礎上，然后再對后面第二個的cid字段進行排序。其實就相當于實現(xiàn)了類似 order by name cid這樣一種排序規(guī)則。
所以：第一個name字段是絕對有序的，而第二字段就是無序的了。所以通常情況下，直接使用第二個cid字段進行條件判斷是用不到索引的，當然，可能會出現(xiàn)上面的使用index類型的索引。這就是所謂的mysql為什么要強調最左前綴原則的原因。
那么什么時候才能用到呢?當然是cid字段的索引數(shù)據(jù)也是有序的情況下才能使用咯，什么時候才是有序的呢？觀察可知，當然是在name字段是等值匹配的情況下，cid才是有序的。發(fā)現(xiàn)沒有，觀察兩個name名字為 c 的cid字段是不是有序的呢。從上往下分別是4 5。 這也就是mysql索引規(guī)則中要求復合索引要想使用第二個索引，必須先使用第一個索引的原因。（而且第一個索引必須是等值匹配）。
所以對于你的這條sql查詢：
EXPLAIN SELECT * FROM student WHERE cid=1 AND name='小紅';
沒有錯，而且復合索引中的兩個索引字段都能很好的利用到了！因為語句中最左面的name字段進行了等值匹配，所以cid是有序的，也可以利用到索引了。
你可能會問：我建的索引是(name,cid)。而我查詢的語句是cid=1 AND name=’小紅’; 我是先查詢cid，再查詢name的，不是先從最左面查的呀？
好吧，我再解釋一下這個問題：首先可以肯定的是把條件判斷反過來變成這樣 name=’小紅’ and cid=1; 最后所查詢的結果是一樣的。那么問題產生了？既然結果是一樣的，到底以何種順序的查詢方式最好呢？所以，而此時那就是我們的mysql查詢優(yōu)化器該登場了，mysql查詢優(yōu)化器會判斷糾正這條sql語句該以什么樣的順序執(zhí)行效率最高，最后才生成真正的執(zhí)行計劃。
所以，當然是我們能盡量的利用到索引時的查詢順序效率最高咯，所以mysql查詢優(yōu)化器會最終以這種順序進行查詢執(zhí)行。
贊一個~不請自轉，感謝原作者沈杰，如有侵權請告知。
創(chuàng)建索引的語法
創(chuàng)建表的時候創(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)建一張表，三個字段：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))
-> );
其中需要說明的是，name(5)就是為name列創(chuàng)建索引，且僅取前5個字符進行索引。
更多創(chuàng)建表時的SQL語法可參考：https://mariadb.com/kb/en/library/create-table/#indexes
為存在的表創(chuàng)建索引
CREATE INDEX語法如下：
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 *************************** //主鍵會創(chuàng)建一個索引
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 *************************** //這個是我們自己創(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)建一個多列索引(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)建的這個索引
唯一索引
創(chuàng)建唯一約束時會自動創(chuàng)建唯一索引，但創(chuàng)建唯一索引時則不會創(chuàng)建唯一約束，且唯一索引能做到和唯一約束一樣的效果。
分享文章：詳解Mariadb索引
轉載來源：http://www.dlmjj.cn/article/cdcdopp.html
