关于mysql inner join 连接查询的优化

时间:2022-09-23 13:07:25

在使用查询语句时,inner join,left join,right join等这些联接语句,想必大家并不陌生,而且经常会用到,联接达到了我们想要的结果,可是当数据越大越大的时候,会发现查询很慢,往往只是加索引进行优化,可是有时候,索引并不能解决问题,这时就束手无策了!

 

下面以真实的例子说明这个问题,下面有二张表,一张rank(排行榜),10000余条数据,一张person(人物)3条数据

CREATE TABLE joye_rank (

  rank_id int(10) NOT NULL AUTO_INCREMENT,

  rank_personId int(10) NOT NULL, '玩家上传分数使用的人物ID'

  rank_score int(10) NOT NULL COMMENT '最高分',

  rank_uploaduser varchar(50) NOT NULL COMMENT '上传用户',

  rank_uploadtime datetime NOT NULL COMMENT '上传时间',

  rank_regtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (rank_id),

  UNIQUE KEY idx_uploaduser (rank_uploaduser) USING BTREE,

  KEY idx_score_time (rank_score,rank_uploadtime) USING BTREE,

  KEY idx_personid (rank_personId) USING BTREE

)

 

CREATE TABLE joye_person(

  person_id int(10) NOT NULL AUTO_INCREMENT,

  person_name varchar(10) NOT NULL COMMENT '人物名称',

person_sort int(10) NOT NULL COMMENT '排列序号',

  person_regtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (person_id),

)

 

接下来,有个需求是这样,查询排名前100的用户有哪些,此时需要返回人物名称,分数,上传用户,按时最高分倒序排列,如果最高分相同则按人物排列序号(person_sort字段,暂时不考虑用它排序)升序排列,最后再按照上传时间倒序排列。

 

先说下我的思路,在这里,我需要二个字段进行排序,rank_score,rank_uploadtime,为了使查询更快,我需要以rank_score,rank_uploadtime建立联合索引,以免mysq使用文件排序,(using filesort)以下就是这个需求的SQL语句:

 

SELECT rank.rank_score,rank.rank_uploaduser FROM joye_rank AS rank

INNER JOIN joye_person as person ON rank.rank_personId=person_id WHERE rank_score > 0

ORDER BY rank.rank_score DESC,rank_uploadtime DESC LIMIT 100;

 

看完这条SQL语句,相信大家都知道这条SQL完全没有问题,可以达到我们相要的结果,

 

可是平均查询费时0.010s!如果我去掉SQL语句中的第二行,即inner join这一行,会发现,平均查询费时0.000sOK,现在我们知道问题了,开始进行优化,个人进行优化的第一步使用EXPLAIN命令,查看mysql是如何执行的,结果如下:

 关于mysql inner join 连接查询的优化

就注意红圈处,这里的意思是:mysql先通过where条件过滤掉最高分小于0的,然后把查询结果放到了临时表里(在内存里面),最后按照文件对结果进行排序!这就是导致查询慢的原因。(如果你不了解上图中每一列的意思,请点击:http://blog.csdn.net/spring_yyy/article/details/12971183)。

 

下面问题就是,我明明建了rank_score,rank_uploadtime的索引,为什么这里使用文件排序?但是索引它确实又用到了!百思不得其解,之前说到,去掉inner join查询会变快,现在看看,去掉它是什么情况:

 关于mysql inner join 连接查询的优化

 

这里,我们发现,没有inner join时,没有使用文件排序,我们大致知道原因所在了,那么,为什么用了inner join后,mysql用使用文件排序呢?

 

Mysql在遇到inner join联接语句时,MySQL表关联的算法是 Nest Loop Join嵌套联接循环),Nest Loop Join就是通过两层循环手段进行依次的匹配操作,最后返回结果集合。SQL语句只是描述出希望连接的对象和规则,而执行计划和执行操作要切实际将一行行的记录进行匹配。Nest Loop Join的操作过程很简单,很像我们最简单的排序检索算法,两层循环结构。进行连接的两个数据集合(数据表)分别称为外侧表(驱动表)和内侧表(非驱动表)。Mysql又会怎样去确定,哪张表是驱动表,哪张表又是非非驱动表呢?mysql它以表中数据最小的一张表作为驱动表(也就是基表),而另一张表就叫做非驱动表,首先处理驱动表中每一行符合条件的数据,之后的每一行数据和非驱动表进行连接匹配操作,直到循环结束,最后合并结果、返回结果给用户。对于驱动表的字段它是可以直接排序的,然而对于非驱动表的字段排序需要通过循环查询的合并结果(临时表)进行排序,因此,order by rak_score desc,rank_uploadtime desc 时,就先产生了 using temporary(使用临时表),而我们都知道,数据库的底层就是IO操作,数据库无法对表排序,所以最后要借助于磁盘,那么产生using filesort(使用文件排序)就不难理解了!任何问题都是有解决方法的,接下来就是要解决这个问题,要达到相同的结果,使用一条SQL语句,那么该如何解决这个问题呢?

 

