SQL語句優(yōu)化

MySQL大表優(yōu)化

一、什么是索引先馆？

索引是幫助 MySQL 高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)跋涣。
在關(guān)系數(shù)據(jù)庫中件缸，索引是一種單獨(dú)的亿乳、物理的對數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種存儲結(jié)構(gòu)访惜，它是某個(gè)表中一列或若干列值的集合和相應(yīng)的指向表中物理標(biāo)識這些值的數(shù)據(jù)頁的邏輯指針清單溢陪。索引的作用相當(dāng)于圖書的目錄朋腋，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容齐疙。

索引提供指向存儲在表的指定列中的數(shù)據(jù)值的指針，然后根據(jù)指定的排序順序?qū)@些指針排序旭咽。數(shù)據(jù)庫使用索引以找到特定值贞奋，然后順指針找到包含該值的行。這樣可以使對應(yīng)于表的 SQL 語句執(zhí)行得更快轻专，可快速訪問數(shù)據(jù)庫表中的特定信息忆矛。

當(dāng)表中有大量記錄時(shí)，若要對表進(jìn)行查詢，第一種搜索信息方式是全表搜索催训，是將所有記錄一一取出洽议，和查詢條件進(jìn)行一一對比，然后返回滿足條件的記錄漫拭，這樣做會(huì)消耗大量數(shù)據(jù)庫系統(tǒng)時(shí)間亚兄，并造成大量磁盤 I/O 操作；第二種就是在表中建立索引采驻，然后在索引中找到符合查詢條件的索引值审胚，最后通過保存在索引中的 ROWID(相當(dāng)于頁碼)快速找到表中對應(yīng)的記錄。索引是一種數(shù)據(jù)結(jié)構(gòu)(平衡樹非二叉)礼旅，即 B 樹/B+ 樹膳叨，通過不斷的縮小想要獲得數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果，同時(shí)把隨機(jī)事件變成順序事件痘系。

二菲嘴、為什么要建立索引？

一個(gè)沒有索引的數(shù)據(jù)庫表就相當(dāng)于一本沒有索引的新華字典汰翠，當(dāng)想找出其中一個(gè)漢字的時(shí)候龄坪，無異于尋找 MH370 碎片。為指定的字段創(chuàng)建索引之后复唤，當(dāng)根據(jù)條件查找數(shù)據(jù)的時(shí)候健田，數(shù)據(jù)庫引擎就可以利用查找算法(二分查找法)高效的查出來。

三佛纫、創(chuàng)建索引的原則

索引要占用存儲空間妓局，建立索引的時(shí)候有一定的規(guī)則可循。

1??最左前綴原則

一般在 where 條件中有兩個(gè)及以上字段時(shí)雳旅，會(huì)建復(fù)合索引跟磨。MySQL 中的索引可以以一定順序引用多個(gè)列间聊，這種索引叫做復(fù)合索引攒盈。對于復(fù)合索引，要遵守最左前綴原則哎榴，該原則和 B+ 樹中的“最左前綴原理”有關(guān)型豁。什么是“最左前綴原則”？

對于該表尚蝌，如果按照 name 字段來建立索引的話迎变，采用 B+ 樹的結(jié)構(gòu)，大概的索引結(jié)構(gòu)如下：

如果要進(jìn)行模糊查找飘言，查找 name 以“張”開頭的所有人的 ID衣形，即 sql 語句為：

select ID from table where name like '張%'

由于在 B+ 樹結(jié)構(gòu)的索引中，索引項(xiàng)是按照索引定義里面出現(xiàn)的字段順序排序的，索引在查找的時(shí)候谆吴，可以快速定位到 ID 為 100 的張一倒源，然后直接向右遍歷所有張開頭的人，直到條件不滿足為止句狼。也就是說笋熬，當(dāng)找到第一個(gè)滿足條件的人之后，直接向右遍歷就可以了腻菇，由于索引是有序的胳螟，所有滿足條件的人都會(huì)聚集在一起。而這種定位到最左邊筹吐，然后向右遍歷尋找的方式糖耸，就是最左前綴原則。

