php - 慢速 SQL 查询 - 找不到

Question

好的，所以我的托管公司已经第四次暂停我的帐户了。这让我很烦，因为他们说的代码导致了问题:

# Mon Mar  5 11:00:00 2012
# Query_time: 4.028706  Lock_time: 0.000272 Rows_sent: 15  Rows_examined: 12188513 use futureg2_imbc; 

SELECT uploadsNew.id   ,
    uploadsNew.title   , uploadsNew.genre   , uploadsNew.content   ,
    uploadsNew.url   , uploadsNew.approved, (IF(v.views IS NOT NULL,
    v.views, 0) + IF(vc.old_views IS NOT NULL, vc.old_views, 0)) AS views,
    r.likes   , r.dislikes FROM uploadsNew   
  LEFT JOIN    
    (SELECT id   ,
      COUNT(*) AS views   
     FROM views   
    WHERE type = '0' AND subtype = '1'  
    GROUP BY id   
    ) AS v   
  ON v.id = uploadsNew.id   
  LEFT JOIN   
    (SELECT
      id   , SUM(views) AS old_views   
    FROM viewsCondensed   
    WHERE type = '0' AND subtype = '1'   
    GROUP BY id   
    ) AS vc   
  ON vc.id = uploadsNew.id   
  LEFT JOIN   
    (SELECT upload   , SUM(IF(rating = '1', 1, 0)) AS likes   , 
      SUM(IF(rating = '-1', 1, 0)) AS dislikes   ,
      IF(username = '', rating, 0) AS user_rated   
    FROM ratingNew   
    WHERE ratingNew.type = '0'   
    GROUP BY upload ) AS r   
  ON r.upload = uploadsNew.id   
  WHERE uploadsNew.type = '1'   AND uploadsNew.status ='0'   AND 
    uploadsNew.school = 'illinois-state-university'   
GROUP BY
  uploadsNew.id ORDER BY uploadsNew.approved DESC LIMIT 15

甚至无法在我的页面上运行。即使每次都更改我的代码并查看 100 次，这仍然是一个问题，它是完全相同的代码，每秒运行多次，每次他们都暂停我的帐户。

这是 PHP 代码:

$sql = "SELECT uploadsNew.id
    , uploadsNew.title
    , uploadsNew.genre
    , uploadsNew.content
    , uploadsNew.url
    , uploadsNew.approved";
if($type < 3) $sql .= ", (IF(v.views IS NOT NULL, v.views, 0) + IF(vc.old_views IS NOT NULL, vc.old_views, 0)) AS views";
else $sql .= ", uploadsNew.member
    , uploadsNew.anonymous
    , r.ratedSong";
$sql .= ", r.likes
    , r.dislikes";
if($sort == "rated") $sql .= ", (r.likes - r.dislikes) AS rating";
if(isset($school)) $sql .= ", s.school_id";
$sql .= " FROM uploadsNew";
if(isset($school)) $sql .= " LEFT JOIN (SELECT url, id AS school_id FROM schools) AS s ON s.url = '". $school ."'";
$sql .= " LEFT JOIN 
            (SELECT id
                , COUNT(*) AS views
            FROM views
            WHERE type = '0' AND subtype = '". $type ."'
            GROUP BY id
            ) AS v
            ON v.id = uploadsNew.id
        LEFT JOIN
            (SELECT id
                , SUM(views) AS old_views
            FROM viewsCondensed
            WHERE type = '0' AND subtype = '". $type ."'
            GROUP BY id
            ) AS vc
            ON vc.id = uploadsNew.id
        LEFT JOIN
            (SELECT upload
                , SUM(IF(rating = '1', 1, 0)) AS likes
                , SUM(IF(rating = '-1', 1, 0)) AS dislikes
                , IF(username = '". $user['username'] ."', rating, 0) AS user_rated
            FROM ratingNew
            WHERE ratingNew.type = '0'
            GROUP BY upload
            ) AS r
            ON r.upload = uploadsNew.id
        WHERE uploadsNew.type = '". $type ."' AND uploadsNew.status = '0'";
