Mysql左外部连接从php响应所花费的时间太长

Question

我的数据库里有近10000条记录。当查询没有连接时，通过PHP发送到mysql的查询工作速度非常快。但是当我们使用左外联接连接表时，它的速度非常慢。

这是我的疑问。

    $sql = "select 
rollno,
stuname,
total,
ifnull(sum(fpaid),0) as feepaid,
total-COALESCE(sum(fpaid),0) as balance,
status,
comments,
category
from 
    (select
        studet.rollno as rollno,
        studet.stuname as stuname,
        studet.course as course,
        studet.branch as branch,
        studet.category as category,
        studet.year as year,
        studet.total as total,
        studet.status as status,
        studet.comments as comments,
        studet.academic as academic ,
        stufeeref.feepaid as fpaid
        from studet
        left outer join stufeeref
        on studet.rollno = stufeeref.rollno
        and studet.year = stufeeref.year
    ) as T
where  branch='$branch'
and year='$year'
and academic='$academic'
and course='$course'
group by rollno,year"; 

请告诉我如何优化我的查询。提前道谢。

Answer 1

首先，为什么要使用子查询？

select s.rollno, s.stuname, s.total, ifnull(sum(sr.fpaid), 0) as feepaid,
       s.total-COALESCE(sum(sr.fpaid),0) as balance,
       s.status, s.comments, s.category
from studet s left outer join
     stufeeref sr
     on s.rollno = sr.rollno and s.year = sr.year
where s.branch='$branch' and s.year='$year' and s.academic='$academic' and s.course='$course'
group by s.rollno, s.year;

现在您的查询可以利用索引。您需要的两个索引是studet（分支，年份，学术，课程）和studeeref（rollno,year,fpayed)。

Answer 2

删除内联视图查询。不需要了。这只会使MySQL做更多的工作。

外部查询中的谓词不会下推到内联视图中。您实际上是创建一个表的副本（派生表），并针对该表运行一个查询。并且派生表没有任何索引,它会对派生表中的每一行进行完整扫描。

SELECT s.rollno
     , s.stuname
     , s.total
     , IFNULL(SUM(r.fpaid),0) AS feepaid
     , s.total-COALESCE(SUM(r.fpaid),0) AS balance
     , s.status
     , s.comments
     , s.category
  FROM studet s
  LEFT
  JOIN stufeeref r
    ON r.rollno = s.rollno
   AND r.year   = s.year
 WHERE s.branch   = '$branch'
   AND s.year     = '$year'
   AND s.academic = '$academic'
   AND s.course   = '$course'
 GROUP BY s.rollno, s.year

为了获得最佳性能，您需要有合适的索引可用。对于stufeeref表，一个覆盖索引：

ON stufeeref (rollno, year, fpaid)

由于连接谓词,您首先需要rollno和year列；包含fpayed列意味着MySQL可以完全从索引中满足查询，而不需要从基础表中检索块。

对于studet表，具有相等谓词的列应该是第一个。

ON studet (branch, year, academic, course)

（索引中这四列的顺序并不重要；关键是MySQL可以通过非常快速地消除大量的行而有效地定位行。）

此外，如果值未正确转义（例如，使用mysqli_real_escape_string函数），则在SQL文本中包含变量是一个SQL注入漏洞。

例如：

 WHERE s.branch   = '". $mysqli->real_escape_string( $branch   ) ."'
   AND s.year     = '". $mysqli->real_escape_string( $year     ) ."'
   AND s.academic = '". $mysqli->real_escape_string( $academic ) ."'
   AND s.course   = '". $mysqli->real_escape_string( $course   ) ."'