創(chuàng)建高性能索引

索引優(yōu)化應(yīng)該是對查詢性能優(yōu)化的最有效的手段了。索引可以輕易的將查詢性能提升幾個數(shù)量級。

索引基礎(chǔ)

SELECT first_name FROM 表 WHERE id=5;

對于上面這個查詢，如果id列上有索引。則MySQL將使用該索引找到id=5的行，也就是說，mysql現(xiàn)在索引上按值查找，然后返回所有包含該值的數(shù)據(jù)行。

索引可以包含一個或多個列的值。如果索引包含多個列，那么列的順序也十分重要，因為MySQL只能高效的使用索引的最左前綴列。

索引的類型

在MySQL中，索引是在存儲引擎層而不是服務(wù)器層實現(xiàn)的。

下面介紹mysql支持的索引類型：

B-Tree索引

當我們在談?wù)撍饕臅r候，如果沒有特別的指明類型，那么多半都是B-Tree索引，他使用B樹數(shù)據(jù)結(jié)構(gòu)來存儲數(shù)據(jù)。當然更多的使用的是B+樹的數(shù)據(jù)結(jié)構(gòu)，你比如說Innodb。

B樹索引為什么能夠加快數(shù)據(jù)的訪問速度呢，這就打到我的甜點位了，因為存儲引擎不需要進行全表掃描了呀，取而代之的是從索引的根節(jié)點開始進行搜索，并且每個子節(jié)點都有子節(jié)點頁的上限和下限，最終引擎要么就是找到，要么就是找不到。

還有一個點B+樹是所有節(jié)點信息都在葉子節(jié)點保存著呢

請注意：索引對多個值進行排序的依據(jù)是你在建表語句中定義索引時列的順序。

你比如說如下的數(shù)據(jù)表：

CREATE TABLE People(
	last_name varchar(50) not null,
	first_name varchar(50) not null,
	dob date  not null,
	gender enum('m','f') not null,
	key(last_name,frist_name,dob)
);

咱就是說，這個key，我又想提幾句

MySQL中有四種Key: Primary Key, Unique Key, Key 和 Foreign Key。

除了Foreign Key最好理解外，其他的都要區(qū)分一下。

剩下的三種都要在原表上建立索引。

Primary Key和Unique Key之間的區(qū)別網(wǎng)上說的最多。Primary Key的提出就是為了唯一標示表中的字段，就像我們的身份證號一樣。此外，所有字段都必須是not null的Unique Key則是為了保證表中有些字段是唯一的。比如有些單位領(lǐng)導(dǎo)叫“張三”，所以下面招人的時候是決不可招一個有同樣名字的。

至于Key嗎，網(wǎng)上說的比較少。其實某個字段標記為Key，是不能保證這個字段的值在表中是唯一出現(xiàn)的。它的目的就是建立索引。

之前所述的索引對下面的類型的查詢都是有效的：

• 全值匹配
  • 全值匹配指的是和索引中的所有列進行匹配，例如你要查找一個姓名，出生日期的信息，就可以完全走索引。
• 匹配最左前綴、
  • 就是查找last_name 的信息可以走索引。
• 匹配列前綴
  • ? 也可以值匹配某一列的值的開頭部分。比如說查找所有以J開頭的last_name的人，就是可以使用索引的第一列。
• 精確匹配某一列并范圍匹配另外一列
  • ? 就是這個索引我們可以第一列l(wèi)ast_name 全匹配，但是first_name進行一個范圍匹配，這樣的操作
• 只訪問索引的查詢
  • B樹通常是支持只訪問索引的查詢，即查詢只需要訪問索引，而無須訪問數(shù)據(jù)行。

因為呀，這個索引樹中的節(jié)點是有序的，所以處理按值查找外，索引還可以用于查詢中的order by 操作，一般來說，

如果B樹可以按照某種方式查找到值，那么也可以按照這種方式用于排序，所以，如果orderby 子句滿足我們剛剛上面提到的查詢類型，那么這個索引也可以滿足對應(yīng)的排序需求。

