MySQL优化大表 [英] MySQL optimization of huge table

问题描述

我一直在尝试提高某些SELECT查询的速度. 情况如下: (在我眼中)有一张巨大的十字餐桌.它目前有大约2000万行，但是我希望这个数字会继续增长. 基于此交叉表，我需要创建另一个表.为此，我需要执行以下查询:

I've been trying to get some speed improvements on a certain SELECT query. The situation is as follows: There is a (in my eyes) huge crossing table. It currently has about 20 million rows, but I expect this to grow alot. Based on this crossing table I need to create another table. To do so, I need to execute the following query:

SELECT hugeCrossingTable.field3, otherTable.field1, hugeCrossingTable.field2 * otherTable.field3 AS someName
FROM hugeCrossingTable 
INNER JOIN otherTable ON hugeCrossingTable.field1 = otherTable.field2

现在，这导致大约一百万行.我已经在2个表中的两个field1上都有索引，但是仍然需要18分钟才能完成. 我曾考虑过拆分表，但是随后我需要找到一种方法来拆分数据，并且由于它只是一个交叉表，因此如何进行拆分就没什么主意了.

Now this currently results in about a million rows. I already have indexes on both the field1 in the 2 tables, but it still takes 18 minutes to finish.. I thought about splitting the table, but then I'd need to find a way on how to split the data, and since it's just a crossing table nothing comes to mind on how to do this.

关于如何对其进行优化的任何想法?

Any ideas on how this can be optimized?

谢谢.

根据要求，这是create语句:

On request here's the create statement:

CREATE TABLE  `hugeCrossingTable` (
  `field` int(11) NOT NULL,
  `field1` int(11) NOT NULL,
  `field2` double(10,5) DEFAULT NULL,
  `field3` int(4) DEFAULT NULL,
  KEY `field1` (`field1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `otherTable` (
  `field` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `field1` int(10) unsigned NOT NULL,
  `field2` int(10) unsigned NOT NULL,
  `field3` decimal(5,2) NOT NULL,
  PRIMARY KEY (`field`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

解释输出:

id, select_type, table              , type , possible_keys, key     , key_len, ref                , rows, Extra
1 , 'SIMPLE'   , 'otherTable'       , 'ALL', ''           , ''      , ''     , ''                 , 294 , ''
1 , 'SIMPLE'   , 'hugeCrossingTable', 'ref', 'field1'     , 'field1', '4'    , 'otherTable.field2', 69  , 'Using where'

推荐答案

以下是一些innodb示例，它们适用于大约10个大型表. 60至5亿行证明了设计良好的innodb表的优点以及如何最佳使用聚集索引(仅适用于innodb)

Here are some innodb examples that work on large tables of approx. 60 to 500 million rows that demonstrate the advantages of a well designed innodb table and how best to use clustered indexes (only available with innodb)

MySQL和NoSQL :帮我选择合适的一个

6000万个条目，请从特定月份中选择条目.如何优化数据库?

重写mysql select减少时间并将tmp写入磁盘

您还需要阅读以下内容:

You will also want to read the following:

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

http://www .xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

整理好表格设计并优化innodb配置后，

Once you've sorted out your table designs and optimised your innodb config:

http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

http://www.mysqlperformanceblog.com/2007/11/03 /choosing-innodb_buffer_pool_size/

您可以尝试以下操作:

start transaction;

insert into target_table (x,y) select x,y from source_table order by x,y;

commit;

希望这会有所帮助.

这篇关于MySQL优化大表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！