1.  MySQL如何使用索引

索引用于快速查找具有特定列值的行。如果沒(méi)有索引，MySQL必須從第一行開(kāi)始，然后遍歷整個(gè)表以找到相關(guān)的行。表越大，花費(fèi)越多。如果表中有相關(guān)列的索引，MySQL可以快速確定要在數(shù)據(jù)文件中間查找的位置，而不必查看所有數(shù)據(jù)。這比順序讀取每一行要快得多。

大多數(shù)MySQL索引（PRIMARY KEY，UNIQUE，INDEX和FULLTEXT）存儲(chǔ)在B樹(shù)（B-tree）中。例外情況：空間數(shù)據(jù)類(lèi)型的索引使用R樹(shù)； MEMORY表還支持哈希索引。 InnoDB對(duì)FULLTEXT索引使用倒排列表。

MySQL使用索引進(jìn)行以下操作：

• 快速查找與WHERE子句匹配的行
• 如果可以在多個(gè)索引之間進(jìn)行選擇，則MySQL通常會(huì)使用查找最小行數(shù)（最具選擇性的索引）的索引
• 有多列索引（也叫“復(fù)合索引”或者“聯(lián)合索引”），那么優(yōu)化器可以使用索引的任何最左前綴來(lái)查找行。 例如，如果在（col1，col2，col3）上有一個(gè)三列索引，則在（col1），（col1，col2）和（col1，col2，col3）上都有索引搜索功能。
• 使用關(guān)聯(lián)（join）查詢(xún)從其他表中檢索行時(shí)，如果聲明相同的類(lèi)型和大小，MySQL可以更有效地在列上使用索引。在這種情況下，如果將VARCHAR和CHAR聲明為相同的大小，則認(rèn)為它們相同。例如，VARCHAR（10）和CHAR（10）的大小相同，但VARCHAR（10）和CHAR（15）的大小不同。
• 對(duì)于非二進(jìn)制字符串列之間的比較，兩個(gè)列應(yīng)使用相同的字符集
• 如果排序或分組是在可用索引的最左前綴（例如，ORDER BY key_part1，key_part2）上完成的，則對(duì)表進(jìn)行排序或分組。如果在所有key部分后面都跟隨有DESC，則將以相反的順序讀取key。
• 在某些情況下，MySQL可以使用索引來(lái)滿(mǎn)足ORDER BY子句，并避免執(zhí)行文件排序操作時(shí)涉及的額外排序。
• 在某些情況下，可以?xún)?yōu)化查詢(xún)以檢索值而無(wú)需查詢(xún)數(shù)據(jù)行。（為查詢(xún)提供所有必要結(jié)果的索引稱(chēng)為覆蓋索引）如果查詢(xún)僅從表中使用某些索引中包含的列，則可以從索引樹(shù)中檢索所選值以提高速度

最后，索引對(duì)小表的查詢(xún)不太重要。當(dāng)查詢(xún)需要訪(fǎng)問(wèn)大多數(shù)行時(shí)，順序讀取比處理索引快。

2.  避免全表掃描

當(dāng)MySQL使用全表掃描來(lái)解析查詢(xún)時(shí)，EXPLAIN的輸出在type列中顯示ALL。 這通常在以下情況下發(fā)生：

• 表太小，以至于執(zhí)行全表掃描要比索引查找要快得多。對(duì)于少于10行且行長(zhǎng)度較短的表，這是很常見(jiàn)的。
• 在ON或WHERE字句中沒(méi)有使用索引列。
• 將索引列與常量值進(jìn)行比較，而MySQL已計(jì)算（基于索引樹(shù)）常量覆蓋了表的很大一部分并且表掃描會(huì)更快。
• 你正在通過(guò)另一列使用基數(shù)低的鍵（許多行與鍵值匹配）。在這種情況下，MySQL假定通過(guò)使用該鍵，它有可能執(zhí)行許多鍵查找，并且表掃描會(huì)更快。 

