mysql联合索引优化（3）

对于多列索引，不是使用的第一部分，则不会使用索引

1、建表：id+user_name联合主键索引

CREATE TABLE `t_user_details` (

  `id` int(11) DEFAULT NULL,

  `user_name` varchar(50) DEFAULT NULL,

  `user_phone` varchar(11) DEFAULT NULL,

  PRIMARY KEY (id,user_name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、插入sql

insert into t_user_details values(1,'yushengjun1','15921009245');

insert into t_user_details values(1,'yushengjun2','15921009245');

insert into t_user_details values(2,'yushengjun1','15921009245');

insert into t_user_details values(2,'yushengjun2','15921009245');

insert into t_user_details values(3,'yushengjun1','15921009245');

insert into t_user_details values(3,'yushengjun2','15921009245');

3、查询

EXPLAIN select * from user_details WHERE id=1

EXPLAIN select * from user_details WHERE id=1 and  user_name='yushengjun1';

EXPLAIN select * from user_details WHERE  user_name='yushengjun1';

EXPLAIN select * from user_details WHERE  user_name='yushengjun1' and  id=1