草庐IT

mysql - 有没有更好的索引来加速这个查询？

coder 2023-10-26 原文

以下查询使用临时和文件排序。如果可能的话，我想避免这种情况。

SELECT lib_name, description, count(seq_id), floor(avg(size)) 
FROM libraries l JOIN sequence s ON (l.lib_id=s.lib_id)
WHERE s.is_contig=0 and foreign_seqs=0 GROUP BY lib_name;

EXPLAIN 说:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,s,ref,libseq,contigs,contigs,4,const,28447,Using temporary; Using filesort
1,SIMPLE,l,eq_ref,PRIMARY,PRIMARY,4,s.lib_id,1,Using where

表格如下所示:

图书馆

CREATE TABLE  `libraries` (
  `lib_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lib_name` varchar(30) NOT NULL,
  `method_id` int(10) unsigned DEFAULT NULL,
  `lib_efficiency` decimal(4,2) unsigned DEFAULT NULL,
  `insert_avg` decimal(5,2) DEFAULT NULL,
  `insert_high` decimal(5,2) DEFAULT NULL,
  `insert_low` decimal(5,2) DEFAULT NULL,
  `amtvector` decimal(4,2) unsigned DEFAULT NULL,
  `description` text,
  `foreign_seqs` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 means the sequences in this library are not ours',
  PRIMARY KEY (`lib_id`),
  UNIQUE KEY `lib_name` (`lib_name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

顺序

CREATE TABLE  `sequence` (
  `seq_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seq_name` varchar(40) NOT NULL DEFAULT '',
  `lib_id` int(10) unsigned DEFAULT NULL,
  `size` int(10) unsigned DEFAULT NULL,
  `add_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sequencing_date` date DEFAULT '0000-00-00',
  `comment` text DEFAULT NULL,
  `is_contig` int(10) unsigned NOT NULL DEFAULT '0',
  `fasta_seq` longtext,
  `primer` varchar(15) DEFAULT NULL,
  `gc_count` int(10) DEFAULT NULL,
  PRIMARY KEY (`seq_id`),
  UNIQUE KEY `seq_name` (`seq_name`),
  UNIQUE KEY `libseq` (`lib_id`,`seq_id`),
  KEY `primer` (`primer`),
  KEY `sgitnoc` (`seq_name`,`is_contig`),
  KEY `contigs` (`is_contig`,`seq_name`) USING BTREE,
  CONSTRAINT `FK_sequence_1` FOREIGN KEY (`lib_id`) REFERENCES `libraries` (`lib_id`)
) ENGINE=InnoDB AUTO_INCREMENT=61508 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

我可以做哪些更改来加快查询速度？如果不是，什么时候(对于 Web 应用程序)值得将上述查询的结果放入 MEMORY 表中？

最佳答案

第一个策略:让mySQL更快地找到你想要汇总的记录。

您已经在 sequence.is_contig 上获得了索引。您可以尝试在 libraries.foreign_seqs 上建立索引。我不知道这是否有帮助，但值得一试。

第二个策略:看看你能否让你的排序在内存中运行，而不是在文件中运行。尝试增大 sort_buffer_size 参数。这将消耗服务器上的 RAM，但这就是 RAM 的用途。

第三种策略:如果您的应用程序需要经常执行此查询但只更新基础数据很少，请采纳您自己的建议并创建一个汇总表。也许使用一个事件来重新制作汇总表。每隔几分钟运行一次。如果您要遵循该策略，请先创建一个包含此表的 View ，然后让您的应用从该 View 中检索信息。然后让汇总表工作，删除 View ，并为汇总表提供与 View 相同的名称。这样，您的数据模型工作和应用程序设计工作就可以相互独立进行。

最后的建议:如果这是真正缓慢变化的摘要数据，请切换到 myISAM。这种数据整理要快一些。

关于mysql - 有没有更好的索引来加速这个查询？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/3637525/

引来mysqlDEFAULTNULLunsignedquery-optimization

有关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 - 难道Lua没有和Ruby的method_missing相媲美的东西吗？ - 2

    我好像记得Lua有类似Ruby的method_missing的东西。还是我记错了？ 最佳答案 表的metatable的__index和__newindex可以用于与Ruby的method_missing相同的效果。 关于ruby-难道Lua没有和Ruby的method_missing相媲美的东西吗？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/7732154/

  3. ruby-on-rails - rails 目前在重启后没有安装 - 2

    我有一个奇怪的问题:我在rvm上安装了ruby​​onrails。一切正常，我可以创建项目。但是在我输入“railsnew”时重新启动后，我有“程序'rails'当前未安装。”。SystemUbuntu12.04ruby-v"1.9.3p194"gemlistactionmailer(3.2.5)actionpack(3.2.5)activemodel(3.2.5)activerecord(3.2.5)activeresource(3.2.5)activesupport(3.2.5)arel(3.0.2)builder(3.0.0)bundler(1.1.4)coffee-rails(

  4. ruby - 在没有 sass 引擎的情况下使用 sass 颜色函数 - 2

    我想在一个没有Sass引擎的类中使用Sass颜色函数。我已经在项目中使用了sassgem，所以我认为搭载会像以下一样简单:classRectangleincludeSass::Script::FunctionsdefcolorSass::Script::Color.new([0x82,0x39,0x06])enddefrender#hamlengineexecutedwithcontextofself#sothatwithintemlateicouldcall#%stop{offset:'0%',stop:{color:lighten(color)}}endend更新:参见上面的#re

  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-on-rails - 更好的替代方法 try( :output). try( :data). try( :name)? - 2

    “输出”是一个序列化的OpenStruct。定义标题try(:output).try(:data).try(:title)结束什么会更好？:) 最佳答案 或者只是这样:deftitleoutput.data.titlerescuenilend 关于ruby-on-rails-更好的替代方法try(:output).try(:data).try(:name)?，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.c

  7. 没有类的 Ruby 方法？ - 2

    大家好!我想知道Ruby中未使用语法ClassName.method_name调用的方法是如何工作的。我头脑中的一些是puts、print、gets、chomp。可以在不使用点运算符的情况下调用这些方法。为什么是这样？他们来自哪里？我怎样才能看到这些方法的完整列表？ 最佳答案 Kernel中的所有方法都可用于Object类的所有对象或从Object派生的任何类。您可以使用Kernel.instance_methods列出它们。 关于没有类的Ruby方法？，我们在StackOverflow

  8. ruby-on-rails - Rails 3，嵌套资源，没有路由匹配 [PUT] - 2

    我真的为这个而疯狂。我一直在搜索答案并尝试我找到的所有内容，包括相关问题和stackoverflow上的答案，但仍然无法正常工作。我正在使用嵌套资源，但无法使表单正常工作。我总是遇到错误，例如没有路线匹配[PUT]"/galleries/1/photos"表格在这里:/galleries/1/photos/1/edit路线.rbresources:galleriesdoresources:photosendresources:galleriesresources:photos照片Controller.rbdefnew@gallery=Gallery.find(params[:galle

  9. ruby-on-rails - 使用 ruby​​ 将多个实例变量转换为散列的更好方法？ - 2

    我收到格式为的回复#我需要将其转换为哈希值(针对活跃商家)。目前我正在遍历变量并执行此操作:response.instance_variables.eachdo|r|my_hash.merge!(r.to_s.delete("@").intern=>response.instance_eval(r.to_s.delete("@")))end这有效，它将生成{:first="charlie",:last=>"kelly"},但它似乎有点hacky和不稳定。有更好的方法吗？编辑:我刚刚意识到我可以使用instance_variable_get作为该等式的第二部分，但这仍然是主要问题。

  10. ruby-on-rails - 有没有办法为 CarrierWave/Fog 设置上传进度指示器？ - 2

    我在Rails应用程序中使用CarrierWave/Fog将视频上传到AmazonS3。有没有办法判断上传的进度，让我可以显示上传进度如何？ 最佳答案 CarrierWave和Fog本身没有这种功能；你需要一个前端uploader来显示进度。当我不得不解决这个问题时，我使用了jQueryfileupload因为我的堆栈中已经有jQuery。甚至还有apostonCarrierWaveintegration因此您只需按照那里的说明操作即可获得适用于您的应用的进度条。 关于ruby-on-r

随机推荐