下面呢，我就要你介紹一些B樹索引的限制了：

• 如果不是按照索引的最左列開始查找，則無法使用索引。（比如姓氏以某個字母結(jié)尾的這種也是無法使用索引的）

• 不能跳過索引的列，就像上面的例子，你不能跳過first_name 去查找last_name 和dob

• 如果查詢中有某個列的范圍查詢，則其右邊所有列都無法使用索引優(yōu)化查找。

WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23'

這個查詢就只能使用索引的前兩列，因為like是一個范圍條件。

所以，如果范圍查詢列值的數(shù)量有限，那么就可以通過使用多個等于條件來代替范圍條件。

哈希索引

哈希索引基于哈希表實現(xiàn)，只有精確匹配索引所有列的查詢才有效。對于每一行數(shù)據(jù)，存儲引擎都會對所有的索引列計算一個哈希碼。哈希碼是一個比較小的值，并且不同鍵值的行計算出來的哈希碼也不一樣，哈希索引將所有的哈希碼存儲在索引中，同時哈希表中保存指向每個數(shù)據(jù)行的指針。

在MySQL中只有memory引擎顯示支持哈希索引。這也是memory引擎表的默認索引類型。

select name FROM 表  WHERE name=‘peter';

哈希索引查詢過程：

MySQL先計算’peter’的哈希值,并使用該值尋找對應(yīng)的記錄指針。f（peter）=8784 。所以mysql在索引中查找8784。就可以找到一個指向表的第三行的指針，最后一步，比較第三行的值是不是peter，以確保就是要查找的行。

因為索引只需存儲對應(yīng)的哈希值，所以索引的結(jié)構(gòu)十分緊湊，這也讓哈希索引查找的速度十分快。

但是

哈希索引，也是有他自己的限制。

• 哈希索引只包含哈希值和行指針，而不存儲字段值，所以不能使用索引中的值來避免讀取行。
• 哈希索引的數(shù)據(jù)并不是按照哦索引值順序存儲的，所以也就無法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因為哈希索引始終是使用索引列的全部內(nèi)容來計算哈希值。
• 哈希索引只支持等值比較查詢，不支持任何范圍查詢。
• 訪問哈希索引的數(shù)據(jù)非?？欤怯泻芏喙_突。當哈希沖突出現(xiàn)的時候，存儲引擎必須遍歷鏈表中的所有行指針，逐行進行比較。直到找到所有符合條件的行。
• 如果哈希沖突特別多的時候，一些索引維護的操作的代價也會特別高。

創(chuàng)建自定義哈希索引

如果存儲引擎不支持哈希索引，則可以模擬像InnoDB一樣創(chuàng)建哈希索引，這樣同樣可以使用一些哈希索引的便利。

思路：

在b樹的基礎(chǔ)上創(chuàng)建一個偽哈希索引，這和真正的哈希索引不是一回事，因為還是使用b樹進行查找，但是他使用的是哈希值不是鍵本身進行索引查找，你需要做的是在查詢的WHERE子句中手動指定使用哈希函數(shù)。

比如：

我們下面這個查詢url的語句：

select id FROM url where url='www.baidu.com';

如果我們刪除原來url列上的索引，而新增一個url_crc列，使用crc32做哈希，就如下面:

select id FROM url where url='www.baidu.com'
AND url_crc=CRC32('www.baidu.com');

你這樣做，他的性能就很高，有多高，有三四層樓那么高。

因為MySQL優(yōu)化器會使用這個選擇性很高而體積很小的基于url_crc列的索引來完成查找。即使多個記錄有相同的索引值，查找依舊很快，只需根據(jù)哈希值做快速的整數(shù)比較就能找到索引條目，然后一一比較返回的對應(yīng)行。

當然這種實現(xiàn)的缺陷是需要維護哈希值。可以手動維護，也可以使用觸發(fā)器實現(xiàn)。

