结合UNION ALL的表的VIEW的MySQL性能 [英] MySQL performance of VIEW for tables combined with UNION ALL
问题描述
假设我在 MySQL 中有2个表:
create table `persons` (
`id` bigint unsigned not null auto_increment,
`first_name` varchar(64),
`surname` varchar(64),
primary key(`id`)
);
create table `companies` (
`id` bigint unsigned not null auto_increment,
`name` varchar(128),
primary key(`id`)
);
现在，我经常需要对它们进行相同的处理，这就是以下查询的原因:
Now, very often I need to treat them the same, that's why following query:
select person.id as `id`, concat(person.first_name, ' ', person.surname) as `name`, 'person' as `person_type`
from persons
union all
select company.id as `id`, company.name as `name`, 'company' as `person_type`
from companies
开始经常出现在其他查询中:作为 joins 或 subselects 的一部分. 现在，我只是将查询插入 joins 或 subselects 中，例如:
starts to appear in other queries quite often: as part of joins or subselects. For now, I simply inject this query into joins or subselects like:
select *
from some_table row
left outer join (>>> query from above goes here <<<) as `persons`
on row.person_id = persons.id and row.person_type = persons.person_type
但是，今天，我不得不多次将讨论的联合查询用于另一个查询，即，将其联接两次.
But, today I had to use discussed union query into another query multiple times i.e. join it twice.
由于我从没有过视图的经验，听说它们有很多缺点，所以我的问题是:
Since I never had experience with views and heard that they have many disadvantages, my question is:
为讨论的联合查询创建视图并将其用于我的 joins ， subselects 等是正常做法吗?在性能方面-与仅将其插入 joins ， subselects 等相比，会更糟，是否相等或更好?在这种情况下，拥有视图有什么弊端吗?
Is it normal practice to create a view for discussed union query and use it in my joins , subselects etc? In terms of performance - will it be worse, equal or better comparing to just inserting it into joins, subselects etc? Are there any drawbacks of having a view in this case?
在此先感谢您的帮助！
推荐答案
我同意Bill Karwin出色回答中的所有观点.
I concur with all of the points in Bill Karwin's excellent answer.
问:，通常的做法是为讨论的联合查询创建视图并在我的联接，子选择等中使用它?
Q: Is it normal practice to create a view for discussed union query and use it in my joins, subselects etc?
A:对于MySQL，更常见的做法是避免使用"CREATE VIEW"语句.
A: With MySQL the more normal practices is to avoid using "CREATE VIEW" statement.
问:就性能而言-与仅将其插入联接，子选择等相比，会更糟，是否相等或更好?
Q: In terms of performance - will it be worse, equal or better comparing to just inserting it into joins, subselects etc?
A:引用视图对象将具有与等效的嵌入式视图相同的性能.
A: Referencing a view object will have the identical performance to an equivalent inline view.
(查找视图对象，检查特权，然后用存储的SQL替换视图引用可能要花点时间，这要比发送一条只需要一点点时间的语句要复杂得多.但是这些差异中的任何一个都是微不足道的.)
(There might be a teensy-tiny bit more work to lookup the view object, checking privileges, and then replace the view reference with the stored SQL, vs. sending a statement that is just a teeny-tiny bit longer. But any of those differences are insignificant.)
问:在这种情况下拥有视图是否有缺点?
Q: Are there any drawbacks of having a view in this case?
A:最大的缺点是MySQL如何处理视图(无论是存储视图还是内联视图). MySQL将始终运行视图查询并将该查询的结果具体化为临时MyISAM表.但是，无论是存储视图定义还是内联包含视图定义都没有什么区别. (其他RDBMS处理视图的方式与MySQL有所不同).
A: The biggest drawback is in how MySQL processes a view, whether it's stored or inline. MySQL will always run the view query and materialize the results from that query as a temporary MyISAM table. But there's no difference there whether the view definition is stored, or whether it's included inline. (Other RDBMSs process views much differently than MySQL).
视图的一个主要缺点是外部查询的谓词永远不会被下推到视图查询中.每次引用该视图时，即使使用单个id值查询，MySQL都将运行该视图查询并创建一个临时MyISAM表(上面没有索引)，然后MySQL将对该临时表运行外部查询MyISAM表.
One big drawback of a view is that predicates from the outer query NEVER get pushed down into the view query. Every time you reference that view, even with a query for a single id value, MySQL is going to run the view query and create a temporary MyISAM table (with no indexes on it), and THEN MySQL will run the outer query against that temporary MyISAM table.
因此，就性能而言，请考虑对与"CREATE TEMPORARY TABLE t (cols) ENGINE=MyISAM"和"INSERT INTO t (cols) SELECT ..."相当的视图的引用.
So, in terms of performance, think of a reference to a view on par with "CREATE TEMPORARY TABLE t (cols) ENGINE=MyISAM" and "INSERT INTO t (cols) SELECT ...".
MySQL实际上将内联视图称为派生表"，当我们了解MySQL在做什么时，该名称就很有意义了.
MySQL actually refers to an inline view as a "derived table", and that name makes a lot of sense, when we understand what MySQL is doing with it.
我个人的喜好是不要使用"CREATE VIEW"语句.最大的缺点(如我所见)是它隐藏"了正在执行的SQL.对于将来的读者来说，对该视图的引用就像一张表.然后，当他去编写SQL语句时，他将像表一样引用视图，因此非常方便.然后，他决定要将该表与自身连接，并对其进行另一个引用. (作为第二个参考，MySQL还再次运行该查询，并创建了另一个临时(且未建立索引)MyISAM表.现在，在该表上执行了JOIN操作.然后添加了谓词"WHERE view.column ='foo'"在外部查询上.
My personal preference is to not use the "CREATE VIEW" statement. The biggest drawback (as I see it) is that it "hides" SQL that is being executed. For the future reader, the reference to the view looks like a table. And then, when he goes to write a SQL statement, he's going to reference the view like it was a table, so very convenient. Then he decides he's going to join that table to itself, with another reference to it. (For the second reference, MySQL also runs that query again, and creates yet another temporary (and unindexed) MyISAM table. And now there's a JOIN operation on that. And then a predicate "WHERE view.column = 'foo'" gets added on the outer query.
最终，它隐藏"了最明显的性能改进，使谓词滑入了视图查询.
It ends up "hiding" the most obvious performance improvement, sliding that predicate into the view query.
然后，有人来决定他们要创建引用旧视图的新视图.他只需要一行子集，并且不能修改现有视图，因为这可能会破坏某些内容，因此他创建了一个新视图...从publicview创建视图myview p在哪里p.col ='foo'.
And then, someone comes along and decides they are going to create new view, which references the old view. He only needs a subset of rows, and can't modify the existing view because that might break something, so he creates a new view... CREATE VIEW myview FROM publicview p WHERE p.col = 'foo'.
现在，对myview的引用将首先运行publicview查询，创建一个临时MyISAM表，然后针对该myview查询运行，创建另一个临时MyISAM表，外部查询将针对该表运行.
And, now, a reference to myview is going to first run the publicview query, create a temporary MyISAM table, then the myview query gets run against that, creating another temporary MyISAM table, which the outer query is going to run against.
基本上，视图的便利性可能会导致意外的性能问题.数据库中的视图定义可供任何人使用，即使不是最合适的解决方案，也要有人使用.
Basically, the convenience of the view has the potential for unintentional performance problems. With the view definition available on the database for anyone to use, someone is going to use it, even where it's not the most appropriate solution.
至少在内联视图中，编写SQL语句的人员更了解实际正在执行的SQL，并且将所有SQL布局都可以对其进行调整以提高性能.
At least with an inline view, the person writing the SQL statement is more aware of the actual SQL being executed, and having all that SQL laid out gives an opportunity for tweaking it for performance.
我的两分钱.
TAMING BEASTLY SQL
我发现，应用常规的格式设置规则(我的工具会自动执行这些规则)会使大量的SQL变成我可以阅读和使用的东西.
I find that applying regular formatting rules (that my tools automatically do) can bend monstrous SQL into something I can read and work with.
SELECT row.col1
, row.col2
, person.*
FROM some_table row
LEFT
JOIN ( SELECT 'person' AS `person_type`
, p.id AS `id`
, CONCAT(p.first_name,' ',p.surname) AS `name`
FROM person p
UNION ALL
SELECT 'company' AS `person_type`
, c.id AS `id`
, c.name AS `name`
FROM company c
) person
ON person.id = row.person_id
AND person.person_type = row.person_type
我同样有可能完全避免使用内联视图，并在SELECT列表中使用条件表达式，尽管对于许多列而言，这样做确实很麻烦.
I'd be equally likely to avoid the inline view at all, and use conditional expressions in the SELECT list, though this does get more unwieldy for lots of columns.
SELECT row.col1
, row.col2
, row.person_type AS ref_person_type
, row.person_id AS ref_person_id
, CASE
WHEN row.person_type = 'person' THEN p.id
WHEN row.person_type = 'company' THEN c.id
END AS `person_id`
, CASE
WHEN row.person_type = 'person' THEN CONCAT(p.first_name,' ',p.surname)
WHEN row.person_type = 'company' THEN c.name
END AS `name`
FROM some_table row
LEFT
JOIN person p
ON row.person_type = 'person'
AND p.id = row.person_id
LEFT
JOIN company c
ON row.person_type = 'company'
AND c.id = row.person_id
这篇关于结合UNION ALL的表的VIEW的MySQL性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
