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)

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值