mysql 索引失效 -- 数据区分度不足30%

感谢 https://blog.csdn.net/kelindame/article/details/56668474 文章,感谢.

理论背景1:

1.当表的索引被查询，会使用最好的索引，除非优化器使用全表扫描更有效。
2.优化器优化成全表扫描取决与使用最好索引查出来的数据是否超过表的30%的数据。

理论背景2:

优化器更加复杂，其估计基于其他因素，表的大小，行数和I/O块的大小。

下面进行试验

1.准备数据

use muke;                       /* 使用muke这个database */

drop table if exists t1;        /* 如果表t1存在则删除表t1 */

CREATE TABLE `t1` (             /* 创建表t1 */
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(20) DEFAULT NULL,
  `b` int(20) DEFAULT NULL,
  `c` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,  
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`) USING BTREE,
  KEY `idx_b` (`b`) USING BTREE,
  KEY `idx_c` (`c`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1，则删除 */
delimiter ;;
create procedure insert_t1()        /* 创建存储过程insert_t1 */
begin
  declare i int;                    /* 声明变量i */
  set i=1;                          /* 设置i的初始值为1 */
  while(i<=10000)do                 /* 对满足i<=10000的值进行while循环 */
    insert into t1(a,b) values(i,i);  /* 写入表t1中a、b两个字段，值都为i当前的值 */
    set i=i+1;                        /* 将i加1 */
  end while;
end;;
delimiter ;
call insert_t1();                    /* 运行存储过程insert_t1 */

update t1 set c = '2019-05-22 00:00:00';  /* 更新表t1的c字段，值都为'2019-05-22 00:00:00' */
update t1 set c = '2019-05-21 00:00:00' where id=10000;	 /* 将id为10000的行的c字段改为与其它行都不一样的数据，以便后面实验使用 */

注意:我上面插入的数据,9999条是 2019-05-22 00:00:00,只有一条是2019-05-21 00:00:00’

2 走索引的情况

EXPLAIN SELECT * FROM t1 WHERE c>='2019-05-21 00:00:00' AND c<='2019-05-21 23:59:59';

3 不走索引的情况

EXPLAIN SELECT * FROM t1 WHERE c>='2019-05-21 00:00:00' AND c<='2019-05-23 23:59:59';

原因：
使用count整张表的数据，和条件的数据发现已经超过30%,，所以失效。
在建立索引的时候，要根据列基数来建立。列基数=列中不同的数据/除以总数据。
越接近1表示重复数据越少，越适合建立索引。

对以后工作的提示：
根据上面的理论，时间断的范围通过订单号(区分符足够高,还有身份证号,手机号这些)查询。因为订单号可以区分时间，并且列基数=1；

下期做一个 对索引字段做函数操作时,优化器会放弃使用索引吗?

不走索引的5个原因

1.函数操作

2.隐式转换

3.模糊查询

4.范围查询

5.计算操作