DBチューニングできますか?

あらすじ

面接に行くとDBに関しての知識はどれぐらいあるのかを問われる事が多々あります。
それは良いのですがその質問がざっくりとしている事が多々あります。たとえば次のように広い質問をうけたりします。

「DBのチューニングはできますか?」

何を基準にした質問なのか、何を基準にして答えればよいのかいつも困るので事前に回答をまとめてから面接に行くとしようという試みです。*1

そもそもDBのチューニングって何?

DBのチューニングできますか?と言われて困ったのは、「相手が期待している回答は何?」という事です。

前提が異なる両者が会話をしても収束しないのでまずは言葉の定義を明確にしたいと思います。

ちなみにこの質問に「ファイルに保存されている情報をより少ない手順で引き出せるか、という質問なのでしょうか?」と逆に質問したところ相手も困惑していました。

今思えば「WEBアプリ作って運営してるとしょっちゅうDBが問題引き起こして困っているんですけど何とかしてくれますか?」という趣旨の質問だったのかもしれません。

とりあえず今回の記事では面接官「この人なら、ひょっとして何とかしてくれるかも!!」と思ってもらえるように理論武装してみようと思います。

これがこの記事の趣旨です。

高速なSQLは書けますか?

これも実際に言われた事があります。この時は次のように答えました。

私「人並みに書けるつもりです…」

この時、私の頭の中はこうなっていました。

私「(必要な情報が格納されているファイルのシークポイントを迅速に発見できるようにインデックスを使ってるっていうのは理解してるつもりではある…)」
私「(そのようにSQLを発行しているかどうかはだいたいイメージできるし実行計画はexplainつけてあげればいいわけだし…)」
私「(まあ何にも分かっていない人が書くよりは妥当なSQL書くとは思うけれど…)」
私「(要するに)人並みに書けるつもりです」

ただし、このやりとりでは誰も得しませんでした。

これは完全にこちらの失敗でした。

「WEBアプリ作って運営してるとしょっちゅうDBが問題引き起こして困っている」人の悩みを
「この人なら、ひょっとして何とかしてくれるかも!!」というふうにを解消していません。

というわけでSQLをおさらい

求めている結果を得るために、最も効率よくSQLを書く、これもDBチューニングの一種だと思います。
というわけで高速なSQLと低速なSQLの違いを説明できるように理論武装してみます。

まずSQLを発行してから、実際に実行されるまでの手順は以下のとおりです。

  • クライアントがSQL文をサーバーに送信する
  • サーバーがクエリキャッシュをチェック。キャッシュヒットした場合はそれを返す。キャッシュミスした場合は次のステップへ
  • サーバーがSQL文を解析、それからどのように処理すれば高速なのかをMySQLが考えて、クエリ実行プランを作成。
  • クエリ実行プランを実行
  • サーバーが結果をクライアントへ返す。

※実践ハイパフォーマンスMySQL第2版の4.3区襟の実行の基礎より

親切なMySQLサーバは、受け取ったSQLを効率よく処理する方法を考え、その後に実行します。

明るい実行計画

MySQLはクエリを実行する前にどのようにファイルアクセスすればよいのかを考える特性があります。

たとえば目の前に100個の箱が並んでいて、その中に1〜1000までの数字がひとつだけ書かれた紙が入っています。300という数字が書かれた箱を探して下さい。
という指令がはいったとします。

さて、あなたはどうやって探しますか?私は全部の箱を開きます。*2

箱は横一列に並んでいて、左側の箱は右側の箱よりも小さい数字が必ず書かれている、という約束事があれば真ん中の箱、つまり左から50番目を開きます。
もし345という数字が入っていれば次は左から25番目を開くでしょう*3

というわけでデータがどのように格納されているのか、知るものと知らざるものでは仕事量が異なります。
なのでMySQLサーバーはクエリを解析し、必要な情報を取得する作業を実行する前に、どのように取得するのかを計画します。

実験準備

テスト用のデータを準備します。

% curl -O http://downloads.mysql.com/docs/sakila-db.tar.gz
% tar zxvf sakila-db.tar.gz 
% mysqladmin -uroot -p create sakila_db
% mysql < sakila-db/sakila-schema.sql -u root -p
% mysql < sakila-db/sakila-data.sql -u root -p

filmテーブルのschema

mysql> show create table film\G
*************************** 1. row ***************************
       Table: film
Create Table: CREATE TABLE `film` (
  `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `release_year` year(4) DEFAULT NULL,
  `language_id` tinyint(3) unsigned NOT NULL,
  `original_language_id` tinyint(3) unsigned DEFAULT NULL,
  `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
  `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
  `length` smallint(5) unsigned DEFAULT NULL,
  `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
  `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
  `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`film_id`),
  KEY `idx_title` (`title`),
  KEY `idx_fk_language_id` (`language_id`),
  KEY `idx_fk_original_language_id` (`original_language_id`),
  CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