如果使用這種方式，你也切記不要使用sha1（）和md5（）作為哈希函數(shù)因為這兩函數(shù)計算出來的哈希值是特別長的字符串，會浪費大量空間，比較時也會比較慢。

處理哈希沖突

就像上面那個哈希索引查詢，你是必須包含常量值的在WHERE子句中，不然哈希值沖突你就沒法搞了呀：

select id FROM url where url='www.baidu.com'
AND url_crc=CRC32('www.baidu.com');

空間索引

myisam表支持空間索引，可以用作地理數(shù)據(jù)存儲。和b樹索引不同，這類索引無須前綴查詢?？臻g索引會從所有維度來索引數(shù)據(jù)。查詢時，可以有效的使用任意維度來組合查詢。必須使用MySQL的GIS相關(guān)函數(shù)如mbrcontains（）等來維護數(shù)據(jù)。

全文索引

全文索引是一種特殊類型的索引，它查找的是文本中的關(guān)鍵詞，而不是直接比較索引中的值。全文搜索和其他幾類索引的匹配方式完全不一樣。他有許多需要注意的細節(jié)，如停用詞，詞干和復(fù)數(shù)，布爾搜索等。

其他索引類別

還有許多第三方存儲引擎使用不同類型的數(shù)據(jù)結(jié)構(gòu)來存儲索引。例如TokuDB使用分形樹索引。這是一類較新開發(fā)的數(shù)據(jù)結(jié)構(gòu)，既有b樹的很多優(yōu)點，也避免了b樹的一些缺點。

索引的優(yōu)點

索引可以讓服務(wù)器快速的定位到表的指定位置，但是這并不是索引的唯一作用。

最常見的b樹索引，按照順序存儲數(shù)據(jù)，所以mysql可以用來做order by和group by 操作。因為數(shù)據(jù)是有序的，所以b樹也會將相關(guān)的列值存儲在一起。最后 ，因為索引中存儲了實際的列值，所以某些查詢只使用索引就能完成全部查詢：

總而言之，言而總之，有著如下的優(yōu)點：

索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量索引可以幫助服務(wù)器避免排序和臨時表索引可以將隨機IO變?yōu)轫樞騃O。

思考一個問題，索引時最好的解決方案嗎？

其實不見的，對于非常小的表，大多是掃描全表是更加高效的，對于中到大型表索引就很有效，對于特大型表，建立和使用索引的代價將隨之增長，這種情況下就要使用分區(qū)技術(shù)了，這個我們后面的文章介紹。

高性能的索引策略

獨立的列

如果查詢的列不是獨立的，則MySQL就不會使用索引。獨立的列是指索引列不能是表達式的一部分，也不能是函數(shù)的參數(shù)。

如：

select actor_id FROM sakila.actor where actor_id + 1 = 5;

其實屏肉眼你是可以判斷出這個actor_id其實就是=4，但是mysql是無法自動解析這個方程式的。這完全是用戶行為，我們應(yīng)該養(yǎng)成習慣，始終將索引列單獨放在比較符號的一側(cè)

前綴索引和索引的選擇性

有時啊，需要的索引很長的字符列，這樣呢會使索引變得大且慢。一個策略是前面提到過的模擬哈希索引。再者呢？

我們可以索引開始的部分字符，這樣可以大大節(jié)約索引空間，從而提高索引效率。

但是

這樣會降低索引的選擇性：

索引的選擇性=不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值。

索引的選擇性越高則查詢的效率越高，因為選擇性高的會在查找時過濾掉更多的行。

當然對于blob，text，varchar這種很長類型的列，必須使用前綴索引，mysql不允許索引這些列的完整長度。

多列索引

很多人對多列索引的理解不夠。一個常見的錯誤就是，為每個列都創(chuàng)建獨立的索引，或者按照錯誤錯誤的順序創(chuàng)建多列索引。

