草庐IT

php - 删除带有索引的大型 MySQL 表的性能

coder 2023-10-11 原文

假设我们有一个带有 MySQL 5.6 数据库的网络论坛应用程序，许多用户可以 24/7 全天候访问。现在有一个这样的表，用于发送给用户的通知的元数据。

| notifications | CREATE TABLE `notifications` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) unsigned NOT NULL,
 `message_store_id` bigint(20) unsigned NOT NULL,
 `status` varchar(10) COLLATE ascii_bin NOT NULL,
 `sent_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`,`sent_date`)
) ENGINE=InnoDB AUTO_INCREMENT=736601 DEFAULT CHARSET=ascii COLLATE=ascii_bin |

此表有 100 万行。有了这个表，某个 message_store_id 由于某种原因突然变得无效，我打算用一个删除语句删除所有带有那个 message_store_id 的记录，比如

DELETE FROM notifications WHERE message_store_id = 12345;

由于这条消息被发送给如此多的用户，这条语句影响了表的 10%。同时，这个通知表一直被成千上万的用户访问，所以索引必须存在。显然，在删除记录时重新创建索引的成本非常高，所以我不敢这样做并通过最大化服务器资源来导致停机。但是，如果我删除索引，删除记录然后再次添加索引，我必须关闭数据库一段时间，不幸的是我们的服务是不可能的。

我希望 MySQL 5.6 不会愚蠢到这条语句就可以杀死数据库，但我想这很有可能。我的问题是，索引重建对于这种情况真的是致命的吗？如果是这样，对于此操作是否有任何好的策略不需要我停止数据库进行维护？

最佳答案

根据您的应用程序的详细信息，您可以使用很多技巧/策略。

  1. 如果您计划定期执行这些操作(例如，这不是一次性的事情)，并且您在 message_store_id 中几乎没有不同的值，则可以使用分区。按message_store_id的值进行分区，预先创建X个分区(其中X是id值数量的合理上限)，然后您可以通过截断立即删除该分区中的所有记录那个分区。几毫秒的事情。缺点:message_store_id 必须是主键的一部分。注意:您必须事先创建分区，因为我上次使用它们时，alter table add partition 重新创建了整个表，这对大型表来说是一场灾难。
  2. 即使alter table truncate partition 对您不起作用，您仍然可以从分区中获益。如果您在分区上发出 DELETE，通过提供相应的 where 条件，表的其余部分将不会受到此 DELETE 操作的影响/锁定。

  3. 在不锁定数据库太长时间的情况下删除记录的替代方法:

    while (true) {
  // assuming autocommit mode
  delete from table where {your condition} limit 10000;
  // at this moment locks are released and other transactions have a chance
  // to do some stuff.
  if (affected rows == 0) {
    break;
  }
  // This is a good place to insert sleep(5) to give other transactions
  // more time to do their stuff before the next chunk gets deleted.
}

关于php - 删除带有索引的大型 MySQL 表的性能，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/24743043/

大型MySQLcodemessage_store_idsectionphpsqldatabaseindexing

