數(shù)據(jù)庫index

Indexes(索引):

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對這個(gè)行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長期合作伙伴，公司提供的服務(wù)項(xiàng)目有：國際域名空間、雅安服務(wù)器托管、營銷軟件、網(wǎng)站建設(shè)、開化網(wǎng)站維護(hù)、網(wǎng)站推廣。

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

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

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

Indexing Basics(索引基礎(chǔ)):

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

Index Concepts(索引的概念):

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

select * from phone_book where last_name = 'Zawodny';

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

然而給定的真實(shí)電話本，我們都知道如何快速定位到名字是Zawodny的地方：翻到書的后面，以Z開頭的地方。因?yàn)榈诙€(gè)單詞是a，我們知道所有匹配的會(huì)在名單列表以Z開頭的未知附近。這個(gè)方法是基于數(shù)據(jù)排序的知識(shí)的。

這是作弊，不是嗎？并不是。你可以快速找到Zawodnys的原因是它們的姓被按字母排序了。當(dāng)然了，如此簡單的找到Zawodny是因?yàn)槟阒繟BC字母。。。

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

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

ALTER TABLE phone_book ADD INDEX (last_name)

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

從數(shù)據(jù)庫服務(wù)器的角度來看，當(dāng)在執(zhí)行一個(gè)查詢時(shí)索引的存在可以從固定的結(jié)果中快速的刪除可能的行。在沒有任何索引時(shí)，MYSQL(比如一個(gè)數(shù)據(jù)庫服務(wù)器)會(huì)檢查表中的每一行數(shù)據(jù)。這不僅僅是時(shí)間上的浪費(fèi)，也會(huì)占用大量磁盤輸入輸出從而嚴(yán)重弄臟磁盤緩存。

在真實(shí)世界中，很少能找到剛剛被排序和已經(jīng)排序的動(dòng)態(tài)的數(shù)據(jù)。書是一種特例，它們趨向于保持靜止。

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

大部分?jǐn)?shù)據(jù)庫的說明書使用了術(shù)語索引和鍵可互換。說last_name是phone_book表中的一個(gè)鍵等同于last_name字段是phone_book表的索引。

Partial indexes(部分索引):

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

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

ALERT TABLE phone_book ADD INDEX (last_name(4))

當(dāng)你這樣做的時(shí)候，已經(jīng)減少了索引所需要部分?jǐn)?shù)據(jù)約一半的空間。這個(gè)折中的是MYSQL不能消除太多行使用這個(gè)索引。比如像下面這樣的查詢：

SELECT * FROM phone_book WHERE last_name = 'Smith';

取出所有以Smit開頭的字段，包括所有名字是Smith，Smitty等人。這個(gè)查詢會(huì)在之后丟棄Smitty等不想關(guān)的行。

Multicolumn indexes(多列):

像很多關(guān)系數(shù)據(jù)庫引擎一樣，MySQL允許你創(chuàng)建的索引由多列組成。

ALERT TABLE phone_book ADD INDEX (last_name, first_name)

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

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

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

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

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

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

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

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

Index order(索引的順序):

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

例如，有些數(shù)據(jù)庫可能會(huì)執(zhí)行這個(gè)快速查詢：

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

然后這個(gè)查詢變慢：

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

為什呢？因?yàn)橛行?shù)據(jù)庫按降序排序存儲(chǔ)索引，并對按這個(gè)順序讀取索引進(jìn)行了優(yōu)化。在第一個(gè)例子中，數(shù)據(jù)庫使用了多列索引定位到所有匹配的記錄，因?yàn)檫@些記錄已經(jīng)按降序被存儲(chǔ)了，已經(jīng)沒有必要再對他們進(jìn)行排序了。但是在第二個(gè)例子中，服務(wù)器找到所有匹配的記錄，然后對這些行執(zhí)行第二遍，以對它們進(jìn)行排序。

在必要時(shí)，MySQL能夠向后遍歷索引。這也能使查詢變得非?？欤軐?shí)現(xiàn)在任何情況下都不需要對記錄進(jìn)行排序。

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

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

ALERT TABLE phone_book ADD UNIQUE (phone_number)

唯一索引具有雙重目的，當(dāng)你根據(jù)一個(gè)電話號(hào)查詢的時(shí)候他的功能和其他索引一樣。

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

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

唯一索引和非唯一索引使用的空間一樣多。記錄每個(gè)字段的值和位置。如果使用唯一索引作為約束而從不作為索引那就是一種浪費(fèi)。換句話說，你可以依靠唯一索引強(qiáng)制執(zhí)行唯一性，但是不要寫使用唯一鍵的查詢。在這個(gè)例子中，MySQL沒有必要將每個(gè)記錄的位置存儲(chǔ)在索引中：因?yàn)槟阌肋h(yuǎn)不會(huì)使用他們。

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

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

