草庐IT

mysql 最佳索引顺序 (FK, key, value)

coder 2023-10-22 原文

我有一个键值表:

id, data_id, key, value

引用数据表:

id, file_id, data

它又引用一个文件表:

id, name

文件包含大约 10000 个条目 数据包含约1亿个条目

键列有大约 100 个不同的值 值字段有许多不同的值(字符串)

现在我想知道这三列的顺序 data_id, key, value 应该在我的索引中。

我感兴趣的查询查找具有特定 file_id 和特定键值组合的数据条目。例如:

SELECT * FROM files, data, keyval
WHERE files.id = data.file_id
AND data.id = keyval.data_id
AND files.id = 999
AND keyval.key = 'revenue'
AND keyval.val = 9.99

最佳答案

索引中列的“最佳顺序”将取决于访问表的实际查询(多个查询)。

如果我们运行的查询中唯一的谓词是 WHERE data.key = 'foo'，那么最大的好处是使用以 key 作为索引的索引领先的专栏。

如果我们没有任何查询在 key 列上有谓词(在 WHERE 子句或 ON 子句中)，并且没有查询执行 GROUP BY 数据。 key, ...，那么我们可能不需要将 key 作为任何索引中的前导列。

有时我们会添加多个索引，具有不同的集合和列的顺序，以满足各种查询。

索引中列顺序的选择不仅取决于数据类型和值分布，还取决于我们计划运行的实际查询的性能要求。

编辑

添加到问题中的查询具有以下形式:

 SELECT f.*
      , d.*
      , k.*
   FROM files f
   JOIN data d
     ON d.file_id = f.id
   JOIN keyval k
     ON k.data_id = d.id
    AND k.key     = 'revenue'
    AND k.val     = 9.99
  WHERE f.id = 999

假设
id 是 files 中的 PRIMARY KEY(或 UNIQUE KEY)，并且
id 是 data

中的 PRIMARY KEY(或 UNIQUE KEY)

我会创建索引

 ON data (file_id,id)
 ON keyval (data_id,key,val)

我会查看查询的 EXPLAIN 输出以了解查询计划以及正在使用的索引。

如果我们没有对 file.id 进行相等预测，并且如果 key='revenue' and val=9.99 非常 选择性， 那么我们可能希望 keyval 作为驱动表。我们将确保一个可用的索引具有 key 和 val 作为前导列，例如

ON keyval(key,val,data_id)

如果查询等价于:

 SELECT f.*
      , d.*
      , k.*
   FROM keyval k
   JOIN data
     ON d.id  = k.data_id
   JOIN files f
     ON f.id  = d.file_id
 -- AND f.id  = 999
  WHERE k.key = 'revenue'
    AND k.val = 9.99

此外，我们真的需要从所有 三个表中提取所有 列吗？ 还是我们只是使用 * 作为快捷方式，以避免列出我们实际需要的列？

关于mysql 最佳索引顺序 (FK, key, value)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/48670285/

mysqlvaluecodedataidindexing

有关mysql 最佳索引顺序 (FK, key, value)的更多相关文章

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

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

  2. ruby - 解析 RDFa、微数据等的最佳方式是什么，使用统一的模式/词汇(例如 schema.org)存储和显示信息 - 2

    我主要使用Ruby来执行此操作，但到目前为止我的攻击计划如下:使用gemsrdf、rdf-rdfa和rdf-microdata或mida来解析给定任何URI的数据。我认为最好映射到像schema.org这样的统一模式，例如使用这个yaml文件，它试图描述数据词汇表和opengraph到schema.org之间的转换:#SchemaXtoschema.orgconversion#data-vocabularyDV:name:namestreet-address:streetAddressregion:addressRegionlocality:addressLocalityphoto:i

  3. ruby - Chef 执行非顺序配方 - 2

    我遵循了教程http://gettingstartedwithchef.com/,第1章。我的运行list是"run_list":["recipe[apt]","recipe[phpap]"]我的phpapRecipe默认Recipeinclude_recipe"apache2"include_recipe"build-essential"include_recipe"openssl"include_recipe"mysql::client"include_recipe"mysql::server"include_recipe"php"include_recipe"php::modul

  4. 使用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

  5. ruby-on-rails - Rails 单表继承 : How to override the value written to the type field - 2

    在我的系统中，我已经定义了STI。Dog继承自Animal，在animals表中有一个type列，其值为"Dog"。现在我想让SpecialDog继承自dog，只是为了在某些特殊情况下稍微修改一下行为。数据还是一样。我需要通过SpecialDog运行的所有查询，以返回数据库中类型为Dog的值。我的问题是因为我有一个type列，rails将WHERE"animals"."type"IN('SpecialDog')附加到我的查询中，所以我不能获取原始的Dog条目。所以我想要的是以某种方式覆盖rails在通过SpecialDog访问数据库时使用的值，使其表现得像Dog。有没有办法覆盖用于类型

  6. ruby-on-rails - Ruby - 如何从 ruby​​ 上的 .pfx 文件中提取公钥、rsa 私钥和 CA key - 2

    我有一个.pfx格式的证书，我需要使用ruby​​提取公共(public)、私有(private)和CA证书。使用shell我可以这样做:#ExtractPublicKey(askforpassword)opensslpkcs12-infile.pfx-outfile_public.pem-clcerts-nokeys#ExtractCertificateAuthorityKey(askforpassword)opensslpkcs12-infile.pfx-outfile_ca.pem-cacerts-nokeys#ExtractPrivateKey(askforpassword)o

  7. ruby-on-rails - 在 RSpec 中，如何以任意顺序期望具有不同参数的多条消息？ - 2

    RSpec似乎按顺序匹配方法接收的消息。我不确定如何使以下代码工作:allow(a).toreceive(:f)expect(a).toreceive(:f).with(2)a.f(1)a.f(2)a.f(3)我问的原因是a.f的一些调用是由我的代码的上层控制的，所以我不能对这些方法调用添加期望。 最佳答案 RSpecspy是测试这种情况的一种方式。要监视一个方法，用allowstub，除了方法名称之外没有任何约束，调用该方法，然后expect确切的方法调用。例如:allow(a).toreceive(:f)a.f(2)a.f(1)

  8. ruby-on-rails - Rails 中同一个类的多个关联的最佳实践？ - 2

    我认为我的问题最好用一个例子来描述。假设我有一个名为“Thing”的简单模型，它有一些简单数据类型的属性。像...Thing-foo:string-goo:string-bar:int这并不难。数据库表将包含具有这三个属性的三列，我可以使用@thing.foo或@thing.bar之类的东西访问它们。但我要解决的问题是当“foo”或“goo”不再包含在简单数据类型中时会发生什么？假设foo和goo代表相同类型的对象。也就是说，它们都是“Whazit”的实例，只是数据不同。所以现在事情可能看起来像这样......Thing-bar:int但是现在有一个新的模型叫做“Whazit”，看起来

  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-on-rails - 向 Rails 3 添加 Ruby 扩展方法的最佳实践？ - 2

    我有一个要在我的Rails3项目中使用的数组扩展方法。它应该住在哪里？我有一个应用程序/类，我最初把它放在(array_extensions.rb)中，在我的config/application.rb中我加载路径:config.autoload_paths+=%W(#{Rails.root}/应用程序/类)。但是，当我转到railsconsole时，未加载扩展。是否有一个预定义的位置可以放置我的Rails3扩展方法？或者，一种预先定义的方式来添加它们？我知道Rails有自己的数组扩展方法。我应该将我的添加到active_support/core_ext/array/conversion

随机推荐