MYSQL:需要帮助快速增长表和降低速度(4mio 行)

Question

我正面临一些问题，表的速度越来越快(目前有 4mio 行，每天插入 300k)。我希望我能在这里得到一些想法和建议，以改进我的设置，并在不久的将来关闭我的网站之前从我的盒子中挤出最后一点。

设置:

    Intel i7 720 
    8GB RAM
    2x750GB SATA RAID 0
    CentOS
    MySQL 5.5.10
    Node.js + node-lib_mysql-client

表定义:

CREATE TABLE IF NOT EXISTS `canvas` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`x1` int(11) NOT NULL,
`y1` int(11) NOT NULL,
`x2` int(11) NOT NULL,
`y2` int(11) NOT NULL,
`c` int(4) unsigned NOT NULL,
`s` int(3) unsigned NOT NULL,
`m` bigint(20) unsigned NOT NULL,
`r` varchar(32) NOT NULL,
PRIMARY KEY (`id`,`x1`,`y1`) KEY_BLOCK_SIZE=1024,
KEY `x1` (`x1`,`y1`) KEY_BLOCK_SIZE=1024,
KEY `x2` (`x2`,`y2`) KEY_BLOCK_SIZE=1024
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=4
/*!50100 PARTITION BY HASH ( (
(
x1 MOD 10000
)
) + y1 MOD 10000)
PARTITIONS 10 */ AUTO_INCREMENT=13168904 ;

查询:

SELECT x1,y1,x2,y2,s,c,r,m FROM canvas
WHERE 1 AND ((
 x1 >= 0
 AND x1 <= 400
 AND y1 >= 0
 AND y1 <= 400
 ) OR ( 
 x2 >= 0
 AND x2 <= 400
 AND y2 >= 0
 AND y2 <= 400
 ) )
  ORDER BY id desc

这是我正在执行的唯一查询，除了 x1、y1、x2 和 y2 的值​​在每次查询时都会发生变化。它是一个二维 Canvas ，每一行代表 Canvas 上的一条线。我想知道为 1 个字段选择的最大范围永远不会大于 1200(像素)也很重要。几周前我升级到 MySQL 5.5.10 并开始使用分区。 'x1 % 10000' hashw 作为我进入分区主题的第一个也是不知道的方法。它已经使我的 SELECT 速度有了相当大的提升，但我确信仍有优化空间。

哦，在你问之前...我知道我正在使用 MyISAM 表这一事实。我的一个 friend 建议使用 innoDB，但已经尝试过了，结果是表大了 2 倍，而且 SELECT 性能大幅下降。我不需要任何花哨的交易和东西....我需要的是尽可能最好的 SELECT 性能和 INSERT 的体面性能。

你会改变什么？我能以某种方式调整我的索引吗？我的分区设置是否有意义？我是否应该增加分区文件的数量？

欢迎所有建议...我还与 friend 讨论了将本地复制到内存表中的问题，但我确信表大小超出我的 RAM 只是时间问题，交换盒是一个相当不错的选择丑陋的东西。

当您考虑我的问题时，请记住它正在快速且不可预测地增长。万一它由于某种原因在某个地方传播开来，我预计每天会看到超过 1mio 的插入。

感谢您的阅读和思考。 :)

编辑:请求的解释结果

select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
SIMPLE  canvas  index_merge     x1,x2   x1,x2   8,8     NULL    133532  Using sort_union(x1,x2); Using where; Using fileso...

EDIT2:请求的 my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

innodb_buffer_pool_size = 1G
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
innodb_file_format = Barracuda

query_cache_type = 1
query_cache_size = 100M

# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

innoDB 值用于我的 innoDB 尝试……我猜它们不再是必需的了。该服务器还运行其他 4 个网站，但它们相当小，不值得一提。不管怎样，我很快就会把这个项目转移到一个专用的盒子里。您的想法可能很激进 - 我不介意实验。

EDIT3 - 带索引的基准

好吧，伙计们……我已经用不同的索引做了一些基准测试，到目前为止，结果还不错。对于此基准测试，我选择了 2000x2000 像素的框中的所有行。

SELECT SQL_NO_CACHE x1,y1,x2,y2,s,c FROM canvas_test WHERE 1 AND (( x1 BETWEEN -6728 AND -4328 AND y1 BETWEEN -6040 AND -4440 ) OR (  x2 BETWEEN -6728 AND -4328 AND y2 BETWEEN -6040 AND -4440 ) )  ORDER BY id asc

使用我在上面发布的表/索引定义平均查询时间是:1740ms

然后我删除了所有索引，除了主键 -> 1900ms

为 x1 添加了一个索引 -> 1800ms

为 y1 添加了一个索引 -> 1700ms

为 x2 添加了一个索引 -> 1500ms

为 y2 添加了一个索引 -> 900ms!

到目前为止，这非常令人惊讶...出于某种原因，我认为为 x1/y1 和 x2/y2 制作组合索引在某种程度上是有意义的，但实际上我错了。

EXPLAIN 现在返回这个:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  canvas_test     index_merge     x1,y1,x2,y2     y1,y2   4,4     NULL    263998  Using sort_union(y1,y2); Using where; Using fileso..

现在我想知道为什么它使用 y1/y2 作为键而不是全部四个？

但是，我仍在寻找更多的想法和建议，尤其是关于分区和适当的散列。

Answer 1

首先，我将 SELECT 修改为

SELECT x1,y1,x2,y2,s,c,r,m FROM canvas
WHERE 
  x1 BETWEEN 0 AND 400 AND y1 BETWEEN 0 AND 400 OR
  x2 BETWEEN 0 AND 400 AND y2 BETWEEN 0 AND 400
ORDER BY id desc

还要确保在该表达式上有一个索引:

CREATE INDEX canvas400 ON canvas(
  x1 BETWEEN 0 AND 400 AND y1 BETWEEN 0 AND 400 OR
  x2 BETWEEN 0 AND 400 AND y2 BETWEEN 0 AND 400
)