😊

MySQL DB レビューで使用しているガイドライン

2024/02/16に公開

今いる会社ではDBにMySQLを使用しているのですが、その使い方にはどうしても開発者やチームによってゆらぎがありますし、あまり効率的でない使われ方もするケースが多いです。

その辺を是正するために、ある程度指針となるようなガイドラインを作成し、社内に展開したりしています。
この内容を基に、データベース関連の機能追加が行われた際の DB レビューも実施しています。
その内容を公開することでどの程度有益なのかわかりませんが、一部の内容をこちらにも投稿しておこうと思います。

会社固有の事情に関わる内容もそれなりに多いので、それらは削っています。記載されているのは実際のガイドラインのごく一部のスナップショットの内容になります。

データベース・テーブル設計

テーブルのPKにはサロゲートキーを使用する

原則として、テーブルの Primary Key にはサロゲートキーを使用する。
サロゲートキーは　unsigned value で定義し、自動的にインクリメントされるものを使用する

CREATE TABLE `sada` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
(snip.)
PRIMARY KEY (`id`),
) ENGINE=InnoDB

サロゲートキーのデータ型については、扱うデータの件数の見積もりに従い決定する

データ型	範囲	補足
int	0 ~ 4294967295	感覚的に、1年で１億レコードを超えるようなテーブルの場合、bigint にしておいたほうが無難かもしれない
bigint	0 ~ 18446744073709551615

PK を自然数のサロゲートキーにしたい理由は以下

レコードを一意に表現するカラムが自動的に定まる
- サロゲートキーを用意しない場合、データの一意性をどのように表現するかについての詳細な設計が必要
id 値がある程度時系列性を保証する。作成日などの情報を持たないテーブルでも時系列順にソートすることが容易
インデックス追加効率・検索効率が良い
- UUIDやハッシュ値などを PK に使用すると、MySQL としてパフォーマンス効率が高くならない可能性がある
- https://techblog.raccoon.ne.jp/archives/1627262796.html

サロゲートキーによる PK だけでレコードが一意に定まらないケース場合は、レコードのユニーク制約については別途設計と定義が必要。

カラムには最適なデータ型を使用する

基本原則としては以下

データ型はサイズが小さいものの方が良い (Smaller is usually better)
シンプルなデータ型を使用する (Simple is good)
NULLはできるだけ排除する (Avoid NULL if possible)

整数型について

事前にそのカラムの値の取りうる範囲を設計した上で、 必要最小限 のデータ型を使用する。

データ型	signed / unsigned	data size (byte)	最小値	最大値
TINYINT	signed	1	-128	127
	unsigned		0	255
SMALLINT	signed	2	-32768	32767
	unsigned		0	65535
MEDIUMINT	signed	3	-8388608	8388607
	unsigned		0	16777215
INT	signed	4	-2147483648	2147483647
	unsigned		0	4294967295
BIGINT	signed	8	-9223372036854775808	9223372036854775807
	unsigned		0	18446744073709551615

たとえば、 ***_flg のような、カーディナリティが極めて少ないことが想定されるデータ型に INT などを使用してはいけない。この場合は TINYINT などの使用を推奨。

逆に、データの種類によって、 INT の扱う範囲を軽く超越するデータを扱うことも少なくないため、必要に応じて BIGINT なども使用する。ただし、安全策で何でも BIGINT にする、という方針は許容しない。

固定小数点、浮動小数点について

小数点を扱う場合、以下の基準でデータ型を選択する

usecase	choise	types
誤差が許されない	固定小数点	DECIMAL, NUMERIC
多少の誤差が許される。データサイズを節約したい	浮動小数点	FLOAT, DUOBLE

整数値を扱うだけのカラムに固定小数点・浮動小数点は使用しない（過剰にデータサイズが大きくなる）

また固定小数点・浮動小数点は数値処理のためにいくつかの関数が用意されている(ROUND()等)が、基本的に処理が遅いので注意する。アプリケーションサイドで実施しても差し支えない処理については DB ではなくアプリサイドでの実行も検討する。

実際の値をN桁倍し、値的には整数値に変換し、BIGINT などのカラムに格納する、という高速化手法が良く知られているが、こちらの手法の採用は積極的には推奨しない（直感的ではなく、実装の複雑度も増すため）。

文字列型について

ここでは VARCHAR、CHAR の違いについて（TEXT は BLOB 型）

type	pros	cons
VARCHAR	データサイズを節約できる可能性がある	フラグメントが起こりやすい。検索・データ追加コストが若干高い
CHAR	VARCHARより効率的にデータ操作出来る可能性	常に固定長のサイズを確保するためデータサイズの使用効率は良くない

