我们在平时的SQL书写时,join是我们sql中再平常不过的连表操作了,当我们要查询的数据来源于多张表中,我们需要通过连接条件来关联多张表,话不多说,结合图来解释
1 首先我们创建两张表
粉丝表
CREATE TABLE `fans` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`start_id` int(11) NULL DEFAULT NULL COMMENT '球星id',
`name` varchar(255) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '姓名',
`sex` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '性别',
`age` int(11) NULL DEFAULT NULL COMMENT '年龄',
`area` varchar(255) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '地区(省份)',
PRIMARY KEY (`id`) USING BTREE
)
球星表
CREATE TABLE `stars` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`s_name` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
`sex` tinyint(4) NULL DEFAULT NULL,
`city` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
`rigist_time` date NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
)
分别往两张表里插入一些测试数据
粉丝表
球星表
使用left join 来连接查询粉丝喜欢的球星时,我们知道,left左边的表作为驱动表,也就是我们常说的主表,此时的查询结果
SELECT * from stars a LEFT JOIN fans b ON a.id = b.start_id
我们EXPLAIN分析看一下它的执行计划
可以发现:粉丝表和球星表两张表都是走的全表扫描,毫无疑问,在稍微大数据量一点的情况下,这种查询效率无疑是最低的,我们球星表stars的id是主键id,为什么还是走的全表扫描呢?其实这就涉及我们拿哪张表来作为驱动表(主表)了,上述的sql中,我们使用的是球星表stars去 join 粉丝表fans, 也就是用球星表stars作为驱动表,查询粉丝表里start_id = 球星表的id 这一条件时,由于粉丝表的start_id字段没有建立索引,所以只有粉丝表只能走全表扫描,而球星stars表作为主表,无论如何都会全表扫描(没加where条件时),那我们反过来查看一下,当使用粉丝表fans作为驱动表连接球星表stars时的执行计划呢?
可以看到,这回球星表stars走了索引,正如我们上面所解释的,当我们使用粉丝表fans作为驱动表时,查询球星表stars里id = 粉丝表的start_id时,由于球星表的id字段是主键索引,所以执行计划里可以看到是走了索引查询了的,那么毫无疑问,这样的查询效率会高上许多。
那么是不是我们只要给粉丝表的start_id字段加上索引后,当我们使用粉丝表作为驱动表那么就一定会走索引了呢?我们不妨来试一下
CREATE INDEX start_id_index ON fans(start_id)
我们可以看一下此时粉丝表的索引信息
可以看到此时粉丝表start_id是建立了一个普通索引,我们再来执行一下查询计划
我们发现type依然还是ALL,这是为什么呢?仔细观察我们可以发现,执行计划里粉丝表中possible_key里有我们刚刚新建的start_id索引列,而possible_key的意思是可能会用到的索引,但为什么没有用到呢?其实是因为我们粉丝表中的数据量太少了,我们之前为了测试方便只向粉丝表中添加了6条数据,在MySQL中有查询优化器,它会选择出执行成本最低的执行计划,由于数据量过少,优化器通过比较发现走全表的成本更低,所以采用了全表扫描,我们可以来验证一下:
采用存储过程向粉丝表添加100条数据
CREATE DEFINER=`root`@`localhost` PROCEDURE `demo_5`(in param int)
begin
while param < 100 do
insert into fans(start_id,name,sex,age,area) values(1003,'阿飞',1,19,'湖北');
set param=param+1;
end while;
end
再来查看一下使用球星表作为驱动表时,粉丝表的start_id有没有走索引
果然,这时候走了索引。
在没有where条件过滤时,驱动表做全表扫描,被驱动表走索引扫描,假设驱动表示M行,被驱动表示M行,那么时间复杂度就是NlognM,n为多路搜索树的阶数。接下来比较一下大表join小表和小表join大表的差别,假设小表a1000行,大表b10000行,为方便计算,取搜索树阶数为10,小表join大表复杂度为1000 * lg10000 = 4000,而大表join小表复杂度为10000 * lg1000 = 30000,这个复杂度的差别还是非常明显的,因此表关联需要用小表join大表
总结:在联表查询时,尽量为被驱动表的条件列加上索引