关联查询优化&子查询优化&排序优化

最新推荐文章于 2025-04-16 19:20:25 发布
原创 最新推荐文章于 2025-04-16 19:20:25 发布 · 1.1k 阅读 · 7 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库 #索引

本文介绍SQL查询优化的方法，包括关联查询、子查询、排序优化等方面的内容，并提供了具体的实践案例。

关联查询的优化(都可以自己去实验一下)

数据准备

#分类
CREATE TABLE IF NOT EXISTS `type` (
`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`)
);

分类表中添加20条数据，将下面的SQL语句执行20次。

#向分类表中添加20条记录
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));

在想课程表中插入20条数据，将下面的SQL语句执行20次。

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

采取左(右)外连接的方式

没加索引的情况下，驱动表和被驱动表都是全表扫描。

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

image-20220326192054091

给被驱动表的条件字段添加索引。

CREATE INDEX Y ON book(card);

image-20220326192209221

采用内连接

结论：对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表，查询优化器会在其中选择一个花费少的。

image-20220326192610228

结论1：对于内连接来说，如果表的连接条件只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。

向type表中添加20条数据

结论2：对于内连接来说，在两个表都存在索引的情况下，会选择数据量小的表作为驱动表“小表驱动大表(小表作为驱动表，大表作为被驱动表)

  1. 一开始都不加索引。
EXPLAIN SELECT SQL_NO_CACHE 
 * FROM `type` INNER JOIN book
 ON type.`card` = book.`card`;

注意到下面的顺序是book是驱动表，type是被驱动表。

image-20220326192824399

  1. 给book表上的card字段添加上索引之后
CREATE INDEX Y ON book(card);

image-20220326193424257

book表从驱动表变成了被驱动表。查询优化器在这里做了优化。

image-20220326193722454

子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

**子查询是MySQL的一项重要的功能，可以帮助我们通过一个sQL语句实现比较复杂的查询。但是，子查询的执行效率不高。**原因：

  1. 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询
  2. 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
  3. 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

举例1：查询班长的信息

  • 子查询情况
EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
SELECT monitor
FROM class c
WHERE monitor IS NOT NULL
);
  • 推荐：使用多表连接查询
EXPLAIN SELECT * FROM student stu1
JOIN class c
ON stu1.stuno=c.monitor
WHERE c.monitor IS NOT NULL;

举例2：取所有不为班长的同学

  • 不推荐子查询格式
EXPLAIN SELECT SQL_NO_CACHE a.* 
FROM student a 
WHERE  a.stuno  NOT  IN (
SELECT monitor FROM class b 
WHERE monitor IS NOT NULL);
  • 推荐，换成多表连接查询
EXPLAIN SELECT SQL_NO_CACHE a.*
 FROM student a  LEFT JOIN class c
 ON a.stuno = c.monitor
 WHERE c.monitor IS NULL;

结论：尽量不要使用NOT IN或者NOT EXISTS，用LEFT JOIN xxx ON xxx WHERE xx IS NULL替代

排序优化

这下面的测试是基于50w条记录的表，数据量不同可能结果会不一样。

排序优化

**问题：**在WHERE条件字段上加索引，但是为什么在ORDER BY字段上还要加索引呢?

回答：

在MySQL中，支持两种排序方式，分别是**FileSort和Index**排序。

  • **Index**排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
  • **FileSort**排序则一般在内存中进行排序，占用CPU 较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

优化建议：

  1. SQL中，可以在WHERE子句和ORDER BY字句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY字句避免使用FilteSort排序。当然某些情况下全表扫描，或者FileSort排序不一定比索引慢。但是总的来说，我们还是要避免，以提高查询效率。
  2. 尽量使用Index完成ORDER BY排序。如果WHERE 和ORDER BY后面是相同的列就使用单索引列；如果不同就使用联合索引。
  3. 无法使用Index时，需要对FilteSort方式进行调优

测试

  1. 删除student和class表中的非主键索引。
call proc_drop_index('atguigudb2','student');
CALL proc_drop_index('atguigudb2','class');
  1. 没有任何的索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;

image-20220327194448181

  1. 添加索引
CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);
  1. 不限制索引失效，数据量太大查询优化器用二级索引去查，找到主键值之后，在去回表操作，花费可能还比文件排序要大，所有干脆就不用索引了。
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;

image-20220327200410971

  1. 添加条件限制
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

image-20220327200541396

过程三：order by时顺序错误，索引失效

以下哪些索引失效?

再到student表上创建索引

CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno);

总共两个索引。

image-20220327201615072

image-20220327201620621

EXPLAIN  SELECT * FROM student ORDER BY classid LIMIT 10;

EXPLAIN  SELECT * FROM student ORDER BY classid,NAME LIMIT 10;  

EXPLAIN  SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10; 

EXPLAIN  SELECT * FROM student ORDER BY age,classid LIMIT 10;

EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10;
过程四：order by时规则不一致，索引失效（顺序错，不索引；方向反，不索引，顺序要都一样，要反着就都反着）

