in后使用普通索引字段到底是否会使用索引（2021-4-14）

1.建表语句以及数据

CREATE TABLE `mashibing`.`Untitled`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `id_status`(`status`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 66 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

除了主键索引外，还加了status字段的普通索引
表中先插入了14条记录（重要，此时数据量仅为14条！！！）

2.SQL语句及执行

1.  explain SELECT id,status,name FROM user where status in ('M','F');
2.  explain SELECT id,status,name FROM user where status in ('M','L','F');



3.   explain SELECT id,status FROM user where status in ('M','F');
4.   explain SELECT id,status FROM user where status in ('M','L','F');


5.  explain select id,name,status  from user where id in (1,4);
6.  explain select id,name,status  from user where id in (1,4,9);

explain结果
1，2条执行结果

3，4条执行结果

5，6条执行结果

对比结果
1. 第 1,2条语句没有使用索引，而下方第3,4条语句都使用了索引，焦点在于有没有select name字段，只要select name字段就无法使用status上的索引
2. 第5，6条语句使用主索引，未使用status的普通索引，仍旧可以使用in，会用到主键索引

此时我以为是in后使用普通索引无法使用索引检索，只能使用主键索引，百度了一下发现可能还与数据量有关，于是增加数据量再执行，加到65条数据

此时执行sql语句，3456条语句执行结果不变，看第1，2条

可以看到此时第1条使用了索引，但第2条仍旧未使用索引。
其可能是因为优化器在不同数据量下的优化选择方案：
数据量较小时，全表扫描（ALL）的成本低于普通索引范围查询（range），数据量较大时才会使用普通索引范围查询，
但如果是主键字段不管数据量大小都会使用主键索引范围查询；

3.验证

可以使用 show status like ‘last_query_cost’； 语句查看查询计划的成本，对比一下即可验证
设计对照：建立一个表user2，字段和user一样，但不给status建立普通索引，以 explain SELECT id,status,name FROM user where status in (‘M’,‘F’) 为测试语句；

14条数据时没有普通索引检索和有普通索引查询成本：

65条数据时没有普通索引检索和有普通索引查询成本：

数据量	是否使用索引	查询成本
14 条	否	3.799
14条	是	3.799
65条	否	13.999（修改）
65条	是	13.209

4.结论：

数据量小的时候，都不走索引，所以查询成本相同（不知道在这种情况下如何让数据库强行使用成本高的方案，所以没法测出小数据量时走索引的查询成本）
当数据量增大的时候，走索引的成本为13.209，不走索引的成本为13.999，所以此时走索引，所以in后普通索引到底会不会使用取决于数据量大小以及数据库的优化方案（如果哪位大佬知道其详细的成本计算方法，望告知一下）。

P.S.

更新：

1. 查询后可以使用 explain SELECT id,status,name FROM user force index(idx_status) where status in (‘M’,‘F’); 该语句强行指定索引（从该博客得知，点击跳转）

此时使用了索引，但少数据量时强行使用索引的成本13.209>>3.799,因为mysql是基于成本（CBU）的数据库，所以小数据量不会使用索引。

2.此时还可以看一下主键索引为何会使用 ，使用SELECT id,status,name FROM user ignore index(idx_status) where id in (1,4); 强行不使用索引（mysql强制索引和禁止某个索引）

PS：如果数据库为8.0版本，还可以使用隐藏索引特性
MySQL8.0版本特性

14条数据时：

使用ignore idex（pri）强行不使用主键索引：

65条数据时

使用ignore idex（pri）强行不使用主键索引

由于前面是用的对照组，但是没有设置索引毕竟是不同的表，可能会造成结果不准确，所以在知道了强制使用/不试用索引的用法后，重新测了一下，不使用普通索引的成本是13.999，而之前使用对照组（user2）是13.799（就是13.999，没有影响）

最终结果：就应该是13.999，前面建表user2少了条数据，只有64条，才会是13.799，65条是13.999，数据已修改。