在使用查询语句时,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的,而后把查询结果放到了临时表里(在内存里面),最后按照文件对结果进行排序!这就是致使查询慢的缘由。(若是你不了解上图中每一列的意思,请点击:/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条数据!