查询的时候,在数据量过多时出现索引失效的情况,请问是什么原因?
具体情况,mysql版本5.7.7,ad_stat_day表总数据在250W左右,day_time字段上有建立索引
-- 25号至30号总数据
mysql> select count(*) from ad_stat_day where dayTime BETWEEN '2017-10-25' and '2017-10-30';
+----------+
| count(*) |
+----------+
| 107063 |
+----------+
1 row in set
-- 只查id的情况下会走索引
mysql> EXPLAIN select id from ad_stat_day where dayTime BETWEEN '2017-10-25' and '2017-10-30';
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+--------+----------+--------------------------+
| 1 | SIMPLE | ad_stat_day | NULL | range | nk_day_time | nk_day_time | 4 | NULL | 189566 | 100 | Using where; Using index |
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+--------+----------+--------------------------+
1 row in set
-- 查id,fee就不走索引了
mysql> EXPLAIN select id,fee from ad_stat_day where dayTime BETWEEN '2017-10-25' and '2017-10-30';
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | ad_stat_day | NULL | ALL | nk_day_time | NULL | NULL | NULL | 646016 | 27.18 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set
--缩小条件范围,查询id,fee也会走索引
mysql> EXPLAIN select id,fee from ad_stat_day where dayTime BETWEEN '2017-10-27' and '2017-10-30';
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
| 1 | SIMPLE | ad_stat_day | NULL | range | nk_day_time | nk_day_time | 4 | NULL | 124092 | 100 | Using index condition; Using MRR |
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
1 row in set
求大佬告知下mysql是根据什么情况来选择使用索引和不使用索引的?
这次范围扫描数据(189566行)不到表总数的10%,而加上一个字段就成了(646016行)占到表总数的25%来了,这个646016行数据mysql是怎么算出来的?还有mysql有明确的配置来说明超过某个阀值(百分比)就不使用索引了吗?