在MySQL中使用STRAIGHT_JOIN的教程

问题

通过「SHOW FULL PROCESSLIST」语句很容易就能查到问题SQL，如下：

SELECT post.*
FROM post
INNER JOIN post_tag ON post.id = post_tag.post_id
WHERE post.status = 1 AND post_tag.tag_id = 123
ORDER BY post.created DESC
LIMIT 100

说明：因为post和tag是多对多的关系，所以存在一个关联表post_tag。

试着用EXPLAIN查询一下SQL执行计划(篇幅所限，结果有删减)：

+----------+---------+-------+-----------------------------+
| table  | key   | rows | Extra            |
+----------+---------+-------+-----------------------------+
| post_tag | tag_id | 71220 | Using where; Using filesort |
| post   | PRIMARY |   1 | Using where         |
+----------+---------+-------+-----------------------------+

下面给出优化后的SQL，唯一的变化就是把连接方式改成了「STRAIGHT_JOIN」：

SELECT post.*
FROM post
STRAIGHT_JOIN post_tag ON post.id = post_tag.post_id
WHERE post.status = 1 AND post_tag.tag_id = 123
ORDER BY post.created DESC
LIMIT 100

试着用EXPLAIN查询一下SQL执行计划(篇幅所限，结果有删减)：

+----------+----------------+--------+-------------+
| table  | key      | rows  | Extra    |
+----------+----------------+--------+-------------+
| post   | status_created | 119340 | Using where |
| post_tag | post_id    |   1 | Using where |
+----------+----------------+--------+-------------+

对比优化前后两次EXPLAIN的结果来看，优化后的SQL虽然「rows」更大了，但是没有了「Using filesort」，综合来看，性能依然得到了提升。
解释

对第一条SQL而言，为什么MySQL优化器选择了一个耗时的执行方案？对第二条SQL而言，为什么把连接方式改成STRAIGHT_JOIN之后就提升了性能？

这一切还得从MySQL对多表连接的处理方式说起，首先要确定以谁为驱动表，也就是说以哪个表为基准，在处理此类问题时，MySQL优化器采用了简单粗暴的解决方法：哪个表的结果集小，就以哪个表为驱动表，通常这都是最佳选择。

说明：在EXPLAIN结果中，第一行出现的表就是驱动表。

继续post连接post_tag的例子，MySQL优化器有如下两个选择，分别是：

  1. 以post为驱动表，通过status_created索引过滤，结果集119340行
  2. 以post_tag为驱动表，通过tag_id索引过滤，结果集71220行

显而易见，post_tag过滤的结果集更小，所以MySQL优化器选择它作为驱动表，可悲催的是我们还需要以post表中的created字段来排序，也就是说排序字段不在驱动表里，于是乎不可避免的出现了「Using filesort」，从而导致慢查询。

知道了来龙去脉，优化起来就容易了。头等大事是务必保证排序字段在驱动表中，所以必须以post是驱动表，于是乎「STRAIGHT_JOIN」就成了答案，它强制了连接顺序。

…

不过我总觉得「STRAIGHT_JOIN」这种非标准的语法属于奇技淫巧的范畴，能不用尽量不用，毕竟多数情况下，MySQL优化器都能做出正确的选择。

赞 (0)

