Mysql的联（复）合索引

 

一、概念
两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

     建立联合主键同时会自动建立复合索引，复合索引遵循的是最左前缀法。但是联合主键创建的索引，要考虑是否能查询出数据的问题。后面再说。

二、命名规则
    1、需要加索引的字段，要在where条件中
    2、数据量少的字段不需要加索引
    3、如果where条件中是OR关系，加索引不起作用
    4、复合索引遵循的是最左前缀法（带头大哥不能死，中间兄弟不能断）：即最左优先，在检索数据时从联合索引的最左边开始匹配
    说明：先通过最左边的（建立索引的字段的左边的字段）字段，来确定下一步的查找对象.
有效命中来说，并不是触发。只要是索引，或者某个联合索引的一部分，就会被触发，具体命中了那些索引字段，要根据key_len来做判断。

三、创建索引

1 CREATE TABLE 'user' (
2 'id' int(11) NOT　NULL,
3 'name' varchar(50) DEFAULT　NULL,
4 'age' int(11) NOT　NULL,
5 'sum' int(11) NOT　NULL,
6 PRIMARY KEY('id','age','sum')
7 )ENGINE=InnoDB DEFAULT CHARSET=utf8;


select * from user where name=?
select * from user where age=?
select * from user where name=? and age=?

     如果我们是在name和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在name、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(name, age)的复合索引，那么其实相当于创建了(name)、(name,age)两个索引，这被称为最佳左前缀特性。

     因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

四、为什么要使用联合索引

1 CREATE TABLE `test` (
2 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
3 `uId` int(10) unsigned NOT NULL,
4 `chatId` smallint(5) unsigned NOT NULL,
5 `tId` int(11) NOT NULL,
6 PRIMARY KEY (`id`),
7 UNIQUE KEY `test_uId_chatId_tId` (`uId`,`chatId`,`tId`) USING BTREE
8 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

   1.  减少开销，联合索引test_uId_chatId_tId 实际建立了(uId)、(uId,chatId)、(uId,chatId,tId)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

   2.   覆盖索引,对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

   补充：关于回表=》回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

  3.  效率高,索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

  4.  引申: **************************
对于联合索引(uId,chatId,tId)，查询语句SELECT * FROM test WHERE chatId=2;是否能够触发索引,估计大部分想到最左前缀原则的人，回答都是不触发，实际上是会触发索引的。

上述两个查询的explain结果中显示用到索引的情况类型是不一样的。可观察explain结果中的type字段，分别是：

  1）type:  index
       这种类型表示mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个联合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。所以，上述语句会触发索引。
  2 ）type: ref
       这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

 

注意事项：
     只要列中包含有NULL值都将不会被包含在索引中
     复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的，所以我们在数据库设计时尽可能不要让字段的默认值为NULL。

==========================================================================================

    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];