if($genre) $sql .= " AND uploadsNew.genre = '". strtolower($genre) ."'";
if(isset($school)) $sql .= " AND uploadsNew.school = s.school_id";
else $sql .= $filter;
$sql .= " GROUP BY uploadsNew.id ORDER BY ". $s ." LIMIT ". ($page - 1) * $limit .", ". $limit;

如果有人甚至可以弄清楚上面引用的代码是如何从那个单一查询中运行的——请放心。另外，如果你能弄清楚它是如何每秒运行多次(就像它是循环的一样)，我会更爱你。

此外，上述方法是否有效？我有另一个关于这个的线程(以及一般的数据库)并且没有人回答我的问题。

支持人员几乎没有给我任何帮助，并且一直在向我推荐一些显而易见的事情供我查看。我觉得最重要的是因为 viewsCondensed 表大约有 80k 个东西。

基本上，viewsCondensed 表用于将所有内容(在 views 表中)的每日 View 压缩为完整的每日总和 (viewsCondensed)。

我应该将其更改为每周一次还是每月一次？我曾经让所有这些只是 uploadsNew 表中的一部分，尽管我觉得这有点低效并且不允许每天保存实际数据。

我们将不胜感激任何帮助!

---

抱歉，这里有更多关于 SELECT 以及各种表的 EXPLAIN 数据:

这是一个正常查询，它在前一个“运行”的页面上运行:

SELECT uploadsNew.id
  , uploadsNew.title
  , uploadsNew.genre
  , uploadsNew.content
  , uploadsNew.url
  , uploadsNew.approved, (IF(v.views IS NOT NULL, v.views, 0) + IF(vc.old_views IS NOT NULL, vc.old_views, 0)) AS views, r.likes
  , r.dislikes FROM uploadsNew
  LEFT JOIN 
  (SELECT id
  , COUNT(*) AS views
  FROM views
  WHERE type = '0' AND subtype = '1'
  GROUP BY id
  ) AS v
  ON v.id = uploadsNew.id
  LEFT JOIN
  (SELECT id
  , SUM(views) AS old_views
  FROM viewsCondensed
  WHERE type = '0' AND subtype = '1'
  GROUP BY id
  ) AS vc
  ON vc.id = uploadsNew.id
  LEFT JOIN
  (SELECT upload
  , SUM(IF(rating = '1', 1, 0)) AS likes
  , SUM(IF(rating = '-1', 1, 0)) AS dislikes
  , IF(username = '', rating, 0) AS user_rated
  FROM ratingNew
  WHERE ratingNew.type = '0'
  GROUP BY upload
  ) AS r
  ON r.upload = uploadsNew.id
  WHERE uploadsNew.type = '1'
  AND uploadsNew.status = '0'
  GROUP BY uploadsNew.id ORDER BY uploadsNew.approved DESC LIMIT 15

解释以上内容:

1 PRIMARY uploadsNew     ref type,type_2               type_2 8 const,const 1965 Using temporary; Using filesort
1 PRIMARY <derived2>     ALL NULL                      NULL   NULL NULL     1335
1 PRIMARY <derived3>     ALL NULL                      NULL   NULL NULL     5429
1 PRIMARY <derived4>     ALL NULL                      NULL   NULL NULL      372
4 DERIVED ratingNew      ALL NULL                      NULL   NULL NULL     2111 Using where; Using temporary; Using filesort
3 DERIVED viewsCondensed ref type,type_2,type_3,type_4 type_2 8            67475 Using where; Using temporary; Using filesort
2 DERIVED views        index type                      id_2   12   NULL     4351 Using where; Using index

解释最初的“问题”查询:

