SQL Glossary

SQL 命令コマンド参考さんこうマニュアル。DML/DDL/DCL/TCL 命令コマンド、実行順序じっこうじゅんじょ、索引類型インデックスるいけいと MySQL 特性とくせいを網羅もうら。

SQL Commands

DML (Data Manipulation Language)

資料操作言語データそうさげんご、資料データの查詢クエリと修正しゅうせいに使用しよう。

DDL (Data Definition Language)

資料定義言語データていぎげんご、資料庫データベース構造こうぞうの定義ていぎに使用しよう。

DCL (Data Control Language)

資料制御言語データせいぎょげんご、ユーザー権限けんげんの管理かんりに使用しよう。

TCL (Transaction Control Language)

交易制御言語トランザクションせいぎょげんご、通常つうじょう DML 操作そうさと一緒いっしょに使用しよう。

Execution Order

SQL 查詢クエリの実行順序じっこうじゅんじょ：

  flowchart LR
    FROM --> JOIN --> ON --> WHERE --> GROUP_BY --> HAVING --> SELECT --> ORDER_BY --> LIMIT

Query Execution Plans

SARGABLE Query

SARGABLE = Search ARGument ABLE（索引インデックスで検索けんさく可能かのう）

SARGABLE Query

WHERE order_date >= '2024-01-01'

Non-SARGABLE Query

WHERE YEAR(order_date) >= 2024

SARGABLE Query を書く原則

• WHERE 句くで索引インデックス欄位フィールドに函数かんすうや計算けいさんを使用しようしない
• 可能かのうな限かぎり直接ちょくせつ比較ひかくを使用しようし、欄位フィールドを函数かんすうで包つつまない
• 欄位フィールドに函数かんすうを使用しようする必要ひつようがある場合ばあい、computed column や function-based index の作成さくせいを検討けんとう

Index Structure

B+Tree

MySQL で最もっとも一般的いっぱんてきな索引インデックス構造こうぞう、範囲はんい查詢クエリとソートをサポート。

Full-Text

全文索引ぜんぶんさくいん、テキスト検索けんさくに使用しよう。

Hash

• Time Complexity: O(1)
• 順序性じゅんじょせいがなく、ソートとグループ化かに使用しようできない
• 正確せいかくな検索けんさくのみをサポート、部分ぶぶん検索けんさくと範囲はんい検索けんさくには使用しようできない

R-Tree

• 空間くうかん資料データ索引インデックス
• 地理ちり資料データ保存ほぞんに使用しよう可能かのう
• すべての次元じげんから資料データを索引インデックスし、任意にんいの次元じげん組合くみあわせ查詢クエリが可能かのう
• GIS 関連かんれん函数かんすうで資料データを維持いじする必要ひつようがある

Index Types

Primary Key Index

Unique Index

Regular Index

Composite Index

Full-Text Index

Covering Index

Spatial Index

Index Pros & Cons

Pros

1. サーバーがスキャンする必要ひつようのある資料行数データぎょうすうを大幅おおはばに削減さくげん
2. サーバーがソートとグループ化かを回避かいひし、Temporary Table の作成さくせいが不要ふよう（B+Tree 索引インデックスは順序じゅんじょ付づき、ORDER BY と GROUP BY に使用しよう可能かのう）
3. ランダム I/O を順次じゅんじ I/O に変換へんかん（B+Tree 索引インデックスは隣接りんせつ資料データを一緒いっしょに保存ほぞん）

Cons

1. 索引インデックスは追加ついかの保存空間ほぞんくうかんが必要ひつよう
2. UPDATE、INSERT、DELETE 操作そうさで索引インデックスも維持いじする必要ひつようがあり、性能せいのう低下ていかを招まねく
3. 小ちいさい Table では、全表ぜんぴょうスキャンの方ほうが Index 使用しようより速はやい場合ばあいがある
4. 選択性せんたくせいが低ひくい欄位フィールドには不向ふむき：欄位フィールドの値あたいが均等きんとうに分布ぶんぷしている場合ばあい、Index 効果こうかが悪わるい

