[Mysql]索引失效

索引失效

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

未遵循最左匹配原则#

由于构建一棵 B+树只能根据一个值来确定索引关系，所以联合索引的B+树依赖联合索引最左侧的元素建立树结构。所以如果想要利用B+树进行数据筛选，必须要提供联合索引的最左列。

下面展示联合索引是否会走索引的情况

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

索引列上使用了函数，计算#

例如，select a from table where length(b) = 5，不会走b上的索引

例如，select a from table where b-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的联合索引。

让我们来尝试解释一下原因

如果b上没有建立索引，对b进行筛选就要走全表扫描，在这个全表扫描的过程中就可以顺便对a进行筛选，没必要单独对a走索引，反而多此一举。

in /not in 和 exists/ not exists使用不当#

In 不是一定会造成全表扫描的，IN 肯定会走索引，但是当 IN 的取值范围较大时会导致索引失效，走全表扫描

对索引隐式类型转换#

如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，你会在执行计划的结果发现这条语句会走全表扫描。
但是如果索引字段本身是数字，而查询条件是字符串，却会走索引。

这是因为mysql中字符串和数字比较的时候，会把字符串转换成数字。如果索引是字符串就会把索引转换成数字，这样对索引进行了操作之后就没有办法再走索引了

order by使用不当#

这主要是 Mysql 自身优化的问题，我们都知道 OrderBy 是排序， 那就代表我需要对数据进行排序，如果我走索引 ，索引是排好序的 如果我要查的数据就在索引里面，那直接就可以查到有序的数据并返回，但是如果我要查的数据不在索引里面，我需要回表，也会消耗时间，这种情况下就不如直接全表扫描来的快。所以mysql会根据是否回表选择更加快的方法。

走索引 + 回表
不走索引 直接全表扫描

Mysql 认为直接全表扫描的速度比回表的速度快所以就直接走索引了，在 Order By 的情况下，走全表扫描反而是更好的选择，但是这不是一定的，如果我想要查的数据就在索引里面，那就不需要回表，在不需要回表的情况下，直接走索引反而是更好的选择。