[資料庫]索引失效

Duancf發表於2024-07-05
資料庫索引

索引失效

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 = "上海";  -- 走索引

相關文章