そもそも大量にDELETEしない方が安心ですが…。
LIMITをつけて刻んだDELETEでも同様です。
DBのストレージ容量の肥大化を防ぐため、ログテーブルなどのもう利用しない古いレコードを大量に物理削除したいときとかってあります。
物理削除した場合は、
OPTIMIZE TABLE table_name;
を実行しないとディスク領域が解放されません。
ただし、実行した場合はテーブルロックがかかります。
サービスがリアルタイムで稼働中の場合は、実行タイミングに注意が必要です。
試しに実行してみます。
以下のような適当なサンプルテーブルを用意してみます。
CREATE TABLE people (
id int(11) NOT NULL,
name varchar(64) NOT NULL,
height int(11) NOT NULL,
weight int(11) NOT NULL,
gender enum('male','female') NOT NULL,
updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE people
ADD PRIMARY KEY (id),
ADD KEY height (height),
ADD KEY weight (weight);
ALTER TABLE people
MODIFY id int(11) NOT NULL AUTO_INCREMENT;
まず、10行入れます。
INSERT INTO people (name, height, weight, gender) VALUES
('suzuki', 170, 65, 'male'),
('ohtani', 190, 90, 'male'),
('sato', 150, 45, 'female'),
('nakamura', 180, 75, 'male'),
('tanaka', 160, 55, 'female'),
('inoue', 155, 50, 'female'),
('ishida', 160, 60, 'male'),
('matsumoto', 165, 60, 'female'),
('sasaki', 175, 70, 'male'),
('kato', 145, 40, 'female');
10000010行に増やします。
自分の環境では2分30秒ぐらいかかりました。
INSERT INTO people (name, height, weight, gender)
SELECT p1.name, p1.height, p1.weight, p1.gender
FROM people p1, people p2, people p3, people p4, people p5, people p6, people p7;
ここで容量を確認しておきます。
SELECT table_name,
table_rows AS rows,
floor((data_length+index_length) / 1024 / 1024) AS all_mb,
floor(data_length / 1024 / 1024) AS data_mb,
floor(index_length / 1024 / 1024) AS index_mb
FROM information_schema.tables
WHERE table_schema = database()
ORDER BY (data_length + index_length) DESC;
| table_name | rows | all_mb | data_mb | index_mb |
|---|---|---|---|---|
| people | 9739621 | 660 | 406 | 253 |
およそ660MBになっています。
DELETE文でおよそ900万行削除したいと思います。
時間かかるのでブレイクタイムにすることをオススメします。
実行したら自分の環境では12分でした。
DELETE FROM people WHERE id > 1000000;
消し終えたらもう一回、容量を確認します。
| table_name | rows | all_mb | data_mb | index_mb |
|---|---|---|---|---|
| people | 1191489 | 660 | 406 | 253 |
行数は変わったけど容量は変わってないんですよね…。
そこで最適化します。
OPTIMIZE TABLE people;
もしくは
ALTER TABLE people ENGINE INNODB;
14秒くらいかかりました。
| table_name | rows | all_mb | data_mb | index_mb |
|---|---|---|---|---|
| people | 997458 | 81 | 50 | 31 |
減りましたのでめでたしです。
しかし、DELETE文が遅いのとテーブルがロックされるのが嫌がられるのがわかりますね。
一時テーブルを作成する方法が取られるのもこちらの結果を考えると納得がいきます。
参考サイト
