MySql索引使用

建表

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。

先说结论

当要查询的字段以及查询条件都在索引里面，会查询索引表，当查询字段不在索引里面，且查询条件不符合索引的最左前缀原则，不使用索引 ，查询字段以及条件字段（覆盖索引）都在索引字段里面，会使用索引。

实验

• 1. 查询字段和条件字段都在索引中

  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 = '李四' ;
  

  

• 3. 覆盖索引，查询字段和索引字段都在索引中国但是条件字段并没有从username开始

  EXPLAIN SELECT a.id FROM tb_users a WHERE a.PASSWORd = '1233' and a.email = '1233@qq.com';
  

  

• 4. 与上述3相似，覆盖索引

  EXPLAIN SELECT a.id FROM tb_users a WHERE a.email = '1233@qq.com';
  

  

• 5. 覆盖索引，查询索引中的列，但是查询条件不符合最左前缀原则

  EXPLAIN SELECT a.username FROM tb_users a WHERE a.password= '1233' and a.email = '1233@qq.com';
  

  

• 6. 查询不在索引中的列，且查询条件不符合最左前缀原则

  EXPLAIN SELECT a.address FROM tb_users a WHERE a.email = '1233@qq.com';
  

  

• 7. 查询不在索引的列，但是查询条件符合最左前缀原则

  EXPLAIN SELECT a.address FROM tb_users a WHERE a.username = '李四';
  

  
  上述除第六条没有使用索引表外，其他都使用了索引。