MySQLの大きなテーブルをすばやく安全に削除

目次
一、表削除プロセス

buffer poolクリア

テーブル関連ディスクファイルの削除

二、ハードリンクの作成
三、削除表
四、ファイル解放スペースの削除
参照先:
高負荷の本番データベースで大きなテーブルを削除するには、drop tableを直接削除すると、ディスクI/Oが大量に発生し、深刻なカードライブラリが発生します.これは高可用性サービスでは受け入れられません.削除テーブルを最適化するには、内部実行プロセスを理解する必要があります.
一、表削除プロセス表削除原理はメモリとディスクの二つの部分操作に分けられる.
テーブルに関連するbuffer poolページをクリアします.テーブル関連のディスクファイルを削除します.

buffer pool削除テーブルを消去すると、MySQLはbuffer poolの対応するページを消去し、この過程でbuffer poolの前のグローバル排他ロックを解除します.MySQL 5.5.23以降のバージョンでは、メモリ構造のクリーンアップ操作によるシステムスループットの変動を軽減するlazy drop table方式が実現されました.具体的な手順:

  1. buffer pool mutexを持っています.  2. buffer poolのflush list mutexを持っています.  3. flush listのスキャンを開始します.    1. dirty pageがdrop tableに属する場合は、flushリストからremoveを直接削除します.    2. 削除されたページ数が#define BUF_を超えた場合LRU_DROP_SEARCH_SIZE 1024という数であれば、buffer pool mutex、flush list mutex、cpuリソースを解放します.      . flush list mutexを解放します.      . buffer pool mutexを解放します.      .強制pthread_を通過yieldはOS context switchを1回行い、残りのcpuタイムスライスを解放する.    3. buffer pool mutexを再保有する.    4. flush list mutextを再保有します.  4. flush list mutexを解放します.  5. buffer pool mutexを解放します.
上記の過程でbuffer pool mutexとflush list mutexを持っており、ロックの保護範囲からbuffer pool mutexの直感的なボトルネックが明らかになります.buffer poolが大きい場合や、テーブルに汚いページがたくさんある場合は、mutexを保持する時間が比較的長くなり、対応するbuffer poolインスタンスを使用するときに他のトランザクションがブロックされ、データベース全体のパフォーマンスに影響します.
この部分の最適化の難点は、ソースコードに関連しており、アプリケーションとしては、削除されたテーブルにアクティブなトランザクションがないことを保証し、アクセスがないことを保証してから、ビジネスの低ピーク期間にテーブル削除操作を実行することです.

テーブル関連ディスクファイルの削除ここでは、独立したテーブルスペース(innodb_file_per_table=1)を使用したinnodbテーブルの削除のみについて説明します.独立した表領域は、共有表領域よりもパフォーマンスとメンテナンスに優れており、現在のほとんどの場合の表ストレージ方式でもあります.メモリスキャンに比べて、ディスクファイルの削除はシステムに与える影響がずっと大きい.問題は、テーブルファイルが大きすぎると、直接削除すると瞬時にI/Oが大量に消費され、IOがブロックされることです.通常、次の3つのステップを使用して大きなテーブルを削除できます.

テーブルファイルのハードリンクを作成します.drop tableテーブルを削除します.テーブルファイルを削除してディスク領域を解放します.二、ハードリンクのディスク上のストレージファイルを作成し、複数のファイル名で参照することができる.この複数のファイルはまったく同じで、同じディスク上のinode indexを指しています.どのファイルを削除しても、実際のストレージファイルには影響しません.参照データを1つ減らすだけです.参照数が1になったときに、再びファイルを削除してこそ、本当に削除されます.
例えば、ハードリンクがない場合、t 1テーブルに対応するディスクファイルは以下の通りである.
-rw-r----- 1 mysql mysql        17973 Jul  3  2018 t1.frm-rw-rw---- 1 mysql mysql 498115543040 Oct  7 17:14 t1.ibdの2番目のフィールドの「1」は、ファイルにinodeポインタが1つしかないことを示し、t 1テーブルを削除すると実際にディスクファイルが削除されます.ハードリンクの作成後:
ln t1.frm t1.frm.hln t1.ibd t1.ibd.h各テーブルファイルには2つのinode参照があります.
-rw-r----- 2 mysql mysql        17973 Jul  3  2018 t1.frm-rw-r----- 2 mysql mysql        17973 Jul  3  2018 t1.frm.h-rw-rw---- 2 mysql mysql 498115543040 Oct  7 17:14 t1.ibd-rw-rw---- 2 mysql mysql 498115543040 Oct  7 17:14 t1.ibd.hテーブルを削除するとinodeリファレンスが削除されるだけで、通常のサービスには影響しません.次のスクリプトを使用して、データベース内のすべてのテーブルにハードリンクを作成できます.
MySQLデータディレクトリ
datadir= mysql -uroot -p123456 -S /data/mysqldata/mysql.sock -e "show variables like 'datadir'" -N -B | awk '{print $2}'
データベース名
dbname='dbname'
cd $datadir/$dbname
ハードリンクの作成
ls *.{ibd,frm}|awk'{print"ln"$0""$0".h"}|bash 3、テーブルdrop table t 1を削除する.MySQLのdrop table操作は、システムテーブルのt 1に関するレコードを削除するとともに、テーブルのt 1を削除する.frmとt 1.ibdファイル.以上のように、この操作は瞬時に完了する、その後2つ残っている.h
-rw-r----- 1 mysql mysql        17973 Jul  3  2018 t1.frm.h-rw-rw---- 1 mysql mysql 498115543040 Oct  7 17:14 t1.ibd.h四、削除ファイル解放空間t 1表は秒削除されたが、ディスク領域は解放されなかった.次にt 1を削除する必要がある.frm.hとt 1.ibd.hは本当に空間を解放する.この場合、500 Gに近いこのファイルを直接rmすることはできません.理由はI/O衝撃にもなります.対応策はcoreutilsのtruncateツールを使用して、ファイルサイズを逐次削減し、最終的にシステムに影響を与えない小さなファイル削除操作を実行することです.例えば、実際にt 1の2つの残りを削除する.h:
rmtablefile.sh t1rmtablefile.shスクリプトファイルの内容は以下の通りです.
#!/bin/bash
テーブル定義ファイルは小さく、直接削除できます
rm $1.frm.h
テーブルデータファイルサイズ、単位M
filesize= ls -l $1.ibd.h | awk '{print int($5/1024/1024)}' if (( $filesize < 100 ))then
100未満の直接削除

rm $1.ibd.h

else
100以上で、毎回100 Mを切断する

for i in `seq $filesize -100 0`
do
    sleep 2
    echo $i
    truncate -s ${i}M $1.ibd.h
done

#     100M   
rm $1.ibd.h

fi truncateを使用してファイルサイズを100 M縮小するたびに、ファイルが100 M未満の場合に削除します.なお、このステップで実際にディスクファイルを削除する操作は、オンラインサービスに影響を及ぼさないことを目標としており、その上でファイルを安全に削除できればよい.