MySql 各类查询 索引是否生效测试

前言：在网上关于MySql索引是否生效的文章有许多，但都不是很全面的整理，作为一个开发人员也一直在意这块内容，自己动手整理一下吧 。如有遗漏或错误请大家不吝赐教。

 

1. 准备一张表（注意：a,b,c三个字段创建的是联合索引，并没有单独创建普通索引，d字段创建的是普通索引）

CREATE TABLE `test` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `a` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'a',
  `b` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'b',
  `c` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'c',
  `d` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'd',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_a_b_c` (`a`,`b`,`c`),
  KEY `idx_d` (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

2. 写入数据的存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `test`(IN num INT)
BEGIN
	SET @count=0;
	WHILE @count < num DO
		INSERT INTO test (a,b,c,d) VALUES(CONCAT('a',@count), CONCAT('b',@count), CONCAT('c',@count), CONCAT('d',@count));
		SET @count = @count + 1;
	END WHILE;
	# 数据插入完成后，查看表中总记录数
	SELECT COUNT(id) FROM test;
    END$$

DELIMITER ;

3. 调用存储过程写入100W数据

CALL test(1000000);

4. 开始测试（关于执行计划explain 请自行学习，本文不做讲解，只需关注`key`字段即可）

关键字`AND` 测试

(1) 查询条件为联合索引中的`a`字段

EXPLAIN SELECT * FROM test WHERE a = "a1"; 

结论：使用索引 idx_a_b_c 

 

(2) 查询条件为联合索引中的`a`,`b`字段

EXPLAIN SELECT * FROM test WHERE a = "a1" AND b = "b1";

结论：使用索引 idx_a_b_c 

 

(3) 查询条件为联合索引中的`a`,`b`,`c`字段

EXPLAIN SELECT * FROM test WHERE a = "a1" AND b = "b1" AND c = "c1";

结论：使用索引 idx_a_b_c 

 

(4) 查询条件为联合索引中的`a`,`b`,`c`字段以及普通索引`d`字段

EXPLAIN SELECT * FROM test WHERE a = "a1" AND b = "b1" AND c = "c1" AND d = "d1";

结论：使用索引 idx_a_b_c 

 

（5）查询条件为联合索引中的`a`,`c`字段 （疑问：如果遵循联合索引左侧匹配原则的话，那么不应该使用到索引才对）

EXPLAIN SELECT * FROM test WHERE a = "a1" AND c = "c1" ;

结论：使用索引 idx_a_b_c 

 

(6) 查询条件为联合索引中的`b`,`c`字段

EXPLAIN SELECT * FROM test WHERE b = "b1" AND c = "c1";

结论：不使用索引

 

(7) 查询条件为联合索引中的`b`或`c`字段

EXPLAIN SELECT * FROM test WHERE b = "b1";

EXPLAIN SELECT * FROM test WHERE c = "c1";

 结论：不使用索引

 

(8) 查询条件为联合索引中的`a`字段和普通索引`d`字段

EXPLAIN SELECT * FROM test WHERE a = "a1" AND d = "d1";

结论：使用索引 idx_a_b_c 

 

(9) 查询条件为联合索引中的`b`字段或`c`字段和普通索引`d`字段

EXPLAIN SELECT * FROM test WHERE b = "b1" AND d = "d1";

EXPLAIN SELECT * FROM test WHERE c = "c1" AND d = "d1";

结论：使用索引 idx_d

 

关键字`OR` 测试

(1) 查询条件为联合索引中的`a`,`b`,`c`字段,查询结果包含字段`d`

EXPLAIN SELECT * FROM test WHERE a = "a1" OR b = "b1" OR c = "c1";

 结论：不使用索引 （全表扫描），在100W数据的情况下查询时间  1000ms左右

 

(2) 查询条件为联合索引中的`a`,`b`,`c`字段,查询结果不包含字段`d`

EXPLAIN SELECT a, b, c FROM test WHERE a = "a1" OR b = "b1" OR c = "c1";

结论：全索引扫描，在100W数据的情况下查询时间  800ms左右

 

(3) 查询条件为联合索引中的`a`字段

EXPLAIN SELECT * FROM test WHERE a = "a1" OR a = "a2";

结论：使用索引 idx_a_b_c 

 

(4) 查询条件为联合索引中的`a`字段和普通索引`d`字段

EXPLAIN SELECT * FROM test WHERE a = "a1" OR d = "d1";

结论：使用索引 idx_a_b_c,idx_d

 

(5) 查询条件为联合索引中的`b`字段或`c`字段和普通索引`d`字段

EXPLAIN SELECT * FROM test WHERE b = "b1" OR d = "d1";

EXPLAIN SELECT d FROM test WHERE c = "c1" OR d = "d1";

结论：不使用索引，无论是`*`查询还是精确查询都是全表扫描

 

(6) 查询条件为联合索引中的`a`,`b`,`c`字段和普通索引`d`字段

EXPLAIN SELECT d FROM test WHERE a = "a1" OR b = "b1" OR c = "c1" OR d = "d1";

结论：不使用索引，无论是`*`查询还是精确查询都是全表扫描

 

(7) 查询条件为普通索引`d`字段

EXPLAIN SELECT * FROM test WHERE d = "d1" OR d = "d2";

结论：使用索引 idx_d

 

关键字`IN` 测试（和AND关键字测试结果一样）

(1) 查询条件为联合索引中的`a`,`b`,`c`字段

EXPLAIN SELECT * FROM test WHERE a IN ('a1','a2','a3') AND b IN ('b1','b2','b3') AND c IN ('c1','c2','c3');

结论：使用索引 idx_a_b_c

 

(2) 查询条件为联合索引中的`a`,`c`字段和普通索引`d`字段

EXPLAIN SELECT * FROM test WHERE a IN ('a1','a2','a3') AND c IN ('c1','c2','c3') AND d IN ('d1','d2','d3');

结论：使用索引 idx_a_b_c

 

(3) 查询条件为普通索引`d`字段

EXPLAIN SELECT * FROM test WHERE d IN ('d1','d2','d3');

结论：使用索引 idx_d

 

关键字`LIKE` 测试

(1) 查询条件为联合索引中的`a`字段,查询结果包含普通索引`d`字段

EXPLAIN SELECT * FROM test WHERE a LIKE "%a1%"

 结论：不使用索引 （全表扫描）

 

(2) 查询条件为联合索引中的`a`字段,查询结果不包含普通索引`d`字段

EXPLAIN SELECT a, b, c FROM test WHERE a LIKE "%a1%";

结论：全索引扫描

 

(3)  查询条件为联合索引中的`a`字段,查询结果不包含普通索引`d`字段，且LIKE条件去除前面的%

EXPLAIN SELECT a,b,c FROM test WHERE a LIKE "a1%" ;

结论：使用索引 idx_a_b_c

 

(4)  查询条件为联合索引中的`a`字段,查询结果不包含普通索引`d`字段，且LIKE条件去除后面的%

EXPLAIN SELECT a,b,c FROM test WHERE a LIKE "%a1" ;

结论：全索引扫描

 

(5) 查询条件为普通索引`d`字段，查询结果包含`a`,`b`,`c`字段

EXPLAIN SELECT * FROM test WHERE d LIKE "%d1%" ;

 结论：不使用索引 （全表扫描）

 

(6) 查询条件为普通索引`d`字段，查询结果包含`a`,`b`,`c`字段，且LIKE条件去除前面的%

EXPLAIN SELECT * FROM test WHERE d LIKE "d1%" ;

结论：使用索引 idx_d

 

(7) 查询条件为普通索引`d`字段，查询结果不包含`a`,`b`,`c`字段

EXPLAIN SELECT d FROM test WHERE d LIKE "%d1%" ;

结论：全索引扫描

 

(8) 查询条件为普通索引`d`字段，查询结果不包含`a`,`b`,`c`字段，且LIKE条件去除前面的%

EXPLAIN SELECT d FROM test WHERE d LIKE "d1%" ;

结论：使用索引 idx_d

 

(9) 查询条件为普通索引`d`字段，查询结果不包含`a`,`b`,`c`字段，且LIKE条件去除后面的%

EXPLAIN SELECT d FROM test WHERE d LIKE "%d1" ;

结论：全索引扫描

 

关键字`<>` 测试

(1) 查询条件为联合索引中的`a`字段，包含普通索引`d`字段

EXPLAIN SELECT * FROM test WHERE a <> "a1";

 结论：不使用索引 （全表扫描）

 

(2) 查询条件为联合索引中的`a`字段，不包含普通索引`d`字段

EXPLAIN SELECT a,b,c FROM test WHERE a <> "a1";

结论：使用索引 idx_a_b_c

 

(3) 查询条件为普通索引中的`d`字段，包含`a`,`b`,`c`字段

EXPLAIN SELECT * FROM test WHERE d <> "d1";

 结论：不使用索引 （全表扫描）

 

(4) 查询条件为普通索引中的`d`字段，不包含`a`,`b`,`c`字段

EXPLAIN SELECT d FROM test WHERE d <> "d1";

结论：使用索引 idx_d

未完待续。。。