mysql inner join 长数字_关于mysql inner join 链接查询的优化

在使用查询语句时，inner join,left join,right join等这些联接语句，想必你们并不陌生，并且常常会用到，联接达到了咱们想要的结果，但是当数据越大越大的时候，会发现查询很慢，每每只是加索引进行优化，但是有时候，索引并不能解决问题，这时就一筹莫展了！mysql

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

CREATE TABLE joye_rank (spring

rank_id int(10) NOT NULL AUTO_INCREMENT,sql

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

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

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

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

rank_regtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,.net

PRIMARY KEY (rank_id),orm

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.000s！OK，如今咱们知道问题了，开始进行优化，我的进行优化的第一步使用EXPLAIN命令，查看mysql是如何执行的，结果以下：

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

下面问题就是，我明明建了rank_score,rank_uploadtime的索引，为何这里使用文件排序？可是索引它确实又用到了！百思不得其解，以前说到，去掉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_uploaduserFROM view_rank as rankINNER JOIN joye_person as person ON person.person_id=rank.rank_personId ;

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

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

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

第一行为3：全表示扫描person表，共3条数据，没有任何条件,因此主键索引，没用上！

第二行为100：全表扫描from子句中的子查询，共100条数，因是派生表，因此没有索引！

第三行为13488：经过索引score扫表rank表，共扫出13488条数据！