MySQL 性能优化 - 多表查询优化

最新推荐文章于 2024-06-06 23:18:01 发布
最新推荐文章于 2024-06-06 23:18:01 发布
阅读量2k 收藏 15
点赞数 2
分类专栏： MySQL 文章标签： mysql 数据库 memcached
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_20315217/article/details/120471100
版权

上一篇讲到了单表数据查询的性能优化，已经掌握了简单的查询优化，这一篇主要针对两表、三表以及多表联查的情况进行分析，看看该如何进行调优。

老样子，先准备两张测试表，脚本如下：

CREATE TABLE IF NOT EXISTS `test_class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `test_book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_class(card)VALUES(FLOOR(1+(RAND()*20)));
 
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO test_book(card)VALUES(FLOOR(1+(RAND()*20)));
  • LEFT JOIN

先进行简单的连表查询：

SELECT * FROM test_class a LEFT JOIN test_book b on a.card = b.card;

使用 Explain 进行查询分析，结果如下图：
在这里插入图片描述分析结果，可以看出，两张表的执行 type 均为 ALL，需要优化，我们来进行索引的创建优化。

因为是 left join 两张表联合查询，那么这个时候，我们需要思考一个问题，索引要怎么建？建到哪一张表上面？ 因为我们还不清楚，况且，目前就两张表，不是左表就是右表，我们先再右表上创建一个索引来进行分析，看一下效果如何，如下：

CREATE INDEX idx_book_card on test_book(card);

再次执行 Explain 进行分析，结果如下：
在这里插入图片描述可以看到，我们在右表 test_book 加的索引已经被使用了，且 type 为 ref，Extra 为 Using index，rows的数量为 1，效果还是很不错的。

那么我们，将右表的索引删掉，重新创建索引在左表上面，试试效果。

drop index idx_book_card on test_book;
create index idx_class_card on test_class(card);

我们再次执行 Explain 进行分析结果查看，结果如下：
在这里插入图片描述通过分析，我们看到，加到左表的索引，也被实际使用了，type 为 index，很明显，没有加到右表的 ref 效果好，另外，通过 rows 中的数量来看，明显比 上一次的结果要大，所以说，加到左表的效果，不如加到右表的效果理想。

那么，这到底是为什么呢？这是由于左连接的特性决定的。left join 是以左表为主表，需要查询出左表里面的所有数据，而右表只是当做联合条件进行匹配，并非全表数据都需要，因此，右表的数据量相对较小，这也满足了小表驱动大表的规范。所以，右边是我们的关键点，一定要加索引。

  • RIGHT JOIN

分析完了 left join ，我们来分析 right join，此时你会发现，他们的结果有共同之处，这里就不进行过多的分析，基本分析流程和上面的一样。

  • 多表查询分析
    上面分析了两张表联查的优化，在工作中，我们也是经常使用的两种 join方式，下面我们再来加入第三张表，进行三张表的联合查询优化。

准备第三张表及相关数据：

