數據庫index

Indexes(索引):

    索引可以使mysql快速的找到和檢索一張包含百萬甚至億萬數據的表中的一組紀錄。如果你曾使用過mysql，不管是多久，你可能為了更輕快的得到查詢的結果而使用過索引。你也可能發現mysql的索引有時候并不想你想像的那樣工作。

    對很多使用者來說，索引就像黑色的藝術。有時候奇跡快速地工作，有時候卻像是緩慢或者阻礙數據插入。

    在本章中，我們將介紹一些索引的概念和mysql提供的各種不同索引。

Indexing Basics(索引基礎):

    理解mysql如何使用索引，最好首先理解索引的基礎工作和特征。一旦你對索引的特征有基本的理解，你就能夠更加合理的正確使用它們了。

Index Concepts(索引的概念):

    要理解索引讓mysql做什么，思考mysql是如何工作才得到查詢的結果。想象電話本是一個含有California州大約35000000條電話的電話本集合。無序地記錄在大腦里，考慮這樣查詢：

select * from phone_book where last_name = 'Zawodny';

    沒有任何類型的索引來咨詢，數據庫必須讀在phone_book這張表里的所有記錄，然后比較last_name這個字段是否與字符串"Zawodny"匹配上。當然了，這種方法是低效的。一旦電話的記錄增加了，就會需要去找對應給予的記錄。在計算機科學中，我們稱之為O(n)時間復雜度問題。

    然而給定的真實電話本，我們都知道如何快速定位到名字是Zawodny的地方：翻到書的后面，以Z開頭的地方。因為第二個單詞是a，我們知道所有匹配的會在名單列表以Z開頭的未知附近。這個方法是基于數據排序的知識的。

    這是作弊，不是嗎？并不是。你可以快速找到Zawodnys的原因是它們的姓被按字母排序了。當然了，如此簡單的找到Zawodny是因為你知道ABC字母。。。

    多數教科書（比如這本）都會在數的背面提供索引。因為這些索引按順序排列在相應的頁碼，使你經常快速的找到書中的術語和概念。想要知道數據庫熱拷貝在哪里有討論嗎？查看索引頁久知道了。

    數據庫索引也是類似的。書的作者或出版者將會在書中選擇重要的概念和術語作為索引。你可以在數據庫的表中選擇特定的字段創建索引。用前面的例子，你會創建姓為索引來快速查找電話號碼：

ALTER TABLE phone_book ADD INDEX (last_name)

    在這樣做的過程中，你會在phone_book表中向數據庫要一個按姓排列的順序列表。每個名字都有自己匹配記錄的位置－－就像這本書后面索引中列的各個條目的頁碼。

    從數據庫服務器的角度來看，當在執行一個查詢時索引的存在可以從固定的結果中快速的刪除可能的行。在沒有任何索引時，MYSQL(比如一個數據庫服務器)會檢查表中的每一行數據。這不僅僅是時間上的浪費，也會占用大量磁盤輸入輸出從而嚴重弄臟磁盤緩存。

    在真實世界中，很少能找到剛剛被排序和已經排序的動態的數據。書是一種特例，它們趨向于保持靜止。

    因為數據庫需要為索引值維護一個單獨的列表，并在數據更新同時使它們保持更新。你千萬不能希望一個表中的所有字段都作為索引。索引是一種空間和時間的折中。在做每個insert，update，delete查詢時使你的大部分（不是全部）查詢更快時，你將在磁盤空間和CPU上犧牲一些額外的空間。

    大部分數據庫的說明書使用了術語索引和鍵可互換。說last_name是phone_book表中的一個鍵等同于last_name字段是phone_book表的索引。

Partial indexes(部分索引):

    索引是空間和性能的交換。但是有時候你并不希望用空間來交換性能。幸虧，MYSQL為你提供了很多關于通過索引控制空間的方法。當你有一張phone_book表，里面有二十億數據。在last_name上增加一個索引將需要很多空間，如果每個last_name占8位，你會發現這個數據的索引大約需要16G的空間。無論你要做什么，行指針都要對每條記錄增加額外的4-8位。

    你可以只將前4位作為索引，而不是將整個last name作為索引。

