數(shù)據(jù)庫學(xué)習(xí)筆記-索引
[TOC]
索引
本質(zhì)
索引是數(shù)據(jù)結(jié)構(gòu)，是一種排好序的快速查找數(shù)據(jù)結(jié)構(gòu)
數(shù)據(jù)本身之外舍咖，數(shù)據(jù)庫還維護(hù)者一個滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式指向數(shù)據(jù)锉桑，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上實現(xiàn)高級查找算法排霉，這種數(shù)據(jù)結(jié)構(gòu)就是索引
官方定義
索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)
目的
提高查找效率，類比字典
功能
- 排序（影響到order by后的排序條件排序的速度）
- 查找速度（影響到where后面的條件查找速度）
優(yōu)勢
- 類似大學(xué)圖書館建書目錄索引民轴，提高數(shù)據(jù)檢索的效率攻柠，降低數(shù)據(jù)庫的IO成本
- 通過索引對數(shù)據(jù)進(jìn)行排序球订，降低數(shù)據(jù)排序的成本，降低了CPU的消耗
劣勢
- 索引也是一張表瑰钮，表保存了主鍵與索引字段冒滩，并指向?qū)嶓w表的記錄，所以索引列也是要占空間的
- 索引大大提高了查詢速度浪谴，但是卻會降低更新表的速度开睡，如對表進(jìn)行insert、update和delete较店，因為更新表士八，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段梁呈，都會因為調(diào)整因為更新所帶來的鍵值變化后的索引信息
索引分類
- 單值索引-一個索引只包含單個列婚度，一張表可以又多個單值索引
- 唯一索引-索引列值必須唯一，但是允許有空值
- 復(fù)合索引官卡，一個索引包含多個列
MySQL索引結(jié)構(gòu)
-
BTree索引
檢索原理：
3層的b+樹可以表示上百萬的數(shù)據(jù)蝗茁，查詢一個存在的數(shù)據(jù)只需要3次數(shù)據(jù)庫IO
什么時候需要創(chuàng)建索引
- 主鍵自動建立唯一索引
- 頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
- 查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
- 頻繁更新的字段不適合創(chuàng)建索引寻咒，因為每次更新不單單是更新了記錄還會更新索引文件
- where條件用不到的字段不創(chuàng)建索引
- 高并發(fā)下傾向創(chuàng)建組合索引
- 查詢中排序的字段哮翘，排序字段如果通過索引去訪問將大大提高排序速度
- 查詢中統(tǒng)計或者分組字段
什么情況不需要創(chuàng)建索引
- 表記錄太少
- 經(jīng)常增刪改的表
- 數(shù)據(jù)重復(fù)且分布平均的表，索引的選擇性是指列中不同值的數(shù)據(jù)與總數(shù)據(jù)的比毛秘，重復(fù)的內(nèi)容建立索引的意義不大
性能分析
MySQL Optimizer（MySQL有專門負(fù)責(zé)優(yōu)化select語句的優(yōu)化器模塊饭寺，主要功能：通過計算分析系統(tǒng)中收集到的統(tǒng)計信息，客戶端請求的Query提供它認(rèn)為最優(yōu)的執(zhí)行計劃）
-
explain
用法：explain+寫的SQL（explain select * from talbe）
-
explain透露的信息：表的讀取順序叫挟、數(shù)據(jù)讀取操作的操作類型艰匙、哪些索引可以使用、哪些索引被實際使用抹恳、表之間的引用和每張表有多少行被優(yōu)化器查詢
-
id - select查詢的序列號员凝，包含一組數(shù)字，表示查詢中執(zhí)行select子句或操作表的順序
- id相同的情況奋献，表示執(zhí)行的順序由上至下
- id不同的情況健霹，如果是子查詢id值會遞增，id值越大優(yōu)先級越高瓶蚂，最早被執(zhí)行糖埋，主查詢最后執(zhí)行
- id相同不同同時存在，id大的先執(zhí)行扬跋，id相同的順序執(zhí)行
-
select_type
- simple-普通查詢阶捆，查詢中不包含子查詢或者union
- primary-主查詢，最后執(zhí)行的查詢
- subquery-子查詢，包含了子查詢
- derived 在from列表中包含的子查詢被標(biāo)記為derived（衍生）MySQL會遞歸執(zhí)行這些子查詢洒试，把結(jié)果放在臨時表中
- 若第二個select出現(xiàn)在union后倍奢，則被標(biāo)記為union；若union包含在from子句的子查詢中垒棋，外層select將被標(biāo)記為：derived
- union result 兩種union結(jié)果的合并
table
-
type-顯示查詢使用了何種類型
查詢類型優(yōu)化排序：system>const>eq_ref>ref>range>index>all
system-表只有一行記錄卒煞，等于系統(tǒng)表，屬于const的例叼架，平時不會出現(xiàn)畔裕，基本可以忽略
const-通過索引一次就查詢到了,const用于比較primary key或者unique索引。因為只匹配一行數(shù)據(jù)乖订，所以很快扮饶。用于直接按主鍵或者唯一索引讀取的情況。
eq_ref-唯一性索引掃描乍构，對于每個索引鍵甜无，表中只有一條記錄與之匹配。常見于主鍵或者唯一索引掃描哥遮。用于連表查詢的情況岂丘。
ref-非唯一性索引掃描，返回匹配某個單獨值得所有行眠饮，本質(zhì)上也是一種索引訪問奥帘，它返回的所有匹配某個單獨值的行，然而仪召，它可能會找到多個符合條件的行寨蹋，所以他應(yīng)該屬于查找和掃描的混合體
range-只檢索給定檢索給定范圍的行，使用一個索引來選擇行扔茅。一般就是where語句中出現(xiàn)了between钥庇、<、>咖摹、in等查詢
index-Full Index Scan，index與ALL的區(qū)別index類型只遍歷索引樹难述。通常比ALL快萤晴，因為索引文件通常比數(shù)據(jù)文件小，index從索引中讀胁后，all從硬盤中讀
-
all-Full Table Scan,遍歷全表以找到匹配的行
一般來說店读，最好能達(dá)到range，最好能達(dá)到ref
-
possible_keys和key
possible_keys-理論上用到的索引
key-實際上用到的索引
分幾種情況 理論上會用到某索引攀芯，實際上用到了某索引屯断。理論上不會用到某索引，實際上用到了某索引。理論上會用到某索引殖演，實際上沒用到某索引
key_len -表示索引中使用的字節(jié)數(shù)氧秘，可通過該列計算查詢中使用的索引的長度。在不損失精確性的情況下趴久，該長度越短越好丸相。key_len顯示的值為索引字段的最大可能長度，并非實際使用長度彼棍，即key_len是根據(jù)表定義計算而得灭忠，不是通過表內(nèi)檢索出的
ref-顯示索引的哪一列被使用了，如果可能的話座硕，是一個常數(shù)弛作。哪些列或者常量用于被查找索引列上的值
rows-大致估算找到所需記錄需要加載的記錄行數(shù)
-
extra-包含不適合在其他列中顯示但是十分重要的額外信息
- using filesort-說明mysql會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取华匾。MySQL中無法利用索引完成的排序操作稱為“文件排序”映琳，出現(xiàn)這種說明排序效率很低
- using remporary-為了排序新建了個內(nèi)部的臨時表保存中間表結(jié)果，再刪除臨時表瘦真。常見于order by,group by
- using index-相應(yīng)的select操作使用了覆蓋索引刊头，效率不錯。同時出現(xiàn)了using where 說明用where進(jìn)行了索引鍵值的查找诸尽，沒有出現(xiàn)說明索引用來讀取數(shù)據(jù)而非查找動作
- using where
- using join buffer -使用連接緩存
- impossible where - where子句的值總是false原杂，不能用來獲取元組
- select tables optimized away
- distinct-優(yōu)化distinct操作，找到第一個匹配的值就收工
-
-
MySQL常見瓶頸
- CPU：CPU在飽和的時候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或者從磁盤上讀取數(shù)據(jù)的時候
- IO:磁盤IO瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時候
- 服務(wù)器硬件的性能瓶頸：top,free您机，iostat和vmstat來查看系統(tǒng)的性能狀態(tài)
