.jpg)
索引的設(shè)計(jì)和使用
5.1 mysql索引概述
所有mysql列類(lèi)型可以被索引。對(duì)相關(guān)列使用索引是提高select操作性能的最佳途徑。根據(jù)存儲(chǔ)引擎定義每個(gè)表的最大索引數(shù)和最大索引長(zhǎng)度。所有存儲(chǔ)引擎支持每個(gè)表至少16個(gè)索引，總索引長(zhǎng)度至少為256字節(jié)。大多數(shù)存儲(chǔ)引擎有更高的限制。
在mysql 5.1中，對(duì)于myisam和innodb表，前綴可以達(dá)到1000字節(jié)長(zhǎng)。請(qǐng)注意前綴的限制應(yīng)以字節(jié)為單位進(jìn)行測(cè)量，而create table語(yǔ)句中的前綴長(zhǎng)度解釋為字符數(shù)。當(dāng)為使用多字節(jié)字符集的列指定前綴長(zhǎng)度時(shí)一定要加以考慮。
還可以創(chuàng)建fulltext索引。該索引可以用于全文搜索。只有 myisam存儲(chǔ)引擎支持fulltext索引，并且只為char、varchar和text列。索引總是對(duì)整個(gè)列進(jìn)行，不支持局部(前綴)索引。也可以為空間列類(lèi)型創(chuàng)建索引。只有myisam存儲(chǔ)引擎支持空間類(lèi)型?？臻g索引使用r-樹(shù)。默認(rèn)情況memory(heap)存儲(chǔ)引擎使用hash索引，但也支持b-樹(shù)索引。
5.2 設(shè)計(jì)索引的原則
1. 搜索的索引列，不一定是所要選擇的列。換句話(huà)說(shuō)，最適合索引的列是出現(xiàn)在where 子句中的列，或連接子句中指定的列，而不是出現(xiàn)在select 關(guān)鍵字后的選擇列表中的列。
2. 使用惟一索引?？紤]某列中值的分布。對(duì)于惟一值的列，索引的效果最好，而具有多個(gè)重復(fù)值的列，其索引效果最差。例如，存放年齡的列具有不同值，很容易區(qū)分各行。
而用來(lái)記錄性別的列，只含有“ m”和“f”，則對(duì)此列進(jìn)行索引沒(méi)有多大用處（不管搜索哪個(gè)值，都會(huì)得出大約一半的行）
3. 使用短索引。如果對(duì)串列進(jìn)行索引，應(yīng)該指定一個(gè)前綴長(zhǎng)度，只要有可能就應(yīng)該這樣做。
例如，如果有一個(gè) char(200) 列，如果在前10 個(gè)或20 個(gè)字符內(nèi)，多數(shù)值是惟一的，那么就不要對(duì)整個(gè)列進(jìn)行索引。對(duì)前10 個(gè)或20 個(gè)字符進(jìn)行索引能夠節(jié)省大量索引空間，也可能會(huì)使查詢(xún)更快。較小的索引涉及的磁盤(pán)i/o 較少，較短的值比較起來(lái)更快。
更為重要的是，對(duì)于較短的鍵值，索引高速緩存中的塊能容納更多的鍵值，因此，mysql也可以在內(nèi)存中容納更多的值。這增加 了找到行而不用讀取索引中較多塊的可能性。
（當(dāng)然，應(yīng)該利用一些常識(shí)。 如僅用列值的第一個(gè)字符進(jìn)行索引是不可能有多大好處的，因?yàn)檫@個(gè)索引中不會(huì)有許多不 同的值。 ）
4. 利用最左前綴。在創(chuàng)建一個(gè)n 列的索引時(shí)，實(shí)際是創(chuàng)建了 mysql可利用的n 個(gè)索引。
多列索引可起幾個(gè)索引的作用，因?yàn)榭衫盟饕凶钭筮叺牧屑瘉?lái)匹配行。這樣的列集稱(chēng)為最左前綴。 （這與索引一個(gè)列的前綴不同，索引一個(gè)列的前綴是利用該的前n個(gè)字符作為索引值。 ）
5. 不要過(guò)度索引。不要以為索引“越多越好”，什么東西都用索引是錯(cuò)的。每個(gè)額外的索引都要占用額外的磁盤(pán)空間，并降低寫(xiě)操作的性能，這一點(diǎn)我們前面已經(jīng)介紹 過(guò)。在修改表的內(nèi)容時(shí)，索引必須進(jìn)行更新，有時(shí)可能需要重構(gòu)，因此，索引越多，所花的時(shí)
間越長(zhǎng)。如果有一個(gè)索引很少利用或從不使用，那么會(huì)不必要地減緩表 的修改速度。
此外，mysql在生成一個(gè)執(zhí)行計(jì)劃時(shí)，要考慮各個(gè)索引，這也要費(fèi)時(shí)間。創(chuàng)建多余的索引給查詢(xún)優(yōu)化帶來(lái)了更多的工作。索引太多，也可能會(huì)使 mysql選擇不到所要使用的最好索引。只保持所需的索引有利于查詢(xún)優(yōu)化。如果想給已索引的表增加索引，應(yīng)該考慮所要增加的索引是否是現(xiàn)有多列索引的最左 索引。如果是，則就不要費(fèi)力去增加這個(gè)索引了，因?yàn)橐呀?jīng)有了。
6. 考慮在列上進(jìn)行的比較類(lèi)型。索引可用于“ =”、“ >”和between 運(yùn)算。在模式具有一個(gè)直接量前綴時(shí)，索引也用于like 運(yùn)算。如果只將某個(gè)列用于其他類(lèi)型的運(yùn)算時(shí)（如strcmp( )） ，對(duì)其進(jìn)行索引沒(méi)有價(jià)值。
5.3 btree索引與hash索引
對(duì)于btree和hash索引，當(dāng)使用=、、in、is null或者is not null操作符時(shí)，關(guān)鍵元素與常量值的比較關(guān)系對(duì)應(yīng)一個(gè)范圍條件。hash索引還有一些其它特征：它們只用于使用=或操作符的等式比較(但很快)。 優(yōu)化器不能使用hash索引來(lái)加速order by操作。
(該類(lèi)索引不能用來(lái)按順序搜索下一個(gè)條目）。mysql不能確定在兩個(gè)值之間大約有多少行(這被范圍優(yōu)化器用來(lái)確定使用哪個(gè)索引)。 如果你將一個(gè)myisam表改為hash-索引的memory表，會(huì)影響一些查詢(xún)。只能使用整個(gè)關(guān)鍵字來(lái)搜索一行。(用b-樹(shù)索引，任何關(guān)鍵字的最左面的前綴可用來(lái)找到行)。
對(duì)于btree索引，當(dāng)使用>、=、，或者like ‘pattern'(其中 ‘pattern’不以通配符開(kāi)始)操作符時(shí)， 關(guān)鍵元素與常量值的比較關(guān)系對(duì)應(yīng)一個(gè)范圍條件。
“常量值”系指：查詢(xún)字符串中的常量、同一聯(lián)接中的const或system表中的列、無(wú)關(guān)聯(lián)子查詢(xún)的結(jié)果、完全從前面類(lèi)型的子表達(dá)式組成的表達(dá)式
下面是一些where子句中有范圍條件的查詢(xún)的例子：
下列范圍查詢(xún)適用于 btree索引和hash索引
select * from t1wherekey_col = 1orkey_col in (15,18,20);
下列范圍查詢(xún)適用于btree索引
select * from t1where key_col > 1and key_col select * from t1where key_col like ‘ab%’or key_col between ‘bar’ and
‘foo’;
5.4 mysql如何使用索引
索引用于快速找出在某個(gè)列中有一特定值的行。不使用索引，mysql必須從第1條記錄開(kāi)始然后讀完整個(gè)表直到找出相關(guān)的行。表越大，花費(fèi)的時(shí)間越多。如果表中查詢(xún)的列有一個(gè)索引，mysql能快速到達(dá)一個(gè)位置去搜尋到數(shù)據(jù)文件的中間，沒(méi)有必要看所有數(shù)據(jù)。如
果一個(gè)表有1000行，這比順序讀取至少快100倍。注意如果你需要訪問(wèn)大部分行，順序讀取要快得多，因?yàn)榇藭r(shí)我們避免磁盤(pán)搜索。
大多數(shù)mysql索引(primary key、unique、index和fulltext)在b樹(shù)中存儲(chǔ)。只是空間列類(lèi)型的索引使用r-樹(shù)，并且memory表還支持hash索引。
關(guān)于什么情況下數(shù)據(jù)庫(kù)會(huì)使用索引以及什么情況下數(shù)據(jù)庫(kù)不會(huì)使用索引的詳細(xì)解釋請(qǐng)看優(yōu)化篇的相關(guān)章節(jié)，這里就不再累述。
想要獲取更多相關(guān)文章請(qǐng)關(guān)注php中文網(wǎng)（www.php.cn）！
.png)
