📑

TiDB の外部キーと外部キー制約について

2025/02/11に公開

今日はTiDBの外部キーについて触れていきたいと思います。TiDBはすごいスピードでバージョンアップが行われているサービスであり、その仕様も拡張され続けています。外部キーの仕様についてもバージョンごとに異なる動作をするため、この記事は2025/02/11時点のServerless(v.7.1.3)に基づいています。

TiDBのドキュメントには以下の記載があります。

外部キーを使用すると、関連データのテーブル間参照が可能になります。一方、外部キー制約は、関連データの一貫性を保証します。v6.6.0 以降、TiDB は外部キーと外部キー制約をサポートします。v8.5.0 以降、この機能は一般に利用可能になります。

外部キーと外部キー制約

例えば以下２つのテーブルがあったとします。
参照元テーブル（子テーブル）：外部キーを持つテーブル。
参照先テーブル（親テーブル）：外部キーが参照するテーブル（通常は主キーが定義されているテーブル）。
外部キーによってこの２つのテーブルが関係性を持ちます。外部キー制約とは、子テーブルの外部キーに、親テーブルに存在しない値を挿入できない、もしくは、子テーブルが保有している外部キーの値が存在している状態（親テーブルと同じ値が格納されている状態）で、親テーブルのレコードを先に削除できない（子テーブルのレコードが参照先を失う）などの挙動を管理するものになります。

複数テーブルにまたがるレコードの参照整合性を維持させることがアプリケーションレイヤーではなくデータベースミドルウェアレイヤーで行えるため、運用上便利になりますが子テーブルのレコード操作に対して常に親テーブルを参照することになるため、パフォーマンス上の問題が発生するため、その利用は限定的にとどめる必要があります。特にTiDBはRDBではなく分散型New SQLデータベースであるためその影響を強く受けるようです。このためドキュメントにも以下のように記載されています。

外部キー機能は通常、 参照整合性制約チェックを強制するために使用されます。パフォーマンスの低下を引き起こす可能性があるため、パフォーマンスが重要なシナリオで使用する前に徹底的なテストを実施することをお勧めします。

v.6.6.0 と v.7.x の違い

ChatGPTやClaudeにtidb のforeign keyとmysql のforeign keyの違いについて教えてくださいという質問を投げると面白い回答が戻ってきます。
chatGPT

claude

つまり、外部キーという設定は存在するが、外部キー制約は存在しないためパフォーマンスに影響を与えない、という回答が戻ってきます。確認したところ外部キーがサポートされたのはv.6.6.0からでその時点では外部キー制約は存在していなかったようです。（そのバージョンのServerlessクラスターが今は起動できないので確認はできませんが、中の方に聞いたらそうでした）一方今のバージョンでは外部キー制約が存在しており仕様が異なっています。

v.6.6.0からServerlessクラスターの現行バージョンである7.1.3までのReleaseNoteを全て確認しましたが、どの時点で外部キー制約がサポートされたかは不明ですが、テストしたところ確かにそうなっています。

やってみる

ではまず単純に以下のクエリーを実行します。

USE test;

-- 親テーブル
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

-- 子テーブル
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- まず親テーブルにデータを挿入
INSERT INTO categories (name) VALUES ('Example Category');

-- その後、子テーブルにデータを挿入
INSERT INTO products (category_id) VALUES (1);

delete from categories;

deleteの行で以下のエラーが出るため、確かに外部キー制約が存在していることが確認できます。

Cannot delete or update a parent row: a foreign key constraint fails (`test`.`products`, CONSTRAINT `fk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`))

同様にdrop tableもエラーとなります。

drop table categories;
Cannot drop table 'categories' referenced by a foreign key constraint 'fk_1' on table 'products'.

親テーブルに存在しない外部キーの値を子テーブルに挿入しようとした時も同様です。

Cannot add or update a child row: a foreign key constraint fails (`test`.`products`, CONSTRAINT `fk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`))
INSERT INTO products (category_id) VALUES (2);

外部キー制約における遅延と`LOCK IN SHARE MODE`

子テーブルへのレコード操作（Insert,Update,Delete)において親テーブルの値をSQL実行前に参照しているわけですから、当然パフォーマンスに影響を与えます。外部キーの利用は必要最小限にとどめ、パフォーマンステストを入念に行う必要があります。TiDBは分散型NewSQLであり、より強くその影響を受けるようです。

では同じ値のInsertを5回連続で繰り返してみます。

INSERT INTO products (category_id) VALUES (1);
INSERT INTO products (category_id) VALUES (1);
INSERT INTO products (category_id) VALUES (1);
INSERT INTO products (category_id) VALUES (1);
INSERT INTO products (category_id) VALUES (1);

それぞれの処理時間は以下の通りで平均21.4msです。初回のノイズを取り除いた平均は15.5msです。

次に以下のクエリーを実行すると平均14.8msでした。

/* Enter "USE {database};" to start exploring your data.
   Press Ctrl + I to try out AI-generated SQL queries or SQL rewrite using Chat2Query. */
USE test;
CREATE TABLE products_nofk (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT
);

INSERT INTO products_nofk (category_id) VALUES (1);
INSERT INTO products_nofk (category_id) VALUES (1);
INSERT INTO products_nofk (category_id) VALUES (1);
INSERT INTO products_nofk (category_id) VALUES (1);
INSERT INTO products_nofk (category_id) VALUES (1);

この例はとてもシンプルですが、テーブルのカラム数やレコード数が増えるにつれその差は顕著になっていきます。

外部キーを参照するレコードの書き込みなどは親テーブルと子テーブルに矛盾が生じないよう都度レコードにロックをかけており、これが遅延の原因となります。同時に発生した同じレコードを参照するSelectクエリーも遅延するためシステム全体の遅延を引き起こします。MySQLではLOCK IN SHARE MODEというものが存在していますがTiDBは現在まだそれをサポートしていません。
LOCK IN SHARE MODEとはデータの更新、削除を防ぐが読み取りは可能とするモードのことです。これによりINSERTと同時に発生するSELECTはロックの影響を受けずデータ読み取りが可能となります。

foreign_key_checks

大量の更新・削除時には、TiDBでは代わりに一時的にforeign_key_checksを使い外部キー制約を外す方法を推奨しています。

SHOW VARIABLES LIKE 'foreign_key_checks';

先ほどの例でいえば以下のような使い方です。

SET foreign_key_checks = 0;
-- その後、子テーブルにデータを挿入
INSERT INTO products (category_id) VALUES (1);
INSERT INTO products (category_id) VALUES (1);
INSERT INTO products (category_id) VALUES (1);
INSERT INTO products (category_id) VALUES (1);
INSERT INTO products (category_id) VALUES (1);
SET foreign_key_checks = 1;