我在下面簡化了一個更大的表格：

CREATE TABLE `core` (
 `id` int NOT NULL,
 `loc_country` enum('United States','Colombia','United Kingdom',       
      'Australia','India','Germany','Canada','Korea','Netherlands',
      '200 more')  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
 `loc_city` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
 `job` enum('a','b','c','d') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `loc_country_2` (`loc_country`,`job`,`loc_city`(6))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
         ROW_FORMAT=COMPRESSED




explain format=json
   SELECT id FROM core
       WHERE id!=518601449
         AND loc_country='Mongolia'
         AND id < 518601449
         AND job='a'
       LIMIT 151\G

*************************** 1. row ***************************
EXPLAIN: {
 "query_block": {
   "select_id": 1,
   "cost_info": {
     "query_cost": "14002.99"
   },
   "table": {
     "table_name": "core",
     "access_type": "range",
     "possible_keys": [
       "PRIMARY",
       "loc_country_2"
     ],
     "key": "loc_country_2",
     "used_key_parts": [
       "loc_country",
       "job",
       "loc_city",
       "id"
     ],
     "key_length": "34",
     "rows_examined_per_scan": 45657,
     "rows_produced_per_join": 45657,
     "filtered": "100.00",
     "using_index_for_skip_scan": true,
     "cost_info": {
       "read_cost": "9437.29",
       "eval_cost": "4565.70",
       "prefix_cost": "14002.99",
       "data_read_per_join": "1G"
     },
     "used_columns": [
       "id",
       "loc_country",
       "job"
     ],
     "attached_condition": "((`api`.`core`.`job` = 'a') and (`api`.`core`.`loc_country` = 'Mongolia') and (`api`.`core`.`id` <> 518601449) and (`api`.`core`.`id` < 518601449))"
   }
 }
}

查詢需要 14 秒才能執行，我需要在 0.01 秒內完成

最大的問題似乎是使用 id < XXX 並按 id 排序，鑑於 id 是主鍵，我認為這應該是“免費”使用的。

我需要 id < 並排序，因為我需要在每次查詢時從數據庫中獲取不同的部分，如果我不使用它，我會在每個國家/地區+工作的每個查詢中收到相同的數據

我無法對錶進行分區，因為我有幾十個這樣的查詢使用不同的列，這只是一個例子。

我相信壓縮會產生很大的影響，它可能是我遇到的大多數問題的原因，儘管我的 NVME 磁碟上沒有儲存可以在沒有壓縮的情況下執行。

將主鍵添加到我擁有的索引中是否有幫助？在末尾 ？我擔心它會浪費很多儲存空間。

有任何想法嗎 ？

方面 #1：索引

無需將 PRIMARY KEY 值添加id到索引中。為什麼 ？

根據聚群和二級索引的 MySQL 文件，小標題下的第 1,2 段How Secondary Indexes Relate to the Clustered Index說如下：

除聚集索引外的所有索引都稱為二級索引。在 InnoDB 中，二級索引中的每條記錄都包含行的主鍵列，以及為二級索引指定的列。InnoDB 使用這個主鍵值來搜尋聚集索引中的行。

如果主鍵長，二級索引佔用的空間就更多，所以主鍵短是有利的。

因此，主鍵會自動添加到二級索引。

證明在 XML 輸出中

 "used_key_parts": [
   "loc_country",
   "job",
   "loc_city",
   "id"
 ],

和

 "used_columns": [
   "id",
   "loc_country",
   "job"
 ],

方面 #2：表壓縮

壓縮可能會極大地導致緩慢。畢竟，InnoDB 必須解壓縮來自該表的數據才能將其駐留在 InnoDB 緩衝池中。此外，InnoDB 緩衝池包含壓縮和未壓縮的數據和索引頁面。怎麼會這樣 ？？？

根據 MySQL Documentation on How Compression Works for InnoDB Tables，副標題下的第 1,2 段Compression and the InnoDB Buffer Pool如下：

在壓縮的 InnoDB 表中，每個壓縮頁面（無論是 1K、2K、4K 還是 8K）對應於一個 16K 字節的未壓縮頁面（如果設置了 innodb_page_size，則為更小的大小）。為了訪問頁面中的數據，如果壓縮頁面不在緩衝池中，MySQL 會從磁碟讀取壓縮頁面，然後將頁面解壓縮為其原始形式。本節描述 InnoDB 如何管理關於壓縮表頁面的緩衝池。

為了最小化 I/O 並減少解壓縮頁面的需要，緩衝池有時會同時包含數據庫頁面的壓縮和未壓縮形式。為了為其他所需的數據庫頁面騰出空間，MySQL 可以從緩衝池中逐出未壓縮的頁面，同時將壓縮頁面留在記憶體中。或者，如果一個頁面有一段時間沒有被訪問過，該頁面的壓縮形式可能會被寫入磁碟，為其他數據騰出空間。因此，在任何給定時間，緩衝池都可能包含頁面的壓縮和未壓縮形式，或者僅包含頁面的壓縮形式，或者兩者都不包含。

建議 #1

由於緩衝池包含壓縮和未壓縮的頁面，因此增加 InnoDB 緩衝池大小可能會稍微改善一些情況。如何 ？

InnoDB 必須從緩衝池中逐出未使用的頁面。更大的緩衝池減少了緩衝池必須被清除以產生新頁面的次數。

建議 #2

也許改變壓縮頁面的大小可能會稍微改善一些事情。這將需要使用新的壓縮大小重新載入表。請參閱我的舊文章innodb_file_format 梭子魚關於更改KEY_BLOCK_SIZE將需要。

更新 2020-12-12 19:52

這是一個瘋狂的嘗試

而不是您的查詢

SELECT id FROM core
WHERE id!=518601449
AND loc_country='Mongolia'
AND id < 518601449 AND job='a'  LIMIT 151\G

嘗試重構如下

SELECT id FROM (SELECT id FROM core WHERE loc_country='Mongolia' AND job='a') A 
WHERE id < 518601449 LIMIT 151\G

這將強制 loc_country_2 索引首先在子查詢中收集 id。然後，所有 ID < 518601449 的 ID 都將被取消。最後，施加 LIMIT 151。

我不保證結果會更好。這可能會更糟。

在您嘗試並查看或至少查看 EXPLAIN 計劃之前，您不會知道！

擺脫前綴索引

KEY `loc_country_2` (`loc_country`,`job`,`loc_city`(6))

–>

KEY `loc_country_2` (`loc_country`,`job`)

它很少有益。對於這個特定的查詢，它會妨礙（並且會損害性能）。如果您過於簡化查詢，這裡的任何建議都可能毫無用處。

我需要使用每個查詢從數據庫中獲取不同的部分，

你的意思是“分頁”嗎？通過OFFSET？那會降級。或者也許id是用來“記住你離開的地方”？見http://mysql.rjweb.org/doc.php/pagination

我有幾十個這樣的查詢使用不同的列，

請再給我們幾個例子——否則我們的建議將毫無用處。

請提供SHOW TABLE STATUS LIKE 'core';——我想查看表格大小、行大小和其他一些內容。

在我迄今為止看到的每一種情況下Using index merge intersect，一個合適的複合索引都會更好地工作。

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