この記事はモチベーションクラウドシリーズアドベントカレンダー2021の17日目の記事です。
はじめに
皆さんが開発/保守/運用しているプロダクト/システムではDBを使っていますか？おそらくほとんどのシステムで使用しているんじゃないでしょうか。
DBはよく作られていて、SQLが書ければ裏側の処理をよく知らずともデータを検索することができてしまいます。
しかし、DBでのクエリ処理の流れを知っておくことは、パフォーマンスの良いプログラムを書くことや障害時の迅速な対応に寄与します。
つまり、フロント/バック/インフラ等の担当領域を問わずエンジニア全員が知っておくべき知識です！
(良いエンジニアとは？PMが今年エンジニアに言われて嬉しかった言葉Best5にも記載がある通り、知見のある分野が広いエンジニアはPMから重宝されるようです。)
そこで、この記事ではDBのselect処理について、実際にコマンドを実行しながら流れを追ってみます。(MySQL@5.7(InnoDB)を使用)
詳細な説明をしない箇所についてもキーワードとして、ワードを記載していくので、もし興味があれば検索してみてください。
(検索については結局「ググり力」とは何なのか、どうやったら伸びるのかも参照)
全体像
処理の全体像としては、
- クエリキャッシュにデータがある場合は、データファイルへのアクセスをせずにキャッシュから結果を返却
- クエリキャッシュにデータがない場合は、構文解析や実行計画策定を経てデータファイルから結果を返却
といった流れです。
サンプルテーブル
例として下記テーブルを使用します。
-- usersテーブル
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 1 | AAA | 2021-12-15 13:23:46 |
| 2 | BBB | 2021-12-15 14:00:31 |
| 3 | CCC | 2021-12-15 14:00:31 |
| 4 | DDD | 2021-12-15 14:00:31 |
| 5 | EEE | 2021-12-15 14:00:31 |
| 6 | FFF | 2021-12-15 14:00:31 |
| 7 | GGG | 2021-12-15 14:00:31 |
| 8 | HHH | 2021-12-15 14:00:31 |
| 9 | III | 2021-12-15 14:00:31 |
| 10 | JJJ | 2021-12-15 14:00:32 |
+----+------+---------------------+
クエリキャッシュ
MySQLでは8.0からクエリキャッシュは廃止されています。
クエリキャッシュとはSELECT結果をキャッシュに保持しておき、以降同じSELECTが行われた際にキャッシュから結果を返却する仕組みです。(MySQLリファレンス)
これによって、後述する構文解析やデータファイルへのアクセスを減らすことができるので、(使い方によっては)性能向上が期待できます。
特徴、注意点等
- 大文字/小文字やスペースの有無等が一致しないと同じクエリとは判定されず、キャッシュは使用されない
-
SELECT * FROM sample_table;とselect * from sample_table;は別のクエリとして扱われる
-
- クエリ内に動的な値(例:
NOW())が入っているとキャッシュは使用されない - 対象テーブルに対して更新処理が行われた場合はキャッシュが破棄される
- 更新頻度が高いテーブルにおいては、「キャッシュする→破棄する」が繰り返され、メンテナンスコストが高くなり性能が落ちる可能性も
例
①まずMySQLのキャッシュの状態を確認します。
mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 0 | -- キャッシュから結果を返却した回数
| Qcache_inserts | 0 | -- 新しくキャッシュに保存した回数
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 0 | -- 結果をキャッシュしているクエリの数
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)
②selectを実行すると、結果がキャッシュされます。(Qcache_insertsの値が1増える)
mysql> select * from users;
-- select結果省略
10 row in set (0.00 sec)
mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1030296 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 | -- select結果がキャッシュされた
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 1 | -- 実行したクエリがキャッシュに保存された
| Qcache_total_blocks | 4 |
+-------------------------+---------+
8 rows in set (0.00 sec)
③再度同じselectを実行すると、結果がキャッシュから返却されます。
mysql> select * from users;
-- select結果省略
10 row in set (0.00 sec)
mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1030296 |
| Qcache_hits | 1 | -- Qcache_hitsの値が増える
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+---------+
8 rows in set (0.00 sec)
④先ほどと同じ結果となるはずのクエリを大文字に変換して実行すると、キャッシュは使われません。
mysql> SELECT * FROM USERS;
-- select結果省略
10 row in set (0.00 sec)
mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1029232 |
| Qcache_hits | 1 |
| Qcache_inserts | 2 | -- 新しくキャッシュされる
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 2 | -- クエリ数の値も増える
| Qcache_total_blocks | 6 |
+-------------------------+---------+
8 rows in set (0.00 sec)
⑤更新処理を行うと、対象テーブルのキャッシュが破棄されます。
mysql> insert into users values(11, 'KKK', now());
Query OK, 1 row affected (0.00 sec)
mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 1 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 0 | -- キャッシュしているクエリ数が減る
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)
関連キーワード
キャッシュヒット率、memcached、Redis
構文解析
- 文法上の誤りがないか、対象テーブルやカラムが存在するかをチェックします。
例
-- 存在しないテーブルを指定するとエラー
mysql> select * from not_exist_table;
ERROR 1146 (42S02): Table 'sample.not_exist_table' doesn't exist
-- 同様に存在しないカラムを指定してもエラー
mysql> select not_exist_column from users;
ERROR 1054 (42S22): Unknown column 'not_exist_column' in 'field list'
関連キーワード
プリペアドステートメント
実行計画
実行計画とはどのようにデータを検索するかを決めたものです。
実行計画は統計情報を元に作成され、MySQLが最適だと考えた計画が採用されます。
explainを使うことで、どのような計画が採用されたかを確認できます。(explainの結果の見方についてはこちらの記事を参照)
今回はどのキー(インデックス)を用いて検索を行っているかを確認してみます。
例
①where句なしの場合
key = nullで、フルスキャンを行っています。
mysql> explain select * from users;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
②id(primary_key)をwhere句に指定した場合
key = PRIMARYであり、primary_keyを用いて検索を行っています。
mysql> explain select * from users where id >= 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 9 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
③インデックスが貼ってあるcreated_atをwhere句に指定した場合
key = created_at_indexであり、インデックスを用いて検索を行っています。
mysql> explain select * from users where created_at > cast('2021-12-15 14:00:00' as datetime);;
+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | created_at_index | NULL | NULL | NULL | 9 | 100.00 | Using where |
+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
関連キーワード
統計情報、オプティマイザトレース、結合アルゴリズム(NLJ等)
データ検索
策定された実行計画をもとに実際にデータファイルに対して検索が行われます。
ちなみにデータファイルはselect @@datadir;を実行することで配置ディレクトリを確認することができます。
MySQL InnoDBの領域管理ではinnodb_rubyというツールを使ってデータファイルを可視化しているのでよければ参照ください。
あとがき
エンジニアとして仕事をしていると、今回記載したクエリ処理の流れに限らず、「裏側の処理を理解していないけどなんとなく使っている」ということは結構あります。
使う技術全てについて完全に理解しなければいけないというわけではないですが、いろいろなことに興味を持って自身の知見を深めていけるといいですよね。
宣伝
弊社のアドベントカレンダーの他の記事もぜひご覧ください！