MySQL查询优化大表
问题描述:
我有一个非常大的图像数据库,我需要运行更新来增加图像的查看计数。每个小时都有超过一百万个独特的行需要更新。现在运行这个查询需要大约一个小时,无论如何要让这个运行速度更快?MySQL查询优化大表
我创建一个内存表:
insert low_priority into tmp_views_table
values ('key', 'count'),('key', 'count'),('key', 'count'), etc...
:
CREATE TABLE IF NOT EXISTS tmp_views_table (
key VARCHAR(7) NOT NULL,
views INT NOT NULL,
primary key (`key`)
) ENGINE = MEMORY
然后我使用运行,直到所有的意见已插入存储表中的循环插入在同一时间观看1000次
然后我运行的实际表的更新是这样的:
update images, tmp_views_table
set images.views = images.views+tmp_views_table.views
where images.key = tmp_views_table.key
这最后一次更新是一个是TA国王一个小时左右,内存表的东西运行得非常快。
有没有更快的方法,我可以做这个更新?
答
你在使用Innodb吗?尝试对mysql和innodb引擎进行一般调整,以实现更快的数据更改。
我想你有key
字段的images
表的索引。你可以尝试你的更新查询也没有索引的内存表 - 在这种情况下,查询优化器应该选择内存表的全表扫描。
我从来没有使用UPDATE语句连接,所以我不知道它是否被执行,但也许JOIN需要很长时间。也许你可以发布该查询的EXPLAIN结果。
下面是我在一个项目中用来做类似事情 - 将实时数据插入/更新到临时表并将其合并到聚集表,每天一次,因此可以尝试它是否会更快地执行。
INSERT INTO st_views_agg (pageid,pagetype,day,count) SELECT pageid,pagetype,DATE(`when`) AS day, COUNT(*) AS count FROM st_views_pending WHERE (pagetype=4) GROUP BY pageid,pagetype,day ON DUPLICATE KEY UPDATE count=count+VALUES(count);
数字ID比varchar更有意义。另外,你的表格是否被索引? – 2012-01-05 18:37:56
我不确定内存表的索引在哪里保存,但是它们不会减慢这些插入/更新操作,特别是在** MEMORY **引擎的情况下? – Rolice 2012-01-05 18:58:20
@OliCharlesworth id是一个散列,所以它包含字母和数字,是的主键是关键或散列 – Brian 2012-01-05 18:58:49