有没有一种方法可以限制Mysql表的索引，以便整体性能不受影响？

Question

有没有一种方法可以限制Mysql表的索引，以便整体性能不受影响？

mysqlindexingthrottling

17

我需要将一个大型数据集加载到生产数据库中。

共有15个文件需要上传并插入到一个表中。每个文件大小约为500 MB。

我有两个需要建立索引的ID列。如果我在上传时已经建好索引，上传需要大约3小时。如果我先去掉索引、用load data local infile命令加载数据，最后再重新建索引，则整个操作只需要30分钟。

问题是，当新导入的数据进行索引时，数据库的响应能力会受到很大影响。有没有办法让索引以“低优先级”方式运行，这样其他查询仍可以在95-100％的速度下运行，而索引操作则在后台缓慢处理？

我使用Amazon RDS，因此无法在不同服务器上加载数据，然后复制表文件。

我会添加赏金，因为我仍然想知道是否有一种方法可以在特定的服务器上进行索引并获得良好性能。

- Zak

请运行SHOW CREATE TABLE tblname\G命令以查看正在加载的表。我们需要查看表使用的存储引擎。请向我们展示/etc/my.cnf文件。 - RolandoMySQLDBA

我目前正在使用MyISAM，但我也尝试过InnoDB。我可以使用任何一种引擎。再次强调，重点是尝试限制索引而不是提高性能。 - Zak

不清楚您执行此操作的频率，但您似乎暗示这是一次性的。出于好奇，30分钟左右的停机问题是什么？不能在低使用期间（夜间/午餐时间）执行吗？ - T9b

数据加载需要每周至少一次，但有时甚至每天都需要。我不在乎它们需要6个小时，我只想限制它们的速度，以免影响其他查询。 - Zak

6个回答

1

这不是你要寻找的确切解决方案，但你可以在单个服务器上启动第二个MySQL实例作为从属，并根据需要将SELECT查询重定向到它。 MySQL Proxy可以帮助你完成此操作，而无需重新编写客户端应用程序。

你还可以从FriendFeed使用MySQL的方法中获取一些想法。他们将实际索引存储在其他表中，并将其用于搜索。如果你将数据的副本存储在其他表甚至其他服务器上并在那里运行索引，你将能够以全速访问主数据并稍后使用其他服务器获得更快的查询。

就像为搜索类型的查询在从属上添加索引并仅在主服务器上运行主键查找一样。

- sanmai

如果我不需要处理交易，这就没问题了。我需要主服务器以便我可以向其写入。在索引期间，写入速度变得太慢了（在其他表上）。 - Zak

我添加了一条关于FriendFeed实际做了什么的注释。 - sanmai

1

一个好的解决方案是使用执行滚动更新的脚本。您可以将索引以非复制方式应用于每个从服务器。下面是一个简单的示例：

for host in $hosts
do
    mysql -h $host -e "STOP SLAVE;\
      SET sql_log_bin=0;\
      FLUSH TABLE t;\
      ALTER TABLE t ADD INDEX a (b,c);\
      SET sql_log_bin=1;\
      START SLAVE;"
done

关闭复制功能后，磁盘活动量将会减少，从而提高索引操作的速度。如果你的从节点需要遵守数据库滞后性要求，你可能需要完全解除从节点池，并包含重新加入从节点池的逻辑，当它恢复到零秒滞后时。

- memnoch_proxy

我目前几乎完全按照这种方式操作，但仍然不够。主服务器上的数据索引导致需要主数据的0.5%查询速度过慢。我需要一种方法来限制索引速度，以便在对其他表进行响应时仍然可以进行索引。 - Zak

Zak，你有一个很好的购买更多设备的商业案例！另一个可能性是在从服务器上对表进行索引，然后将其复制到主服务器，最后像这样重命名它：SET sql_log_bin=0; flush table t; rename t to dugout_t, t_atbat to t; SET sql_log_bin=1; - memnoch_proxy

我有点陷入了进退两难的境地。我转移到RDS是为了避免系统管理员成本，但每台机器的费用更高。然而，由于没有直接访问文件系统，RDS不允许您在原地移动索引或表。如果我在RDS中增加我的数据库大小，那么我将失去所有的系统管理员成本节省，因为我使用的multiAZ DB会变得非常昂贵！ - Zak

1

这也可以通过政策和客户消息来解决。你的网站与客户有特定的SLA协议吗？考虑在客户登录页面上创建一个“网站维护通知”，警告他们服务降级或中断，然后在你预先警告的日期和时间进行必要的操作。 - memnoch_proxy

0

你尝试过提高导入的索引设置吗？这可以显著提高导入性能。sort_buffer_size适用于任何表类型，myisam_sort_buffer_size适用于MyISAM表。innodb_buffer_pool_size是Innodb的“关键缓存”。根据您的表类型提高导入时的缓存大小。您要做的是避免在索引创建期间进行文件排序。

您可能能够将导入/索引时间缩短至10-15分钟或更短。这不是限制，但它会显著缩短影响期。

或者，如果您使用的是MyISAM表，也许MERGE表是一个选项？创建一个新表，执行导入，然后将新表添加到MERGE表中。在导入期间，数据库不会受到任何影响。除了服务器执行任务之外。

- Brent Baisley

0

一个之前未尝试过的想法，也不是关于索引限制的，如果你创建一个备份表并使用你提到的更短时间跨度的方式更新它，然后再转换/重命名表，这样会怎么样呢？我鼓励写下我的想法，因为你需要知道一种方法。

- Asad Rasheed

0

在插入时，您可以禁用任何非唯一索引，并在完成后重新启用它们。请查看禁用键/启用键。但这仅适用于非唯一索引。

如果使用多值插入语句（insert into table(...) values(...),(...),(...)...），还可以加快插入速度。

顺便说一下，在MySQL中插入大量数据似乎是最快的方法是使用load data infile。

- Maxim Krizhanovsky

是的，我可以在禁用键的情况下在大约5分钟内加载所有数据。但是当我启用键时，索引就会发生！这就是导致我的数据库性能下降的原因。 - Zak

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Zak · Accepted Answer

我从未找到限制速率的方法，但我确实找到了一种缓解问题的方法。

这个解决方案是针对我的问题而设计的，但如果有人发现它有用，我会发布它。

我编写了一个名为 CautiousIndexer 的类。

首先，我存储了一个创建表语句，以便重新创建没有索引的表结构。我存储了一个读取从数据库的数组，并循环重命名了包含未索引数据的表为 prevent_indexing_($name)。
然后，我只在从服务器上运行了创建表语句。这有效地将数据移出了主服务器上可能发生的索引语句的路线。
接下来，我在主服务器上运行了索引查询。由于新创建的表是空的，因此读取从服务器不会有性能影响，而主服务器正在进行索引。
当主服务器完成索引后，我将其中1个从服务器停止使用，删除空表，将完整表移回原位，然后在停止使用的从服务器上对表进行索引。
当这个过程完成后，我将其放回生产状态，并在剩余的从服务器上重复从服务器索引过程。
当所有从服务器都被索引后，我将表放入生产状态。

在效率方面这仍然是可以接受的，但在主服务器上进行索引时写入性能会明显下降。我还在寻找一种带有限制速率的索引方法。