MySQL 提供 MyISAM 、 InnoDB 、 memory(heap) 等多种存储引擎。每种存储引擎对于索引的支持以及实现都不尽相同， 本文主要讨论 InnoDB 引擎相关的联合索引应用。
为何使用索引
索引用于快速找出在某个列中有一特定值的行。在查询时如果没有应用索引，MySQL 将不得不扫描表以找出符合条件的数据项，我们知道，IO操作是非常耗时的。
建立索引的几个原则
-
尽量使用唯一索引，对于有唯一值的列索引效果最好，对于像性别只有很少的值的列索引效果就不明显。
-
索引长度尽量短，这样做有几个好处，首先短的索引可以节省索引空间，也会使查找的速度得到提升。对于较短的键值，索引高速缓存中的块能容纳更多的键值，MySQL也可以在内存中容纳更多的值。
-
太长的列，可以选择只建立部分索引
-
更新非常频繁的数据不适宜建索引
-
利用最左前缀原则，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
-
不要过多创建索引，首先过多的索引会占用更多的空间，而且每次增、删、改操作都会重建索引，而且过多索引也会增加之后的优化复杂度
-
尽量扩展索引，比如现有索引(a)，现在我又要对(a,b)进行索引，不需要再建一个索引(a,b)
-
请注意，一次查询是不能应用多个索引的
-
<，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行
-
like “xxxx%” 是可以用到索引的，like “%xxxx” 则不行(like “%xxx%” 同理)
-
NULL会使索引的效果大打折扣
浅谈联合索引
首先因为 InnoDB 的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键。主键对于 InnoDB 的索引结构是十分重要的。
InnoDB 引擎的索引是使用 B+树 实现索引结构的，当我们建立一个联合索引(a, b, c)时，B+树将按照从左至右来建立搜索树，然后检索时B+树将先比较 a 然后再其基础上比较 b 和 c 。不难看出如果我们的搜索条件中只有 a 和 c ，将不能使用完整的(a, b, c)索引，如果我们的搜索条件中没有 a 那么这条查询将不会用上索引，这其实就是最左前缀特性。
** 接下来我们来看下联合索引应用时的几种情况： **
desc users;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| rname | varchar(50) | NO | | NULL | |
| rdesc | varchar(50) | NO | | NULL | |
| age | int(11) | NO | MUL | NULL | |
| card | int(5) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
show index from users;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users | 0 | PRIMARY | 1 | id | A | 15 | NULL | NULL | | BTREE | | |
| users | 1 | age_name_desc | 1 | age | A | 15 | NULL | NULL | | BTREE | | |
| users | 1 | age_name_desc | 2 | rname | A | 15 | 10 | NULL | | BTREE | | |
| users | 1 | age_name_desc | 3 | rdesc | A | 15 | 15 | NULL | | BTREE | | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+- 全列匹配
explain select * from users where age = 11 and rname = "asd" and rdesc = "asd";
+----+-------------+-------+------+---------------+---------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------------+---------+-------------------+------+-------------+
| 1 | SIMPLE | users | ref | age_name_desc | age_name_desc | 83 | const,const,const | 1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------------------+------+-------------+以上 explain 的结果可以看出，当对索引中所有列进行精确匹配的时候，可以用到完整索引。
explain select * from users where rname = "asd" and age = 11 and rdesc = "asd";
+----+-------------+-------+------+---------------+---------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------------+---------+-------------------+------+-------------+
| 1 | SIMPLE | users | ref | age_name_desc | age_name_desc | 83 | const,const,const | 1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------------------+------+-------------+按照B+树的结构联合索引本是对顺序十分敏感的，但是从以上结果可看出调整顺序并没有影响到索引的选用，这是因为MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引。
- 部分匹配（左前缀）
explain select * from users where age = 11;
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+
| 1 | SIMPLE | users | ref | age_name_desc | age_name_desc | 4 | const | 1 | NULL |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+从上述结果可看出当精确匹配最左前缀的列时，是可以用到索引的，但是 key_len = 4，只用到了第一列前缀索引。
explain select * from users where age = 11 and rdesc = "asd";
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| 1 | SIMPLE | users | ref | age_name_desc | age_name_desc | 4 | const | 1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+上述查询缺失了rname列，可以看出当缺失中间列时将不能使用完整的联合索引。查询只用到了最左部分索引，而rdesc由于没有rname无法和左前缀衔接。
explain select * from users where rname = "asd";
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 15 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+上述查询由于查询条件不是最左前缀，将不能使用联合索引age_name_desc，建立联合索引时顺序是很重要的，必须在建索引前考虑清楚。
- 非精确查询
explain select * from users where age = 11 and rname like 'sad%';
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
| 1 | SIMPLE | users | range | age_name_desc | age_name_desc | 36 | NULL | 1 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
explain select * from users where age = 11 and rname like 'sad%' and rdesc = 'asd';
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
| 1 | SIMPLE | users | range | age_name_desc | age_name_desc | 83 | NULL | 1 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+可以看出like查询当通配符’%‘不出现在开头时，是可以应用到索引的。
explain select * from users where age = 11 and rname like '%sad%';
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| 1 | SIMPLE | users | ref | age_name_desc | age_name_desc | 4 | const | 1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+通配符’%‘出现在开头则不行
接下来我们看看范围查询
explain select * from users where age = 61 and rname < 'fasd';
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
| 1 | SIMPLE | users | range | age_name_desc | age_name_desc | 36 | NULL | 1 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+- 函数和表达式
explain select * from users where (age + 1) = 33;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 15 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+当查询条件中出现函数或表达式时，将不能应用索引。写查询语句时要尽量避免表达式和函数的出现。
指导性建议
建议在选择性高的列上建立索引，所谓索引的选择性，是指不重复的索引值与表记录数的比值：
Index Selectivity = Cardinality / Count显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大。 比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就趋近于0
select count(distinct(rname))/count(*) as selectivity from users;
+-------------+
| selectivity |
+-------------+
| 0.9333 |
+-------------+有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。
select count(distinct(left(rname, 3)))/count(*) as selectivity from users;
+-------------+
| selectivity |
+-------------+
| 0.7333 |
+-------------+
select count(distinct(left(rname, 5)))/count(*) as selectivity from users;
+-------------+
| selectivity |
+-------------+
| 0.9333 |
+-------------+可以看到，当把前缀取到5时selectivity值就和完整的selectivity值一样了，这样可以大幅度减小索引所占用的空间，而且相应的查询速度也会有一定提升。
* * *