你比如說下面這個例子：

CREATE TABLE t(
c1 int,
c2 int,
c3 int,
key(c1),
key(c2),
key(c3)
);

像這樣在多個列上建立獨立的單列索引大部分情況下并不能提高MySQL的查詢性能。

但是

MySQL5.0和更新的版本引入了一個叫“索引合并”的策略，一定程度上是可以使用表上的多個單列索引來定位指定的行。在此之前的版本的MySQL只能使用其中某個單列索引。

你再比如說下面這個例子：

select film_id,actor_id from biao
where actor_id =1 OR film_id = 1;

在老版本的mysql中，你像上面這樣where里的OR語句的情況是不能使用兩個單列索引進行掃描的，只會進行全表掃描。除非你把or改成UNION的形式。

索引合并策略確確實實的解決了上面我們所描述的那種情況。但實際上更多時候還是說明呀，表上的索引建的hin糟糕：

• 當出現(xiàn)服務(wù)器對多個索引做相交操作時（通常有多個and條件）。通常其實意味著是需要一個包含所有相關(guān)列的多列索引，而不是多個獨立的單列索引。
• 當服務(wù)器需要對多個索引做聯(lián)合操作時（通常有多個or操作），通常需要耗費大量的CPU和內(nèi)存資源在算法的緩存，排序和合并操作上。特別是有些索引的選擇性不高，需要合并掃描返回的大量數(shù)據(jù)的時候
• 更重要的是，優(yōu)化器并不會把這些計算到“查詢成本”中，優(yōu)化器只關(guān)心隨機頁面讀取。這會使得查詢的成本被低估，導(dǎo)致該執(zhí)行計劃還不如直接走全表掃描。這樣子不但會消耗更多的CPU和內(nèi)存資源，還可能會影響到查詢的并發(fā)性。

所以：如果在explain中看到有索引合并，應(yīng)該好好去檢查一下查詢的表和結(jié)構(gòu)，看看是不是已經(jīng)最優(yōu)了。你可以通過參數(shù)optimizer switch 來關(guān)閉索引合并功能。也可以使用ignore index 提示優(yōu)化器忽略掉某些索引。

選擇合適的索引順序

我們遇到的最容易引起困惑的問題就是索引列的順序。這個順序的正確性其實是依賴使用該索引的查詢是怎么樣的。并且我們還需要考慮如何更好的滿足排序和分組的需要。（順帶說一嘴，本節(jié)內(nèi)容適用于b樹索引）

我們之前也有介紹，在一個多列b樹索引中，索引列的順序意味著索引首先按照最左列進行排序，其次是第二列，索引可以按照升序或者是降序進行掃描，以滿足查詢列里的orderby groupby 和distinct等子句的查詢需求。

多列索引的列順序是至關(guān)重要的。

那么，對于如何選擇索引的列順序呢？有這樣一個經(jīng)驗法則：將選擇性最高的列放到索引的最前列。當然了場景不同，選擇不同，沒有那種放任四海皆準的法則。

當不需要考慮排序和分組時，將選擇性最高的列放在前面通常是很好的。這時候索引的作用就是用于優(yōu)化where條件的查找。在這種情況下，這樣設(shè)計的索引確實能夠最快的過濾出需要的行，對于在where子句中只使用索引部分前綴列的查詢來說選擇性也更高。當然為了性能你還得考慮哪些列是運行頻率比較高的，來調(diào)整索引列的順序,讓這種情況下的索引的選擇性最高。

你比如說下面這個例子：

SELECT * FROM 表 
WHERE
staff_id = 2 AND customer_id = 584

那么你就要考慮是創(chuàng)建一個（staff_id,customer_id）索引，還是兩者調(diào)換一下順序呢？

這個我們就得去查詢一下表中值的分布情況，來確定哪個列的選擇性更高。

我們一查就發(fā)現(xiàn)：

staff_id有7992個數(shù)據(jù)，staff_id只有30個數(shù)據(jù)。

