使用覆盖索引是解决的关键,可以使得最佳左前缀原则失效
测试需要用到的表结构和数据
DROP TABLE IF EXISTS `emp1`;
CREATE TABLE `emp1` (
`id` int DEFAULT NULL,
`name` varchar(15) DEFAULT NULL,
`hire_date` date DEFAULT NULL,
`salary` double(10,2) DEFAULT NULL,
KEY `name_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*Data for the table `emp1` */
insert into `emp1`(`id`,`name`,`hire_date`,`salary`) values
(2,NULL,'2000-12-21',4000.00),
(3,'jim','2022-03-10',6000.00),
(4,'Jam','2022-03-10',6600.00),
(103,'Hunold','1990-01-03',9000.00),
(104,'Ernst','1991-05-21',6000.00),
(105,'Austin','1997-06-25',5760.00),
(106,'Pataballa','1998-02-05',5760.00),
(107,'Lorentz','1999-02-07',4200.00),
(204,'Baer','1994-06-07',12000.00);
建立要LIKE的字段的索引
#创建name索引
CREATE INDEX name_index ON emp1(`name`);
使用覆盖索引
#使用覆盖索引,可以%放左边
EXPLAIN SELECT `name`
FROM emp1
WHERE `name` LIKE '%u';
查询的结果集,只包含索引的字段,就会使用覆盖索引,在使用覆盖索引时,MySQL查询优化器会认为使用索引才是成本最低的,最佳左前缀原则会失效。
使用覆盖索引和联合索引,可以查询到更多的字段
#建立联合索引
CREATE INDEX name_hiredate_salary_index
ON emp1(`name`, hire_date, salary);
#使用覆盖索引和联合索引,可以%放左边并查询到更多的列
EXPLAIN SELECT `name`, hire_date, salary
FROM emp1
WHERE `name` LIKE '%u';