欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL海量數(shù)據(jù)(2億級(jí)表字段)無損更新方案

 更新時(shí)間:2025年04月02日 08:40:56   作者:碼農(nóng)阿豪@新空間  
在大型互聯(lián)網(wǎng)應(yīng)用中,數(shù)據(jù)表動(dòng)輒達(dá)到億級(jí)規(guī)模,當(dāng)需要對(duì)生產(chǎn)環(huán)境中的海量表進(jìn)行字段更新時(shí),如何在不影響業(yè)務(wù)正常讀寫的情況下完成任務(wù),是每個(gè)DBA和開發(fā)者都會(huì)面臨的挑戰(zhàn),本文將以一個(gè)真實(shí)案例詳細(xì)講解四種漸進(jìn)式更新方案及其實(shí)現(xiàn)原理,需要的朋友可以參考下

一、問題背景與挑戰(zhàn)

1.1 場(chǎng)景描述

  • 表名:statistics_data
  • 數(shù)據(jù)量:2億條記錄
  • 需求:將timeout字段全部更新為0
  • 約束條件:業(yè)務(wù)持續(xù)運(yùn)行,不能有顯著影響

1.2 核心難點(diǎn)

  1. 鎖爭用風(fēng)險(xiǎn):全表更新可能導(dǎo)致長時(shí)間鎖表
  2. 主從延遲:大批量操作產(chǎn)生大量binlog
  3. 性能波動(dòng):CPU/IO壓力影響正常查詢響應(yīng)
  4. 進(jìn)度控制:需要可中斷、可監(jiān)控的方案

二、四大解決方案對(duì)比

2.1 方案一:直接全表更新(不推薦)

-- 危險(xiǎn)操作!會(huì)導(dǎo)致長時(shí)間鎖表
UPDATE statistics_data SET timeout = 0;

缺陷:

  • 產(chǎn)生單個(gè)大事務(wù),鎖定全表直到完成
  • 可能觸發(fā)undo空間爆滿
  • 回滾成本極高

2.2 方案二:分批更新(推薦)

Shell腳本實(shí)現(xiàn)

#!/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

優(yōu)勢(shì):

  • 每次只鎖定少量行
  • 可通過調(diào)整LIMIT值控制單次影響

執(zhí)行效果監(jiān)控

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

2.3 方案三:pt-online-schema-change

Percona工具鏈的黃金方案:

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

原理:

  • 創(chuàng)建影子表(結(jié)構(gòu)+新字段定義)
  • 增量同步原表數(shù)據(jù)到影子表
  • 原子切換表名

2.4 方案四:主從切換更新

操作步驟:

  • 在從庫執(zhí)行全量更新
  • 主從切換(需配合VIP或DNS切換)
  • 原主庫作為新從庫追平數(shù)據(jù)

三、Python自動(dòng)化實(shí)現(xiàn)詳解

3.1 完整腳本代碼

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)

3.2 關(guān)鍵優(yōu)化點(diǎn)

  • 動(dòng)態(tài)進(jìn)度顯示

print(f"\r進(jìn)度: {updated}/{total} ({progress:.2f}%)", end="")
    • \r實(shí)現(xiàn)行內(nèi)刷新輸出
    • 避免日志刷屏
  • 自適應(yīng)批次調(diào)整

