tom__bo’s Blog

MySQL!! MySQL!! @tom__bo

MySQLのquery review helperを作ってみた

せっかくのGWなので、おもちゃを作りました。 Query Review Helperです。

github.com

背景

最近MySQLで実行されるクエリをレビューするタスクが異常に多くなってきました。 開発者の全員がindexやjoin, partition, limit offsetなどなどのハマりどころを知っているというのが理想ではありますが、そうも行きません。
大体EXPLAINをかけてtable scanになっていないか、sortやlimit offsetで劇的に重いクエリがないか確認すれば、すぐに問題になるようなことはありませんがいくつか問題があります。

  1. 複数のサービスを横串に見ていると各サービスのテーブル設計の詳細を知らない
  2. 大きいサービスだとテーブル数やカラム数が異常に多い(数百テーブル, 各テーブルに数十カラムとFKが、、、とか)
  3. tableやcolumn, subqueryのaliasが入り乱れている

(1)と(2)はあわせ技で来ると、状況を把握するのがかなり面倒になります。MySQL workbenchのような便利なツールもありますが、そもそもテーブルが300とかを超えていると画面がめちゃくちゃ見ずらいです。。。(これは仕方ない)

また、この手のツールはセキュリティやGUIによる誤操作をしやすいといった理由から本番環境のreplicaに対しても使えないことも少なくないと思います。
特に厄介なのは(3)でaliasがあるとEXPLAINの結果はそれを名前解決をせずに結果を出してきます。

例えば、サンプルとして有名なSakila Sample Databaseクエリサンプルにaliasを使ったクエリをEXPLAINすると以下のようになります。

クエリサンプル

SELECT CONCAT(c.last_name, ', ', c.first_name) AS customer,
           a.phone, f.title
           FROM rental r INNER JOIN customer c ON r.customer_id = c.customer_id
           INNER JOIN address a ON c.address_id = a.address_id
           INNER JOIN inventory i ON r.inventory_id = i.inventory_id
           INNER JOIN film f ON i.film_id = f.film_id
           WHERE r.return_date IS NULL
           AND rental_date + INTERVAL f.rental_duration DAY < CURRENT_DATE()
           ORDER BY title
           LIMIT 5;

EXPLAIN結果

+----+-------------+-------+------------+--------+----------------------------------------+---------+---------+-----------------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                          | key     | key_len | ref                   | rows  | filtered | Extra                                        |
+----+-------------+-------+------------+--------+----------------------------------------+---------+---------+-----------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | r     | NULL       | ALL    | idx_fk_inventory_id,idx_fk_customer_id | NULL    | NULL    | NULL                  | 16008 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY,idx_fk_address_id              | PRIMARY | 2       | sakila.r.customer_id  |     1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY                                | PRIMARY | 2       | sakila.c.address_id   |     1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | i     | NULL       | eq_ref | PRIMARY,idx_fk_film_id                 | PRIMARY | 3       | sakila.r.inventory_id |     1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | f     | NULL       | eq_ref | PRIMARY                                | PRIMARY | 2       | sakila.i.film_id      |     1 |   100.00 | Using where                                  |
+----+-------------+-------+------------+--------+----------------------------------------+---------+---------+-----------------------+-------+----------+----------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

1文字エイリアスが厳しいというのもありますが、joinが多いクエリでこのクエリの最後のORDER BY titleが気になりますが、titleカラムがどのテーブルにあるのか把握するのは手間です。

こういうのどうやって把握してますか??

helper作った

というわけで作ったのがquery_review_helperです。

reviewするクエリをstdinから渡して、そのクエリ中で出てくるtable, columnに関する情報を取得してリストアップします。 必要な情報はReplicaサーバなど負荷が問題ないがものに接続して取得する想定です。

例えば、上記のクエリだと以下のような実行結果になります。

$ ./bin/query_review_helper -u {user} -p {password} -h 192.168.1.10 -P 3306  -d sakila
(Input query and ^D at the last line)

SELECT CONCAT(c.last_name, ', ', c.first_name) AS customer,
           a.phone, f.title
           FROM rental r INNER JOIN customer c ON r.customer_id = c.customer_id
           INNER JOIN address a ON c.address_id = a.address_id
           INNER JOIN inventory i ON r.inventory_id = i.inventory_id
           INNER JOIN film f ON i.film_id = f.film_id
           WHERE r.return_date IS NULL
           AND rental_date + INTERVAL f.rental_duration DAY < CURRENT_DATE()
           ORDER BY title
           LIMIT 5;
^D
- rental
  - Index
    - PRIMARY             : (rental_id)
    - idx_fk_customer_id  : (customer_id)
    - idx_fk_inventory_id : (inventory_id)
    - rental_date         : (rental_date,inventory_id,customer_id)
    - idx_fk_staff_id     : (staff_id)
  - Cardinality
    - return_date = 9800
    - rental_date = 9794
    - inventory_id = 4575
    - customer_id = 599

- customer
  - Index
    - PRIMARY             : (customer_id)
    - idx_fk_address_id   : (address_id)
    - idx_last_name       : (last_name)
    - idx_fk_store_id     : (store_id)
  - Cardinality
    - customer_id = 599
    - first_name = 591
    - last_name = 599
    - address_id = 599

- address
  - Index
    - PRIMARY             : (address_id)
    - idx_fk_city_id      : (city_id)
    - idx_location        : (location)
  - Cardinality
    - address_id = 603
    - phone = 602

- inventory
  - Index
    - PRIMARY             : (inventory_id)
    - idx_fk_film_id      : (film_id)
    - idx_store_id_film_id: (store_id,film_id)
  - Cardinality
    - inventory_id = 4581
    - film_id = 958

- film
  - Index
    - PRIMARY             : (film_id)
    - idx_fk_original_language_id: (original_language_id)
    - idx_title           : (title)
    - idx_fk_language_id  : (language_id)
  - Cardinality
    - rental_duration = 5
    - film_id = 1000
    - title = 1000

サンプルのSELECTクエリでアクセスしているtable、そしてjoin, whereで利用されているcolumnのcardinalityの推測をリストアップしてくれます。
これでこの出力からcmd-Fで検索することもできるようになりました。

また、比較的古めなMySQL 5.7などでも使いたいので、このcardinalityはPKを利用して以下のようなクエリでサンプリングして来ています。
見てわかるように現状だとcardinalityは1~10000にしかなりませんが、ここからでもある程度の見当がつけられます。

SELECT count(distinct {column})
FROM (
    (SELECT {column} FROM {table} ORDER BY {PK_COL1} ASC , {PK_COL2} ASC LIMIT 5000)
    UNION DISTINCT
    (SELECT {column} FROM {table} ORDER BY {PK_COL1} DESC , {PK_COL2} DESC LIMIT 5000)
) tmp

これで、where句に大量の条件があっても、とりあえずどのカラムのcardinalityが高いのか、もしくはかなり低いのかの予想がつけられます。
このサンプリング方法は今後改善していく予定です。

クエリのパースにはTiDBで有名なpingcapのparser を利用しました。 今回やりたかったことはkickstartの説明からそれほど離れていなかったので、かなり簡単にできました。

multi-column indexの取得方法はid:yoku0825さんに教わりました!

8.0ではmulti-column index中でもASC, DESCをカラムごとに設定できるので、この点も今後改善が必要です。

というわけで、明日からこれを使ってみて、適宜改良を加えていきます。 なにか問題があればissueを投げてもらえば見てみます。

そもそもコンセプト的に使い物になるかもわからないので、testもありません。悪しからず。