一般丘薛，復(fù)合索引是一個(gè)有序元組蔬捷，其中各個(gè)元素均為數(shù)據(jù)表的一列，實(shí)際上要嚴(yán)格定義索引需要用到關(guān)系代數(shù)榔袋。另外周拐，單列索引可以看成聯(lián)合索引元素?cái)?shù)為 1 的特例。如：
[id,name,age,school]凰兑，相當(dāng)于創(chuàng)建了(id)妥粟、(id,name)、(id,name,age)和(id,name,age,school) 四個(gè)索引吏够。直接用 id勾给，或者 id、name 或 id锅知、name播急、age 這樣的順序可以命中索引；id售睹、school 只有 id 部分用到了索引桩警；name、school 無法使用這個(gè)索引昌妹。所以在創(chuàng)建復(fù)合索引的時(shí)候一定要注意索引字段順序：①常用的查詢字段放在最前面捶枢。②需要考慮字段值去重之后的個(gè)數(shù)，較多的放前面飞崖。

復(fù)合索引失效示例：

1. 在使用索引字段作為條件時(shí)烂叔，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件才能保證系統(tǒng)使用該索引固歪，否則該索引將不會(huì)被使用蒜鸡。如：where name="xxp" and age=18 and school ="wg"; 按照最左匹配原則，這個(gè)條件就沒法走索引，因?yàn)槭紫缺仨氂?id逢防。

2. 如果條件都用上了康聂，但是順序不同，現(xiàn)在的查詢引擎會(huì)自動(dòng)優(yōu)化為匹配復(fù)合索引的順序胞四，這樣是能夠命中索引的恬汁。但是應(yīng)盡可能的讓字段順序與索引順序相一致。如：where name="xxp" and id=1 and age<18 使用的索引仍然為(id,name,age)組合辜伟。

3. MySQL 會(huì)從左至右匹配氓侧，直到遇到范圍查找(>、<、like、between)就停止左冬。如：

select * from tab where id=1 and name="xxp" and age<18 and school="wg";

實(shí)際用到的索引為(id,name,age)铅乡。因?yàn)橛龅搅?age<18 就停止了秃流，school 列就沒有用上。

2??不冗余原則
? 盡量擴(kuò)展索引、不要新建索引
mysql目前主要索引有：FULLTEXT,HASH,BTREE
好的索引可以提高查詢效率，不好的索引不但不會(huì)起作用氓癌，反而給 DB 帶來負(fù)擔(dān)，基于 BTREE 結(jié)構(gòu)贫橙，插入贪婉、修改都會(huì)重新調(diào)整索引結(jié)構(gòu)，存儲成本增加卢肃，寫效率降低疲迂，同時(shí) DB 系統(tǒng)也要消耗資源去維護(hù)∧妫基于最左前綴原則尤蒿，盡量在原有基礎(chǔ)上擴(kuò)展索引，不要新增索引幅垮。能用單索引腰池，不用聯(lián)合索引；能用窄索引军洼，不用寬索引巩螃；能復(fù)用索引，不新建索引匕争。

3??最大選擇性原則

一般兩種情況不建議建索引：