那么根據(jù)我們的經(jīng)驗法則，我們應(yīng)該將索引列customer_id放到前面，因為對應(yīng)條件值的customer_id數(shù)量更少。

這樣做其實你還要注意一個地方，因為這個查詢結(jié)果是十分依賴選定的具體值。如果按照上面的查詢結(jié)果進行優(yōu)化，可能對其他的值不公平，進而導(dǎo)致服務(wù)器整體的性能可能更糟。

但是一般情況下我們就用下面介紹的經(jīng)驗法則就可以知道那個字段選擇性更高：

SELECT COUNT(DISTINCT staff_id)/count(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM 表;

這樣我們可以看出來customer_id的選擇性更高， 所以就將它作為索引的第一列

ALTER TABLE 表  payment ADD KEY (customer_id,staff_id);

聚簇索引

聚簇索引并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式。具體的細節(jié)依賴于其實現(xiàn)方式，比如說innodb的聚簇索引實際上在同一個結(jié)構(gòu)中保存了b樹索引和數(shù)據(jù)行。

當表中有聚簇索引時，他的數(shù)據(jù)行實際上存放在索引的葉子頁中。

如果沒有定義主鍵，innodb會選擇一個唯一的非空索引代替。如果沒有這樣的索引，innodb會隱式定義一個主鍵來作為聚簇索引。innodb只聚集在同一個頁面中的記錄。包含相鄰鍵值的頁面可能會相距甚遠。

但是聚簇主鍵可能對性能有幫助，也可能導(dǎo)致嚴重的性能問題

聚集的數(shù)據(jù)有一些重要的優(yōu)點：

• 可以把相關(guān)數(shù)據(jù)保存在一起。比如存儲電子郵箱，可以根據(jù)用戶ID來聚集數(shù)據(jù)，這樣只需要從磁盤讀取少數(shù)的數(shù)據(jù)頁就能獲取某個用戶的全部郵件。如果沒有使用聚簇索引，則每封郵件都可能導(dǎo)致一次磁盤IO。
• 數(shù)據(jù)訪問更快。聚簇索引將索引和數(shù)據(jù)保存在同一個b樹中，因此從聚簇索引中獲取數(shù)據(jù)你通常要比在非聚簇索引中查找要快
• 使用覆蓋索引掃描的查詢可以直接使用葉節(jié)點中的主鍵值。

聚簇索引的一些缺點：

• 聚簇數(shù)據(jù)最大限度地提高了IO密集型應(yīng)用的性能，但是如果數(shù)據(jù)全部都放在內(nèi)存中，則訪問的數(shù)據(jù)就沒那么重要了，聚簇索引也就沒什么優(yōu)勢了。
• 插入速度嚴重依賴與插入順序。按照主鍵的順序插入是加載數(shù)據(jù)到innodb表中速度最快的方式。但是如果不是按照主鍵順序加載數(shù)據(jù)，那么在加載完成后最好使用optimize TABLE命令重新組織一下表。
• 更新聚簇索引列的代價很大，因為innodb會強制將每個被更新的行移動到新的位置。
• 基于聚簇索引的表在插入新行，或者主鍵被更新導(dǎo)致需要移動行的時候，可能面臨“葉分裂”的問題。當行的主鍵值要求必須將這一行插入到某個已滿的頁中時，存儲引擎會將該頁分裂成兩個
• 頁面來容納該行，這就是一次頁分裂操作。頁分裂會導(dǎo)致表占用更多的磁盤空間。
• 聚簇索引可能導(dǎo)致全表掃描變慢，尤其是行比較稀疏，或者頁分裂導(dǎo)致數(shù)據(jù)存儲不連續(xù)的時候。
• 二級索引（非聚簇索引）可能比想象的要更大，因為在二級索引的葉子節(jié)點包含了引用行的主鍵列。

最后一點，做出一個解釋，為什么二級索引需要兩次索引查找？因為二級索引葉子節(jié)點保存的不是指向行的物理位置的指針，而是行的主鍵值。這就意味著通過二級索引查找行，存儲引擎需要找到二級索引的葉子節(jié)點獲得對應(yīng)的主鍵值。然后根據(jù)這個值去聚簇索引中查找到對應(yīng)的行。

覆蓋索引

通常啊，我們大家都會根據(jù)查詢的where條件來創(chuàng)建合適的索引，不過這只是索引優(yōu)化的一個方面。設(shè)計優(yōu)秀的索引是應(yīng)該考慮到整個查詢，而不是單單where條件部分。索引確實是一種查找數(shù)據(jù)的高效方式，但是mysql也可以使用索引來直接獲取列數(shù)據(jù)，這樣就不需要再讀取數(shù)據(jù)行。

咱就是說，如果索引的葉子節(jié)點中已經(jīng)包含要查詢的數(shù)據(jù)，那么還有什么必要回表查詢呢？

如果一個索引包含所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。

如果咱們查詢只是需要掃描索引而無須回表，是會帶來很多好處的：

• 索引條目通常遠小于數(shù)據(jù)行大小，所以如果只需要讀取索引，那么MySQL就會極大的減少數(shù)據(jù)訪問量。這對緩存的負載非常重要，因為這種情況下響應(yīng)時間大部分花費在數(shù)據(jù)拷貝上了。覆蓋索引對IO密集型的應(yīng)用也有幫助，因為索引比數(shù)據(jù)更小，更容易全部放入內(nèi)存。
• 因為索引是按照列值順序存儲的，所以對于IO密集型的范圍查詢會比隨機從磁盤讀取每一行數(shù)據(jù)的IO要少的多。
• 由于innodb使用聚簇索引，所以覆蓋索引對innodb是特別有用的，innodb的二級索引在葉子節(jié)點中保存了行的主鍵值，所以如果二級主鍵能夠覆蓋查詢，則可以避免對主鍵索引的二次查詢。這個我們之前也有提到過。

當然，我們要清楚一點，不是所有類型的索引都可以成為覆蓋索引。覆蓋索引必須要存儲索引列的值，而哈希索引，空間索引和全文索引等都不存儲索引列的值，所以mysql只能使用b樹索引做覆蓋索引。

但是

索引覆蓋查詢還是有很多陷阱可能導(dǎo)致無法實現(xiàn)優(yōu)化，MySQL查詢優(yōu)化器會在執(zhí)行查詢前判斷是否有一個索引能進行覆蓋，假設(shè)索引覆蓋了where條件中的字段，但不是整個查詢涉及的字段。MySQL5.5或者更早的版本還是會回表獲取數(shù)據(jù)行，盡管這一行可能最終還是會被過濾掉。

我們舉個栗子：

SELECT * FROM products where actor = 'SEAM CARREY'
AND title like '%APOLLO%';

像上面這個查詢索引是無法覆蓋的，有兩個原因：

• 沒有任何索引能夠覆蓋這個查詢。因為查詢的結(jié)果字段是包括了所有的列，沒有任何一個索引是可以覆蓋所有的列的。當然，理論上MySQL還是有一個捷徑可以利用，就是where語句中的條件是有索引覆蓋的。
• MySQL不能在索引中執(zhí)行l(wèi)ike操作。這是底層存儲引擎API的限制。MySQL5.5和更早的版本只支持在索引中做簡單比較操作（例如等于，不等于，大于）。mysql能在索引中做最左前綴匹配的like比較，因為該操作可以轉(zhuǎn)換為簡單的比較操作，但是如果是通配符開頭的like查詢，存儲引擎就無法做比較匹配。

也有辦法是可以解決我上面說的這兩個問題的，那就是需要重寫查詢并巧妙地設(shè)計索引。首先將索引擴展到覆蓋是三個數(shù)據(jù)列（actor，title，prod_id），然后按照如下的方式重寫查詢。

select * 
from products
join (
	select prod_id
	from products
	where actor = 'SEAM CARREY' AND title like '%APOLLO%';
) AS t1 ON (t1.prod_id=products.prod_id);

我們把這種方式叫做延遲關(guān)聯(lián)，因為延遲了對列的訪問。在查詢的第一階段MySQL可以使用覆蓋索引，在from子句的子查詢中找到匹配的prod_id,然后根據(jù)這些prod_id 值在外層查詢匹配獲取需要的所有列值，雖然無法使用索引覆蓋整個查詢，但總算比完全無法利用索引覆蓋的好。

延時關(guān)聯(lián)，即通過使用覆蓋索引查詢返回需要的主鍵,再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù)，尤其在大分頁查詢的場景下，可以提高查詢效率。