Use Cases

• 非常ひじょうに小ちいさいテーブル：単純たんじゅんな全表ぜんぴょうスキャンの方ほうが効率的こうりつてき
• 中ちゅう〜大だいテーブル：索引インデックスは非常ひじょうに効果的こうかてき
• 非常ひじょうに大おおきいテーブル：索引インデックスの構築こうちくと維持いじコストが増加ぞうか、分区パーティション技術ぎじゅつが必要ひつよう

Index Invalid

以下いかの状況じょうきょうで索引インデックスが無効むこうになる：

• != または <> は索引インデックス無効むこう、全表ぜんぴょうスキャンになる
• 類型タイプ不一致ふいっち：欄位フィールドが varchar だが查詢クエリで int を使用しよう（暗黙的あんもくてき類型変換タイプへんかん）
• 索引インデックス欄位フィールドに count、sum などの函数かんすう操作そうさを使用しよう
• 模糊あいまい検索けんさくで文字列もじれつ前綴プレフィックスも模糊あいまいの場合ばあい（LIKE '%xxx'）
• 聯合索引ふくごうさくいん（Compound Indexes）查詢クエリ条件じょうけんが最左さいさマッチ原則げんそくを満みたさない
• Compound Indexes で、明確めいかくな欄位フィールド查詢クエリを * の代かわりに使用しようすると、覆蓋索引カバリングインデックスが使つかえる可能性かのうせいがある

InnoDB

サポートする索引構造インデックスこうぞう：B+Tree、Hash、Full-Text

Isolation Level

隔離層級かくりそうきゅうは資料庫データベース管理かんりシステムで交易トランザクション間かんの隔離程度かくりていどを設定せっていするオプション。

MyISAM

サポートする索引構造インデックスこうぞう：Full-Text、R-Tree

• Inverted index を使用しよう
• Transaction と FK をサポートしない
• 主おもに LIKE '%xxx%' の効率こうりつが低ひくい問題もんだいを代替だいたいするために使用しよう

MySQL Monitor

接続プール監視

-- 最大許可接続数を表示
SHOW VARIABLES LIKE 'max_connections';

-- 現在使用中の接続数を表示
SHOW STATUS LIKE 'Threads_connected';

-- 履歴最大接続数を表示
SHOW STATUS LIKE 'Max_used_connections';

-- 接続使用率を計算
SELECT
  ROUND(100 * (A.variable_value / B.variable_value), 2) AS connection_usage_percentage
FROM
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Threads_connected') A,
  (SELECT variable_value FROM performance_schema.global_variables WHERE variable_name = 'max_connections') B;

キャッシュ監視

-- InnoDB Buffer Pool 設定を表示
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- キャッシュヒット率を計算
SELECT
  ROUND(100 * (1 - (A.variable_value / B.variable_value)), 2) AS buffer_pool_hit_ratio
FROM
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') A,
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests') B;

クエリ性能監視

-- クエリ実行計画を表示
EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

-- スロークエリログが有効かを確認
SHOW VARIABLES LIKE 'slow_query_log';

-- 実行時間が最も長い SQL 文を查詢
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

ディスク容量監視

-- すべてのデータベースのサイズを查詢
SELECT table_schema AS "Database",
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables
GROUP BY table_schema;

-- 単一テーブルのサイズを查詢
SELECT table_name AS "Table",
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC
LIMIT 10;

トランザクションとロック監視

-- 現在の InnoDB トランザクションを表示
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

-- InnoDB ロック状態を表示
SHOW ENGINE INNODB STATUS;

接続管理

-- 特定の接続を手動で閉じる
KILL thread_id;

-- タイムアウト接続を一括で閉じる（KILL コマンドを生成）
SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist
WHERE command='Sleep' AND time > 300;

ALTER ALGORITHM

ALGORITHM=INPLACE に適する場合

ALGORITHM=INPLACE に適さない場合

Collate