什么是索引?

索引是一種高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。

索引的類型

FULLTEXT，（HASH，BTREE[mysql主要使用的兩種]），RTREE。

1、FULLTEXT

即為全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不過目前只有 CHAR、VARCHAR ，TEXT 列上可以創(chuàng)建全文索引。

全文索引并不是和MyISAM一起誕生的，它的出現(xiàn)是為了解決WHERE name LIKE “%word%"這類針對文本的模糊查詢效率較低的問題。

（免費學(xué)習(xí)視頻教程推薦：mysql視頻教程）

2、HASH
由于HASH的唯一（幾乎100%的唯一）及類似鍵值對的形式，很適合作為索引。

HASH索引可以一次定位，不需要像樹形索引那樣逐層查找,因此具有極高的效率。但是，這種高效是有條件的，即只在“=”和“in”條件下高效，對于范圍查詢、排序及組合索引仍然效率不高。

3、BTREE

BTREE索引就是一種將索引值按一定的算法，存入一個樹形的數(shù)據(jù)結(jié)構(gòu)中（二叉樹），每次查詢都是從樹的入口root開始，依次遍歷node，獲取leaf。這是MySQL里默認(rèn)和最常用的索引類型。

4、RTREE

RTREE在MySQL很少使用，僅支持geometry數(shù)據(jù)類型，支持該類型的存儲引擎只有MyISAM、BDb、InnoDb、NDb、Archive幾種。

相對于BTREE，RTREE的優(yōu)勢在于范圍查找。

索引種類

普通索引：僅加速查詢

唯一索引：加速查詢 + 列值唯一（可以有null）

主鍵索引：加速查詢 + 列值唯一（不可以有null）+ 表中只有一個

組合索引：多列值組成一個索引，專門用于組合搜索，其效率大于索引合并

全文索引：對文本的內(nèi)容進(jìn)行分詞，進(jìn)行搜索

索引使用

1、創(chuàng)建索引

1 --創(chuàng)建普通索引CREATE INDEX index_name ON table_name(col_name); 2 --創(chuàng)建唯一索引CREATE UNIQUE INDEX index_name ON table_name(col_name); 3 --創(chuàng)建普通組合索引CREATE INDEX index_name ON table_name(col_name_1,col_name_2); 4 --創(chuàng)建唯一組合索引CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

2、通過修改表結(jié)構(gòu)創(chuàng)建索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

3、創(chuàng)建表時直接指定索引

CREATE TABLE table_name (     ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name) );

4、刪除索引

--直接刪除索引DROP INDEX index_name ON table_name; --修改表結(jié)構(gòu)刪除索引ALTER TABLE table_name DROP INDEX index_name;

5、其它命令

- 查看表結(jié)構(gòu)     desc table_name;  - 查看生成表的SQL     show create table table_name;  - 查看索引     show index from  table_name;  - 查看執(zhí)行時間     set profiling = 1;     SQL...     show profiles;

索引失效的原因

1、全值匹配，相當(dāng)于索引沒有使用。

2、未滿足最佳前綴法則也可能造成索引失效。

3、在索引上做關(guān)于（計算、函數(shù)、（自動or手動）類型轉(zhuǎn)換），會造成索引失效而導(dǎo)致全表掃描。

4、mysql在使用不等于（<>，!=）的時候無法使用索引而導(dǎo)致全表掃描。

5、is null ，is not null 也無法使用索引。

6、like 以通配符開關(guān)（'%abc')會導(dǎo)致索引失效而全表掃描。

7、字符串不加單引號索引會失效。

8、少用or，用or連接時會導(dǎo)致索引失效。

9、使用select * 查詢，盡量使用覆蓋索引。

mysql索引規(guī)約

1、【強(qiáng)制】業(yè)務(wù)上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引（說明：不要以為唯一索引影響了 insert 速度，這個速度損耗可以忽略，

但提高查找速度是明顯的；另外，即使在應(yīng)用層做了非常完善的校驗控制，只要沒有唯一索引，根據(jù)墨菲定律，必然有臟數(shù)據(jù)產(chǎn)生。）

2、【強(qiáng)制】超過三個表禁止 join。需要 join 的字段，數(shù)據(jù)類型必須絕對一致；多表關(guān)聯(lián)查詢時，保證被關(guān)聯(lián)的字段需要有索引。

（說明：即使雙表 join 也要注意表索引、SQL 性能。）

3、【強(qiáng)制】在 varchar 字段上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據(jù)實際文本區(qū)分度決定索引長度即可。

（說明：索引的長度與區(qū)分度是一對矛盾體，一般對字符串類型數(shù)據(jù)，長度為 20 的索引，區(qū)分度會高達(dá) 90%以上，

可以使用 count(distinct left(列名, 索引長度))/count(*)的區(qū)分度來確定。）

4、【強(qiáng)制】頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請走搜索引擎來解決。

（說明：索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。）

相關(guān)文章教程推薦：mysql教程