explainを使うとオプティマイザがクエリからどのような方法を使って情報を引き出そうとしているかを知る事ができます。
filmテーブルから全てのカラムを取得する場合と、indexが生成されているカラムだけを取得する場合を比較してみます。

ちなみにInnoDBです。

explainからカバリングインデックスを使っているかを調べる

「いつそんなクエリ使うんだ」という気もしますが、次のクエリをご覧下さい。

mysql> explain select film_id from film where film_id=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: const
         rows: 1
        Extra: Using index
1 row in set (0.00 sec)

次のクエリは良くあるパターンかな。

mysql> explain select * from film where film_id=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

このSQLの共通点はtype列がconstになっている点です。PRIMARY KEY/UNIQUEキーを条件に指定しているとマッチするレコードが1つしかない上に順番が規則的なので高速に検索する事が可能です。

異なっている点はExtra列です。

軽くおさらいすると、indexedされているカラムを持つテーブルは、テーブルの情報を格納しているファイルとは別にインデックスファイルを持っています。
そしてこのインデックスファイルを先に調べてからテーブル本体の情報を格納しているファイルを見に行きます。

ただし、最初のクエリはインデックスファイルを見た時点でもうid:1という情報を知っているのでわざわざテーブル本体の情報を読む必要はありません。
この時Extra: Using indexとなり、これをカバリングインデックスと呼んだりします。

カバリングインデックスを活用してみる

titleを検索条件に指定しておいて、マッチするfilm一覧を表示したいケースを考えます。無難に次のようなクエリを発行してみます。*4

mysql> explain select * from film where title LIKE 'J%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: range
possible_keys: idx_title
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 20
        Extra: Using where
1 row in set (0.00 sec)

もしカバリングインデックスを知っていて、かつその一覧表示で必要なのはtitleと詳細画面へ遷移するためのfilm_idだけだとすると、次のようなクエリを思いつく事ができるでしょう。

mysql> explain select film_id, title from film where title LIKE 'J%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: range
possible_keys: idx_title
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 20
        Extra: Using where; Using index
1 row in set (0.00 sec)

まあ…ちょっとだけ速くなるんじゃないですかねぇ。

explainからインデックスが使えていないクエリに気づいてみる

できれば改善したいのがtype: ALLです。actorテーブルは名字にインデックスをつけているのですが名前にはインデックスがついていません。

mysql> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

名前で検索してみます。

mysql>  explain select * from actor where first_name LIKE 'J%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra: Using where
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'last_query_cost'\G
*************************** 1. row ***************************
Variable_name: Last_query_cost
        Value: 40.999000
1 row in set (0.00 sec)

名字で検索したほうが速いです。

mysql>  explain select * from actor where last_name LIKE 'J%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: range
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: NULL
         rows: 7
        Extra: Using where
1 row in set (0.01 sec)

mysql> SHOW STATUS LIKE 'last_query_cost'\G
*************************** 1. row ***************************
Variable_name: Last_query_cost
        Value: 10.809000
1 row in set (0.00 sec)

ちなみにこのLast_query_costセッション変数はオプティマイザがクエリを実行するために約10ページのランダムデータを読み取るだろうという見積もりだそうです。

type: Allは最もコストがかかっている検索方法です。この場合はインデックスが適切かどうか、
必要ないカラムを読んでいないか、要件を満たすためにどうしてもそのクエリでないと駄目なのかと言った事を検討するとよいかもしれません。

インデックスは絶対ではない

次のようなクエリでは仮にsexカラムにインデックスを使っていても、MySQLはtype: ALLのフルテーブルスキャンを採用する可能性が高いです。

select * from Employee where sex = 'male';

列のデータ種類が、テーブルのレコード数に比べて少ない状態をカーディナリティが低いといいます。
この状態だとMySQLサーバーは「フルテーブルスキャンのほうが速い」と判断します。

MySQLサーバ

今日のまとめ

インデックスについて少し学習しておくと、SQLパーサがどのようなクエリ実行計画をたてるのかをある程度予測する事ができます。
それはexplain句を使う事によってある程度SQLパーサがどうしようとしているのかを知る事ができます。

「速いSQLを書けます」と答えるかはともかく
「処理に時間がかかっているクエリをMySQLがどのような実行計画を立てているかを調べ、そこから適切なインデックスが使用されているのか、無駄となるクエリを発行していないかを調査できます」
ぐらいは答えられるかな?

これだけでDBチューニングができるわけではないですが「人並みに書けるつもりです…」よりは大分ましになった気がします。^^

*1:仕事は準備で8割が決まるんだぜ

*2:300という数字が何個あるのかわからないからです

*3:ご存知2分検索です

*4:実用的な例思いつかないので分かりやすい例って事で