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 的值​​根据查询而变化这一事实之外。它是一个 2D 画布，每一行代表画布上的一条线。我想知道为 1 场选择的最大范围永远不会大于 1200（像素）也很重要。 几周前，我升级到 MySQL 5.5.10 并开始使用分区。 “x1 % 10000” hashw 是我进入分区主题的第一个无意识方法。它已经给我的 SELECT 速度带来了相当大的提升，但我确信仍然有优化的空间。

哦，在你问之前...我知道我正在使用 MyISAM 表。我的一个朋友建议使用innoDB，但已经尝试过，结果是表变大了2倍，并且SELECT性能大幅下降。我不需要任何花哨的事务和东西......我所需要的只是尽可能最好的 SELECT 性能和良好的 INSERT 性能。

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

欢迎所有建议...我还与朋友讨论了本地复制到内存表的问题，但我确信表大小超过我的 RAM 只是时间问题，而交换盒对我来说是一件相当难看的事情看。

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

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

编辑：请求的 EXPLAIN 结果

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

编辑2：请求的 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 添加一个索引 -> 900毫秒！

到目前为止，这相当令人惊讶......出于某种原因，我认为为 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
)

Answer 2

1. 您的服务器当前使用了多少内存？
2. 这是服务器上唯一的数据库/表吗？
3. 您只使用 MyISAM 吗？

只要您不更新行，MyISAM 就可以使用。当您更新 MyISAM 表上的一行时，MySQL 会锁定整个表，阻止任何 SELECT 和 INSERTS 的执行，直到 UPDATE 完成。 UPDATE 优先于 SELECT，因此如果您正在运行大量 UPDATE，则 SELECTS 将等到它们全部完成后才返回任何行。

如果您同意，请转到您的服务器配置。你的 my.cnf 文件是什么样的？您需要优化此文件以最大化可用于索引的内存量。如果这些 SELECT 速度变慢，那是因为表索引不适合内存。如果 MySQL 无法将表索引放入内存，那么它必须转到磁盘并进行表扫描来获取数据。这会扼杀性能。

编辑 5/18/2011 9:30PM EST

查看您的 my.cnf 后，我注意到您的 MyISAM 优化为零。您的起始位置将是 key_buffer_size 变量。根据经验，该变量设置为系统总可用内存的 25% 到 50% 之间。您的系统有 8GB 可用内存，因此我认为 3GB 左右是最低起点。但是，如果您知道自己可以控制系统上的其他变量，则可以估计需要多少并根据需要对其进行优化。

您应该做的是 cd 到 mysql 数据目录（通常为 /var/lib/mysql），这是所有数据文件所在的位置。知道您有多少索引数据的快速方法是执行

 sudo du -hc `find . -type f -name "*.MYI"

此命令将查看所有 MyISAM 索引文件的大​​小并告诉您它们的总大小。如果您有足够内存，您希望 my.cnf 中的 key_buffer_size 比所有 MYI 文件的总大小大。这将确保您的 MyISAM 索引位于内存中，因此 MySQL 不必访问磁盘来获取索引数据。

快速说明一下，不要随意增加 key_buffer_size。这只是 MySQL 需要内存的一个区域，您还需要平衡其他移动部分的内存使用。 MySQL连接占用内存，不同的表引擎为其索引使用不同的内存池，并且MySQL将其他位内存用于不同的事情。如果由于将 key_buffer_size 设置得太大而导致内存不足，您的服务器可能会开始分页（使用虚拟内存，这会进一步降低性能）或更糟糕的情况是崩溃。如果您不确定，请从较小的值开始，检查内存使用情况，然后增加它，直到您对性能感到满意并且服务器不会崩溃。

Answer 3

请记住，MySQL 在每次查询时只会对每个表使用一个索引。您的 SELECT 查询将无法在同一查询中使用两个索引 - 它将使用其中之一。您可能会发现将两个 SELECT 查询联合起来更有效，以便每个查询都可以使用适当的索引，例如：

SELECT x1,y1,x2,y2,s,c,r,m FROM canvas
WHERE 
 x1 >= 0
 AND x1 <= 400
 AND y1 >= 0
 AND y1 <= 400
UNION
SELECT x1,y1,x2,y2,s,c,r,m FROM canvas
WHERE
 x2 >= 0
 AND x2 <= 400
 AND y2 >= 0
 AND y2 <= 400
;

或者您可以像建议的其他答复之一一样使用 BETWEEN ，例如：

SELECT x1,y1,x2,y2,s,c,r,m FROM canvas
WHERE x1 BETWEEN 0 AND 400 AND y1 BETWEEN 0 AND 400
UNION
SELECT x1,y1,x2,y2,s,c,r,m FROM canvas
WHERE x2 BETWEEN 0 AND 400 AND y2 BETWEEN 0 AND 400
;

自从我使用 UNION 以来已经有一段时间了所以我不确定你应该把 ORDER BY 子句放在哪里，但你可以尝试一下。

正如提到的其他回复之一，使用 EXPLAIN 来查看 MySQL 必须考虑多少行才能满足查询。

RTREE 索引也可能值得一看，尽管我自己没有使用过这些索引。

Answer 4

你能达到什么样的速度？由于您不需要任何关系数据，因此您应该考虑将数据移动到 Redis，它应该可以轻松执行 +100k 插入或在您的机器上每秒读取次数。