如何讓 MySQL 使用正確的索引

我對有近 180 萬行的表進行了查詢。我創建了一個索引，其中包含and子句中new_index的所有列，以提高性能。現在我發出查詢：WHERE``ORDER

select  col_1
   from  mytable
   where  col_1 in (1,2,6,8,9,22,26,28,29,30,31,34,35,36,37,
                    38,39,41,44,45,46,51,52,53,54,55,57,60 )
     and  col_2 in(1,4,6,10)
     and  (col_3 = 0)
     and  (      col_4 = 1
             or  col_4 = 2
          )
   order by  insert_time asc;

處理程序讀取的總和（由 顯示SHOW SESSION STATUS LIKE 'Handler%';）為 1,781,069。這是解釋查詢的輸出。令人驚訝的是，選擇的索引不是我創建的，而是另一個mytable_ind_col_3。它只使用一列。

+----+-------------+-------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------+--------+-----------------------------+
| id | select_type | table       | type | possible_keys                                                                                                                                         | key                     | key_len | ref   | rows   | Extra                       |
+----+-------------+-------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------+--------+-----------------------------+
|  1 | SIMPLE      | mytable     | ref  | mytable_ind_11,mytable_ind_col_3,new_index | mytable_ind_col_3 | 5       | const | 892093 | Using where; Using filesort |
+----+-------------+-------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+-------+--------+-----------------------------+

所以我強制 MySQL 使用我的索引：

select  col_1
   from  mytable use index (new_index)
   where  col_1 in (1,2,6,8,9,22,26,28,29,30,31,34,35,36,37,
                    38,39,41,44,45,46,51,52,53,54,55,57,60 )
     and  col_2 in(1,4,6,10)
     and  (col_3 = 0)
     and  (      col_4 = 1
             or  col_4 = 2
          )
   order by  insert_time asc;

現在處理程序讀取的總和是 227。關於解釋查詢結果有兩個有趣的事情。首先，行數高於第一個查詢，我從處理程序計數中知道這是不正確的。優化器有什麼困惑？我該如何解決？其次，key_len是 20，我希望它是 25（4 個 INT + 1 個日期時間 + 5 個可空列）。所以我猜有些列沒有被使用。這是為什麼？

+----+-------------+-------------+-------+------------------+------------------+---------+------+---------+------------------------------------------+
| id | select_type | table       | type  | possible_keys    | key              | key_len | ref  | rows    | Extra                                    |
+----+-------------+-------------+-------+------------------+------------------+---------+------+---------+------------------------------------------+
|  1 | SIMPLE      | mytable     | range | new_index        | new_index        | 20      | NULL | 1141728 | Using where; Using index; Using filesort |
+----+-------------+-------------+-------+------------------+------------------+---------+------+---------+------------------------------------------+

col_1, col_2, col_3,col_4都是可以為空的 INT 並且insert_time是可以為空的日期時間。new_index是 ( col_1, col_2, col_3, col_4, insert_time)mytable_ind_col_3上的索引，並且是col_3only 上的索引。這是在 MySQL 5.6 上。

我怎樣才能讓 MySQL 使用new_index，這似乎是最佳解決方案，而不強迫它？

請提供SHOW CREATE TABLE以便我們查看數據類型和new_index.

對於那個查詢，我建議

INDEX(col_3, col_1, col_2, col_4, insert_time)

col_3由於“=”測試，我將其放在首位：col_3 = 0 其餘列用於“覆蓋”（參見“使用索引”），排序可能無關緊要。這種排序可能會消除對USE INDEX. 會有一個排序。

如果mytable_ind_col_3是INDEX(col_3)，DROP則在添加我的索引時。（否則優化器可能會繼續使用它。）

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