我正在嘗試在 MariaDB 10.1.26 上執行以下連接查詢作為更複雜查詢的一部分。

select distinct
   project_commits.project_id,
   date_format(created_at, '%x%v1') as week_commit
   from project_commits
   left join commits
   on project_commits.commit_id = commits.id;

兩個連接欄位都被索引。但是，連接涉及對 的完整掃描project_commits和索引查找commits。的輸出證實了這一點EXPLAIN。

+------+-------------+-----------------+--------+---------------+---------+---------+-------------------------------------+------------+-----------------+
| id   | select_type | table           | type   | possible_keys | key     | key_len | ref                                 | rows       | Extra           |
+------+-------------+-----------------+--------+---------------+---------+---------+-------------------------------------+------------+-----------------+
|    1 | SIMPLE      | project_commits | ALL    | NULL          | NULL    | NULL    | NULL                                | 5417294109 | Using temporary |
|    1 | SIMPLE      | commits         | eq_ref | PRIMARY       | PRIMARY | 4       | ghtorrent.project_commits.commit_id |          1 |                 |
+------+-------------+-----------------+--------+---------------+---------+---------+-------------------------------------+------------+-----------------+

這兩個表的大小都比較大：project_commits包含 50 億行和commits8.47 億行。伺服器的記憶體大小也相對較小（16GB）。這可能意味著索引查找會命中（不幸的是磁性）磁碟，因此性能會受到嚴重影響。根據pmonitor run on 生成的臨時表的輸出，已經執行了半天多的查詢，還需要 373 小時才能完成。

/home/mysql/ghtorrent/project_commits#P#p0.MYD 6.68% ETA 373:38:11

我是否可以通過對錶進行分區來提高查詢的性能，以便可以在記憶體中執行連接，或者通過強制 MySQL 執行排序合併連接？由於執行替代策略所涉及的時間可能很多小時，我寧願提出建議，而不是嘗試一些事情。

由於 MariaDB 似乎不支持排序合併連接，我最終將所需欄位導出到兩個排序文件中，使用 Unix連接和uniqJOIN工具執行and並將結果導入回數據庫。手術用了不到 12 個小時就完成了。完整的細節在這裡。DISTINCT

從 EXPLAIN 計劃的外觀來看，您正在對project_commits.

commits從查詢的外觀來看，我推測從to存在一對多的關係project_commits。我看到的問題是您的查詢正在以多對一的方式收集數據。

您也在使用LEFT JOIN.

您的查詢是說：

顯示所有project_commits連接或孤立的commits

相反，您可能希望查詢說：

顯示所有project_commits相關的commits

建議#1：翻轉表順序

EXPLAIN select distinct
   project_commits.project_id,
   date_format(created_at, '%x%v1') as week_commit
   from commits
   left join project_commits
   on commits.id = project_commits.commit_id;

建議#2：使用INNER JOIN

EXPLAIN select distinct
   project_commits.project_id,
   date_format(created_at, '%x%v1') as week_commit
   from project_commits
   inner join commits
   on project_commits.commit_id = commits.id;

建議#3：添加created_at索引

如果您已經有一個索引，created_at或者如果您已經有一個第一列為 的複合索引created_at，請跳過此建議。

ALTER TABLE `project_commits` ADD INDEX created_at_ndx (`created_at`);

添加索引將更改EXPLAIN計劃以進行索引掃描而不是表掃描

建議#4：改變JOIN行為

您可以通過 tweeking 優化器來操縱連接操作的樣式

根據 MySQL Documentation on Block Nested-Loop and Batched Key Access Joins

使用 BKA 時，join_buffer_size 的值定義了對儲存引擎的每個請求中的密鑰批大小。緩衝區越大，對連接操作右側表的順序訪問就越多，這可以顯著提高性能。

要使用 BKA，optimizer_switch 系統變數的 batched_key_access 標誌必須設置為 on。BKA 使用 MRR，因此 mrr 標誌也必須打開。目前，MRR的成本估算過於悲觀。因此，還需要關閉 mrr_cost_based 才能使用 BKA。

同一頁面建議這樣做：

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

試試看 ！！！

注意：我不知道我的建議會有什麼期望。畢竟，你LEFT JOIN就像一個迭代笛卡爾連接，有可能製作一個臨時表，如下所示

正在查找 45.73 Quintillion 行（54 億 X 08.47 億）

玩得開心，讓我們知道您發現了什麼

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