現状、VARCHAR / CHAR の違いによるパフォーマンスの差異はそこまで気にする事はないため、無理して CHAR 型の使用をする必要はない

VARBINARY / BINARY を使用することにより　SQL の性能が向上する可能性があるが、こちらも無理に採用をする必要はない

VARCHAR と TEXT

VARCHAR は1レコード内にデータの実体がすべて保存されますが、TEXT はポインタが保存され、実体は別領域に保存される。そのため、仕組み上 TEXT は VARCHAR に比べて検索効率が落ちる
そもそも VARCHAR と TEXT は仕組みと制約が異なる、ということは必ず頭に入れておく必要がある

それでも大規模なテキストデータを保存したい、という用途にのみ TEXT は使用するようにする

MySQL 5.7 では VARCHAR の長さは 0~65535　まで指定できるため、一般的な文字列については VARCHAR を採用するようにする
※ VARCHAR が 3072 byte より長いとそのままでは Index 作成ができない、という制約があるので注意。 3072 byte を超える長さの VARCHAR は UNIQE KEY には出来ない。検索用の Index の場合はプリフィックスインデックスで作成する必要がある
※ TEXT 型の場合も、インデックスの作成の際に プリフィックスインデックス で作成する必要がある、という制約がある

BLOB、TEXT型について

他のデータ型では取り扱えない大きなサイズのデータを取り扱う際にのみ使用する。
主な目的はデータの保存用途で、BLOB型のカラムを検索条件に加えることは基本NG。

SELECT 文を実行する際に、以下の観点について気をつける。

WHERE 句、ORDER BY 句の対象には基本加えない
- 既存のデータベース・SQL で検索対象に含まれている場合は、プリフィックスインデックスとしてインデックスを付与する
- https://dev.mysql.com/doc/refman/5.6/ja/column-indexes.html
SELECT * FROM ができるだけ発生しないようにする
- BLOB、TEXT のデータが本当に必要な時にだけ、値を取得するようにする

日付型、時間型について

日付、時間関連のデータ型を使用する際は、以下について整理をしてください。

扱うデータの範囲
- 年月日 だけで十分。もしくは時分秒 まで保存する必要がある、等
timezone, offset 情報を保持する必要があるか。
- ある場合は　TIMESTAMP 型が必須
- 正確には TIMESTAMP 型が timezone を保持しているわけではない (UTC時間に変換して保存してくれる)

usecase	data type	data size (byte)	補足
年だけで良い	YEAR	1
時分秒だけで良い	TIME	3
年月日だけで良い	DATE	3
年月日時分秒。timezone 考慮不要	DATETIME	5 バイト + 小数秒ストレージ
timezone の考慮が必要	TIMESTAMP	4 バイト + 小数秒ストレージ	1970-01-01 00:00:01~2038-01-19 03:14:07 しか扱えない

取り扱うサービスについて、少しでも国際化対応の余地がある場合は、日付関連のデータ型は TIMESTAMP 型を採用するのが無難です。ただし、扱う日付の範囲が狭いため設計時に注意が必要です。

もしくは、保存する日付は必ず UTC であるという前提で DATETIME 型を使用する、というやり事もありえます。この場合は開発に際して規約の整備や、アプリケーションコード上での実装などが必要となります。

INT/BIGINT 型のカラムに unixtime を格納する方法について

なお、TIMESTAMP 型はデータサイズを多く使うこともあるため、データサイズの削減のために unixtime の値 (e.g. 1670561559) を INT 型カラムに保存し、unixtime からの日付情報の変換はアプリケーション側で行う、というテクニックもよく知られています。
この方式だとタイムゾーンの問題にも直面することはなく、INT ではなく BIGINT にすれば TIMESTAMP型における扱える範囲の少なさ・2038年問題に直面することもなくなるため、比較的ロバストな方式と思われます。

システムの開発初期からこの方針で運用出来る場合は個人的には推奨したり方式ではありますが、SQL で実行する時の可読性が少し落ちるため、その点は注意が必要です。

ENUM型について

データベースの取りうる値を厳格に規定できるため、誤ったデータが当該カラムに挿入されることを防ぐことはできる。

ただ、値の変更を行うために都度 migration が必要になるため、基本的には使用は推奨しない。
CHAR, VARCHAR で代替をする

JSON型について

JSON操作用の関数を用いることで、JSON 内の特定の値の検索などが行えるが、パフォーマンスが基本的に悪いのでオンライン処理などで検索対象に含めることはNG

取り扱いの注意点については BLOB とほぼ同様

NULL の扱いについて

基本的には、カラムの値に NULL を許容することは推奨されない。
基本的に、各カラムには NOT NULL 制約を付与し、デフォルト値が必要なのであれば適切な値を設定することを推奨。

