如何強制 JOIN 使用 MySQL 中的特定索引？

我有JOINs2 個表的查詢；lineitem和part,

select
       sum(l_extendedprice* (1 - l_discount)) as revenue
from
       lineitem force index for join (l_pk),
       part
where
       (
               p_partkey = l_partkey
               and p_brand = 'Brand#12'
               and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
               and l_quantity >= 1 and l_quantity <= 1 + 10
               and p_size between 1 and 5 
               and l_shipmode in ('AIR', 'AIR REG')
               and l_shipinstruct = 'DELIVER IN PERSON'
       )
       or
       (
               p_partkey = l_partkey
               and p_brand = 'Brand#23'
               and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
               and l_quantity >= 10 and l_quantity <= 10 + 10
               and p_size between 1 and 10
               and l_shipmode in ('AIR', 'AIR REG')
               and l_shipinstruct = 'DELIVER IN PERSON'
       )
       or
       (
               p_partkey = l_partkey
               and p_brand = 'Brand#34'
               and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
               and l_quantity >= 20 and l_quantity <= 20 + 10
               and p_size between 1 and 15
               and l_shipmode in ('AIR', 'AIR REG')
               and l_shipinstruct = 'DELIVER IN PERSON'
       );

我在 lineitem 上有一個索引，並希望查詢使用此索引進行連接p_partkey = l_partkey

create index l_pk on tpch.lineitem(l_partkey);

MySQLexplain顯示：

| id | select_type | table    | type   | possible_keys | key     | key_len | ref                     | rows    | Extra       |       
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+---------+-------------+
|  1 | SIMPLE      | lineitem | ALL    | l_pk          | NULL    | NULL    | NULL                    | 5982534 | Using where | 
|  1 | SIMPLE      | part     | eq_ref | PRIMARY       | PRIMARY | 4       | tpch.lineitem.L_PARTKEY |       1 | Using where | 
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+---------+-------------+

為什麼l_pk不使用索引？

l_pk由於表的連接方式，未使用索引。

要使用索引，我們需要在該索引中查找一些內容。

連接兩個表的時候，左表有一個值，需要和右表對應的行進行匹配，所以我們使用右表的索引來查找匹配的行——或者，有一個值在右表，我們需要將它與左表中的行匹配，因此我們使用左表上的索引來查找匹配的行。

您不使用兩個表上的索引來執行連接 - 您使用一個或另一個。在這種情況下，主鍵part被用於連接。

那麼，下一個問題是“為什麼？”

為了回答這個問題，我們考慮您的查詢要求伺服器做什麼。

由伺服器執行的這兩個過程中的任何一個都會產生相同的結果：

你要求伺服器…

1. 根據您為 l_quantity、l_shipmode 和 l_shipinstruct … 提供的值查找 lineitem 中的所有行，然後加入部分匹配的所有行，但僅連接那些也具有 p_brand、p_container 和 p_size 預期值的行… 要麼
2. 部分基於 p_brand、p_container 和 p_size 查找行，然後辨識 lineitem 中存在 l_quantity、l_shipmode、l_shipinstruct 匹配值的匹配行。

您possible_keys每個僅包含一個索引，這表明您正在搜尋的列… p_brand、p_container、p_size、l_quantity、l_shipmode、l_shipinstruct … 都沒有索引。

所以伺服器將別無選擇，只能對一個表或另一個表進行全表掃描。它之所以選擇，lineitem是因為它得出的結論是，這將是最具成本效益的路線。

真正的問題似乎是您正在搜尋應該被索引的列。

即使您正在使用FORCE INDEX查詢優化器按預期接管。

表中的通過lineitem超過了總索引條目的 5% 以上。這就是導致查詢優化器放棄使用索引的原因。Oracle、MSSQL 和 PostgreSQL 不會有任何不同。

這是我重構查詢的病態嘗試

select sum(l_extendedprice* (1 - l_discount)) as revenue
from
(
   select p_partkey l_partkey,'S' contsize from part
   where p_brand = 'Brand#12'
   and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
   and p_size between 1 and 5 
   union
   select p_partkey,'M' contsize from part
   where p_brand = 'Brand#23'
   and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
   and p_size between 1 and 10
   union
   select p_partkey,'L' contsize from part
   where p_brand = 'Brand#34'
   and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
   and p_size between 1 and 15
) partkeys
left join listitem using (l_partkey)
where
   l_shipmode in ('AIR', 'AIR REG') and
   l_shipinstruct = 'DELIVER IN PERSON' and
   IF(contsize='S',IF(l_quantity >= 1 and l_quantity <= 1 + 10,1,0),
       IF(contsize='M',IF(l_quantity >= 10 and l_quantity <= 10 + 10,1,0),
           IF(contsize='L',IF(l_quantity >= 20 and l_quantity <= 20 + 10,1,0),0)
       )
   )
;

您可能需要索引表

ALTER TABLE parts ADD INDEX brand_container_size_ndx
(p_brand,p_container,p_size,p_partkey);
ALTER TABLE listitem ADD INDEX partkey_shipinstruct_shipmode_quantity_ndx
(l_partkey,l_shipinstruct,l_shipmode ,l_quantity);

試一試，讓我們知道它是否很快或者是否有效。

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