索引失效,你age是按照降序排列的,而classid按升序排列的，查询优化器认为花费比文件排序大，所以，索引失效了

# 索引失效,你age是按照降序排列的,而classid按升序排列的，
# 查询优化器认为花费比文件排序大，所以，索引失效了
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;

image-20220327203815027

失效，最左前缀原则，最左边的age没有，索引失效。

EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;

失效，参考上上个。

EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10;

没有失效，直接两个字段都倒过来排序。

EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

image-20220327204030272

过程五：无过滤，不索引

只在where字段用到了索引，查完之后，再排序比较费事，就没有用索引了。

EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;

image-20220327210921416

和上面那个一样。

EXPLAIN  SELECT * FROM student WHERE  age=45 ORDER BY classid,NAME;

image-20220327211033054

用不上索引，数据量太大了，索引失效。

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;

索引生效，这里可能是查询优化器帮我把order by和where的条件换了，就用了索引。

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age LIMIT 10;

image-20220327211640402

小结

INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

案例实战

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。执行案例前先清除student上的索引，只留主键：

DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid_stuno ON student;
DROP INDEX idx_age_classid_name ON student;
#或者
call proc_drop_index('atguigudb2','student');

场景：查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

image-20220327213058214

结论：type 是 ALL，即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须
的。

优化思路：

方案一: 为了去掉filesort我们可以把索引建成idx_age_name(age,NAME)

#创建新索引
CREATE INDEX idx_age_name ON student(age,NAME);

再去查询

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

image-20220327213657074

执行时间

image-20220327214927512

这样就优化了filesort

方案二：给age、stuno、name创建联合索引

虽然extra还是filesort，这是因为使用age和stuno作为条件过滤已经过滤掉大部分数据了，就没有必要再回表操作了。

CREATE INDEX idx_age_stuno_name ON student(age,stuno,NAME);

image-20220327214658978

执行时间
image-20220327215005356

结果竟然有filesort的sql运行速度，超过了已经优化掉 filesort的 sql，而且快了很多，几乎一瞬间就出现了结果。

原因：
所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤掉大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。相对的stuno<101000这个条件，如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

结论：

  1. 两个索引同时存在，mysql自动选择最优的方案。（对于这个例子，mysql选择
    idx_age_stuno_name）。但是， 随着数据量的变化，选择的索引也会随之变化的 。
  2. 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段
    上。反之，亦然。
