mixi engineer blog

*** 引っ越しました。最新の情報はこちら → https://medium.com/mixi-developers *** ミクシィ・グループで、実際に開発に携わっているエンジニア達が執筆している公式ブログです。様々なサービスの開発や運用を行っていく際に得た技術情報から採用情報まで、有益な情報を幅広く取り扱っています。

新卒研修の受講レポート~データベース編~

17新卒エンジニアデータベース研修

今回は、XFLAG事業本部 SREグループの清水さん(@isaoshimizu)によるデータベース研修で学んだことについて、新卒エンジニアの左野と坂本がレポートしていきます。

f:id:mixi_engineers:20170824103326j:plain

↑研修中の様子です

研修内容

講義は以下の内容で進んでいきました! - MySQLの基本的な話 - データベースの基本的な話 - インデックス - 負荷対策 - 運用の話 - 演習

今回は研修内容については深く掘り下げませんが、研修を受けて得られた学びと感想について私たちが感じたことを書いていきます!

今年の新卒が使ったことのあるフレームワーク

1位. Ruby on Rails

2位. Sinatra

3位. FuelPHP

いきなり蛇足ですが、研修前に扱ったことがあるフレームワークについて事前にアンケートがありまして、Ruby on Railsが人気のようでした。ORMはActive Recordが人気でした。

照合順序と寿司とビールの話

前半は、MySQLを題材にデータベースの基礎的な部分について学習しました。ACID特性やトランザクション、正規化、インデックスなどです。聞いた話の中で印象に残ったのは、寿司ビール問題と言われているもので、例えばMySQLで設定しているUnicodeの照合順序によっては🍣や🍺などの絵文字が同一扱いになったりならなかったりする問題です。詳しくは以下の記事が参考になります。

MySQL と寿司ビール問題 - かみぽわーる http://blog.kamipo.net/entry/2015/03/23/093052

インデックスの話

わたくし坂本は、過去にMySQLを利用したサービスの開発やISUCONに参加したことがあるのですが、その際、速度向上を狙いインデックスを貼ることがありました。しかし実際にインデックスがどのように作用して速度向上するのか、ということは全く理解せずにインデックスを追加していました。 今回の研修では改めてインデックスの挙動について学ぶことが出来ました。 例えば、「関数や式、否定構文、LIKEではインデックスは効かない(LIKEの前方一致では効く)」「LIMIT OFFSETはとても遅いのでWHEREで絞り込むと良い」などの注意すべき点を教わりました。これまでは、こういった点を知らずにクエリを発行していましたが、今後はどのようなクエリを発行するのか、学んだことを踏まえたインデックス設計を行いたいと思いました。

演習してみた話

最後に、演習として実際に効率の良いクエリ発行ができるか、の確認をしました。 データセットとしては、 https://github.com/datacharmer/test_db 内の employees.sql を利用しました。

演習1

USE employees;
SELECT SQL_NO_CACHE * FROM employees WHERE hire_date = '1985-02-01' AND birth_date = '1963-08-02';

このクエリを実行すると全件スキャンがおこなわれ、検索に時間がかかります。 最も短い時間で検索が行われるように工夫してください。

まずは、このまま実行をするとどうなるのかを確認します。

mysql> SELECT SQL_NO_CACHE * FROM employees WHERE hire_date = '1985-02-01' AND birth_date = '1963-08-02';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  20539 | 1963-08-02 | Poorav     | Gecsei    | M      | 1985-02-01 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.13 sec)

この時点で0.13secかかっており既に遅いということが分かります。実際にどの程度スキャンされているのかを explain を用いて確認します。

mysql> explain SELECT SQL_NO_CACHE * FROM employees WHERE hire_date = '1985-02-01' AND birth_date = '1963-08-02';
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299290 |     1.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

rows の項目を見ると分かるように、30万件近いスキャンが走っていることが分かります。

この場合は、インデックスを追加することでスキャンする数を減らすことができます。 今回はインデックスを hire_date に追加します。

mysql> alter table employees add index hire_date_index(hire_date);
Query OK, 0 rows affected (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

その後、同じクエリを実行すると以下のようになります。

mysql> SELECT SQL_NO_CACHE * FROM employees WHERE hire_date = '1985-02-01' AND birth_date = '1963-08-02';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  20539 | 1963-08-02 | Poorav     | Gecsei    | M      | 1985-02-01 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.01 sec)

