Mysql join查询的优化方法

Mysql4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接JOIN替代。

join的实现原理

join的实现是采用Nested Loop Join算法，就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果有多个join，则将前面的结果集作为循环数据，再一次作为循环条件到后一个表中查询数据。

接下来通过一个三表join查询来说明mysql的Nested Loop Join的实现方式。

 代码如下 复制代码

select m.subject msg_subject, c.content msg_content
from user_group g,group_message m,group_message_content c
where g.user_id = 1
and m.group_id = g.group_id
and c.group_msg_id = m.id

使用explain看看执行计划：

 代码如下 复制代码

explain select m.subject msg_subject, c.content msg_content from user_group g,group_message m,
group_message_content c where g.user_id = 1 and m.group_id = g.group_id and c.group_msg_id = m.id\G;结果如下：

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: g
type: ref
possible_keys: user_group_gid_ind,user_group_uid_ind,user_group_gid_uid_ind
key: user_group_uid_ind
key_len: 4
ref: const
rows: 2
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: PRIMARY,idx_group_message_gid_uid
key: idx_group_message_gid_uid
key_len: 4
ref: g.group_id
rows: 3
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ref
possible_keys: idx_group_message_content_msg_id
key: idx_group_message_content_msg_id
key_len: 4
ref: m.id
rows: 2

Extra:从结果可以看出，explain选择user_group作为驱动表，首先通过索引user_group_uid_ind来进行const条件的索引ref查找，然后用user_group表中过滤出来的结果集group_id字段作为查询条件，对group_message循环查询，然后再用过滤出来的结果集中的group_message的id作为条件与group_message_content的group_msg_id进行循环比较查询，获得最终的结果。

这个过程可以通过如下代码来表示：

 代码如下 复制代码

for each record g_rec in table user_group that g_rec.user_id=1{
     for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{
          for each record c_rec in group_message_content that c_rec.group_msg_id=m_rec.id
                pass the (g_rec.user_id, m_rec.subject, c_rec.content) row
          combination to output;
      }
}

如果去掉group_message_content表上面的group_msg_id字段的索引，执行计划会有所不一样。

 代码如下 复制代码

drop index idx_group_message_content_msg_id on group_message_content;
explain select m.subject msg_subject, c.content msg_content from user_group g,group_message m,
group_message_content c where g.user_id = 1 and m.group_id = g.group_id and c.group_msg_id = m.id\G;

得到的执行计划如下：

 代码如下 复制代码
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: g
type: ref
possible_keys: user_group_uid_ind
key: user_group_uid_ind
key_len: 4
ref: const
rows: 2
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: PRIMARY,idx_group_message_gid_uid
key: idx_group_message_gid_uid
key_len: 4
ref: g.group_id
rows: 3
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 96

Extra:Using where;Using join buffer因为删除了索引，所以group_message_content的访问从ref变成了ALL，keys相关的信息也变成了NULL，Extra信息也变成了Using Where和Using join buffer，也就是说需要获取content内容只能通过对全表的数据进行where过滤才能获取。Using join buffer是指使用到了Cache，只有当join类型为ALL，index，rang或者是index_merge的时候才会使用join buffer，它的使用过程可以用下面代码来表示：

 代码如下 复制代码

for each record g_rec in table user_group{
      for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{
           put (g_rec, m_rec) into the buffer
           if (buffer is full)
                 flush_buffer();
      }
}
flush_buffer(){
      for each record c_rec in group_message_content that c_rec.group_msg_id = c_rec.id{
            for each record in the buffer
                 pass (g_rec.user_id, m_rec.subject, c_rec.content) row combination to output;
      }
      empty the buffer;
}

在实现过程中可以看到把user_group和group_message的结果集放到join buffer中，而不用每次user_group和group_message关联后马上和group_message_content关联，这也是没有必要的；需要注意的是join buffer中只保留查询结果中出现的列值，它的大小不依赖于表的大小，我们在伪代码中看到当join buffer被填满后，mysql将会flush buffer。

假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

 代码如下 复制代码
SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

 
如果使用连接JOIN来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

 代码如下 复制代码
SELECT * FROM customerinfo
    LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID
    WHERE salesinfo.CustomerID IS NULL

 
连接JOIN之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

当两个表相交时常常使用left join，当表中内容较多时往往结果数据集会非常大，甚至造成无法运行的后果。
数据库提示如下错误信息

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

解决方法1：按照提示执行相应代码，我测试了

SET SQL_BIG_SELECTS=1

当执行sql语句之前新执行以上语句，查询可以顺利进行。

解决方法2：给做关联的两个字段添加索引，程序也顺利执行了。

解决方法3：修改sql语句，减小结果集。

 代码如下 复制代码

修改前

SELECT w . * , r.sound_url
FROM sti_words AS w
LEFT JOIN sti_word_list_ja AS r ON w.word = r.word
WHERE w.insert_date = '2010-11-16'
AND w.user_id =54
GROUP BY w.word
ORDER BY w.id
LIMIT 0 , 30

修改后

