Mysql优化SELECT速度

Question

Mysql优化SELECT速度

sqlmysqldatabase-designquery-optimizationdatabase-partitioning

11

我目前正试图提高MySQL表的SELECT速度，并希望得到任何可以改进它的建议。

我们的表中有超过3亿条记录，其结构为标签、日期和值。主键是标签和日期的组合键。这个表包含大约600个唯一的标签的信息，其中大多数包含平均约400,000行，但可能范围从2000到超过1100万行。

针对该表运行的查询包括：

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

...而且很少或者根本没有插入操作。

我尝试将数据按标签分成各种不同数量的分区，但速度似乎增加很小。

- allyLogan

当您省略ORDER BY时，是否有所帮助？您能否发布包括和不包括ORDER BY的查询实际时间？ - S.Lott

5

请解释以下查询语句： EXPLAIN SELECT date,value FROM table where tag = "a" and date BETWEEN 'x' and 'y' order by date - piyush

3

你没有提到索引 - 尝试每列使用一个索引（标签，日期，值），或者一个包含所有三个的复合索引。请注意，在复合索引中，列的顺序很重要--从左边开始，如果该列在查询中没有被引用，那么该索引将不会被使用。 - OMG Ponies

@Ponies：主键声明将在（标签，日期）上创建一个索引，是吗？ - Larry Lustig

解释查询结果：id:'1'，select_type:'SIMPLE'，table:'table'，type:'range'，possible_keys:'PRIMARY'，key:'PRIMARY'，key_len:'85'，ref:NULL，rows:'29559'，Extra:'Using where' - allyLogan

我没有看到关于此查询速度的任何细节。它是否经常执行？平均运行时间是多少？目标运行时间是多少？优化查询的最佳方法不是执行它...例如通过缓存结果... - pascal

8个回答

1

我会做两件事情 - 首先按照上面建议的在标签和日期周围添加一些索引：

alter table table add index (tag, date);

接下来，将您的查询分成一个主查询和子查询，在主查询中缩小结果范围：

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

- OregonJeff

1

你的查询涉及到几个方面 - 并且由于行数较多，数据的外观可能会影响最佳方法。

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

有一些因素可能会减缓这个查询的速度。

需要排序的非常大的结果集（order by）。
非常大的结果集。如果标签和日期在索引中（假设这是最好的情况），每个结果行都必须离开索引来查找值字段。可以将其想象成需要获取书的每章第一句话。如果你只需要知道章节名称，那很容易——你可以从目录中得到它，但是由于你需要第一句话，所以你必须去实际的章节中查找。在某些情况下，优化器可能选择翻阅整本书（在查询计划术语中进行表扫描）来获取这些第一句话。
首先按错误的where子句进行过滤。如果索引按标签、日期的顺序排列，则标签应该是两个列中更严格的条件（对于大多数查询）。因此，除非您有比日期更多的标签（或者可能比典型日期范围中的日期更多），否则日期应该是索引中两个列中的第一个。

以下是一些建议：

考虑是否可能截断一些数据，如果它们太旧了，在大多数情况下不需要关心。
尝试调整当前索引 - 即更改其中的项目顺序。
放弃当前索引，并用覆盖索引替换它（其中包含所有3个字段）。
运行一些 EXPLAIN 并确保它完全使用了您的索引。
切换到其他数据存储（如mongo db），或以其他方式尽可能将此巨型表保留在内存中。

- Jody

1

日期字段的基数是多少（即该字段中有多少个不同的值）？如果“date BETWEEN 'x' AND 'y'”比WHERE子句中的“tag ='a'”更具限制性，请尝试将主键设置为（date，tag）而不是（tag，date），以允许使用日期作为索引值。

此外，在指定WHERE子句中的“x”和“y”时要小心。在某些情况下，MySQL会将每个日期字段转换为与您比较的非日期隐含类型匹配的类型。

- Larry Lustig

日期列比标签列具有更多不同的值，因为日期列可以每隔几秒钟就有一个值，它是不可预测的，而标签列仅具有 600 个不同的值。 - allyLogan

根据你的日期范围的宽度，尝试将(date, tag)作为主键可能会获得更好的性能。不妨试试看。 - Larry Lustig

虽然我们称日期为“date”，但实际上它是一个时间戳，每秒钟有一些值，有些标签有1100万个值，因此日期范围非常广。我尝试创建一个新表，以（日期、标签）作为主键，但在大约12小时的数据上传后，仍然只完成了六分之一！！ - allyLogan

时间戳的基数增加使其成为主键中第一个良好的选择，但仅当您执行的搜索返回相对较小的时间戳值范围时。因此，看起来我的建议最终对您没有帮助。 - Larry Lustig

0

我猜在(tag, date)上添加索引会有帮助：

alter table table add index (tag, date);

请发布此查询的 explain 结果（EXPLAIN SELECT date, value FROM ......）。

- Arnaud Le Blanc

主键声明将创建该索引。 - Larry Lustig

可能是MyISAM引擎，因此是非聚集B树主键索引 - 哎呀 - Jon Black

0

尝试将所需日期插入临时表中，然后使用临时表进行标记和排序的选择。

CREATE temporary table foo
SELECT date, value 
FROM table 
WHERE date BETWEEN 'x' and 'y' ;

ALTER TABLE foo ADD INDEX index( tag );

SELECT date, value 
FROM foo 
WHERE tag = "a" 
ORDER BY date;

如果那不起作用，尝试从标签选择中创建foo。

CREATE temporary table foo
SELECT date, value 
FROM table 
WHERE tag = "a";    

ALTER TABLE foo ADD INDEX index( date );

SELECT date, value 
FROM foo 
WHERE date BETWEEN 'x' and 'y' 
ORDER BY date;

- riotopsys

0

我认为你进一步改进的唯一机会就是使用覆盖索引，包含所有三个列（标签、数据、值）。这样可以避免表格访问。

我不认为分区可以帮助解决这个问题。

- Markus Winand

0

我认为你的性能问题在于value列位于底部。它不是索引的一部分，因此我们将需要访问整个表。此外，我认为ORDER BY不太可能对性能产生如此严重的影响，因为它是索引的一部分，应该是有序的。

我会通过分区并不能真正减少查询执行时间来证明我对value列的怀疑。您可以尝试在没有value的情况下执行查询，并进一步给我们一些结果以及EXPLAIN？您真的需要每行都有它吗？它是什么类型的列？

干杯！

- Lachezar Balev

该值为VARCHAR类型，每个查询都需要。我已经在上面发布了EXPLAIN... - allyLogan

所以似乎仍然存在问题。如果您在测试值为空的情况下运行查询，会有显著的增加吗？ - Lachezar Balev

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jon Black · Accepted Answer

请仔细阅读我的答案：（与您的类似）

500万行数据，15万行范围扫描时间为0.02秒。

MySQL和NoSQL：帮助我选择正确的数据库

然后按以下方式修改您的表引擎为InnoDB：

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;

您可能会考虑以下内容作为主键：

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

但仅当值不是一些大型 varchar 类型时才能执行！

与之前相同的查询：

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表中。

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

导出

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

导入

将数据按照正确的顺序导入回表格中！

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