mysql索引介紹

什麼是索引?

索引是一種高效能取得資料的資料結構。

索引的類型

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

1、FULLTEXT

即為全文索引，目前只有MyISAM引擎支援。其可在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不過目前只有 CHAR、VARCHAR ，TEXT 欄位上可以建立全文索引。

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

（免費學習影片教學推薦：mysql影片教學）

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

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

3、BTREE

BTREE索引就是一種將索引值依一定的演算法，存入一個樹狀的資料結構中（二叉樹），每次查詢都是從樹的入口root開始，依序遍歷node，取得leaf。這是MySQL裡預設和最常用的索引類型。

4、RTREE

RTREE在MySQL很少使用，僅支援geometry資料類型，支援該類型的儲存引擎只有MyISAM、BDb、InnoDb、NDb、Archive幾種。

相對於BTREE，RTREE的優點在於範圍查找。

索引種類

普通索引：僅加速查詢

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

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

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

全文索引：對文字的內容進行分詞，進行搜尋

#索引使用

1、建立索引

1 --创建普通索引CREATE INDEX index_name ON table_name(col_name);
2 --创建唯一索引CREATE UNIQUE INDEX index_name ON table_name(col_name);
3 --创建普通组合索引CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
4 --创建唯一组合索引CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

2、透過修改表結構建立索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

3、建立資料表時直接指定索引

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;
--修改表结构删除索引ALTER TABLE table_name DROP INDEX index_name;

5、其它指令

#

- 查看表结构
    desc table_name;
 - 查看生成表的SQL
    show create table table_name;
 - 查看索引
    show index from  table_name;
 - 查看执行时间
    set profiling = 1;
    SQL...
    show profiles;

索引失效的原因

1、全值匹配，相當於索引沒有使用。

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

3、在索引上做關於（計算、函數、（自動or手動）類型轉換），會造成索引失效而導致全表掃描。

4、mysql在使用不等於（a8093152e673feb7aba1828c43532094，!=）的時候無法使用索引而導致全表掃描。

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

6、like 以通配符開關（'�c')會導致索引失效而全表掃描。

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

8、少用or，用or連接時會導致索引失效。

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

mysql索引規約

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

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

2、【強制】超過三個表禁止join。需要 join 的字段，資料類型必須絕對一致；多表關聯查詢時，保證被關聯的字段需要有索引。

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

3、【強制】在varchar 欄位上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據實際文字區分度決定索引長度即可。

（說明：索引的長度與區分度是一對矛盾體，一般對字串類型數據，長度為20 的索引，區分度會高達90%以上，

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

4、【強制】頁面搜尋嚴禁左模糊或全模糊，如果需要請走搜尋引擎來解決。

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

相關文章教學建議： mysql教學

以上是mysql索引介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！