MySQL索引
- 索引介紹
- 索引原理與分析
- 組合索引
- 索引失效分析
索引介紹
- 什么是索引
索引：包括聚集索引、覆蓋索引播聪、組合索引朽基、前綴索引、唯一索引等离陶，沒有特別說
明稼虎，默認(rèn)都是使用B+樹結(jié)構(gòu)組織（多路搜索樹，并不一定是二叉的）的索引招刨。
存儲(chǔ)：存儲(chǔ)在磁盤文件中
索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)霎俩。更通俗的說，數(shù)據(jù)庫索引好比是一本書前面的目錄沉眶，能加快數(shù)據(jù)庫的查詢速度打却。 - 索引的優(yōu)勢(shì)和劣勢(shì)
優(yōu)勢(shì)：提高數(shù)據(jù)的檢索效率，降低數(shù)據(jù)庫的IO成本谎倔；對(duì)數(shù)據(jù)進(jìn)行排序
劣勢(shì)： 占據(jù)磁盤空間柳击，降低更新表的效率 - 索引的分類
單列索引、組合索引传藏、全文索引腻暮、空間索引 - 索引的使用
創(chuàng)建索引
CREATE INDEX index_name ON table(column(length)) ;
ALTER TABLE table_name ADD INDEX index_name (column(length));
//唯一索引：
CREATE UNIQUE INDEX index_name ON table(column(length)) ;
alter table table_name add unique index index_name(column);
//全文索引：
CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
alter table table_name add fulltext index_name(column)
//組合索引：
ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;
刪除索引：
DROP INDEX index_name ON table
查看索引：
SHOW INDEX FROM table_name \G
索引原理與分析
- 索引的存儲(chǔ)結(jié)構(gòu)
索引的存儲(chǔ)結(jié)構(gòu)：
不同的存儲(chǔ)引擎，會(huì)使用不同的索引
MyISAM和InnoDB存儲(chǔ)引擎：只支持B+ TREE索引毯侦， 也就是說默認(rèn)使用BTREE哭靖，不能夠更換
MEMORY/HEAP存儲(chǔ)引擎：支持HASH和BTREE索引
- B樹和B+樹：
B樹
B樹和B+樹的最大區(qū)別在于非葉子節(jié)點(diǎn)是否存儲(chǔ)數(shù)據(jù)的問題。
- B樹是為了磁盤或其它存儲(chǔ)設(shè)備而設(shè)計(jì)的一種多叉（下面你會(huì)看到侈离，相對(duì)于二叉试幽，B樹每個(gè)內(nèi)結(jié)點(diǎn)有多個(gè)分支，即多叉）平衡查找樹卦碾。
B樹的高度一般都是在2-4這個(gè)高度铺坞，樹的高度直接影響IO讀寫的次數(shù)。
如果是三層樹結(jié)構(gòu)---支撐的數(shù)據(jù)可以達(dá)到20G洲胖，如果是四層樹結(jié)構(gòu)---支撐的數(shù)據(jù)可以達(dá)到幾十T
B樹是非葉子節(jié)點(diǎn)和葉子節(jié)點(diǎn)都會(huì)存儲(chǔ)數(shù)據(jù)济榨。
B+樹只有葉子節(jié)點(diǎn)才會(huì)存儲(chǔ)數(shù)據(jù)，而且存儲(chǔ)的數(shù)據(jù)都是在一行上绿映，而且這些數(shù)據(jù)都是有指針指向的擒滑，也
就是有順序的腐晾。 索引列 order by
- 非聚集索引（MyISAM）
B+樹的索引不在一起就是非聚集索引
非聚集索引一般包含主鍵索引 和輔助索引都會(huì)存儲(chǔ)指針的值。 - 聚焦索引（InnoDB）
數(shù)據(jù)和索引在一起就是聚焦索引丐一。
主鍵索引的葉子節(jié)點(diǎn)會(huì)存儲(chǔ)數(shù)據(jù)行藻糖。輔助索引只會(huì)存儲(chǔ)主鍵值。
主鍵索引：1.InnoDB 要求表必須有主鍵(MyISAM 可以沒有),如果沒有顯式指定,則 MySQL系統(tǒng)會(huì)自動(dòng)選擇一個(gè)可以唯一標(biāo)識(shí)數(shù)據(jù)記錄的列作為主鍵,如果不存在這種列,則MySQL 自動(dòng)為 InnoDB 表生成一個(gè)隱含字段作為
主鍵,類型為長整形库车。
輔助索引：2.第二個(gè)與 MyISAM 索引的不同是 InnoDB 的輔助索引 data 域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址巨柒。換句話說,InnoDB 的所有輔助索引都引用主鍵作為 data 域。
select * from user where name='Alice' 回表查詢 檢索兩次 非主鍵索引 --- pk---索引--->數(shù)據(jù)
select id,name from user where name='Alice' 不需要回表 在輔助索引樹上就可以查詢到了 覆蓋索
引(多用組合索引)
引申:為什么不建議使用過長的字段作為主鍵?
因?yàn)樗休o助索引都引用主索引,過長的主索引會(huì)令輔助索引變得過大柠衍。
同時(shí),請(qǐng)盡量在 InnoDB 上采用自增字段做表的主鍵洋满。
-
MyISAM 和 InnoDB的存儲(chǔ)結(jié)構(gòu)
InnoDB 主鍵索引
輔助索引
組合索引
-
哪些情況需要?jiǎng)?chuàng)建索引
- 主鍵自動(dòng)建立唯一索引
- 頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
- 多表關(guān)聯(lián)查詢中，關(guān)聯(lián)字段應(yīng)該創(chuàng)建索引 on 兩邊都要?jiǎng)?chuàng)建索引
- 查詢中排序的字段拧略，應(yīng)該創(chuàng)建索引
- 頻繁查找字段 覆蓋索引
- 查詢中統(tǒng)計(jì)或者分組字段芦岂，應(yīng)該創(chuàng)建索引 group by
-
哪些情況不需要?jiǎng)?chuàng)建索引
- 表記錄太少
- 經(jīng)常進(jìn)行增刪改操作的表
- 頻繁更新的字段
- where條件里使用頻率不高的字段
為什么兒使用組合索引
為了節(jié)省mysql索引存儲(chǔ)空間以及提升搜索性能
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3'）-
索引原則
- 一顆索引樹上創(chuàng)建3個(gè)索引 ： 省空間
- 三顆索引樹上分別創(chuàng)建1個(gè)索引瘪弓， 更容易實(shí)現(xiàn)覆蓋索引
- 遵循最左前綴原則
- 前綴 索引 like a%
- 從左到右匹配直到遇到范圍查詢 > < between like
- 創(chuàng)建組合索引(a,b,c,d)where a=1 and b=1 and c>3 and d=1到 a>3 停下來了
- (a,b,d,c)create index idx_com on t1(a,b,d,c);
索引失效分析
- 查看執(zhí)行計(jì)劃 explain
id垫蛆、select_type、table腺怯、type袱饭、possible_keys、key呛占、key_len虑乖、ref、rows晾虑、Extra - 案例數(shù)據(jù)
--用戶表
create table tuser(
id int primary key,
loginname varchar(100),
name varchar(100),
age int,
sex char(1),
dep int,
address varchar(100)
);
--部門表
create table tdep(
id int primary key,
name varchar(100)
);
--地址表
create table taddr(
id int primary key,
addr varchar(100)
);
--創(chuàng)建普通索引
mysql> alter table tuser add index idx_dep(dep);
--創(chuàng)建唯一索引
mysql> alter table tuser add unique index idx_loginname(loginname);
--創(chuàng)建組合索引
mysql> alter table tuser add index idx_name_age_sex(name,age,sex);
--創(chuàng)建全文索引
mysql> alter table taddr add fulltext ft_addr(addr);
1. id
- 每個(gè) SELECT語句都會(huì)自動(dòng)分配的一個(gè)唯一標(biāo)識(shí)符.
- 表示查詢中操作表的順序疹味，有三種情況：
-- id相同：執(zhí)行順序由上到下
-- id不同：如果是子查詢，id號(hào)會(huì)自增帜篇，id越大糙捺，優(yōu)先級(jí)越高。
-- id相同的不同的同時(shí)存在 - id列為null的就表示這是一個(gè)結(jié)果集笙隙，不需要使用它來進(jìn)行查詢洪灯。
2. select_type（重要）
- 查詢類型，主要用于區(qū)別普通查詢竟痰、聯(lián)合查詢(union签钩、union all)、子查詢等復(fù)雜查詢坏快。
simple
表示不需要union操作或者不包含子查詢的簡單select查詢铅檩。有連接查詢時(shí)，外層的查詢?yōu)閟imple莽鸿，且只有一個(gè)
primary
一個(gè)需要union操作或者含有子查詢的select昧旨，位于最外層的單位查詢的select_type即為primary。且只有一個(gè)
subquery
除了from字句中包含的子查詢外，其他地方出現(xiàn)的子查詢都可能是subquery
dependent subquery
與dependent union類似臼予，表示這個(gè)subquery的查詢要受到外部表查詢的影響
union
union連接的兩個(gè)select查詢鸣戴，第一個(gè)查詢是PRIMARY，除了第一個(gè)表外粘拾，第二個(gè)以后的表select_type都是union
dependent union
與union一樣窄锅，出現(xiàn)在union 或union all語句中，但是這個(gè)查詢要受到外部查詢的影響
union result
包含union的結(jié)果集缰雇，在union和union all語句中,因?yàn)樗恍枰獏⑴c查詢入偷，所以id字段為null
derived
from字句中出現(xiàn)的子查詢，也叫做派生表械哟，其他數(shù)據(jù)庫中可能叫做內(nèi)聯(lián)視圖或嵌套select
3. table
- 顯示的查詢表名疏之，如果查詢使用了別名，那么這里顯示的是別名
- 如果不涉及對(duì)數(shù)據(jù)表的操作暇咆，那么這顯示為null
- 如果顯示為尖括號(hào)括起來的就表示這個(gè)是臨時(shí)表锋爪，后邊的N就是執(zhí)行計(jì)劃中的id，表示結(jié)果來自于這個(gè)查詢產(chǎn)生爸业。
- 如果是尖括號(hào)括起來的<union M,N>其骄，與類似，也是一個(gè)臨時(shí)表扯旷，表示這個(gè)結(jié)果來自于union查詢的id為M,N的結(jié)果集拯爽。
4. type（重要）
依次從好到差：
system，const钧忽，eq_ref毯炮，ref，fulltext耸黑，ref_or_null桃煎，unique_subquery， index_subquery崎坊，range备禀，index_merge，index奈揍，ALL
除了all之外曲尸，其他的type都可以使用到索引，除了index_merge之外男翰，其他的type只可以用到一個(gè)索引
注意事項(xiàng)：最少要索引使用到range級(jí)別另患。
system，表中只有一行數(shù)據(jù)或者是空表蛾绎。
const昆箕，使用唯一索引或者主鍵鸦列，返回記錄一定是1行記錄的等值where條件時(shí)，通常type是const鹏倘。其他數(shù)據(jù)庫也叫做唯一索引掃描
eq_ref薯嗤，關(guān)鍵字:連接字段主鍵或者唯一性索引。都只能匹配到后表的一行結(jié)果
ref纤泵，針對(duì)非唯一性索引骆姐，使用等值（=）查詢非主鍵∧筇猓或者是使用了最左前綴規(guī)則索引的查詢玻褪。
fulltext，全文索引檢索
ref_or_null公荧，增加了null值的比較
unique_subquery带射，用于where中的in形式子查詢，子查詢返回不重復(fù)值唯一值
index_subquery循狰，用于in形式子查詢使用到了輔助索引或者in常數(shù)列表窟社，子查詢可能返回重復(fù)值，可以使用索引將子查詢?nèi)ブ亍?br> range晤揣，索引范圍掃描桥爽，常見于使用>,<,is null,between ,in ,like等運(yùn)算符的查詢中。
index_merge昧识，表示查詢使用了兩個(gè)以上的索引，最后取交集或者并集
index盗扒，關(guān)鍵字：條件是出現(xiàn)在索引樹中的節(jié)點(diǎn)的跪楞。可能沒有完全匹配索引侣灶。索引全表掃描
ALL, 全表掃描數(shù)據(jù)文件
5. possible_keys: 此次查詢中可能選用的索引甸祭，一個(gè)或多個(gè)、
6. key : 查詢真正使用到的索引
7. key_len: 處理查詢的索引長度,key_len只計(jì)算where條件用到的索引長度褥影，而排序和分組就算用到了索引池户，也不會(huì)計(jì)算到key_len中
8. ref
- 如果是使用的常數(shù)等值查詢，這里會(huì)顯示const
- 如果是連接查詢凡怎，被驅(qū)動(dòng)表的執(zhí)行計(jì)劃這里會(huì)顯示驅(qū)動(dòng)表的關(guān)聯(lián)字段
- 如果是條件使用了表達(dá)式或者函數(shù)校焦，或者條件列發(fā)生了內(nèi)部隱式轉(zhuǎn)換，這里可能顯示為func
9. rows: 執(zhí)行計(jì)劃中估算的掃描行數(shù)统倒，不是精確值（InnoDB不是精確的值寨典，MyISAM是精確的值，主要原因是InnoDB里面使用了MVCC并發(fā)機(jī)制）
10. Extra
- using temporary: 使用了臨時(shí)表存儲(chǔ)中間結(jié)果,MySQL在對(duì)查詢結(jié)果order by和group by時(shí)使用臨時(shí)表; 臨時(shí)表可以是內(nèi)存臨時(shí)表和磁盤臨時(shí)表房匆，執(zhí)行計(jì)劃中看不出來耸成，需要查看status變量报亩，used_tmp_table，used_tmp_disk_table才能看出來井氢。
- no tables used : 使用not in()形式子查詢或not exists運(yùn)算符的連接查詢弦追，這種叫做反連接
- using filesort : MySQL中無法利用索引完成的排序操作稱為“文件排序”
- using index
- using where: 存儲(chǔ)引擎返回的記錄并不是所有的都滿足查詢條件，需要在server層進(jìn)行過濾花竞。
- firstmatch(tb_name): 5.6.x開始引入的優(yōu)化子查詢的新特性之一骗卜，常見于where字句含有in()類型的子查詢。如果內(nèi)表的數(shù)據(jù)量比較大左胞，就可能出現(xiàn)這個(gè)
- loosescan(m..n): 在in()類型的子查詢中寇仓，子查詢返回的可能有重復(fù)記錄時(shí)，
就可能出現(xiàn)這個(gè)- filtered : 5.7之后的版本默認(rèn)就有這個(gè)字段烤宙，不需要使用explain extended了.這個(gè)字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在server層過濾后遍烦，剩下多少滿足查詢的記錄數(shù)量的比
例，注意是百分比躺枕，不是具體記錄數(shù)服猪。
參考網(wǎng)站
https://segmentfault.com/a/1190000008131735
https://blog.csdn.net/rewiner120/article/details/70598797
- 索引失效分析
1.全值匹配 *
2.最佳左前綴法則 組合索引->帶頭索引不能死，中間索引不能斷
3.不要在索引上做計(jì)算
4.范圍條件右邊的列失效
5.盡量使用覆蓋索引 也就是索引列和查詢列一致拐云，減少select *
6.索引字段上不要使用不等,會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描
7.主鍵索引字段上不可以判斷null
主鍵字段上不可以使用 null
索引字段上使用 is null / is not null 判斷時(shí)罢猪，可使用索引
8.索引字段使用like不以通配符開頭
9.索引字段字符串要加單引號(hào)
10.索引字段不要使用or索引字段使用 or 時(shí)，會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描
總結(jié)
