Всегда ли MySQL view выполняет полное сканирование таблицы?
Я пытаюсь оптимизировать запрос, который использует представление в MySQL 5.1. Кажется, что даже если я выбираю 1 столбец в представлении, он всегда выполняет полное сканирование таблицы. Это ожидаемое поведение?
Представление просто ВЫБРАТЬ "Все столбцы из этих таблиц - НЕ *" для таблиц, которые я указал в первом запросе ниже.
Это мой вывод объяснения, когда я выбираю индексированный столбец 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: <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 ответа
Представления в MySQL не индексируются, поэтому по своей природе требуют полного сканирования при каждом обращении к ним. Вообще говоря, это делает представления действительно полезными только в ситуациях, когда у вас довольно сложный статический запрос, который возвращает небольшой набор результатов, и вы планируете получать весь набор результатов каждый раз.
Редактировать: Конечно, представления будут использовать индексы на базовых таблицах, так что само представление будет оптимизировано (иначе они вообще не будут иметь никакого смысла в использовании), но из-за отсутствия индексов в представлении это невозможно для WHERE. запрос на просмотр для оптимизации.
В любом случае создание индексов для представлений было бы дорогостоящим, поскольку, хотя я и не пытался профилировать какие-либо представления, я вполне уверен, что временная таблица создается за кулисами, а затем возвращается набор результатов. Создание временной таблицы уже занимает много времени, я бы не хотел, чтобы представление также пыталось угадать, какие индексы нужны. В связи с этим возникает второй момент: MySQL в настоящее время не предлагает метод, позволяющий указать, какие индексы использовать для представления, так как он знает, какие поля нужно проиндексировать? Это угадывает на основе вашего запроса?
Вы можете рассмотреть возможность использования временной таблицы, потому что тогда вы можете указать индексы для полей во временной таблице. Однако, по опыту, это очень медленно.
Если все это представление содержит SELECT ALL FROM table1, table2, table3; тогда я должен был бы спросить, почему этот запрос вообще должен быть в представлении? Если по какой-то причине это абсолютно необходимо, вы можете использовать хранимую процедуру для инкапсуляции запроса, поскольку тогда вы сможете получить оптимизированную производительность, сохраняя при этом преимущество более простого обращения к базе данных для набора результатов.
Я углубился в это и пропустил ключевую информацию:(В моем запросе представления фактически есть объединение с другой таблицей. Это заставляет представление использовать алгоритм TEMPORARY TABLE вместо алгоритма MERGE.
Алгоритм TEMPORARY TABLE не позволяет использовать индексы в базовых таблицах.
Это похоже на ошибку в MySQL, о которой было сообщено еще в 2006 году, но не похоже, что она была решена в 2009 году! http://forums.mysql.com/read.php?100,56681,56681
Похоже, мне просто придется переписать запрос как внешнее соединение.