最初0.13secかかっていた時間が、0.01secまで短縮できました!explainでスキャン数も確認してみます。

mysql> explain SELECT SQL_NO_CACHE * FROM employees WHERE hire_date = '1985-02-01' AND birth_date = '1963-08-02';
+----+-------------+-----------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ref  | hire_date_index | hire_date_index | 3       | const |   15 |    10.00 | Using where |
+----+-------------+-----------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

30万件近くスキャンしていたのが、15件まで減らすことができました! key を見ると先程設定した hire_date_index が利用されていることも確認できました。

演習2

次の演習は以下のような課題です

USE employees;
SELECT SQL_NO_CACHE * FROM employees WHERE birth_date > '1959-01-01' ORDER BY hire_date LIMIT 5;

上のクエリを実行すると、ファイルソートが発生して検索速度が遅いことが分かります。ファイルソートが起きないように工夫をしてみましょう。

mysql> SELECT SQL_NO_CACHE * FROM employees WHERE birth_date > '1959-01-01' ORDER BY hire_date LIMIT 5;
+--------+------------+-------------+-----------+--------+------------+
| emp_no | birth_date | first_name  | last_name | gender | hire_date  |
+--------+------------+-------------+-----------+--------+------------+
| 111400 | 1959-11-09 | Arie        | Staelin   | M      | 1985-01-01 |
| 110725 | 1961-03-14 | Peternela   | Onuegbe   | F      | 1985-01-01 |
| 111035 | 1962-02-24 | Przemyslawa | Kaelbling | M      | 1985-01-01 |
| 110085 | 1959-10-28 | Ebru        | Alpin     | M      | 1985-01-01 |
|  87761 | 1960-08-19 | Shir        | Munck     | F      | 1985-02-01 |
+--------+------------+-------------+-----------+--------+------------+
5 rows in set (0.14 sec)

実行結果です。0.14 sec とクエリが遅いことが確認できました。EXPLAINを使ってクエリを確認してみましょう。

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM employees WHERE birth_date > '1959-01-01' ORDER BY hire_date LIMIT 5;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299157 |    33.33 | Using where; Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

ここで Extra という項目に注目してみましょう。Using filesort からファイルソートが発生していることが確認できます。これはテーブルにインデックスが存在しないために ORDER BY で指定されたソートを行うためのメモリ使用量が増加しており、一時的な記憶領域としてファイルを利用するためクエリが遅くなります。1つ目の演習と同じように hire_date にインデックスを追加してみましょう。

mysql> alter table employees add index index_name(hire_date);
Query OK, 0 rows affected (0.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

再びクエリを実行してみます。

mysql> SELECT SQL_NO_CACHE * FROM employees WHERE birth_date > '1959-01-01' ORDER BY hire_date LIMIT 5;
+--------+------------+-------------+-----------+--------+------------+
| emp_no | birth_date | first_name  | last_name | gender | hire_date  |
+--------+------------+-------------+-----------+--------+------------+
| 110085 | 1959-10-28 | Ebru        | Alpin     | M      | 1985-01-01 |
| 110725 | 1961-03-14 | Peternela   | Onuegbe   | F      | 1985-01-01 |
| 111035 | 1962-02-24 | Przemyslawa | Kaelbling | M      | 1985-01-01 |
| 111400 | 1959-11-09 | Arie        | Staelin   | M      | 1985-01-01 |
|  20539 | 1963-08-02 | Poorav      | Gecsei    | M      | 1985-02-01 |
+--------+------------+-------------+-----------+--------+------------+
5 rows in set (0.00 sec)

0.00 sec まで改善できました。EXPLAINでファイルソートが起きていないか確認してみましょう。

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM employees WHERE birth_date > '1959-01-01' ORDER BY hire_date LIMIT 5;
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | index_name | 3       | NULL |    5 |    33.33 | Using where |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Using filesort が消えてファイルソートが起きなくなったことを確認できました。

感想

坂本: 今までデータベースについていろいろなアンチパターンや高速化について勉強をしてきましたが、今回の研修を通じて体系的に学べたおかげで点と点が線になり、今後につながる学びが得られたと思います。今後は、業務で役に立つようにさらに勉強を重ねて自分の武器となるようにしていきたいです。

左野: 今回の研修ではデータベースについて知らないことばかりだと痛感しました。データベースの知識は業務で欠かせないものになると思うので、講義資料を読み込んで勉強していきます。