MySQL学习笔记-索引失效的情况

1.表结构

CREATE TABLE `t` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `word` varchar(16) NOT NULL DEFAULT '',
  `num` int unsigned NOT NULL DEFAULT '0',
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `word` (`word`),
  KEY `num` (`num`),
  KEY `date` (`date`)
) ENGINE=InnoDB

2. 插入实验数据

delimiter;
create procedure test_data()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into t(word,num,date) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))),i+1,"2020-02-23");
    set i=i+1;
  end while;
end;
delimiter;
call test_data();
update t set word='1000' where id=10

如下图所示：

3. 查询语句执行分析

3.1 查询一

EXPLAIN SELECT * FROM `t` WHERE word=1000

如下图所示：

Tips：可以从上图看出使用条件 word=1000 没有使用到索引。

3.2 查询二

EXPLAIN SELECT * FROM `t` WHERE word='1000'

如下图所示：

Tips：可以从上图看出使用条件 word='1000' 用到了索引。

3.3 查询三

EXPLAIN SELECT * FROM `t` WHERE num='15'

如下如所示：

Tips：可以从上图看出使用条件 num='15' 用到了索引。

3.4 查询四

EXPLAIN SELECT * FROM `t` WHERE num=15

如下如所示：

Tips：可以从上图看出使用条件 num=15 用到了索引。

3.5 查询五

EXPLAIN SELECT * FROM `t` WHERE month(date)=12

如下图所示：

Tips：可以从上图看出使用条件 month(date)=12 没有用到索引，索引加快查询速度是利用搜索树节点元素有序性，若字段用了函数，会破坏有序性。

3.6 原因分析

• 查询五 没有用到索引是因为对查询字段使用了 month 函数，而索引加速查询的原理本质是利用搜索树节点的有序性，若使用函数对字段处理，会破坏节点的有序性。
  
• 查询一 没有用到索引是因为查询条件是 word=1000，但 word 字段是 string 类型，给的条件 1000 是 int 类型，当 string 类型和 int 类型作比较时，默认是把 string 转化为 int 型，所以 word=1000 这个条件在做判断时，逻辑是 strToint(word)=1000(其中 strToint 表示把 word 字段的值转化为 int 型，这个函数是假设出来的类似逻辑的函数)，所以在这种情况下是不会用到索引的
  
• 查询三 用到索引，是因为条件 num='15' 中 num 字段是 int 类型，'15' 是 string 类型，当 string 类型和 int 类型作比较时，默认是把 string 转化为 int 型，条件 num='15' 做判断时，是把 string 类型的 '15' 转化为 int 类型的 15，因此会用到索引
  

Tips：另外类似的索引失效的情况还有 “当两表字符集不同时，连表查询时，索引涉及到字符转化的情况” 也可能会导致索引失效。