ALERT TABLE phone_book ADD INDEX (last_name(4))

    當你這樣做的時候，已經減少了索引所需要部分數據約一半的空間。這個折中的是MYSQL不能消除太多行使用這個索引。比如像下面這樣的查詢：

SELECT * FROM phone_book WHERE last_name = 'Smith';

    取出所有以Smit開頭的字段，包括所有名字是Smith，Smitty等人。這個查詢會在之后丟棄Smitty等不想關的行。

Multicolumn indexes(多列):

    像很多關系數據庫引擎一樣，MySQL允許你創建的索引由多列組成。

ALERT TABLE phone_book ADD INDEX (last_name, first_name)

    如果你經常在where條件中用簡短或單一沒有足夠的種類的列但查詢結果是所有列，這種索引可以提高查詢速度。當然你也可以使用部分索引減少占用空間：

ALERT TABLE phone_table ADD INDEX (last_name(4), first_name(4))

    在任一情況下，查找Josh Woodward的快速執行：

select * from phone_book where last_name = 'Woodward' and first_name = 'Josh'

    擁有last_name和first_name兩個索引意味著MYSQL可以基于兩個字段消除行。于是更大程度上減少需要考慮的行。畢竟，在電話本上姓氏以Wood開頭的人遠遠多于姓氏以Wood開頭并且名以Josh開頭的人。

    在討論多列索引時，你可能會看到單獨的索引列被稱為鍵部分或“部分鍵”。多列索引也被稱為綜合索引或混合索引。

    那么為什么不直接創建兩個索引，一個建在last_name上，另一個建在first_name上？你也可以那么做，但是MYSQL不會同時使用它們。事實上，MYSQL在每次查詢的時候只會在每張表中使用一個索引－－除了UNIONs之外[3].這個事實足以說明MySQL對于每張表的每次查詢將永遠只使用一個索引。

    對于拆分索引上的first_name和last_name，MySQL會選擇其中一個。這個選擇是通過一個很有根據的猜想得到結果的，即判斷哪個索引匹配到的行比較少。我們之所以說這是一個很有根據的猜想，是因為MySQL會對這個索引的蹤跡進行統計來支撐他覺得哪個索引更好的推斷。當然，這個統計是概括性的。雖然他們經常讓MySQL做出明智的決定，但是如果你有非常團結的數據，MySQL可能會做出關于索引使用的次優選擇。在某些地方如果索引的鍵是稀疏的（比如以X開頭的名字）且其他地方是高度集中的（比如名字是Smith的以英語為母語的國家），我們把這種數據稱為次優數據。這是一個重要的點，我們在書的稍后內容中將會提到。

Index order(索引的順序):

    MySQL是如何在索引中對值排序的？如果你使用過其他RDBMS，你可能希望MySQL對索引有按升序、降序或其他順序排序的指定句法。MySQL提供了一個內部沒有控制排序方法的索引值。這是有原因的，在4.0版本中，這個特性很好的優化了導致其他數據庫性能降低的問題。

    例如，有些數據庫可能會執行這個快速查詢：

SELECT * FROM phone_book WHERE last_name = 'Zawodny' ORDER BY first_name DESC

    然后這個查詢變慢：

SELECT * FROM phone_book WHERE last_name = 'Zawodny' ORDER BY first_name ASC

    為什呢？因為有些數據庫按降序排序存儲索引，并對按這個順序讀取索引進行了優化。在第一個例子中，數據庫使用了多列索引定位到所有匹配的記錄，因為這些記錄已經按降序被存儲了，已經沒有必要再對他們進行排序了。但是在第二個例子中，服務器找到所有匹配的記錄，然后對這些行執行第二遍，以對它們進行排序。

    在必要時，MySQL能夠向后遍歷索引。這也能使查詢變得非常快，他能實現在任何情況下都不需要對記錄進行排序。