對(duì)于小表，表掃描通常是合適的，并且對(duì)性能的影響可以忽略不計(jì)。 

對(duì)于大表，可以嘗試以下技術(shù)，以避免優(yōu)化器錯(cuò)誤地選擇表掃描：

• 用ANALYZE TABLE tbl_name來(lái)更新key的分布
• 使用FORCE INDEX來(lái)告訴MySQL相比于使用給定的索引來(lái)說(shuō)，表掃描是非常昂貴的

3.  列索引

B樹(shù)（B-tree）數(shù)據(jù)結(jié)構(gòu)使索引可以在WHERE子句中快速找到與運(yùn)算符（例如=，>，≤，BETWEEN，IN等）相對(duì)應(yīng)的特定值，一組值或一系列值。 

每個(gè)存儲(chǔ)引擎都會(huì)定義每個(gè)表的最大索引數(shù)和最大索引長(zhǎng)度。所有存儲(chǔ)引擎支持每個(gè)表至少16個(gè)索引，并且索引總長(zhǎng)度至少為256個(gè)字節(jié)。

索引前綴

用col_name(N)可以創(chuàng)建僅使用列的前N個(gè)字符的索引。在InnoDB表中，前綴最長(zhǎng)767字節(jié)。

全文索引

FULLTEXT索引用于全文搜索。僅InnoDB和MyISAM存儲(chǔ)引擎支持FULLTEXT索引，并且僅支持CHAR，VARCHAR和TEXT列。索引始終在整個(gè)列上進(jìn)行，并且不支持列前綴索引。

空間索引

指依據(jù)空間對(duì)象的位置和形狀或空間對(duì)象之間的某種空間關(guān)系按一定的順序排列的一種數(shù)據(jù)結(jié)構(gòu)

MEMORY存儲(chǔ)引擎上的索引

默認(rèn)情況下，MEMORY存儲(chǔ)引擎使用HASH索引，但也支持BTREE索引。 

4.  多列索引

MySQL可以創(chuàng)建復(fù)合索引（即多列上的索引）。 一個(gè)索引最多可以包含16列。

假設(shè)有一張表示這樣定義的： 

CREATE TABLE test (
  id     INT NOT NULL,
  last_name CHAR(30) NOT NULL,
  first_name CHAR(30) NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_name (last_name,first_name)
);

idx_name索引是建立在last_name和first_name列之上的索引，該索引可以用于指定了last_name和first_name值組合的查詢(xún)，也可以用于僅指定last_name值的查詢(xún)，因?yàn)樵撍饕亲钭笄熬Y匹配的。

因此，idx_name索引可以用于下列查詢(xún)：

SELECT * FROM test WHERE last_name='Jones';

SELECT * FROM test WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test WHERE last_name='Jones' AND (first_name='John' OR first_name='Jon');

SELECT * FROM test WHERE last_name='Jones' AND first_name >='M' AND first_name < 'N';

然而，idx_name索引不能用于下列查詢(xún)：

SELECT * FROM test WHERE first_name='John';

SELECT * FROM test WHERE last_name='Jones' OR first_name='John';

考慮下面的SQL：

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果在col1和col2上存在一個(gè)多列索引，那么可以直接抓取適當(dāng)?shù)男?。如果col1和col2上分別存在單獨(dú)的單列索引，則優(yōu)化器將嘗試使用索引合并優(yōu)化，或者通過(guò)確定哪個(gè)索引需要排除更多行來(lái)查找限制性最強(qiáng)的索引，并使用該索引來(lái)獲取行。

如果表具有多列索引，那么優(yōu)化器可以使用該索引的任何最左前綴來(lái)查找行。例如，如果有一個(gè)三列索引(col1, col2, col3)，那么在(col1), (col1, col2), (col1, col2, col3) 上具有索引搜索功能。

如果列不構(gòu)成索引的最左前綴，則MySQL無(wú)法使用索引執(zhí)行查找。

