MySQL何時使用索引與不使用索引？

MySQL何時使用索引與不使用索引？

索引：

使用索引可快速存取資料庫表中的特定資訊。索引是對資料庫表中一列或多列的值進行排序的一種結構，例如 employee 表的姓(name)列。如果要按姓尋找特定職員，與必須搜尋表中的所有行相比，索引會協助您更快地獲得該資訊。

索引是一個單獨的、物理的資料庫結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的資料頁的邏輯指標清單。

索引提供指向儲存在表的指定列中的資料值的指標，然後根據您指定的排序次序對這些指標排序。資料庫使用索引的方式與您使用書籍中的索引的方式很相似：它搜尋索引以找到特定值，然後順指標找到包含該值的行。

在資料庫圖表中，您可以在選定表的“索引/鍵”屬性頁面中建立、編輯或刪除每個索引類型。當儲存索引所附加到的表，或儲存該表所在的關係圖時，索引將儲存在資料庫中。

注意：

並非所有的資料庫都以相同的方式使用索引。作為通用規則，只有當經常查詢索引列中的資料時，才需要在表上建立索引。索引佔用磁碟空間，並且降低添 加、刪除和更新行的速度。在多數情況下，索引用於資料檢索的速度優勢大大超過它的不足之處。但是，如果應用程式非常頻繁地更新資料或磁碟空間有限，則可能 需要限制索引的數量。

可以基於資料庫表中的單列或多列建立索引。多列索引使您可以區分其中一列可能有相同值的行。

如果經常同時搜尋兩列或多列或按兩列或多列排序時，索引也很有協助。例如，如果經常在同一查詢中為姓和名兩列設定判據，那麼在這兩列上建立多列索引將很有意義。

確定索引的有效性：

• 檢查查詢的 WHERE 和 JOIN 子句。在任一子句中包括的每一列都是索引可以選擇的對象。
• 對新索引進行實驗以檢查它對執行查詢效能的影響。
• 考慮已在表上建立的索引數量。最好避免在單個表上有很多索引。
• 檢查已在表上建立的索引的定義。最好避免包含共用列的重疊索引。
• 檢查某列中唯一資料值的數量，並將該數量與表中的行數進行比較。比較的結果就是該列的可選擇性，這有助於確定該列是否適合建立索引，如果適合，確定索引的類型。

MySQL何時使用索引

對一個鍵碼使用>, >=, =, <, <=, IF NULL和BETWEEN

1. SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5; 
2. SELECT * FROM table_name WHERE key_part1 IS NULL; 

當使用不以萬用字元開始的LIKE

1. SELECT * FROM table_name WHERE key_part1 LIKE 'jani%' 

在進行連接時從另一個表中提取行時

1. SELECT * from t1,t2 where t1.col=t2.key_part 

找出指定索引的MAX()或MIN()值

1. SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10 

一個鍵碼的首碼使用ORDER BY或GROUP BY

1. SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3 

在所有用在查詢中的列是鍵碼的一部分時間

1. SELECT key_part3 FROM table_name WHERE key_part1=1 

MySQL何時不使用索引

如果MySQL能估計出它將可能比掃描整張表還要快時，則不使用索引。例如如果key_part1均勻分布在1和100之間，下列查詢中使用索引就不是很好：

1. SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90 

如果使用HEAP表且不用=搜尋所有鍵碼部分。

在HEAP表上使用ORDER BY。

如果不是用鍵碼第一部分

1. SELECT * FROM table_name WHERE key_part2=1 

如果使用以一個萬用字元開始的LIKE

1. SELECT * FROM table_name WHERE key_part1 LIKE '%jani%' 

搜尋一個索引而在另一個索引上做ORDER BY

1. SELECT * from table_name WHERE key_part1 = # ORDER BY key

 

增加註明：當查詢條件欄位相同時，使用OR範圍查詢索引可以執行，反之，索引失效。

　　  例如：

1. SELECT * from table_name WHERE name= "A"  OR  name="B";(生效)
   
2. SELECT * from table_name WHERE name= "A"  OR  sex="男";(失效）

誤區

　　1.並不是在where條件常用的列上加上索引，一條語句只會走一個索引。

　　2.在多列建立索引，查詢哪一列，都將發揮作用？ 滿足左首碼要求

　　　    聯合索引   index（A1，A2，A3）

　　   　 where A=1   　　　　　　　　　　　　　　　　　　　　 (生效)

　　　　where A=1 and where A2=2　　　　　　　　　　　　　  (生效)

　　　　where A=1 and where A2=2 and where A3=3         　　　  (生效)

　　　　where A=2        where A3=3　                                              (不生效)

　　　　where A=1 and where A2>2 and where A3=3                      (A1,A2生效，A3不生效)

　　　　where A=1 and where A2 like 'jay%' and where A3=3          (A1,A2生效，A3不生效)

SQL最佳化

1.limit分頁最佳化

2.叢集索引：主鍵、沒有主鍵則定位第一個唯一索引（所有行的值非空）、如果上2個條件都不滿足則自動產生一個6位元組的id叢集索引。

3.輔助索引：包含索引值的書籤，會儲存書籤資料。

4.count(*)   改為輔助索引。