MySql的索引小实验

索引会失效，无非就是mysql判断使用索引的成本可能会比全表扫描还要高，mysql判断的因素还是挺多的，所以想做些小实验记录一下。

以下测试都在mysql5.7和8.0测试过，整体结果一致，只是有些细节不一样。

1、建表

CREATE TABLE `user2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `extra` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

先只添加age和name字段单独的索引

alter table user2 add index `age`(`age`) USING BTREE;
alter table user2 add index `name`(`name`) USING BTREE;

添加10条数据，方便测试

 

2、测试

• 测试1

  1. EXPLAIN SELECT * FROM `user2` u ORDER BY u.age;

     结果是没有使用age索引来进行排序，type=ALL,extra=using filesort;

  2. EXPLAIN SELECT * FROM `user2` u WHERE age=1;

     结果是没有使用age索引来查询，type=ALL,extra=Using where;

  3. EXPLAIN SELECT * FROM `user2` u WHERE age=2;

     结果是使用了age来查询，type=ref

总结：可以看出来如果索引查询出来的结果集数量太多，mysql就不会使用该索引，mysql 5.7是大于总条数的一半就不会使用该索引，mysql8.0则将比例调高为大于等于总条数的4/5则不会使用索引，原因自然就是该语句每用索引查询出一条数据就要回表查询一次完整的数据，并且这个查询是随机IO,效率不如直接全表查询的顺序IO,可以想象一下一张表你使用索引查询还查询出来7、8成的数据，那这个索引也就没有意义了，所以索引设计时最好不用用重复数据较多的字段。

• 测试2

1. EXPLAIN SELECT id FROM `user2` u  ORDER BY u.age;

   结果是使用了age索引，type=index,extra=Using index;

2. EXPLAIN SELECT id FROM `user2` u WHERE u.age=1;

   结果是使用了age索引，type=ref,extra=using index;

3. EXPLAIN SELECT id FROM `user2`;

   结果是使用了age索引，type=index,extra=using index;

总结：如果能使用覆盖索引，就会直接去走索引，即使索引查询出来的条数很多。原因自然是该语句使用了覆盖索引，不需要回表，就没有io的问题，所以覆盖索引是个好东西。

• 测试3

1. EXPLAIN SELECT * FROM user2 u WHERE age=1 AND name='1';

   结果是没有使用索引，type=ALL

2. EXPLAIN SELECT * FROM user2 u WHERE age=2 AND name='2';

   结果是使用了age索引，type=ref

3. EXPLAIN SELECT * FROM user2 u WHERE age=1 AND name='2';

   结果是使用了name索引，type=ref

4. EXPLAIN SELECT * FROM user2 u WHERE age=2 AND name='1';

   结果是使用了age索引，type=ref

总结：这里与实验一得出的结果是一致的，只不过如果存在多个索引都满足查询的条数不会太多的话，则会选择靠前的索引，如果所有索引都查询出来的条数太多，则会直接全表扫描，不使用索引。不过一般这种情况都会直接建联合索引了，毕竟联合索引适用的场景更多。

• 测试4

1. EXPLAIN SELECT id FROM user2 WHERE age=1 AND name='1';

   结果是使用了覆盖索引，并且age和name的索引同时使用，最后对两边的结果去取交集，type=index_merge,extra=Using intersect(age,name);Using index;

2. UPDATE `user2` SET `age` = 1, `name` = '1' WHERE `id` = 7;
       UPDATE `user2` SET `age` = 1, `name` = '1' WHERE `id` = 8;#将查询出来的数据调至占总条数的8成
       EXPLAIN SELECT id FROM user2 u WHERE age=1 AND name='1';

   结果是使用了age索引，type=ref

3. UPDATE `user2` SET `age` = 1, `name` = '1' WHERE `id` = 9;
       UPDATE `user2` SET `age` = 1, `name` = '1' WHERE `id` = 10;#将查询出来的数据调至占总条数的10成
       EXPLAIN SELECT id FROM user2 u WHERE age=1 AND name='1';

   结果是没有使用索引，type=ALL

总结：当能使用覆盖索引，mysql会尽量取走索引，并不惜使用索引合并，但是当索引查询出来的条数太多时，也会选择回表操作，甚至直接全表扫描，不同版本下比例的标准可能不一样。

• 测试5

alter table user2 drop index `age`;
alter table user2 drop index `name`;
alter table user2 add index `age_name`(`age`,`name`) USING BTREE;

 先删除之前的索引，然后再单独添加一个联合索引

1. EXPLAIN SELECT * FROM user2 u WHERE age=1 AND name='1'

   结果是不使用索引，type=ALL

2. EXPLAIN SELECT * FROM user2 u WHERE age=2 AND name='2'

   结果是使用age_name联合索引，type=ref

总结：与测试1的结果大致相同，联合索引也是会受到索引查询出来结果数的影响

• 测试6

1. EXPLAIN SELECT id FROM user2 u WHERE age=1 AND name='1'

   结果是使用age_name的联合索引的覆盖索引，type=ref,extra=using index;

2. EXPLAIN SELECT id FROM user2;

   结果是使用age_name的联合索引的覆盖索引,type=index,extra=using index;

总结：与测试3结果大致相同，如果能使用覆盖索引，就会直接去走索引，即使索引查询出来的条数很多

• 测试7

1. EXPLAIN SELECT * FROM user2 u WHERE age=2 AND name like '%3';

   结果是使用了age_name的联合索引，并且使用了索引下推的优化(type=ref,extra=Using index condition)

总结：如果当前使用的联合索引不能使用覆盖索引，并且联合索引中有的字段不能用来搜索的话，则会启用索引下推来减少回表，因为索引下推目的是减少回表操作，在索引就进行筛选，避免先回表再进行筛选

• 测试8

1. EXPLAIN SELECT * FROM `user2` u WHERE age=2 ORDER BY name ASC

   结果是使用了age_name的联合索引，并且因为索引本身的顺序就是asc，索引直接使用索引的顺序即可排序(type=ref,extra=Using index condition),这里会出现Using index condition我是不明白为什么。

2. EXPLAIN SELECT * FROM `user2` u WHERE age=2 ORDER BY name DESC;

   结果是使用了age_name的联合索引，因为排序顺序与索引顺序刚好相反，mysql8.0会优化为从索引的尾部开始遍历(extra=Backward index scan)，mysql5.7则没有这个优化,而是要将结果倒序

3. EXPLAIN SELECT * FROM `user2` u WHERE age<2 ORDER BY name;

   结果是使用了age_name的联合索引，因为age使用的是范围查询，所以无法确保结果集的age都是相同的，所以不能直接使用索引的顺序作为排序结果，要自己排序(extra=Using filesort)

4. EXPLAIN SELECT * FROM `user2` u WHERE age=2 and extra=1 ORDER BY name ASC;

   结果是使用了age_name的联合索引，说明即使WHERE多出了extra这个条件，也能够使用索引，毕竟只要每筛选出一条age=2的数据就去回表获取整行数据，然后看看extra是不是等于1，是就拿，不是就丢弃即可

总结

通过这些实验，基本上就对mysql简单查询使用索引的方式有了大概的认知，自己设计索引时就能站在mysql的角度去思考问题。

---