mysql-patterns

MySQL パターン

MySQL のベストプラクティスに関するクイックリファレンスです。詳細なガイダンスについては、mysql-database-reviewer エージェントを使用してください。

有効化するタイミング

• SQL クエリやマイグレーションを作成するとき
• データベーススキーマを設計するとき
• 遅いクエリをトラブルシューティングするとき
• コネクションプーリングを設定するとき
• マルチテナント分離を実装するとき

クイックリファレンス

インデックスチートシート

クエリパターン	インデックスの種類	例
WHERE col = value	B-tree（デフォルト）	CREATE INDEX idx ON t (col)
WHERE col > value	B-tree	CREATE INDEX idx ON t (col)
WHERE a = x AND b > y	複合	CREATE INDEX idx ON t (a, b)
MATCH(...) AGAINST(...)	FULLTEXT	CREATE FULLTEXT INDEX ft ON t (col)
長い文字列のプレフィックス	プレフィックス	CREATE INDEX idx ON t (col(50))
地理データ	SPATIAL	CREATE SPATIAL INDEX idx ON t (col)

データ型クイックリファレンス

ユースケース	正しい型	避けるべきもの
ID	bigint unsigned	int、PK としてのランダム UUID
ビジネスコード	varchar(64)	char、text
文字列	varchar(n)	短い文字列に対する text
タイムスタンプ	datetime	timestamp（2038 年問題）
金額	decimal(10,2)	float、double
フラグ	tinyint	varchar、boolean リテラル
ステータス	tinyint	enum（変更が困難）
JSON データ	json	構造化データに対する text

一般的なパターン

複合インデックスの順序:

-- Equality columns first, then range columns
CREATE INDEX idx_status_created ON t_order (status, created_at);
-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'
-- Does NOT work for: WHERE created_at > '2024-01-01' alone

プレフィックスインデックス（長い文字列）:

CREATE INDEX idx_url ON t_page (url(100));
-- Index only first 100 characters

生成列 + インデックス（JSON）:

ALTER TABLE t_product
ADD COLUMN brand varchar(100) GENERATED ALWAYS AS (attributes->>'$.brand') STORED;
CREATE INDEX idx_brand ON t_product (brand);

UPSERT（ON DUPLICATE KEY）:

INSERT INTO t_setting (user_code, `key`, `value`)
VALUES ('u123', 'theme', 'dark')
ON DUPLICATE KEY UPDATE
  `value` = VALUES(`value`),
  updated_at = NOW();

カーソルページネーション:

SELECT * FROM t_product WHERE id > ? ORDER BY id LIMIT 20;
-- O(1) vs OFFSET which is O(n)

キュー処理（MySQL 8.0+）:

START TRANSACTION;
SELECT * FROM t_job
WHERE status = 'pending'
ORDER BY created_at LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Process job...
UPDATE t_job SET status = 'processing' WHERE id = ?;
COMMIT;

バッチ挿入:

INSERT INTO t_event (user_code, action) VALUES
  ('u1', 'click'),
  ('u2', 'view'),
  ('u3', 'click');
-- 1 round trip instead of 3

アンチパターンの検出

-- Find tables without primary key
SELECT t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.key_column_usage k
  ON t.table_schema = k.table_schema
  AND t.table_name = k.table_name
  AND k.constraint_name = 'PRIMARY'
WHERE t.table_schema = DATABASE()
  AND k.constraint_name IS NULL
  AND t.table_type = 'BASE TABLE';

-- Find slow queries (performance_schema)
SELECT DIGEST_TEXT, COUNT_STAR,
  ROUND(AVG_TIMER_WAIT/1000000000, 2) as avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT > 100000000  -- > 100ms
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

-- Find unused indexes (sys schema)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = DATABASE();

-- Find redundant indexes
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = DATABASE();

-- Check table fragmentation
SELECT table_name, data_free,
  ROUND(data_free/(data_length+index_length+data_free)*100, 2) as frag_pct
FROM information_schema.tables
WHERE table_schema = DATABASE() AND data_free > 1000000
ORDER BY data_free DESC;

設定テンプレート

-- Connection limits
SET GLOBAL max_connections = 200;
SET GLOBAL max_user_connections = 50;

-- Timeouts
SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 600;
SET GLOBAL max_execution_time = 30000;  -- 30s query timeout (MySQL 5.7.8+)

-- Slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- InnoDB settings (my.cnf recommended)
-- innodb_buffer_pool_size = 70% of RAM
-- innodb_log_file_size = 256M
-- innodb_flush_log_at_trx_commit = 1

テーブルテンプレート

CREATE TABLE `t_example` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'PK',
  `example_code` varchar(64) NOT NULL COMMENT 'Business code (UUID)',

  -- Business fields here
  `status` tinyint NOT NULL DEFAULT 1 COMMENT '0-inactive, 1-active',

  -- Audit fields (5 fields)
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
  `created_by` varchar(64) NOT NULL DEFAULT '' COMMENT 'Creator (user_code)',
  `updated_by` varchar(64) NOT NULL DEFAULT '' COMMENT 'Modifier (user_code)',
  `deleted_at` datetime DEFAULT NULL COMMENT 'Soft delete marker',

  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_example_code` (`example_code`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

EXPLAIN 分析

EXPLAIN ANALYZE SELECT * FROM t_order WHERE user_code = 'u123';

指標	問題	解決策
type: ALL	フルテーブルスキャン	インデックスを追加
type: index	フルインデックススキャン	WHERE を確認
Using filesort	ソートがインデックス化されていない	ORDER BY インデックスを追加
Using temporary	一時テーブル	GROUP BY を最適化
高い rows	選択性が低い	条件を見直す

関連

• エージェント: mysql-database-reviewer - データベースレビューの完全なワークフロー

---

MySQL 5.7+ / 8.0+ のクイックリファレンス