代码之家 › 专栏 › 技术社区 › viraptor

易失性数据查询的优化

optimization mysql

viraptor · 技术社区 · 14 年前

我正在尝试解决一个MySQL-5.0db的延迟问题。

查询本身非常简单: SELECT SUM(items) FROM tbl WHERE col = 'val'
上面有索引 col 在最坏的情况下,总和不超过10000个值(平均值为 count(items) 对于以下所有值 科尔 大约10岁)。
这张桌子最多有两排。
查询的运行频率足够高,有时执行时间长达10秒,尽管99%的查询需要花费<<1秒
这个查询并不是真正可执行的——在几乎所有情况下,像这样的每个查询都将在下一分钟后插入到该表中,并且显示旧值是不可能的(账单信息)。
钥匙足够好-100%命中率

我要查找的结果是每个查询<1。是否有任何方法可以在不更改表的情况下改进选择时间?或者,是否有任何有助于解决问题的有趣更改?我想简单地创建一个表,在每次插入后立即更新每个列的当前和,但是也许有更好的方法可以做到这一点?

2 回复 | 直到 14 年前

Martin 14 年前

另一种方法是添加摘要表:

create table summary ( col varchar(10) primary key, items int not null );

在tbl中添加一些触发器,以便:

关于插入:

insert into summary values( new.col, new.items ) 
on duplicate key update set summary.items = summary.items + new.items;

关于删除:

update summary set summary.items = summary.items - old.items where summary.col = old.col

更新:

update summary set summary.items = summary.items - old.items where summary.col = old.col;
update summary set summary.items = summary.items + new.items where summary.col = new.col;

这将减慢插入速度,但允许您在摘要表中单击

select items from summary where col = 'val';

最大的问题是启动摘要表的值。如果可以使应用程序脱机,则可以使用tbl中的值轻松初始化摘要。

insert into summary select col, sum(items) from tbl group by col;

但是,如果您需要保持服务的运行,那么就更加困难了。如果有副本,则可以停止复制、构建摘要表、安装触发器、重新启动复制,然后将服务故障转移到使用副本,然后在失效的主服务器上重复该过程。

如果您不能这样做,那么您可以一次更新汇总表的一个col值,以减少影响:

lock table write tbl, summary; 
delete from summary where col = 'val';
insert into summary select col, sum(items) from tbl where col = 'val';
unlock tables;

或者,如果您可以忍受长时间停机:

lock table write tbl, summary;
delete from summary;
insert into summary select col, sum(items) from tbl group by col;
unlock tables;

Martin 14 年前

覆盖指数有助于:

create index cix on tbl (col, items);

这样就可以在不读取数据文件的情况下执行求和—这应该更快。

您还应该跟踪您的密钥缓冲区有多有效,以及是否需要为其分配更多内存。这可以通过轮询服务器状态并监视“key%”值来完成:

SHOW STATUS LIKE 'Key%';

MySQL Manual - show status

密钥读取请求(即索引查找数)与密钥读取(即需要从磁盘读取索引块的请求数)之间的比率非常重要。磁盘读取次数越高,查询运行速度越慢。您可以通过增加配置文件中的keybuffer大小来改进这一点。