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 '原創(chuàng)文章',
  `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;
// 創(chuàng)建組合索引
ALTER TABLE `user` ADD INDEX idx_username_password_user_rank (`username`,`password`,`user_rank`)

select * from table where password= “xxx” and user_rank = 1

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

// 命中索引跟順序無關(guān)
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