MySQL海量数据无损更新实战：2亿级表字段批量更新方案

-- 危险操作！会导致长时间锁表
UPDATE statistics_data SET timeout = 0;

#!/bin/bash
# 分批更新脚本（每10万条间隔1秒）
while true; do
  affected=$(mysql -uroot -p$PWD -e "
    UPDATE statistics_data 
    SET timeout = 0 
    WHERE timeout != 0 
    LIMIT 100000;
    SELECT ROW_COUNT();" | tail -1)
  
  [ $affected -eq 0 ] && break
  sleep 1
done

-- 查看剩余待更新量
SELECT COUNT(*) FROM statistics_data WHERE timeout != 0;

pt-online-schema-change \
  --alter "MODIFY timeout INT DEFAULT 0" \
  D=database,t=statistics_data \
  --execute

import pymysql
import time
import sys

def batch_update(config):
    conn = pymysql.connect(config)
    cursor = conn.cursor()
    
    # 获取总记录数
    cursor.execute("SELECT COUNT(*) FROM statistics_data WHERE timeout != 0")
    total = cursor.fetchone()[0]
    
    print(f"待更新记录总数: {total}")
    
    batch_size = 100000
    updated = 0
    start = time.time()
    
    try:
        while updated < total:
            sql = f"""
                UPDATE statistics_data 
                SET timeout = 0 
                WHERE timeout != 0 
                LIMIT {batch_size}
            """
            cursor.execute(sql)
            count = cursor.rowcount
            conn.commit()
            
            updated += count
            progress = updated / total * 100
            
            print(f"\r进度: {updated}/{total} ({progress:.2f}%)", end="")
            
            if count == batch_size:
                time.sleep(1)  # 主动暂停降低负载
                
    except Exception as e:
        conn.rollback()
        print(f"\n错误发生: {str(e)}")
    finally:
        cursor.close()
        conn.close()
        
    print(f"\n更新完成! 耗时: {time.time()-start:.2f}秒")

if __name__ == "__main__":
    db_config = {
        'host': '10.0.0.5',
        'port': 3307,  # 非标准端口示例
        'user': 'admin',
        'password': 'safe@123',
        'db': 'stats_db',
        'connect_timeout': 60
    }
    batch_update(db_config)

print(f"\r进度: {updated}/{total} ({progress:.2f}%)", end="")

if os.getloadavg()[0] > 5.0:
    batch_size = max(50000, batch_size // 2)

from DBUtils.PooledDB import PooledDB
pool = PooledDB(pymysql, db_config)

-- 查看当前锁状态
SELECT * FROM performance_schema.events_waits_current 
WHERE EVENT_NAME LIKE '%lock%';

# 每批次提交后立即释放锁
conn.commit()  
time.sleep(0.5)  # 故意留出锁释放窗口

if time.localtime().tm_hour in range(9,18):  # 白天工作时间
    print("禁止在业务高峰执行!")
    sys.exit(1)

watch -n 1 "mysqladmin ext | grep -E 'Threads_running|Queries'"