そもそも大量に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文が遅いのとテーブルがロックされるのが嫌がられるのがわかりますね。
一時テーブルを作成する方法が取られるのもこちらの結果を考えると納得がいきます。
参考サイト
コメント失礼します。
行を増やすコマンドのところで質問があります。
1. 行を1億にしたい場合はどうすれば実現できますか？
2. 容量を確認するコマンドを叩くとエラーが出てしまいますが解消方法を教えていただけますか？
エラー名：
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘rows,
floor((data_length+index_length) / 1024 / 1024) all_mb,
floo’ at line 2
mysql初学者のため、ご教授いただければと思います。
よろしくお願いします。
一ノ瀬 様
コメントありがとうございます。
ご質問に回答させていただきます。
> 1. 行を1億にしたい場合はどうすれば実現できますか？
サンプルの例でお話しますと、テーブルが空っぽの状態から10行インサートした後に、1千万行にするクエリでもう一つp8を増やすと実現できます。
元々入っている行数が10行なので、10^8(10の8乗)= 1億となります。
INSERT INTO `people` (`name`, `height`, `weight`, `gender`, `created`)
SELECT p1.name, p1.height, p1.weight, p1.gender, p1.created
FROM people p1, people p2, people p3, people p4, people p5, people p6, people p7, people p8;
ただし、一度にあまりに多くの行数を入れてしまうと設定によっては以下のエラーが出てしまうかもしれません。
「The total number of locks exceeds the lock table size」
こちらのエラーがでてしまうとmysqlの構成ファイル(my.cnf)を見直す必要が出てきますのでご注意ください。
> 2. 容量を確認するコマンドを叩くとエラーが出てしまいますが解消方法を教えていただけますか？
いただいたエラーを確認いたしますとSQL構文エラーのようです。
今一度自分の実行環境でコピペして確認しましたが問題なく動作しました。
元のSQL見るとall_mbの前にASが抜けておりましたので環境によってはそれでエラーが出るのかもしれません。
申し訳ありません、本記事のSQLは修正いたしましたのでもう一度お試しください。
それでもこちらエラーが出てしまう場合ですが
・sql文の中に全角スペースが入っている
・必要なところでカンマが抜ける
・必要ないところでカンマが入っている
等で容易に発生しますのでそういった場所がないかご確認をお願いいたします。
本エラーについては以下のサイトの「２．６ エラー 1064（42000）の原因と対処法」にも記載がありますので併せてご確認ください。
https://proengineer.internous.co.jp/content/columnfeature/7054#section203
本記事の容量確認のSQLについては以下のサイト(Tableのサイズ)にも記載がありますので、こちらで動くかの確認もお願い出来ればと思います。
https://qiita.com/ikenji/items/b868877492fee60d85ce
それでも解決出来ない場合はお手数ですがもう一度ご連絡ください。
よろしくお願いいたします。