有关php - 删除带有索引的大型 MySQL 表的性能的更多相关文章

  1. ruby-on-rails - 如何从 format.xml 中删除 <hash></hash> - 2

    我有一个对象has_many应呈现为xml的子对象。这不是问题。我的问题是我创建了一个Hash包含此数据，就像解析器需要它一样。但是rails自动将整个文件包含在.........我需要摆脱type="array"和我该如何处理？我没有在文档中找到任何内容。 最佳答案 我遇到了同样的问题；这是我的XML:我在用这个:entries.to_xml将散列数据转换为XML，但这会将条目的数据包装到中所以我修改了:entries.to_xml(root:"Contacts")但这仍然将转换后的XML包装在“联系人”中，将我的XML代码修改为

  2. ruby - 我可以使用 Ruby 从 CSV 中删除列吗？ - 2

    查看Ruby的CSV库的文档，我非常确定这是可能且简单的。我只需要使用Ruby删除CSV文件的前三列，但我没有成功运行它。 最佳答案 csv_table=CSV.read(file_path_in,:headers=>true)csv_table.delete("header_name")csv_table.to_csv#=>ThenewCSVinstringformat检查CSV::Table文档:http://ruby-doc.org/stdlib-1.9.2/libdoc/csv/rdoc/CSV/Table.html

  3. ruby - 我可以使用 aws-sdk-ruby 在 AWS S3 上使用事务性文件删除/上传吗？ - 2

    我发现ActiveRecord::Base.transaction在复杂方法中非常有效。我想知道是否可以在如下事务中从AWSS3上传/删除文件:S3Object.transactiondo#writeintofiles#raiseanexceptionend引发异常后，每个操作都应在S3上回滚。S3Object这可能吗？？ 最佳答案 虽然S3API具有批量删除功能，但它不支持事务，因为每个删除操作都可以独立于其他操作成功/失败。该API不提供任何批量上传功能(通过PUT或POST)，因此每个上传操作都是通过一个独立的API调用完成的

  4. ruby - 如何安全地删除文件？ - 2

    在Ruby中是否有Gem或安全删除文件的方法？我想避免系统上可能不存在的外部程序。“安全删除”指的是覆盖文件内容。 最佳答案 如果您使用的是*nix，一个很好的方法是使用exec/open3/open4调用shred:`shred-fxuz#{filename}`http://www.gnu.org/s/coreutils/manual/html_node/shred-invocation.html检查这个类似的帖子:Writingafileshredderinpythonorruby?

  5. ruby-on-rails - 标准化文件名的字符串，删除重音和特殊字符 - 2

    我正在尝试找到一种方法来规范化字符串以将其作为文件名传递。到目前为止我有这个:my_string.mb_chars.normalize(:kd).gsub(/[^\x00-\x7F]/n,'').downcase.gsub(/[^a-z]/,'_')但第一个问题:-字符。我猜这个方法还有更多问题。我不控制名称，名称字符串可以有重音符、空格和特殊字符。我想删除所有这些，用相应的字母('é'=>'e')替换重音符号，并将其余的替换为'_'字符。名字是这样的:“Prélèvements-常规”“健康证”...我希望它们像一个没有空格/特殊字符的文件名:“prelevements_routin

  6. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  7. ruby-on-rails - 带有 Zeus 的 RSpec 3.1，我应该在 spec_helper 中要求 'rspec/rails' 吗？ - 2

    使用rspec-rails3.0+，测试设置分为spec_helper和rails_helper我注意到生成的spec_helper不需要'rspec/rails'。这会导致zeus崩溃:spec_helper.rb:5:in`':undefinedmethod`configure'forRSpec:Module(NoMethodError)对thisissue最常见的回应是需要'rspec/rails'。但这是否会破坏仅使用spec_helper拆分rails规范和PORO规范的全部目的？或者这无关紧要，因为Zeus无论如何都会预加载Rails？我应该在我的spec_helper中做

  8. ruby-on-rails - 为什么在 Rails 5.1.1 中删除了 session 存储初始化程序 - 2

    我去了这个website查看Rails5.0.0和Rails5.1.1之间的区别为什么5.1.1不再包含:config/initializers/session_store.rb？谢谢 最佳答案 这是删除它的提交:Setupdefaultsessionstoreinternally,nolongerthroughanapplicationinitializer总而言之，新应用没有该初始化器，session存储默认设置为cookie存储。即与在该初始值设定项的生成版本中指定的值相同。 关于

  9. ruby-on-rails - 协会的 Rails 索引 - 2

    我发现自己需要这个。假设cart是一个包含用户列表的模型。defindex_of_itemcart.users.each_with_indexdo|u,i|ifu==current_userreturniendend获取此类关联索引的更简单方法是什么？ 最佳答案 indexArray上的方法与您的index_of_item方法相同，例如cart.users.index(current_user)返回数组中第一个对象的索引==给obj。如果未找到匹配项，则返回nil。 关于ruby-on-

  10. Ruby:如何使用带有散列的 'send' 方法调用方法？ - 2

    假设我有一个类A，里面有一些方法。假设stringmethodName是这些方法之一，我已经知道我想给它什么参数。它们在散列中{'param1'=>value1,'param2'=>value2}所以我有:params={'param1'=>value1,'param2'=>value2}a=A.new()a.send(methodName,value1,value2)#callmethodnamewithbothparams我希望能够通过传递我的哈希以某种方式调用该方法。这可能吗？ 最佳答案 确保methodName是一个符号，而

随机推荐