ただし、NOT NULL 制約を付与するためだけに、アプリケーションとして意味の無いデフォルト値を設定することは推奨されない。
たとえば以下のようなケースの場合、本当の意味で "0000-00-00" を表したいときと、値が未設定の状態、の区別がつかない。

CREATE TABLE ... (
    dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
)

カラムとして 「未設定の値」 をどうしても表現したい場合は、NULL を使用する事を極端に恐れる必要はない。

ただし、以下の点に注意。

NULL は検索効率が悪い
- MySQL は NULL も Index 化できるが、B-Tree 上すべての NULL 値は同じ Tree 近辺に集約されてしまうため、 NULL を持つレコードが多くなればなるほど検索効率が落ちる
NULL or NOT NULL で表現しない方が適切なケースが多い
- たとえば deleted_at IS NULL で未削除、deleted_at IS NOT NULL で削除済みの判定を行うのは、SQL 上効率が悪い。理由は上述。
- この場合、削除された情報を管理するテーブルを用意し、そちらのレコード有無で検索した方がずっと効率は良くなる
  - deleted_at のようなカラムは、あくまで削除されたイベントの発生日時の管理、のためだけに使用

インデックス設計

MySQL で使用できるインデックスの種類、仕組みについてはあらかじめ理解をしておく

(gihyoのサイトから引用)

ハッシュインデックスは MySQL では MEMORY Storage でしか使えないので、基本的に B-Tree しか使えない、という風に理解する

Index Basis

すべての参照系の SQL について、EXPLAIN した結果 Index が使用されない SQL は基本的に NG。SQL 追加時などに、必ず当該 SQL が Index を使用していることを確認する

Clustered Index

別名、Primary Key。

MySQL の内部構造的に、PK を用いた検索の方が高速に動作するため、使用が可能な場合は PK を検索条件にすることが推奨される。

Prefix Index

当該カラムの先頭N文字のみインデックスの対象とする方法

CREATE TABLE ... (
    name VARCHAR(2048) NOT NULL DEFAULT 'sada masashi',
    INDEX idx_name (name(200))
)

上記だと、name カラムの先頭 200 byte のみがインデックス対象になる。

大きなサイズのカラムに対して、やむを得ず WHERE 句の検索条件となり、やむを得ず Index を貼りたい時に使用する。
が原則として、使用は NG。

とくに、 UNIQUE Key についても Prefix Index が使用できてしまうが、重大な一意性違反の問題に直面することになるため、UNIQUE Key での使用は絶対に NG.

Multicolumn Index

WHERE ならびに ORDER BY 句に指定する条件が複数ある場合は、効率化のために Multicolumn Index を採用する。

以下の特性には注意

MySQL は検索時に一つの Index しか使用できない
- 一度に複数の Index を組み合わせて検索、ということができない
- 検索条件が複数ある SQL に Index を適用しようとした場合、必要な Index 定義をすべて包含した一つの Multicolumn Index が必要となる
検索条件に OR が用いられている場合、Index / Multicolumn Index にしても有効に機能しないことがある
- OR の内部的に buffering, sorting, merging など複雑な処理が必要になるため
- 基本的に検索条件に OR を使うことは推奨しない

また、Multicolumn Index を使用すると、他の Index と定義が重複することがある

CREATE TABLE ... (
    name VARCHAR(256) NOT NULL DEFAULT 'sada masashi',
    age INT NOT NULL DEFAULT '70',
    INDEX idx_name (name),
    INDEX idx_name_age (name, age)
)

という定義がされている場合、 idx_name_age の内容が idx_name の内容を包含しているため、 MySQL 内部で idx_name が使われることは基本無いと思われる。Multicolumn Index を用いる場合は、他のインデックスと重複していないかについてはチェックが必要。

実際に稼働しているシステムで、Index が冗長で同じような定義をもつものが存在しているか否かを確認したい場合は、 sys.schema_redundant_indexes を見ることで確認できる

Full-text Index

設定することで、MySQL の特定カラムに対して全文検索を実施することができる。

MySQL では、文字の分かち書きは N-Gram しか使用できない。Elasticsearch などのように形態素解析の結果などを用いて単語の分かち書きを行う、ということはできない。また Stop words のきめ細かなチューニングなども行うことはできない。

※ N-Gramモデル: 「東京都」 というデータに対して、 「東京」 も 「京都」 もヒットするモデル。本来の検索語の意図するものと全然異なる文章がヒットする可能性もあるが、検索の漏れは少ないモデル。
「F値」の文脈で言うと、Precision（適合率）は低くなるが、Recall（再現率）は高くなるモデル

Elasticsearch など専用の全文検索エンジンを使う時と比べて、MySQL の Full-text Index は性能面でも機能面でも劣るため、基本的には採用はNG