select ww.*,r.sound_url
from(
SELECT w . *
FROM sti_words AS w
WHERE w.insert_date = '2010-11-16'
AND w.user_id =54
) as ww
LEFT JOIN sti_word_list_ja AS r ON ww.word = r.word
GROUP BY ww.word
ORDER BY ww.id
LIMIT 0 , 30

join语句的优化总结

1. 用小结果集驱动大结果集，尽量减少join语句中的Nested Loop的循环总次数；

2. 优先优化Nested Loop的内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；

3. 对被驱动表的join字段上建立索引；

4. 当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size。

时间： 2024-08-23 11:46:17

Mysql join查询的优化方法的相关文章

MySQL order by性能优化方法实例

  这篇文章主要介绍了MySQL order by性能优化方法实例,本文讲解了MySQL中order by的原理和优化order by的三种方法,需要的朋友可以参考下 前言 工作过程中,各种业务需求在访问数据库的时候要求有order by排序.有时候不必要的或者不合理的排序操作很可能导致数据库系统崩溃.如何处理好order by排序呢?本文从原理以及优化层面介绍 order by . 一 MySQL中order by的原理 1 利用索引的有序性获取有序数据 当查询语句的 order BY 条件和

MySQL延迟关联性能优化方法

  这篇文章主要介绍了MySQL延迟关联性能优化方法,本文讲解了延迟关联的背景.延迟关联的分析.延迟关联的解决等内容,需要的朋友可以参考下 [背景] 某业务数据库load 报警异常,cpu usr 达到30-40 ,居高不下.使用工具查看数据库正在执行的sql ,排在前面的大部分是: 代码如下: SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, b

MySQL慢查询 mysqldumpslow分析方法

mysql教程优化的第一步应该做的就是排查问题,找出瓶颈,而通常情况下的瓶颈和问题都需要通过观察mysql的运行情况来进行分析,<查看mysql运行状况>这篇文章罗列了一些常用的查看mysql运行信息的方式. 而对于大多数的程序员来说,最容易发现并解决的问题就是mysql的慢查询或者没有利用索引的查询,所以这里主要给大家介绍如何利用官方的mysqldumps教程low工具方便的查看这些信息.如何打开mysql的慢查询,请点击 最简单的方式,在php教程myadmin直接点击状态查看,详细如下图

mysql服务器常见的优化方法

1.查询时,能不用* 就不用,尽量写全字段名. 2.索引不是越多越好,每个表控制在6个索引以内.范围where条件的情况下,索引不起作用,比如where value<100 3.大部分情况连接效率远大于子查询,但是有例外.当你对连接查询的效率都感到不能接受的时候可以试试用子查询,虽然大部分情况下你会更失望,但总有碰到惊喜的时候不是么- 4.多用explain 和 profile分析查询语句 5.有时候可以1条大的SQL可以分成几个小SQL顺序执行,分了吧,速度会快很多. 6.每隔一段时间用alt

mysql慢查询的分析方法

本文主要介绍的是MySQL慢查询分析方法,前一段日子,我曾经设置了一次记录在MySQL数据库中对慢于1秒钟的SQL语句进行查询.想起来有几个十分设置的方法,有几个参数的名称死活回忆不起来了,于是重新整理一下,自己做个笔记. 对于排查问题找出性能瓶颈来说,最容易发现并解决的问题就是MySQL慢查询以及没有得用索引的查询. OK,开始找出MySQL中执行起来不"爽"的SQL语句吧. MySQL慢查询分析方法一: 这个方法我正在用,呵呵,比较喜欢这种即时性的. MySQL5.0以上的版本可以

MySQL Order By索引优化方法_Mysql

尽管 ORDER BY 不是和索引的顺序准确匹配,索引还是可以被用到,只要不用的索引部分和所有的额外的 ORDER BY 字段在 WHERE 子句中都被包括了. 使用索引的MySQL Order By 下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分: 复制代码 代码如下: SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; SELECT * FROM t1 WHERE key_part1=constant ORD

mysql随机查询的优化

mysql随机查询最常见的写法如下: 1 SELECT * FROM tablename ORDER BY RAND() LIMIT 1 php手册上如此解释: About selecting random rows from a MySQL table: SELECT * FROM tablename ORDER BY RAND() LIMIT 1 works for small tables, but once the tables grow larger than 300,000 reco

多条件数据库查询的优化方法

在数据库编程中,管理人员需要经常从数据库中查询数据.当查询条件为确定时,我们可以明确用的SQL语句来实现,但是当查询条件为多个条件的动态组合时,查询语句会由于分支太多及IF语句的多重嵌套而变得相当复杂.在此,笔者提供了一种优化方法,运用本方法可以有效地减少查询语句的分支和数量以及IF条件语句的嵌套层数,从而提高程序的运行效率. 下面我们以一个简单的例子来说明,假设有一个名为employee的表,现在我们要从其中查询数据,条件有三个,由用户动态选择,如图1所示: 其中条件A.B.C之间是与的关系,

千万级的mysql数据库与sql优化方法

对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使