同事一句sql成功将线上服务搞瘫

搞笑起因

事情的起因是这样的 张三：怎么线上服务这么慢 李四：你的网络有问题，换个网试试 张三：偷偷打开网页，一样的龟速

王五：不对呀，别的都很快，为什么单单我们的系统慢啊，服务器不行了？

赵六：获取过一会就好了，等下吧

。。。。。。一等不要紧，系统越来越慢，甚至有人进不去，有人直接超时

王麻子：我本地不会哦，而且是飞一般的感觉 刘能子：我本地也会，巨卡(前端大佬，连接的是线上后端)

我：冷静一下，首先我们服务器配置很高，不会存在被打满的情况，而且线上的后端，与线下的后端差的就是一个正式库，一个测试库，还没等我想完(近处一个声音飘过~~~~~~，测试库很快，正式库很慢)。

找原因

根据上面的问题，我们不难发现是数据库出现了问题

第一步

执行SHOW PROCESSLIST;,它是 MySQL 中的一个命令，用于显示当前正在运行的线程信息。它可以帮助你了解哪些查询正在执行，以及查询的状态。

每列的含义如下：

• Id：线程的唯一标识符。
• User：执行该线程的 MySQL 用户。
• Host：执行该线程的客户端主机。
• db：当前连接的数据库名。
• Command：正在执行的命令类型（如 Sleep、Query 等）。
• Time：命令已执行的时间（以秒为单位）。
• State：线程的状态（如 Sending data、Sorting result 等）。
• Info：正在执行的 SQL 语句。

从执行结果看，没有发生死锁的，当然如果发生了死锁，情况应该比这个还惨吧，但是显而易见的是有一条的执行时间很长，命令大致就是update 语句。

第二步

随后问了一下是否有人执行了这个语句，果然，同事说她执行了这个语句，==而且update的数量是几百w，什么概念。==，随即另一个同事说这个可以kill掉吗，就直接kill了。但是它的状态显示为killed，然而它还存在。很显然这个事务发生了回滚。

第三步

知道关于事务，于是我又执行了另一个语句SELECT * FROM information_schema.innodb_trx;

该查询将返回如下列：

• trx_id：事务的唯一标识符。
• trx_state：事务的状态，如 RUNNING、LOCK WAIT 等。
• trx_started：事务开始的时间。
• trx_requested_lock_id：事务请求的锁的标识符。
• trx_wait_started：事务开始等待的时间。
• trx_weight：事务的权重，即事务修改的行数和锁的数量。
• trx_mysql_thread_id：与该事务关联的 MySQL 线程的标识符。
• trx_query：事务正在执行的 SQL 查询。
• trx_operation_state：事务的操作状态。
• trx_tables_in_use：事务正在使用的表数量。
• trx_tables_locked：事务锁定的表数量。
• trx_lock_structs：事务的锁结构数量。
• trx_lock_memory_bytes：事务的锁占用的内存量。
• trx_rows_locked：事务锁定的行数。
• trx_rows_modified：事务修改的行数。
• trx_concurrency_tickets：事务的并发票据。
• trx_isolation_level：事务的隔离级别。
• trx_unique_checks：事务的唯一性检查状态。
• trx_foreign_key_checks：事务的外键检查状态。
• trx_last_foreign_key_error：事务的最后一个外键错误。
• trx_adaptive_hash_latched：事务的自适应哈希表的锁定状态。
• trx_adaptive_hash_timeout：事务的自适应哈希表的超时时间。
• trx_is_read_only：事务是否为只读事务。
• trx_autocommit_non_locking：事务的自动提交非锁定状态。

上图可以看到事务的状态为回滚，事务的权重为100多万，80w，还有一个386w的

解决方法

第一种

最简单最暴力的方法就是上面的，但是会存在很大的问题

第二种

第二种方法就是躺平，等回滚完成。后续杜绝这种

为什么update会导致数据库慢

首先我们要知道，该表中数据大约有500w，而sql更新的是100w条，就会出现以下问题。

锁争用：

• 更新操作会导致数据库表上的行锁或表锁，特别是在并发访问较高的情况下，会导致锁争用问题，使得其他操作（如查询、插入、删除）无法及时执行。

索引更新：

• 如果表上有多个索引，更新操作会导致索引的更新和重建，这会消耗大量的CPU和IO资源，导致性能下降。

事务开销：

• 如果更新操作是在一个事务中执行的，事务的开销（如日志记录、回滚段的使用等）也会影响数据库的性能。

IO瓶颈：

• 大量的更新操作会导致大量的磁盘IO操作，如果磁盘性能不够，可能会成为瓶颈。

表和索引的碎片化：

• 大量的更新操作可能会导致表和索引的碎片化，影响查询和更新的效率。

内存和缓存压力：

• 更新大量数据会消耗大量的内存和缓存资源，如果内存和缓存不足，可能会导致性能下降。

锁升级：

• 数据库可能会从行锁升级为页锁或表锁，从而影响其他事务的执行

确实需要这样操作呢？

如果你确实需要对300万条数据进行更新，可以采取以下方法来确保操作的高效性和安全性：

1. 分批更新

将大规模更新操作分成多个小批次来执行，每次只更新一部分数据。这样可以减少锁定时间，降低对系统的负载。

-- 伪代码示例，假设每次更新1万条记录
SET @batch_size = 10000;
SET @start = 0;

