多表查询SQL语句优化
数据多个表关联查询时，由于表之间的连接关系(内连接、外连接、交叉连接)，导致数据库服务器常常从⼏万条甚⾄更多的数据记录中查符合条件的记录，如果sql查询语句设计不好查询的复杂度就会直线上升，甚⾄是指数级上升，导致查询时间长甚⾄失去相应，这⾥讲两种从sql语句优化查询的⽅法。
sql优化的几种方式1.把外连接变成交叉连接或内连接(对不起，在写改良例⼦的时候发现这种⽅法并不能改善查询的速度，这是⼀个错误)：
证明过程如下：
我们建三个表分别是：temple、bonze、和woodfish。
temple表⽰庙宇，它的字段有：
temple_id      int
temple_name  varchar(50)
location  varchar(50)
build_date      datetime
temple_id是主键，设为⾃增；temple_name是庙宇名；location是位置，就是寺庙的地址；build_date是建庙时间。
bonze表⽰和尚，它的字段有(出家⼈四⼤皆空，这些字段够了，呵呵)：
bonze_id  int
temple_id        int
bonze_name    varchar(50)
register_time  datetime
bonze_id是主键，设为⾃增；temple_id表⽰这个和尚属于哪个寺庙，对应寺庙的temple_id，对于没有寺庙的流浪和尚这⾥暂时不考
虑(写这篇⽂章主要是告诉⼀些⼤寺庙，他们可以采⽤软件管理的⽅式来管理寺庙，如果他们看到了我这篇⽂章可能会我开发⼀个开发软件也未定，⾄于流浪和尚是不太可能的了，所以不考虑他们，呵呵，罪过、罪过)；bonze_name是和尚名字，和尚本没有名字就叫法号
吧；register_time是注册时间，现在做个和尚挺不容易的，难度差不多赶上⾼考上榜了，所以要⼀个注册时间。
woodfish表⽰⽊鱼，呵呵，⽊鱼的英⽂实在不会写，只好直译了，哪位兄弟妹知道了告诉我⼀下。⽊鱼的字段有：
woodfish_id    int    Unchecked
bonze_id  int    Unchecked
woodfish_num      varchar(50)    Checked
woodfish_id是主键，设为⾃增；bonze_id表⽰这个⽊鱼属于那个和尚，不属于任何和尚的⽊鱼我们也暂时不考虑，那些房⼦仓库就可以了，不⽤编号什么的；woodfish_num表⽰⽊鱼编号。
我们建⽴表的关系图如下(微软的SqlServerManagement应该提供可以把关系图转换成图⽚的功能，我还要photoshop来截图)：
图1 数据关系图
相当简单的⼀个数据库。我们假设有些寺庙可以没有和尚，这就是荒庙，看过武侠⼩说的兄弟们应该可以了解的。和尚可以没有⽊鱼，可能太穷了，买不起，也可能太懒了，懒得敲。当然有些和尚也可以拥有⼏个⽊鱼，可能准备收徒弟的时候送个⽊鱼做见⾯礼什么的。
现在我们插⼊⼀些数据。⼀下数据仅为测试使⽤，并⽆意冒犯任何寺庙和庙⾥的⾼僧。
temple的数据为：
temple_id temple_name location build_time
1少林寺河南省登封市嵩⼭1900-2-2 0:00:00
2⼤杰寺五龙⼭1933-2-3 3:03:03
3法源寺宣武门外教⼦胡同南端东侧1941-2-3 5:04:03
4⼴济寺⾩成门内⼤街东⼝1950-3-3 3:03:03
5碧云寺⾹⼭东麓1963-3-3 3:03:03
表1 寺庙信息
bonze的数据为：
bonze_id temple_id bonze_name register_time
11悟空2003-2-2 0:00:00
21悟静2002-2-2 0:00:00
31悟能2001-2-2 0:00:00
41悟性2004-4-4 0:00:00
51悟戒2005-5-5 0:00:00
62觉原2001-3-3 0:00:00
72觉情2005-3-3 0:00:00
82觉静2006-4-4 0:00:00
93⼀噌2004-4-4 0:00:00
105⼋戒2001-1-1 0:00:00
表2 和尚信息
woodfish的数据为：
woodfish bonze_id woodfish_num
111111
212222
313333
424444
565555
686666
787777
8108888
9109999
10101010
11411111
1241212
1341313
好了，假设现在我们要查询某⼀个或⼏个寺庙的详细信息，即寺庙信息、和尚信息和⽊鱼信息。对于没有和尚的寺庙要列⼀条记录，和尚和⽊鱼的所有字段显⽰为空；对于没有⽊鱼的和尚也列⼀条记录，⽊鱼信息显⽰为空。
如果我们直接⽤内连接查询，对于没有和尚的寺庙的信息或者没有⽊鱼的和尚的信息就查不出来，因为数据库服务器先对所有表做交叉连接，然后在到符合条件的数据。如果和尚信息为空，这个寺庙信息交叉后就是空的，和尚和⽊鱼也是同样的道理。
这时我们就要⽤到外连接，外连接分左连接、右连接、和全外(ALL)连接，左连接就是左边的表作为重点，把左边的表的信息全列出来，对于右边的表⽤空(整数⽤0)来补；右连接重点刚好相反，全外连接就是没有主次之分的。两边的表信息都能列举出来。
现在我们要⽤到的就是左连接。
左连接后的搜索记录总数可以通过以下SQL语句来检测：
SELECT    ple_id, ple_name, temple.location, temple.build_date, bonze.bonze_id, ple_id AS Expr1, bonze.bonze_name,
FROM        temple LEFT OUTER JOIN
bonze ple_id = ple_id LEFT OUTER JOIN
woodfish ON bonze.bonze_id = woodfish.bonze_id
总共有18条记录
改成内连接后搜索记录总数可以通过以下三个SQL语句查询结果联合得到：
SELECT    ple_id, ple_name, temple.location, temple.build_date, bonze.bonze_id, ple_id AS Expr1, bonze.bonze_name,
FROM        temple INNER JOIN
bonze ple_id = ple_id INNER JOIN
woodfish ON bonze.bonze_id = woodfish.bonze_id
寺庙既有和尚和尚⼜有⽊鱼的记录有13条记录
SELECT    ple_id, ple_name, temple.location, temple.build_date, bonze.bonze_id, ple_id AS Expr1, bonze.bonze_name,
FROM        temple INNER JOIN
bonze ple_id = ple_id
WHERE    (bonze.bonze_id NOT IN
(SELECT    bonze_id
FROM          woodfish))
寺庙的和尚没有⽊鱼有4条记录
SELECT    temple_id, temple_name, location, build_date, 0 AS bonze_id, 0 AS Expr1, NULL AS bonze_name, NULL AS
register_time, 0 AS woodfish_id,
0 AS Expr2, 0 AS woodfish_num
FROM        temple
WHERE    (temple_id NOT IN
(SELECT    temple_id
FROM          bonze))
寺庙即没有和尚⼜没有⽊鱼的记录有1条
以上证明把外连接改成内连接并不能改善查询速度。
2.把内连接的where⼦句换成⼦表查询(去掉where⼦句)：
改良前
SELECT DISTINCT
student.card_id, student.name, student.student_num, [order].order_id, [order].order_since, [order].persist_time,
lab.name AS labname, bench.num, bench.bench_ip
FROM        student INNER JOIN
[order] ON student.student_id = [order].student_id INNER JOIN
lesson ON [order].lesson_id = lesson.lesson_id INNER JOIN
bench ON [order].bench_id = bench.bench_id INNER JOIN
lab ON bench.lab_id = lab.lab_id
WHERE    (NOT ([order].order_id IN
(SELECT    order_id
FROM          history))) AND (student.student_num = @card_id) AND (lab.name = @labname) AND ([order].status = 0) AND ([order].editor_type = 0)
ORDER BY [order].order_since
改良后
SELECT DISTINCT
student1.card_id, student1.name, student1.student_num, der_id, der_since, order1.persist_time, dure_time,
lesson.allow_late, lab1.name AS labname, bench.num, bench.bench_ip
FROM        (SELECT    student_id, card_id, name, student_num
FROM          student
WHERE      (student_num = 'card_id233')) AS student1 INNER JOIN
(SELECT    order_id, bench_id, lesson_id, student_id, order_since, persist_time
FROM          [order]
WHERE      (NOT (order_id IN
(SELECT    order_id
FROM          history))) AND (status = 0) AND (editor_type = 0)) AS order1 ON student1.student_id = order1.student_id INNER JOIN
lesson ON order1.lesson_id = lesson.lesson_id INNER JOIN
bench ON order1.bench_id = bench.bench_id INNER JOIN
(SELECT    lab_id, name
FROM          lab
WHERE      (name = 'labname23')) AS lab1 ON bench.lab_id = lab1.lab_id
ORDER der_since

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。