Mariadb索引怎么創(chuàng)建

索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分)，它們包含著對數(shù)據(jù)表里所有記錄的引用指針。

更通俗的說，數(shù)據(jù)庫索引好比是一本書前面的目錄，在查找內(nèi)容之前可以先在目錄中查找索引位置，以此快速定位查詢數(shù)據(jù)。

索引的分類

單值（列）索引：即一個索引只包含單個列，一個表可以有多個單列索引。

唯一索引：索引列的值必須唯一，但允許有空值。 -> 主鍵是特殊的唯一索引，因為其不允許有空值。

復(fù)合（組合）索引：即一個索引包含多個列。

全文索引：FULLTEXTl類型索引,可以在CHAR，VARCHAR，或者TEXT類型的列上創(chuàng)建，僅MyISAM支持。

空間索引：對空間數(shù)據(jù)庫的支持，GIS系統(tǒng)什么的。。。。哎呀，不看這里了，看起來比較吊。

索引結(jié)構(gòu)

各存儲引擎支持的索引結(jié)構(gòu)如下表（摘自MariaDB的KB）：

其中BTREE是默認的索引結(jié)構(gòu)，而對于MEMORY存儲引擎，HASH則是默認的。

B-TREE：支持 >, >=, =, >=, 操作符，

R-tree：空間索引上的數(shù)據(jù)結(jié)構(gòu)，不看了…..太難。

哪些情況需要創(chuàng)建索引

1. 主鍵自動建立唯一索引
2. 頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
3. 查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
4. 頻繁更新的字段不適合建立索引，因為每次更新不單單是更新了記錄還會更新索引
5. WHERE條件里用不到的字段不創(chuàng)建索引
6. 單鍵/組合索引的選擇問題，who?(在高并發(fā)下傾向創(chuàng)建組合索引)
7. 查詢中排序的字段，排序的字段若通過索引去訪問將大大提高排序速度
8. 查詢中統(tǒng)計或者分組字段

哪些情況不要創(chuàng)建索引

1. 表記錄太少
2. 經(jīng)常增刪改的表

提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE、和DELETE。

因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。

數(shù)據(jù)重復(fù)且分布平均的表字段，因此應(yīng)該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)建立索引。

注意，如果某個數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒有太大的實際效果。

最左前綴原則

這里找到一篇好文章，摘自知乎：mysql索引最左匹配原則的理解? 作者：沈杰

表結(jié)構(gòu)如下：有三個字段，分別是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;

EXPLAIN SELECT * FROM student WHERE   cid=1 AND name='小紅';

你的疑問是：sql查詢用到索引的條件是必須要遵守最左前綴原則，為什么上面兩個查詢還能用到索引？

講上面問題之前，我先補充一些知識，因為我覺得你對索引理解是狹隘的： 上述你的兩個查詢的explain結(jié)果中顯示用到索引的情況類型是不一樣的。,可觀察explain結(jié)果中的type字段。

你的查詢中分別是： \1. type: index \2. type: ref

解釋： index：這種類型表示是mysql會對整個該索引進行掃描。要想用到這種類型的索引，對這個索引并無特別要求，只要是索引，或者某個復(fù)合索引的一部分，mysql都可能會采用index類型的方式掃描。但是呢，缺點是效率不高，mysql會從索引中的第一個數(shù)據(jù)一個個的查找到最后一個數(shù)據(jù)，直到找到符合判斷條件的某個索引。

所以：對于你的第一條語句：

EXPLAIN SELECT * FROM student WHERE   cid=1;

判斷條件是cid=1,而cid是(name,cid)復(fù)合索引的一部分，沒有問題，可以進行index類型的索引掃描方式。explain顯示結(jié)果使用到了索引，是index類型的方式。

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

有些了解的人可能會問，索引不都是一個有序排列的數(shù)據(jù)結(jié)構(gòu)么。不過答案說的還不夠完善，那只是針對單個索引，而復(fù)合索引的情況有些同學(xué)可能就不太了解了。

下面就說下復(fù)合索引：

以該表的(name,cid)復(fù)合索引為例,它內(nèi)部結(jié)構(gòu)簡單說就是下面這樣排列的：

mysql創(chuàng)建復(fù)合索引的規(guī)則是首先會對復(fù)合索引的最左邊的，也就是第一個name字段的數(shù)據(jù)進行排序，在第一個字段的排序基礎(chǔ)上，然后再對后面第二個的cid字段進行排序。其實就相當于實現(xiàn)了類似 order by name cid這樣一種排序規(guī)則。

所以：第一個name字段是絕對有序的，而第二字段就是無序的了。所以通常情況下，直接使用第二個cid字段進行條件判斷是用不到索引的，當然，可能會出現(xiàn)上面的使用index類型的索引。這就是所謂的mysql為什么要強調(diào)最左前綴原則的原因。

那么什么時候才能用到呢?當然是cid字段的索引數(shù)據(jù)也是有序的情況下才能使用咯，什么時候才是有序的呢？觀察可知，當然是在name字段是等值匹配的情況下，cid才是有序的。發(fā)現(xiàn)沒有，觀察兩個name名字為 c 的cid字段是不是有序的呢。從上往下分別是4 5。 這也就是mysql索引規(guī)則中要求復(fù)合索引要想使用第二個索引，必須先使用第一個索引的原因。（而且第一個索引必須是等值匹配）。

所以對于你的這條sql查詢：

EXPLAIN SELECT * FROM student WHERE   cid=1 AND name='小紅';

沒有錯，而且復(fù)合索引中的兩個索引字段都能很好的利用到了！因為語句中最左面的name字段進行了等值匹配，所以cid是有序的，也可以利用到索引了。

你可能會問：我建的索引是(name,cid)。而我查詢的語句是cid=1 AND name=’小紅’; 我是先查詢cid，再查詢name的，不是先從最左面查的呀？

好吧，我再解釋一下這個問題：首先可以肯定的是把條件判斷反過來變成這樣 name=’小紅’ and cid=1; 最后所查詢的結(jié)果是一樣的。那么問題產(chǎn)生了？既然結(jié)果是一樣的，到底以何種順序的查詢方式最好呢？所以，而此時那就是我們的mysql查詢優(yōu)化器該登場了，mysql查詢優(yōu)化器會判斷糾正這條sql語句該以什么樣的順序執(zhí)行效率最高，最后才生成真正的執(zhí)行計劃。

所以，當然是我們能盡量的利用到索引時的查詢順序效率最高咯，所以mysql查詢優(yōu)化器會最終以這種順序進行查詢執(zhí)行。

贊一個~不請自轉(zhuǎn)，感謝原作者沈杰，如有侵權(quán)請告知。

創(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)建唯一約束，且唯一索引能做到和唯一約束一樣的效果。