MySQL视图是否总是执行全表扫描?

I'm trying to optimize a query which uses a view in MySQL 5.1. It seems that even if I select 1 column from the view it always does a full table scan. Is that the expected behaviour?

我正在优化一个使用MySQL 5.1视图的查询。看起来，即使我从视图中选择了1列，它总是做一个全表扫描。这是预期的行为吗?

The view is just a SELECT "All Columns From These Tables - NOT *" for the tables I have specified in the first query below.

视图只是一个SELECT“所有这些表的列—不是*”，对于我在下面第一个查询中指定的表。

This is my explain output from when i select the indexed column PromotionID from the query which makes up the view. As you can see it is vastly different from the output on the view.

这是我从组成视图的查询中选择索引列PromotionID时的explain输出。正如您所看到的，它与视图上的输出有很大的不同。

EXPLAIN SELECT pb.PromotionID FROM PromotionBase pb INNER JOIN PromotionCart pct ON pb.PromotionID = pct.PromotionID INNER JOIN PromotionCode pc ON pb.PromotionID = pc.PromotionID WHERE pc.PromotionCode = '5TAFF312C0NT'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pc
         type: const
possible_keys: PRIMARY,fk_pc_pb
          key: PRIMARY
      key_len: 302
          ref: const
         rows: 1
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: pb
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: pct
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index
3 rows in set (0.00 sec)

The output when i select the same thing but from the view

当我从视图中选择相同的东西时的输出

EXPLAIN SELECT vpc.PromotionID FROM vw_PromotionCode vpc  WHERE vpc.PromotionCode = '5TAFF312C0NT'\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5830
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: pcart
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 33
        Extra: Using index
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: pb
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: readyinteractive.pcart.PromotionID
         rows: 1
        Extra:
*************************** 4. row ***************************
           id: 2
  select_type: DERIVED
        table: pc
         type: ref
possible_keys: fk_pc_pb
          key: fk_pc_pb
      key_len: 4
          ref: readyinteractive.pb.PromotionID
         rows: 249
        Extra: Using where
*************************** 5. row ***************************
           id: 3
  select_type: UNION
        table: pp
         type: index
possible_keys: PRIMARY
          key: pp_p
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using index
*************************** 6. row ***************************
           id: 3
  select_type: UNION
        table: pb
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: readyinteractive.pp.PromotionID
         rows: 1
        Extra:
*************************** 7. row ***************************
           id: 3
  select_type: UNION
        table: pc
         type: ref
possible_keys: fk_pc_pb
          key: fk_pc_pb
      key_len: 4
          ref: readyinteractive.pb.PromotionID
         rows: 249
        Extra: Using where
*************************** 8. row ***************************
           id: 4
  select_type: UNION
        table: pcp
         type: index
possible_keys: PRIMARY
          key: pcp_cp
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using index
*************************** 9. row ***************************
           id: 4
  select_type: UNION
        table: pb
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: readyinteractive.pcp.PromotionID
         rows: 1
        Extra:
*************************** 10. row ***************************
           id: 4
  select_type: UNION
        table: pc
         type: ref
possible_keys: fk_pc_pb
          key: fk_pc_pb
      key_len: 4
          ref: readyinteractive.pb.PromotionID
         rows: 249
        Extra: Using where
*************************** 11. row ***************************
           id: 5
  select_type: UNION
        table: ppc
         type: index
possible_keys: PRIMARY
          key: ppc_pc
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using index
*************************** 12. row ***************************
           id: 5
  select_type: UNION
        table: pb
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: readyinteractive.ppc.PromotionID
         rows: 1
        Extra:
*************************** 13. row ***************************
           id: 5
  select_type: UNION
        table: pc
         type: ref
possible_keys: fk_pc_pb
          key: fk_pc_pb
      key_len: 4
          ref: readyinteractive.pb.PromotionID
         rows: 249
        Extra: Using where
*************************** 14. row ***************************
           id: 6
  select_type: UNION
        table: ppt
         type: index
