好的,所以我的托管公司已经第 4 次暂停了我的帐户。这让我很烦恼,因为他们说的代码导致了问题:
# 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;
如果有人能弄清楚上面引用的代码是如何从单个查询中运行的 - 请随意。另外,如果你能弄清楚它是如何每秒运行多次的(就好像它是循环的一样(,我会更爱你。
另外,上述方法是否有效?我有另一个关于这个的线程(以及一般的数据库(,没有人回答过我的问题。
支持给我的帮助很少,并且不断向我推荐显而易见的事情。我觉得最大的事情是因为视图压缩表大约~80k的东西。
基本上,视图压缩表用于将所有内容的每日视图(在视图表中(压缩为完全每日的总和(视图压缩(。
我应该把它改成每周的事情,还是每月的事情?我曾经让所有这些只是上传新表中的一部分,尽管我觉得这有点低效,并且不允许每天保存实际数据。
任何和所有的帮助将不胜感激!
抱歉,以下是 SELECT 以及各种表格上的更多 EXPLAIN 数据:
这是一个 NORMAL 查询,在前一个查询"运行"的页面上运行:
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 主要上传新的参考类型,type_2 type_2 8 常量,常量 1896 使用 哪里;使用临时的;使用文件排序 1 主要 全部 空 空 空 空 479 1 主 全部 空 空 空 空 6015
1 主要 全部 空 空 空 空 384 4 派生评级新 全部空 2171 使用位置;用 临时;使用文件排序 3 派生视图精简引用类型,type_2,type_3,type_4 type_3 4 53779 使用 哪里;使用临时的;使用文件排序 2 派生视图 ref 类型 4 688 使用位置;使用临时的; 使用文件排序
视图表:
创建表
views
(id
INT(10( 不为空默认值 '0',type
int(1( 不为空默认值 '0',subtype
int(1( 不为空的缺省值 '0',date
日期时间不为空,ip
int(20( 不为空默认值"0",user
VARCHAR(20( 不为空,键id
(id
,type
(,键id_2
(id
,type
,subtype
(, 钥匙id_3
(id
,type
,date
(, 钥匙type
(type
,ip
( (引擎=MyISAM 默认字符集=拉丁语1
视图精简表:
创建表
viewsCondensed
(id
INT(10( 不为空默认值 '0',type
int(1( 不是空默认值 '0',subtype
int(1( 不是空默认值 "0",date
日期不为空,views
int(10( 不为空 默认值"0",键id
(id
,type
(, 钥匙id_2
(id
,type
,subtype
(, 钥匙id_3
(id
,type
,date
(, 钥匙type
(type
,views
(, 钥匙type_2
(type
,subtype
,views
(, 键type_3
(type
,date
,views
(, 钥匙type_4
(type
( 引擎=MyISAM 默认字符集=拉丁语1
上传新表:
创建表
uploadsNew
(id
int(10( 不为空AUTO_INCREMENT,member
varchar(30( 不为空,ip
int(20( 不为空,gallery
瓦尔查尔(30( 不为空,type
int(1( 不为空,genre
瓦尔查(30( 不 空,anonymous
int(1( 不为空,school
int(6( 不为空,added
日期时间不为空,approved
日期时间不为空,title
varchar(255( 不为空,content
瓦尔查尔(2500( 不为空,url
瓦尔查尔(300( 不 NULL,address
varchar(40( 不为 NULL,tags
varchar(200( 不为 NULL,rating
int(1( 不为空,status
int(1( 不为空,source
VARCHAR(600( 不为空,主键 (id
(, 键id
(id
,member
,status
(, 钥匙type
(type
,genre
,approved
,rating
,status
(, 键type_2
(type
,status
( (引擎=MyISAM AUTO_INCREMENT=6004 默认值 字符集=拉丁语1
评级新表:
创建表
ratingNew
(upload
int(10( 不为空,type
int(1( 不为空 默认值 '0',username
瓦尔查尔(20( 不为空,ip
INT(16( 不为空,rating
int(1( 不为空,date
日期时间不为空,键upload
(upload
,type
( 引擎=MyISAM 默认字符集=拉丁语1
更多编辑(尝试新查询并解释(:
新建查询
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
-
uploadsNew
的PRIMARY KEY
是什么?是id
吗?如果是,请删除GROUP BY uploadsNew.id
。它应该给出相同的结果。 -
你有什么指数在表上?如果没有,请添加:
- 表
views
中(type, subtype, id)
的索引 - 表
viewsCondensed
中(type, subtype, id, views)
的索引。 - 表
ratingNew
中(type, upload, rating)
的索引。 - 表
uploadsNew
中(type, status, school, approved)
的索引。
- 表
-
然后(暂时不要运行查询(,但使用 EXPLAIN 语句获取查询计划并将其发布到此处。如果您添加表的定义(以便我们知道您拥有的数据类型和索引(,那也会很好。
-
您的几个表没有
PRIMARY KEY
。这不好,但这不是这两个查询缓慢的原因,所以让我们暂时忘记它(但你应该稍后处理(。
您 有几个冗余索引,但这并不是上述查询性能缓慢的原因,因此让我们也跳过它(但您也应该在一段时间后处理这个问题(。
添加我在上面的评论 2 中放置的索引。唯一可能不是最好的是表
ratingNew
中的(type, upload, rating)
.它可能必须是:(type, upload, username, rating)
,但如果该表没有多少行,现在不会有问题。您的代码会生成查询的多个变体。因此,您还必须添加此索引:
(type, status, approved)
在表uploadsNew
中。
首先尝试对此变体进行解释,然后运行它:
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
切勿使用子选择,而应重新格式化它以像标准左连接一样使用它。
我很难像这样解析您的代码,因此,如果您可以向我们粘贴 SQL STRING 构建的结果,我们将能够尽可能多地重新格式化它。