代码之家 › 专栏 › 技术社区 › dnagirl

有没有更好的索引来加速这个查询?

query-optimization mysql

dnagirl · 技术社区 · 14 年前

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

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应用程序)值得将上述查询的结果放入内存表中?

1 回复 | 直到 14 年前

O. Jones 14 年前

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

你已经有序列的索引了。您可以尝试在libraries.foreign\seq上建立索引。我不知道这是否有帮助,但值得一试。

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

第三种策略:如果您的应用程序需要进行大量的查询,但只更新一点基础数据,那么您可以根据自己的建议创建一个汇总表。可能使用事件重新创建摘要表,并每隔几分钟运行一次。如果您要遵循这个策略,首先创建一个包含此表的视图,并让您的应用程序从该视图中检索信息。然后让汇总表的内容正常工作,删除视图,并将汇总表命名为与视图相同的名称。这样,您的数据模型工作和应用程序设计工作就可以彼此独立地进行。

最后建议:如果这真的是缓慢地更改摘要数据,请切换到myisam。这种数据处理速度要快一点。