1. 一兩千條記錄，沒必要建索引爷耀，讓查詢做全表掃描就好了甘桑。因?yàn)椴皇墙司鸵欢〞?huì)走索引，執(zhí)行計(jì)劃會(huì)選擇一個(gè)最優(yōu)的方式，MySQL 輔助索引的葉子節(jié)點(diǎn)并不直接存儲實(shí)際數(shù)據(jù)跑杭，只是主建 ID铆帽，再通過主鍵索引二次查找。這么一來全表可能很有可能效率更高德谅。
2. 索引選擇性較低的情況爹橱。
   所謂選擇性(Selectivity)，是指不重復(fù)的索引值(也叫基數(shù)窄做，Cardinality)與表記錄數(shù)(#T)的比值愧驱。Index Selectivity = Cardinality / #T
   顯然選擇性的取值范圍為(0, 1]，選擇性越高的索引價(jià)值越大椭盏，這是由 B+ 樹的性質(zhì)決定的组砚。

選擇區(qū)分度高的列做索引。什么是區(qū)分度高的字段呢掏颊？count(distinct 字段)/count(*)糟红，當(dāng)然最大就是 1，也就是唯一索引乌叶。這個(gè)值越大盆偿，查詢的效率越高。當(dāng)這個(gè)值小到一定的程度的時(shí)候准浴，數(shù)據(jù)庫就會(huì)放棄索引進(jìn)行全表掃描陈肛。創(chuàng)建復(fù)合索引，需要注意把區(qū)分度最大的放到最前面兄裂，也就是值越大的放前面句旱。

假設(shè)一個(gè)表有一百萬的數(shù)據(jù)，其中有一個(gè)性別的字段晰奖。然后為該字段建了一個(gè)索引谈撒，自以為查詢性能提升 n 倍。其實(shí)匾南，就相當(dāng)于把一百萬條數(shù)據(jù)按照性別分別放到兩個(gè)箱子里面啃匿，每箱子里面的性別都是一樣的，索引起不了任何作用蛆楞，二分查找也用不上溯乒，只能用暴力算法解決，全表掃描豹爹。相反裆悄，可以為身份證號碼建立唯一索引，這樣可以從頭到尾用二分查找法查找臂聋，非常高效光稼。

四或南、索引類型

1??普通索引【normal】：使用字段關(guān)鍵字建立的索引，主要是提高查詢速度艾君。
2??唯一索引【unique】：加速查詢 + 列值唯一(可以有null)+ 表中可以有多個(gè)
3??主鍵索引【primary】：加速查詢 + 列值唯一(不可以有null)+ 表中只有一個(gè)
4??復(fù)合索引：多列值組成一個(gè)索引采够，專門用于組合搜索，其效率大于索引合并
5??全文索引【full text】：對文本的內(nèi)容進(jìn)行分詞冰垄，進(jìn)行搜索蹬癌。在比較老的版本中，只有 myisam 引擎支持全文索引虹茶，在 innodb5.6后引擎也支持全文索引逝薪，在 mysql 中全文索引不支持中文。一般使用 sphinx 集合coreseek 來實(shí)現(xiàn)中文的全文索引写烤。
6??spatial：空間索引翼闽。

ps.索引合并，使用多個(gè)單列索引組合搜索

覆蓋索引洲炊，select的數(shù)據(jù)列只從索引中就能夠取得感局，不必讀取數(shù)據(jù)行，換句話說查詢列要被所建的索引覆蓋

五暂衡、索引方法

MySQL目前主要有以下幾種索引類型：FULLTEXT询微，HASH，BTREE狂巢，RTREE撑毛。

1. 全文索引~FULLTEXT
   目前只有MyISAM引擎支持。其可以在CREATE TABLE 唧领，ALTER TABLE 藻雌，CREATE INDEX 使用，不過目前只有 CHAR斩个、VARCHAR 胯杭，TEXT 列上可以創(chuàng)建全文索引。全文索引并不是和MyISAM一起誕生的受啥，它的出現(xiàn)是為了解決where name like “%word%"這類針對文本的模糊查詢效率較低的問題做个。

2. HASH
   由于HASH的唯一（幾乎100%的唯一）及類似鍵值對的形式，很適合作為索引滚局。HASH索引可以一次定位居暖，不需要像樹形索引那樣逐層查找,因此具有極高的效率。但是藤肢，這種高效是有條件的太闺，即只在“=”和“in”條件下高效，對于范圍查詢谤草、排序及組合索引仍然效率不高跟束。

3. BTREE
   BTREE索引就是一種將索引值按一定的算法莺奸，存入一個(gè)樹形的數(shù)據(jù)結(jié)構(gòu)中（二叉樹）丑孩，每次查詢都是從樹的入口root開始冀宴，依次遍歷node，獲取leaf温学。這是MySQL里默認(rèn)和最常用的索引類型略贮。

4. RTREE
   RTREE在MySQL很少使用，僅支持geometry數(shù)據(jù)類型仗岖，支持該類型的存儲引擎只有MyISAM逃延、BDb、InnoDb轧拄、NDb揽祥、Archive幾種。相對于BTREE檩电，RTREE的優(yōu)勢在于范圍查找拄丰。

六、B樹

1. 定義任意非葉子結(jié)點(diǎn)最多只有M個(gè)兒子俐末；且M>2料按；
2. 根結(jié)點(diǎn)的兒子數(shù)為[2, M]；
3. 除根結(jié)點(diǎn)以外的非葉子結(jié)點(diǎn)的兒子數(shù)為[M/2, M]卓箫；
4. 每個(gè)結(jié)點(diǎn)存放至少M(fèi)/2-1（取上整）和至多M-1個(gè)關(guān)鍵字载矿；（至少2個(gè)關(guān)鍵字）
5. 非葉子結(jié)點(diǎn)的關(guān)鍵字個(gè)數(shù)=指向兒子的指針個(gè)數(shù)-1；
6. 非葉子結(jié)點(diǎn)的關(guān)鍵字：K[1], K[2], …, K[M-1]烹卒；且K[i] < K[i+1]闷盔；
7. 非葉子結(jié)點(diǎn)的指針：P[1], P[2], …, P[M]；其中P[1]指向關(guān)鍵字小于K[1]的子樹旅急，P[M]指向關(guān)鍵字大于K[M-1]的子樹逢勾，其它P[i]指向關(guān)鍵字屬于(K[i-1], K[i])的子樹；
8. 所有葉子結(jié)點(diǎn)位于同一層坠非；

七敏沉、B-樹

B-樹的搜索，從根結(jié)點(diǎn)開始炎码，對結(jié)點(diǎn)內(nèi)的關(guān)鍵字（有序）序列進(jìn)行二分查找盟迟，如果命中則結(jié)束，否則進(jìn)入查詢關(guān)鍵字所屬范圍的兒子結(jié)點(diǎn)潦闲；重復(fù)攒菠，直到所對應(yīng)的兒子指針為空，或已經(jīng)是葉子結(jié)點(diǎn)歉闰；

B-樹的特性：

1. 關(guān)鍵字集合分布在整顆樹中辖众；
2. 任何一個(gè)關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個(gè)結(jié)點(diǎn)中卓起；
3. 搜索有可能在非葉子結(jié)點(diǎn)結(jié)束；
4. 其搜索性能等價(jià)于在關(guān)鍵字全集內(nèi)做一次二分查找凹炸；
5. 自動(dòng)層次控制戏阅；

由于限制了除根結(jié)點(diǎn)以外的非葉子結(jié)點(diǎn)，至少含有M/2個(gè)兒子啤它，確保了結(jié)點(diǎn)的至少利用率奕筐，其最底搜索性能為：

其中，M為設(shè)定的非葉子結(jié)點(diǎn)最多子樹個(gè)數(shù)变骡，N為關(guān)鍵字總數(shù)离赫；
所以B-樹的性能總是等價(jià)于二分查找（與M值無關(guān)），也就沒有B樹平衡的問題塌碌；
由于M/2的限制渊胸，在插入結(jié)點(diǎn)時(shí)，如果結(jié)點(diǎn)已滿台妆，需要將結(jié)點(diǎn)分裂為兩個(gè)各占M/2的結(jié)點(diǎn)翎猛；刪除結(jié)點(diǎn)時(shí)，需將兩個(gè)不足M/2的兄弟結(jié)點(diǎn)合并频丘；

八办成、為什么用 B+ 樹做索引而不用哈希表做索引

1??B+ 樹是 B- 樹 的變體，也是一種多路搜索樹

1. 其定義基本與 B- 樹同搂漠，除了：
2. 非葉子結(jié)點(diǎn)的子樹指針與關(guān)鍵字個(gè)數(shù)相同迂卢；
3. 非葉子結(jié)點(diǎn)的子樹指針P[i]，指向關(guān)鍵字值屬于[K[i], K[i+1])的子樹（B-樹是開區(qū)間）桐汤；
4. 為所有葉子結(jié)點(diǎn)增加一個(gè)鏈指針而克；
5. 所有關(guān)鍵字都在葉子結(jié)點(diǎn)出現(xiàn)；

2??B+的特性

1. 所有關(guān)鍵字都出現(xiàn)在葉子結(jié)點(diǎn)的鏈表中(稠密索引)怔毛，且鏈表中的關(guān)鍵字恰好是有序的员萍；
2. 不可能在非葉子結(jié)點(diǎn)命中；
3. 非葉子結(jié)點(diǎn)相當(dāng)于是葉子結(jié)點(diǎn)的索引(稀疏索引)拣度，葉子結(jié)點(diǎn)相當(dāng)于是存儲(關(guān)鍵字)數(shù)據(jù)的數(shù)據(jù)層碎绎；
4. 更適合文件索引系統(tǒng)。

3??原因

1. 哈希表是把索引字段映射成對應(yīng)的哈希碼然后再存放在對應(yīng)的位置抗果，這樣的話筋帖，如果要進(jìn)行模糊查找的話，顯然哈希表這種結(jié)構(gòu)是不支持的冤馏，只能遍歷這個(gè)表日麸。而 B+ 樹則可以通過最左前綴原則快速找到對應(yīng)的數(shù)據(jù)。
2. 如果要進(jìn)行范圍查找逮光，例如查找 ID 為 100 ~ 400 的人代箭，哈希表同樣不支持墩划，只能遍歷全表。
3. 索引字段通過哈希映射成哈希碼嗡综，如果很多字段都剛好映射到相同值的哈希碼的話乙帮，那么形成的索引結(jié)構(gòu)將會(huì)是一條很長的鏈表，這樣的話蛤高，查找的時(shí)間就會(huì)大大增加蚣旱。

九碑幅、主鍵索引和非主鍵索引有什么區(qū)別

主鍵索引和非主鍵索引的示意圖如下：

結(jié)構(gòu)對比

其中 R 代表一整行的值。

由圖看出沟涨，主鍵索引和非主鍵索引的區(qū)別是：非主鍵索引的葉子節(jié)點(diǎn)存放的是主鍵的值恤批，而主鍵索引的葉子節(jié)點(diǎn)存放的是整行數(shù)據(jù)。非主鍵索引也被稱為二級索引裹赴，而主鍵索引也被稱為聚簇索引喜庞。

1??根據(jù)這兩種結(jié)構(gòu)進(jìn)行查詢，看看區(qū)別：

1. 執(zhí)行select * from table where ID = 100棋返，即主鍵查詢的方式延都，只需要搜索 ID 這棵 B+ 樹。
2. 執(zhí)行select * from table where k = 1睛竣，即非主鍵的查詢方式晰房，則先搜索 k 索引樹，得到 ID = 100射沟，再到 ID 索引樹搜索一次殊者，這個(gè)過程也被稱為回表。

2??聚集索引和非聚集索引的區(qū)別：

1. 聚集索引表示表中存儲的數(shù)據(jù)按照索引的順序存儲验夯，檢索效率比非聚集索引高猖吴，但對數(shù)據(jù)更新影響較大。(比如主鍵索引)
2. 非聚集索引表示數(shù)據(jù)存儲在一個(gè)地方挥转，索引存儲在另一個(gè)地方海蔽，索引帶有指針指向數(shù)據(jù)的存儲位置。非聚集索引檢索效率比聚集索引低绑谣，但對數(shù)據(jù)更新影響較小党窜。

十、為什么建議使用主鍵自增的索引域仇？

但是如果插入的是 ID = 350 的一行數(shù)據(jù)，由于 B+ 樹是有序的暇务，那么需要將下面的葉子節(jié)點(diǎn)進(jìn)行移動(dòng)泼掠，騰出位置來插入 ID = 350 的數(shù)據(jù)怔软，這樣就會(huì)比較消耗時(shí)間。如果剛好 R4 所在的數(shù)據(jù)頁已經(jīng)滿了择镇，需要進(jìn)行頁分裂操作挡逼，這樣會(huì)更加糟糕。

但是腻豌，如果主鍵是自增的家坎，每次插入的 ID 都會(huì)比前面的大，那么每次只需要在后面插入就行吝梅， 不需要移動(dòng)位置虱疏、分裂等操作，這樣可以提高性能苏携。也就是為什么建議使用主鍵自增的索引做瞪。