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

删除内联 View 查询。不需要。它只会导致 MySQL 做更多的工作。

外部查询中的谓词不会被插入内联 View 中。您实际上是在创建表的副本(派生表)，并针对该副本运行查询。并且该派生表没有任何索引，它对派生表中的每一行进行完整扫描。

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 列；包含 fpaid 列意味着 MySQL 可以完全从索引满足查询，而不需要从基础表中检索 block 。

对于 stuet 表，具有等式谓词的列应该放在第一位。

ON studet (branch, year, academic, course)

(该索引中这四列的顺序并不那么重要；关键是 MySQL 可以通过非常快速地消除检查中的大量行来有效地定位行。)

<小时/>

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

例如:

 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   ) ."'