儘管有索引，但帶有額外標準的 PK 內部連接很慢？

鑑於下面的兩個表格，我很難理解：

• 為什麼即使前兩個查詢很快，第三個查詢也很慢
• EXPLAIN 到底在說什麼
• 我可以做些什麼來顯著加快慢查詢？

在 PK 上加入兩個表很快：

mysql> select sql_no_cache p.id, sv.postProcessed 
      from product_views p, site_visits sv 
      where p.siteVisitId=sv.id 
      limit 1;
+----+---------------+
| id | postProcessed |
+----+---------------+
|  1 |             1 |
+----+---------------+
1 row in set (0.10 sec)

所以只是按時間戳範圍選擇 PV：

mysql> select sql_no_cache p.id, p.timestamp 
      from product_views p 
      where p.timestamp >= "2012-10-10" 
        and p.timestamp < "2012-11-10" 
      limit 1;
+-----------+---------------------+
| id        | timestamp           |
+-----------+---------------------+
| 501719231 | 2012-10-10 00:01:03 |
+-----------+---------------------+
1 row in set (0.56 sec)

但加入兩者真的很慢（需要 5 min+ ）：

mysql> select sql_no_cache p.id, p.timestamp, sv.postProcessed 
      from product_views p, site_visits sv 
      where p.siteVisitId=sv.id 
        and p.timestamp >= "2012-10-10" 
        and p.timestamp < "2012-11-10" 
      limit 1;

這是解釋

mysql> explain select sql_no_cache p.id, p.timestamp, sv.postProcessed from product_views p, site_visits sv where p.siteVisitId=sv.id and p.timestamp >= "2012-10-10" and p.timestamp < "2012-11-10" limit 1;
+----+-------------+-------+--------+------------------------------------------------------------+--------------------+---------+---------------------+-----------+--------------------------+
| id | select_type | table | type   | possible_keys                                              | key                | key_len | ref                 | rows      | Extra                    |
+----+-------------+-------+--------+------------------------------------------------------------+--------------------+---------+---------------------+-----------+--------------------------+
|  1 | SIMPLE      | p     | index  | FK52C29B1E3CAB9CC4,timestamp_idx,siteVisitId_timestamp_idx | FK52C29B1E3CAB9CC4 | 8       | NULL                | 119195469 | Using where; Using index |
|  1 | SIMPLE      | sv    | eq_ref | PRIMARY                                                    | PRIMARY            | 8       | clabs.p.siteVisitId |         1 |                          |
+----+-------------+-------+--------+------------------------------------------------------------+--------------------+---------+---------------------+-----------+--------------------------+
2 rows in set (0.10 sec)

問題

• 我希望最後一個查詢的執行速度與前兩個加在一起的速度差不多：1）在給定的時間戳內辨識 product_view 和 2）對匹配的 site_visit 行進行持續查找。在該時間戳範圍內 product_views 中有 < 95m 行，不確定為什麼要掃描 120M…
• 上面的解釋似乎說沒有使用“timestamp_idx”。為什麼不？（我猜 mysqld 正在對按時間戳匹配的 product_views 進行全分區掃描）
• 我嘗試添加一個（siteVisitId，timestamp）索引來覆蓋“WHERE”中使用的所有屬性——但這也沒有被使用。為什麼？
• 我能做些什麼來加快速度？

關於我們的數據庫的注意事項：

• 兩個表都是 100M+ 行
• 每個 product_view 都只有一個 siteVisit。（FK 被移除以適應 InnoDB 分區約束）
• 使用 mysql 5.5
• 沒有針對數據庫伺服器的其他流量

表格

mysql> show create table site_visits\G
*************************** 1. row ***************************
      Table: site_visits
Create Table: CREATE TABLE `site_visits` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `postProcessed` tinyint(1) NOT NULL,
 `siteVisitState` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `post_processed_idx` (`postProcessed`),
 KEY `visit_state_idx` (`siteVisitState`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1


mysql> show create table product_views\G
*************************** 1. row ***************************
      Table: product_views
Create Table: CREATE TABLE `product_views` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `timestamp` datetime NOT NULL,
 PRIMARY KEY (`id`,`timestamp`),
 KEY `FK52C29B1E3CAB9CC4` (`siteVisitId`),
 KEY `rebateSearchZipCode_idx` (`rebateSearchZipCode`),
 KEY `siteVisitId_timestamp_idx` (`siteVisitId`,`timestamp`),
 KEY `timestamp_idx` (`timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(`timestamp`)
(PARTITION p0 VALUES LESS THAN ('2012-05-01') ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN ('2012-06-01') ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN ('2012-07-01') ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN ('2012-08-01') ENGINE = InnoDB,
/* partition declarations truncated */
PARTITION p33 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

優化器看不到您的條件是相關的，因此選擇了錯誤的訪問方法。

基本上，它考慮了兩種選擇：

1. 掃描索引siteVisitId直到第一個匹配site_visits和第一個滿足timestamp條件。
2. 掃描索引timestamp直到第一個匹配site_visits。

由於timestamp是主鍵的一部分而siteVisitId不是，第二個計劃將涉及product_views比純索引掃描慢幾倍的表查找（Using index計劃中的註釋）。

優化器計算滿足條件的條件機率timestamp（假設存在相應的site_visit記錄）並將其與表訪問的成本進行比較。

由於您的timestamp條件非常廣泛（如索引直方圖所示），優化器更喜歡第一種方法。

然而，由於兩者siteVisitId都是timestamp增量的，它們是相關的，並且兩個匹配的條件機率不僅僅是它們獨立機率的乘積。

簡而言之，您必須過濾許多低點siteVisitId，直到找到第一個匹配項timestamp，這正是您的查詢發生的情況。

您應該添加ORDER BY timestamp到查詢中以使timestamp索引更便宜，因為它不必排序。它還有助於timestamp, siteVisitId（按此順序）創建索引以避免表查找。

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