这是项目中遇到的问题,为多表联合查询,我这边简单描述一下:
SQL语句是这样的:select * from A a left join B b on a.id = b.device_id left join C c on a.id = c.alarm_id ...... where (中间有查询条件,但是默认为空) order by a.alarm_time
这里我创建了3个索引,一个是a表的alarm_time:create index idx_A_alarm_time on A(alarm_time),因为d.device_id并不是主键,而且可以为null,因此也B表创建了一个索引,然后给C表的alarm_id也建立了一个索引
这时候有意思的就来了:
1.当where 条件里面有alarm_time查询的时候,这个查询会用到A表的索引
2.当where条件不在的时候,也就是说没有where条件的时候,这个查询就没有用到索引,显示的是Using filesort,这可是一个很严重的问题了
3.一开始我以为是关联查询的问题,于是测试了各种子查询,然后又遇到不同的坑,子查询虽然能避免上面的问题,但是我惊讶的发现这个子查询里面加order by和不加没有任何区别。也就是说mysql内部的子查询order by 失效了(问题是5.7之后,要解决只有加limit 限制),于是加了limit后还是会使用filesort,子查询等于没查,又回到起点
4.于是我测试了单张表,在单表下测试这个order by 有没有使用索引,这里我为了不泄漏数据,使用自己测试的数据,这里插入4条数据,用做测试足够了,写存储过程也麻烦。
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`descri` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '张三', '1', '1');
INSERT INTO `user` VALUES ('2', '李四', '2', '1');
INSERT INTO `user` VALUES ('3', '王五', '3', '1');
INSERT INTO `user` VALUES ('4', '码子', '4', '1');
5.在name字段创建索引
这是查看表存在的索引。
再运行这段代码explain select * from user order by name ,看看有没有使用到索引 ,出现filesort,并没有使用到索引
6.因此问题就很明白了,连表查询表示我不背这个锅,那么怎么解决呢?
我个人的解决方式是强制mysql使用索引
SQL语句:select * from user force index(idx_user_name),这样能够达到index级别,全表查询也算可以了