第三节 关联查询优化

1、数据准备

  1. #分类
  2. CREATE TABLE IF NOT EXISTS `class` (
  3. `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4. `card` INT(10) UNSIGNED NOT NULL,
  5. PRIMARY KEY (`id`)
  6. );
  7. #图书
  8. CREATE TABLE IF NOT EXISTS `book` (
  9. `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  10. `card` INT(10) UNSIGNED NOT NULL,
  11. PRIMARY KEY (`bookid`)
  12. );
  14. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  15. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  16. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  17. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  18. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  19. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  20. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  21. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  22. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  23. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  24. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  25. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  26. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  27. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  28. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  29. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  30. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  31. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  32. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  33. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  35. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  36. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  37. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  38. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  39. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  40. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  41. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  42. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  43. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  44. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  45. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  46. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  47. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  48. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  49. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  50. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  51. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  52. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  53. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  54. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

2、left join

①测试

开始是没有加索引的情况。下面开始explain分析：

EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;

分析结果：

img025.png

添加索引优化

ALTER TABLE book ADD INDEX Y (card); 
ALTER TABLE class ADD INDEX X (card);

重新分析的结果：

img026.png

看这个分析结果发现：在 class 表上添加的索引起的作用不大。

③结论

  • 小表驱动大表
    • 小表：相对来说记录较少的表
    • 大表：相对来说记录较多的表
  • 驱动方式识别
    • left join：左边驱动右边（此时把小表放在左边）
    • right join：右边驱动左边（此时把小表放在右边）
  • 加索引的方式：通常建议在大表（被驱动）的表加索引，效率提升更明显。
  • 原因：
    • 原因1：被驱动表加了索引之后，收益更大。从 ALL -> ref
    • 原因2：外连接首先读取驱动表的全部数据，被驱动只读取满足连接条件的数据。

3、inner join

换成inner join（MySQL自动选择驱动表）

# 特意将 book 放在 from 子句，去对 class 表做内连接
EXPLAIN
SELECT SQL_NO_CACHE *
FROM book
         inner JOIN class ON class.card = book.card;

分析结果：

img027.png

MySQL 还是选择了 class 作为驱动表。

4、小结

  • 保证被驱动表的 join 字段被索引。join 字段就是作为连接条件的字段。
  • left join 时，选择小表作为驱动表（放左边），大表作为被驱动表（放右边）
  • inner join 时，mysql 会自动将小结果集的表选为驱动表。
  • 子查询尽量不要放在被驱动表，衍生表建不了索引
  • 能够直接多表关联的尽量直接关联，不用子查询

上一节 回目录 下一节