索引簡介

介紹

• 索引(index)是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。
• 可以理解為：索引是數(shù)據(jù)結(jié)構(gòu)；或者排好序的快速查找數(shù)據(jù)結(jié)構(gòu)。
• 索引本身很大，不可能全部存在內(nèi)存中，是以索引文件的形式存儲在磁盤上。

優(yōu)缺點(diǎn)

• 優(yōu)點(diǎn)

1. 提高數(shù)據(jù)檢索效率，降低數(shù)據(jù)庫的io成本。
2. 通過索引對數(shù)據(jù)排序，降低排序成本，降低cpu消耗。

• 缺點(diǎn)

1. 降低了更新表的速度，如insert、update和delete。
2. 索引也是一張表，該表保存了主鍵和索引字段，并指向?qū)嶓w表的紀(jì)錄，所以占用了更多空間。

索引結(jié)構(gòu)

• BTree

BTree的特點(diǎn)：
（1）所有鍵值分布在整個樹中。
（2）任何關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個節(jié)點(diǎn)中。
（3）搜索有可能在非葉子節(jié)點(diǎn)結(jié)束。
（4）在關(guān)鍵字全集內(nèi)做一次查找，性能逼近二分查找算法。

B-Tree存在的問題：
（1）每個節(jié)點(diǎn)中有key，也有data，而每一個頁的存儲空間是有限的，如果data數(shù)據(jù)較大時就會導(dǎo)致每個節(jié)點(diǎn)（即一個頁）能存儲的key的數(shù)量很小
（2）當(dāng)存儲的數(shù)據(jù)量很大時，同樣1會導(dǎo)致B-Tree的深度較大，增加查詢時的磁盤I/O次數(shù)，進(jìn)而影響查詢效率

image.png

image.png

• BTree

B+Tree與BTree的不同在于：
（1）所有關(guān)鍵字存儲在葉子節(jié)點(diǎn)，非葉子節(jié)點(diǎn)不存儲真正的data。
（2）為所有葉子節(jié)點(diǎn)增加了一個鏈指針。

image.png

image.png

• 總結(jié)：

因?yàn)橛?jì)算機(jī)內(nèi)存問題和查詢效率問題，mysql選擇B+Tree

索引分類

• 基本語法

# 創(chuàng)建索引
CREATE  [UNIQUE ]  INDEX [indexName] ON table_name(column)) 
# 刪除索引
DROP INDEX [indexName] ON mytable; 
# 查看索引
SHOW INDEX FROM table_name\G

• 單一索引

# 隨表一起建索引：
create table `t_emp`(
    `id` int(11) not null auto_increment,
    `name` varchar(20) default null,
    `age` int(3) default null,
    `deptId` int(11) default null,
    `empno` int not null,
    primary key (`id`),
    key 'idx_dept_id' ('deptId')    --創(chuàng)建索引
)engine=innodb auto_increment=1 default charset = utf8;
  
# 單獨(dú)建單值索引：
create index idx_name on t_emp(name);
 
# 刪除索引：
drop index idx_name on t_emp;

• 唯一索引

create unique index idx_empno on t_emp(empno);

• 主鍵索引

注意：新建的表可以創(chuàng)建主鍵索引，已有數(shù)據(jù)的表不能新建索引。

# 隨表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
   
CREATE TABLE customer2 (id INT(10) UNSIGNED   ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
 
# 單獨(dú)建主鍵索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no);  
 
# 刪除建主鍵索引：
ALTER TABLE customer drop PRIMARY KEY ;  
 
# 修改建主鍵索引：
必須先刪除掉(drop)原索引，再新建(add)索引

• 復(fù)合索引

create index idx_age_deptid_name on t_emp(age,deptId,name);

image.png

索引創(chuàng)建條件

• 需要創(chuàng)建索引的條件

1. 主鍵自動建立唯一索引 。
2. 頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引(where 后面的語句)。
3. 查詢中與其它表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引。
4. 單鍵/組合索引的選擇問題，who？(在高并發(fā)下傾向創(chuàng)建組合索引)。
5. 查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度
6. 查詢中統(tǒng)計(jì)或者分組字段

• 不需要創(chuàng)建索引的條件

1. 表記錄太少
2. 經(jīng)常增刪改的表或者字段
3. Where條件里用不到的字段不創(chuàng)建索引
4. 過濾性不好的不適合建索引

Explain（索引創(chuàng)建的標(biāo)尺）

介紹

使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道MySQL是
如何處理你的SQL語句的。分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸。

使用方式

#Explain + SQL語句
explain select c.name, ab.name ceoname from t_emp c left join (
    select a.name,b.id from t_emp a inner join t_dept b on a.id = b.CEO
) ab on c.deptId = ab.id;

可以得到以下字段

image.png

字段解釋

id字段

select查詢的序列號,包含一組數(shù)字，表示查詢中執(zhí)行select子句或操作表的順序。
每個id號，表示一趟獨(dú)立的查詢。一個sql 的查詢趟數(shù)越少越好，即id號越少越好。

image.png

select_type字段

查詢的類型，主要是用于區(qū)別普通查詢、聯(lián)合查詢、子查詢等的復(fù)雜查詢。

image.png

table字段

顯示這一行的數(shù)據(jù)是關(guān)于哪張表的。

type字段

字段類型

image.png

type顯示的是訪問類型，是較為重要的一個指標(biāo)，結(jié)果值從最好到最壞依次是：
（1）system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
（2）system>const>eq_ref>ref>range>index>ALL
一般來說，得保證查詢至少達(dá)到range級別，最好能達(dá)到ref。

image.png

possible_keys字段

顯示可能應(yīng)用在這張表中的索引，一個或多個。
查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實(shí)際使用。

key字段

實(shí)際使用的索引。如果為NULL，則沒有使用索引。
查詢中若使用了覆蓋索引，則該索引和查詢的select字段重疊。

key_len字段

表示索引中使用的字節(jié)數(shù)，可通過該列計(jì)算查詢中使用的索引的長度。
key_len字段能夠幫你檢查是否充分的利用上了索引。

ref字段

顯示索引的哪一列被使用了，如果可能的話，是一個常數(shù)。哪些列或常量被用于查找索引列上的值。

rows字段

rows列顯示MySQL認(rèn)為它執(zhí)行查詢時必須檢查的行數(shù)。越少越好。

Extra字段

image.png

參考內(nèi)容

MySQL索引原理及慢查詢優(yōu)化