索引(mysql相關知識)
一、什么是索引
索引是一種高效獲取資料的資料結構，
二、索引的型別
FULLTEXT，（HASH，BTREE[mysql主要使用的兩種]），RTREE，
2.1. FULLTEXT
即為全文索引，目前只有MyISAM引擎支持，其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不過目前只有 CHAR、VARCHAR ，TEXT 列上可以創建全文索引，
全文索引并不是和MyISAM一起誕生的，它的出現是為了解決WHERE name LIKE “%word%"這類針對文本的模糊查詢效率較低的問題，
2.2. HASH
由于HASH的唯一（幾乎100%的唯一）及類似鍵值對的形式，很適合作為索引，
HASH索引可以一次定位，不需要像樹形索引那樣逐層查找,因此具有極高的效率，但是，這種高效是有條件的，即只在“=”和“in”條件下高效，對于范圍查詢、排序及組合索引仍然效率不高，
2.3. BTREE
BTREE索引就是一種將索引值按一定的演算法，存入一個樹形的資料結構中（二叉樹），每次查詢都是從樹的入口root開始，依次遍歷node，獲取leaf，這是MySQL里默認和最常用的索引型別，
2.4. RTREE
RTREE在MySQL很少使用，僅支持geometry資料型別，支持該型別的存盤引擎只有MyISAM、BDb、InnoDb、NDb、Archive幾種，
相對于BTREE，RTREE的優勢在于范圍查找，
三、索引種類
-
普通索引：僅加速查詢
-
唯一索引：加速查詢 + 列值唯一（可以有null）
-
主鍵索引：加速查詢 + 列值唯一（不可以有null）+ 表中只有一個
-
組合索引：多列值組成一個索引，專門用于組合搜索，其效率大于索引合并
-
全文索引：對文本的內容進行分詞，進行搜索
四、索引使用
4.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);
4.2 通過修改表結構創建索引
ALTER TABLE table_name ADD INDEX index_name(col_name);
4.3 創建表時直接指定索引
CREATE TABLE table_name ( ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name) );
4.4 洗掉索引
--直接洗掉索引DROP INDEX index_name ON table_name;--修改表結構洗掉索引ALTER TABLE table_name DROP INDEX index_name;
4.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在使用不等于（<>，!=）的時候無法使用索引而導致全表掃描，
5.is null ，is not null 也無法使用索引，
6.like 以通配符開關（'%abc')會導致索引失效而全表掃描，
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 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引，）
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/236511.html
標籤：其他