WHILE @start < 3000000 DO
  UPDATE your_table
  SET column1 = new_value
  WHERE condition
  LIMIT @start, @batch_size;

  SET @start = @start + @batch_size;
END WHILE;

2. 使用事务控制

将每个批次的更新操作放在一个事务中，确保数据的一致性和完整性。如果出现错误，可以回滚事务，避免部分更新导致数据不一致。

START TRANSACTION;

-- 更新操作
UPDATE your_table
SET column1 = new_value
WHERE condition
LIMIT @batch_size;

COMMIT;

3. 维护索引

在更新操作前，检查并确保相关索引的存在，以加快查询和更新速度。如果更新涉及索引列，可能需要在更新后重建索引。

-- 检查并重建索引
ALTER TABLE your_table DROP INDEX index_name;
ALTER TABLE your_table ADD INDEX index_name (column1);

4. 避免高峰期操作

选择系统负载较低的时间段进行大规模更新，减少对其他用户和操作的影响。

5. 监控和调优

实时监控数据库的性能和资源使用情况，及时调整参数和资源分配。例如，调整Innodb_buffer_pool_size、Innodb_log_file_size等参数，以适应大规模更新操作。

6. 利用临时表

如果需要对大量数据进行复杂计算或转换，可以考虑使用临时表来存储中间结果，减少对原表的直接操作。

-- 创建临时表
CREATE TEMPORARY TABLE temp_table AS
SELECT * FROM your_table WHERE condition;

-- 在临时表中进行更新
UPDATE temp_table
SET column1 = new_value
WHERE condition;

-- 将更新后的数据回写到原表
UPDATE your_table t
JOIN temp_table tt ON t.id = tt.id
SET t.column1 = tt.column1;

7. 日志和备份

在进行大规模更新前，确保有最新的数据库备份，以防止意外数据丢失。同时，可以开启详细日志记录，便于后续问题排查。

示例操作步骤

以下是一个具体的示例，演示如何分批次更新300万条记录：

-- 设置批次大小
SET @batch_size = 10000;
SET @start = 0;
SET @end = 3000000;

-- 开始更新操作
WHILE @start < @end DO
  START TRANSACTION;

  -- 批次更新
  UPDATE your_table
  SET column1 = new_value
  WHERE condition
  LIMIT @batch_size;

  COMMIT;

  -- 更新起始点
  SET @start = @start + @batch_size;

  -- 可以加入短暂的延迟，避免系统负载过高
  DO SLEEP(1);
END WHILE;

通过以上方法，可以有效地降低大规模数据更新对系统性能的影响，确保操作的安全性和高效性。

加餐

默认情况下一般不会调整下面我说的这两个参数，但是我认为他们确实很重要

调大 innodb_buffer_pool_size 和 innodb_io_capacity 是优化 MySQL InnoDB 存储引擎性能的重要步骤，尤其是在进行大规模数据更新时。下面是这两个参数的作用和调整建议：

1. innodb_buffer_pool_size

作用

innodb_buffer_pool_size 参数指定了 InnoDB 缓冲池的大小。缓冲池用于缓存数据和索引页，这样可以减少对磁盘的访问，提高读取性能。

• 增加缓冲池大小：能够容纳更多的表数据和索引在内存中，减少磁盘IO，从而提高查询和更新的速度。
• 影响：提高缓冲池大小可以减少数据库访问磁盘的次数，但过大可能会导致系统内存不足，影响其他进程的运行。

调整建议

• 根据系统内存：一般情况下，可以将 innodb_buffer_pool_size 设置为系统总内存的60%-80%，具体取决于其他应用程序对内存的需求。
• 逐步增加：逐步增加参数值，监控数据库性能和系统资源，找到最佳配置。
• 监控：使用 SHOW ENGINE INNODB STATUS; 命令监控缓冲池的使用情况。检查 Buffer pool hit rate 和 Buffer pool reads 等指标，以确保缓冲池配置合理。

-- 设置缓冲池大小
SET GLOBAL innodb_buffer_pool_size = 8G;  -- 设置为8GB

2. innodb_io_capacity

作用

innodb_io_capacity 参数控制了InnoDB每秒允许的IO操作的数量，主要影响InnoDB的异步IO性能，决定了InnoDB在后台如何处理写操作和检查点操作。

• 增加IO容量：可以提高InnoDB的IO吞吐量，减少I/O瓶颈，尤其是在进行大量写操作时。
• 影响：提高 innodb_io_capacity 可以加快数据的写入速度和检查点的处理，但设置过高可能会导致磁盘IO过载，影响系统稳定性。

调整建议

• 根据磁盘性能：设置 innodb_io_capacity 时需要考虑磁盘的实际性能。一般情况下，可以设置为磁盘每秒IOPS的20%-50%。
• 监控：使用 SHOW ENGINE INNODB STATUS; 和 SHOW VARIABLES LIKE 'innodb_io_capacity'; 监控IO操作情况，确保系统能够承受设置的IO负载。

-- 设置IO容量
SET GLOBAL innodb_io_capacity = 2000;  -- 设置为2000

总结

1. **设置 innodb_buffer_pool_size**：确保足够的内存用于缓存数据和索引，减少磁盘访问。
2. **设置 innodb_io_capacity**：提高IO吞吐量，适应大规模写操作和检查点处理。
3. 监控和调整：在调整这两个参数后，监控数据库性能和系统资源使用情况，逐步调整，以达到最佳性能。

确保在修改这些参数之前，备份好数据库和配置文件，避免因配置不当导致系统不稳定。