if os.getloadavg()[0] > 5.0:
    batch_size = max(50000, batch_size // 2)
  • 連接池支持
from DBUtils.PooledDB import PooledDB
pool = PooledDB(pymysql, db_config)

四、原理深度解析

4.1 InnoDB的鎖機(jī)制

-- 查看當(dāng)前鎖狀態(tài)
SELECT * FROM performance_schema.events_waits_current 
WHERE EVENT_NAME LIKE '%lock%';
  • 行鎖(Record Lock):僅鎖定被更新的記錄
  • 間隙鎖(Gap Lock):WHERE條件無索引時(shí)會(huì)升級(jí)

4.2 MVCC如何保障讀寫分離

  • 讀操作訪問read_view快照
  • 寫操作創(chuàng)建新版本記錄

4.3 事務(wù)拆分最佳實(shí)踐

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

五、生產(chǎn)環(huán)境注意事項(xiàng)

  1. 前置檢查清單

    •  確認(rèn)備庫磁盤空間足夠(至少2倍表大?。?/li>
    •  檢查innodb_buffer_pool_size是否足夠
    •  備份mysqldump -–single-transaction stats_db statistics_data
  2. 熔斷機(jī)制

if time.localtime().tm_hour in range(9,18):  # 白天工作時(shí)間
    print("禁止在業(yè)務(wù)高峰執(zhí)行!")
    sys.exit(1)
  • 監(jiān)控指標(biāo)
watch -n 1 "mysqladmin ext | grep -E 'Threads_running|Queries'"

結(jié)語

通過分批更新、工具輔助、架構(gòu)調(diào)整三種維度的解決方案,配合Python自動(dòng)化腳本的實(shí)現(xiàn),我們成功實(shí)現(xiàn)了2億級(jí)數(shù)據(jù)表的無損更新。建議讀者在實(shí)際操作前:

  1. 在測(cè)試環(huán)境驗(yàn)證腳本
  2. 提前與業(yè)務(wù)方溝通維護(hù)窗口
  3. 準(zhǔn)備好回滾方案(如:通過備份恢復(fù))

經(jīng)驗(yàn)法則:對(duì)于超過1億行的表,單次操作數(shù)據(jù)量控制在10萬條以內(nèi),間隔時(shí)間不少于0.5秒,可確保業(yè)務(wù)平穩(wěn)運(yùn)行。

以上就是MySQL海量數(shù)據(jù)(2億級(jí)表字段)無損更新方案的詳細(xì)內(nèi)容,更多關(guān)于MySQL數(shù)據(jù)無損更新的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!

相關(guān)文章

  • MySQL深分頁問題解決的實(shí)戰(zhàn)記錄

    MySQL深分頁問題解決的實(shí)戰(zhàn)記錄

    優(yōu)化項(xiàng)目代碼過程中發(fā)現(xiàn)一個(gè)千萬級(jí)數(shù)據(jù)深分頁問題,覺著有必要給大家總結(jié)整理下,這篇文章主要給大家介紹了關(guān)于解決MySQL深分頁問題的相關(guān)資料,需要的朋友可以參考下
    2021-09-09
  • MySQL服務(wù)無法啟動(dòng)的解決辦法(親測(cè)有效)

    MySQL服務(wù)無法啟動(dòng)的解決辦法(親測(cè)有效)

    用管理員身份打開cmd試圖啟動(dòng)MySQL時(shí)出現(xiàn)服務(wù)無法啟動(dòng)并提示服務(wù)沒有報(bào)錯(cuò)任何錯(cuò)誤,所以本文小編給大家介紹了一個(gè)親測(cè)有效的解決辦法,需要的朋友可以參考下
    2023-12-12
  • 刪除mysql數(shù)據(jù)表如何操作

    刪除mysql數(shù)據(jù)表如何操作

    在本篇文章里小編給大家分享了關(guān)于刪除mysql數(shù)據(jù)表簡單方法,需要的朋友們可以參考學(xué)習(xí)下。
    2020-06-06
  • mac下安裝mysql忘記密碼的修改方法

    mac下安裝mysql忘記密碼的修改方法

    這篇文章主要介紹了mac下安裝mysql忘記密碼的修改方法,需要的朋友可以參考下
    2017-06-06
  • mysql正確刪除數(shù)據(jù)的方法(drop,delete,truncate)

    mysql正確刪除數(shù)據(jù)的方法(drop,delete,truncate)

    這篇文章主要給大家介紹了關(guān)于mysql正確刪除數(shù)據(jù)的相關(guān)資料,DELETE語句是MySQL中最常用的刪除數(shù)據(jù)的方式之一,但也有幾種其他方法來實(shí)現(xiàn),需要的朋友可以參考下
    2023-10-10
  • 如何實(shí)現(xiàn)MySQL的索引

    如何實(shí)現(xiàn)MySQL的索引

    這篇文章主要介紹了如何實(shí)現(xiàn)MySQL的索引,MySQL中索引分三類,有B+樹索引、Hash索引和全文索引,下面我們一起來看看MySQL索引的具體實(shí)現(xiàn),需要的小伙伴可以參考一下
    2022-01-01
  • MySQL導(dǎo)入.CSV數(shù)據(jù)中文亂碼的解決方式

    MySQL導(dǎo)入.CSV數(shù)據(jù)中文亂碼的解決方式

    當(dāng)將xls或xlsx文件轉(zhuǎn)換為CSV并導(dǎo)入數(shù)據(jù)庫時(shí),可能出現(xiàn)亂碼,原因是編碼格式不是UTF-8,解決方法是使用Notepad或記事本打開CSV文件,所以本文給大家介紹了MySQL導(dǎo)入.CSV數(shù)據(jù)中文亂碼的解決方式,需要的朋友可以參考下
    2024-08-08
  • MySQL查詢性能優(yōu)化的7個(gè)常見查詢錯(cuò)誤及解決方案

    MySQL查詢性能優(yōu)化的7個(gè)常見查詢錯(cuò)誤及解決方案

    數(shù)據(jù)庫性能是Web應(yīng)用和大型軟件系統(tǒng)穩(wěn)定運(yùn)行的關(guān)鍵,即使是精心設(shè)計(jì)的應(yīng)用,如果數(shù)據(jù)庫查詢效率低下,也會(huì)導(dǎo)致用戶體驗(yàn)下降、系統(tǒng)資源浪費(fèi),甚至系統(tǒng)崩潰,本文將深入探討MySQL查詢優(yōu)化,分析常見的查詢錯(cuò)誤,并提供提升數(shù)據(jù)庫性能的實(shí)用技巧,需要的朋友可以參考下
    2025-04-04
  • 輕松掌握MySQL函數(shù)中的last_insert_id()

    輕松掌握MySQL函數(shù)中的last_insert_id()

    相信大家應(yīng)該都知道Mysql函數(shù)可以實(shí)現(xiàn)許多我們需要的功能,這篇文章介紹的Mysql函數(shù)Last_insert_id()就是其中之一,文章通過一個(gè)例子展開來講,應(yīng)該更有助于大家的理解和學(xué)習(xí),有需要的朋友們下面來一起看看吧。
    2016-12-12
  • MySQL通過login_path登錄數(shù)據(jù)庫的實(shí)現(xiàn)示例

    MySQL通過login_path登錄數(shù)據(jù)庫的實(shí)現(xiàn)示例

    login_path是MySQL5.6開始支持的新特性,本文主要介紹了MySQL通過login_path登錄數(shù)據(jù)庫,文中通過示例代碼介紹的非常詳細(xì),需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2025-02-02

最新評(píng)論