質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

3回答

514閲覧

MySQLでたくさん外部キー張られた大量データを含むテーブルへのカラム追加

love_engineerin

総合スコア19

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2018/10/24 02:08

### 質問内容
たくさん外部キー張られた大量データを含むテーブルに対して、
alter table A add XXXとすると、かなり時間がかかってしまいます。

特に本番環境などではロックがかかってしまうため、利用できません。

そのため、現状以下の手順でカラム追加をしています。
(対象テーブルを「A」とします。)

①カラム追加したA_newをcreate table
②AからA_newにselect&insert
③Aへの外部キー外す
④Aをdrop
⑤A_newをAにrename
⑥新しいAに外部キー設定

手間がかかるのでより良い手順があれば教えていただけないでしょうか。

### 環境
MySQL5.7

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

ベストアンサー

多分こちら(【MYSQL】サイズの大きなテーブルに対して素早く属性変更を行う方法)などを参考にされたんだと思いますが、結局alter tableと同じことをやっています。

気になるのは、質問の手順で、元データに対してWRITEをロックしているかどうかです。
そうしないとデータに差異が発生します。
若しくは、元のテーブルをdropする前に、差分を新テーブルに追加するか。

alter table行うとしたら、普通は業務停止して行うものだと思いますので、alter tableを生かして時間を短縮する方法を考えた方が良いかと。

ALTER TABLEを上手に使いこなそう。
上記で、

スキーマの変更をしたい場合には注意するべき点が一つある。コマンドが一回で終わるように、カラムやインデックスの追加・削除を一行で書くということである。一行でALTER TABLEを書くには、加えるべき変更をカンマで区切って羅列する。

とあります。
この点に注意されていないなら、もう一度試す価値はありそうです。

投稿2018/10/24 03:14

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

love_engineerin

2018/10/24 04:11

ご回答ありがとうございます。 WRITEのロックについては完全に考慮不足でした。 仰る通り、SELECT&INSERT中はWRITEをロックする必要があり、 それであればALTER TABLEとやっている内容に違いが無いですね。 (リンク先のブログ、大変参考になりました。) ALTER TABLEが楽で正確なので、それで頑張るのが正解かなと思いました。 (もちろん、カラム追加が少ないように設計するのが前提ですが)
sazi

2018/10/24 04:42

試せてませんが、ALTER TABLEの前にDROP INDEXして、ALTER後にCREATE INDEXすると時間短縮されるかもしれません。 ALTER TABLE自体がそのような動きをしていれば変わらないでしょうけど。
guest

0

使っていない索引、他の索引と統合できる索引は整理します。
外部キーを使わないでもデータの整合性を維持できる力量のあるメンバーで構築したシステム、特にDWHなど大量のデータを扱うシステムでは外部キーをDROPします。

投稿2018/10/24 02:14

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

love_engineerin

2018/10/24 02:36

ご回答ありがとうございます。 DWHでは外部キーをDROPしてしまうのですね。 ただあまりメンバースキルに依存したくないと思いました。
Orlofsky

2018/10/24 03:31

高性能のハードウェアを用意するという選択肢もあります。
guest

0

まずは外部キー制約がほんとにいるのか再検討するところからでしょうね
どうしても必要ということなら結局はご提示のような流れになるでしょうから
あとはプロシージャにまとめるなどして一つ一つの作業を
意識しないでよいようにするか
外部のプログラムでひとまとめに処理するかでしょう

投稿2018/10/24 02:13

yambejp

総合スコア114572

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

love_engineerin

2018/10/24 02:34

ご回答ありがとうございます。 プロシージャにまとめるのは良い方法だと思いました。 ただ、既に外部キーをまとめて付け替えるような機能がMySQLにあるのでは?と思い質問させていただきました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問