單列索引和多列索引 · web開發者知識體系

### 索引 #### 單列索引所有的MySQL列類型能被索引。在相關的列上的使用索引是改進SELECT操作性能的最好方法。一個表最多可有16個索引。最大索引長度是256個字節，盡管這可以在編譯MySQL時被改變。對于CHAR和VARCHAR列，你可以索引列的前綴。這更快并且比索引整個列需要較少的磁盤空間。在CREATE TABLE語句中索引列前綴的語法看起來像這樣： KEY index_name (col_name(length)) 下面的例子為name列的頭10個字符創建一個索引： mysql> CREATE TABLE test ( name CHAR(200) NOT NULL, KEY index_name (name(10))); 對于BLOB和TEXT列，你必須索引列的前綴，你不能索引列的全部。 #### 多列索引 MySQL能在多個列上創建索引。一個索引可以由最多15個列組成。（在CHAR和VARCHAR列上，你也可以使用列的前綴作為一個索引的部分)。一個多重列索引可以認為是包含通過合并(concatenate)索引列值創建的值的一個排序數組。當你為在一個WHERE子句索引的第一列指定已知的數量時，MySQL以這種方式使用多重列索引使得查詢非常快速，即使你不為其他列指定值。假定一張表使用下列說明創建： mysql> CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name)); 那么索引name是一個在last_name和first_name上的索引，這個索引將被用于在last_name或last_name和first_name的一個已知范圍內指定值的查詢，因此，name索引將使用在下列查詢中： mysql> SELECT * FROM test WHERE last_name=”Widenius”; mysql> SELECT * FROM test WHERE last_name=”Widenius” AND first_name=”Michael”; mysql> SELECT * FROM test WHERE last_name=”Widenius” AND (first_name=”Michael” OR first_name=”Monty”); mysql> SELECT * FROM test WHERE last_name=”Widenius” AND first_name >=”M” AND first_name < "N"; 然而，name索引將不用在下列詢問中： mysql> SELECT * FROM test WHERE first_name=”Michael”; mysql> SELECT * FROM test WHERE last_name=”Widenius” OR first_name=”Michael”; 創建一個多列索引： CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name) ); 創建多個索引： CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name), INDEX_2 name (first_name) ); 當查詢語句的條件中包含last_name 和 first_name時，例如： SELECT * FROM test WHERE last_name='Kun' AND first_name='Li'; sql會先過濾出last_name符合條件的記錄，在其基礎上再過濾first_name符合條件的記錄。那如果我們分別在last_name和first_name上創建兩個列索引，mysql的處理方式就不一樣了，它會選擇一個最嚴格的索引來進行檢索，可以理解為檢索能力最強的那個索引來檢索，另外一個利用不上了，這樣效果就不如多列索引了。但是多列索引的利用也是需要條件的，以下形式的查詢語句能夠利用上多列索引： SELECT * FROM test WHERE last_name='Widenius'; SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael'; SELECT * FROM test WHERE last_name='Widenius' AND (first_name='Michael' OR first_name='Monty'); SELECT * FROM test WHERE last_name='Widenius' AND first_name >='M' AND first_name < 'N'; 以下形式的查詢語句利用不上多列索引： SELECT * FROM test WHERE first_name='Michael'; SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael'; 多列建索引比對每個列分別建索引更有優勢，因為索引建立得越多就越占磁盤空間，在更新數據的時候速度會更慢。另外建立多列索引時，順序也是需要注意的，應該將嚴格的索引放在前面，這樣篩選的力度會更大，效率更高。