如何让MySQL使用INDEX进行查看查询？ [英] How do I get MySQL to use an INDEX for view query?
问题描述
我正在使用Java EE上的MySql数据库开展Web项目。我们需要一个视图来总结来自3个表格的数据，总体上超过3M行。每个表都是使用索引创建的。但是我没有找到一种方法来利用我们用[group by]创建的视图中的条件选择语句检索中的索引中的优势。
I'm working on a web project with MySql database on Java EE. We needed a view to summarize data from 3 tables with over 3M rows overall. Each table was created with index. But I haven't found out a way to take advantages in the indexes in the conditional select statement retrieval from the view that we created with [group by].
我'从人们那里获得建议在MySql中使用视图不是一个好主意。因为你无法像在oracle中那样为mysql中的视图创建索引。但是在我进行的一些测试中，索引可以在view select语句中使用。也许我以错误的方式创建了这些观点。
I've getting suggestions from people that using views in MySql is not a good idea. Because you can't create index for views in mysql like in oracle. But in some test that I took, indexes can be used in view select statement. Maybe I've created those views in a wrong way.
我将用一个例子来描述我的问题。
I'll use a example to describe my problem.
我们有一张表记录了NBA比赛中得分高的数据，其中索引列为[eventsnd_in]
We have a table that records data for high scores in NBA games, with index on column [happend_in]
CREATE TABLE `highscores` (
`tbl_id` int(11) NOT NULL auto_increment,
`happened_in` int(4) default NULL,
`player` int(3) default NULL,
`score` int(3) default NULL,
PRIMARY KEY (`tbl_id`),
KEY `index_happened_in` (`happened_in`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据（8行）
INSERT INTO highscores(happened_in, player, score)
VALUES (2006, 24, 61),(2006, 24, 44),(2006, 24, 81),
(1998, 23, 51),(1997, 23, 46),(2006, 3, 55),(2007, 24, 34), (2008, 24, 37);
然后我创建一个视图，看看科比每年得到的最高分
then I create a view to see the highest score that Kobe Bryant got in each year
CREATE OR REPLACE VIEW v_kobe_highScores
AS
SELECT player, max(score) AS highest_score, happened_in
FROM highscores
WHERE player = 24
GROUP BY happened_in;
我写了条件声明，看看 kobe 得到的最高分的 2006 ;
I wrote a conditional statement to see the highest score that kobe got in 2006;
select * from v_kobe_highscores where happened_in = 2006;
当我在forad中为mysql解释它时，我发现mysql已扫描所有行以形成视图，然后在其中查找具有条件的数据，而不使用[happen_in]上的索引。
When I explain it in toad for mysql, I found out that mysql have scan all rows to form the view, then find data with condition in it, without using index on [happened_in].
explain select * from v_kobe_highscores where happened_in = 2006;
我们在项目中使用的视图是在具有数百万行的表中构建的。在每个视图数据检索中扫描表中的所有行是不可接受的。请帮忙！谢谢！
The view that we use in our project is built among tables with millions of rows. Scanning all the rows from table in every view data retrieval is unacceptable. Please help! Thanks!
@zerkms这是我在现实生活中测试的结果。我没有看到太多的区别。我认为@ spencer7593有正确的观点。 MySQL优化器不会在视图查询中推送该谓词。
@zerkms Here is the result I tested on real-life. I don't see much differences between. I think @spencer7593 has the right point. The MySQL optimizer doesn't "push" that predicate down in the view query.
推荐答案
如何让MySQL使用索引进行视图查询？简短的回答，提供MySQL可以使用的索引。
How do you get MySQL to use an index for a view query? The short answer, provide an index that MySQL can use.
在这种情况下，最佳索引可能是覆盖索引：
In this case, the optimum index is likely a "covering" index:
... ON highscores (player, happened_in, score)
MySQL可能会使用该索引，并且EXPLAIN将显示：使用索引由于 WHERE播放器= 24 （索引中前导列的等式谓词。 GROUP BY happen_id （索引中的第二列），可能允许MySQL使用索引来优化它以避免排序操作。在索引中包含得分列将允许查询完全从索引中满足，而无需访问（查找）索引引用的数据页。
It's likely that MySQL will use that index, and the EXPLAIN will show: "Using index" due to the WHERE player = 24 (an equality predicate on the leading column in the index. The GROUP BY happened_id (the second column in the index), may allow MySQL to optimize that using the index to avoid a sort operation. Including the score column in the index will allow the query to satisfied entirely from the index, without having to visit (lookup) the data pages referenced by the index.
这是快速的答案。答案更长的是MySQL不太可能使用带有
That's the quick answer. The longer answer is that MySQL is very unlikely to use an index with leading column of happened_id for the view query.
为什么选择他认为会导致性能问题
MySQL视图的一个问题是MySQL没有从外部查询推送谓词进入视图查询。
One of the issues you have with the MySQL view is that MySQL does not "push" the predicate from the outer query down into the view query.
您的外部查询指定 WHERE happen_in = 2006 。 MySQL优化器在运行内部视图查询时不考虑谓词。视图的查询在外部查询之前单独执行。执行该查询的结果集得到物化;也就是说，结果存储为中间MyISAM表。 （MySQL称之为派生表，当您理解MysQL执行的操作时，他们使用的名称是有意义的。）
Your outer query specifies WHERE happened_in = 2006. The MySQL optimizer does not consider the predicate when it runs the inner "view query". That query for the view gets executed separately, before the outer query. The resultset from the execution of that query get "materialized"; that is, the results are stored as an intermediate MyISAM table. (MySQL calls it a "derived table", and that name they use makes sense, when you understand the operations that MysQL performs.)
底线是索引你在上定义了在没有形成视图定义的查询时，MySQL没有使用happen_in 。
在创建中间派生表之后，然后使用该派生表作为行源执行外部查询。当外部查询运行时，将评估 happen_in = 2006 谓词。
After the intermediate "derived table" is created, THEN the outer query is executed, using that "derived table" as a rowsource. It's when that outer query runs that the happened_in = 2006 predicate is evaluated.
请注意，所有行都来自存储视图查询，（在您的情况下）是每个 happen_in 的值的行，而不仅仅是您在外部查询中指定了等式谓词的行。
Note that all of the rows from the view query are stored, which (in your case) is a row for EVERY value of happened_in, not just the one you specify an equality predicate on in the outer query.
查看查询的方式可能会被某些人意外，这就是使用MySQL中的视图可能导致性能问题的一个原因，与其他关系数据库处理视图查询的方式。
The way that view queries are processed may be "unexpected" by some, and this is one reason that using "views" in MySQL can lead to performance problems, as compared to the way view queries are processed by other relational databases.
使用合适的覆盖率提高视图查询的性能index
根据您的视图定义和查询，您将获得的最佳信息是视图查询的使用索引访问方法。为此，你需要一个覆盖索引，例如
Given your view definition and your query, about the best you are going to get would be a "Using index" access method for the view query. To get that, you'd need a covering index, e.g.
... ON highscores (player, happened_in, score).
对于现有视图定义和现有查询，这可能是最有利的索引（性能明智） 。 player 列是前导列，因为在视图查询中该列上有等式谓词。接下来是 happen_in 列，因为您在该列上有一个GROUP BY操作，并且MySQL将能够使用此索引来优化GROUP BY操作。我们还包含得分列，因为这是查询中引用的唯一其他列。这使得索引成为覆盖索引，因为MySQL可以直接从索引页面满足该查询，而无需访问基础表中的任何页面。这就像我们要退出查询计划一样好：使用索引没有使用filesort。
That's likely to be the most beneficial index (performance wise) for your existing view definition and your existing query. The player column is the leading column because you have an equality predicate on that column in the view query. The happened_in column is next, because you've got a GROUP BY operation on that column, and MySQL is going to be able to use this index to optimize the GROUP BY operation. We also include the score column, because that is the only other column referenced in your query. That makes the index a "covering" index, because MySQL can satisfy that query directly from index pages, without a need to visit any pages in the underlying table. And that's as good as we're going to get out of that query plan: "Using index" with no "Using filesort".
将性能与没有派生表的独立查询进行比较
您可以将查询的执行计划与视图进行比较等效独立查询：
You could compare the execution plan for your query against the view vs. an equivalent standalone query:
SELECT player
, MAX(score) AS highest_score
, happened_in
FROM highscores
WHERE player = 24
AND happened_in = 2006
GROUP
BY player
, happened_in
独立查询也可以使用覆盖索引，例如
The standalone query can also make use of a covering index e.g.
... ON highscores (player, happened_in, score)
但无需实现中间MyISAM表。
but without a need to materialize an intermediate MyISAM table.
我不确定之前的任何一个是否直接回答了你提出的问题。
I am not sure that any of the previous provides a direct answer to the question you were asking.
问：我如何让MySQL使用用于查看查询的INDEX？
A：定义视图查询可以使用的合适INDEX。
简答题是提供覆盖索引（索引包括视图查询中引用的所有列）。该索引中的前导列应该是使用相等谓词引用的列（在您的情况下，列 player 将是一个前导列，因为您有 player = 24 查询中的谓词。此外，GROUP BY中引用的列应该是索引中的前导列，这允许MySQL优化 GROUP BY 操作，通过使用索引而不是使用排序操作。
The short answer is provide a "covering index" (index includes all columns referenced in the view query). The leading columns in that index should be the columns that are referenced with equality predicates (in your case, the column player would be a leading column because you have a player = 24 predicate in the query. Also, the columns referenced in the GROUP BY should be leading columns in the index, which allows MySQL to optimize the GROUP BY operation, by making use of the index rather than using a sort operation.
这里的关键点是视图查询基本上是一个独立的查询;该查询的结果存储在一个中间的派生表中（一个MyISAM表，它是在对视图的查询运行时创建的。
The key point here is that the view query is basically a standalone query; the results from that query get stored in an intermediate "derived" table (a MyISAM table that gets created when a query against the view gets run.
在MySQL中使用视图是不一定是一个坏主意，但我强烈提醒那些选择使用MySQL中的视图来了解MySQL如何处理引用这些视图的查询。并且MySQL处理视图查询的方式与查看查询的方式有很大不同由其他数据库处理（例如Oracle，SQL Server）。
Using views in MySQL is not necessarily a "bad idea", but I would strongly caution those who choose to use views within MySQL to be AWARE of how MySQL processes queries that reference those views. And the way MySQL processes view queries differs (significantly) from the way view queries are handled by other databases (e.g. Oracle, SQL Server).
这篇关于如何让MySQL使用INDEX进行查看查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