再看下面的SQL語(yǔ)句：

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果在（col1, col2, col3）上存在復(fù)合索引，那么只有前兩個(gè)查詢(xún)會(huì)使用。而后最后兩個(gè)查詢(xún)不會(huì)使用索引來(lái)執(zhí)行查找，因?yàn)椋╟ol2）和（col2，col3）并不是（col1，col2，col3）的最左前綴。

5.  B-Tree 和 Hash 索引的比較

B樹(shù)索引特征 

B樹(shù)（B-tree）索引可用于使用=，>，>=，<，<=，BETWEEN運(yùn)算符的表達(dá)式中的列比較。如果LIKE的參數(shù)是一個(gè)不以通配符開(kāi)頭的常量字符串，則該索引也可以用于LIKE比較。

下列這些子句不會(huì)使用索引：

/* the LIKE value begins with a wildcard character */
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
/* the LIKE value is not a constant */
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

沒(méi)有覆蓋WHERE子句中所有AND級(jí)別的任何索引都不會(huì)用于優(yōu)化查詢(xún)。換句話(huà)說(shuō)，為了能夠使用索引，必須在每個(gè)AND組中使用索引的前綴。

下列WHERE子句會(huì)使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

  /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2

  /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5

  /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

下面這些WHERE子句不會(huì)使用索引：

/* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2

  /* Index is not used in both parts of the WHERE clause */
... WHERE index=1 OR A=10

  /* No index spans all rows */
... WHERE index_part1=1 OR index_part2=10

有時(shí)，即使有可用的索引，MySQL也不使用索引。發(fā)生這種情況的一種可能原因是，優(yōu)化器估計(jì)使用索引將需要訪(fǎng)問(wèn)表中很大比例的行。（在這種情況下，表掃描可能會(huì)更快，因?yàn)樗枰俚牟檎?。）但是，如果這樣的查詢(xún)使用LIMIT只檢索某些行，則MySQL仍然使用索引，因?yàn)樗梢愿斓卣业椒祷亟Y(jié)果的幾行。

哈希索引特征

哈希索引與剛剛討論的索引具有一些不同的特征：

• 哈希索引只用于=或者<=>運(yùn)算符的相等比較（但非常快），不用于比較運(yùn)算符來(lái)查找值的范圍。依賴(lài)于這種單值查找的系統(tǒng)被稱(chēng)為“鍵值對(duì)存儲(chǔ)”，為了將MySQL用于此類(lèi)應(yīng)用，請(qǐng)盡可能地使用哈希索引。
• 優(yōu)化器無(wú)法使用哈希索引來(lái)加快 ORDER BY 操作。（哈希類(lèi)型的索引不能用于按順序搜索下一個(gè)條目）
• MySQL無(wú)法確定兩個(gè)值之間大約有多少行（范圍優(yōu)化器使用它來(lái)決定使用哪個(gè)索引）
• 只有整個(gè)keys可用于搜索行。（對(duì)于B樹(shù)索引，key的任何最左邊的前綴都可用于查找行）

B-tree

樹(shù)型數(shù)據(jù)結(jié)構(gòu)，廣泛用于數(shù)據(jù)庫(kù)索引中。該結(jié)構(gòu)始終保持有序，從而可以快速查找精確匹配（等于運(yùn)算符）和范圍（例如，大于，小于和BETWEEN運(yùn)算符）。 此類(lèi)索引可用于大多數(shù)存儲(chǔ)引擎，例如InnoDB和MyISAM。

因?yàn)锽樹(shù)節(jié)點(diǎn)可以有很多子節(jié)點(diǎn)，所以B樹(shù)與二叉樹(shù)不同，后者的每個(gè)節(jié)點(diǎn)最多只能有2個(gè)子節(jié)點(diǎn)。

術(shù)語(yǔ)B樹(shù)的使用旨在參考索引設(shè)計(jì)的一般類(lèi)別。由于經(jīng)典B樹(shù)設(shè)計(jì)中不存在復(fù)雜性，MySQL存儲(chǔ)引擎使用的B樹(shù)結(jié)構(gòu)可能被視為變體。

