[数据库]索引失效

索引失效

https://mp.weixin.qq.com/s/mwME3qukHBFul57WQLkOYg

• 未遵循最左匹配原则
• 索引列上使用了函数
  • 例如，select a from table where length(b) = 5，不会走b上的索引
• 索引列上使用了计算
  • 例如，select a from table where lb-1 = 5，不会走b上的索引
• 使用like%
  • 例如，SELECT * FROM products WHERE products.prod_name like '%Li';不会走索引
  • 但是，SELECT * FROM products WHERE products.prod_name like 'Li%';就会走索引
• 使用 OR 导致索引失效
  • 例如，select a，b from table where a = 1 or b = 2，如果只在a上建立了索引，那么这条语句不会走索引，如果想要走索引，需要建立a，b的联合索引。
• in /not in 和 exists/ not exists使用不当
  • In 不是一定会造成全表扫描的，IN 肯定会走索引，但是当 IN 的取值范围较大时会导致索引失效，走全表扫描
• order by使用不当

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_code` int(100) NULL DEFAULT NULL,
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `height` double NULL DEFAULT NULL,
  `classid` int(11) NULL DEFAULT NULL,
  `create_time` datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `普通索引`(`height`) USING BTREE,
  INDEX `联合索引`(`sname`, `s_code`, `address`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '学生1', 1, '上海', 170, 1, '2022-11-02 20:44:14');
INSERT INTO `student` VALUES (2, '学生2', 2, '北京', 180, 2, '2022-11-02 20:44:16');
INSERT INTO `student` VALUES (3, '变成派大星', 3, '京东', 185, 3, '2022-11-02 20:44:19');
INSERT INTO `student` VALUES (4, '学生4', 4, '联通', 190, 4, '2022-11-02 20:44:25');

explain select create_time from student where sname = "变成派大星" ; -- 走索引
explain select create_time from student where s_code = 1;   -- 不走索引
explain select create_time from student where address = "上海";  -- 不走索引
explain select create_time from student where address = "上海" and s_code = 1; -- 不走索引
explain select create_time from student where address = "上海" and sname = "变成派大星";  -- 走索引,这一条会被优化，先查sname再查address
explain select create_time from student where sname = "变成派大星" and address = "上海";  -- 走索引
explain select create_time from student where sname = "变成派大星" and s_code = 1 and address = "上海";  -- 走索引