mysql數據庫建立組合索引原則

mysql數據庫建立組合索引原則是什麼？估計很多的朋友對於mysql組合索引都不知道什麼時候創建會比較好了，下面小編來為各位介紹一下吧。

現實中，mysql可以根據業務需要建立組合索引，由於mysql使用B－Tree格式索引，可以直接定位記錄，無需掃描。mysql建立多列索引有最左前綴的原則，即最左優先，如：

如果有一個2列的索引(col1,col2),則已經對(col1)、(col1,col2)上建立了索引；
如果有一個3列索引(col1,col2,col3)，則已經對(col1)、(col1,col2)和(col1,col2,col3)上建立了索引；

如何建立組合索引？
最頻繁使用的列放在左邊；
查看列的選擇性（即該列的索引值數量與記錄數量的比值），比值越高，效果越好；

例如用戶表，如果按照用戶姓名查詢比較多，可以考慮在根據姓名建立索引。這裡有兩種形式：1在用戶的名字字段（name）在做索引。2,在用戶的名字和姓氏字段建立索引（name+family_name）。我們查看了下name字段的選擇性,執行如下語句。

SELECT count(DISTINCT(name))/count(*) AS Selectivity FROM user;

+-------------+

| Selectivity |
+-------------+
|   0.0052    |
+-------------+
發現值非常小，因為name相同的用戶實在太多。 再查看下name+family_name的選擇性。

SELECT count(DISTINCT(concat(name, family_name)))/count(*) AS Selectivity FROM user;

+-------------+

| Selectivity |
+-------------+
|   0.9563    |
+-------------+
發現名字完全相同的員工基本沒有。但是索引key如果太長會使得索引文件變大並且維護開銷增大，name+family name的長度等於30，還是希望有一個艱巨選擇性和長度的方案。 因此可以考慮在name和family前自己字符上面建立索引，例如 name + left(family_name,5)：

SELECT count(DISTINCT(concat(name, left(family_name,5))))/count(*) AS Selectivity FROM user;

+-------------+

| Selectivity |
+-------------+
|   0.9012    |
+-------------+

雖然不如以name+family_name的選擇性高，但是已經基本滿足要求。

MySQL組合索引的注意點

比如這個索引 key(last_name, first_name, dob)
如果想使用索引，你必須保證按索引的最左邊前綴(leftmost prefix of the index)來進行查詢。
(1)匹配全值(Match the full value)：對索引中的所有列都指定具體的值。
即所有條件都是等於，並且全部匹配

(2)匹配最左前綴(Match a leftmost prefix)：僅僅使用索引中的第1列。
即索引中的最左邊的用等於條件。

(3)匹配列前綴(Match a column prefix)：這僅僅使用索引中的第1列。
即’X%’

(4)匹配值的范圍查詢(Match a range of values)：僅僅使用索引中第1列。
即第一列 可以用大於 小於 X>0 and X<1

(5)匹配部分精確而其它部分進行范圍匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name為Allen，而first name以字母K開始的人。
即，第一列 精確匹配，後面一列 范圍匹配

(6)僅對索引進行查詢(Index-only queries)：如果查詢的列都位於索引中，則不需要讀取元組的值。
由於B-樹中的節點都是順序存儲的，所以可以利用索引進行查找(找某些值)，也可以對查詢結果進行ORDER BY。

當然，使用B-tree索引有以下一些限制：
(1) 查詢必須從索引的最左邊的列開始。關於這點已經提了很多遍了。例如你不能利用索引查找在某一天出生的人。
(2) 不能跳過某一索引列。例如，你不能利用索引查找last name為Smith且出生於某一天的人。
(3) 存儲引擎不能使用索引中范圍條件右邊的列。例如，如果你的查詢語句為WHERE last_name=”Smith” AND first_name LIKE ‘J%’ AND dob=’1976-12-23′，則該查詢只會使用索引中的前兩列，因為LIKE是范圍查詢。

總結出來就是，使用了組合索引以後，你必須要從左到右依次精確匹配索引，能匹配多少匹配多少，直到最後一個可以匹配范圍索引，只要用了某列范圍索引，後面的列的索引就無效了。。所以組合索引雖好，但必須要用巧。條件並不能隨便給的。

mysql組合索引與字段順序 

一般來說，可能是某些字段沒有創建索引，或者是組合索引中字段的順序與查詢語句中字段的順序不符。

看下面的例子：
假設有一張訂單表(orders)，包含order_id和product_id二個字段。
一共有31條數據。符合下面語句的數據有5條。

執行下面的sql語句：
select product_id
from orders
where order_id in (123, 312, 223, 132, 224);

這條語句要mysql去根據order_id進行搜索，然後返回匹配記錄中的product_id。

所以組合索引應該按照以下的順序創建：
create index orderid_productid on orders(order_id, product_id)
mysql> explain select product_id from orders where order_id in (123, 312, 223, 132, 224) G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: range
possible_keys: orderid_productid
          key: orderid_productid
      key_len: 5
          ref: NULL
         rows: 5
        Extra: Using where; Using index
1 row in set (0.00 sec)

可以看到，這個組合索引被用到了,掃描的范圍也很小，只有5行。

