mysql视图慢,MySql视图非常慢。为什么？
My normal query:
SELECT
DISTINCT vt.id as id,
vtt.name as n,
vt.etxid as etx
FROM vt
LEFT JOIN vtt ON
(vtt.locale = "etx"
AND vtt.etxid = vt.etxid)
Execution time: 5ms
My view:
CREATE OR REPLACE VIEW myview AS
SELECT
DISTINCT vt.id as id,
vtt.name as n,
vt.etxid as etx
FROM vt
LEFT JOIN vtt ON
(vtt.locale = "etx"
AND vtt.etxid = vt.etxid)
My view query:
SELECT * from myview;
Execution time: 600ms
解决方案
As soon as you mention DISTINCT or aggregation functions in a view MySQL selects TEMPTABLE algorithm for this view, and it means it will create a temporary table for the view and then apply sorting, grouping, and aggregations to it. See more details here. Also, there are some recommendations here concerning view performance.
My normal query: SELECT DISTINCT vt.id as id, vtt.name as n, vt.etxid as etx FROM vt LEFT JOIN vtt ON (vtt.locale = "etx" AND vtt.etxid = vt.etxid) Execution time: 5ms My view: CREATE OR REPLACE VIEW myview AS SELECT DISTINCT vt.id as id, vtt.name as n, vt.etxid as etx FROM vt LEFT JOIN vtt ON (vtt.locale = "etx" AND vtt.etxid = vt.etxid) My view query: SELECT * from myview; Execution time: 600ms 解决方案 As soon as you mention DISTINCT or aggregation functions in a view MySQL selects TEMPTABLE algorithm for this view, and it means it will create a temporary table for the view and then apply sorting, grouping, and aggregations to it. See more details here. Also, there are some recommendations here concerning view performance.