Mysql提高选择速度

问题描述：

我目前正在尝试提高MySQL表的SELECTS的速度，并希望对改进它的方法提出任何建议。Mysql提高选择速度

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

对表运行的查询是：

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

....而且有如有INSERTS寥寥无几。

我试图通过标记将数据分区到不同数量的分区中，但是这似乎没有增加速度。

当你把`ORDER BY`忽略出来，有帮助吗？你可以发布有和没有ORDER BY的查询的实际时间吗？ – 2011-01-23 18:24:01

发布解释查询 EXPLAIN SELECT日期，值FROM表标记=“a”和日期BETWEEN'x'和'y'按日期排序 – piyush 2011-01-23 18:24:39

您还没有提到索引 - 每列中有一列（标签，日期，值）或包含全部三个的单个复合索引。请注意，列顺序在组合索引中很重要 - 从左侧开始，如果列未在查询中引用，则不会使用索引。 – 2011-01-23 18:28:44

答

需要时间读到这里我的答案：（也有类似的卷到你）

500百万行，0.02秒15万行的范围扫描。

MySQL and NoSQL: Help me to choose the right one

然后修改你的表引擎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...

答

我想说你唯一的机会来进一步改善它是一个涵盖所有三列（标签，数据，价值）的索引。这避免了表访问。

我不认为这种分区可以帮助。

答

我猜想，在(tag, date)添加索引可以帮助：

alter table table add index (tag, date);

请邮寄的结果对这个查询说明（EXPLAIN SELECT日，值FROM ......）

答

我认为value列处于性能问题的底部。它不是索引的一部分，所以我们将拥有表格访问权限。此外，我认为ORDER BY不太可能严重影响性能，因为它是您的索引的一部分，应该订购。

我将通过分区并不真正减少查询的执行时间这一事实来论证我对value列的怀疑。你可以在没有value的情况下执行查询，并进一步给我们一些结果以及EXPLAIN？你是否真的需要每一行，它是什么样的列？

干杯！

答

日期字段的基数（即该字段中出现了多少个不同的值）是什么？如果BETWEEN'x'和'y'的日期比WHERE子句的tag ='a'部分更具有限制性，请尝试使用主键（日期，标记）而不是（标记，日期），从而允许使用日期作为索引值。

此外，请注意如何在WHERE子句中指定'x'和'y'。在某些情况下，MySQL会将每个日期字段转换为与您比较的值的非日期隐含类型相匹配。

答

尝试将所需的日期插入到临时表中，然后在临时表上选择标记和排序。

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;

如果不起作用尝试关闭标签选择创建富来代替。

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;

答

我会做两两件事 - 第一扔在那里周围的标签和日期部分指标上面的建议：

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

答

您的查询要求的几件事情 - 与高＃行的，数据的外观可以改变什么，最好的办法是。

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

有几件事可以减慢这个选择查询。

一个非常大的结果集，必须进行排序（排序）。
一个非常大的结果集。如果标记和日期位于索引中（并且假设它和它一样好），那么每个结果行将不得不离开索引来查找值字段。想想这需要每本书的每一章的第一句话。如果你只需要知道章节名称，简单 - 你可以从目录中得到它，但由于你需要第一句话，你必须去实际的章节。在某些情况下，优化器可能会选择只翻阅整本书（查询计划术语中的表扫描）以获取第一个句子。
首先通过错误的where子句过滤。如果索引位于订单标签中，则日期...标签应该（对于大多数查询）是两列中更严格的。所以基本上，除非你有更多的标签而不是日期（或者可能比日期范围内的日期），那么日期应该是索引中两列中的第一列。

一对夫妇的建议：

考虑是否有可能削减一些数据，如果它太旧，最关心的时间。
尝试使用您当前的索引进行播放 - 即更改其中的项目顺序。
取消当前的索引并将其替换为覆盖索引（其中包含所有3个字段）
运行一些EXPLAIN并确保它使用您的索引。
切换到其他数据存储区（mongo db？）或者另外确保这个怪物表尽可能保留在内存中。

Mysql提高选择速度

相关推荐