MySQL关联查询如何优化
清晨qc的博客
03-13 2812
前面讲了原理，从原理出发，讲一下优化的建议被驱动表的连接字段建立索引，因为建立索引的查询方式是效率最高的。left join 或者 right join 这种外连接的情况，要保证小表（小结果集）作为驱动表，大表(大结果集)作为被驱动表，这样性能更好。在查询字段的话，要避免写出 select * ，而是根据业务需要，需要查询出来的 select 出来就行，因为这些字段也会加入到 join buffer 中，减少额外的内存消耗。能够直接多表关联的尽量直接关联，不用子查询，因为子查询的效率更加低。
【数据库】查询优化之子连接优化
weixin_44433834的博客
01-12 627
作者：chirpyli 来源：恒生LIGHT云社区 子查询的逻辑优化内容很多，我们先以 select * from t1 where id in (select a from t2);这条SQL语句为例分析PostgreSQL对子连接的逻辑优化过程。 在往下讲之前，先区别一下子连接与子查询，因为代码中分别会对子连接和子查询进行上拉操作。怎么区别呢？通常，以范围表的方式存在的称为子查询，以表达式的方式存在的称为子连接。实际上也可以通过子句所处的位置来区分子连接和子查询，出现在FROM关键字之后的子句时子查.
SQL优化-关联子查询改表连接
songjian1104的专栏
11-04 1053
1.数据脚本 drop table test1 ; create table test1 as select * from (select d.*,rownum as rn from dba_objects d where d.owner not in ('SYS','SYSTEM','PUB...
关联查询优化
Hai-Yang-code的博客
08-30 1726
建表 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 NO
【mysql】关联子查询的一种优化
moxiaomomo的专栏
07-28 6439
很多时候，在mysql上实现的子查询的性能较差，这听起来实在有点难过。特别有时候，用到IN()子查询语句时，对于上了某种数量级的表来说，耗时多的难以估计。本人mysql知识所涉不深，只能慢慢摸透个中玄机了。 假设有这样的一个exists查询语句： select * from table1 where exists (select * from table2 where id>=30000 and table1.uuid=table2.uuid);
Oracle数据库中基本的查询优化与子查询优化讲解
09-10
此外，子查询优化还包括考虑使用关联子查询（JOIN）代替嵌套子查询，或者使用索引来加速查询。关联子查询可以在单次遍历中完成，而嵌套子查询可能需要多次访问表。同时，确保子查询涉及的列有合适的索引，可以大大...
MySQL之查询优化：执行计划、关联查询与排序优化
最新发布
wj_rdk的博客
04-16 1010
大家好！在MySQL数据库的学习与应用中，查询优化是提升系统性能的关键环节。一直以来，我都希望能和大家一起深入探索MySQL的技术奥秘，今天咱们就聚焦于MySQL查询执行的相关知识，包括执行计划、关联查询优化以及排序优化，帮助大家更好地理解和优化数据库查询，提升系统的整体性能。
Mysql数据库性能优化之子查询
09-09
2. **利用临时表和JOIN**：有时候，将子查询的结果存入临时表，然后通过JOIN操作与主表进行关联，可能比直接使用子查询更高效。 3. **使用EXPLAIN分析执行计划**：通过EXPLAIN命令，可以查看MySQL如何执行查询，...
查询优化：子查询优化~排序优化~分组优化~分页优化
liuerchong的博客
03-25 876
文章目录1. 子查询优化2. 排序（order by）优化3. 分组（group by）优化4. 分页查询（limit）优化 1. 子查询优化 MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结 果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。 子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子 查询的执行效率不高。原因： 执行子查询时，M
MySQL查询优化:连接查询排序浅谈
09-10
在MySQL查询优化中，连接查询和排序是两个关键的环节，尤其当它们结合在一起时，可能会引发性能问题。本文将探讨一个具体的例子，即在连接查询中加入`ORDER BY`子句导致效率骤降的问题，以及如何通过优化SQL语句来...
MySQL高级篇——关联查询和子查询优化
种一棵树最好的时间是十年前，其次是现在
05-22 4043
左外连接：优先右表创建索引，连接字段类型要一致、内连接：驱动表由数据量和索引决定、 join语句原理、子查询优化：拆开查询或优化成连接查询
数据库查询优化之关联查询优化
qq_43193797的博客
12-25 788
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...
关联查询优化总结
qq_44201969的博客
03-28 1232
保证被驱动表的JOIN字段已经创建了索引 需要JOIN 的字段，数据类型保持绝对一致。 LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表 。减少外层循环的次数。 INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数) 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。 补充：什么是“ 小表”？ 在决定哪个表做驱...
MySQL优化之关联查询优化
朗福德的博客
07-06 1666
前言 SQL语句优化是很重的一个事情。所以要去做。 正文 一、left join 先写一个SQL语句看看怎么性能如何： 这样的情况怎么去优化呢？肯定是使用到的查询字段去建索引。 哪就先给idx_card表的card字段加上索引看看有没有效果。 执行： ALTER TABLE `book` ADD INDEX idx_card( `card`); 建完索引后再接着运行咱们的查询语句看看怎么样了。 果然是有点变化。 前边说过了，在使用的查询条件中添加索引，现在把刚建的索引删除，然后重新在class表中的
Join关联查询优化
qq_33240556的博客
01-18 1164
准备数据mysql的表关联常见有两种算法。
查询优化_关联查询与子查询优化
daai5201314的博客
09-11 430
2)left join 时，选择小表作为驱动表，大表作为被驱动表 3)inner join时，mysql会自动将小结果集的表选为驱动表。选择相信mysql优化策略。4)能够直接多表关联的尽量直接关联，不用子查询。先删除之前建立的索引！class book;1)保证被驱动表的join字段被索引。换成inner join（
mysql关联查询优化器
stay hungry ! stay foolish!
10-27 599
mysql关联查询优化器 目录 一、关联查询的含义 二、关联查询优化器 三、举例 四、贪婪模式 一、关联查询的含义 mysql的关联查询：总是从一个表开始一直嵌套循环、回溯完成所有表关联，是一个左侧深度优化的树。 二、关联查询优化器 它决定了多个表关联时的顺序，通常多表 关联的时候，可以 有多种不同的关联顺序来获得相结...
对关联优化查询建议
汪雯琦的博客
04-24 699
【关联优化查询建议】 1、保证被驱动表的join字段已经被索引 　　被驱动表&nbsp; join 后的表为被驱动表&nbsp; (需要被查询) 2、left join 时，选择小表作为驱动表，大表作为被驱动表。 　　但是 left join 时一定是左边是驱动表，右边是被驱动表 3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。 　　mysql 自动选择。小表作为驱动表...
关联查询SQL的一次优化过程
行万里
03-31 1555
本博文出自51CTO博客gaochaojs博主，有任何问题请进入博主页面互动讨论! 博文地址：http://jncumter.blog.51cto.com/812546/1620406 如前几次博文中所述，流程结束后的实例信息可以通过统一的入口即高级查询（可以导出excel，也预留了生成各种报表的接口）查询。但对于一些特殊的工作流，比如转正、离职、考勤等我们也提供了专门的查询模
Oracle9i查询优化深度解析
2. **SQL语句转换**：优化器可以转换SQL语句，如视图合并（简单和复杂）、子查询展平、谓词传递和消除通用子表达式，以提高查询性能。 3. **访问路径选择**：包括联接排序、适应性查找策略、位图索引、快速全索引...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值