回想一下,前面说过,在查询中去掉inner join,它就会对排序走索引,不会出现文件排序,既然是这样,试想一下,我们是不是可以把去掉inner join的语句在子查询中先查出来(在这里也称派生表),然后在用这个结果去做两张表的关联呢?

SELECT person_id,person_name,rank.rank_person,rank.rank_score,rank.rank_uploaduser FROM(

      SELECT rank.rank_personId,rank.rank_score,rank.rank_uploaduser

      FROM joye_rank AS rank

      WHERE rank_score > 0

      ORDER BY rank_score DESC,rank_uploadtime DESC LIMIT 100

) tmp

INNER JOIN joye_person as person ON person.person_id=tmp.rank_personId ;

 

这条SQL,首先在form中的子查询采用没有inner join的语句查出结果,这个结果会产生临时表,存在于内存当中,我们在内存当中就对它进行排好序,所以不会出现using temporary,using filesort最后在用这个临时表tmp去关联person表,在tmp 后面的语句只是一个以inner join以及联接条件on的简单语句,并没有排序和其它操作。所以,最后可以完全可以肯定,mysql不会产生问题using temporary,using filesort,那这样问题就已经得到解决了!

上面红色的部份mysql在执行时会产生派生表,因为它是form子句中的子查询!由于它存在于内存当中,所以使用的时候,千万不要用*号,只取需要用到的数据,以免占用太多内存!

同时也要避免使用派生表,最好采用其他方式来编写查询语句,大部分情况都比派生表来的快。很多情况下,甚至连独立的临时表都来的快,因为派生表无法增加索引。

可以考虑使用视图来取代派生表如果确实需要在 FROM子句中使用到子查询,可以考虑在查询时创建试图,当查询完之后删除试图。

所以,上面的SQL也可以用以下的视图来解决

首先创建视图,

CREATE VIEW view_rank AS SELECT rank.rank_personId,rank.rank_score,rank.rank_uploaduser

       FROM joye_rank AS rank

       WHERE rank_score > 0

       ORDER BY rank_score DESC,rank_uploadtime DESC LIMIT 100;

改写后的SQL语句:

SELECT person_id,person_name,person_sort,person_head,rank_score,rank_uploaduser
FROM view_rank as rank INNER JOIN joye_person as person ON person.person_id=rank.rank_personId ;

 注意,上面这条SQL语句from后面跟的是视图名而不是表名

用以上两条SQL,最终产生的结果一下!用EXPLAIN命令查看一下它的执行计划:

 

关于mysql inner join 连接查询的优化

这里,我们发现已经没有using temporary, using filesort说明我们已经解决了这个问题,最后看看rows这一列的数据.

第一行为3:全表示扫描person表,共3条数据,没有任何条件,所以主键索引,没用上!

第二行为100:全表扫描from子句中的子查询,共100条数,因是派生表,所以没有索引!

第三行为13488:通过索引score扫表rank表,共扫出13488条数据!