MySql Select 与使用错误索引的组合索引

Question

我有下表:

CREATE TABLE IF NOT EXISTS `dbname`.`test_table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(80) NOT NULL,      
  `confidence` FLOAT NULL DEFAULT NULL,
  `task_id` INT(11) NOT NULL,
  `check_status` INT(11) NULL DEFAULT NULL,
  `deleted` BOOLEAN NOT NULL DEFAULT 0,
  `createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
   INDEX task_status (task_id, check_status),
   INDEX task_name_conf(task_id, name, confidence))
ENGINE = InnoDB
AUTO_INCREMENT = 11
DEFAULT CHARACTER SET = utf8;

我经常使用以下查询:

select * from test_table where task_id = 13 and check_status = 1;
select name from test_table
    where task_id = 13
    AND name like '%name%'
        order by confidence desc;

如您所见，我创建了 2 个索引来加速上述查询。当我对第二个查询执行 explain 时，虽然 task_status 和 task_name_conf 都列为 possible_keys，但 key 字段只有 task_status。 ref 字段也只有一个 const，这意味着这里只使用 task_id。

为什么不使用task_name_conf索引来执行第二个查询？

编辑:

我知道名称like，然后 order by 无法利用该索引。只有平等才能发挥作用。 name ='name' order byconfidence 利用了 task_name_conf 索引。但奇怪的是 select name from test_table where task_id= and name= andconfidence= (standard 3 equals) 无法再次使用索引 task_name_conf。

Answer 1

name like '%name%' 条件不能使用任何索引，因为您要查找的值可以位于字段内的任何位置。

confidence 字段是 task_name_conf 索引中的最后一个，name 字段位于其前面，因此优化器无法使用该索引进行排序要么是信心。

这意味着只有task_id字段被任何索引覆盖，因此mysql选择较小(字段较少)的索引。

您需要将 name 字段完全保留在第二个索引之外。或者，如果名称搜索是最昂贵的标准，则考虑使用全文索引和搜索，而不是简单的 psttern 匹配。