mysql当查询结果集中的数据比例超过全表数据一定比例时索引失效

1. 当查询结果集中的数据行超过全表数据行的一定比例时索引失效

1.1. 准备模拟数据：

# 创建test表：
create table test
(
    name varchar(10) null,
    age  int(1)      null
);
# 给age列添加索引：
create index idx_age
    on test (age);
# 插入一些数据：
insert into test
values ('li11', 11),
       ('li12', 12),
       ('li13', 13),
       ('li14', 14),
       ('li15', 15),
       ('li16', 16),
       ('li16', 17),
       ('li16', 18);       

1.2. 问题复现：

注意：如下2条查询中的比例（所查询条数/全部数据条数），不代表mysql优化器是按照下面的2条语句中的比例进行判断是走全表扫描还是走索引！

# 当查询6条数据（共8条）时：执行explain后type：all（全表扫描）没有走索引！
explain select * from test where age between 11 and 16;
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
|1 |SIMPLE     |test |NULL      |ALL |idx_age      |NULL|NULL   |NULL|8   |75      |Using where|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+

# 当查询2条数据（共8条）时：执行explain后type:range, key:idx_age，即走了索引！
explain select * from test where age between 11 and 12;
+--+-----------+-----+----------+-----+-------------+-------+-------+----+----+--------+---------------------+
|id|select_type|table|partitions|type |possible_keys|key    |key_len|ref |rows|filtered|Extra                |
+--+-----------+-----+----------+-----+-------------+-------+-------+----+----+--------+---------------------+
|1 |SIMPLE     |test |NULL      |range|idx_age      |idx_age|5      |NULL|2   |100     |Using index condition|
+--+-----------+-----+----------+-----+-------------+-------+-------+----+----+--------+---------------------+

1.3. 结论：当查询结果集中的数据行超过全表数据行的一定比例时索引失效！

2. 参考：

MySQL带时间字段的范围查询不走索引了？