為大型數據庫中一組有限的分塊/分組行選擇最大聚合值？

我致力於實現站點地圖功能的 WordPress 外掛。

其中一個步驟是為文章條目建立站點地圖頁面的索引。所需的邏輯大致是：確定每塊 N 個文章的最後修改時間。

對於 N=1000，生成和執行的查詢結果如下所示（這是古老的遺產，其開發歷史早已失傳）：

SELECT post_modified_gmt FROM (
   SELECT @rownum:=@rownum+1 rownum, wp_posts.post_modified_gmt 
   FROM (SELECT @rownum:=0) r, wp_posts
       WHERE post_status IN ('publish','inherit')
       AND post_type = 'test'
       ORDER BY post_modified_gmt ASC
   ) x 
WHERE rownum %1000 = 0

這在較小的站點上可以正常工作，但在較大的站點（100Ks 文章）上開始變慢，並且在非常大的站點（1M+ 文章）上變得不切實際。

我對 SQL 不是很精通，到目前為止，我嘗試的所有內容都歸結為該查詢需要遍歷每一行，這無法擴展。

嘗試執行多個查詢反而不太順利，因為偏移量也不能很好地擴展，因此對 1M 文章執行 20 次更簡單的查詢將導致更糟糕的結果。

到目前為止，我偶然發現的唯一特殊優化是暗示不使用可用索引，這是違反直覺的，但確實加快了查詢速度：

SELECT post_modified_gmt FROM (
   SELECT @rownum:=@rownum+1 rownum, wp_posts.post_modified_gmt 
   FROM (SELECT @rownum:=0) r, wp_posts USE INDEX()
       WHERE post_status IN ('publish','inherit')
       AND post_type = 'test'
       ORDER BY post_modified_gmt ASC
   ) x 
WHERE rownum %1000 = 0

我可以研究哪些其他方法或技術來優化此類案例？

PS 尋找可以在廣泛的 MySQL 版本（5.0+）上工作的東西，但即使是可以在較新版本（5.6+）上進行漸進增強的東西也會很棒*。*

查詢的解釋（帶索引的版本）：

id  select_type     table       type    possible_keys       key                 key_len ref     rows    Extra
1   PRIMARY         <derived2>  ALL     null                null                null    null    15484   Using where
2   DERIVED         <derived3>  system  null                null                null    null    1       Using filesort
2   DERIVED         wp_posts    ref     type_status_date    type_status_date    82      const   15484   Using index condition; Using where
3   DERIVED         null        null    null                null                null    null    null    No tables used

表 CREATE 語句：

CREATE TABLE `wp_posts` (
   `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
   `post_author` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
   `post_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
   `post_date_gmt` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
   `post_content` LONGTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
   `post_title` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
   `post_excerpt` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
   `post_status` VARCHAR(20) NOT NULL DEFAULT 'publish' COLLATE 'utf8mb4_unicode_ci',
   `comment_status` VARCHAR(20) NOT NULL DEFAULT 'open' COLLATE 'utf8mb4_unicode_ci',
   `ping_status` VARCHAR(20) NOT NULL DEFAULT 'open' COLLATE 'utf8mb4_unicode_ci',
   `post_password` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
   `post_name` VARCHAR(200) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
   `to_ping` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
   `pinged` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
   `post_modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
   `post_modified_gmt` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
   `post_content_filtered` LONGTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
   `post_parent` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
   `guid` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
   `menu_order` INT(11) NOT NULL DEFAULT '0',
   `post_type` VARCHAR(20) NOT NULL DEFAULT 'post' COLLATE 'utf8mb4_unicode_ci',
   `post_mime_type` VARCHAR(100) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
   `comment_count` BIGINT(20) NOT NULL DEFAULT '0',
   PRIMARY KEY (`ID`),
   INDEX `type_status_date` (`post_type`, `post_status`, `post_date`, `ID`),
   INDEX `post_parent` (`post_parent`),
   INDEX `post_author` (`post_author`),
   INDEX `post_name` (`post_name`(191))
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=MyISAM
AUTO_INCREMENT=19833;

查詢版本之間的性能差異：

SELECT rownum, post_modified_gmt FROM (
   SELECT @rownum:=@rownum+1 rownum, wp_posts.post_modified_gmt 
   FROM (SELECT @rownum:=0) r, wp_posts
       WHERE post_status IN ('publish','inherit')
       AND post_type = 'test'
       ORDER BY post_modified_gmt ASC
   ) x 
WHERE rownum %1000 = 0;
/* Affected rows: 0  Found rows: 15  Warnings: 0  Duration for 1 query: 0,218 sec. */

SELECT rownum, post_modified_gmt FROM (
   SELECT @rownum:=@rownum+1 rownum, wp_posts.post_modified_gmt 
   FROM (SELECT @rownum:=0) r, wp_posts USE INDEX()
       WHERE post_status IN ('publish','inherit')
       AND post_type = 'test'
       ORDER BY post_modified_gmt ASC
   ) x 
WHERE rownum %1000 = 0;
/* Affected rows: 0  Found rows: 15  Warnings: 0  Duration for 1 query: 0,046 sec. */

MySQL 中似乎存在一個錯誤（在 sqlfiddle.com 上檢查到 5.6.21），其中優化器在可用時無法辨識最佳索引。它可以通過使用force index()提示來解決：

SELECT post_modified_gmt FROM (
   SELECT @rownum:=@rownum+1 rownum, wp_posts.post_modified_gmt 
   FROM (SELECT @rownum:=0) r, wp_posts FORCE INDEX(type_modified_status)
       WHERE post_status IN ('publish','inherit')
       AND post_type = 'test'
       ORDER BY post_modified_gmt ASC
   ) x 
WHERE rownum %1000 = 0;

最佳索引在哪裡

INDEX `type_modified_status` (`post_type`, `post_modified_gmt`, `post_status`)

該索引支持ref訪問post_type和優化排序post_modified_gmt，post_status可直接用於附加檢查，因此查詢不需要隨機查找主表數據。

即使沒有提示，也正在使用索引force index()，但優化器由於某種原因無法辨識可用性post_status並從表中讀取整行，從而通過大量隨機 IO 減慢查詢執行速度。

http://sqlfiddle.com/#!9/1cc46/4

編輯：經過更多探勘，在ANALYZE TABLE wp_posts;沒有索引提示的情況下使用正確的索引。但是從我收集的內容來看，ANALYZE 並不是適合您的情況的解決方案。

引用自：https://dba.stackexchange.com/questions/139477