MySQL--索引优化实战篇（1）

前言：

我们常说的SQL优化，简单来说就是索引优化，通过合理创建索引，调整SQL语法等，来提升查询效率，想要进行SQL优化，就必须知道索引的原理，而且能够看懂SQL的执行计划。

MySQL–索引底层数据结构详解
MySQL–索引类型详解
MySQL–explain执行计划详解

准备数据：

#创建user表
CREATE TABLE `user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户姓名',
  `user_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户工号',
  `age` tinyint DEFAULT NULL COMMENT '用户年龄',
  `address` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户地址',
  `hobby` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户爱好',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

#准备数据
INSERT INTO `user`(id, user_name, user_code, age, address, hobby)VALUES(1, '张三', 'TC-00000001', 25, '湖北', '篮球');
INSERT INTO `user`(id, user_name, user_code, age, address, hobby)VALUES(2, '李四', 'TC-00000002', 26, '湖南', '足球');
INSERT INTO `user`(id, user_name, user_code, age, address, hobby)VALUES(3, '王五', 'TC-00000003', 23, '广东', '电影');

#创建索引
create index  index_name on user(user_name);
#创建 user_name,age,address联合索引
create index  index_name_age_address on user(user_name,age,address);

查看user表中的所有索引：

show index from user;

结果：

案例一：

explain select * from user where user_name='张三';

执行计划：

分析执行计划：

• 我们知道可能用到的索引是 index_name，index_name_age_address，实际我们用到的索引是 index_name，没有问题。
• 再来看看key_len：202，我们的user_name 字段是 varchar(50) utf8mb4编码，50 * 4 + 2 = 202没有问题，确实是使用了index_name索引。
• rows：1，张三只有一条数据，没有问题。
• filtered：100，经过过滤后只有一条，且只需要一条，没有问题。

案例二：

explain select * from user where user_name='张三' and age=25;

执行计划：

分析执行计划：

• 我们知道可能用到的索引是 index_name，index_name_age_address，这次我们用到的索引还是 index_name，为什么没有使用联合索引 index_name_age_address ，这里是因为表中只有三条数据，而user_name 等于 张三 的只有一条，使用 index_name 结果就已经确定，就没必要使用 联合索引了。
• 再来看看key_len：202，我们的user_name 字段是 varchar(50) utf8mb4编码，50 * 4 + 2 = 202没有问题，确实是使用了index_name索引。
• rows：1，张三只有一条数据，没有问题。
• filtered：33.33，经过过滤后只有一条，且只需要一条，为什么是33.33呢？这个我也没有明白，还需要继续探讨。

案例三：

#再次拆入一条用户数据
INSERT INTO `user`(id, user_name, user_code, age, address, hobby)VALUES(1, '张三', 'TC-00000004', 28, '广东', '足球');

user 表数据情况：

再次执行案例二同样的SQL：

explain select * from user where user_name='张三' and age=25;

执行计划：

分析执行计划：

• 我们知道可能用到的索引是 index_name，index_name_age_address，这次我们用到的索引是 index_name_age_address，同样的SQL在数据情况发生改变的时候使用到的索引不一样，现在是因为user_name 等于 张三 的有两条数据，再使用 index_name 索引就无法准确定位到数据了，测试MySQL就自动使用了 index_name_age_address 联合索引。
• 再来看看key_len：204，我们的user_name 字段是 varchar(50) utf8mb4编码，50 * 4 + 2 = 202，age字段 tinyint 类型占用一个字节，且age字段可以为空，再占用一个字节，202没有问题，确实是使用了index_name索引。
• rows：1，user_name 为张三， age=25的只有一条数据，没有问题。
• filtered：100，经过过滤后只有一条，且只需要一条，没有问题。

总结：同样的SQL在不同的在不同数据的情况下会使用不同的索引，因此我们更要谨慎的设计索引，写完SQL一定要看执行计划。

如有不正确的地方请各位指出纠正。