MySQL索引的使用(2)其他会导致索引失效的情况

建表语句，数据，和索引插入：

DROP TABLE IF EXISTS `index_test`;
CREATE TABLE `index_test`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `index1` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `index2` int(0) NULL DEFAULT NULL,
  `index3` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `code` int(0) NULL DEFAULT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_index123`(`index1`, `index2`, `index3`) USING BTREE,
  INDEX `idx_code`(`code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

# 添加索引

ALTER TABLE movie ADD index idx_code_type_name (code, type, name);
ALTER TABLE movie ADD index idx_ranks (ranks);

#插入数据

INSERT INTO `index_test` (`index1`, `index2`, `index3`, `code`, `name`) VALUES ('101', 1, 'a', 1, 'name1');
INSERT INTO `index_test` (`index1`, `index2`, `index3`, `code`, `name`) VALUES ('202', 2, 'b', 3, 'name2');
INSERT INTO `index_test` (`index1`, `index2`, `index3`, `code`, `name`) VALUES ('303', 3, 'c', 5, 'name3');

1.如果select语句中的查询列都是索引列，那么这些列被称为覆盖索引。这种情况下，查询的相关字段都能走索引，索引查询的效率相对高一些

# 使用了 select *

EXPLAIN
select * from index_test;
#不走索引

sql中使用了select *，从执行结果来看，走了全表扫描，没有用到任何索引，查询效率非常低

EXPLAIN
SELECt index1, index2, index3 from index_test;
#走索引

2.order by导致索引失效

EXPLAIN
SELECT index1, index2, index3, code 
from index_test ORDER BY code;
#不走索引

EXPLAIN
SELECT code from index_test ORDER BY code;
#走索引

在我们的开发过程中，经常会使用到order by，为了保证order by的查询效率，建议将order by字段和select字段添加联合索引; 但是如果需求经常变更需要增加字段，不能总是去修改select字段的联合索引；可以通过给order by增加索引，先通过子查询查询出id,然后再手动回表查询所需要的字段

3.索引列上有计算/使用了函数会对索引列进行一次重新计算

EXPLAIN
SELECT * from index_test where IFNULL(code,0)=0;
#不走索引

EXPLAIN
SELECT * from index_test where code+1=2;
#不走索引

4.字段类型不同

mysql如果是int类型字段作为查询条件时，他会自动将该字段的参数进行隐式转换，把字符串换成int类型

EXPLAIN
SELECT * from index_test where index1='101';
#走索引

EXPLAIN
SELECT * from index_test where index1=101;
#不走索引

EXPLAIN
SELECT * from index_test where code='101';
#走索引

5.like左边包含了%

当like语句中的%出现在查询条件的左边时，索引会失效；但是如果我们使用覆盖索引就不会失效

EXPLAIN
SELECT * from index_test where index1 like '%1';
#不走索引

EXPLAIN
SELECT * from index_test where index1 like '1%';
#走索引

EXPLAIN
SELECT * from index_test where index1 like '%1%';
#不走索引

EXPLAIN
SELECT index1, index3, index2 from index_test where  index1 LIKE '%1%';
#走索引

6.列对比

如果把两个单独建了索引的列用来做对比时索引会失效

走覆盖索引的话是可以走索引的，比如只查询ranks字段或者将where条件字段换成我们的聚合索引上面的字段

EXPLAIN
SELECT * from index_test where id=code;
#不走索引

EXPLAIN
SELECT code from index_test where id=code;
#走索引

EXPLAIN
SELECT index1, index2, index3 from index_test where id=index1;
#走索引

7.使用or关键字

如果使用了or关键字，那么他前面和后面的字段都要加索引，不然所有的索引都会失效。这里是一个大坑

EXPLAIN
SELECT * from index_test where id=1 or code=1;
# or字段前后两个字段都增加了索引，mysql8会走索引，mysql5.7不会走索引

EXPLAIN
SELECT * from index_test where id=1 or name='a';
#name字段没有索引，增加or后会不走索引

8.<>不等于导致索引失效

mysql5.7版本<>不等于会导致索引失效；但是在mysql8.0还是会走索引

不走索引场景解析：其实它是可以走索引的，如果返回结果集大于20%，那么它就不会走索引。

EXPLAIN
SELECT * from index_test where code=1;
#走索引

EXPLAIN
SELECT * from index_test where code<>1;
#5.7不走索引

9.范围查询数量过多导致索引失效

where条件大于1的情况下，因为扫描的行数比较多，还不如全表扫描；但是当ranks大于10的时候，数据比较少，回表成本也比较小，所以还是会选择走索引；在我们开发过程中如果遇到范围条件，尽可能的去精确一点