相关推荐

  • Mysql中Join的使用实例详解

    在前几章节中,我们已经学会了如果在一张表中读取数据,这是相对简单的,但是在真正的应用中经常需要从多个数据表中读取数据. 本章节我们将向大家介绍如何使用MySQL 的 JOIN 在两个或多个表中查询数据. 你可以在SELECT, UPDATE 和 DELETE 语句中使用Mysql 的 join 来联合多表查询. 以下我们将演示MySQL LEFT JOIN 和 JOIN 的使用的不同之处. 在命令提示符中使用JOIN 我们在RUNOOB数据库中有两张表 tcount_tbl 和 runoob_t

  • MySQL中视图的使用及多表INNER JOIN的技巧分享

    创建视图   Sql代码 CREATE VIEW view_name AS SELECT t1.xxx, t2.xxx, t3.xxx FROM (table1 t1 INNER JOIN table2 t2 ON t1.fid = t2.fid) INNER JOIN table3 t3 ON t1.mid = t3.mid; 这里使用了3表关联,对于多表关联的 INNER JOIN 写法有一个技巧 1. 先写最简单的2表关联 INNER JOIN 2. 然后使用 () 从 FROM 之后到语

  • MySQL中union和join语句使用区别的辨析教程

    MySQL中union和join语句使用区别的辨析教程

    union和join是需要联合多张表时常见的关联词,具体概念我就不说了,想知道上网查就行,因为我也记不准确. 先说差别:union对两张表的操作是合并数据条数,等于是纵向的,要求是两张表字段必须是相同的(Schema of both sides of union should match.).也就是说如果A表中有三条数据,B表中有两条数据,那么A union B就会有五条数据.说明一下union 和union all的差别,对于union如果存在相同的数据记录会被合并,而union all不会合

  • 深入理解mysql之left join 使用详解

    ON 子句与 WHERE 子句的不同    一种更好地理解带有 WHERE ... IS NULL 子句的复杂匹配条件的简单方法    Matching-Conditions 与 Where-conditions 的不同 关于 "A LEFT JOIN B ON 条件表达式" 的一点提醒 ON 条件("A LEFT JOIN B ON 条件表达式"中的ON)用来决定如何从 B 表中检索数据行. 如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列

  • 在MySQL中使用JOIN语句进行连接操作的详细教程

    到目前,我们已经学习了从一个表中获取数据.这是简单的需要,但在大多数现实MySQL的使用,经常需要将数据从多个表中的一个单一的查询. 可以使用多个表中的单一SQL查询.在MySQL中联接(join)行为是指两个或多个表到一个表中可以使用连接在SELECT,UPDATE和DELETE语句中加入MySQL表.我们将看到一个例子LEFT JOIN简单的MySQL连接. 在命令提示符使用联接: 假设我们两个表的教程tcount_tbl和tutorials_tbl的完整列表如下: 例子: 试试下面的例子:

  • MYSQL使用inner join 进行 查询/删除/修改示例

    复制代码 代码如下: --查询 SELECT tp.tp_id, tp.tpmc, tp.leveid, tp.tpdz, tp.jgm, tp.scsj, tp.pbzyid, tp.ksbfsj, tp.jsbfsj, tp.status, tp.tpbz FROM qdgl_tupian tp INNER JOIN qdgl_pqb pq ON tp.tp_id=pq.tpid WHERE pq.bfjgm='27010825' AND ps_bfsj >= '2013-01' AND p

  • MySQL在右表数据不唯一的情况下使用left join的方法

    MySQL在右表数据不唯一的情况下使用left join的方法

    1.left join 基本用法 mysql left join 语句格式 A LEFT JOIN B ON 条件表达式 left join 是以A表为基础,A表即左表,B表即右表. 左表(A)的记录会全部显示,而右表(B)只会显示符合条件表达式的记录,如果在右表(B)中没有符合条件的记录,则记录不足的地方为NULL. 例如:news 与 news_category表的结构如下,news表的category_id与news_category表的id是对应关系. news 表 news_categ

  • 解析mysql left( right ) join使用on与where筛选的差异

    解析mysql left( right ) join使用on与where筛选的差异

    有这样的一个问题mysql查询使用mysql中left(right)join筛选条件在on与where查询出的数据是否有差异.可能只看着两个关键字看不出任何的问题.那我们使用实际的例子来说到底有没有差异. 例如存在两张表结构表结构1 复制代码 代码如下: drop table if EXISTS A;  CREATE TABLE A (    ID int(1) NOT NULL,    PRIMARY KEY  (ID)  ) ENGINE=MyISAM DEFAULT CHARSET=lat

  • 在MySQL中使用STRAIGHT_JOIN的教程

    问题 通过「SHOW FULL PROCESSLIST」语句很容易就能查到问题SQL,如下: SELECT post.* FROM post INNER JOIN post_tag ON post.id = post_tag.post_id WHERE post.status = 1 AND post_tag.tag_id = 123 ORDER BY post.created DESC LIMIT 100 说明:因为post和tag是多对多的关系,所以存在一个关联表post_tag. 试着用E

  • 详解在MySQL中创建表的教程

    创建表的命令要求: 表的名称 表字段名称 每个字段的定义 语法: 下面是通用的SQL语法来创建一个MySQL表: CREATE TABLE table_name (column_name column_type); 现在,我们将创建下面的教程数据库表中. tutorials_tbl( tutorial_id INT NOT NULL AUTO_INCREMENT, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40)

  • MySQL中基本的多表连接查询教程

    一.多表连接类型 1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用','  如: 由于其返回的结果为被连接的两个数据表的乘积,因此当有WHERE, ON或USING条件的时候一般不建议使用,因为当数据表项目太多的时候,会非常慢.一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN 2.   内连接INNER JOIN 在MySQL中把I SELECT * FROM table1 CROSS JOIN tabl

  • MySQL中对表连接查询的简单优化教程

    在MySQL中,A LEFT JOIN B join_condition执行过程如下: · 根据表A和A依赖的所有表设置表B. · 根据LEFT JOIN条件中使用的所有表(除了B)设置表A. · LEFT JOIN条件用于确定如何从表B搜索行.(换句话说,不使用WHERE子句中的任何条件). · 可以对所有标准联接进行优化,只是只有从它所依赖的所有表读取的表例外.如果出现循环依赖关系,MySQL提示出现一个错误. · 进行所有标准WHERE优化. · 如果A中有一行匹配WHERE子句,但B中没

  • MySQL中一些优化straight_join技巧

    在oracle中可以指定的表连接的hint有很多:ordered hint 指示oracle按照from关键字后的表顺序来进行连接:leading hint 指示查询优化器使用指定的表作为连接的首表,即驱动表:use_nl hint指示查询优化器使用nested loops方式连接指定表和其他行源,并且将强制指定表作为inner表. 在mysql中就有之对应的straight_join,由于mysql只支持nested loops的连接方式,所以这里的straight_join类似oracle中

  • Mysql中禁用与启动触发器教程【推荐】

    在使用MYSQL过程中,经常会使用到触发器,但是有时使用不当会造成一些麻烦.有没有一种办法可以控制触发器的调用呢? 触发器顾名思义就是数据库在一定的调条件自动调用的SQL语句,触发器拒绝了人工调用的过程,由数据库MYSQL数据库自动的调用,执行更加高效. 如何禁用触发器呢? 1.新建两张表: 表demo_1: CREATE TABLE `demo_1` ( `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `STUNAME` varchar(3

  • MySQL中explain语句的基本使用教程

    MySQL中explain语句的基本使用教程

    一.概述 在 MySQL 中,我们可以使用慢查询日志或者 show processlist 命令等方式定位到执行耗时较长的 SQL 语句,在这之后我们可以通过 EXPLAIN或者 DESC 命令获取 MySQL 如何执行 SELECT 语句 的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序 1.explain语句测试 explain+select语句即 explain 命令的使用方式 2.explain结果中各列的说明 下面,将对这些列逐一进行讲解 二.explain之id列

  • 将sqlite3中数据导入到mysql中的实战教程

    前言 sqlite3只小巧轻便,但是并不支持并发访问,当网站并发量较大时候,数据库请求队列边长,有可能导致队列末尾去数据库操作超时,从而操作失败.因此需要切换到支持并发访问的数据库.切换数据库需要将老的数据导出,再导入到新的数据库中,但是sqlite3和mysql的数据库并不完全兼容,需要做部分调整才能正常导入到mysql中.我最近工作中就遇到了这个问题. 最近一个项目中使用magenetico抓取磁力链接,由于它使用的是sqlite3, 文件会越来越大,而且不支持分布式:所以需要将其改造成My

  • MySQL中的唯一索引的简单学习教程

    mysql 唯一索引UNIQUE一般用于不重复数据字段了我们经常会在数据表中的id设置为唯一索引UNIQUE,下面我来介绍如何在mysql中使用唯一索引UNIQUE吧. 创建唯一索引的目的不是为了提高访问速度,而只是为了避免数据出现重复.唯一索引可以有多个但索引列的值必须唯一,索引列的值允许有空值.如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该使用关键字UNIQUE. 把它定义为一个唯一索引. 创建表时直接设置: DROP TABLE IF EXISTS `st

随机推荐