mysql join查询优化_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。