データベース処理の高速化を図る。-Select編-
こんにちわ！
Inagora株式会社バックエンドエンジニアの福田です。
前回はデータベースのInsertの高速化について、書かせてもらったのですが、今回はデータの取得についての高速化について書いていきます。
というのも、先日、データベースの処理において商品情報の取得や受注情報の取得に時間がかかっていた為、データベース・クエリのチューニングを行いました。 その時に行った事を主に書いていきます。
今回のチューニングで行ったのは
①Index付与 ②キャッシュ導入 ③処理全体の見直し
を行いました。
今回は①Index付与をピックアップしていきます。
MySQLのテーブルには参照を速くする為にIndexを付与できるのは周知の事だと思いますが、このIndexもただ闇雲に付与しても当然の事ながら速くはなりません。
システムで用いているSQLがIndexを使用しているのかどうか、使用しているならどのIndexを使用しているのか。
こちらを調査して正しくIndexを付与できれば、データベースアクセスのパフォーマンスが向上します。
それを的確に知るコマンドがありました！
それが EXPLAIN コマンドです。（私は今回の件でこのコマンドを知りましたorz）
このEXPLAINコマンドは実際のSQL文を用いて、そのSQL文がIndexを使用しているのか、どんなアクセスタイプなのかを調査する事ができます。
少し例を挙げてみましょう。
ID、学籍番号、名前を管理する student テーブルがあるとします。
（Indexなどは何も付与していない状態です。）
mysql> SELECT * FROM student; +----+--------+------------------+ | id | number | name | +----+--------+------------------+ | 1 | 3301 | 西住 みほ | | 2 | 3302 | 武部 沙織 | | 3 | 3303 | 五十鈴 華 | | 4 | 3304 | 秋山 優花里 | | 5 | 3305 | 冷泉 麻子 | +----+--------+------------------+
全体のデータはこんな感じ。
こちらから学生番号をwhere文で指定して、select文でデータを取得すると
mysql> SELECT * FROM student WHERE number = '3301'; +----+--------+---------------+ | id | number | name | +----+--------+---------------+ | 1 | 3301 | 西住 みほ | +----+--------+---------------+
このようになります。
このselect文をEXPLAINコマンドで見ていきましょう。
mysql> EXPLAIN SELECT * FROM student WHERE number = '3301'; +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 5 | Using where | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
なんかいろいろでてきます
上記の様に、EXPLAINコマンドを使うと、このSQL文に関する様々な情報が出てきます！
要点を挙げると
・table … 対象テーブル。
・type … 対象テーブルに対して、どのような方法でアクセスしているかを示す。ALLの場合はフルテーブルスキャン。Indexがまったく使われていない状態。
・possible_keys … テーブルのアクセスに利用可能なIndexの候補。
現在、Indexをまったく付与していないので、アクセスタイプはALLとなり、テーブル全体を読み込んでいる状態となります。
この例ではレコード数が少ないので、処理の速さにはほとんど差は無いと思いますが、1万件、2万件のデータをフルスキャンしていたら、非常に重い処理になってしまうでしょう。
これではいかん！という事で、このstudentテーブルにIndexを付与してみましょう！
学籍番号にIndexを付与していきます。
mysql> ALTER TABLE student ADD INDEX number(`number`); Query OK, 0 rows affected (0.08 sec)
Indexを付与した状態でまた改めて同じSQL文をEXPLAINコマンドで見ていきましょう。
mysql> EXPLAIN SELECT * FROM student WHERE number = '3301'; +----+-------------+---------+------+---------------+--------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+--------+---------+-------+------+-------+ | 1 | SIMPLE | student | ref | number | number | 5 | const | 1 | NULL | +----+-------------+---------+------+---------------+--------+---------+-------+------+-------+
typeが先ほどのALLからrefへ変わっていますね。
refはユニークでないIndexを使用して検索を行っている事を意味しています。今回の例では先ほど学籍番号に付与したIndexがこのSQL文で使われている事を意味していますね！
typeに関して、EXPLAINに関してこちらにさらに詳しく記載されています。
MySQLのEXPLAINを徹底解説!!
typeがALLだったり、indexだったりしたらクエリのチューニングを行うのが、おすすめされているみたいです。
今回の記事は以上となります。
EXPLAINはSQL文単位でどのようにアクセスしているのか詳しい情報を知る事ができるので、どこに問題があるのか調査しやすく非常に便利なコマンドという印象でした。
今回の例では挙げませんでしたが、複数のテーブルをJOINしたり、サブクエリを用いたりしていても同様にEXPLAINコマンドでSQL文の調査を行う事ができます。
・参考
