关联查询优化详解和案例

文章探讨了数据准备过程,包括创建class和book表并插入随机数据。然后,通过leftjoin操作分析没有索引和添加索引后的查询性能,指出在大表上的索引能带来更大优化。文章还讨论了innerjoin时MySQL选择驱动表的机制,并提供了关联查询优化的建议,如选择合适的驱动表,避免在被驱动表中使用子查询,以及限制关联查询的表数量,以提高查询效率。
摘要由CSDN通过智能技术生成

1、数据准备

#分类
CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);

#图书
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);
 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
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;

分析结果:

添加索引优化:

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

重新分析的结果:

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

③结论

  • 小表驱动大表
    • 小表:相对来说记录较少的表
    • 大表:相对来说记录较多的表
  • 驱动方式识别
    • left join:左边驱动右边(此时把小表放在左边)
    • right join:右边驱动左边(此时把小表放在右边)
  • 加索引的方式:通常建议在大表(被驱动)的关联字段上加索引,效率提升更明显。
  • 原因:
    • 原因1:被驱动表加了索引之后,收益更大:从 ALL -> ref。我们应该把更强的力量用给更重的任务,如果被驱动表是用主键或唯一二级索引列作为连接条件,那么就不仅是升级到ref,而是eq_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;

分析结果:

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

此时需要给 book 表增加较多条记录,让两个表数据量差异明显才有这个效果。

4、关联查询的优化建议

  • 把大表放在被驱动表的位置上
  • 保证被驱动表的 join 字段被索引。join 字段就是作为连接条件的字段。
  • inner join 时,MySQL会自动将小结果集的表选为驱动表。
  • 子查询尽量不要放在被驱动表,子查询会生成衍生表,衍生表利用不了索引
  • 能够用关联查询就不用子查询
  • 关联查询涉及到的数据库表不要超过3张。
  • 2
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值