有没有办法限制 Mysql 表的索引，使整体性能不受影响？
我需要将大型数据集加载到生产数据库中。
需要分别上传 15 个文件并插入到表中。 每个大约500 Mb。
我有两个需要索引的 ID 列。 如果我加载带有适当索引的文件，则上传大约需要 3 小时。 如果我删除索引，加载数据本地infile，然后重新添加索引，整个操作大约需要30分钟。
问题是，在对新导入的数据建立索引时，数据库响应能力会受到很大影响。有没有办法让索引以“低优先级”运行，以便其他查询仍然获得 95-100% 的速度，并且索引在后台运行？
我使用的是 Amazon RDS，因此我无法选择仅加载到不同的服务器上然后复制表文件。
对此添加赏金，因为我仍然想看看是否有一种方法可以在对特定框进行索引时获得良好的性能。
I need to load a large data set onto a production database.
15 files need to each be uploaded and inserted into a table.
Each is about 500 Mb.
I have two ID columns that need to be indexed.
If I load the files with indexes in place, the upload takes around 3 hours.
If I drop indexes, load data local infile, then re-add the indexes, the whole operation takes about 30 minutes.
The problem is, database responsiveness takes a big hit while indexing the freshly imported data. Is there a way to make the indexing run at a "low priority" so that other queries still get 95-100% speed and the indexing kind of chugs along in the background?
I'm using Amazon RDS, so I don't have the option of just loading on a different server then copying over the table files.
Adding a bounty to this as I still want to see if there is a way to get good performance while indexing on a specific box.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
好吧，我从来没有找到一种方法来节流，但我确实找到了一种方法来缓解我的问题。
该解决方案对于我的问题来说是独一无二的，但我会发布它以防其他人发现它有用。
我编写了一个名为
CautiousIndexer的类。prevent_indexing_($name)。就效率而言，这仍然很好，但在主服务器上建立索引期间，写入性能下降得令人无法接受。仍在寻找一种通过节流进行索引的方法。
Well, I never found a way to throttle, but I did figure out a way to alleviate my problem.
The solution was unique to my problem, but I'll post it in case someone else finds it useful.
I wrote a class named
CautiousIndexer.prevent_indexing_($name).This was still fine in terms of efficiency, but during the indexing on the master server write performance was unacceptably slowed. Still looking for a way to index with throttling.
一个好的解决方案是使用执行滚动更新的脚本。您可以以非复制方式将索引应用于每个从站。粗略说明：
通过关闭复制，应该减少磁盘活动量并提高索引操作的速度。如果您对从属设备有数据库延迟要求，您可能希望完全取消从属设备池化，并包含在从属设备恢复零秒延迟时重新池化从属设备的逻辑。
A good solution to this is a script that performs a rolling update. You would apply the index to each slave in a non-replicating manner. A rough illustration:
By turning off replication, the amount of disk activity should be reduced and increase the speed of the indexing operation. If you have database lag requirements for your slaves, you might want to entirely de-pool the slave and include logic to re-pool the slave when it resumes zero seconds lag.
这不是您正在寻找的确切解决方案，但您可以 bing up 第二个 mysqld 实例作为此单个框上的从属，并根据需要将
SELECT查询重定向到它。有 MySQL Proxy 可以帮助您完成此任务无需重写客户端应用程序。您还可以从 MySQL 的 FriendFeed 使用中收集一些想法。它们将实际索引存储在其他表中并使用它们进行搜索。如果您将数据的副本存储在其他表中，甚至在其他服务器上，并在那里运行索引，您将能够尽快全速访问主数据，并在以后使用其他服务器获得更快的查询。
就像您在从属服务器上添加索引以进行搜索类型查询并在主服务器上仅运行主键查找一样。
This is not an exact solution you're looking for, but you can bing up a second mysqld instance as a slave on this single box and redirect
SELECTqueries to it as needed. There is MySQL Proxy which can help you to accomplish this without rewriting client apps.You can also gather some ideas from FriendFeed usage of MySQL. They store actual indexes in other tables and use them for search. If you store a copy of you data in other table even on other server and run indexes there, you'll be able to access master data ASAP at full speed and get speedier queries later using other server.
It's like if you add indexes on a slave for search-type queries and run only primary key lookups on the master.
以前没有尝试过的想法也不是关于索引限制，如果您创建一个备份表并用您提到的方式更新它，那么时间跨度更短，并且比转换/重命名表要好。我鼓励写下我的想法，因为你需要知道一种方法。
An idea not tried before also not about index throttling , what if you make a backup table and update it with the way you mentioned has shorter time span and than convert/rename the tables. I encourage to write my thoughts b/c you need to know a way.
您可以在插入时禁用任何非唯一索引，并在完成后重新启用它们。看一下禁用键/启用键。但它仅适用于非唯一索引。
如果使用多值插入语句，也可以加快插入速度（插入表（...）值（...），（...），（...）...
顺便说一下，加载data infile 似乎是在 mysql 中插入大量数据的最快方法。
You can disable any non-unique indexes while inserting, and re-enable them after you finish. Take a look at disable keys / enable keys. But it works only for non-unique indexes.
You can speed up inserts as well if you use multi-values insert statements (insert into table(...) values(...),(...),(...)...
By the way, load data infile seems to be the fastest way to insert a lot of data in mysql.
您是否尝试过提高导入的索引设置？这可以显着提高进口绩效。 sort_buffer_size适用于任何表类型，myisam_sort_buffer_size适用于MyISAM表。 innodb_buffer_pool_size 是 Innodb 的“密钥缓存”。根据您的表类型，将其添加到导入中。您想要做的是避免在索引创建期间进行文件排序。
您也许可以将导入/索引时间缩短至 10-15 分钟或更短。它不是节流，但会显着缩短影响期。
或者，如果您使用 MyISAM 表，也许 MERGE 表是一个选项？创建一个新表，执行导入，然后将新表添加到 MERGE 表中。导入过程中不会对数据库产生任何影响。除了执行任务的服务器之外。
Have you tried bumping up your index settings for the import? That can increase import performance significantly. sort_buffer_size is for any table type, myisam_sort_buffer_size is for MyISAM tables. innodb_buffer_pool_size is sort of your "key cache" for Innodb. Bump those up for the import depending on your table type. What you are trying to do is avoid file sorting during index creation.
You may be able to get your import/index time down to 10-15 minutes or less. It's not throttling, but it will significantly shorten the impact period.
Or, if you are using MyISAM tables, maybe a MERGE table is an option? Create a new table, perform the import, than add the new table to the MERGE table. There will be no impact on the database during import. Aside from the server performing a task.