草庐IT

MYSQL - 索引和优化选择查询

coder 2023-10-05 原文

我有一个超过 500 万行的表。当我执行选择查询时，大约需要 20 秒。

SELECT CompUID,Weburl FROM `CompanyTable` WHERE (Alias1='match1' AND Alias2='match2' )OR Alias3='match3' OR Alias4='match4'

表结构如下:

CREATE TABLE `CompanyMaster` (
  `CompUID` int(11) NOT NULL AUTO_INCREMENT,
  `Weburl` varchar(150) DEFAULT NULL,
  `CompanyName` varchar(200) DEFAULT NULL,
  `Alias1` varchar(150) DEFAULT NULL,
  `Alias2` varchar(150) DEFAULT NULL,
  `Alias3` varchar(150) DEFAULT NULL,
  `Alias4` varchar(150) DEFAULT NULL,  
  `Created` datetime DEFAULT NULL,
  `LastModified` datetime DEFAULT NULL,  
  PRIMARY KEY (`CompUID`),
  KEY `Alias` (`Alias1`,`Alias2`,`Alias3`,`Alias4`)
) ENGINE=InnoDB AUTO_INCREMENT=5457968 DEFAULT CHARSET=latin1

这是来自该查询的 EXPLAIN:

--------+------------------------------------------------------------------------------------------------------+
| id | select_type | table        | type  | possible_keys | key    | key_len  | ref  | rows    | Extra         |
+----+-------------+----------+-------+---------------+------+---------+------+---------+----------------------+
|  1 | SIMPLE      | CompanyTable | ALL   |     Alias     | NULL   | NULL     | NULL | 5255929 |  Using where  |
+----+-------------+----------+-------+---------------+------+---------+------+---------+----------------------+

我使用了复合索引Alias (Alias1,Alias2,Alias3,Alias4)。 但我相信这不是最好的。请为此选择查询查找建议正确的索引。

最佳答案

要让查询引擎使用复合索引中的列，左边的列必须首先得到满足。也就是说，列必须用作减少从左到右读取的候选行的限制。

OR alias3(或 alias4)子句违反了这条规则，因为它说“我不关心左边的部分(alias1 或 alias2(或 alias3))是什么，因为我不依赖它们”。

然后需要进行全表扫描，看是否有任何个alias3(或alias4)值满足条件。

在这种情况下可能有用的指标是:

  • INDEX(alias1, alias2): alias1 AND alias2 覆盖了这个复合索引
  • 索引(别名3)
  • 索引(别名4)

实际统计 and plan selection需要进一步调查 - 但至少现在查询规划器可以使用一些东西。

话虽这么说 - 我不确定“别名”的作用是什么 - 规范化表格可能有意义。以下确实略微改变了语义，因为它删除了“别名位置”(可以重新添加)并且应该验证语义正确性。

CREATE TABLE `CompanyMaster` (
  `CompUID` int(11) NOT NULL AUTO_INCREMENT
 ,`CompanyName` varchar(200) DEFAULT NULL
 ,PRIMARY KEY (`CompUID`)
)

-- (This establishes a unique alias-per-company, which may be incorrect.)
CREATE TABLE `CompaniesAliases` (
  `CompUID` int(11) NOT NULL
 ,`Alias` varchar(150) NOT NULL
  -- Both CompUID and Alias appear in 'first' positions:
  --   CompUID for Join, Alias for filter
 ,PRIMARY KEY (`CompUID`, `Alias`)
 ,KEY (`Alias`)
 -- Alternative, which may change plan selection by eliminating options:
 -- ,PRIMARY KEY (`Alias`, `CompUID`) -- and no single KEY/index on Alias or CompUID
 ,FOREIGN KEY(CompUID) REFERENCES CompanyMaster(CompUID)
)

然后可以查询它与原始查询大致相似，不同之处在于它不关心哪个“别名”匹配哪个值:

-- AND constructed by joins (could also use GROUP BY .. HAVING COUNT)
SELECT c.CompUID FROM `CompanyTable` c
JOIN `CompaniesAliases` ac1
ON ac1.CompUID = c.CompUID AND Alias = 'match1'
JOIN `CompaniesAliases` ac2
ON ac2.CompUID = c.CompUID AND Alias = 'match2'

-- OR constructed by union(s)
UNION
SELECT c.CompUID FROM `CompanyTable` c
JOIN `CompaniesAliases` ac1
ON ac1.CompUID = c.CompUID AND (Alias = 'match3' OR Alias = 'match4')

我希望在 SQL Server - YMMV with MySQL 中有效地实现这样的查询。

关于MYSQL - 索引和优化选择查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/35739385/

MYSQL索引AliasCompUIDcodeselectindexing

有关MYSQL - 索引和优化选择查询的更多相关文章

  1. 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.

  2. 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

  3. ruby - Rails 3 的 RGB 颜色选择器 - 2

    状态:我正在构建一个应用程序，其中需要一个可供用户选择颜色的字段，该字段将包含RGB颜色代码字符串。我已经测试了一个看起来很漂亮但效果不佳的。它是“挑剔的颜色”，并托管在此存储库中:https://github.com/Astorsoft/picky-color.在这里我打开一个关于它的一些问题的问题。问题:请建议我在Rails3应用程序中使用一些颜色选择器。 最佳答案 也许页面上的列表jQueryUIDevelopment:ColorPicker为您提供开箱即用的产品。原因是jQuery现在包含在Rails3应用程序中，因此使用基

  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. 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中提取小时

  6. 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-

  7. ruby - 我正在学习编程并选择了 Ruby。我应该升级到 Ruby 1.9 吗？ - 2

    我完全不是程序员，正在学习使用Ruby和Rails框架进行编程。我目前正在使用Ruby1.8.7和Rails3.0.3，但我想知道我是否应该升级到Ruby1.9，因为我真的没有任何升级的“遗留”成本。缺点是什么？我是否会遇到与普通gem的兼容性问题，或者甚至其他我不太了解甚至无法预料的问题？ 最佳答案 你应该升级。不要坚持从1.8.7开始。如果您发现不支持1.9.2的gem，请避免使用它们(因为它们很可能不被维护)。如果您对gem是否兼容1.9.2有任何疑问，您可以在以下位置查看:http://www.railsplugins.or

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

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

  9. ruby-on-rails - Rails 单选按钮 - 模型中多列的一种选择 - 2

    我希望用户从一个模型的三个选项中选择一个。即我有一个模型视频，可以被评为正面/负面/未知目前我有三列bool值(pos/neg/unknown)。这是处理这种情况的最佳方式吗？为此，表单应该是什么样的？目前我有类似的东西但显然它允许多项选择，而我试图将它限制为只有一个..怎么办？ 最佳答案 如果要使用字符串列，让我们说rating。然后在你的表单中:#...#...它只允许一个选择编辑完全相同但使用radio_button_tag: 关于ruby-on-rails-Rails单选按钮-模

  10. ruby-on-rails - CarrierWave - PDF - 只选择第一页 - 2

    我的Rails应用程序中安装了carrierwave。但是，当用户上传多页pdf时，我只希望应用程序获取文档中的第一页并将其转换为jpeg。这可能吗？用什么命令？这是我的uploader。#encoding:utf-8classImageUploader[200,300]##defscale(width,height)##dosomething#end#Createdifferentversionsofyouruploadedfiles:version:thumbdoprocess:resize_to_fill=>[150,210]process:convert=>:jpgdefful

随机推荐