MySql视图非常慢.为什么? [英] MySql view is very slow. Why?

问题描述

我的普通查询:

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)

执行时间:5ms

我的观点:

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)

我的视图查询:

SELECT * from myview;

执行时间:600ms

Execution time: 600ms

推荐答案

只要在视图中提到DISTINCT或聚合函数，MySQL就会为该视图选择TEMPTABLE算法，这意味着它将为该视图创建一个临时表，并且然后对其进行排序，分组和聚合.在此处中查看更多详细信息.另外，这里有一些关于视图性能的建议.

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.

这篇关于MySql视图非常慢.为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！