草庐IT

mysql - 如何让 MySQL 使用 INDEX 进行 View 查询？

coder 2023-05-10 原文

我正在使用 Java EE 上的 MySql 数据库开发 Web 项目。我们需要一个 View 来汇总 3 个总行超过 300 万行的表中的数据。每个表都是用索引创建的。但是我还没有找到一种方法可以从我们使用 [group by] 创建的 View 中利用条件选择语句检索中的索引。

我收到了来自 的人的建议在 MySql 中使用 View 不是一个好主意 .因为您不能像在 oracle 中那样为 mysql 中的 View 创建索引。但是在我进行的一些测试中，可以在 View 选择语句中使用索引。也许我以错误的方式创建了这些 View 。

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

我们有一个记录 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);

然后我创建一个 View 来查看科比·布莱恩特每年获得的最高分
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;

我写了个条件语句看最高分是神户进入 2006 ;
select * from v_kobe_highscores where happened_in = 2006;

我在toad for mysql里解释的时候发现mysql有扫描所有行形成 View ，然后在其中查找具有条件的数据，而不使用 [happened_in] 上的索引。
explain select * from v_kobe_highscores where happened_in = 2006;



我们在项目中使用的 View 是在具有数百万行的表中构建的。在每个 View 数据检索中扫描表中的所有行是 Not Acceptable 。请帮忙!谢谢!

@zerkms 这是我在现实生活中测试的结果。我看不出有多大区别。我认为@spencer7593 的观点是正确的。 MySQL 优化器不会在 View 查询中“推送”该谓词。

最佳答案

如何让 MySQL 使用索引进行 View 查询？简短的回答，提供一个 MySQL 可以使用的索引。

在这种情况下，最佳索引可能是“覆盖”索引:

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

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

这就是快速答案。更长的答案是 MySQL 不太可能使用前导列 happened_id 的索引。用于 View 查询。

为什么 View 会导致性能问题

MySQL View 的问题之一是 MySQL 不会将谓词从外部查询“推送”到 View 查询中。

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

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

创建中间“派生表”后，然后执行外部查询，使用该“派生表”作为行源。当外部查询运行时 happened_in = 2006谓词被评估。

请注意， View 查询中的所有行都已存储，(在您的情况下)是 happened_in 的每个值的行，而不仅仅是您在外部查询中指定相等谓词的那个。

处理 View 查询的方式可能有些人“出乎意料”，这就是与其他关系数据库处理 View 查询的方式相比，在 MySQL 中使用“ View ”会导致性能问题的原因之一。

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

给定您的 View 定义和查询，您将获得的最好的方法是 View 查询的“使用索引”访问方法。为此，您需要一个覆盖索引，例如
... ON highscores (player, happened_in, score).

对于现有 View 定义和现有查询，这可能是最有益的索引(性能方面)。 player column 是前导列，因为您在 View 查询中对该列有一个相等谓词。 happened_in列是下一个，因为您对该列进行了 GROUP BY 操作，并且 MySQL 将能够使用此索引来优化 GROUP BY 操作。我们还包括 score列，因为这是您的查询中唯一引用的其他列。这使得索引成为“覆盖”索引，因为 MySQL 可以直接从索引页面满足该查询，而无需访问基础表中的任何页面。这与我们将要退出查询计划一样好:“​​使用索引”而不是“使用文件排序”。

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

您可以将查询的执行计划与 View 与等效的独立查询进行比较:
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 进行 View 查询？

A:定义 View 查询可以使用的合适的INDEX。

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

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

在 MySQL 中使用 View 不一定是一个“坏主意”，但我强烈提醒那些选择在 MySQL 中使用 View 的人要注意 MySQL 如何处理引用这些 View 的查询。并且 MySQL 处理 View 查询的方式(显着地)不同于其他数据库(例如 Oracle、SQL Server)处理 View 查询的方式。

关于mysql - 如何让 MySQL 使用 INDEX 进行 View 查询？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/13944946/

mysqlcodeViewbrindexing

