概要
INSERT文は、MySQLのテーブルに新しい行を挿入するためのSQL文である。
基本的な構文として、VALUES 形式、SET 形式、VALUES ROW() 形式が提供されており、用途に応じて使い分けることができる。
INSERT文は、単一行だけでなく複数行を一度に挿入することも可能であり、パフォーマンスの観点からは複数行挿入が推奨される。
また、INSERT ... SELECT 構文を使用することで、他のテーブルから取得したデータを直接挿入することができ、データ移行やバックアップ作成に有用である。
重複キーの処理については、INSERT ... ON DUPLICATE KEY UPDATE 構文を使用することで、UPSERT (INSERT または UPDATE) 動作を実現できる。
これにより、同一キーのレコードが存在する場合は更新、存在しない場合は挿入という処理を1つのSQL文で記述可能である。
AUTO_INCREMENT 列への挿入時は、値を指定せずに自動採番させることが一般的であり、LAST_INSERT_ID() 関数で採番されたIDを取得できる。
外部キー制約が設定されている場合は、参照整合性を維持するために、親テーブルに存在する値のみを挿入する必要がある。
大量のデータを挿入する際は、バルクインサート (複数行を1文で挿入) や LOAD DATA INFILE 文の使用により、パフォーマンスを大幅に向上させることができる。
InnoDBテーブルでは、UNIQUE制約チェックの無効化やPRIMARY KEY順序での挿入により、さらなる最適化が可能である。
トランザクション内で実行することで、挿入の一貫性を保証し、エラー発生時にはロールバックにより挿入を取り消すことができる。
基本構文
VALUES構文
最も一般的なINSERT文の構文は、VALUES 句を使用する形式である。
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
VALUES (value_list) [, (value_list)] ...
[ON DUPLICATE KEY UPDATE assignment_list]
基本的な使用例を以下に示す。
# 単一行の挿入
INSERT INTO users (name, email, age)
VALUES ('Taro Yamada', 'taro@example.com', 30);
# 列名を省略 (全列の値を順序通りに指定)
INSERT INTO users
VALUES (1, 'Hanako Suzuki', 'hanako@example.com', 25);
# 複数行の挿入
INSERT INTO users (name, email, age)
VALUES
('Ichiro Sato', 'ichiro@example.com', 28),
('Jiro Tanaka', 'jiro@example.com', 35),
('Saburo Kato', 'saburo@example.com', 42);
SET構文
SET 句を使用する形式は、UPDATE文と同様の構文で挿入を行う。
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET assignment_list
[ON DUPLICATE KEY UPDATE assignment_list]
使用例を以下に示す。
# SET構文での挿入
INSERT INTO users
SET name = 'Shiro Ito',
email = 'shiro@example.com',
age = 33;
※注意
SET構文では、複数行の挿入はできない。
VALUES構文の方が柔軟性が高いため、一般的にはVALUES構文が推奨される。
VALUES ROW()構文
MySQL 8.0.19以降では、VALUES ROW() 構文が利用可能である。
# VALUES ROW()構文
INSERT INTO users (name, email, age)
VALUES ROW('Goro Nakamura', 'goro@example.com', 29);
VALUES ROW()構文の特徴を以下に示す。
- テーブル値コンストラクタとして使用可能
- JOINやUNIONと組み合わせ可能。
- ROW()は空にできない
- 各スカラー値はNULL可。
- 出力列名は暗黙的に column_0, column_1, column_2... となる
- エイリアスで明示的に指定可能。
修飾子
INSERT文では、下表に示す修飾子を使用して挿入動作を制御できる。
| 修飾子 | 説明 |
|---|---|
| LOW_PRIORITY | 他のクライアントがテーブルを読み取っていない時に挿入を実行する。 MyISAM、MEMORY、MERGEストレージエンジンで有効 |
| HIGH_PRIORITY | 通常の挿入よりも優先的に実行する。LOW_PRIORITY との同時使用は不可
|
| DELAYED | MySQL 8.0では無視される (廃止予定)。 MySQL 5.7以前では、挿入をキューイングして非同期実行 |
| IGNORE | 重複キーエラーや制約違反エラーを WARNING に変換して処理を継続する。エラーが発生した行はスキップされる |
使用例を以下に示す。
# 低優先度で挿入を実行
INSERT LOW_PRIORITY INTO logs (message, level)
VALUES ('System started', 'INFO');
# エラーを無視して挿入を継続
INSERT IGNORE INTO users (id, name, email)
VALUES
(1, 'User A', 'usera@example.com'),
(1, 'User B', 'userb@example.com'), -- 重複キーエラーはスキップ
(2, 'User C', 'userc@example.com');
PARTITION句
パーティション化されたテーブルでは、PARTITION 句を使用して特定のパーティションへの挿入を明示的に指定できる。
# 特定のパーティションに挿入
INSERT INTO sales
PARTITION (p_2024_q1)
(product_id, amount, sale_date)
VALUES (100, 5000, '2024-01-15');
# 複数パーティションを指定
INSERT INTO logs
PARTITION (p0, p1)
(message, level)
VALUES ('Log entry', 'INFO');
※注意
指定したパーティションが、挿入するデータのパーティショニング条件と一致しない場合はエラーになる。
通常は、PARTITION句を省略して、MySQLに自動的にパーティションを決定させることが推奨される。
複数行の挿入
複数行を一度に挿入することで、パフォーマンスを大幅に向上させることができる。
基本的な複数行挿入
VALUES句に複数の値リストをカンマ区切りで指定する。
# 複数行を一度に挿入
INSERT INTO products (name, price, stock)
VALUES
('Product A', 1000, 50),
('Product B', 1500, 30),
('Product C', 2000, 20),
('Product D', 2500, 10);
パフォーマンスの利点
複数行挿入は、単一行挿入を繰り返すよりも大幅に高速である。
理由を以下に示す。
- ネットワークラウンドトリップの削減
- 1回のSQL文送信で複数行を挿入できる。
- パースとプランニングのオーバーヘッド削減
- SQL文の解析と実行計画の作成が1回で済む。
- ロックの取得と解放の回数削減
- テーブルロックの取得が1回で済む。
- インデックス更新の最適化
- 複数行のインデックス更新を一括で実行できる。
パフォーマンス比較の例を以下に示す。
# 低速：単一行挿入を1000回繰り返す
INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com');
INSERT INTO users (name, email) VALUES ('User 2', 'user2@example.com');
# ... 1000回繰り返す
# 高速：1000行を1度に挿入
INSERT INTO users (name, email)
VALUES
('User 1', 'user1@example.com'),
('User 2', 'user2@example.com'),
# ... 1000行
('User 1000', 'user1000@example.com');
一般的に、複数行挿入は単一行挿入の繰り返しに比べて、数倍から数十倍の速度向上が期待できる。
推奨バッチサイズ
一度に挿入する行数の推奨値を以下に示す。
- 推奨範囲
- 1,000～10,000行
- 理由
- SQL文のサイズとパフォーマンスのバランスが良い。
- 制限事項
max_allowed_packetの設定値を超えないようにする必要がある。
max_allowed_packet の確認と変更方法を以下に示す。
# 現在の設定値を確認 (デフォルトは4MBまたは16MB)
SHOW VARIABLES LIKE 'max_allowed_packet';
# セッション単位で変更 (例: 64MBに設定)
SET GLOBAL max_allowed_packet = 67108864; -- 64MB
※注意
max_allowed_packet を超える巨大なINSERT文を実行すると、エラーになる。
大量データの挿入時は、適切なバッチサイズに分割することが重要である。
INSERT ... SELECT
他のテーブルから取得したデータを直接挿入する構文である。
基本構文
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
SELECT ...
[ON DUPLICATE KEY UPDATE assignment_list]
基本的な使用例
# 他のテーブルから全データをコピー
INSERT INTO users_backup
SELECT * FROM users;
# 特定の列のみをコピー
INSERT INTO active_users (name, email)
SELECT name, email FROM users WHERE status = 'active';
# WHERE句で条件を指定
INSERT INTO premium_users (name, email, plan)
SELECT name, email, 'premium'
FROM users
WHERE subscription_type = 'premium';
JOINを使用した挿入
複数のテーブルを結合してデータを挿入できる。
# JOINを使用した挿入
INSERT INTO user_orders (user_name, order_total)
SELECT u.name, SUM(o.amount)
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING SUM(o.amount) > 10000;
# 複数テーブルのJOIN
INSERT INTO sales_summary (product_name, category, total_sales)
SELECT p.name, c.name, SUM(s.amount)
FROM products p
INNER JOIN categories c ON p.category_id = c.id
INNER JOIN sales s ON p.id = s.product_id
WHERE s.sale_date >= '2024-01-01'
GROUP BY p.id, p.name, c.name;
同一テーブルからの選択
同じテーブルからSELECTして挿入する場合、MySQLは内部的に一時テーブルを作成する。
# 同一テーブルからの挿入 (内部的に一時テーブルを使用)
INSERT INTO users (name, email, created_at)
SELECT CONCAT(name, ' (copy)'), CONCAT('copy_', email), NOW()
FROM users
WHERE status = 'active';
※注意
同一テーブルからのSELECTは、一時テーブルの作成によりパフォーマンスが低下する可能性がある。
サブクエリとの組み合わせ
# サブクエリを使用した挿入
INSERT INTO top_customers (name, total_spent)
SELECT u.name, (
SELECT SUM(amount) FROM orders WHERE user_id = u.id
) AS total
FROM users u
WHERE u.status = 'active'
HAVING total > 50000;
INSERT ... ON DUPLICATE KEY UPDATE
重複キーが検出された場合にUPDATE文を実行する構文である。
これにより、UPSERT (INSERT または UPDATE) 動作を1つのSQL文で実現できる。
基本構文
INSERT INTO tbl_name (col_name [, col_name] ...)
VALUES (value_list) [, (value_list)] ...
ON DUPLICATE KEY UPDATE
col_name = expression [, col_name = expression] ...
基本的な使用例
# PRIMARY KEYまたはUNIQUE KEYが重複した場合にUPDATE
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (100, 1, NOW())
ON DUPLICATE KEY UPDATE
login_count = login_count + 1,
last_login = NOW();
# 複数行の挿入とUPDATE
INSERT INTO inventory (product_id, stock)
VALUES
(1, 10),
(2, 20),
(3, 30)
ON DUPLICATE KEY UPDATE
stock = stock + VALUES(stock); -- MySQL 8.0.19以前
VALUES()関数の非推奨化
MySQL 8.0.20以降、VALUES() 関数は非推奨となった。
代わりに、新しい値のエイリアスを使用する構文が推奨される。
# MySQL 8.0.19以前の構文 (非推奨)
INSERT INTO products (id, name, price)
VALUES (1, 'Product A', 1000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);
# MySQL 8.0.20以降の推奨構文 (エイリアスを使用)
INSERT INTO products (id, name, price)
VALUES (1, 'Product A', 1000) AS new
ON DUPLICATE KEY UPDATE
price = new.price;
# 複数列の更新
INSERT INTO products (id, name, price, stock)
VALUES (1, 'Product A', 1000, 50) AS new
ON DUPLICATE KEY UPDATE
name = new.name,
price = new.price,
stock = stock + new.stock;
affected_rowsの値
ON DUPLICATE KEY UPDATE の実行結果は、以下のように解釈される。
| affected_rows | 意味 |
|---|---|
| 1 | 新しい行が挿入された |
| 2 | 既存の行が更新された |
| 0 | 既存の行が存在したが、値が変更されなかった (更新前と更新後が同じ値) |
確認方法を以下に示す。
INSERT INTO user_stats (user_id, login_count)
VALUES (100, 1) AS new
ON DUPLICATE KEY UPDATE
login_count = login_count + 1;
SELECT ROW_COUNT(); -- 1=新規挿入, 2=更新, 0=変更なし
計算式を使用した更新
# 既存の値に加算
INSERT INTO daily_sales (sale_date, total_amount)
VALUES ('2024-01-15', 5000) AS new
ON DUPLICATE KEY UPDATE
total_amount = total_amount + new.total_amount;
# 条件式を使用
INSERT INTO products (id, name, price, discount_rate)
VALUES (1, 'Product A', 1000, 0.1) AS new
ON DUPLICATE KEY UPDATE
price = new.price,
discount_rate = IF(new.price > 5000, 0.2, 0.1);
# タイムスタンプの更新
INSERT INTO users (id, name, email, created_at)
VALUES (100, 'User A', 'usera@example.com', NOW()) AS new
ON DUPLICATE KEY UPDATE
name = new.name,
email = new.email,
updated_at = NOW();
DEFAULT値とNULL
列にDEFAULT値が設定されている場合、または NULL が許可されている場合の挿入動作について説明する。
DEFAULTキーワード
DEFAULT キーワードを使用して、列のデフォルト値を明示的に挿入できる。
# テーブル定義
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
status VARCHAR(20) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
# DEFAULTキーワードを使用
INSERT INTO users (name, status, created_at)
VALUES ('User A', DEFAULT, DEFAULT);
# 列を省略 (自動的にDEFAULT値が使用される)
INSERT INTO users (name)
VALUES ('User B');
NULL値の挿入
NULL が許可されている列には、明示的に NULL を挿入できる。
# テーブル定義
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT NULL
);
# NULL値を明示的に挿入
INSERT INTO products (name, description)
VALUES ('Product A', NULL);
# 列を省略 (自動的にNULLが挿入される)
INSERT INTO products (name)
VALUES ('Product B');
STRICTモードと非STRICTモード
MySQLのSQLモード設定により、不正な値の挿入時の動作が異なる。
| 項目 | STRICTモード | 非STRICTモード |
|---|---|---|
| NOT NULL列への NULL挿入 | エラー | DEFAULT値または型の暗黙的なデフォルト値が挿入される |
| DEFAULT値がない列の省略 | エラー | 型の暗黙的なデフォルト値が挿入される |
| 範囲外の値 | エラー | 範囲の境界値に切り詰められる |
| 不正な日付 | エラー | '0000-00-00' が挿入される |
SQLモードの確認と変更方法を以下に示す。
# 現在のSQLモードを確認
SELECT @@sql_mode;
# STRICTモードを有効化
SET sql_mode = 'STRICT_TRANS_TABLES';
# STRICTモードを無効化
SET sql_mode = '';
※推奨
データの整合性を保つため、STRICTモード (STRICT_TRANS_TABLES) の使用が推奨される。
AUTO_INCREMENT
AUTO_INCREMENT 属性を持つ列は、値を指定しない場合に自動的に連番が採番される。
基本的な使用例
# テーブル定義
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
# AUTO_INCREMENT列に値を指定しない
INSERT INTO users (name, email)
VALUES ('User A', 'usera@example.com');
-- id=1 が自動採番される
INSERT INTO users (name, email)
VALUES ('User B', 'userb@example.com');
-- id=2 が自動採番される
LAST_INSERT_ID()関数
LAST_INSERT_ID() 関数を使用して、最後に採番されたAUTO_INCREMENT値を取得できる。
# 挿入後にIDを取得
INSERT INTO users (name, email)
VALUES ('User C', 'userc@example.com');
SELECT LAST_INSERT_ID();
-- 最後に挿入された行のid値を返す
※重要
LAST_INSERT_ID() はセッション単位で管理される。
他のクライアントの挿入操作の影響を受けない。
複数行挿入時のLAST_INSERT_ID()
複数行を1度に挿入した場合、LAST_INSERT_ID() は最初の行のIDを返す。
# 複数行を挿入
INSERT INTO users (name, email)
VALUES
('User D', 'userd@example.com'), -- id=4 (これが返される)
('User E', 'usere@example.com'), -- id=5
('User F', 'userf@example.com'); -- id=6
SELECT LAST_INSERT_ID(); -- 4 を返す
挿入された全てのIDを取得する方法を以下に示す。
# 挿入された行数を取得
SELECT ROW_COUNT(); -- 3 を返す
# 挿入されたIDの範囲
# 開始ID: LAST_INSERT_ID()
# 終了ID: LAST_INSERT_ID() + ROW_COUNT() - 1
AUTO_INCREMENT値の明示的な指定
AUTO_INCREMENT列に明示的に値を指定することも可能である。
# AUTO_INCREMENT列に明示的に値を指定
INSERT INTO users (id, name, email)
VALUES (100, 'User G', 'userg@example.com');
# 次の自動採番は101から開始される
INSERT INTO users (name, email)
VALUES ('User H', 'userh@example.com'); -- id=101
※注意
既存のIDと重複する値を指定すると、重複キーエラーになる。
AUTO_INCREMENT初期値の設定
# テーブル作成時に初期値を指定
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
) AUTO_INCREMENT = 1000;
# 既存テーブルの初期値を変更
ALTER TABLE products AUTO_INCREMENT = 2000;
外部キー制約と挿入
外部キー制約が設定されている場合、参照整合性を維持するために、親テーブルに存在する値のみを挿入できる。
基本的な外部キー制約
# 親テーブル
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100)
);
# 子テーブル (外部キー制約あり)
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
FOREIGN KEY (user_id) REFERENCES users(id)
);
# 親テーブルにデータを挿入
INSERT INTO users (id, name) VALUES (1, 'User A');
# 子テーブルに正常に挿入 (user_id=1は親テーブルに存在)
INSERT INTO orders (id, user_id, amount)
VALUES (100, 1, 5000);
# エラー：user_id=999は親テーブルに存在しない
INSERT INTO orders (id, user_id, amount)
VALUES (101, 999, 3000);
ON DELETE CASCADE
親レコード削除時に、子レコードも自動的に削除される制約である。
# 外部キー制約の定義
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
);
# データ挿入
INSERT INTO users (id, name) VALUES (1, 'User A');
INSERT INTO orders (id, user_id, amount) VALUES (100, 1, 5000);
# 親レコードを削除すると、子レコードも自動削除される
DELETE FROM users WHERE id = 1;
-- orders テーブルの user_id=1 の行も自動削除される
外部キーチェックの一時的な無効化
データの一括挿入時に外部キーチェックを一時的に無効化する方法を以下に示す。
# 外部キーチェックを無効化
SET FOREIGN_KEY_CHECKS = 0;
# 外部キー制約を無視してデータ挿入
INSERT INTO orders (id, user_id, amount)
VALUES (102, 999, 3000); -- user_id=999は親テーブルに存在しないがエラーにならない
# 外部キーチェックを再度有効化
SET FOREIGN_KEY_CHECKS = 1;
※警告
外部キーチェックを無効にすると、データの整合性が失われる可能性がある。
使用後は必ず再度有効化し、データの整合性を確認することが重要である。
挿入結果の確認
INSERT文の実行結果は、挿入された行数として表示される。
INSERT INTO users (name, email)
VALUES ('User A', 'usera@example.com');
# 出力例： Query OK, 1 row affected (0.01 sec)
複数行挿入時の出力例を以下に示す。
INSERT INTO users (name, email)
VALUES
('User B', 'userb@example.com'),
('User C', 'userc@example.com'),
('User D', 'userd@example.com');
# 出力例： Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0
ROW_COUNT()関数
ROW_COUNT() 関数を使用して、直前のINSERT文で挿入された行数を取得できる。
INSERT INTO products (name, price)
VALUES ('Product A', 1000);
SELECT ROW_COUNT();
# 出力例 # +--------------+ # | ROW_COUNT() | # +--------------+ # | 1 | # +--------------+
※注意
ROW_COUNT()関数は、直後のSELECT文で実行する必要がある。
他のSQL文を実行すると、値がリセットされる。
IGNORE修飾子使用時
IGNORE 修飾子を使用した場合、エラーが発生した行はスキップされ、WARNINGとして記録される。
INSERT IGNORE INTO users (id, name, email)
VALUES
(1, 'User A', 'usera@example.com'),
(1, 'User B', 'userb@example.com'), -- 重複キーエラー (スキップ)
(2, 'User C', 'userc@example.com');
# 出力例： Query OK, 2 rows affected, 1 warning (0.01 sec) Records: 3 Duplicates: 1 Warnings: 1
WARNINGの内容を確認する方法を以下に示す。
SHOW WARNINGS;
# 出力例 # +---------+------+---------------------------------------+ # | Level | Code | Message | # +---------+------+---------------------------------------+ # | Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' | # +---------+------+---------------------------------------+
パフォーマンス最適化
大量のデータを挿入する際のパフォーマンス最適化手法について説明する。
バルクインサート
最も効果的な最適化手法は、複数行を1つのINSERT文で挿入することである。
# 低速：単一行挿入を繰り返す
INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com');
INSERT INTO users (name, email) VALUES ('User 2', 'user2@example.com');
# ... 10,000回繰り返す
# 高速：1,000行ずつバッチ挿入
INSERT INTO users (name, email)
VALUES
('User 1', 'user1@example.com'),
('User 2', 'user2@example.com'),
# ... 1,000行
('User 1000', 'user1000@example.com');
# これを10回繰り返す
挿入操作の時間配分を以下に示す。
- 接続 : 3
- データベースへの接続時間
- クエリ送信 : 2
- SQL文のサーバへの送信時間
- パース : 2
- SQL文の解析時間
- 行挿入: 1 × 行サイズ
- 各行の挿入時間
- インデックス挿入 : 1 × インデックス数
- B-treeインデックスの場合、テーブルサイズに対して対数的 (log N) に増加
- クローズ : 1
- 接続のクローズ時間
複数行INSERT文は、接続・パース・クローズのオーバーヘッドを大幅に削減できる。
大量データの挿入
LOAD DATA INFILE
LOAD DATA INFILE 文は、MySQLで最も高速なデータインポート方法である。
通常のINSERT文よりも20倍以上高速な場合がある。
# CSVファイルからデータをインポート (サーバ側ファイル)
LOAD DATA INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS -- ヘッダ行をスキップ
(name, email, age);
# ローカルファイルからインポート (クライアント側ファイル)
LOAD DATA LOCAL INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(name, email, age);
LOAD DATA INFILE と LOAD DATA LOCAL INFILE の違いを以下に示す。
| 項目 | LOAD DATA INFILE | LOAD DATA LOCAL INFILE |
|---|---|---|
| ファイルの場所 | サーバ側 | クライアント側 |
| 速度 | 高速 | サーバ側より低速 (ネットワーク転送あり) |
| エラー時の動作 | 即座に停止可能 | 転送開始後は途中停止不可 |
| セキュリティ | サーバ側ファイルアクセス権限が必要 | local_infile 設定が必要
|
セキュリティ設定の確認と変更方法を以下に示す。
# local_infile の設定を確認
SHOW VARIABLES LIKE 'local_infile';
# local_infile を有効化 (サーバ側)
SET GLOBAL local_infile = 1;
※重要
MySQL 8.0以降では、local_infile はデフォルトで無効化されている。
セキュリティリスクを理解した上で使用する必要がある。
トランザクションの活用
InnoDBテーブルでは、トランザクション内で複数の挿入を実行することで、パフォーマンスを向上できる。
# 自動コミットを無効化
START TRANSACTION;
# 大量のINSERT文を実行
INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com');
INSERT INTO users (name, email) VALUES ('User 2', 'user2@example.com');
# ... 1,000回繰り返す
# コミット
COMMIT;
理由を以下に示す。
- トランザクションログの書き込みが1回で済む
- 各挿入ごとにコミットするよりも高速
- ロックの取得と解放が1回で済む
- オーバーヘッドが削減される
autocommitの無効化
# autocommitを無効化
SET autocommit = 0;
# 大量のINSERT文を実行
INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com');
INSERT INTO users (name, email) VALUES ('User 2', 'user2@example.com');
# ... 1,000回繰り返す
# 手動でコミット
COMMIT;
# autocommitを再度有効化
SET autocommit = 1;
インデックスの無効化
大量のデータを挿入する際は、インデックスを一時的に無効化することでパフォーマンスを向上できる。
# MyISAMテーブルの場合
ALTER TABLE users DISABLE KEYS;
# 大量データを挿入
INSERT INTO users (name, email) VALUES ... ;
# インデックスを再構築
ALTER TABLE users ENABLE KEYS;
※注意
この方法は MyISAM ストレージエンジンでのみ有効である。
InnoDBでは使用できない。
InnoDBテーブルでは、データ挿入後にインデックスを作成する方法が有効である。
# インデックスなしでテーブルを作成
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
# 大量データを挿入
INSERT INTO users (id, name, email) VALUES ... ;
# 挿入後にインデックスを作成
CREATE INDEX idx_email ON users(email);
UNIQUE制約とインデックスの考慮
UNIQUE制約やインデックスが多いテーブルでは、挿入時のパフォーマンスが低下する。
InnoDBテーブルでは、以下の最適化手法が有効である。
# UNIQUE制約チェックの一時的な無効化 (InnoDB)
SET unique_checks = 0;
# 大量データを挿入
INSERT INTO users (id, name, email) VALUES ... ;
# UNIQUE制約チェックを再度有効化
SET unique_checks = 1;
この設定により、セカンダリキーのディスクI/Oを大幅に削減できる。
PRIMARY KEY順序での挿入最適化を以下に示す。
# InnoDBはクラスター化インデックスを使用
# PRIMARY KEY順序での挿入が最適
# 推奨：PRIMARY KEY順序でのインサート
INSERT INTO users (id, name, email)
VALUES
(1, 'User 1', 'user1@example.com'),
(2, 'User 2', 'user2@example.com'),
(3, 'User 3', 'user3@example.com');
# 非推奨：ランダム順序でのインサート (ページ分割が多発)
INSERT INTO users (id, name, email)
VALUES
(100, 'User 100', 'user100@example.com'),
(5, 'User 5', 'user5@example.com'),
(75, 'User 75', 'user75@example.com');
AUTO_INCREMENT最適化を以下に示す。
# innodb_autoinc_lock_mode の確認
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
# interleavedモード (値: 2) に設定 (MySQL 8.0のデフォルト)
# 並行挿入のパフォーマンスが向上
※重要
innodb_autoinc_lock_mode=2 は、レプリケーション環境でROWベースのバイナリログ形式が必要である。
挿入後の最適化
大量のデータを挿入した後は、OPTIMIZE TABLE 文でテーブルを最適化する。
# テーブルの最適化 (断片化の解消、領域の再利用)
OPTIMIZE TABLE users;
OPTIMIZE TABLEの効果を以下に示す。
- テーブルの断片化を解消
- ディスクI/Oの効率化
- 未使用領域の再利用
- ディスクスペースの最適化
- インデックスの再構築
- クエリパフォーマンスの向上
※注意
OPTIMIZE TABLEは、テーブルをロックするため、本番環境では低負荷時に実行することが推奨される。
INSERT文とREPLACE文の違い
INSERT ... ON DUPLICATE KEY UPDATE と REPLACE 文は、どちらも重複キーの処理を行うが、動作が異なる。
| 項目 | INSERT ON DUPLICATE KEY UPDATE | REPLACE |
|---|---|---|
| 基本動作 | 重複時に既存行を更新 | 重複時に既存行を削除して新規行を挿入 |
| 内部処理 | UPDATE処理 | DELETE → INSERT処理 |
| 速度 | 高速 | 低速 (削除と挿入の2段階) |
| PRIMARY KEYの変更 | 変更されない | 変更される可能性がある (AUTO_INCREMENTが再採番) |
| 外部キー制約 | 問題なし | DELETE時に制約違反の可能性 |
| トリガー | UPDATEトリガーが起動 | DELETEとINSERTトリガーが起動 |
| 部分的な更新 | 指定した列のみ更新可能 | 全列が新しい値に置き換わる |
| 推奨度 | 推奨 | 非推奨 (特殊な用途以外) |
REPLACE文の構文
# REPLACE文の基本構文
REPLACE INTO products (id, name, price)
VALUES (1, 'Product A', 1000);
# 複数行のREPLACE
REPLACE INTO products (id, name, price)
VALUES
(1, 'Product A', 1000),
(2, 'Product B', 1500);
動作の違いの例
# テーブル作成
CREATE TABLE test_products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
price INT
);
# 初期データ挿入
INSERT INTO test_products (id, name, price)
VALUES (1, 'Product A', 1000);
# INSERT ON DUPLICATE KEY UPDATE の場合
INSERT INTO test_products (id, name, price)
VALUES (1, 'Product A Updated', 1200) AS new
ON DUPLICATE KEY UPDATE
name = new.name,
price = new.price;
-- 結果: id=1 のまま、name と price のみ更新
# REPLACE の場合
REPLACE INTO test_products (id, name, price)
VALUES (1, 'Product A Replaced', 1500);
-- 結果 : 古い行が削除され、新しい行が挿入される
-- AUTO_INCREMENTカウンタが進む可能性がある
使い分けの指針を以下に示す。
- 部分的な更新が必要な場合
- INSERT ON DUPLICATE KEY UPDATE を使用する。
- 全列を新しい値に置き換える必要がある場合
- REPLACE を使用できるが、DELETE → INSERT の動作に注意が必要である。
- 外部キー制約がある場合
- INSERT ON DUPLICATE KEY UPDATE を使用する。 (REPLACEは制約違反の可能性がある)
- パフォーマンスが重要な場合
- INSERT ON DUPLICATE KEY UPDATE を使用する。 (REPLACEより高速)
- AUTO_INCREMENTカウンタの進行を避けたい場合
- INSERT ON DUPLICATE KEY UPDATE を使用する。
一般的には、INSERT ... ON DUPLICATE KEY UPDATE の使用が推奨される。