MySql性能调优02-[索引优化]

MySQL优化

• 测试用的表和数据

DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int NULL DEFAULT NULL COMMENT '年龄',
  `position` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '职位',
  `hire_time` datetime NULL DEFAULT NULL COMMENT '入职时间',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
  `salary` decimal(10, 2) NULL DEFAULT NULL COMMENT '薪水',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name_age_position`(`name`, `age`, `position`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'John Doe', 25, 'Manager', '2024-07-12 12:14:28', '撒嘎嘎', 5000.00);
INSERT INTO `employees` VALUES (2, 'Jane Smith', 56, 'Engineer', '2024-07-02 12:14:31', '对对对', 4500.00);
INSERT INTO `employees` VALUES (3, 'Bob Johnson', 66, 'Analyst', '2024-06-12 12:14:36', '大概', 4000.00);
INSERT INTO `employees` VALUES (4, 'Alice Brown', 77, 'Marketing Specialist', '2024-07-20 12:14:42', '撒嘎嘎', 3500.00);
INSERT INTO `employees` VALUES (5, 'Mike Davis', 88, 'Sales Representative', '2024-07-28 12:14:47', '谁大纲', 3000.00);
INSERT INTO `employees` VALUES (6, 'Sarah Harris', 99, 'HR Representative', '2024-07-19 12:14:53', '撒呃呃我', 2500.00);
INSERT INTO `employees` VALUES (7, 'Alex Thompson', 102, 'Technician', '2024-08-17 12:14:57', '呵呵呵', 2200.00);
INSERT INTO `employees` VALUES (8, 'Emily Davis', 103, 'Intern', '2024-09-01 12:15:01', '规范国家', 2000.00);
INSERT INTO `employees` VALUES (9, 'David Lee', 56, 'Contractor', '2024-07-08 12:15:09', '一体同', 2500.00);
INSERT INTO `employees` VALUES (10, 'Olivia Martinez', 55, ' Temp', '2024-08-22 12:15:49', '其二切换', 2250.00);

SET FOREIGN_KEY_CHECKS = 1;

索引优化-全值匹配

要查询的列都在联合索引中，这样子就没有回表

索引优化-最左前缀原则

不符合最左前缀原则，索引失效

索引优化-索引列上少计算

索引列进行计算会让索引失去有序性(无法进行二分查找)

索引优化-范围后面全失效

索引优化-覆盖索引不写星

索引优化-不等空值还有or,索引失效要少用

索引优化-like百分写最右

使用前模糊，%xxx会导致索引失效

索引优化-引号不可丢

字符串不加单引号也会导致索引失效(本质是索引进行了类型转换导致索引无序)

索引优化-范围查询优化

索引优化-总结