MySQL底层索引优化

MySQL索引调优原则

• 禁止join超过三张表
• 联合索引左前缀法则
• 禁止回表查询

MySQL查询EXPLAIN-type级别

• All 全表扫描，最原生的sql状态，有很大的优化空间
• index 另一种形式的全表扫描，扫描顺序是按照索引的顺序，扫描根据索引然后回表查询数据
• range 指的是有范围的全表扫描，相对于index有了范围，要优于index
• ref 查询条件使用了索引，而没用主键和unique，虽然用烂了索引但索引列的值不唯一，有重复
• ref_eq 比ref好在它会知道这种类型查找结果集只有一个，要么主键索引要么唯一索引
• const 常量，此时索引几乎接近最优，剩余优化取决于优化器。

MySQL-EXPLAIN-Extra

using index ：用覆盖索引的时候出现

using where：使用索引的情况下，需要回表查询所需的数据

using index condition：使用了索引，但需要回表查询数据

using index & using where：使用了索引，但需要的数据都在索引列中能找到，所以不需要回表

//普通索引,xxx标识字段名
alter table table_name add index idx_xxx (xxx) ;
//唯一索引,xxx标识字段名
alter table table_name add unique (xxx) ;
//主键索引,xxx标识字段名
alter table table_name add primary key (xxx) ;
//删除索引
drop index index_name on talbe_name

新建两张表,用户表和用户班级表，一对多关联关系，通过user的id关联

表结构DDL如下

用户表

CREATE TABLE `test_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `addr` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

班级表

CREATE TABLE `user_class` (
  `class_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `class_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

添加测试数据后进行一次关联查询

EXPLAIN SELECT a.user_id,a.user_name,a.addr from test_user a left join user_class b on a.user_id = b.user_id;

查询结果

看上去没啥问题，但是用其实效率极低

使用EXPLAIN工具查看

发现这次sql发起了两次查询,且都是全表扫描的，说明还有很大的优化空间。

先给关联表，关联的user_id主键添加一个普通索引

alter table user_class add index idx_user_id (user_id);

再次用explain查看

第二次查询已经从全表的All变为ref，说明已经走了索引

test_user表结构修改为

CREATE TABLE `test_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `addr` varchar(255) DEFAULT NULL,
  `age` int(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `idx_add_age_sex` (`addr`,`age`,`sex`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

对于单表查询

EXPLAIN select a.addr,a.age,a.sex from test_user a

可以看到返回这三个条件，走的是全表扫描，接下来进行优化

联合索引的索引失效问题

单表的联合索引进行优化

对user表添加一个联合索引

alter table test_user add index `idx_add_age_sex` (`addr`,`age`,`sex`)

添加后再次查询

发现走到了index，使用到了索引的全表扫描

如果带上where条件后

EXPLAIN select a.addr,a.age,a.sex from test_user a where a.addr = '浙江杭州' and a.age=22 and a.sex=0

发现没啥问题，确实走到了我们的联合索引进行查询

但如果改为

EXPLAIN select a.addr,a.age,a.sex from test_user a where a.age=22 and a.sex=0

此时利用索引扫描了全表，效率明显降低

如果调换顺序呢

EXPLAIN select a.addr,a.age,a.sex from test_user a where a.age=22 and a.addr = '浙江杭州' and a.sex=0

可以看到又走了索引

如果去掉联合索引中间的呢？

EXPLAIN select a.addr,a.age,a.sex from test_user a where a.addr = '浙江杭州' and a.sex=0

可以看到依旧走了联合索引

由此可以得出结论，联合索引查询时候，必须要有第一个索引作为where中的条件，左前缀原则的顺序，mysql的底层会进行优化调换顺序。

避免回表查询

对比以下两段sql

EXPLAIN select a.addr,a.age,a.sex,a.user_name from test_user a where a.addr = '浙江杭州' and a.age=22 and a.sex=0

EXPLAIN select a.addr,a.age,a.sex from test_user a where a.addr = '浙江杭州' and a.age=22 and a.sex=0

第一段SQL的Extra说明了底层进行了回表查询，也就是说需要返回的数据不在索引可以的到的数据范围内，所以又回表查询了一次才得到所有需要的数据