Hash index

一種索引類(lèi)型，專(zhuān)用于使用相等運(yùn)算符而不是范圍運(yùn)算符的查詢(xún)。 它可用于MEMORY表。 盡管出于歷史原因，哈希索引是MEMORY表的默認(rèn)索引，但是該存儲(chǔ)引擎還支持B樹(shù)索引，對(duì)于一般用途的查詢(xún)而言，B樹(shù)索引通常是更好的選擇。

6.  優(yōu)化數(shù)據(jù)大小

設(shè)計(jì)表以使得它們?cè)诖疟P(pán)上占用最少的空間。 通過(guò)減少寫(xiě)入磁盤(pán)和從磁盤(pán)讀取的數(shù)據(jù)量，這可以帶來(lái)巨大的改進(jìn)。 較小的表通常在查詢(xún)執(zhí)行期間處理其內(nèi)容時(shí)需要較少的主內(nèi)存。表數(shù)據(jù)的任何空間減少都會(huì)導(dǎo)致索引變小，從而可以更快地處理索引。

MySQL支持許多不同的存儲(chǔ)引擎（表類(lèi)型）和行格式。對(duì)于每個(gè)表，可以決定使用哪種存儲(chǔ)和索引方法。為應(yīng)用程序選擇適當(dāng)?shù)谋砀袷娇梢源蟠筇岣咝阅堋?nbsp;

Table Columns

• 盡可能使用最有效（最小）的數(shù)據(jù)類(lèi)型。MySQL具有許多專(zhuān)門(mén)的類(lèi)型，可以節(jié)省磁盤(pán)空間和內(nèi)存。例如，如果可能，使用較小的整數(shù)類(lèi)型以獲得較小的表。MEDIUMINT通常比INT更好，因?yàn)镸EDIUMINT列使用的空間要少25％。
• 如果可能，將列聲明為NOT NULL。通過(guò)更好地使用索引并消除測(cè)試每個(gè)值是否為NULL的開(kāi)銷(xiāo)，它可以使SQL操作更快。而且還節(jié)省了一些存儲(chǔ)空間，每列一比特。如果表中確實(shí)需要NULL值，那就用它們。只要避免使用默認(rèn)設(shè)置，該默認(rèn)設(shè)置允許每列中都為NULL值。
  

Row Format 

為了通過(guò)壓縮形式存儲(chǔ)表數(shù)據(jù)來(lái)進(jìn)一步減少空間，請(qǐng)?jiān)趧?chuàng)建InnoDB表時(shí)指定ROW_FORMAT=COMPRESSED

Indexes 

• 表的主鍵索引應(yīng)盡可能短。這使得識(shí)別每一行變得容易而高效。對(duì)于InnoDB表，主鍵列在每個(gè)輔助索引條目中都是重復(fù)的，因此如果你有許多輔助索引，則較短的主鍵可節(jié)省大量空間。
• 僅創(chuàng)建需要提高查詢(xún)性能的索引。索引很適合檢索，但是會(huì)降低插入和更新操作的速度。如果你主要通過(guò)搜索列的組合來(lái)訪(fǎng)問(wèn)表，請(qǐng)?jiān)诒砩蟿?chuàng)建單個(gè)組合索引，而不是為每個(gè)列創(chuàng)建單獨(dú)的索引。索引的第一部分應(yīng)該是最常用的列。如果從表中查詢(xún)時(shí)總是使用許多列，則索引中的第一列應(yīng)是重復(fù)次數(shù)最多的列，以便更好地壓縮索引。
• 如果是一個(gè)長(zhǎng)字符串列，則很可能在第一個(gè)字符上具有唯一的前綴，這種情況下最好使用MySQL前綴進(jìn)行索引（PS：只對(duì)前幾個(gè)字符進(jìn)行索引）。索引越短越快，這不僅是因?yàn)樗鼈冃枰^少的磁盤(pán)空間，而且還因?yàn)樗鼈冞€會(huì)使索引緩存中的命中次數(shù)增加，從而減少磁盤(pán)尋道次數(shù)。 