使用索引掃描來做排序

MySQL有兩種方式可以生成有序的結(jié)果：通過排序操作；或者按照索引順序掃描；如果explain出來的type列的值是index，則說明mysql使用了索引掃描來做排序（不要和extra列的using index高混淆了）

掃描索引本身是很快的，因為只需要從一條索引記錄移動到緊挨著的下一條記錄。但是如果索引不能覆蓋查詢所需的全部列，那就不得不每掃描一條索引記錄就都回表查詢一次對應(yīng)的行。這基本上都是隨機IO，因此按索引順序讀取數(shù)據(jù)的速度通常要比順序的全表掃描慢，尤其是在IO密集型的工作負載時。

只有當索引的列順序和order by子句順序完全一致，并且所有列的排序方向都一樣時，MySQL才能用索引來對結(jié)果做排序。如果查詢需要關(guān)聯(lián)多張表，則只有當order by子句引用的字段全為第一個表時，才能使用索引做排序。order by子句和查找型查詢的限制是一樣的：需要滿足索引的最左前綴的要求；否則MySQL都需要執(zhí)行排序操作，而無法利用索引排序。

當然有一種情況下order by子句可以不滿足索引的最左前綴的要求，就是前導(dǎo)列為常量的時候。如果where子句或者join子句中對這些列指定了常量，就可以彌補索引的不足。

