mysql聯合索引詳解

標籤：

所有的MySQL列類型能被索引。在相關的列上的使用索引是改進SELECT操作效能的最好方法。

一、首碼索引

對於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以這種方式使用多重列索引使得查詢非常快速，即使你不為其他列指定值。

 

索引原則
1.索引越少越好
原因：主要在修改資料時，每個索引都要進行更新，降低寫速度。
2.最窄的欄位放在鍵的左邊
3.避免file sort排序，暫存資料表和表掃描.


MySQL如何利用索引最佳化ORDER BY排序語句

MySQL也能利用索引來快速地執行ORDER BY和GROUP BY語句的排序和分組操作。

通過索引最佳化來實現MySQL的ORDER BY語句最佳化：
1、ORDER BY的索引最佳化。如果一個SQL語句形如：
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]這個欄位上建立索引就可以實現利用索引進行order by 最佳化。
2、WHERE + ORDER BY的索引最佳化，形如：
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
建立一個聯合索引(columnX,sort)來實現order by 最佳化。
注意：如果columnX對應多個值，如下面語句就無法利用索引來實現order by的最佳化
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];
3、WHERE+ 多個欄位ORDER BY
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
建立索引(uid,x,y)實現order by的最佳化,比建立(x,y,uid)索引效果要好得多。
MySQL Order By不能使用索引來最佳化排序的情況
* 對不同的索引鍵做 ORDER BY ：(key1,key2分別建立索引)
SELECT * FROM t1 ORDER BY key1, key2;
* 在非連續的索引鍵部分上做 ORDER BY：(key_part1,key_part2建立聯合索引;key2建立索引)
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
* 同時使用了 ASC 和 DESC：(key_part1,key_part2建立聯合索引)
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
* 用於搜尋記錄的索引鍵和做 ORDER BY 的不是同一個：(key1,key2分別建立索引)
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
* 如果在WHERE和ORDER BY的欄位上應用運算式(函數)時，則無法利用索引來實現order by的最佳化
SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;
特別提示:
1>mysql一次查詢只能使用一個索引。如果要對多個欄位使用索引，建立複合索引。
2>在ORDER BY操作中，MySQL只有在排序條件不是一個查詢條件運算式的情況下才使用索引。

 

如何知道mysql中sql語句索引是否生效 

mysql函數 explain

 explain命令詳解
 用explain 來執行檢驗sql 例如explain select * from a+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys    | key     | key_len |ref   | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t3   | const | PRIMARY,idx_t3_id | PRIMARY | 4      | const |   1 |       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ 第四列 type 這列很重要,顯示了串連使用了哪種類別,有無使用索引.
從最好到最差的連線類型為const、eq_reg、ref、range、indexhe和ALL (1).system

這是const聯結類型的一個特例。表僅有一行滿足條件.2).const

表最多有一個匹配行，它將在查詢開始時被讀取。因為僅有一行，在這行的列值可被最佳化器剩餘部分認為是常數。const表很快，因為它們唯讀取一次！ 3). eq_ref

對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯結類型，除了const類型。它用在一個索引的所有部分被聯結使用並且索引是UNIQUE或PRIMARYKEY。

eq_ref可以用於使用=操作符比較的帶索引的列。比較值可以為常量或一個使用在該表前面所讀取的表的列的運算式。 (4).ref

對 於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯結只使用鍵的最左邊的首碼，或如果鍵不是UNIQUE或 PRIMARYKEY（換句話說，如果聯結不能基於關鍵字選擇單個行的話），則使用ref。如果使用的鍵僅僅匹配少量行，該聯結類型是不錯的。

ref可以用於使用=或<=>操作符的帶索引的列。 (5).  ref_or_null

該聯結類型如同ref，但是添加了MySQL可以專門搜尋包含NULL值的行。在解決子查詢中經常使用該聯結類型的最佳化。

在下面的例子中，MySQL可以使用ref_or_null聯結來處理ref_tables：

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL; 

(6). index_merge

該聯結類型表示使用了索引合并最佳化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。

例如:
mysql> explain select * from t4 where id=3952602 oraccountid=31754306 ;
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
| id | select_type | table | type       | possible_keys             | key                      | key_len | ref  | rows | Extra                                           |
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
|  1 | SIMPLE      | t4   | index_merge | idx_t4_id,idx_t4_accountid |idx_t4_id,idx_t4_accountid | 4,4     | NULL|    2 | Using union(idx_t4_id,idx_t4_accountid); Usingwhere |
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
1 row in set (0.00 sec)

(7). unique_subquery

該類型替換了下面形式的IN子查詢的ref：

value IN (SELECT primary_key FROM single_table WHEREsome_expr)
unique_subquery是一個索引尋找函數，可以完全替換子查詢，效率更高。

(8).index_subquery

該聯結類型類似於unique_subquery。可以替換IN子查詢，但只適合下列形式的子查詢中的非唯一索引：

value IN (SELECT key_column FROM single_table WHEREsome_expr)

(9).range

只檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。在該類型中ref列為NULL。

當使用=、<>、>、>=、<、<=、ISNULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range

mysql> explain select * from t3 where id=3952602 or id=3952603;
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys    | key       | key_len |ref  | rows | Extra       |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | t3   | range | PRIMARY,idx_t3_id | idx_t3_id | 4      | NULL |   2 | Using where |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
1 row in set (0.02 sec)

(10).index

該聯結類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引檔案通常比資料檔案小。

當查詢只使用作為單索引一部分的列時，MySQL可以使用該聯結類型。

(11). ALL

對於每個來自於先前的表的行組合，進行完整的表掃描。如果表是第一個沒標記const的表，這通常不好，並且通常在它情況下很差。通常可以增加更多的索引而不要使用ALL，使得行能基於前面的表中的常數值或列值被檢索出。

mysql聯合索引詳解