如果把組合索引的順序換成product_id, order_id的話，
mysql就會去索引中搜索 *123 *312 *223 *132 *224，必然會有些慢了。
mysql> create index orderid_productid on orders(product_id, order_id);                                                      
Query OK, 31 rows affected (0.01 sec)
Records: 31  Duplicates: 0  Warnings: 0

mysql> explain select product_id from orders where order_id in (123, 312, 223, 132, 224) G

*************************** 1. row ***************************

           id: 1
  select_type: SIMPLE
        table: orders
         type: index
possible_keys: NULL
          key: orderid_productid
      key_len: 10
          ref: NULL
         rows: 31
        Extra: Using where; Using index
1 row in set (0.00 sec)

這次索引搜索的性能顯然不能和上次相比了。

rows:31，我的表中一共就31條數據。

索引被使用部分的長度：key_len:10，比上一次的key_len:5多了一倍。

不知道是這樣在索引裡面查找速度快，還是直接去全表掃描更快呢？
mysql> alter table orders add modify_a char(255) default 'aaa';
Query OK, 31 rows affected (0.01 sec)
Records: 31  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> explain select modify_a from orders where order_id in (123, 312, 223, 132, 224) G         
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 31
        Extra: Using where
1 row in set (0.00 sec)

這樣就不會用到索引了。 剛才是因為select的product_id與where中的order_id都在索引裡面的。

為什麼要創建組合索引呢？這麼簡單的情況直接創建一個order_id的索引不就行了嗎？
如果只有一個order_id索引，沒什麼問題，會用到這個索引，然後mysql要去磁盤上的表裡面取到product_id。

如果有組合索引的話，mysql可以完全從索引中取到product_id，速度自然會快。

再多說幾句組合索引的最左優先原則：
組合索引的第一個字段必須出現在查詢組句中，這個索引才會被用到。
如果有一個組合索引(col_a,col_b,col_c)

下面的情況都會用到這個索引：

col_a = "some value";
col_a = "some value" and col_b = "some value";
col_a = "some value" and col_b = "some value" and col_c = "some value";
col_b = "some value" and col_a = "some value" and col_c = "some value";

對於最後一條語句，mysql會自動優化成第三條的樣子~~。

下面的情況就不會用到索引：
col_b = "aaaaaa";
col_b = "aaaa" and col_c = "cccccc";

MySQL單列索引和組合索引的區別

MySQL單列索引和組合索引的區別可能有很多人還不是十分的了解，下面就為您分析兩者的主要區別，供您參考學習。

為了形象地對比兩者，再建一個表：
CREATE TABLE myIndex ( i_testID INT NOT NULL AUTO_INCREMENT, vc_Name VARCHAR(50) NOT NULL, vc_City VARCHAR(50) NOT NULL, i_Age INT NOT NULL, i_SchoolID INT NOT NULL, PRIMARY KEY (i_testID) );
在這 10000 條記錄裡面 7 上 8 下地分布了 5 條 vc_Name="erquan" 的記錄，只不過 city,age,school 的組合各不相同。
來看這條 T-SQL：SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City='鄭州' AND i_Age=25;
首先考慮建MySQL單列索引：
在 vc_Name 列上建立了索引。執行 T-SQL 時，MYSQL 很快將目標鎖定在了 vc_Name=erquan 的 5 條記錄上，取出來放到一中間結果集。在這個結果集裡，先排除掉 vc_City 不等於"鄭州"的記錄，再排除 i_Age 不等於 25 的記錄，最後篩選出唯一的符合條件的記錄。
雖然在 vc_Name 上建立了索引，查詢時MYSQL不用掃描整張表，效率有所提高，但離我們的要求還有一定的距離。同樣的，在 vc_City 和 i_Age 分別建立的MySQL單列索引的效率相似。
為了進一步搾取 MySQL 的效率，就要考慮建立組合索引。就是將 vc_Name,vc_City，i_Age 建到一個索引裡：
ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);
建表時，vc_Name 長度為 50，這裡為什麼用 10 呢？因為一般情況下名字的長度不會超過 10，這樣會加速索引查詢速度，還會減少索引文件的大小，提高 INSERT 的更新速度。
執行 T-SQL 時，MySQL 無須掃描任何記錄就到找到唯一的記錄！！
肯定有人要問了，如果分別在 vc_Name,vc_City，i_Age 上建立單列索引，讓該表有 3 個單列索引，查詢時和上述的組合索引效率一樣嗎？大不一樣，遠遠低於我們的組合索引。雖然此時有了三個索引，但 MySQL 只能用到其中的那個它認為似乎是最有效率的單列索引。
建立這樣的組合索引，其實是相當於分別建立了
vc_Name,vc_City,i_Age
vc_Name,vc_City
vc_Name
這樣的三個組合索引！為什麼沒有 vc_City，i_Age 等這樣的組合索引呢？這是因為 mysql 組合索引“最左前綴”的結果。簡單的理解就是只從最左面的開始組合。並不是只要包含這三列的查詢都會用到該組合索引，下面的幾個 T-SQL 會用到：
SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="鄭州"
SELECT * FROM myIndex WHREE vc_Name="erquan"
而下面幾個則不會用到：
SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="鄭州"
SELECT * FROM myIndex WHREE vc_City="鄭州"