MySQL关联查询优化实现方法详解

目录
  • 左外连接
  • 内连接INNER JOIN

我们准备如下两个表，并插入数据。

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

左外连接

首先我们分析SQL如下，type为驱动表（内表），book为被驱动表（外表）。

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

每次从type中获取一条数据然后后book中的数据进行对比（全表扫描），这个过程要要重复20次（type 表有20条数据）。

这里可以看到，type均为all。另外还可以看到MySQL帮我们做了一个优化，使用了join buffer进行缓存。

我们为被驱动表 book.card 添加索引优化

CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book
ON type.card = book.card;

这里能够看到，虽然type表仍旧是要处理20次，但是拿着type的数据去book中寻找时，走的是索引。对于B+树来讲，其时间复杂度为logN，相比前面的全表扫描要快很多。

也就是对于左外连接来讲，如果只能添加一个索引，那么一定添加到被驱动表上。

当然，给type的card页创建索引也是可以的。

CREATE INDEX X ON `type`(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book
ON type.card = book.card;

如果索引只加在了驱动表（左表）呢？

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

可以看到，同样使用了join buffer。而对于驱动表来讲，即使用到了索引也要做一个整体的遍历（无非这时走的是索引文件）。而被驱动表没有索引，那么性能会相对较慢。

如下图所示，从其查询成本我们也可以看到显著区别。

结论： 左(外)连接时，索引加在右表的连接字段。left join用于确定如何从右表搜索行，左表一定都有。同理，右(外)连接时，索引创建在左表的连接字段。该连接字段在两个表中的数据类型保持一致。

此外，从上面Using where; Using join buffer (Block Nested Loop)我们也可以想到，如果有条件，那么join buffer给一个较大的容量是有助于提升性能的。

内连接INNER JOIN

我们去掉索引，然后查看执行计划。

DROP INDEX X ON `type`;
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book
ON type.card = book.card;

我们给被驱动表 book.card 添加索引

CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book
ON type.card = book.card;

我们再给驱动表type添加索引

CREATE INDEX X ON `type`(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book
ON type.card = book.card;

可以看到这里二者均用到了索引。需要说明的是，这时type和book上下次序可能转换，也就是说 对于inner join来讲，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的 。

那如果book.card没有索引，type.card 有索引呢？

DROP INDEX Y ON book;
EXPLAIN  SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book
ON type.card = book.card;

可以看到book作为了驱动表，type作为了被驱动表。即，对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。

如果两个表数据量不一致呢？比如这里我们type为40条，book为20条。

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

结论： 对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表，即“小表驱动大表”。

到此这篇关于MySQL关联查询优化实现方法详解的文章就介绍到这了,更多相关MySQL关联查询优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • 实例讲解Java的MyBatis框架对MySQL中数据的关联查询

    实例讲解Java的MyBatis框架对MySQL中数据的关联查询

    mybatis 提供了高级的关联查询功能,可以很方便地将数据库获取的结果集映射到定义的Java Bean 中.下面通过一个实例,来展示一下Mybatis对于常见的一对多和多对一关系复杂映射是怎样处理的. 设计一个简单的博客系统,一个用户可以开多个博客,在博客中可以发表文章,允许发表评论,可以为文章加标签.博客系统主要有以下几张表构成: Author表:作者信息表,记录作者的信息,用户名和密码,邮箱等. Blog表   :  博客表,一个作者可以开多个博客,即Author和Blog的关系是一对多.

  • Yii2实现跨mysql数据库关联查询排序功能代码

    背景:在一个mysql服务器上(注意:两个数据库必须在同一个mysql服务器上)有两个数据库: memory (存储常规数据表) 中有一个 user 表(记录用户信息) memory_stat (存储统计数据表) 中有一个 user_stat (记录用户统计数据) 现在在 user 表生成的 GridView 列表中展示 user_stat 中的统计数据 只需要在User的model类中添加关联 public function getStat() { return $this->hasOne(U

  • 浅谈mysql中多表不关联查询的实现方法

    大家在使用MySQL查询时正常是直接一个表的查询,要不然也就是多表的关联查询,使用到了左联结(left join).右联结(right join).内联结(inner join).外联结(outer join).这种都是两个表之间有一定关联,也就是我们常常说的有一个外键对应关系,可以使用到 a.id = b.aId这种语句去写的关系了.这种是大家常常使用的,可是有时候我们会需要去同时查询两个或者是多个表的时候,这些表又是没有互相关联的,比如要查user表和user_history表中的某一些数据

  • mysql一对多关联查询分页错误问题的解决方法

    xml问价中查询数据中包含list,需要使用collection <resultMap id="XX" type="com.XXX.XXXX"> <id column="o_id" jdbcType="BIGINT" property="id" /> <result column="o_user_id" jdbcType="BIGINT"

  • MySQL JOIN关联查询的原理及优化

    MySQL JOIN关联查询的原理及优化

    目录 1 关联查询的执行 2 没有索引的算法 1 关联查询的执行 关联查询的执行过程是:先遍历关联表t1(驱动表,全表扫描),然后根据从表t1中取出的每行数据中的a值,去表t2(被关联表,被驱动表)中查找满足条件的记录,可以走t2的索引搜索.在形式上,这个过程就跟我们写程序时的嵌套查询类似,并且可以用上被驱动表的索引,所以我们称之为“Index Nested-Loop Join”,简称NLJ.在join语句的执行流程中,驱动表是走全表扫描,而被驱动表是走索引树搜索. 假设被驱动表的行数是M.每次

  • MySQL详细讲解多表关联查询

    MySQL详细讲解多表关联查询

    目录 数据库设计范式 外键 内连接 外连接 结语 数据库设计范式 目前数据库设计有五种范式 , 一般我们数据库只需要满足前三项即可 第一范式 : 确保每列保持原子性 什么是原子性? 意思就是不可再分的,例如下 联系方式有 QQ,微信 , 电话等等 , 显然此列不满足原子性, 如果是单独的QQ或者电话等,则只有一个, 满足第一范式 第二范式 : 要有主键,要求其他字段都依赖于主键 为什么主键这么重要? 我们可以这样理解, 如果把表当作一个队伍, 那么主键就是这个队伍的队旗 • 没有主键就没有唯一性

  • mysql中的跨库关联查询方法

    mysql中的跨库关联查询方法

    业务场景:关联不同数据库中的表的查询 比如说,要关联的表是:机器A上的数据库A中的表A && 机器B上的数据库B中的表B. 这种情况下,想执行"select A.id,B.id from A left join B on ~~~;"那是不可能的,但业务需求不可变,数据库设计不可变,这就蛋疼了.. 解决方案:在机器A上的数据库A中建一个表B... 这当然不是跟你开玩笑啦,我们采用的是基于MySQL的federated引擎的建表方式. 建表语句示例: CREATE TABL

  • MySQL中三种关联查询方式的简单比较

    看看下面三个关联查询的 SQL 语句有何区别? SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) SELECT * FROM film JOIN film_actor USING (film_id) SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id 最大的不同更多是语法糖,但有一些有意思的东西值得关注. 为了

  • MySQL关联查询优化实现方法详解

    MySQL关联查询优化实现方法详解

    目录 左外连接 内连接INNER JOIN 我们准备如下两个表,并插入数据. #分类 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 NO

  • 查看mysql当前连接数的方法详解

    1.查看当前所有连接的详细资料: ./mysqladmin -uadmin -p -h10.140.1.1 processlist2.只查看当前连接数(Threads就是连接数.): ./mysqladmin -uadmin -p -h10.140.1.1 status .查看当前所有连接的详细资料: mysqladmin -uroot -proot processlist D:\MySQL\bin>mysqladmin -uroot -proot processlist| Id | User

  • Django 多表关联 存储 使用方法详解 ManyToManyField save

    当models中使用ManyToManyField进行多表关联的时候,需要使用字段的add()方法来增加关联关系的一条记录,让两个实例关联起来才能顺利保存关联关系 #models.py 问题分类question_category和类别使用了多对多关系(先不管是否合理) #coding:utf-8 from django.db import models # Create your models here. class QuestionCategory(models.Model): categor

  • zabbix监控MySQL主从状态的方法详解

    搭建MySQL主从后,很多时候不知道从的状态是否ok,有时候出现异常不能及时知道,这里通过shell脚本结合zabbix实现监控并告警 一般情况下,在MySQL的从上查看从的运行状态是通过Slave_IO_Running线程和Slave_SQL_Running线程是否ok,通过命令"show slave status\G;"即可查看.所以这里根据这两个值进行判断. agent端脚本编写及配置 说明:所有zabbix相关的脚本我都放在了/etc/zabbix/script/ 目录里面,下

  • Mysql之组合索引方法详解

    对于任何DBMS,索引都是进行优化的最主要的因素.对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加,性能会急剧下降. 如果对多列进行索引(组合索引),列的顺序非常重要,MySQL仅能对索引最左边的前缀进行有效的查找.例如: 假设存在组合索引(c1,c2),查询语句select * from t1 where c1=1 and c2=2能够使用该索引.查询语句select * from t1 where c1=1也能够使用该索引.但是,查询语句select * from t1

  • MySQL联合查询实现方法详解

    MySQL联合查询实现方法详解

    联合查询简单说 就是将两次查询合并在一起 例如 我们这里有一个用户表 我们先编写一段SQL select name from staff where age > 21; 查询年龄大于21的 输出结果如下 然后我们再写一段sql select name from staff where status =1; 查询 status 状态字段等于1 的 输出效果如下 然后我们可以二合一一下 select name from staff where age > 21 union all select n

  • MySQL binlog 远程备份方法详解

    以前备份binlog时,都是先在本地进行备份压缩,然后发送到远程服务器中.但是这其中还是有一定风险的,因为日志的备份都是周期性的,如果在某个周期中,服务器宕机了,硬盘损坏了,就可能导致这段时间的binlog就丢失了. 而且,以前用脚本对远程服务器进行备份的方式,有个缺点:无法对MySQL服务器当前正在写的二进制日志文件进行备份.所以,只能等到MySQL服务器全部写完才能进行备份.而写完一个binlog的时间并不固定,这就导致备份周期的不确定. 从MySQL5.6开始,mysqlbinlog支持将

  • MySQL主从数据库搭建方法详解

    本文实例讲述了MySQL主从数据库搭建方法.分享给大家供大家参考,具体如下: 主从服务器是mysql实时数据同步备份的一个非常好的方案了,现在各大中小型网都都会使用mysql数据库主从服务器功能来对网站数据库进行异步备份了,下面我们来给大家介绍主从服务器配置步骤. Mysql的主从复制至少是需要两个Mysql的服务,当然Mysql的服务是可以分布在不同的服务器上,也可以在一台服务器上启动多个服务. (1)首先确保主从服务器上的Mysql版本相同 (2)在主服务器上,设置一个从数据库的账户,使用R

  • MySQL延时复制库方法详解

    简单的说延迟复制就是设置一个固定的延迟时间,比如1个小时,让从库落后主库一个小时. MySQL延时复制库作用 存在即合理,延迟复制可以用来解决以下三类问题:  1.用来在数据库误操作后,快速的恢复数据. 比如有人误操作的表,那么这个操作在延迟时间内,从库的数据并没有发生变化,可以用从库的数据进行快速恢复. 把服务停了,把从库的表直接导入主库,补binlog,binglog不能恢复表  2.用来做延迟测试 比如你做好的数据库读写分离,把从库作为读库,那么你想知道当数据产生延迟的时候到底会发生什么.

  • MySQL临时表的使用方法详解

    MySQL临时表的使用方法详解

    目录 1. 写在前面的话 2. 临时表的使用 2.1 创建一个只存放亚洲国家信息的临时表 2.1.1 创建临时表 2.1.2 向临时表里写数据 2.2 在查询过程中直接创建临时表 2.3 查询临时表中的数据 2.4 删除临时表 3. 以上操作的全部代码 总结 1. 写在前面的话 在开发数据库时,特别是写存储过程,遇到比较复杂的需求,使用临时表可以简化很多逻辑.曾经在一家互联网金融公司供职,公司数据组团队做数据清洗,写SQL脚本时,一个查询语句可以套到数层查询,甚至十几层.看起来几百行上千行的脚本

随机推荐