Joins

在具有相同數(shù)據(jù)類(lèi)型的不同表中聲明具有相同信息的列，以加快基于相應(yīng)列的聯(lián)接。
保持列名簡(jiǎn)單，以便可以在不同的表中使用相同的名稱(chēng)，并簡(jiǎn)化聯(lián)接查詢(xún)。例如，在名為customer的表中，使用name列名代替customer_name。為了使你的名稱(chēng)可移植到其他SQL服務(wù)器中，請(qǐng)考慮將名稱(chēng)長(zhǎng)度控制在18個(gè)字符以?xún)?nèi)。 

Normalization

通常，盡量保持所有數(shù)據(jù)不冗余(數(shù)據(jù)庫(kù)理論中稱(chēng)為第三范式)。為它們分配唯一的id來(lái)代替一個(gè)重復(fù)冗長(zhǎng)的值，根據(jù)需要在多個(gè)較小的表中重復(fù)這些id，并通過(guò)在join子句中引用id來(lái)連接查詢(xún)中的表。 

7.  優(yōu)化數(shù)據(jù)類(lèi)型

數(shù)值類(lèi)型

行的唯一標(biāo)識(shí)最好使用數(shù)值而不是字符串，因?yàn)榇髷?shù)值比相應(yīng)的字符串占用更少的存儲(chǔ)字節(jié)，因此傳輸和比較它們更快，占用的內(nèi)存也更少。

字符和字符串類(lèi)型

• 在比較來(lái)自不同列的值時(shí)，盡可能使用相同的字符集和排序規(guī)則聲明這些列，以避免在運(yùn)行查詢(xún)時(shí)進(jìn)行字符串轉(zhuǎn)換。
• 對(duì)于小于8KB的列值，請(qǐng)使用二進(jìn)制VARCHAR而不是BLOB。 GROUP BY和ORDER BY子句可以生成臨時(shí)表，并且如果原始表不包含任何BLOB列，則這些臨時(shí)表可以使用MEMORY存儲(chǔ)引擎。
• 如果一個(gè)表包含名稱(chēng)和地址等字符串列，但是許多查詢(xún)沒(méi)有檢索這些列，那么可以考慮將字符串列分割成單獨(dú)的表，并在必要時(shí)使用帶有外鍵的連接查詢(xún)。當(dāng)MySQL從一行中檢索任何值時(shí)，它讀取包含該行所有列(可能還有其他相鄰行)的數(shù)據(jù)塊。保持每行較小，只包含最常用的列，可以讓每個(gè)數(shù)據(jù)塊容納更多的行。這種緊湊的表減少了常見(jiàn)查詢(xún)的磁盤(pán)I/O和內(nèi)存使用。
• 當(dāng)在InnoDB表中使用一個(gè)隨機(jī)生成的值作為主鍵時(shí)，最好在它前面加上一個(gè)升序值，比如當(dāng)前日期和時(shí)間(如果可能的話(huà))。當(dāng)連續(xù)的主鍵值物理上彼此相鄰存儲(chǔ)時(shí)，InnoDB可以更快地插入和檢索它們。
  

其它

• ORDER BY 和 GROUP BY 使用的列不一致，或者 在連接查詢(xún)中ORDER BY 或 GROUP BY 使用了第一個(gè)表以外的表的列時(shí)會(huì)使用臨時(shí)表
• MySQL對(duì)每個(gè)表有4096列的硬限制，但是對(duì)于給定的表，有效最大值可能會(huì)更少。 InnoDB對(duì)每個(gè)表有1017列的限制。

以上就是MySQL如何優(yōu)化索引的詳細(xì)內(nèi)容，更多關(guān)于MySQL優(yōu)化索引的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論