MySQL索引命中與失效程式碼實現

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(5) NOT NULL AUTO_INCREMENT COMMENT '使用者id',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '使用者名稱',
  `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '使用者密碼',
  `create_essay` int(5) NOT NULL COMMENT '原創文章',
  `user_visited` int(10) NOT NULL COMMENT '被存取量',
  `user_rank` int(5) NOT NULL COMMENT '使用者排名',
  `perms` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `nickname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '使用者暱稱',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 116856 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

// 建立組合索引
ALTER TABLE `user` ADD INDEX idx_username_password_user_rank (`username`,`password`,`user_rank`)

idx_username_password_user_rank(`username`,`password`,`user_rank`)

// 命中索引跟順序無關
explain SELECT * from `user` where username =  "liuxiangcheng" and password = "515239" and user_rank = 1
explain SELECT * from `user` where user_rank = 1 and username =  "liuxiangcheng" and password = "515239"  
explain SELECT * from `user` where user_rank = 1 and password = "515239" and username =  "liuxiangcheng"

// 去掉大哥
explain SELECT * from `user` where password = "515239" and user_rank = 1

drop index idx_username_password_user_rank on `user`

alter table `user` ADD UNIQUE key (`username`)

explain SELECT * from `user` where username= 'user110819'

explain SELECT * from `user` where concat(username,'')= 'user110819'

explain SELECT * from `user` where username like '%user11081'

explain SELECT * from `user` where username like 'user11081%'

explain SELECT * from `user` where username is not null

explain SELECT * from `user` where username = "liuxiangcheng" and password = "515239"

explain SELECT * from `user` where username = "liuxiangcheng" or user_rank = 1

alter table `user` ADD index (`user_rank`)

explain SELECT * from `user` where username = "liuxiangcheng" or user_rank = 1

explain SELECT * from `user` where   user_rank != 1

or 

explain SELECT * from `user` where   user_rank <> 1

explain SELECT * from `user` where user_rank >(<,>=,<=) 1

// password is varchar type

explain SELECT * from `user` where password = 515239

explain SELECT * from `user` where password = '515239'

explain SELECT * from `user` where password = "515239"

// user_rank is int type

explain SELECT * from `user` where user_rank = "1"

explain SELECT * from `user` where user_rank = '1'

explain SELECT * from `user` where user_rank = 1