SQL多表关联原理研究实验验证

SQL多表关联原理研究-实验验证

2019年04月18日 12:41:30 jwolf2 阅读数 66

数据准备-建表语句及数据

1. CREATE TABLE testdb.table_a

2. (

3. Aid int(11) PRIMARY KEY NOT NULL,

4. A_field1 varchar(12),

5. A_field2 varchar(12),

6. A_field3 varchar(12),

7. Bid int(11),

8. Did int(11)

9. );

10. CREATE INDEX tablea_Bid_index ON testdb.table_a (Bid);

11. CREATE INDEX table_a_Did_index ON testdb.table_a (Did);

12. INSERT INTO testdb.table_a (Aid, A_field1, A_field2, A_field3, Bid, Did) VALUES (1, 'af11', 'af12', 'af13', 1, 1);

13. INSERT INTO testdb.table_a (Aid, A_field1, A_field2, A_field3, Bid, Did) VALUES (2, 'af21', 'af22', 'af23', 2, 2);

14. INSERT INTO testdb.table_a (Aid, A_field1, A_field2, A_field3, Bid, Did) VALUES (3, 'af31', 'af32', 'af33', 3, 3);

15. CREATE TABLE testdb.table_b

16. (

17. Bid int(11) PRIMARY KEY NOT NULL,

18. B_field1 varchar(12),

19. B_field2 varchar(12),

20. B_field3 varchar(12),

21. Cid int(11)

22. );

23. CREATE INDEX tablea_cid_index ON testdb.table_b (Cid);

24. INSERT INTO testdb.table_b (Bid, B_field1, B_field2, B_field3, Cid) VALUES (1, 'bf11', 'bf12', 'bf13', 1);

25. INSERT INTO testdb.table_b (Bid, B_field1, B_field2, B_field3, Cid) VALUES (2, 'bf21', 'bf22', 'bf23', 2);

26. INSERT INTO testdb.table_b (Bid, B_field1, B_field2, B_field3, Cid) VALUES (3, 'bf31', 'bf32', 'bf33', 3);

27. CREATE TABLE testdb.table_c

28. (

29. Cid int(11) PRIMARY KEY NOT NULL,

30. C_field1 varchar(12),

31. C_field2 varchar(12),

32. C_field3 varchar(12)

33. );

34. INSERT INTO testdb.table_c (Cid, C_field1, C_field2, C_field3) VALUES (1, 'cf11', 'cf12', 'cf13');

35. INSERT INTO testdb.table_c (Cid, C_field1, C_field2, C_field3) VALUES (2, 'cf21', 'cf22', 'cf23');

36. INSERT INTO testdb.table_c (Cid, C_field1, C_field2, C_field3) VALUES (3, 'cf31', 'cf32', 'cf33');

37. CREATE TABLE testdb.table_d

38. (

39. Did int(11) PRIMARY KEY NOT NULL,

40. D_field1 varchar(12),

41. D_field2 varchar(12),

42. D_field3 varchar(12)

43. );

44. INSERT INTO testdb.table_d (Did, D_field1, D_field2, D_field3) VALUES (1, 'df11', 'df12', 'df13');

45. INSERT INTO testdb.table_d (Did, D_field1, D_field2, D_field3) VALUES (2, 'df21', 'df22', 'df23');

46. INSERT INTO testdb.table_d (Did, D_field1, D_field2, D_field3) VALUES (3, 'df31', 'df32', 'df33');

表关系

实验1：验证驱动表、执行顺序、回表操作与索引覆盖

结论1:执行顺序与驱动表——mysql会有优化SQL语句，对各个表进行排序，执行计划中ID越大的先执行，如图中ID为2 的子查询先执行，ID相同的从上往下依次执行，最上的一个为驱动表，驱动表会根据where等条件择优选取尽量优先滤掉大多数数据减少后面的nested loop次数，始终是小结果集驱动大结果集。b表为什么排在驱动表下一位——b表跟驱动表c有关联，b与a关联，a与d关联，故顺序是cbad。

结论2:回表与索引覆盖——子查询执行计划看到table为null说明该查询不要查询数据表，只需要查询索引文件。说明索引文件与数据库文件是分离的，如果先查询的索引就能取得需要的数据，就不会回去去数据表文件，这就是传说中的索引覆盖。把where条件改成where c.Cid >substr((select min(Cid)+C_field1 from table_c),1,1)即可看到不能索引覆盖了而是要回表取C_field1

另外也说明为什么不能索引列过多，索引列长度不能过大，索引列区分度要大不能是性别字段等，不能是频繁更新字段等要求——索引过多过大加载数据量大性能消耗多，也浪费磁盘内存空间，区分度小还不如不查索引文件而直接查数据表，频繁更新的字段建索引可能导致不断更新索引文件。

结论3：这里我们去掉where条件，可以看到执行顺序变成的本来的顺序abdc，为什么a表没有利用索引——因为没有能过滤掉a表数据的条件，即使是查询a表的索引也是获取的是全部数据，还不如绕过索引文件直接查数据表文件，故变成了全表扫描，bdc这几个关联表要根据关联条件即各表ID相互关联，利用索引就可以快速关联

实验2——验证where条件摆放位置

结论：left join的表可以看到过滤条件摆放到where后与摆放到on后最终取到的数据是有区别的，where过滤条件是对整个查询出来的数据行进行“截取”，而摆放在on后始终会保留left join的左表的数据，而右表不符合on后条件的都是null。摆放在on后的条件仅是过滤右表（on后如果有非右表的过滤条件如b left join c on a.aid=2这个条件是无效的）的数据不会影响整体SQL的执行顺序，explain看到驱动表不是c而是原始的a，顺序是abdc，不能过滤掉其它表数据，会导致后面可能有的nested loop次数较多。如果业务要求无论如何都要保留左表数据可以将过滤条件放on后，如果是右表不满足条件，左表数据也不要保留了就一定要将条件放到where，这其实跟inner join一样了，下面来研究一下何时该inner join何时该left join。

实验三——left join 与inner join

结论：如上的inner join 与left join 执行计划及返回值都一样。驱动表会根据on后及where后条件综合选取（尽量选择根据过滤能过滤大多数数据的表为驱动表），且on 后的条件与where 后等效，即使是非左表右表的条件也有效。

总结：

• 1、如果a表关联b表，不满b表条件时a表数据也不要时，这时可以考虑inner join 过滤条件写到on后。这给人的赶脚就是一个基本表不断扩张字段并缩减数据行的过程。也可以全程使用left join，不满b表条件时a表数据要时就把过滤条件写到on后，否则写到where后，所谓大SQL不过是多个关联外加一些子查询union all等。
• 2、使用大SQL有其优点也有缺点——优点：业务层代码简单，压力小，避免了MySQL服务器<=>web应用的数据IO，避免了频繁的数据库连接；缺点：需要SQL功底较好的程序员来维护，需要分表分库SQL改动量大，数据库CPU消耗稍高。
• 3、到底是单表还是多表关联效率高还得具体问题具体分析，即使是单表也千万不能循环内嵌套数据库操作，不能select *导致大量不必要的数据IO。