MySQL提高SELECT速度 [英] Mysql improve SELECT speed

问题描述

我目前正在尝试提高MySQL表的SELECTS的速度，并且希望就改进它的方式提出建议.

I'm currently trying to improve the speed of SELECTS for a MySQL table and would appreciate any suggestions on ways to improve it.

我们在表中有3亿多条记录，并且该表具有结构标签，日期和值.主键是标签和日期的组合键.该表包含约600个唯一标签的信息，其中大多数标签平均包含约40万行，但范围可能从2000行到超过1100万行.

We have over 300 million records in the table and the table has the structure tag, date, value. The primary key is a combined key of tag and date. The table contains information for about 600 unique tags most containing an average of about 400,000 rows but can range from 2000 to over 11 million rows.

针对该表运行的查询是:

The queries run against the table are:

  SELECT date,
         value 
    FROM table 
   WHERE tag = "a" 
     AND date BETWEEN 'x' and 'y' 
ORDER BY date

....而且几乎没有插入.

....and there are very few if any INSERTS.

我曾尝试通过标签将数据划分为不同数量的分区，但这似乎并没有提高速度.

I have tried partitioning the data by tag into various number of partitions but this seems to have little increase in speed.

推荐答案

花点时间在这里阅读我的答案:(与您的书卷相似)

take time to read my answer here: (has similar volumes to yours)

5亿行，在0.02秒内扫描1500万行.

500 millions rows, 15 million row range scan in 0.02 seconds.

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

然后将表引擎修改为innodb，如下所示:

then amend your table engine to innodb as follows:

create table tag_date_value
(
tag_id smallint unsigned not null, -- i prefer ints to chars
tag_date datetime not null, -- can we make this date vs datetime ?
value int unsigned not null default 0, -- or whatever datatype you require
primary key (tag_id, tag_date) -- clustered composite PK
)
engine=innodb;

您可以考虑将以下内容用作主键:

you might consider the following as the primary key instead:

primary key (tag_id, tag_date, value) -- added value save some I/O

但仅当值不是某些大型varchar类型时！

but only if value isnt some LARGE varchar type !

像以前一样查询:

select
 tag_date, 
 value
from
 tag_date_value
where
 tag_id = 1 and
 tag_date between 'x' and 'y'
order by
 tag_date;

希望这会有所帮助:)

编辑

哦，忘了提及-不要使用alter table将引擎类型从mysiam更改为innodb，而是将数据转储到csv文件中，然后重新导入到新创建的空innodb表中.

oh forgot to mention - dont use alter table to change engine type from mysiam to innodb but rather dump the data out into csv files and re-import into a newly created and empty innodb table.

请注意，我在导出过程中正在对数据进行排序-聚集索引是关键！

note i'm ordering the data during the export process - clustered indexes are the KEY !

导出

select * into outfile 'tag_dat_value_001.dat' 
fields terminated by '|' optionally enclosed by '"'
lines terminated by '\r\n'
from
 tag_date_value
where
 tag_id between 1 and 50
order by
 tag_id, tag_date;

select * into outfile 'tag_dat_value_002.dat' 
fields terminated by '|' optionally enclosed by '"'
lines terminated by '\r\n'
from
 tag_date_value
where
 tag_id between 51 and 100
order by
 tag_id, tag_date;

-- etc...

导入

以正确的顺序导入到表中！

import back into the table in correct order !

start transaction;

load data infile 'tag_dat_value_001.dat' 
into table tag_date_value
fields terminated by '|' optionally enclosed by '"'
lines terminated by '\r\n'
(
tag_id,
tag_date,
value
);

commit;

-- etc...

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