比如說我們舉一個例子：

對于下面這樣一個表結(jié)構(gòu)：

create table 表名（
。。。。
key lizi （date，id1，id2），
。。。。
）

然后對于下面這個查詢語句：

select 內(nèi)容
from  表
where date  = 11
order by id1,id2;

這種查詢，MySQL就可以使用lizi這個索引為查詢排序。當然，我們可以看到即使order by這個子句是不滿足最左前綴的要求的，但是沒關(guān)系呀，索引的第一列date的被指定為了一個常數(shù)呀。

你再比如像下面這樣：

where date = 常數(shù)
order by id1;

你像上面這個還是可以使用索引排序的，第一列常數(shù)，然后再索引的最左前綴嘛。

你再在比如說下面這個：

where date >日期 order by  date，id1;

這個就也能用索引排序，order by使用的兩列就是索引的最左前綴。

都講了這么老半天的能用索引排序的，那么下面我就來說說這個不能使用索引排序的查詢。

• 使用兩種不同的排序方向，即使索引列都是正序排序的。

where date = 常數(shù) order by id1 desc ，id2  asc;

• order by 子句中引用了一個不在索引中的列

where  date  = 常數(shù) order by id1 ，id3;

• where和order by 中的列無法組合成索引的最左前綴

where date = 常數(shù) order by id2;

• 查詢的第一列不是常數(shù)，而是范圍，就也不能用最左前綴了。

where date > 常數(shù) order by id1，id2;

• id1這一列有多個等于條件，對于排序也是范圍查詢，不能使用索引排序

where date = 常數(shù) AND id1 IN （1,2）order by id2;

壓縮（前綴壓縮）索引

myisam使用前綴壓縮來減少索引的大小，從而讓更多的索引可以放入內(nèi)存中，這在某些情況下能極大的提高性能。默認只壓縮字符串，但通過參數(shù)設(shè)置也可以對整數(shù)做壓縮。