CREATE TABLE IF NOT EXISTS `test_phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

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

先来写一个简单的三表联查语句，如下：

EXPLAIN SELECT * FROM test_class a 
	LEFT JOIN test_book b on a.card = b.card
	LEFT JOIN test_phone p on p.card = a.card;

通过上面的 SQL 分析，结果如下：
在这里插入图片描述因为我们新建的 test_phone 表中，并没有任何索引，所以现在type 是 ALL，进行了全表扫描。像这种三张表连接查询的，可以先把前两张的联合进行优化，优化后，当做一张新表，再与下一张表进行联合处理，还是通过两两联合查询进行分析，逐步进行处理。

下面，我们针对新建的 test_phone 表进行索引的创建：

create index idx_phone_card on test_phone(card);

再来执行上面的Explain 分析语句，结果如下：
在这里插入图片描述可以看到，已经非常的完美了。

最终结论
JOIN 语句的优化：

LEFT JOIN 时，索引要建到右表；
RIGHT JOIN时，索引要建到左表；
如果都存在索引，但是分析时，索引性能不佳，可以考虑根据已有的索引，来重新调整两张表的位置，比如 a left join b，换成 b right join a，这个需要针对具体的业务场景来进行分析了。
尽可能减少Join 语句中的NestedLoop的循环总次数，永远用小结果集驱动大的结果集。
优先优化NestedLoop的内层循环；
保证Join语句中被驱动表上的Join 条件字段已经被索引；
当无法保证被驱动表的Join条件字段被索引且内存资源充足的情况下，不要太吝啬JoinBuffer 的设置；

搬运Gong
关注
  • 2
    点赞
  • 踩
  • 15
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL（五）——MySQL索引优化实战（多表联查优化）
yemuxiaweiliang的博客
10-17 3000
MySQL索引优化实战（多表联查优化）
MySQL - Left Join和Inner Join的效率对比，以及优化
MinggeQingchun的博客
09-04 7568
这样的多个left join组合，总觉得这种写法是有问题的，直接用inner join会怎样呢？差别在哪里？后续使用inner join发现速度要比left join快一些。
MySQL：优化技巧、最佳实践，让你的数据库更高效！
最新发布
C00K1E
06-06 1023
查询所有数据库：查询当前数据库：创建数据库：CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];删除数据库：DROP DATABASE [ IF EXISTS ] 数据库名;使用数据库：USE 数据库名;MySQL入门指南：学习数据库管理的基础知识；MySQL备份和恢复策略：确保数据安全的最佳实践；深度解析MySQL：优化技巧、性能调优与最佳实践，让你的数据库更高效！
MySQL 性能优化的 9 种姿势，面试再也不怕了
m0_62289824的博客
03-18 840
我个人认为，如果你想靠着背面试题来获得心仪的offer，用癞蛤蟆想吃天鹅肉形容完全不过分。想必大家能感受到面试越来越难，想找到心仪的工作也是越来越难，高薪工作羡慕不来，却又对自己目前的薪资不太满意，工作几年甚至连一个应届生的薪资都比不上，终究是错付了，错付了自己没有去提升技术。这些面试题分享给大家的目的，其实是希望大家通过大厂面试题分析自己的技术栈，给自己梳理一个更加明确的学习方向，当你准备好去面试大厂，你心里有底，大概知道面试官会问多广，多深，避免面试的时候一问三不知。
【面试】使用联表查询比较慢如何进行优化？
m0_64210833的博客
10-26 7989
如果查询的字段比较多，就需要对sql语句进行优化了，通过explain关键字加在慢sql前执行看是否走的索引，如果没有走索引就创建索引。
Mysql 多表联查优化
weixin_55347832的博客
05-01 6506
一、尽量避免使用子查询 二、尽量避免在where 字句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描 三、in 和 not in 也要尽量避免使用，否则会导致全表扫描（对于连续的字段可以使用between and；也可以使用exists代替in） 四、尽量使用数字型字段，若只含数值信息的字段尽量不要设置为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为迎请在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型二闫只需要比较一次就够了。 五、尽量使用表变量来代替
mysql性能优化工具--tuner-primer使用介绍
09-10
MySQL性能优化是数据库管理中的重要环节，以确保系统的高效运行和数据访问的快速响应。Tuner-Primer是一个专门用于MySQL性能调优的脚本工具，由Matthew Montgomery开发，它可以帮助管理员识别潜在的问题并提供改进...
高并发基础之-MySql性能优化.pdf
04-25
总结来说，MySQL性能优化是一个系统工程，涵盖查询优化、数据库结构优化和服务器配置优化等多个层面。通过掌握这些优化手段，我们可以有效减少系统瓶颈，提高数据库响应速度，从而提升整个应用的性能。在实际操作中...
MySQL查询性能优化
01-19
MySQL 查询性能优化 MySQL 查询性能优化是数据库管理和开发中非常重要的一方面。为了实现高性能，需要从多方面考虑，包括库表结构的设计、索引的建立、查询的设计等。只有合理的设计库表结构和索引，才能确保查询的...
Mysql数据库性能优化之子查询
09-09
MySQL数据库性能优化是数据库管理员和开发人员关注的重要领域，尤其在处理复杂查询和大量数据时。子查询作为SQL语言中的一个重要组成部分，有时会被误解为性能杀手。然而，正确理解和使用子查询可以极大地提升查询...
基于MySQL的数据库查询性能优化.pdf
02-02
基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf...
MySQL 多表关联查询优化实践和原理解析
kerwin的博客
10-04 7681
MySQL 多表关联查询优化实践和原理解析
MySQL优化｜一分钟带你了解三表联查优化
m0_62222133的博客
10-20 1950
创建表 其余两张边在双表优化的案例说过，过去直接复制即可 双表优化 1 CREATE TABLE IF NOT EXISTS `phone` ( 2 `phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 3 `card` INT(10) UNSIGNED NOT NULL, 4 PRIMARY KEY (phoneid) 5 ); 模拟数据 1 INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() *
多表联查优化
qq_47425497的博客
07-08 5745
Mysql多表查询优化
请教一个多表关联查询性能问题.困惑很久
01-21 378
我们程序员开发当中,在做数据库持久化操作的时候.经常需要多表一起关联查询.除了对报表那块是由DBA给出SQL之外.其它的业务大多都是自己写SQL实现. 听到过很多必须这样做.但一直不知道具体原因.所以想发贴出来请教一下坛内高手...
MySQL多表关联查询优化
fanx_home的博客
05-25 3699
SQL优化
left join 和子查询效率_Mysql 多表联合查询效率分析及优化
weixin_39611769的博客
12-03 6054
1. 多表连接类型1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN，或者使用',' 如： SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1,table21 由于其返回的结果为被连接的两个数据表的乘积，因此当有WHER...
sql三表关联查询_SQL优化思路
weixin_39627052的博客
12-09 1416
找到慢查询SQL 找到优化目标。一个项目中有很多SQL，不要一上来就逐一优化，应该找出需要优化的SQL。客户端需要花费很长时间等待查询结果的慢查询是最需要优化的。开发人员可以通过调用链直接追踪到SQL，数据库管理员可以通过日志拿到慢查询，像DM、MySQL等数据库一般都有慢查询记录日志，可以从中找到慢查询语句，一般数据库都自带有分析慢查询日志的工具。分析SQL 找到慢查询SQL后也不应急于查看执行...
MySQL性能优化：查询优化与数据库调优策略
MySQL性能优化是确保数据库高效运行的关键，涵盖了查询优化、数据库结构优化和服务器优化等多个层面。优化的目标是减少系统瓶颈，降低资源占用，提高系统的响应速度。在MySQL中，我们可以通过SHOW STATUS语句来监控...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值