-- 危險(xiǎn)操作！會(huì)導(dǎo)致長時(shí)間鎖表
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()
    
    # 獲取總記錄數(shù)
    cursor.execute("SELECT COUNT(*) FROM statistics_data WHERE timeout != 0")
    total = cursor.fetchone()[0]
    
    print(f"待更新記錄總數(shù): {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進(jìn)度: {updated}/{total} ({progress:.2f}%)", end="")
            
            if count == batch_size:
                time.sleep(1)  # 主動(dòng)暫停降低負(fù)載
                
    except Exception as e:
        conn.rollback()
        print(f"\n錯(cuò)誤發(fā)生: {str(e)}")
    finally:
        cursor.close()
        conn.close()
        
    print(f"\n更新完成! 耗時(shí): {time.time()-start:.2f}秒")

if __name__ == "__main__":
    db_config = {
        'host': '10.0.0.5',
        'port': 3307,  # 非標(biāo)準(zhǔn)端口示例
        'user': 'admin',
        'password': 'safe@123',
        'db': 'stats_db',
        'connect_timeout': 60
    }
    batch_update(db_config)

print(f"\r進(jìn)度: {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)

-- 查看當(dāng)前鎖狀態(tài)
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):  # 白天工作時(shí)間
    print("禁止在業(yè)務(wù)高峰執(zhí)行!")
    sys.exit(1)

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