什么是索引?

索引是一種高效獲取數(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%"這類針對(duì)文本的模糊查詢效率較低的問題。

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

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

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

3、BTREE

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

4、RTREE

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

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

索引種類

普通索引：僅加速查詢

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

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

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

全文索引：對(duì)文本的內(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)建表時(shí)直接指定索引

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í)行時(shí)間     set profiling = 1;     SQL...     show profiles;

索引失效的原因

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

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

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

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

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

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

7、字符串不加單引號(hào)索引會(huì)失效。

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

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

mysql索引規(guī)約

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

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

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

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

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

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

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

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

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

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