有关mysql - 如何让 MySQL 使用 INDEX 进行 View 查询？的更多相关文章

  1. ruby - 如何使用 Nokogiri 的 xpath 和 at_xpath 方法 - 2

    我正在学习如何使用Nokogiri，根据这段代码我遇到了一些问题:require'rubygems'require'mechanize'post_agent=WWW::Mechanize.newpost_page=post_agent.get('http://www.vbulletin.org/forum/showthread.php?t=230708')puts"\nabsolutepathwithtbodygivesnil"putspost_page.parser.xpath('/html/body/div/div/div/div/div/table/tbody/tr/td/div

  2. ruby - 如何从 ruby​​ 中的字符串运行任意对象方法？ - 2

    总的来说，我对ruby​​还比较陌生，我正在为我正在创建的对象编写一些rspec测试用例。许多测试用例都非常基础，我只是想确保正确填充和返回值。我想知道是否有办法使用循环结构来执行此操作。不必为我要测试的每个方法都设置一个assertEquals。例如:describeitem,"TestingtheItem"doit"willhaveanullvaluetostart"doitem=Item.new#HereIcoulddotheitem.name.shouldbe_nil#thenIcoulddoitem.category.shouldbe_nilendend但我想要一些方法来使用

  3. ruby - 使用 RubyZip 生成 ZIP 文件时设置压缩级别 - 2

    我有一个Ruby程序，它使用rubyzip压缩XML文件的目录树。gem。我的问题是文件开始变得很重，我想提高压缩级别，因为压缩时间不是问题。我在rubyzipdocumentation中找不到一种为创建的ZIP文件指定压缩级别的方法。有人知道如何更改此设置吗？是否有另一个允许指定压缩级别的Ruby库？ 最佳答案 这是我通过查看ruby​​zip内部创建的代码。level=Zlib::BEST_COMPRESSIONZip::ZipOutputStream.open(zip_file)do|zip|Dir.glob("**/*")d

  4. ruby - 为什么我可以在 Ruby 中使用 Object#send 访问私有(private)/ protected 方法？ - 2

    类classAprivatedeffooputs:fooendpublicdefbarputs:barendprivatedefzimputs:zimendprotecteddefdibputs:dibendendA的实例a=A.new测试a.foorescueputs:faila.barrescueputs:faila.zimrescueputs:faila.dibrescueputs:faila.gazrescueputs:fail测试输出failbarfailfailfail.发送测试[:foo,:bar,:zim,:dib,:gaz].each{|m|a.send(m)resc

  5. ruby-on-rails - 使用 Ruby on Rails 进行自动化测试 - 最佳实践 - 2

    很好奇，就使用ruby​​onrails自动化单元测试而言，你们正在做什么？您是否创建了一个脚本来在cron中运行rake作业并将结果邮寄给您？git中的预提交Hook？只是手动调用？我完全理解测试，但想知道在错误发生之前捕获错误的最佳实践是什么。让我们理所当然地认为测试本身是完美无缺的，并且可以正常工作。下一步是什么以确保他们在正确的时间将可能有害的结果传达给您？ 最佳答案 不确定您到底想听什么，但是有几个级别的自动代码库控制:在处理某项功能时，您可以使用类似autotest的内容获得关于哪些有效，哪些无效的即时反馈。要确保您的提

  6. ruby - 在 Ruby 中使用匿名模块 - 2

    假设我做了一个模块如下:m=Module.newdoclassCendend三个问题:除了对m的引用之外，还有什么方法可以访问C和m中的其他内容？我可以在创建匿名模块后为其命名吗(就像我输入“module...”一样)？如何在使用完匿名模块后将其删除，使其定义的常量不再存在？ 最佳答案 三个答案:是的，使用ObjectSpace.此代码使c引用你的类(class)C不引用m:c=nilObjectSpace.each_object{|obj|c=objif(Class===objandobj.name=~/::C$/)}当然这取决于

  7. ruby - 使用 ruby​​ 和 savon 的 SOAP 服务 - 2

    我正在尝试使用ruby​​和Savon来使用网络服务。测试服务为http://www.webservicex.net/WS/WSDetails.aspx?WSID=9&CATID=2require'rubygems'require'savon'client=Savon::Client.new"http://www.webservicex.net/stockquote.asmx?WSDL"client.get_quotedo|soap|soap.body={:symbol=>"AAPL"}end返回SOAP异常。检查soap信封，在我看来soap请求没有正确的命名空间。任何人都可以建议我

  8. python - 如何使用 Ruby 或 Python 创建一系列高音调和低音调的蜂鸣声？ - 2

    关闭。这个问题是opinion-based.它目前不接受答案。想要改进这个问题？更新问题，以便editingthispost可以用事实和引用来回答它.关闭4年前。Improvethisquestion我想在固定时间创建一系列低音和高音调的哔哔声。例如:在150毫秒时发出高音调的蜂鸣声在151毫秒时发出低音调的蜂鸣声200毫秒时发出低音调的蜂鸣声250毫秒的高音调蜂鸣声有没有办法在Ruby或Python中做到这一点？我真的不在乎输出编码是什么(.wav、.mp3、.ogg等等)，但我确实想创建一个输出文件。

  9. ruby-on-rails - 如何验证 update_all 是否实际在 Rails 中更新 - 2

    给定这段代码defcreate@upgrades=User.update_all(["role=?","upgraded"],:id=>params[:upgrade])redirect_toadmin_upgrades_path,:notice=>"Successfullyupgradeduser."end我如何在该操作中实际验证它们是否已保存或未重定向到适当的页面和消息？ 最佳答案 在Rails3中，update_all不返回任何有意义的信息，除了已更新的记录数(这可能取决于您的DBMS是否返回该信息)。http://ar.ru

  10. ruby-on-rails - 按天对 Mongoid 对象进行分组 - 2

    在控制台中反复尝试之后，我想到了这种方法，可以按发生日期对类似activerecord的(Mongoid)对象进行分组。我不确定这是完成此任务的最佳方法，但它确实有效。有没有人有更好的建议，或者这是一个很好的方法？#eventsisanarrayofactiverecord-likeobjectsthatincludeatimeattributeevents.map{|event|#converteventsarrayintoanarrayofhasheswiththedayofthemonthandtheevent{:number=>event.time.day,:event=>ev

随机推荐