myisam壓縮每個索引塊的方法是，先完全保存索引塊中的第一個值，然后將其他值和第一個值進行比較得到相同前綴的字節(jié)數(shù)和剩余的不同后綴部分，把這部分存儲起來即可。

就是這樣，你比如說：索引塊的第一個值是“perform”，第二個值是“performance”，那么第二個值的前綴壓縮后存儲的類似“7，ance”這樣的形式。myisam對行指針也采用類似的前綴壓縮方式。

壓縮塊使用更少的空間，代價是某些操作可能更慢。這你也是可以想象的嘛，因為前綴壓縮了，你必然是不能二分查找，只能順序查找 了，對于order by desc 這種倒序的，那就更難受了。

我們可以在create table 語句中指定pack_keys參數(shù)來控制索引壓縮的方式。

冗余和重復(fù)索引

MySQL是允許在相同列上創(chuàng)建多個索引，無論你創(chuàng)建的時候是有意的還是無意的。MySQL需要單獨維護重復(fù)的索引，并且優(yōu)化器在優(yōu)化查詢的時候也需要進行逐個考慮，這回影響性能。

重復(fù)索引，啥叫重復(fù)索引，就是指在相同的列上按照相同的順序創(chuàng)建的相同類型的索引。應(yīng)該避免這樣的創(chuàng)建，發(fā)現(xiàn)之后也應(yīng)該立即移除。

你比如說下面這樣的例子：

create table test（
		ID int NOT null primary key,
		......
		unique(ID),
		INDEX(ID)
）

這上面，你創(chuàng)建了一個主鍵，先加上唯一限制，然后再加上索引以供查詢使用。事實上呀，MySQL的唯一限制和主鍵限制都是通過索引實現(xiàn)的 ，因此呀，上面這個寫法其實就是在相同的列上 創(chuàng)建了三個重復(fù)的索引。通常情況下是沒有必要這樣做的，除非呀是在同一列上創(chuàng)建不同類型的索引來滿足不同的查詢需求。

冗余索引和重復(fù)索引其實又有一些不同。如果創(chuàng)建了索引（A,B），再創(chuàng)建索引（A）那就是冗余索引了，因為這只是前一個索引的前綴索引。但是你要是創(chuàng)建的是（B,A）那可就不是冗余索引了。當然不同的索引類型肯定也不會涉及到冗余索引的事情。

還有一種情況，將索引（A）擴展為了索引（A，ID），其中ID是主鍵，這在innodb中來說，主鍵列已經(jīng)包含到二級索引中去了，所以這也是冗余索引。

大多數(shù)情況下，我們不應(yīng)該是創(chuàng)建新索引，而是擴展已有的索引。但你話也不能說死，有時候出于性能的考慮，也會考慮冗余索引，因為你擴展索引會導(dǎo)致其變得太大，從而影響其他使用該索引的查詢的性能。

未使用的索引

除了冗余索引，和重復(fù)索引，還有些索引呀，服務(wù)器可能這輩子都用不到，這種也要考慮刪除掉呀。

索引和鎖

索引可以讓查詢鎖定更少的行。

總結(jié)

在選擇索引和編寫利用索引的查詢時：有三個原則需要始終牢記。

• 單行訪問是很慢的
• 按順序訪問范圍數(shù)據(jù)是很快的
• 索引覆蓋查詢是很快的。
  都是通過索引實現(xiàn)的 ，因此呀，上面這個寫法其實就是在相同的列上 創(chuàng)建了三個重復(fù)的索引。通常情況下是沒有必要這樣做的，除非呀是在同一列上創(chuàng)建不同類型的索引來滿足不同的查詢需求。

到此這篇關(guān)于Mysql中創(chuàng)建高性能索引詳解的文章就介紹到這了,更多相關(guān)Mysql高性能索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論