索引失效

场景准备

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT '0',
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `height` int DEFAULT '0',
  `address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_age_name` (`code`,`age`,`name`),
  KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

• 查看执行计划

  • 如果想查看某条 sql 语句是否使用了索引
  • 通过 explain 关键字，查看该sql语句的执行计划，来判断索引使用情况
  • explain select * from user where id=1;

    不满足最左匹配原则

• 联合索引的索引是有顺序的，在使用联合索引时，需要注意最左前缀原则

  • 使用联合索引时，一定要有联合索引的第一个字段
  • 这就是 最左匹配原则

    使用了 select *

• 使用 select * 查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低

• 覆盖索引

  • 如果select语句中的查询列，都是索引列，那么这些列被称为覆盖索引
  • 这种情况下，查询的相关字段都能走索引，索引查询效率相对来说更高一些
  • 此时，即使不满足 最左匹配原则，也可使用联合索引

    索引列上有计算

    索引列使用了函数

    字段类型不同

• 比如 索引列是 vachar，SQL语句中忘了加引号而误认为是 int

• int类型的参数，不管在查询时加没加引号，都能走索引

  • mysql发现如果是 int 类型字段作为查询条件，会自动将该字段的传参进行隐式转换

    like左边包含%

    列对比

    使用or关键字

• 如果使用了 or 关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效

  not in 和 not exists

• 主键字段中使用 not in 关键字查询数据范围，仍然可以走索引

  • 而普通索引字段则会 索引失效

    order by

• 联合索引，满足最左匹配原则，还要加 limit 关键字或 where 关键字

• 如果包含了联合索引的多个排序字段，需要它们的排序规律是相同的
• 对不同的索引做 order by，会索引失效