possible_keys: PRIMARY
          key: ppt_pt
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using index
*************************** 15. row ***************************
           id: 6
  select_type: UNION
        table: pb
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: readyinteractive.ppt.PromotionID
         rows: 1
        Extra:
*************************** 16. row ***************************
           id: 6
  select_type: UNION
        table: pc
         type: ref
possible_keys: fk_pc_pb
          key: fk_pc_pb
      key_len: 4
          ref: readyinteractive.pb.PromotionID
         rows: 249
        Extra: Using where
*************************** 17. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3,4,5,6>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
17 rows in set (0.18 sec)

2 个解决方案

#1

Views in MySQL are not indexed so by their very nature require a full scan each time they are accessed. Generally speaking this makes Views really only useful for situations where you have a fairly complex static query that returns a small result set and you plan to grab the entire result set every time.

MySQL中的视图没有被索引，因此它们的本质要求每次访问时都要进行一次完整的扫描。一般来说，这使得视图只对非常复杂的静态查询有用，静态查询返回一个小的结果集，并且计划每次都获取整个结果集。

Edit: Of course Views will use the indexes on the underlying tables so that the View itself is optimized (otherwise they wouldn't make any sense at all to use) but because there are no indexes on a View it is not possible for a WHERE query on the View to be optimized.

编辑:当然视图将使用底层表上的索引,视图本身优化(否则他们不会任何意义使用),而是因为没有索引视图是不可能的,查询的优化。

Constructing indexes for Views would be expensive anyway because while I've not tried to profile any Views, I'm fairly certain that a temp table is constructed behind the scenes and then the result set returned. It already takes plenty of time to construct the temp table, I wouldn't want a view that also tries to guess what indexes are needed. Which brings up the second point which is that MySQL does not currently offer a method to specify what indexes to use for a View so how does it know what fields need to be indexed? Does it guess based on your query?

无论如何，构建视图的索引都是昂贵的，因为尽管我没有尝试去剖析任何视图，但我相当确定在幕后构建了一个临时表，然后返回结果集。构建临时表已经花费了大量的时间，我不希望视图也试图猜测需要什么索引。这就引出了第二个问题，MySQL目前没有提供一种方法来指定要为视图使用哪些索引，那么它如何知道需要对哪些字段进行索引呢?它会根据你的查询进行猜测吗?

You might consider using a Temporary Table because then you can specify indexes on fields in the temporary table. However, from experience this tends to be really, really slow.

您可以考虑使用临时表，因为您可以在临时表中指定字段的索引。然而，从经验来看，这是非常非常缓慢的。

If all this view contains is a SELECT ALL FROM table1, table2, table3; then I would have to ask why this query needs to be in a View at all? If for some reason its absolutely necessary, you might want to use a stored procedure to encapsulate the query as you'll then be able to get optimized performance while maintaining the benefit of a simpler call to the database for the result set.

如果这个视图包含一个SELECT all FROM table1, table2, table3;然后我就会问为什么这个查询需要放在视图中呢?如果出于某种原因，它是绝对必要的，那么您可能希望使用存储过程来封装查询，因为这样您就可以在保持对结果集的更简单的数据库调用的好处的同时获得优化的性能。

#2

I've looked deeper into it an I've missed a key point of information :( My view query actually has a union with another table. This is causing the view to use the TEMPORARY TABLE algorithm instead of the MERGE algorithm.

我已经更深入地研究了它，我漏掉了一个关键信息:(我的视图查询实际上与另一个表有一个联合。这导致视图使用临时表算法而不是合并算法。

The TEMPORARY TABLE algorithm doesn't allow the use of indexes in the underlying tables.

临时表算法不允许在底层表中使用索引。

This seems to be a bug in MySQL and was reported way back in 2006 but doesn't look like it has been solved in 2009! http://forums.mysql.com/read.php?100,56681,56681

这似乎是MySQL中的一个bug，在2006年被报道过，但是看起来不像在2009年被解决了!http://forums.mysql.com/read.php?100,56681年、56681年

Looks like i'm just going to have to re-write the query as an outer join.

看起来我只需要将查询重写为外部连接。

#1