如何让 MySQL 使用 INDEX 进行视图查询？

我在 JavaEE 上做一个使用 MySql 数据库的网络项目。我们需要一个视图来汇总来自3个表的数据，总行数超过3M。每个表都是使用索引创建的。但是我还没有找到一种方法来利用条件选择语句检索中的索引优势，这种检索是从我们用[ group by ]创建的视图中获得的。

我从人们那里得到了一些建议。因为不能像 Oracle 那样在 mysql 中为视图创建索引。但是在我进行的一些测试中，索引可以用在 view select 语句中。也许我创造这些观点的方式不对。

我将用一个例子来描述我的问题。

我们有一个表，用于记录 NBA 比赛中高分数的数据，其索引位于[ 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);

然后我创建一个视图来看看科比布莱恩特每年的最高得分

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;

我写了一个 If判断语句，看看 科比在 二零零六年中得到的最高分;

select * from v_kobe_highscores where happened_in = 2006;

当我在 toad 中为 mysql 解释它时，我发现 mysql 通过扫描 所有行来形成视图，然后找到包含条件的数据，而不使用[ happened _ in ]上的索引。

explain select * from v_kobe_highscores where happened_in = 2006;

explain result

我们在项目中使用的视图是在具有数百万行的表之间构建的。在每个视图数据检索中扫描表中的所有行是不可接受的。救命啊！谢谢！

@ zerkms 这是我在现实生活中测试的结果。我看不出。我认为@spencer7593的观点是正确的。MySQL 优化器不会在视图查询中“下推”谓词。 real-life test

99577 次浏览
小开

在这种情况下，使用 player + happened_in(以这种特定的顺序)列创建 合成的索引是最佳选择。

PS: 不要在这么少的行上测试 mysql 优化器行为，因为它可能更喜欢全扫描而不是索引。如果你想知道在现实生活中会发生什么，那就用真实生活中的大量数据来填充它。

小开
最佳答案

如何让 MySQL 将索引用于视图查询？简短的回答是，提供一个 MySQL 可以使用的索引。

在这种情况下，最佳指数可能是一个“覆盖”指数:

... ON highscores (player, happened_in, score)

MySQL 很可能会使用这个索引，而 EXPLAIN 将显示: "Using index"，因为 WHERE player = 24(索引中前一列上的等式谓词)。GROUP BY happened_id(索引中的第二列)可能允许 MySQL 使用索引优化它，以避免排序操作。在索引中包含 score列将允许查询完全从索引中满足，而不必访问(查找)索引引用的数据页。

答案很简单。较长的回答是，MySQL 不太可能使用带有前导列 happened_id的索引进行视图查询。

为什么视图会导致性能问题

MySQL 视图存在的问题之一是，MySQL 不会将谓词从外部查询“推”到视图查询中。

外部查询指定 WHERE happened_in = 2006。MySQL 优化器在运行内部“视图查询”时不考虑谓词。视图的查询在外部查询之前单独执行。执行该查询得到的结果集“物化”; 也就是说，结果存储为一个中间 MyISAM 表。(MySQL 称之为“派生表”，当您理解 MySQL 执行的操作时，它们使用的名称是有意义的。)

底线是当 MySQL 运行构成视图定义的查询时，您在 happened_in上定义的索引没有被 MySQL 使用。

创建中间“派生表”后，将使用该“派生表”作为行源执行外部查询 THEN。当外部查询运行时，就会计算 happened_in = 2006谓词。

请注意，视图查询中的所有行都存储在这里，在您的示例中，这是一个每个 happened_in值的行，而不仅仅是您在外部查询中指定相等谓词的行。

对于某些人来说，视图查询的处理方式可能是“意想不到的”，这就是在 MySQL 中使用“视图”会导致性能问题的原因之一，与其他关系数据库处理视图查询的方式相比。

使用合适的覆盖索引提高视图查询的性能

考虑到您的视图定义和查询，最好的方法是为视图查询提供“使用索引”访问方法。要做到这一点，你需要一个覆盖指数，例如。

... ON highscores (player, happened_in, score).

这可能是对现有视图定义和现有查询最有益的索引(在性能方面)。player列是前导列，因为视图查询中该列上有一个相等谓词。接下来是 happened_in列，因为在该列上有一个 GROUPBY 操作，MySQL 将能够使用该索引来优化 GROUPBY 操作。我们还包括 score列，因为这是查询中唯一引用的其他列。这使得索引成为一个“覆盖”索引，因为 MySQL 可以直接从索引页面满足该查询，而不需要访问底层表中的任何页面。这就是我们将要跳出的查询计划: “使用索引”而不是“使用文件排序”。

将性能与没有派生表的独立查询进行比较

您可以将查询的执行计划与视图和等效的独立查询进行比较:

SELECT player
, MAX(score) AS highest_score
, happened_in
FROM highscores
WHERE player = 24
AND happened_in = 2006
GROUP
BY player
, happened_in

独立查询还可以使用覆盖索引，例如。

... ON highscores (player, happened_in, score)

但不需要实现中间的 MyISAM 表。

我不确定前面的任何一个答案是否能直接回答你刚才提出的问题。

问: 如何让 MySQL 使用 INDEX 进行视图查询？

答: 定义视图查询可以使用的合适的 INDEX。

简短的回答是提供一个“覆盖索引”(索引包括视图查询中引用的所有列)。该索引中的前导列应该是用相等谓词引用的列(在您的示例中，player列应该是前导列，因为查询中有一个 player = 24谓词。此外，GROUP BY 中引用的列应该是索引中的前导列，这允许 MySQL 通过使用索引而不是使用排序操作来优化 GROUP BY操作。

这里的关键点是，视图查询基本上是一个独立的查询; 来自该查询的结果存储在一个中间“派生”表(一个 MyISAM 表，当针对视图的查询运行时创建该表。

在 MySQL 中使用视图并不一定是一个“坏主意”，但是我要强烈提醒那些选择在 MySQL 中使用视图的人注意 MySQL 是如何处理引用这些视图的查询的。MySQL 处理视图查询的方式与其他数据库(如 Oracle、 SQLServer)处理视图查询的方式有很大不同。

小开

这并没有直接回答这个问题，但是对于其他遇到这个问题的人来说，这是一个直接相关的解决方案。这实现了使用视图的同样好处，同时最大限度地减少了缺点。

我设置了一个 PHP 函数，我可以向其发送参数，将内容放入其中，以最大限度地利用索引，而不是在连接或视图外的 where 子句中使用它们。在函数中，您可以为派生表制定 SQL 语法，并返回该语法。然后在调用程序中，你可以这样做:

$table = tablesyntax(parameters);
select field1, field2 from {$table} as x... + other SQL

因此，您可以获得视图的封装优势，能够像调用视图一样调用它，但不受索引限制。


提交答案