草庐IT

mysql - 加速联合查询需要复合索引吗？

coder 2023-10-03 原文

一位同事让我解释索引(索引？)如何提高性能；我尝试这样做，但自己也感到困惑。
我使用下面的模型进行解释(错误/诊断日志数据库)。它由三个表组成:

  • 业务系统列表，包含其名称的“System”表
  • 不同类型的跟踪列表，表“TraceTypes”，定义可以记录哪些类型的错误消息
  • 实际跟踪消息，具有来自System 和TraceTypes 表的外键

我在演示中使用了 MySQL，但我不记得我使用的表类型。我认为是 InnoDB。 

 System                                TraceTypes
-----------------------------         ------------------------------------------
| ID          | Name        |         | ID    | Code   | Description           |
-----------------------------         ------------------------------------------
| 1           | billing     |         | 1     | Info   | Informational mesage  |
| 2           | hr          |         | 2     | Warning| Warning only          |
-----------------------------         | 3     | Error  | Failure               |
           |                          ------------------------------------------
           |                ------------|
 Traces    |                |            
 --------------------------------------------------
 | ID | System_ID | TraceTypes_ID | Message       |
 --------------------------------------------------
 | 1  |  1        |  1            | Job starting  |
 | 2  |  1        |  3            | System.nullr..|
 --------------------------------------------------

首先，我向所有表中添加了一些记录，并证明下面的查询在 0.005 秒内执行:

select count(*) from Traces 
  inner join System on Traces.System_ID = System.ID
  inner join TraceTypes on Traces.TraceTypes_ID = TraceTypes.ID
where 
  System.Name='billing' and TraceTypes.Code = 'Info'

然后我生成了更多数据(还没有索引)

  • “系统”包含大约 100 个条目
  • “TraceTypes”包含大约 50 个条目
  • “痕迹”包含约 1000 万条记录。

现在之前的查询需要 8-10 秒。

我在 Traces.System_ID 列和 Traces.TraceTypes_ID 列上创建了索引。现在这个查询以毫秒为单位执行:

select count(*) from Traces where System_id=1 and TraceTypes_ID=1;

这也很快:

select count(*) from Traces 
  inner join System on Traces.System_ID = System.ID
where System.Name='billing' and TraceTypes_ID=1;

但是之前连接所有三个表的查询仍然需要 8-10 秒才能完成。

只有当我创建复合索引时(索引中包含 System_ID 和 TraceTypes_ID 列)，速度才下降到毫秒。

我之前学到的基本语句是“你用于连接的所有列都必须被索引”。
但是，在我的场景中，我在 System_ID 和 TraceTypes_ID 上都有索引，但是 MySQL 没有使用它们。问题是——为什么？我敢打赌——项目计数比率 100:10,000,000:50 会使单列索引太大而无法使用。但这是真的吗？

最佳答案

首先，分析慢速 SQL 语句的正确且最简单的方法是执行 EXPLAIN。了解优化器如何选择其计划，并思考为什么以及如何改进它。我建议仅使用 2 个单独的索引来研究 EXPLAIN 结果，以了解 mysql 如何执行您的语句。

我对 MySQL 不是很熟悉，但是 MySQL 4 似乎限制在查询中涉及的每个表只能使用一个索引。自 MySQL 5 ( index merge ) 以来似乎对此有所改进，但我不确定它是否适用于您的情况。同样，EXPLAIN 应该告诉您真相。

即使允许每个表使用 2 个索引(MySQL 5)，使用 2 个单独的索引通常比复合索引慢。与使用复合索引的单次传递相比，使用 2 个单独的索引需要索引合并步骤。

Multi Column indexes vs Index Merge可能会有帮助，它使用 MySQL 5.4.2。

关于mysql - 加速联合查询需要复合索引吗？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/2047772/

mysql加速TraceTypesSystemcodeperformancejoinindexing

有关mysql - 加速联合查询需要复合索引吗？的更多相关文章

  1. ruby - 我需要将 Bundler 本身添加到 Gemfile 中吗？ - 2

    当我使用Bundler时，是否需要在我的Gemfile中将其列为依赖项？毕竟，我的代码中有些地方需要它。例如，当我进行Bundler设置时:require"bundler/setup" 最佳答案 没有。您可以尝试，但首先您必须用鞋带将自己抬离地面。 关于ruby-我需要将Bundler本身添加到Gemfile中吗？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/4758609/

  2. ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2

    我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.

  3. ruby - rspec 需要 .rspec 文件中的 spec_helper - 2

    我注意到像bundler这样的项目在每个specfile中执行requirespec_helper我还注意到rspec使用选项--require，它允许您在引导rspec时要求一个文件。您还可以将其添加到.rspec文件中，因此只要您运行不带参数的rspec就会添加它。使用上述方法有什么缺点可以解释为什么像bundler这样的项目选择在每个规范文件中都需要spec_helper吗？ 最佳答案 我不在Bundler上工作，所以我不能直接谈论他们的做法。并非所有项目都checkin.rspec文件。原因是这个文件，通常按照当前的惯例，只

  4. ruby - 如何在 Lion 上安装 Xcode 4.6，需要用 RVM 升级 ruby - 2

    我实际上是在尝试使用RVM在我的OSX10.7.5上更新ruby，并在输入以下命令后:rvminstallruby我得到了以下回复:Searchingforbinaryrubies,thismighttakesometime.Checkingrequirementsforosx.Installingrequirementsforosx.Updatingsystem.......Errorrunning'requirements_osx_brew_update_systemruby-2.0.0-p247',pleaseread/Users/username/.rvm/log/138121

  5. ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2

    我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr

  6. ruby - 为什么在 ruby​​ 中创建 Rational 不需要新方法 - 2

    这个问题在这里已经有了答案:关闭10年前。PossibleDuplicate:Rubysyntaxquestion:Rational(a,b)andRational.new!(a,b)我正在阅读ruby镐书，我对创建有理数的语法感到困惑。Rational(3,4)*Rational(1,2)产生=>3/8为什么Rational不需要new方法(我还注意到例如我可以在没有new方法的情况下创建字符串)？

  7. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  8. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  9. ruby-on-rails - 协会的 Rails 索引 - 2

    我发现自己需要这个。假设cart是一个包含用户列表的模型。defindex_of_itemcart.users.each_with_indexdo|u,i|ifu==current_userreturniendend获取此类关联索引的更简单方法是什么？ 最佳答案 indexArray上的方法与您的index_of_item方法相同，例如cart.users.index(current_user)返回数组中第一个对象的索引==给obj。如果未找到匹配项，则返回nil。 关于ruby-on-

  10. ruby - Rails -- :id attribute? 所需的数据库索引 - 2

    因此，当我遵循MichaelHartl的RubyonRails教程时，我注意到在用户表中，我们为:email属性添加了一个唯一索引，以提高find的效率方法，因此它不会逐行搜索。到目前为止，我们一直在根据情况使用find_by_email和find_by_id进行搜索。然而，我们从未为:id属性设置索引。:id是否自动索引，因为它在默认情况下是唯一的并且本质上是顺序的？或者情况并非如此，我应该为:id搜索添加索引吗？ 最佳答案 大多数数据库(包括sqlite，这是RoR中的默认数据库)会自动索引主键，对于RailsMigration

随机推荐