索引失效的7个原因

索引失效的7个原因

• 概述
• 1. 最左匹配原则
• 2. 使用函数
• 3. 计算操作
• 4. Like %
• 5. 使用Or导致索引失效
• 6. in使用不当
• 7. order By
• 8. 总结
• 9. 补充 SELECT *
• 参考

  实际工作以及面试��，应该经常会遇到SQL相关的问题，而这些问题中，索引失效的场景又是一个常客。下面总结一下索引失效的场景，一共7种，索引失效的原因逃不过这7个。

  概述

  主要内容如下：

  先创建一张表用于测试，表中创建了三个索引（MySQL版本8.0.26）

  • 主键索引 id
  • 普通索引 age
  • 联合索引 name,code,address

    SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for user-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user`  (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,  `code` int(100) NULL DEFAULT NULL,  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  `age` int(11) NULL DEFAULT NULL,  `create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0),  PRIMARY KEY (`id`) USING BTREE,  INDEX `普通索引`(`age`) USING BTREE,  INDEX `联合索引`(`name`, `code`, `address`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of user-- ----------------------------INSERT INTO `user` VALUES (1, '海绵宝宝', 1001, '上海', 23, '2022-11-02 20:44:14');INSERT INTO `user` VALUES (2, '章鱼哥', 1002, '北京', 22, '2022-11-02 20:44:16');INSERT INTO `user` VALUES (3, '派大星', 1003, '苏州', 23, '2022-11-02 20:44:19');INSERT INTO `user` VALUES (4, '蟹老板', 1004, '南通', 24, '2022-11-02 20:44:25');

    1. 最左匹配原则

    最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，并且最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、、 1 and name = "派大星" and address = "上海"

    把code=1改为code>1，执行：

    虽然也使用了联合索引，但是她的type是range。而改造前的type如下图是ref：

    那么type位range和ref的区别是什么呢？

    可以理解为：range是范围查找，ref是索引查找。

    根据官方描述，扫描方式从快到慢依次是：

    也就是说，**遇到范围查询(>、