php - Mysql查询需要很多时间来执行

Question

我正在开发一个时间表应用程序，并编写一个 PHP 代码来获取截至日期的所有时间表。这是我为获取时间表而编写的查询 -

SELECT a.accnt_name, u.username, DATE_FORMAT(t.in_time, '%H:%i') inTime, DATE_FORMAT(t.out_time, '%H:%i') outTime, DATE_FORMAT(t .work_time, '%H:%i') workTime, w.wrktyp_name, t.remarks, DATE_FORMAT(t.tmsht_date, '%d-%b-%Y') tmshtDate, wl.loctn_name, s.serv_name, t. status_code, t.conv_kms convkms, t.conv_amount convamount FROM timesheets t, accounts a, services s, worktypes w, work_location wl, users WHERE a.accnt_code=t.accnt_code and w.wrktyp_code=t.wrktyp_code and wl.loctn_code=t 用户.loctn_code 和 s.serv_code=t.serv_code 和 t.usr_code = u。按 tmsht_date desc 排序

where 子句包含从各个表中获取各个代码的实际值的子句。

问题是执行此查询需要花费大量时间，并且应用程序会在几分钟后崩溃。

我在 phpmyadmin 中运行了这个查询，它在那里运行没有任何问题。

需要帮助了解执行缓慢背后的可能原因。

Answer 1

使用 EXPLAIN 查看查询的执行计划。确保 MySQL 有合适的可用索引，并且正在使用这些索引。

查询文本似乎缺少此处的列名...

  t.usr_code = u.    ORDER
                 ^^^

我们可以“猜测”它应该是 u.usr_code，但这只是一个猜测。

应该返回多少行？结果集有多大？

您的客户端是否试图将所有行“存储”在内存中，并因为内存不足而崩溃？

如果是这样，我建议您避免这样做，并在需要时获取行。

或者，考虑在 WHERE 子句中添加一些额外的谓词以仅返回您需要的行，而不是表中的所有行。

现在是 2015 年。是时候放弃连接操作的老式逗号语法，改用 JOIN 关键字，并将连接谓词从 WHERE 子句移动到 ON 子句。并格式化它。数据库不关心，但它会让需要破译 SQL 语句的可怜人更容易。

  SELECT a.accnt_name
       , u.username
       , DATE_FORMAT(t.in_time  ,'%H:%i') AS inTime
       , DATE_FORMAT(t.out_time ,'%H:%i') AS outTime
       , DATE_FORMAT(t.work_time,'%H:%i') AS workTime
       , w.wrktyp_name
       , t.remarks
       , DATE_FORMAT(t.tmsht_date, '%d-%b-%Y') AS tmshtDate
       , wl.loctn_name
       , s.serv_name
       , t.status_code
       , t.conv_kms      AS convkms
       , t.conv_amount   AS convamount 
    FROM timesheets t
    JOIN accounts a
      ON a.accnt_code = t.accnt_code
    JOIN services s
      ON s.serv_code = t.serv_code 
    JOIN worktypes w
      ON w.wrktyp_code = t.wrktyp_code
    JOIN work_location wl
      ON wl.loctn_code = t.loctn_code 
    JOIN users
      ON u.usr_code = t.usr_code
   ORDER BY t.tmsht_date DESC

格式化日期列的排序非常奇怪。您更有可能希望结果按“日期”顺序返回，而不是按年份之前的月份和日期的字符串顺序返回。 (您真的要先按天值排序，然后再按年排序吗？)

跟进

如果这个完全相同的查询很快完成，整个结果集(大约 720 行)来自不同的客户端(相同的数据库，相同的用户)，那么问题可能不是这个 SQL其他声明。

我们不希望 SQL 语句的执行导致 PHP“崩溃”。

如果要存储整个结果集(例如，使用 mysqli store_result)，则需要有足够的内存。但是选择列表中的十三个表达式看起来都比较短(格式化日期、名称和代码)，我们不希望“备注”超过几 KB。

如其他人所建议的那样，为了调试它，请尝试在查询中添加一个 LIMIT 子句，例如LIMIT 1 并观察行为。

或者，使用虚拟查询进行测试；使用保证返回特定值和特定行数的查询。

  SELECT 'morpheus'             AS accnt_name
       , 'trinity'              AS username
       , '01:23'                AS inTime
       , '04:56'                AS outTime
       , '00:45'                AS workTime
       , 'neo'                  AS wrktyp_name
       , 'yada yada yada'       AS remarks
       , '27-May-2015'          AS tmshtDate
       , 'zion'                 AS loctn_name
       , 'nebuchadnezzar'       AS serv_name
       , ''                     AS status_code
       , '123'                  AS convkms
       , '5678'                 AS convamount 

我怀疑查询不是您所观察到的行为的根本原因。我怀疑问题出在代码的其他地方。

How to debug small programs http://ericlippert.com/2014/03/05/how-to-debug-small-programs/