建表
DROP TABLE IF EXISTS `tb_users`;
CREATE TABLE `tb_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `username`(`username`, `password`, `email`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1004 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
从上面的建表语句中我们可以看到,tb_users 有五个字段 两个索引分别是主键索引id,和联合索引 (username, password, email),非索引字段address。
先说结论
当要查询的字段以及查询条件都在索引里面,会查询索引表,当查询字段不在索引里面,且查询条件不符合索引的最左前缀原则,不使用索引 ,查询字段以及条件字段(覆盖索引)都在索引字段里面,会使用索引。
实验
-
- 查询字段和条件字段都在索引中
EXPLAIN SELECT a.id FROM tb_users a WHERE a.username = '李四' and a.password= '1233' and a.email = '1233@qq.com';
-
2 查询条件和索引条件都在索引中但是查询字段调换了顺序
EXPLAIN SELECT a.id FROM tb_users a WHERE a.email = '1233@qq.com' and a.password= '1233' and a.username = '李四' ;
-
- 覆盖索引,查询字段和索引字段都在索引中国但是条件字段并没有从username开始
EXPLAIN SELECT a.id FROM tb_users a WHERE a.PASSWORd = '1233' and a.email = '1233@qq.com';
-
- 与上述3相似,覆盖索引
EXPLAIN SELECT a.id FROM tb_users a WHERE a.email = '1233@qq.com';
-
- 覆盖索引,查询索引中的列,但是查询条件不符合最左前缀原则
EXPLAIN SELECT a.username FROM tb_users a WHERE a.password= '1233' and a.email = '1233@qq.com';
-
- 查询不在索引中的列,且查询条件不符合最左前缀原则
EXPLAIN SELECT a.address FROM tb_users a WHERE a.email = '1233@qq.com';
-
- 查询不在索引的列,但是查询条件符合最左前缀原则
EXPLAIN SELECT a.address FROM tb_users a WHERE a.username = '李四';
上述除第六条没有使用索引表外,其他都使用了索引。