《高性能MySQL(第3版)》摘要——索引篇
簡介
索引是存儲(chǔ)引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。
索引對于良好的性能非常關(guān)鍵，尤其是當(dāng)表中的數(shù)據(jù)量越來越大時(shí)，索引對性能的影響愈發(fā)重要。當(dāng)數(shù)據(jù)量較少且負(fù)載較低時(shí)，不恰當(dāng)?shù)乃饕龑π阅艿挠绊懣赡苓€不明顯，但當(dāng)數(shù)據(jù)量逐漸增大時(shí)，性能則會(huì)急劇下降。
索引優(yōu)化應(yīng)該是對查詢性能優(yōu)化最有效的手段了。索引能夠輕易將查詢性能提高幾個(gè)數(shù)量級，“最優(yōu)”的索引有時(shí)比一個(gè)“好的”索引性能要好兩個(gè)數(shù)量級。創(chuàng)建一個(gè)真正“最優(yōu)”的索引經(jīng)常需要重寫查詢。
一、索引基礎(chǔ)
MySQL的索引類似于書籍對應(yīng)了頁碼的目錄。
索引可以包含一個(gè)或多個(gè)列的值。如果索引包含多個(gè)列，name列的順序也十分重要，因?yàn)镸ySQL只能高效的使用索引的最左前綴列。創(chuàng)建一個(gè)包含兩個(gè)列的索引，和創(chuàng)建兩個(gè)只包含一列的索引的大不相同的。
索引的類型
1.B-Tree索引
當(dāng)人們討論索引的時(shí)候如果沒有特別指明類型，那么多半說的是B-Tree索引，它使用B-Tree數(shù)據(jù)結(jié)構(gòu)來存儲(chǔ)數(shù)據(jù)。
B-Tree對索引列是順序組織存儲(chǔ)的，索引很適合查找范圍數(shù)據(jù)。例如，在一個(gè)基于文本域的索引樹上，按字母順序傳遞連續(xù)的值進(jìn)行查找是非常合適的，所以像“找出所有以I到K開頭的名字”這樣的查找效率是會(huì)常高。
假如有如下數(shù)據(jù)表：
CREATE TABLE people(
last_name varchar(50)? ?not null,
first_naem varchar(50)? ?not null,
dob date not null,
gender enum('m','f')? not null,
key(last_name, first_naem, dob)
);
對于表中的每一行數(shù)據(jù)，索引中包含了last_name, first_name和dob列的值。
B-Tree索引適合于全鍵值、鍵值范圍或鍵前綴查找。其中鍵前綴查找只適用于根據(jù)最左前綴的查找。
WHERE條件中查詢條件可任意順序，MySQl的查詢優(yōu)化器會(huì)優(yōu)化到匹配最佳索引。
前面所述的索引對如下類型的查詢有效。
全值匹配：
select * from people where last_name = 'Allen' and first_name = 'Cuba' and dob = '1960-01-01';
全值匹配指的是和索引中的所有列進(jìn)行匹配，例如前面提到的索引可用于查找姓名為Cuba Allen、出生于1960-01-01的人。
匹配最左前綴：
select * from people where last_name = 'Allen';
前面提到的索引可用于查找所有姓為Allen的人，即只使用索引的第一列。
匹配列前綴：
select * from people where last_name like 'J%';
也可以只匹配某一列的值得開頭部分。例如前面提到的索引可用于查找所有以J開頭的姓的人。這里也只用了索引的第一列。
匹配范圍值：
select * from people where last_name between 'Allen' and 'Barrymore';
例如前面提到的索引可用于查找姓在Allen和Barrymore之間的人。這里也只使用了索引的第一列。
精確匹配某一列并范圍匹配另外一列：
select * from people where last_name = 'Allen' and first_name like 'K%';
前面提到的索引也可用于查找所有姓為Allen，并且名字是字母K開頭的人。即第一列l(wèi)ast_name全匹配，第二列first_name范圍匹配。
只訪問索引的查詢：
select last_name, first_name from people where last_name = 'Allen' and first_name = 'Cube';
B-Tree通常可以支持“只訪問索引的查詢”，即查詢只需要訪問索引，而無須訪問數(shù)據(jù)行（覆蓋索引）。
因?yàn)樗饕龢渲械墓?jié)點(diǎn)是有序的，所以除了按值查找之外，索引還可以用于查詢中的ORDER BY操作（按順序查找）。一般來說，如果B-Tree可以按照某種方式查找到值，那么也可以按照這種方式用于排序。所以，如果ORDER BY字句滿足前面列出的幾種查詢類型，則這個(gè)索引也可以滿足對應(yīng)的排序需求。
下面是一些關(guān)于B-Trss索引的限制：
如果不是按照索引的最左列開始查找，則無法使用索引。
select * from people where first_name = 'Bill';
select * from people where dob = 'xxxx-xx-xx';
select * from people where last_name like '%X';
例如上面例子中的索引無法用于查找名字為Bill的人，也無法查找某個(gè)特定生日的人，因?yàn)檫@兩列都不是最左數(shù)據(jù)列。類似地，也無法查找姓氏以某個(gè)字母結(jié)尾的人。
不能跳過索引中的列。
select * from people where last_name = 'Smith' and dob = 'xxxx-xx-xx';（只能使用索引的第一列）
也就是說，前面所述的索引無法用于查找姓為Smith并且在某個(gè)特定日期出生的人。如果不指定名（first_name），則MySQL只能使用索引的第一列。
如果查詢中有某個(gè)列的范圍查詢，則其右邊所有列都無法使用索引優(yōu)化查找。
select * from people where?last_name='Smith' AND first_name?LIKE 'J%'?AND dob = '1976-12-23';
這個(gè)查詢只能使用索引的前兩列，因?yàn)檫@里L(fēng)IKE是一個(gè)范圍條件。如果范圍查詢列值得數(shù)量有限，那么可以通過使用多個(gè)等于條件來代替范圍條件。
到這里讀者應(yīng)該可以明白，前面提到的索引列的順序是多么重要：這些限制都和索引列的順序有關(guān)。在優(yōu)化性能的時(shí)候，可能需要使用相同的列但順序不同的索引來滿足不同類型的查詢需求。
2.哈希索引
3.空間數(shù)據(jù)索引（R-Tree）
4.全文索引
5.其他索引類別
二、索引的優(yōu)點(diǎn)
1.索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量。
2.索引可以幫助服務(wù)器避免排序和臨時(shí)表。
3.索引可以將隨機(jī)I/O變?yōu)轫樞騃/O。
索引是最好的解決方案嗎？
索引并不總是最好的工具。總的來說，只有當(dāng)索引幫助存儲(chǔ)引擎快速查找到記錄帶來的好處大于其帶來的額外工作時(shí)，索引才是有效的。對于非常小的表，大部分情況下簡單的全表掃描更高效。對于中到大型的表，索引就非常有效。但對于特大型的表，建立和使用索引的代價(jià)將隨之增長。這種情況下，則需要一種技術(shù)可以直接區(qū)分出查詢需要的一組數(shù)據(jù)，而不是一條記錄一條記錄匹配。例如可以使用分區(qū)技術(shù)。如果表的數(shù)量特別多，可以建立一個(gè)元數(shù)據(jù)信息表，用來查詢需要用到的某些特性。對于TB級別的數(shù)據(jù)，定位單條記錄的意義不大，所以經(jīng)常會(huì)使用塊級別元數(shù)據(jù)技術(shù)來代替索引。
三、高性能的索引策略
1.獨(dú)立的列
“獨(dú)立的列”是指索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)。
反例：mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(data_col) <= 10;
我們應(yīng)該養(yǎng)成簡化WHERE條件的習(xí)慣，始終將索引列單獨(dú)放在比較符號的一側(cè)。
2.前綴索引和索引選擇性
字符列太長會(huì)讓索引變得大且慢，通常可以索引開始的部分字符，這樣可以大大節(jié)約索引空間，從而提高索引效率。
但這樣會(huì)降低索引的選擇性。索引的選擇性是指，不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值。
索引的選擇性越高則查詢效率越高，因?yàn)檫x擇性高的索引可以讓MySQL在查找時(shí)過濾掉更多的行。唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。
一般情況下某個(gè)列前綴的選擇性也是足夠高的，足以滿足查詢性能。
對于BLOB、TEXT或者更長的VARCHAR類型的列，必須使用前綴索引，因?yàn)镸ySQL不允許索引這些列的完整長度。
訣竅在于要選擇足夠長的前綴以保證較高的選擇性，同時(shí)又不能太長（以便節(jié)約空間）。
創(chuàng)建前綴索引：mysql> ALTER TABLE sakila.city ADD KEY(city(7));
3.多列索引
一個(gè)常見的錯(cuò)誤就是，為每個(gè)列創(chuàng)建獨(dú)立的索引，后者按照錯(cuò)誤的順序創(chuàng)建多列索引。
4.選擇合適的索引列順序
對于如何選擇索引的列順序有一個(gè)經(jīng)驗(yàn)法則：將選擇性最高的列放到索引最前列。但一定要記住別忘了WHERE字句中的排序、分組和范圍條件等其他因素，這些因素可能對查詢性能造成非常大的影響。
5.聚簇索引
6.覆蓋索引
如果一個(gè)索引包含（或者說覆蓋）所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。
7.使用索引掃描來做排序
只有當(dāng)索引的列順序和ORDER BY子句的順序完全一致，并且所有列的排序方向（倒序或正序）都一樣時(shí)，MySQL才能夠使用索引來對結(jié)果排序。如果查詢需要關(guān)聯(lián)多張表，則只有當(dāng)ORDER BY子句引用的字段全部為第一個(gè)表時(shí)，才能使用索引做排序。ORDER BY子句和查找型查詢的限制是一樣的：需要滿足索引的最左前綴的要求；否則，MySQL都需要執(zhí)行排序操作，而無法利用索引排序。
例如，Sakila示例數(shù)據(jù)庫的表rental在列（retal_date, inventory_id,customer_id）上有名為rental_date的索引。
MySQL可以使用rental_date索引為下面的查詢做排序，EXPLAIN中不會(huì)出現(xiàn)文件排序（firesort）操作；
mysql> EXPLAIN SELECT rental_id, staff_id FROM rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id\G;
即使ORDER BY子句不滿足索引的最左前綴的要求，也可以用于查詢排序，這是因?yàn)樗饕牡谝涣斜恢付橐粋€(gè)常數(shù)。
mysql> ... WHERE rental_date = '2005-05-25' ORDER BY?inventory_id DESC;
第一列提供了常量條件，而使用第二列進(jìn)行排序，將兩列組合在一起，就形成了索引的最左前綴。
mysql> ... WHERE rental_date > '2005-05-25' ORDER BY rental_date,?inventory_id;
上面這個(gè)查詢也沒問題，ORDER BY使用的兩列就是索引的最左前綴。
反例，下面是一些不能使用索引做排序的查詢：
- 下面這個(gè)查詢使用了兩張不同的排序方向，但是索引列都是正序排序的：
... WHERE rental_date = '2005-05-25' ORDER BY?inventory_id DESC, customer_id ASC;
- 下面這個(gè)查詢的ORDER BY子句中引用了一個(gè)不在索引的列：
... WHERE rental_date = '2005-05-25' ORDER BY?inventory_id, staff_id;
- 下面這個(gè)查詢的WHERE和ORDER BY中的列無法組合成索引的最左前綴：
... WHERE rental_date = '2005-05-25' ORDER BY customer_id;
- 下面這個(gè)查詢在索引列的第一列上是范圍條件，所以MySQL無法使用索引的其余列：
... WHERE rental_date > '2005-05-25' ORDER BY?inventory_id, customer_id;
- 這個(gè)查詢在inventory_id列上有多個(gè)等于條件。對于排序來說，這也是一種范圍查詢：
... WHERE rental_date = '2005-05-25' AND?inventory_id IN (1, 2) ORDER BY customer_id;
8.壓縮（前綴壓縮）索引
9.冗余和重復(fù)索引
重復(fù)索引是指在相同的列上按照相同的順序創(chuàng)建的相同類型的索引。應(yīng)該避免這樣創(chuàng)建重復(fù)索引，發(fā)現(xiàn)以后也應(yīng)該立即移除。
如果創(chuàng)建了索引（A,B），再創(chuàng)建（A）就是冗余索引，因?yàn)檫@只是前一個(gè)索引的前綴索引。因此前一個(gè)也可以當(dāng)做后一個(gè)索引來使用。如果再創(chuàng)建（B,A）、（B），則不是冗余索引。
冗余索引通常發(fā)生在為表添加新索引的時(shí)候。例如，有人可能會(huì)增加一個(gè)新的索引（A,B）而不是擴(kuò)展已有的索引（A），還有一種情況是將一個(gè)索引擴(kuò)展為（A,ID），其中ID是主鍵，對于InnoDB來說主鍵列已經(jīng)包含在二級索引中了，所以這也是冗余的。
盡量擴(kuò)展已有的索引而不是創(chuàng)建新索引。但有時(shí)候處于性能方面的考慮需要冗余索引，因?yàn)閿U(kuò)展已有的索引會(huì)導(dǎo)致其變得太大，從而影響其他使用該索引的查詢的性能。例如，如果再整數(shù)列上有一個(gè)索引，現(xiàn)在需要額外增加一個(gè)很長的VARCHAR列來擴(kuò)展該索引，那性能可能會(huì)急劇下降。
10.未使用的索引
11.索引和鎖
四、索引案例學(xué)習(xí)
假設(shè)要設(shè)計(jì)一個(gè)在線約會(huì)網(wǎng)站，用戶信息表有很多列，包括國家、地區(qū)、城市、性別、眼睛顏色，等等。網(wǎng)站必須支持上面這些特征的各種組合來搜索用戶，還必須允許根據(jù)用戶的最后在線時(shí)間、其他會(huì)員對用戶的評分等對用戶進(jìn)行排序并對結(jié)果進(jìn)行限制。如何設(shè)計(jì)索引滿足上面的復(fù)雜需求呢？
1.支持多種過濾條件
將大部分查詢中都會(huì)用到的列作為組合索引的前綴，即使查詢沒有使用到該列，那么可以通過在查詢條件中新增IN()列表(多個(gè)等值條件查詢)查詢條件來讓MySQL選擇該索引。這種做法在該列的選擇性不高的時(shí)候非常有效，但如果列有太多不同的值，就會(huì)讓IN()列表太長，這樣做就不行了。
2.避免多個(gè)范圍條件
假設(shè)我們有一個(gè)last_online列并希望通過下面的查詢顯示在過去幾周上線過的用戶：
WHERE eye_color? IN ('brown', 'blue', 'hezel')
AND? ?hair_color? IN ('black', 'red', 'blonde', 'brown')
AND? ?sex? ? ? ? ? ? IN ('M', 'F')
ADN? ?last_online > DATE_SUB(NOW(), INTERVAL 7 DAY)
ADN? ?age? ? ? ? ? ? BETWEEN 18 ADN 25
這個(gè)查詢有一個(gè)問題：他有兩個(gè)范圍條件，last_online列和age列，MySQL可以使用last_online列索引或者age列索引，但無法同時(shí)使用它們。
方法一：將age字段轉(zhuǎn)換為一個(gè)IN()的列表；
方法二：實(shí)現(xiàn)計(jì)算好一個(gè)active列，由定時(shí)任務(wù)來維護(hù)，每當(dāng)用戶登錄時(shí)，將對應(yīng)值設(shè)置為1，并且將過去連續(xù)七天未曾登錄的用戶的值設(shè)置為0；
3.優(yōu)化排序
使用文件排序?qū)π?shù)據(jù)集是很快的，但如果一個(gè)查詢匹配的結(jié)果有上百萬行的話會(huì)怎么樣？例如如果WHERE子句只有sex列，如何排序；對于那些選擇性非常低的列，可以增加一些特殊的索引來做排序。例如，可以創(chuàng)建（sex，rating）索引用于下面的查詢：
mysql> SELECT <cols> FROM profiles WHERE sex = 'M' ORDER BY rating LIMIT 10;
這個(gè)查詢使用了ORDER BY和LIMIT，如果沒有索引的話會(huì)很慢。
即使有索引，如果用戶界面上需要翻頁，而且翻頁翻到比較靠后時(shí)查詢也可能非常慢。
mysql>?SELECT <cols> FROM profiles WHERE sex = 'M' ORDER BY rating LIMIT 1000000 10;
無論如何創(chuàng)建索引，這種查詢都是個(gè)嚴(yán)重的問題。因?yàn)殡S著偏移量的增加，MySQL需要花費(fèi)大量的時(shí)間來掃描需要丟棄的數(shù)據(jù)。
優(yōu)化這類索引的另一個(gè)比較好的策略是使用延遲關(guān)聯(lián)，通過使用覆蓋索引查詢返回需要的主鍵，再根據(jù)這些主鍵關(guān)聯(lián)原表獲取需要的行。這可以減少M(fèi)ySQL掃描那些需要丟棄的行數(shù)。下面這個(gè)查詢顯示了如何高效地使用（sex，rating）索引進(jìn)行排序和分頁：
mysql>?SELECT <cols> FROM profiles INNER JOIN (SELECT <primary key cols> FROM profiles WHERE x.sex = 'M' ORDER BY rating LIMIT 1000000, 10) AS x USING(<primary key cols>);
五、補(bǔ)充：
1.創(chuàng)建索引SQL：
CREATE INDEX index_name ON table_name(col[(length)]...);
ALTER TABLE table_name ADD INDEX index_name(col[(length)]...);
2.刪除索引SQL：
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;
3.走不走索引的總結(jié)（如有錯(cuò)誤，歡迎指正）：
- NOT IN，<>，LIKE '%...' 不走索引；
- =，IN(多個(gè)等值條件查詢)，LIKE '(非%開頭)...'，BETWEEN ... AND ...(范圍條件查詢)，EXISTS，NOT EXISTS走索引；
- <，<=，>，>=如果字段是整數(shù)類型會(huì)走索引；字符類型根據(jù)實(shí)際查詢速度來判斷；如果全盤掃描速度比索引速度要快則不走索引；
轉(zhuǎn)載于:https://www.cnblogs.com/NguyenVm/p/10040229.html
總結(jié)
以上是生活随笔為你收集整理的《高性能MySQL(第3版)》摘要——索引篇的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
