# MySQL索引失效总结

8,905 阅读3分钟

一、准备工作

  • 创建一张表 t_index ,脚本如下:
CREATE TABLE `t_index` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表记录标识号,数据库主键,不用于实际业务',
  `key1` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '字段1',
  `key2` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '字段2',
  `key3` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '字段3',
  `del_flag` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '删除标志。0未删除,1删除。',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录修改时间',
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='索引表';

二、普通索引

1.准备工作

  • 在字段 key1 上创建索引,脚本如下:
ALTER TABLE `t_index` ADD INDEX idx_key1(key1);

2.正常走索引

  • 查询脚本
SELECT * FROM `t_index` WHERE key1 = '1';
  • 查看执行计划
EXPLAIN SELECT * FROM `t_index` WHERE key1 = '1';
  • 结果显示走索引查询,如下图
    正常.png

3.如下场景会导致查询走全表查询,不走索引

(1)查询条件使用不等式

  • 查询脚本
SELECT * FROM `t_index` WHERE key1 <> '1';
  • 查看执行计划,结果显示全表扫描,如下图

    不等式.png

  • 总结:不等式 <>!= 会导致索引失效

(2)查询条件类型不一致

  • 查询脚本
SELECT * FROM `t_index` WHERE key1 = 1;
  • 查看执行计划,结果显示全表扫描,如下图

    类型不一致.png

  • 总结:字段 key1 为字符串,传入的值为数字类型,会导致索引失效

(3)查询条件使用函数计算

  • 查询脚本
SELECT * FROM `t_index` WHERE key1 + 1 = 1;
SELECT * FROM `t_index` WHERE CHAR_LENGTH(key1) = 1;
  • 查看执行计划,结果显示全表扫描,如下图

    函数计算.png

  • 总结:函数计算 x+1x-1CHAR_LENGTH(x) 等会导致索引失效

(4)模糊查询

  • 查询脚本
SELECT * FROM `t_index` WHERE key1 LIKE  '3';
SELECT * FROM `t_index` WHERE key1 LIKE  '%3';
SELECT * FROM `t_index` WHERE key1 LIKE  '3%';
  • 查看执行计划,如下图

    模糊查询1.png
    模糊查询2.png
    模糊查询3.png

  • 总结:模糊查询查询条件前缀模糊不会走索引

三、复合索引

1.准备工作

  • 删除刚才的索引,在字段 key1, key2, key3 上创建复合索引,脚本如下:
DROP INDEX idx_key1 ON `t_index`;
ALTER TABLE `t_index` ADD INDEX idx_key123(key1, key2, key3);

2.正常走索引

  • 先看下正常走索引的查询脚本
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 = '3';
  • 查看执行计划
EXPLAIN SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 = '3';
  • 结果显示走索引查询,如下图
    复合索引正常.png

3.如下场景会导致查询走全表查询,不走索引

(1)查询条件使用不等式

  • 查询脚本,只要有一个条件含有不等式,都不会走索引
SELECT * FROM `t_index` WHERE key1 <> '1' AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 <> '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 <> '3';
  • 查看执行计划,结果显示全表扫描,三种情况结果一样,如下图

    复合索引不等式.png

  • 总结:逻辑普通索引

(2)查询条件类型不一致

  • 查询脚本
SELECT * FROM `t_index` WHERE key1 = 1 AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = 2 AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 = 3;
  • 查看执行计划,结果显示(第一个参数类型不一致走全表扫描,第二个参数类型不一致,索引仅仅能使用第一列,第三个参数类型不一致,索引能使用前两列),如下图

    复合索引类型不一致1.png
    复合索引类型不一致2.png
    复合索引类型不一致3.png

  • 总结:从第一个查询条件开始,第N个参数类型不一致,索引能使用前N-1列

(3)查询条件使用函数计算

  • 查询脚本
SELECT * FROM `t_index` WHERE key1 + 1 = '1' AND key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 + 1 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key1 = '1' AND key2 = '2' AND key3 + 1 = '3';
  • 查看执行计划,结果同上(第一个参数类型不一致走全表扫描,第二个参数类型不一致,索引仅仅能使用第一列,第三个参数类型不一致,索引能使用前两列),如下图

    复合索引函数计算1.png
    复合索引函数计算2.png
    复合索引函数计算3.png

  • 总结:逻辑普通索引

(4)不使用索引首列当查询条件

  • 查询脚本
SELECT * FROM `t_index` WHERE key2 = '2' AND key3 = '3';
SELECT * FROM `t_index` WHERE key2 = '2';
SELECT * FROM `t_index` WHERE key3 = '3';
  • 查看执行计划,结果显示(都不会走索引),三种情况结果一样,如下图

    复合索引不使用索引首列.png

  • 总结:查询条件不使用复合索引的首列,均会导致索引失效


本文完。