联合索引有一个最左前缀原则，具体是什么？

[phpforlan]

问题：
联合索引有一个最左前缀原则，具体是什么？

[phpforlan]

mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：

如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；
如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；

总结：
1、b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道第一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。

2、比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。（这种情况无法用到联合索引）

[phpforlan]

测试1：
联合索引： KEY key_aS_aT (auditSt,applyTime)

1、explain select * from tblArticle0 where applyTime=123 and auditSt =1 ;
2、 explain select * from tblArticle0 where auditSt =1 and applyTime =123 ;

测试结果：1和2两种情况都用到了索引key_aS_aT，所以索引顺序是可以颠倒的，只要where条件中的字段包含索引中的第一个字段即可。

[phpforlan]

测试2：
联合索引： KEYsex_type_age on user(sex,type,age);

1、explain select * from user_test where sex = 2
2、explain select * from user_test where sex = 2 and type = 2
3、explain select * from user_test where sex = 2 and age = 10

测试结果：这3条sql语句都会用到联合索引sex_type_age，因为where条件中包含了第一个索引字段sex。

[chenjiayao]

如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col3)和(col1,col2,col3)上建立了索引；

有个地方指教下 : 其他地方的资料跟你的有有点偏差,有一个3列索引(col1,col2,col3),其实是对(col1),(col1,col2),(col1,col2,col3)建立了索引,而不是你说的 : 对(col1)、(col1,col2)、(col1,col3)和(col1,col2,col3)上建立了索引；
链接

[phpforlan]

你好，你可以根据测试2中的示例，在测试数据库中测试下。我这边的测试结果是，只要包含联合索引中最左字段，那么在查询的时候会用到该联合索引。

[chenjiayao]

对,从Explain种的key字段可以看到使用了联合索引.但是还是有点区别的.我根据你的示例建了一个表.

CREATE TABLE `t1` (
  `test_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `age` int(11) NOT NULL,,
  KEY `idx_id_name_age` (`test_id`,`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

执行explain select * from t1 where test_id = 2 and age = 2结果

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | idx_id_name_age | idx_id_name_age | 4       | const |    1 | Using where |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+

执行explain select * from t1 where test_id = 2 and name='xxxxx';的结果

+----+-------------+-------+------+-----------------+-----------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+-----------------+-----------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | idx_id_name_age | idx_id_name_age | 16      | const,const |    1 | Using where |
+----+-------------+-------+------+-----------------+-----------------+---------+-------------+------+-------------+

虽然key字段都是idx_id_name_age,但是在key_len字段第一个为4,但是第二个为16,说明第一个只使用了id作为索引,并没有使用联合索引.第二个字段才使用了联合索引.
也就是说idx_id_name_age(id,name,age)实际上只有三个索引,不包含(nam,age)索引.

[shawn47]

我理解测试3 中用到的索引是(col1)，并没有用到(col1,col2,col3)。

根据最左前缀原则，联合索引的顺序是（sex,type,age），测试3中查询条件只有sex 和age这两个，缺少中间的type，所以无法使用(col1,col2,col3)。

如果在测试3中想用到索引(col1,col2,col3)，可以在sex 和age中间填补上一个type的全量查询。

[sanwancoder]

https://www.zhihu.com/question/36996520