一、概要
索引就像一本書的目錄。而當用戶通過索引查找數(shù)據(jù)時，就好比用戶通過目錄查詢某章節(jié)的某個知識點。這樣就幫助用戶有效地提高了查找速度。所以，使用索引可以有效地提高數(shù)據(jù)庫系統(tǒng)的整體性能
在我們的一個應用系統(tǒng)中,讀寫的比例一般大概8:2左右，在實戰(zhàn)開發(fā)中,當數(shù)據(jù)量比較大的時候查詢的速度比較慢的時候,我們可以通過創(chuàng)建索引的方式來加快我們的查詢速度
二、什么叫索引
系統(tǒng)根據(jù)某種算法，將已有的數(shù)據(jù)（未來可能新增的數(shù)據(jù)），單獨建立一個文件，這個文件能夠實現(xiàn)快速匹配數(shù)據(jù)，并且能夠快速的找到對應的記錄,本質上是一種數(shù)據(jù)結構
三、優(yōu)缺點
1、優(yōu)點
- 提升查詢數(shù)據(jù)的效率
- 可以加速表與表之間的連接
- 在使用分組和排序進行檢索的時候，可以減少查詢中分組和排序的時間
2、缺點
- 當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度。
- 創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加。
- 索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。
四、索引分類
1、按存儲結構
- BTree索引
- Hash索引
- 位圖索引(mysql不支持)
2 、按應用層次
- 普通索引，
- 主鍵索引
- 唯一索引
- 全文索引
- 復合索引
3、數(shù)據(jù)行的物理順序與列值的邏輯順序相同
- 聚集索引
- 非聚集索引
五、基本使用
4.1、主鍵索引
- 說明
創(chuàng)建主鍵約束自動會建立主鍵索引，不允許重復，不允許空值； - 語法格式
-- 創(chuàng)建主鍵時數(shù)據(jù)庫自動創(chuàng)建 CREATE TABLE 表名 ( 列名 類型 PRIMARY KEY ) -- 或者 表級創(chuàng)建 CREATE TABLE 表名 ( 列名 類型 , PRIMARY KEY(列名) ) - 示例代碼
CREATE TABLE t_test( -- 自動創(chuàng)建主鍵索引 tid int AUTO_INCREMENT PRIMARY KEY ) -- 或者 CREATE TABLE t_test( -- 自動創(chuàng)建主鍵索引 tid int AUTO_INCREMENT , PRIMARY KEY(tid) )
4.2、普通索引
- 說明
在創(chuàng)建普通索引時，不附加任何限制條件。這類索引可以創(chuàng)建在任何數(shù)據(jù)類型中，其值是否唯一和非空由字段本身的完整性約束條件決定。 - 語法格式
-- 在創(chuàng)建表的時候創(chuàng)建 (不推薦) CREATE TABLE 表名( 列名 類型 約束, ...., key () ) -- 創(chuàng)建表之后在創(chuàng)建索引 (推薦方式創(chuàng)建) CREATE INDEX 索引名 ON 表 (列名,); - 示例代碼
CREATE TABLE t_index_key ( tid int PRIMARY KEY AUTO_INCREMENT, name varchar(64) NOT NULL, KEY (name) )-- 刪除索引 DROP INDEX name ON t_index_key -- 創(chuàng)建索引 推薦 CREATE INDEX idx_key_name ON t_index_key(name) -- 查看索引 EXPLAIN SELECT * from t_index_key WHERE name='123'
4.3、唯一索引
- 說明
用來建立索引的列的值必須是唯一的，允許空值, 可以通過創(chuàng)建表的時候使用唯一約束創(chuàng)建 - 語法格式
CREATE UNIQUE INDEX 索引名 ON 表名(列名 DESC,列名) - 示例代碼
CREATE UNIQUE INDEX idx_user_username ON t_user(username DESC)
4.4、全文索引
說明
即為全文索引，Mysql5.6之前只有MyISAM引擎支持，Mysql5.6之后InnoDB也支持。目前只有 CHAR、VARCHAR ，TEXT 列上可以創(chuàng)建全文索引
主要解決 它的出現(xiàn)是為了解決WHERE name LIKE “%word%"這類針對文本的模糊查詢效率較低的問題-
創(chuàng)建格式
-- 1. 創(chuàng)建表 DROP TABLE IF EXISTS t_myisam; CREATE TABLE t_myisam ( mid int AUTO_INCREMENT PRIMARY KEY, name varchar(64) NOT NULL, detail text ) ENGINE = MYISAM DEFAULT CHARSET = UTF8MB4; -
查詢格式
MATCH (列名,...) AGAINST ('查詢的關鍵字' 檢索模式) -
搜索語法規(guī)則
-
+一定要有(不含有該關鍵詞的數(shù)據(jù)條均被忽略)。 -
-不可以有(排除指定關鍵詞，含有該關鍵詞的均被忽略)。 -
>提高該條匹配數(shù)據(jù)的權重值 -
*全匹配，不像其他語法放在前面，這個要接在字符串后面。
-
-
檢索模式
- 自然語言檢索： IN NATURAL LANGUAGE MODE
- 布爾檢索： IN BOOLEAN MODE
特點如下不剔除50%以上符合的row。
不自動以相關性反向排序。
可以對沒有FULLTEXT index的字段進行搜尋，但會非常慢。
限制最長與最短的字符串。
套用Stopwords。
-
示例代碼
-- 1. 創(chuàng)建 DROP TABLE IF EXISTS t_myisam; CREATE TABLE t_myisam ( mid int AUTO_INCREMENT PRIMARY KEY, name varchar(64) NOT NULL, detail text ) ENGINE = MYISAM DEFAULT CHARSET = UTF8MB4; -- 2.創(chuàng)建全文索引 CREATE FULLTEXT INDEX idx_username_detail ON t_myisam(detail) -- 查看索引信息 SHOW INDEX FROM t_myisam -- 或者 SHOW KEYS FROM t_myisam SELECT * FROM t_myisam WHERE MATCH(detail) AGAINST('ab') -- 查看索引是否生效 EXPLAIN SELECT * FROM t_myisam WHERE MATCH(detail) AGAINST('ab') -- 查詢必須包含ab開頭的詞 SELECT * FROM articles WHERE MATCH (detail) AGAINST ('+ab*' IN BOOLEAN MODE);
4.5、聯(lián)合索引(多列索引)
- 說明
聯(lián)合索引遵守“最左前綴”原則，即在查詢條件中使用了聯(lián)合索引的第一個字段，索引才會被使用。因此，在聯(lián)合索引中索引列的順序至關重要。如果不是按照索引的最左列開始查找，則無法使用索引 - 語法格式
CREATE INDEX 索引名 ON 表名(索引字段,索引字段,...) - 示例代碼
CREATE TABLE t_user ( uid int AUTO_INCREMENT PRIMARY KEY, username varchar(64) NOT NULL, password varchar(128) NOT NULL, phone varchar(11) NOT NULL, is_delete tinyint DEFAULT 0 NOT NULL, create_date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, CONSTRAINT index_user_name UNIQUE (username) ); -- 創(chuàng)建普通的聯(lián)合索引 CREATE INDEX index_user_date ON t_user (username,phone,create_date);-- 聯(lián)合索引字段 username,phone,create_date -- 1. 可以 SELECT * FROM t_user WHERE username = 'admin'; -- 2. 可以 SELECT * FROM t_user WHERE username = 'admin' AND phone = '123456'; -- 3. 可以 SELECT * FROM t_user WHERE username = 'admin' AND phone = '123456' AND create_date = '2019-07-18 17:04:12'; -- 不可以 SELECT * FROM t_user WHERE phone = '123456'; - 最左前綴的原則
- 如果我們建立了一個2列的聯(lián)合索引(a,b),實際上已經(jīng)相當于建立了兩個聯(lián)合索引( a ) 、( a, b ）;
- 如果有一個3列索引( a, b, c )，實際上已經(jīng)建立了三個聯(lián)合索引( a )、(a, b)、(a, b, c)。依次內推
- 總結
當創(chuàng)建(a,b,c)聯(lián)合索引時，相當于創(chuàng)建了(a)單列索引，(a,b)聯(lián)合索引以及(a,b,c)聯(lián)合索引
想要索引生效的話,只能使用 a和a,b和a,b,c三種組合；當然，我們上面測試過，a,c組合也可以，但實際上只用到了a的索引，c并沒有用到！
六、什么情況使用索引
- 索引應該經(jīng)常建在where 子句經(jīng)常用到的列上。如果某個大表經(jīng)常使用某個字段進行查詢，并且檢索行數(shù)小于總表行數(shù)的5%。則應該考慮。
- 對于兩表連接的字段，應該建立索引。如果經(jīng)常在某表的一個字段進行Order By 則也經(jīng)過進行索引。
- 不應該在小表上建設索引(例如表中只有三四個字段)。
七、索引失效的情況
-
對索引列運算，運算包括（+、-、*、/、！、%），導致索引失效
-- 則會使索引失效， EXPLAIN SELECT * FROM tbl WHERE age + 5 > 10 - 不等于(!=)比較特殊 除主鍵索引或索引是整數(shù)類型外的其它索引都失效
EXPLAIN SELECT * FROM t_user WHERE username != 'OlUldQDIVV'; -- 索引有效 EXPLAIN SELECT * FROM t_user WHERE uid != 1; - like以通配符開頭（‘%’）,如果非要用使用全文索引
- 如果條件中有or，即使其中有條件帶索引也不會使用 如果想使用or，又想讓索引生效，只能將or條件中的每個列都加上索引
- 小于 大于這個根據(jù)實際查詢數(shù)據(jù)來判斷，如果全盤掃描速度比索引速度要快則不走索引 。
- 索引列上不要使用函數(shù),oracle必須使用函數(shù)索引
總結一句話就是, 使用explain 關鍵執(zhí)行一下 key是否有值, 有值就說明走了索引,null就表示索引失效-- 索引失效 SELECT * FROM t_user WHERE substr(username ,1 ,3 ) = 'ABC'
八、key和index區(qū)別
1、說明
key 是數(shù)據(jù)庫的物理結構，它包含兩層意義，一是約束（偏重于約束和規(guī)范數(shù)據(jù)庫的結構完整性），二是索引（輔助查詢用的）。包括primary key, unique key, foreign key
2、primary key
- 一是約束作用（constraint），用來規(guī)范一個存儲主鍵和唯一性，
- 同時也在此key上建立了一個index
3、 unique key
- 約束作用（constraint），規(guī)范數(shù)據(jù)的唯一性
- 在這個key上建立了一個index
4、foreign key
- 約束作用（constraint），規(guī)范數(shù)據(jù)的引用完整性
- 在這個key上建立了一個index
九、總結
- 索引占磁盤空間，不要重復的索引，盡量短
- 只給常用的查詢條件加索引
- 過濾性高的列建索引，取值范圍固定的列不建索引
- 唯一的記錄添加唯一索引
- 頻繁更新的列不要建索引
- 不要對索引列運算
- 同樣過濾效果下，保持索引長度最小
- 合理利用組合索引，注意索引字段先后順序
- 多列組合索引，過濾性高的字段最前
- order by 字段建立索引，
- 組合索引，不同的排序順序不能使用索引
十、其它
1、查看索引
- 語法
show index from tblname; -- 或者 show keys from tblname; - 說明
- Non_unique：如果索引不能包括重復詞，則為0。如果可以，則為1
- Key_name：索引的名稱
- Column_name：索引列名稱
- Index_type ：索引類型
2、執(zhí)行計劃(explain)
-
概要
在 select 語句之前增加 explain 關鍵字，MySQL 會在查詢上設置一個標記，執(zhí)行查詢時，會返回執(zhí)行計劃的信息，而不是執(zhí)行這條SQL（如果 from 中包含子查詢，仍會執(zhí)行該子查詢，將結果放入臨時表中）
image - 主要字段說明
- type
system > const > eq_ref > ref > range > index > all
一般來說，得保證查詢至少達到range級別，最好能達到ref。
index all 性能差需要優(yōu)化 至少要達到 range 級別，要求是 ref 級別，如果可以是 consts 最好 - possible_keys
顯示可能應用在這張表中的索引，一個或多個。查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用 - key
實際使用的索引，如果為NULL，則沒有使用索引
- type
