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="郑州"