Indexes as constraints(索引作為約束):

    索引并不全是用于查詢定位匹配行的。唯一索引指定特定值只能在給定列中出現一次。在電話本的例子中，你可以對電話號碼創建一個唯一索引，以確保每個電話號碼只出現一次。

ALERT TABLE phone_book ADD UNIQUE (phone_number)

    唯一索引具有雙重目的，當你根據一個電話號查詢的時候他的功能和其他索引一樣。

SELECT * FROM phone_book WHERE phone_number = '555-7271'

    不過，他在插入和更新每條記錄的同時還能檢查以確保這條值是否已經存在。唯一索引以這種方式進行約束。

    唯一索引和非唯一索引使用的空間一樣多。記錄每個字段的值和位置。如果使用唯一索引作為約束而從不作為索引那就是一種浪費。換句話說，你可以依靠唯一索引強制執行唯一性，但是不要寫使用唯一鍵的查詢。在這個例子中，MySQL沒有必要將每個記錄的位置存儲在索引中：因為你永遠不會使用他們。

    不幸的，沒有辦法向MySQL表示你的意圖。未來，我們將特別介紹這個具體的例子。MyISAM存儲引擎已經支持了不帶索引的唯一字段(它使用基于散列的系統)，但是該機制尚未在SQL級別公開。

Clustered and secondary indexes(集群和二級索引):

    使用MyISAM表，索引保持在一個完全隔離的文件中，包含了主鍵（也可能有二級鍵）列表和表示記錄的字節偏移量的值。這些確保了MySQL可以找到并且快速跳到在數據庫中的那個點來查找記錄。MySQL必須用這種方法存儲索引，因為記錄基本上是以亂序進行存儲的。

    集群索引，主鍵和記錄本身聚集在一起，并且記錄都以主鍵順序存儲。InnoDB使用聚集的索引。在Oracle世界，聚集的索引被稱為索引組織表。它將幫助你記錄主鍵和行順序的關系。

    當你的數據幾乎都通過他的主鍵查找時，聚集的索引可以使查找快得難以置信。使用標準MyISAM索引，有兩個查找，一個到索引，另一個到表自身，通過索引指定得位置。使用聚集的索引，有一個直接指向相關記錄的查找。

    有些操作使用集群索引是低效的。例如，當使用一個二級索引時，回到電話本的例子，當你需要將last_name作為主索引，phone_number作為二級索引，你做以下查詢：

SELECT * FROM phone_book WHERE phone_number = '555-7271'

    MySQL遍歷了phone_number索引去查找555-7271項，包含了主鍵項Zawodny，因為電話本的主鍵是last name，MySQL會在數據庫自身中跳到相關項。

    換句話說，基于主鍵的查詢將會非常快，基于二級索引的查找速度基本上和MyISAM索引是一致的。

    但是在正確（或者說錯誤）的情況下，聚集索引事實上可能會降低性能。當你和二級索引一起使用時，你必須考慮這種結合對存儲的影響。二級索引指向主鍵而不是特定的行。于是，如果你在一個很大的值上設索引，并且同時有一些二級索引，你將最終獲得很多主索引的許多重復副本，首先作為與記錄一起存儲的聚集索引，然后再次以相同的次數與次級索引指向這些聚集索引。使用小的值作為主鍵，可能就不會這么差了，但是如果你使用某些個別數據將特別長的東西作為索引，例如URL，主鍵在磁盤上的重復存儲將會導致存儲問題。

    另一個不常見的但是同樣會發生問題的條件是：當數據在主鍵插入時主鍵在記錄中被修改。這是聚集索引最昂貴的功能。一些事情的發生可以使這樣的操作有更多的性能沖擊。

根據查詢的結果在問題中插入記錄

基于插入數據的記錄，為記錄決定新的主鍵

搬遷存儲的記錄一次這個問題中的記錄將在表空間中被移動到正確的位置

更新指向主鍵的所有二級索引

    你可能在想，如果你為一些記錄插入了主鍵，在執行UPDATE命令時將會花費相當一部分時間，特別是在更大的表中。更明智地選擇主鍵。盡量使用幾乎不會改變的值，例如使用社會安全賬號而不是姓，使用序列號而不是產品名等等。

