前言

索引的設計可以遵循一些已有的原則，創(chuàng)建索引的時候應盡量考慮符合這些原則，便于提升索引的使用效率，更高效的使用索引。本節(jié)將介紹一些索引的設計原則。

1. 選擇唯一性索引

唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。例如，學生表中學號是具有唯一性的字段。為該字段建立唯一性索引可以很快的確定某個學生的信息。如果使用姓名的話，可能存在同名現(xiàn)象，從而降低查詢速度。

2. 為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引

經(jīng)常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作會浪費很多時間。如果為其建立索引，可以有效地避免排序操作。

3. 為常作為查詢條件的字段建立索引

如果某個字段經(jīng)常用來做查詢條件，那么該字段的查詢速度會影響整個表的查詢速度。因此，為這樣的字段建立索引，可以提高整個表的查詢速度。

注意：常查詢條件的字段不一定是所要選擇的列，換句話說，最適合索引的列是出現(xiàn)在 WHERE 子句中的列，或連接子句中指定的列，而不是出現(xiàn)在 SELECT 關(guān)鍵字后的選擇列表中的列。

4. 限制索引的數(shù)目

索引的數(shù)目不是“越多越好”。每個索引都需要占用磁盤空間，索引越多，需要的磁盤空間就越大。在修改表的內(nèi)容時，索引必須進行更新，有時還可能需要重構(gòu)。因此，索引越多，更新表的時間就越長。

如果有一個索引很少利用或從不使用，那么會不必要地減緩表的修改速度。此外，MySQL 在生成一個執(zhí)行計劃時，要考慮各個索引，這也要花費時間。創(chuàng)建多余的索引給查詢優(yōu)化帶來了更多的工作。索引太多，也可能會使 MySQL 選擇不到所要使用的最佳索引。

5. 盡量使用數(shù)據(jù)量少的索引

如果索引的值很長，那么查詢的速度會受到影響。例如，對一個 CHAR(100) 類型的字段進行全文檢索需要的時間肯定要比對 CHAR(10) 類型的字段需要的時間要多。

6. 數(shù)據(jù)量小的表最好不要使用索引

由于數(shù)據(jù)較小，查詢花費的時間可能比遍歷索引的時間還要短，索引可能不會產(chǎn)生優(yōu)化效果。

7. 盡量使用前綴來索引

如果索引字段的值很長，最好使用值的前綴來索引。例如，TEXT 和 BLOG 類型的字段，進行全文檢索會很浪費時間。如果只檢索字段的前面的若干個字符，這樣可以提高檢索速度。

8. 刪除不再使用或者很少使用的索引

表中的數(shù)據(jù)被大量更新，或者數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不再需要。應該定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。

9. 索引最好設置為 NOT NULL：

索引列存在 NULL 就會導致優(yōu)化器在做索引選擇的時候更加復雜，更加難以優(yōu)化，因為可為 NULL 的列會使索引、索引統(tǒng)計和值比較都更復雜，比如進行索引統(tǒng)計時，count 會省略值為NULL 的行。

NULL 值是一個沒意義的值，但是它會占用物理空間，所以會帶來的存儲空間的問題，因為 InnoDB 存儲記錄的時候，如果表中存在允許為 NULL 的字段，那么行格式 中至少會用 1 字節(jié)空間存儲 NULL 值列表。

10. 防止索引失效：

我們設置了索引并不意味著一定會用上索引，再某些情況下索引也會失效。

• 當我們使用左或者左右模糊匹配的時候，也就是 Link %x 或者 Link %x% 這兩種方式都會造成索引失效；
• 當我們在查詢條件中對索引列做了計算、函數(shù)、類型轉(zhuǎn)換操作，這些情況下都會造成索引失效；
• 聯(lián)合索引要能正確使用需要遵循最左匹配原則，也就是按照最左優(yōu)先的方式進行索引的匹配，否則就會導致索引失效。
• 在 WHERE 子句中，如果在 or 前的條件列是索引列，而在 or 后的條件列不是索引列，那么索引會失效。

總結(jié)

選擇索引的最終目的是為了使查詢的速度變快，上面給出的原則是最基本的準則，但不能只拘泥于上面的準則。應該在學習和工作中不斷的實踐，根據(jù)應用的實際情況進行分析和判斷，選擇最合適的索引方式。

最新評論