SQL笛卡尔积查询与关联查询性能对比
首先声明一下,sql会用略懂,不是专家,以下内容均为工作经验,聊以抒情。
今天帮忙验证同事发布的端口时,查看了一下相关sql内容,发现其使用的sql语句会导致笛卡尔积现象,为了帮其讲解进行了如下分析:
student表:
teacher表:
course表:
student_course表:
与发现问题类似的SQL1查询语句:
SELECT d.st_name,d.class_id,d.st_id FROM course AS a, student_course AS b, teacher AS c, student AS d WHERE a.cu_id = b.cu_id AND b.st_id = d.st_id AND c.dep_id = d.dep_id AND a.cu_name = \'英语\';
采用内关联的SQL2语句:
SELECT student.st_name, student.class_id, student.st_id FROM course JOIN student_course USING ( cu_id ) JOIN student USING ( st_id ) JOIN teacher USING ( dep_id ) WHERE course.cu_name = \'英语\';
执行时间对比(已经多次验证):
SELECT d.st_name,d.class_id,d.st_id FROM course AS a, student_course AS b, teacher AS c, student AS d WHERE a.cu_id = b.cu_id AND b.st_id = d.st_id AND c.dep_id = d.dep_id AND a.cu_name = \'英语\' > OK > 时间: 0.002s SELECT student.st_name, student.class_id, student.st_id FROM course JOIN student_course USING ( cu_id ) JOIN student USING ( st_id ) JOIN teacher USING ( dep_id ) WHERE course.cu_name = \'英语\' > OK > 时间: 0.001s
分析原因:
在不加course.cu_name = \’英语\’这条约束条件时,我们对比一下查询结果内容,如下所示SQL1查询结果:
SQL2查询结果:
可以看出SQL1结果的字段多于SQL2,当数据量很大或相关表字段更多时,通过where的条件查询会在性能上有明显的区别,因此建议sql编写时注意相关方法的使用以提升性能。
只是个小实验,详细解释可参考该贴:https://www.cnblogs.com/alianbog/p/5618349.html
盗图一枚,敬请见谅。