使用MyISAM表，索引保持在一個(gè)完全隔離的文件中，包含了主鍵（也可能有二級鍵）列表和表示記錄的字節(jié)偏移量的值。這些確保了MySQL可以找到并且快速跳到在數(shù)據(jù)庫中的那個(gè)點(diǎn)來查找記錄。MySQL必須用這種方法存儲(chǔ)索引，因?yàn)橛涗浕旧鲜且詠y序進(jìn)行存儲(chǔ)的。

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

當(dāng)你的數(shù)據(jù)幾乎都通過他的主鍵查找時(shí)，聚集的索引可以使查找快得難以置信。使用標(biāo)準(zhǔn)MyISAM索引，有兩個(gè)查找，一個(gè)到索引，另一個(gè)到表自身，通過索引指定得位置。使用聚集的索引，有一個(gè)直接指向相關(guān)記錄的查找。

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

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

MySQL遍歷了phone_number索引去查找555-7271項(xiàng)，包含了主鍵項(xiàng)Zawodny，因?yàn)殡娫挶镜闹麈I是last name，MySQL會(huì)在數(shù)據(jù)庫自身中跳到相關(guān)項(xiàng)。

換句話說，基于主鍵的查詢將會(huì)非?？?，基于二級索引的查找速度基本上和MyISAM索引是一致的。

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

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

根據(jù)查詢的結(jié)果在問題中插入記錄

基于插入數(shù)據(jù)的記錄，為記錄決定新的主鍵

搬遷存儲(chǔ)的記錄一次這個(gè)問題中的記錄將在表空間中被移動(dòng)到正確的位置

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

你可能在想，如果你為一些記錄插入了主鍵，在執(zhí)行UPDATE命令時(shí)將會(huì)花費(fèi)相當(dāng)一部分時(shí)間，特別是在更大的表中。更明智地選擇主鍵。盡量使用幾乎不會(huì)改變的值，例如使用社會(huì)安全賬號(hào)而不是姓，使用序列號(hào)而不是產(chǎn)品名等等。

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

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

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

Indexing NULLs:

SQL在執(zhí)行邏輯操作時(shí)使用三態(tài)邏輯是很難記住的。除非一個(gè)字段聲明了NOT NUL，有三種可能的結(jié)果在邏輯比較中。這個(gè)比較可能由于它們的值是等價(jià)的所以結(jié)果是對的；也有可能由于它們的值是不等價(jià)的所以結(jié)果是錯(cuò)誤的；又或者它會(huì)因?yàn)橹凳荖ULL而不會(huì)匹配。無論何時(shí)比較的一個(gè)值是NULL那么結(jié)果也是NULL。

程序員經(jīng)常認(rèn)為NULL就相當(dāng)于沒有被定義或未知的。這是一種告訴數(shù)據(jù)庫服務(wù)器“一個(gè)未知的數(shù)據(jù)來了”的方式。所以NULL值的數(shù)據(jù)怎么能影響索引呢？

NULL值將會(huì)使用常規(guī)索引（即非唯一索引），所有數(shù)據(jù)庫服務(wù)器都是這樣。然而，不像狠多數(shù)據(jù)庫服務(wù)器，MySQL允許你在唯一索引上使用NULL值[6]。你可以在唯一索引上存儲(chǔ)你想存儲(chǔ)盡可能多的NULL值。這看起來感覺有點(diǎn)違反直覺，但這就是NULL的本質(zhì)。因?yàn)镹ULL代表了一個(gè)沒有被定義的值。如果它在唯一索引只允許單個(gè)值，MySQL需要宣稱所有的NULL值都是一樣的。

去做一些更有意思的事，一個(gè)NULL值在主鍵中可能值出現(xiàn)一次。為什么呢？因?yàn)镾QL標(biāo)準(zhǔn)主宰了這一行為。這是在MySQL少數(shù)方法中唯一不同于唯一鍵的主鍵。而且如果你想知道，在索引中允許NULL值并不會(huì)影響性能。

[1]這個(gè)有點(diǎn)偏差。數(shù)據(jù)庫不只是存儲(chǔ)匹配記錄的位置，我們很快久知道為什么。

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

[3]在UNION中，每個(gè)邏輯是單獨(dú)運(yùn)行然后進(jìn)行合并的。

[4]除了NULL，當(dāng)然了，NULL總是特殊的情況。

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

[6]MySQL版本3.23及以前的版本不支持這個(gè)功能，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))，下面各種情況都能夠利用這個(gè)多列索引進(jìn)行快速查找嗎

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

此時(shí)這個(gè)查詢可以利用多列索引進(jìn)行快速查找。

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

此時(shí)這個(gè)查詢可以利用給出的多列索引進(jìn)行快速查找

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

此時(shí)該查詢不能利用給出的多列索引進(jìn)行快速查詢，因?yàn)檫@個(gè)多列索引是根據(jù)last_name找到它對應(yīng)的first_name的。

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

分享題目：數(shù)據(jù)庫index
網(wǎng)頁路徑：http://muchs.cn/article38/ihicpp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供域名注冊、品牌網(wǎng)站制作、電子商務(wù)、用戶體驗(yàn)、定制網(wǎng)站、網(wǎng)站制作

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容