1 PRIMARY uploadsNew ref type,type_2 type_2 8 const,const 1896 Using where; Using temporary; Using filesort 1 PRIMARY ALL NULL NULL NULL NULL 479 1 PRIMARY ALL NULL NULL NULL NULL 6015
1 PRIMARY ALL NULL NULL NULL NULL 384 4 DERIVED ratingNew ALL NULL NULL NULL NULL 2171 Using where; Using temporary; Using filesort 3 DERIVED viewsCondensed ref type,type_2,type_3,type_4 type_3 4 53779 Using where; Using temporary; Using filesort 2 DERIVED views ref type type 4 688 Using where; Using temporary; Using filesort

View 表:

CREATE TABLE views ( id int(10) NOT NULL DEFAULT '0', type int(1) NOT NULL DEFAULT '0', subtype int(1) NOT NULL DEFAULT '0', date datetime NOT NULL, ip int(20) NOT NULL DEFAULT '0', user varchar(20) NOT NULL, KEY id (id,type), KEY id_2 (id,type,subtype), KEY id_3 (id,type,date), KEY type (type,ip) ) ENGINE=MyISAM DEFAULT CHARSET=latin1

viewsCondensed table:

CREATE TABLE viewsCondensed ( id int(10) NOT NULL DEFAULT '0', type int(1) NOT NULL DEFAULT '0', subtype int(1) NOT NULL DEFAULT '0', date date NOT NULL, views int(10) NOT NULL DEFAULT '0', KEY id (id,type), KEY id_2 (id,type,subtype), KEY id_3 (id,type,date), KEY type (type,views), KEY type_2 (type,subtype,views), KEY type_3 (type,date,views), KEY type_4 (type) ) ENGINE=MyISAM DEFAULT CHARSET=latin1

上传新表:

CREATE TABLE uploadsNew ( id int(10) NOT NULL AUTO_INCREMENT, member varchar(30) NOT NULL, ip int(20) NOT NULL, gallery varchar(30) NOT NULL, type int(1) NOT NULL, genre varchar(30) NOT NULL, anonymous int(1) NOT NULL, school int(6) NOT NULL, added datetime NOT NULL, approved datetime NOT NULL, title varchar(255) NOT NULL, content varchar(2500) NOT NULL, url varchar(300) NOT NULL, address varchar(40) NOT NULL, tags varchar(200) NOT NULL, rating int(1) NOT NULL, status int(1) NOT NULL, source varchar(600) NOT NULL, PRIMARY KEY (id), KEY id (id,member,status), KEY type (type,genre,approved,rating,status), KEY type_2 (type,status) ) ENGINE=MyISAM AUTO_INCREMENT=6004 DEFAULT CHARSET=latin1

rating新表:

CREATE TABLE ratingNew ( upload int(10) NOT NULL, type int(1) NOT NULL DEFAULT '0', username varchar(20) NOT NULL, ip int(16) NOT NULL, rating int(1) NOT NULL, date datetime NOT NULL, KEY upload (upload,type) ) ENGINE=MyISAM DEFAULT CHARSET=latin1

---

更多编辑(尝试新的查询和解释):

新查询

SELECT 
    uploadsNew.id,     uploadsNew.title, 
    uploadsNew.genre,  uploadsNew.content,
    uploadsNew.url,    uploadsNew.approved, 
    COALESCE(v.views, 0) + COALESCE(vc.old_views, 0) AS views,
    r.likes,           r.dislikes 
FROM  ( SELECT *
        FROM uploadsNew
        WHERE type = 1  
          AND status = 0  
        ORDER BY approved DESC 
        LIMIT 15
      ) AS uploadsNew  
  LEFT JOIN    
      ( SELECT  id,  COUNT(*) AS views   
        FROM views   
        WHERE type = 0 AND subtype = 1  
        GROUP BY id   
      ) AS v   ON v.id = uploadsNew.id   
  LEFT JOIN   
      ( SELECT  id,  SUM(views) AS old_views   
        FROM viewsCondensed   
        WHERE type = 0 AND subtype = 1   
        GROUP BY id   
      ) AS vc  ON vc.id = uploadsNew.id   
  LEFT JOIN   
      ( SELECT  upload, 
                SUM(rating = 1 ) AS likes, 
                SUM(rating = -1) AS dislikes,
                IF(username = '', rating, 0) AS user_rated   
        FROM ratingNew   
        WHERE type = 0   
        GROUP BY upload 
      ) AS r   ON r.upload = uploadsNew.id   