Unique indexes versus primary keys(唯一索引與主鍵):

    如果你用過其他關系型數據庫，你可能會好奇在MySQL中主鍵和唯一索引之間有什么不同。通常這取決于，在MyISAM 表這并沒有什么區別。主鍵中唯一不同的是它的值不能是NULL的。主鍵只是一個不為空的唯一索引（NOT NULL UNIQUE INDEX）命名為鍵（PRIMARY）。MyISAM表不要求你聲明一個主鍵。

    InnoDB和BDB要求每個表都有主鍵，不過沒有要求你指定其中某一個。如果你不指定，存儲引擎將自動為你創建一個隱藏的主鍵。在這兩種例子下，主鍵只是簡單的增加數值，類似于一個自動增加列。如果你稍后要加一個主鍵，只需要使用ALTER TABLE添加一個。兩個存儲引擎將會丟棄它們自己內部的密鑰去使用你的密鑰。堆表不要求一個主鍵但是也會為你創建一個。事實上你可以創建一個無鎖的堆表。

Indexing NULLs:

    SQL在執行邏輯操作時使用三態邏輯是很難記住的。除非一個字段聲明了NOT NUL，有三種可能的結果在邏輯比較中。這個比較可能由于它們的值是等價的所以結果是對的；也有可能由于它們的值是不等價的所以結果是錯誤的；又或者它會因為值是NULL而不會匹配。無論何時比較的一個值是NULL那么結果也是NULL。

    程序員經常認為NULL就相當于沒有被定義或未知的。這是一種告訴數據庫服務器“一個未知的數據來了”的方式。所以NULL值的數據怎么能影響索引呢？

    NULL值將會使用常規索引（即非唯一索引），所有數據庫服務器都是這樣。然而，不像狠多數據庫服務器，MySQL允許你在唯一索引上使用NULL值[6]。你可以在唯一索引上存儲你想存儲盡可能多的NULL值。這看起來感覺有點違反直覺，但這就是NULL的本質。因為NULL代表了一個沒有被定義的值。如果它在唯一索引只允許單個值，MySQL需要宣稱所有的NULL值都是一樣的。

    去做一些更有意思的事，一個NULL值在主鍵中可能值出現一次。為什么呢？因為SQL標準主宰了這一行為。這是在MySQL少數方法中唯一不同于唯一鍵的主鍵。而且如果你想知道，在索引中允許NULL值并不會影響性能。

[1]這個有點偏差。數據庫不只是存儲匹配記錄的位置，我們很快久知道為什么。

[2]這也是一種過度簡化，MySQL使用一些策略來減少索引的大小，同時付出一些代價。

[3]在UNION中，每個邏輯是單獨運行然后進行合并的。

[4]除了NULL，當然了，NULL總是特殊的情況。

[5]然而在現實世界中，這或許是一個很差的例子，任何人都可以和幾個室友共用一個電話和你打電話。

[6]MySQL版本3.23及以前的版本不支持這個功能，4.0及以后的版本支持。

原文來自：https://www.safaribooksonline.com/library/view/high-performance-mysql/0596003064/ch04.html#ftn.hpmysql-CHP-4-FNOTE-4

另外的問題：

1.多列索引如：ALERT TABLE phone_table ADD INDEX (last_name(4), first_name(4))，下面各種情況都能夠利用這個多列索引進行快速查找嗎

(a)select * from phone_book where last_name = 'Woodward' and first_name = 'Josh'；

此時這個查詢可以利用多列索引進行快速查找。

(b)select * from phone_book where last_name = 'Woodward'；

此時這個查詢可以利用給出的多列索引進行快速查找

(c)select * from phone_book where first_name = 'Josh'；

此時該查詢不能利用給出的多列索引進行快速查詢，因為這個多列索引是根據last_name找到它對應的first_name的。

2.數據庫的索引是怎么工作的。。。這個問題就比較復雜啦，目前只了解了個大概，我需要時間研究一下