mysql 全表扫描 视图_MySQL视图是否总是进行全表扫描？

我正在尝试优化使用

MySQL 5.1中的视图的查询.看来即使我从视图中选择1列,它也总是进行全表扫描.这是预期的行为吗？

对于我在下面第一个查询中指定的表,视图只是SELECT“这些表中的所有列 – NOT *”.

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

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)

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

EXPLAIN SELECT vpc.PromotionID FROM vw_PromotionCode vpc WHERE vpc.PromotionCode = '5TAFF312C0NT'\G;

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table:

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:

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

Extra:

17 rows in set (0.18 sec)