限制偏移量查询的MySQL查询优化

Question

我有一个特别的问题：

SELECT users.role, users.first_name, users.last_name, users.email,
   projects.project_id, projects.reminder, projects.title, projects.user_id,
   COUNT(DISTINCT CASE WHEN citations.deleted=0 THEN citations.citation_id ELSE NULL END) AS nr_citations,
   COUNT(DISTINCT CASE WHEN citations.deleted=1 THEN citations.citation_id ELSE NULL END) AS nr_citations_deleted,
   COUNT(DISTINCT CASE WHEN citations.deleted=0 AND authors.first_name != "" AND authors.last_name !="" AND authors.last_name NOT LIKE "author_lastname%" AND authors.last_name NOT LIKE "author_firstname%" THEN citations.citation_id ELSE NULL END) AS nr_citations_filled,
   COUNT(DISTINCT CASE WHEN citations.deleted=0 AND citations.user_comment IS NOT NULL THEN citations.citation_id ELSE NULL END) AS nr_comments,
   (CASE WHEN user_stats.type IN (4,66,67,68,73,74) THEN user_stats.type ELSE NULL END) AS source,
   COUNT(DISTINCT CASE WHEN user_stats.type=1 THEN user_stats.id ELSE NULL END) AS nr_export_word,
   MAX(CASE WHEN user_stats.type=1 THEN user_stats.timestamp ELSE NULL END) AS last_export_word,
   COUNT(DISTINCT CASE WHEN user_stats.type=3 THEN user_stats.id ELSE NULL END) AS nr_export_email,
   MAX(CASE WHEN user_stats.type=3 THEN user_stats.timestamp ELSE NULL END) AS last_export_email,
   MAX(export_format_class_name) as exported_style
FROM projects
LEFT JOIN projects_styles ON projects_styles.project_id = projects.project_id
LEFT JOIN users ON users.user_id = projects.user_id
LEFT JOIN user_stats ON user_stats.project_id = projects.project_id
LEFT JOIN citations ON citations.project_id = projects.project_id
LEFT JOIN citations_authors ON citations_authors.citation_id = citations.citation_id
LEFT JOIN authors ON authors.author_id = citations_authors.author_id
GROUP BY projects.project_id
ORDER BY projects.project_id DESC
LIMIT 0,4000;

对于低偏移量，工作良好，但对于12000或16000的偏移量，它的工作非常糟糕。我知道这是正常的，但是时间随着每个偏移量呈指数增长，我认为这不是正常的事情。我猜我的查询并不像我想的那样是最优的。

稍后编辑:以下是对我的查询的解释：

id,select_type,table,type,possible_keys,key,key_len,ref,rows,filtered,Extra
"1","SIMPLE","projects","index",NaN,"PRIMARY","4",NaN,"102","55850.00",""
"1","SIMPLE","projects_styles","ref","projects_styles_project_id_index","projects_styles_project_id_index","4","citelighter.projects.project_id","1","100.00",""
"1","SIMPLE","users","eq_ref","PRIMARY","PRIMARY","4","citelighter.projects.user_id","1","100.00",""
"1","SIMPLE","user_stats","ref","user_stats_project_id_index","user_stats_project_id_index","5","citelighter.projects.project_id","13","100.00",""
"1","SIMPLE","citations","ref","citations_project_id_index","citations_project_id_index","4","citelighter.projects.project_id","3","100.00",""
"1","SIMPLE","citations_authors","ref","citations_authors_citation_id_index","citations_authors_citation_id_index","4","citelighter.citations.citation_id","1","100.00",""
"1","SIMPLE","authors","eq_ref","PRIMARY","PRIMARY","4","citelighter.citations_authors.author_id","1","100.00",""

Answer 1

下面是如何查看查询的执行计划。只需在查询前面加上EXPLAIN或EXPLAIN EXTENDED关键字。这是查看查询是否命中您认为命中的索引，或者是否必须执行全表扫描等的好方法。有关输出的示例，请参阅EXPLAIN的MySQL文档。这将是您在优化查询时要采取的第一步：

EXPLAIN EXTENDED SELECT users.role, users.first_name, users.last_name, users.email,
    projects.project_id, projects.reminder, projects.title, projects.user_id,
    COUNT(DISTINCT CASE WHEN citations.deleted=0 THEN citations.citation_id ELSE NULL END) AS nr_citations,
    COUNT(DISTINCT CASE WHEN citations.deleted=1 THEN citations.citation_id ELSE NULL END) AS nr_citations_deleted,
    COUNT(DISTINCT CASE WHEN citations.deleted=0 AND authors.first_name != "" AND authors.last_name !="" AND authors.last_name NOT LIKE "author_lastname%" AND authors.last_name NOT LIKE "author_firstname%" THEN citations.citation_id ELSE NULL END) AS nr_citations_filled,
    COUNT(DISTINCT CASE WHEN citations.deleted=0 AND citations.user_comment IS NOT NULL THEN citations.citation_id ELSE NULL END) AS nr_comments,
    (CASE WHEN user_stats.type IN (4,66,67,68,73,74) THEN user_stats.type ELSE NULL END) AS source,
    COUNT(DISTINCT CASE WHEN user_stats.type=1 THEN user_stats.id ELSE NULL END) AS nr_export_word,
    MAX(CASE WHEN user_stats.type=1 THEN user_stats.timestamp ELSE NULL END) AS last_export_word,
    COUNT(DISTINCT CASE WHEN user_stats.type=3 THEN user_stats.id ELSE NULL END) AS nr_export_email,
    MAX(CASE WHEN user_stats.type=3 THEN user_stats.timestamp ELSE NULL END) AS last_export_email,
    MAX(export_format_class_name) as exported_style
FROM projects
LEFT JOIN projects_styles ON projects_styles.project_id = projects.project_id
LEFT JOIN users ON users.user_id = projects.user_id
LEFT JOIN user_stats ON user_stats.project_id = projects.project_id
LEFT JOIN citations ON citations.project_id = projects.project_id
LEFT JOIN citations_authors ON citations_authors.citation_id = citations.citation_id
LEFT JOIN authors ON authors.author_id = citations_authors.author_id
GROUP BY projects.project_id
ORDER BY projects.project_id DESC
LIMIT 0,4000;

Answer 2

它很慢，因为在返回所需的行之前，它需要计算所有这些偏移量行。您可以尝试在唯一键上添加WHERE条件，但这假设键中没有间隙，这是用于导出到csv的unlikely.But，只需创建一个新的唯一列并使用它即可。

WHERE  unique_col > 10000 ORDER BY unique_col LIMIT 4000;