Mysql

MySQL 在結合 join 和 range 時不使用整個索引

  • September 1, 2017

我正在嘗試優化連接兩個表並應用範圍條件的簡單查詢。從下面的解釋計劃中，您可以看到索引inv_quantity_on_hand僅被部分使用（4 個字節，僅用於第一列 - inv_item_sk）。我希望使用整個索引，因為索引的第二部分（inv_quantity_on_hand）在範圍條件的 WHERE 子句中使用。

請注意，這只發生在連接和範圍條件下。將範圍條件替換為常量相等比較 (inv_quantity_on_hand = 5) 將更改解釋計劃，MySQL 將使用整個索引。

這似乎是這個錯誤的一個實例：https://bugs.mysql.com/bug.php?id=8569。

我用 MySQL 5.7.18 檢查了它，執行計劃仍然以相同的方式創建。

請問有人能想出一個好的解決方法嗎？

架構結構：

CREATE TABLE `inventory` (
   `inv_date_sk` INT(11) NOT NULL,
   `inv_item_sk` INT(11) NOT NULL,
   `inv_warehouse_sk` INT(11) NOT NULL,
   `inv_quantity_on_hand` INT(11) DEFAULT NULL,
   PRIMARY KEY (`inv_date_sk` , `inv_item_sk` , `inv_warehouse_sk`),
   KEY `inv_w` (`inv_warehouse_sk`),
   KEY `inv_i` (`inv_item_sk`),
   KEY `inv_quantity_on_hand_index` (`inv_item_sk` , `inv_quantity_on_hand`),
   CONSTRAINT `inv_d` FOREIGN KEY (`inv_date_sk`)
       REFERENCES `date_dim` (`d_date_sk`)
       ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `inv_i` FOREIGN KEY (`inv_item_sk`)
       REFERENCES `item` (`i_item_sk`)
       ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `inv_w` FOREIGN KEY (`inv_warehouse_sk`)
       REFERENCES `warehouse` (`w_warehouse_sk`)
       ON DELETE NO ACTION ON UPDATE NO ACTION
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

CREATE TABLE `item` (
   `i_item_sk` INT(11) NOT NULL,
   `i_item_id` CHAR(16) NOT NULL,
   `i_rec_start_date` DATE DEFAULT NULL,
   `i_rec_end_date` DATE DEFAULT NULL,
   `i_item_desc` VARCHAR(200) DEFAULT NULL,
   `i_current_price` DECIMAL(7 , 2 ) DEFAULT NULL,
   `i_wholesale_cost` DECIMAL(7 , 2 ) DEFAULT NULL,
   `i_brand_id` INT(11) DEFAULT NULL,
   `i_brand` CHAR(50) DEFAULT NULL,
   `i_class_id` INT(11) DEFAULT NULL,
   `i_class` CHAR(50) DEFAULT NULL,
   `i_category_id` INT(11) DEFAULT NULL,
   `i_category` CHAR(50) DEFAULT NULL,
   `i_manufact_id` INT(11) DEFAULT NULL,
   `i_manufact` CHAR(50) DEFAULT NULL,
   `i_size` CHAR(20) DEFAULT NULL,
   `i_formulation` CHAR(20) DEFAULT NULL,
   `i_color` CHAR(20) DEFAULT NULL,
   `i_units` CHAR(10) DEFAULT NULL,
   `i_container` CHAR(10) DEFAULT NULL,
   `i_manager_id` INT(11) DEFAULT NULL,
   `i_product_name` CHAR(50) DEFAULT NULL,
   PRIMARY KEY (`i_item_sk`),
   KEY `item_color_index` (`i_color`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

詢問：

SELECT 
   *
FROM
   inventory
       INNER JOIN
   item ON inventory.inv_item_sk = item.i_item_sk
WHERE
   inventory.inv_quantity_on_hand > 100
       AND item.i_color = 'red';

執行計劃：

# id | select_type | table     | partitions | type | possible_keys                    | key                        | key_len | ref                  | rows | filtered |  Extra
-----+-------------+-----------+------------+------+----------------------------------+----------------------------+---------+----------------------+-----------------+-------------------------
1    | SIMPLE      | item      |            | ref  | PRIMARY,item_color_index         | item_color_index           | 61      | const                | 384  | 100.00   |  
1    | SIMPLE      | inventory |            | ref  | inv_i,inv_quantity_on_hand_index | inv_quantity_on_hand_index | 4       | tpcds.item.i_item_sk | 615  |  33.33   | Using where; Using index

JSON執行計劃：

{
 "query_block": {
   "select_id": 1,
   "cost_info": {
     "query_cost": "48745.23"
   },
   "nested_loop": [
     {
       "table": {
         "table_name": "item",
         "access_type": "ref",
         "possible_keys": [
           "PRIMARY",
           "item_color_index"
         ],
         "key": "item_color_index",
         "used_key_parts": [
           "i_color"
         ],
         "key_length": "61",
         "ref": [
           "const"
         ],
         "rows_examined_per_scan": 384,
         "rows_produced_per_join": 384,
         "filtered": "100.00",
         "cost_info": {
           "read_cost": "384.00",
           "eval_cost": "76.80",
           "prefix_cost": "460.80",
           "data_read_per_join": "633K"
         },
         "used_columns": [
           "i_item_sk",
           "i_item_id",
           "i_rec_start_date",
           "i_rec_end_date",
           "i_item_desc",
           "i_current_price",
           "i_wholesale_cost",
           "i_brand_id",
           "i_brand",
           "i_class_id",
           "i_class",
           "i_category_id",
           "i_category",
           "i_manufact_id",
           "i_manufact",
           "i_size",
           "i_formulation",
           "i_color",
           "i_units",
           "i_container",
           "i_manager_id",
           "i_product_name"
         ]
       }
     },
     {
       "table": {
         "table_name": "inventory",
         "access_type": "ref",
         "possible_keys": [
           "inv_i",
           "inv_quantity_on_hand_index"
         ],
         "key": "inv_quantity_on_hand_index",
         "used_key_parts": [
           "inv_item_sk"
         ],
         "key_length": "4",
         "ref": [
           "tpcds.item.i_item_sk"
         ],
         "rows_examined_per_scan": 615,
         "rows_produced_per_join": 78819,
         "filtered": "33.33",
         "using_index": true,
         "cost_info": {
           "read_cost": "987.83",
           "eval_cost": "15763.96",
           "prefix_cost": "48745.23",
           "data_read_per_join": "1M"
         },
         "used_columns": [
           "inv_date_sk",
           "inv_item_sk",
           "inv_warehouse_sk",
           "inv_quantity_on_hand"
         ],
         "attached_condition": "(`tpcds`.`inventory`.`inv_quantity_on_hand` > 100)"
       }
     }
   ]
 }
}

原始查詢中的處理程序計數器：

'Handler_commit', '1'
'Handler_delete', '0'
'Handler_discover', '0'
'Handler_external_lock', '4'
'Handler_mrr_init', '0'
'Handler_prepare', '0'
'Handler_read_first', '0'
'Handler_read_key', '385'
'Handler_read_last', '0'
'Handler_read_next', '249369'
'Handler_read_prev', '0'
'Handler_read_rnd', '0'
'Handler_read_rnd_next', '0'
'Handler_rollback', '0'
'Handler_savepoint', '0'
'Handler_savepoint_rollback', '0'
'Handler_update', '0'
'Handler_write', '0'

處理程序對沒有範圍條件的相同查詢進行計數：

'Handler_commit', '1'
'Handler_delete', '0'
'Handler_discover', '0'
'Handler_external_lock', '4'
'Handler_mrr_init', '0'
'Handler_prepare', '0'
'Handler_read_first', '0'
'Handler_read_key', '385'
'Handler_read_last', '0'
'Handler_read_next', '249369'
'Handler_read_prev', '0'
'Handler_read_rnd', '0'
'Handler_read_rnd_next', '0'
'Handler_rollback', '0'
'Handler_savepoint', '0'
'Handler_savepoint_rollback', '0'
'Handler_update', '0'
'Handler_write', '0'

這不是錯誤，而是優化器的開發方式，正如 Timour Katchaounov 在 2005 年 2 月 28 日所解釋的那樣：

這不是錯誤，這是系統目前的工作方式（4.1 和 5.0），並且記錄了此行為。

通常，具有比較運算符的查詢有三種選擇：

1）

使用“範圍訪問方法”。在這種情況下這是不可能的，因為正如“7.2.5.2. 多部分索引的範圍訪問方法”一節所說：“對於 BTREE 索引，間隔可能可用於與 AND 組合的條件，其中每個條件比較一個鍵使用 =、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN 或 LIKE ‘pattern’（其中 ‘pattern’ 不以萬用字元開頭)。”

注意“常數”這個詞。在範例查詢中，相等性比較兩個表屬性的值，而不是一個屬性與常量，因此此方法不適用。

2）

使用“ref”訪問方法。正如“7.2.1. EXPLAIN 語法（獲取有關 SELECT 的資訊）”一節所說：

“ref 可用於使用 = 運算符比較的索引列。” 好吧，在這種情況下我們有不等式，因此 ref 訪問方法僅使用等式中引用的關鍵部分，在這種情況下僅使用第一個關鍵部分。

3）

使用“檢查每條記錄的範圍”。如文件所述，只有在優於全表掃描時才會使用此方法，而我們的小範例並非如此。

  1. 和 2) 的組合是一個相當大的新特性，將在 MySQL 的某些未來版本中實現。

您可能想跟進開發人員並了解該功能是否已經（或將要）在 MySQL 的未來版本中實現。

（不是答案，但評論太多了。我想追究這些問題。）

你用什麼版本測試過這個？（不僅僅是“5.7”，還有“5.7.xx”。）

是否EXPLAIN FORMAT=JSON SELECT ...提供更多細節？

測試一下，這些數字可能會提供資訊：

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

探勘這些數字（它們是準確的，不像EXPLAIN's），加上明智SELECT COUNT(*)...的查詢，人們通常可以推斷出很多關於查詢執行期間發生的事情。

附帶說明：使用VARCHAR，CHAR除非列確實是固定長度。這將節省大量空間，從而提高速度。

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

相關問答

Mysql

如何使 MySQL 在整數範圍選擇查詢中使用索引

  • December 20, 2021
檢視問答
Mysql

為什麼在 MySQL 中批量多列鍵查詢這麼慢？

  • January 15, 2019
檢視問答
Mysql

使用具有日期範圍的複合索引

  • December 7, 2018
檢視問答
Mysql

創建空表後性能更差

  • June 5, 2018
檢視問答
Mysql

在索引列中包含結果列的性能影響

  • August 10, 2017
檢視問答
Mysql

如何使用各種排序優化 MySQL 查詢的索引

  • March 25, 2017
檢視問答