ORDER BY uploadsNew.approved DESC 

解释

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    15  Using temporary; Using filesort
1   PRIMARY <derived3>  ALL NULL    NULL    NULL    NULL    479 
1   PRIMARY <derived4>  ALL NULL    NULL    NULL    NULL    6015    
1   PRIMARY <derived5>  ALL NULL    NULL    NULL    NULL    384 
5   DERIVED ratingNew   index   NULL    upload_3    34  NULL    2171    Using where; Using index
4   DERIVED viewsCondensed  ref type,type_2,type_3,type_4   type_3  4       53779   Using where; Using temporary; Using filesort
3   DERIVED views   ref type    type    4       688 Using where; Using temporary; Using filesort
2   DERIVED uploadsNew  range   type,type_2,type_3,type_4   type_4  4   NULL    5970    Using where

Answer 1

1. uploadsNew 的PRIMARY KEY 是什么？是 id 吗？如果是，请删除 GROUP BY uploadsNew.id。它应该给出相同的结果。

2. 您在表格上有哪些索引？如果还没有，请添加:

   • 表 views 中 (type, subtype, id) 的索引
   • (type, subtype, id, views) 表 viewsCondensed 中的索引。
   • (type, upload, rating) 表 ratingNew 中的索引。
   • (type, status, school, approved) 表 uploadsNew 中的索引。

3. 然后，(先不要运行查询)，但使用 EXPLAIN 语句获取查询计划并将其发布在这里。如果您添加表的定义(这样我们就知道您拥有的数据类型和索引)也很好。

4. 您的多个表没有PRIMARY KEY。这不是很好，但这不是这 2 个查询缓慢的原因，所以让我们暂时忘记它(但您应该稍后处理)。

5. 您有多个冗余索引，但这也不是上述查询性能低下的原因，所以我们也跳过它(但您也应该稍后再处理它)。

6. 添加我在上面评论 2 中输入的索引。唯一可能不是最好的是表 ratingNew 中的 (type, upload, rating)。它可能必须是:(type, upload, username, rating) 而不是，但如果该表没有很多行，现在就不会成为问题。

7. 您的代码会生成多个查询变体。所以，你们也必须添加这个索引:(type, status, approved) in table uploadsNew。

然后，首先尝试对这个变体执行 EXPLAIN，然后运行它:

SELECT 
    uploadsNew.id,     uploadsNew.title, 
    uploadsNew.genre,  uploadsNew.content,
    uploadsNew.url,    uploadsNew.approved, 
    COALESCE(v.views, 0) + COALESCE(vc.old_views, 0) AS views,
    r.likes,           r.dislikes 
FROM  ( SELECT *
        FROM uploadsNew
        WHERE type = 1  
          AND status = 0  
          AND school = 'illinois-state-university'   
        ORDER BY approved DESC 
        LIMIT 15
      ) AS uploadsNew  
  LEFT JOIN    
      ( SELECT  id,  COUNT(*) AS views   
        FROM views   
        WHERE type = 0 AND subtype = 1  
        GROUP BY id   
      ) AS v   ON v.id = uploadsNew.id   
  LEFT JOIN   
      ( SELECT  id,  SUM(views) AS old_views   
        FROM viewsCondensed   
        WHERE type = 0 AND subtype = 1   
        GROUP BY id   
      ) AS vc  ON vc.id = uploadsNew.id   
  LEFT JOIN   
      ( SELECT  upload, 
                SUM(rating = 1 ) AS likes, 
                SUM(rating = -1) AS dislikes,
                IF(username = '', rating, 0) AS user_rated   
        FROM ratingNew   
        WHERE type = 0   
        GROUP BY upload 
      ) AS r   ON r.upload = uploadsNew.id   
ORDER BY uploadsNew.approved DESC