Using index 和 Using index condition 区别

基础数据

员工表

CREATE TABLE `employees1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `firstname` varchar(24) NOT NULL DEFAULT '' COMMENT 'firstname',
  `lastname` varchar(24) NOT NULL DEFAULT '' COMMENT 'lastname',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`firstname`,`lastname`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工表';

extra

Using index

不需要回表

explain SELECT name,firstname FROM employees1 WHERE NAME='LiuWei' AND firstname = 'aa'

Using where; Using index

不需要回表

explain SELECT name,firstname FROM employees1 WHERE NAME='LiuWei' AND firstname LIKE '%aa%'

Using index condition

需要回表

explain SELECT * FROM employees1 WHERE NAME='LiuWei' AND lastname LIKE '%aa%'