人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

MySQLで複数テーブルに対し、共通するIDをもつレコードを削除したいと思います。

DELETE tb1,tb2,tb3 FROM tb1 LEFT JOIN tb1.id=tb2.id LEFT JOIN tb1.id=tb3.id WHERE tb1.id='1'

とすれば可能なのですが、削除したいテーブルが増えた場合、またはテーブル内の対象レコードが多い場合、LEFTJOINで結合していると、かなり負荷がかかって削除出来ません。(インデックスを設定していても)
ちなみにLEFT JOINにしているのは、各テーブルに対象IDが存在しない場合があるからです。

GROPが使えればと思ったのですが、駄目でした。

何か良い方法がありましたら、ご意見いただければと思います。

※MySQLは4.1.22を利用しています。
※1テーブル毎にIDの存在を確認して削除する方法以外で教えて下さい。

●質問者: k27w
●カテゴリ:ウェブ制作
✍キーワード:DELETE MySQL インデックス レコード 存在
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● hijk05
●23ポイント

http://sqltips.blog40.fc2.com/blog-entry-6.html

http://0-9.sakura.ne.jp/blog/archives/2008/07/10013846.html

JOINは使用してよいので、とりあえず上記URLのようにテーブル毎に削除するようにしたら良いです。

◎質問者からの返答

質問にも書きましたが、LEFT JOINの数が増えると負荷がかかるので、他の方法を模索しています。(おっしゃっているのは、INNER JOINを使う方法ではありませんか?)


2 ● chuken_kenkou
●23ポイント

表を構成する列と、表と表の関連を示してもらえれば、より具体的なアドバイスができると思うのですけどね。

参照整合性制約、外部キー制約を使ってみてはいかがでしょうか?

整合性が崩れる操作を制約する方法と、整合性が崩れないように子も操作する方法があります。

なお、MySQLでは、InnoDBのみ、対象にできます。


MySQL :: MySQL 4.1 リファレンスマニュアル :: 6.5.3 CREATE TABLE 構文


1.制約

(1)親の削除時、子がいると削除できない

(2)親の主キー更新時、子がいると更新できない

(3)子を追加する場合、外部キーに対応する主キーを持つ親がいないと、追加できない。

(4)子の外部キー更新時、更新後の値と同じ主キーを持つ親がいないと更新できない


2.動作

(1)親の削除時、子も削除

(2)親の主キー更新時、子の外部キーも更新


create table tbl1
(pkey int primary key,
 data1 varchar(10))
engine=innodb
;
create table tbl2
(fkey int,
 data2 varchar(10),
 foreign key(fkey) references tbl1(pkey),
 index(fkey))
engine=innodb
;
◎質問者からの返答

詳しく回答していただいてありがたいのですが、innodbではなく、MyISMを使っていて、それを変更する気はありません。質問に書かずにすみませんでした。


3 ● shintabo
●22ポイント

実際に試してみました

http://modperlis.bounceme.net/2008/11/hatenaquestion.html

EXPLAIN SELECT tb1.* FROM tb1 LEFT JOIN tb2 ON tb1.id = tb2.id LEFT JOIN tb3 ON tb1.id = tb3.id WHERE tb1.id = '6';

などの結果を教えていただけると助かります。

※idというカラムを見ると、PRIMARYと思ってしまうので・・・

◎質問者からの返答

質問の仕方が悪かった(と言うか誤り)がありました。


ブログにまで書いていただいたのに、申し訳ありませんでした。結合しているIDはPRIMARY ではないです。

質問文のソースを短くしようと、単に「id」としたのですが、それが回答者様を惑わす原因になったようです。

お手数おかけして申し訳ありませんでした。


4 ● chuken_kenkou
●22ポイント

#2回答者です。

いくつか確認事項があります。


1.

>テーブル内の対象レコードが多い場合、LEFTJOINで結合していると、かなり負荷がかかって削除

>出来ません。(インデックスを設定していても)


対象レコード数が多いとのことですが、母体件数がどのくらいあり、その内、何件くらいを対象にしているのでしょうか?

LEFT JOINに関しては、インデクスが有効利用できているのでしょうか?

DELETEでなく、SELECTなら、それなりに性能は確保されているのでしょうか?


2.

>innodbではなく、MyISMを使っていて、それを変更する気はありません


これは、レンタルサーバ等の制限があるといった理由でしょうか?

せっかく具体的な提案をしても、「そういった対処をする気がない」、「そういった対処はできない」と返事されたのでは、どういう提案をすればいいのか判断できなくなります。


3.

結合キーにしているID列は、通番のようなデータでしょうか?

auto_incrementで生成しているような、

(1)一意である値を自動生成できることに意味がある

(2)値の内容自体に意味はない

といったデータでしょうか?

もし、そうであれば、DELETEするのでなく、「削除を示す」付加情報、つまり「削除フラグ」を付け、DELETEするのでなく、UPDATEで擬似削除する方法は考えられないのでしょうか?

通常、DELETEに比べ、UPDATEは負荷は軽いです。


4.

実際の表やインデクスの定義。表と表の関係。

(1)表とその構成列、そのデータ型・・・特にTEXT系やVARCHAR系の有無や定義長

(2)どういうインデクス定義にしているか

(a)PRIMARY KEY

(b)UNIQUE・・・表定義でのUNIQUE指定またはCREATE UNIQUE INDEX

(c)INDEX・・・表定義でのINDEX指定またはCREATE INDEX


5.

>ちなみにLEFT JOINにしているのは、各テーブルに対象IDが存在しない場合があるからです。


親に対応する子でないものを、本来、DELETEの対象にすべきではないのではないでしょうか?

最終的に「WHERE tb1.id='1'」で絞り込んでいるので、これはINNER JOINと同じです。

◎質問者からの返答

1:

母体件数は1万件ぐらいで、実際に対象となるキーは100件ぐらいだと思うのですが、LEFT JOINでいくつもテーブルを繋げていくと、サーバがダウンするので、物凄い数が結合されているのでしょう。

ちなみにSELECTなら一瞬です。GROUP BYで結合する対象のIDを指定してるというのも大きいと思います。


2:

書き方が悪かったら申し訳ありません。ただ、今あるテーブルはすべてMyISAMです。それをinnodbに変えるのは躊躇します。トランザクションなどが使えるからいいとは思うのですが、データ表示・検索の際にパフォーマンスが落ちるので。ですので、極端な話、innodbじゃないと私の質問した事が出来ないのなら、諦めようと思います。


3:

auto_incrementではありません。質問ではわかりやすく「id」として書きましたが、逆にわかりづらかったかもしれませんね。

利用用途で言えば、利用者IDとかカテゴリIDとかその類です。auto_incrementのIDとはまた別です。


4:

3でも書きましたが、おそらく私の質問の書き方が悪かったのだと思います。それは大変申し訳ありませんでした。

テーブルの構成は

id|user_id|body がフィールド

idがauto_incrementであり、PRIMARY KEY

user_idがINDEX

このような構成のテーブルでテストしていました。


5:

これも質問文の問題ですね。すみません。

例えば

SELECT * FROM tb1 LEFT JOIN tb1.user_id=tb2.user_id LEFT JOIN tb1.user_id=tb3.user_id WHERE tb1.user_id='1'


とした場合の結果をご想像いただければLEFT JOINを使っている意味もおわかりいただけるのではないでしょうか。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