MySQL之join查询如何优化

2023-07-05 11:50

短信预约 -IT技能 免费直播动态提醒

这篇文章主要介绍“MySQL之join查询如何优化”，在日常操作中，相信很多人在MySQL之join查询如何优化问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL之join查询如何优化”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

1. 那什么是驱动表呢？

指定了联接条件时，满足查询条件的记录行数少的表为驱动表
未指定联接条件时，行数少的表为驱动表（Important!）

如果你搞不清楚该让谁做驱动表、谁 join 谁，就别指定谁 left/right join 谁了，请交给 MySQL优化器运行时决定吧。

2. 复杂的sql怎么识别驱动表呢？

按经验谈，使用EXPLAIN，第一行出现的表就是驱动表。

3. 关联查询原理是怎样的？

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。

//例: user表10000条数据，class表20条数据select * from user u left join class c u.userid=c.userid

上面sql的后果就是需要用user表循环10000次才能查询出来，而如果用class表驱动user表则只需要循环20次就能查询出来。

4. 该如如何优化？

优化的目标是尽可能减少JOIN中Nested Loop的循环次数，以此保证：永远用小结果集驱动大结果集。

排序的字段也有影响，有条原则：对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序！

5. 实例

explain select * from user u left join class c on u.userid=c.userid INNER JOIN subject s on c.subjectId=s.id  WHERE 1=1 ORDER BY u.create_time DESC limit 0,10

够复杂吧。假如，user表有千万级记录，class表要少得多，从执行计划的得知驱动表（数据到千万级）。由于动用了“LEFT JOIN”，所以相当于已经指定了驱动表。

如何优化？

//优化第一步：LEFT JOIN改为JOIN，对，直接 join！explain select * from user u join class c on u.userid=c.userid INNER JOIN subject s on c.subjectId=s.id  WHERE 1=1 ORDER BY u.create_time DESC limit 0,10//优化第二步：从上面执行计划得知， 有Using temporary(临时表);Using filesort，解决方法是调整排序字段（借助前面讲过排序的原则）explain select * from user u join class c on u.userid=c.userid INNER JOIN subject s on c.subjectId=s.id  WHERE 1=1 ORDER BY c.id DESC limit 0,10

总之，sql优化中explain工具是非常重要的武器。

mysql优化(关联查询优化)

准备数据

#分类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)));