一、概要
索引就像一本書的目錄掐禁。而當(dāng)用戶通過索引查找數(shù)據(jù)時怜械，就好比用戶通過目錄查詢某章節(jié)的某個知識點。這樣就幫助用戶有效地提高了查找速度穆桂。所以宫盔，使用索引可以有效地提高數(shù)據(jù)庫系統(tǒng)的整體性能
在我們的一個應(yīng)用系統(tǒng)中,讀寫的比例一般大概8:2左右，在實戰(zhàn)開發(fā)中,當(dāng)數(shù)據(jù)量比較大的時候查詢的速度比較慢的時候,我們可以通過創(chuàng)建索引的方式來加快我們的查詢速度
二享完、什么叫索引
系統(tǒng)根據(jù)某種算法，將已有的數(shù)據(jù)（未來可能新增的數(shù)據(jù)）有额，單獨建立一個文件般又，這個文件能夠?qū)崿F(xiàn)快速匹配數(shù)據(jù)彼绷，并且能夠快速的找到對應(yīng)的記錄,本質(zhì)上是一種數(shù)據(jù)結(jié)構(gòu)
三、優(yōu)缺點
1茴迁、優(yōu)點
- 提升查詢數(shù)據(jù)的效率
- 可以加速表與表之間的連接
- 在使用分組和排序進行檢索的時候寄悯，可以減少查詢中分組和排序的時間
2、缺點
- 當(dāng)對表中的數(shù)據(jù)進行增加堕义、刪除和修改的時候猜旬，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度倦卖。
- 創(chuàng)建索引和維護索引要耗費時間洒擦，這種時間隨著數(shù)據(jù)量的增加而增加。
- 索引需要占物理空間怕膛，除了數(shù)據(jù)表占數(shù)據(jù)空間之外熟嫩，每一個索引還要占一定的物理空間，如果要建立聚簇索引褐捻，那么需要的空間就會更大掸茅。
三、索引分類
1柠逞、按存儲結(jié)構(gòu)
- BTree索引
- Hash索引
- 位圖索引(mysql不支持)
2 昧狮、按應(yīng)用層次
- 普通索引，
- 主鍵索引
- 唯一索引
- 全文索引
- 復(fù)合索引
3板壮、數(shù)據(jù)行的物理順序與列值的邏輯順序相同
- 聚集索引
- 非聚集索引
四逗鸣、基本使用
4.1、主鍵索引
-
說明
創(chuàng)建主鍵約束自動會建立主鍵索引个束，不允許重復(fù)慕购，不允許空值；
-
語法格式
-- 創(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ān)鍵字') -
示例代碼
-- 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%')
4.5帮坚、聯(lián)合索引(多列索引)
-
說明
聯(lián)合索引遵守“最左前綴”原則妻往，即在查詢條件中使用了聯(lián)合索引的第一個字段，索引才會被使用试和。因此讯泣，在聯(lián)合索引中索引列的順序至關(guā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)相當(dāng)于建立了兩個聯(lián)合索引( a ) 好渠、( a, b ）;
- 如果有一個3列索引( a, b, c )，實際上已經(jīng)建立了三個聯(lián)合索引( a )节视、(a, b)拳锚、(a, b, c)。依次內(nèi)推
-
總結(jié)
當(dāng)創(chuàng)建(a,b,c)聯(lián)合索引時肴茄，相當(dāng)于創(chuàng)建了(a)單列索引晌畅，(a,b)聯(lián)合索引以及(a,b,c)聯(lián)合索引
想要索引生效的話,只能使用 a和a,b和a,b,c三種組合；當(dāng)然寡痰，測試過抗楔，a,c組合也可以
五、什么情況使用索引
- 索引應(yīng)該經(jīng)常建在where 子句經(jīng)常用到的列上拦坠。如果某個大表經(jīng)常使用某個字段進行查詢连躏，并且檢索行數(shù)小于總表行數(shù)的5%。則應(yīng)該考慮贞滨。
- 對于兩表連接的字段入热，應(yīng)該建立索引。如果經(jīng)常在某表的一個字段進行Order By 則也經(jīng)過進行索引晓铆。
- 不應(yīng)該在小表上建設(shè)索引(例如表中只有三四個字段)勺良。
六、索引失效的情況
-
對索引列運算骄噪，運算包括（+尚困、-、*链蕊、/事甜、！滔韵、%）逻谦，導(dǎo)致索引失效
-- 則會使索引失效， 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ù)索引
-- 索引失效 SELECT * FROM t_user WHERE substr(username ,1 ,3 ) = 'ABC'
總結(jié)一句話就是, 使用explain 關(guān)鍵執(zhí)行一下 key是否有值, 有值就說明走了索引,null就表示索引失效
七忱嘹、key和index區(qū)別
1嘱腥、說明
key 是數(shù)據(jù)庫的物理結(jié)構(gòu)耕渴，它包含兩層意義，一是約束（偏重于約束和規(guī)范數(shù)據(jù)庫的結(jié)構(gòu)完整性）齿兔，二是索引（輔助查詢用的）橱脸。包括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
七靖秩、總結(jié)
- 能用唯一索引须眷，一定用唯一索引
- 可以給字段加非空約束就盡量加上非空約束
- 聯(lián)合索引的順序不同，影響索引的選擇沟突，盡量將值少的放在前面
- 千萬不要給大字段加索引
八 花颗、其它
1、查看索引
-
語法
show index from tblname; -- 或者 show keys from tblname; -
說明
- Non_unique：如果索引不能包括重復(fù)詞惠拭，則為0扩劝。如果可以，則為1
- Key_name：索引的名稱
- Column_name：索引列名稱
- Index_type ：索引類型
