為什麼 MySQL 不使用聯合選擇視圖中的索引？

我創建了一個視圖：

create view dh_hits_new_all_old
as
select * from dh_hits_new_201806
union all
select * from dh_hits_new_201807;

當我選擇帶有 where 子句的內容時，其中包含在各個表上具有索引的欄位，MySQL 不使用任何索引。查看解釋輸出：

mysql> explain select count(*) from dh_hits_new_201806 where id_hits_url = 130442;
+----+-------------+--------------------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-------------+
| id | select_type | table              | partitions | type | possible_keys               | key                         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------------------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | dh_hits_new_201806 | NULL       | ref  | dh_idx-hits_new-id_hits_url | dh_idx-hits_new-id_hits_url | 4       | const | 4453 |   100.00 | Using index |
+----+-------------+--------------------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec)

mysql> explain select count(*) from dh_hits_new_201807 where id_hits_url = 130442;
+----+-------------+--------------------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-------------+
| id | select_type | table              | partitions | type | possible_keys               | key                         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------------------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | dh_hits_new_201807 | NULL       | ref  | dh_idx-hits_new-id_hits_url | dh_idx-hits_new-id_hits_url | 4       | const | 4009 |   100.00 | Using index |
+----+-------------+--------------------+------------+------+-----------------------------+-----------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec)

mysql> explain select count(*) from dh_hits_new_all_old where id_hits_url = 130442;
+----+-------------+--------------------+------------+------+---------------+-------------+---------+-------+-------+----------+-------+
| id | select_type | table              | partitions | type | possible_keys | key         | key_len | ref   | rows  | filtered | Extra |
+----+-------------+--------------------+------------+------+---------------+-------------+---------+-------+-------+----------+-------+
|  1 | PRIMARY     | <derived2>         | NULL       | ref  | <auto_key0>   | <auto_key0> | 4       | const |    10 |   100.00 | NULL  |
|  2 | DERIVED     | dh_hits_new_201806 | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 65658 |   100.00 | NULL  |
|  3 | UNION       | dh_hits_new_201807 | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 61552 |   100.00 | NULL  |
+----+-------------+--------------------+------------+------+---------------+-------------+---------+-------+-------+----------+-------+
3 rows in set, 1 warning (0,00 sec)

我不明白為什麼。這種行為當然也反映在執行時間中：

mysql> select count(*) from dh_hits_new_201806 where id_hits_url = 130442;
+----------+
| count(*) |
+----------+
|     4453 |
+----------+
1 row in set (0,01 sec)

mysql> select count(*) from dh_hits_new_201807 where id_hits_url = 130442;
+----------+
| count(*) |
+----------+
|     4009 |
+----------+
1 row in set (0,00 sec)

mysql> select count(*) from dh_hits_new_all_old where id_hits_url = 130442;
+----------+
| count(*) |
+----------+
|     8462 |
+----------+
1 row in set (2,33 sec)

因為它是舊 MySQL 版本中的一種錯誤。UNION使用索引的效果。

在 MySQL 5.7.3 版本之前的版本中，UNION語句使用臨時表。這意味著，首先，您的數據會移動到臨時表中，並在執行過濾之前從臨時表中讀取它們。

伺服器不再為滿足特定條件的 UNION 語句使用臨時表。相反，它從臨時表創建中只保留執行結果列類型轉換所需的資料結構。該表沒有完全實例化，沒有行被寫入或讀取；行直接發送到客戶端。結果是減少了記憶體和磁碟需求，並且在將第一行發送到客戶端之前的延遲更小，因為伺服器不需要等到最後一個查詢塊被執行。EXPLAIN 和優化器跟踪輸出將更改：UNION RESULT 查詢塊將不存在，因為該塊是從臨時表中讀取的部分。

查看5.7.3 版優化器修復。

VIEWs是語法糖。它們可能永遠不會提高性能，有時還會損害性能。

您的查詢實際上是：

SELECT COUNT(*)
   FROM (
       select * from dh_hits_new_201806
       union all
       select * from dh_hits_new_201807
        ) AS x
   WHERE id_hits_url = 130442;

優化器不是很聰明；它無法將其轉換為執行速度更快的 this：

SELECT
   ( SELECT COUNT(*)  from dh_hits_new_201806 WHERE id_hits_url = 130442 ) +
   ( SELECT COUNT(*)  from dh_hits_new_201807 WHERE id_hits_url = 130442 );

也許更好的解決方案是避免笨拙的模式，在這種模式中，您有多個表，這些表在邏輯上組合成一個表。也就是說，只有一張桌子dh_hits_new。或者，可以對該表進行分區。但是，同樣，不要期望性能提升 - 除了通過DROP PARTITION.

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