せっかくのGWなので、おもちゃを作りました。 Query Review Helperです。
背景
最近MySQLで実行されるクエリをレビューするタスクが異常に多くなってきました。
開発者の全員がindexやjoin, partition, limit offsetなどなどのハマりどころを知っているというのが理想ではありますが、そうも行きません。
大体EXPLAIN
をかけてtable scanになっていないか、sortやlimit offsetで劇的に重いクエリがないか確認すれば、すぐに問題になるようなことはありませんがいくつか問題があります。
- 複数のサービスを横串に見ていると各サービスのテーブル設計の詳細を知らない
- 大きいサービスだとテーブル数やカラム数が異常に多い(数百テーブル, 各テーブルに数十カラムとFKが、、、とか)
- 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さんに教わりました!
SELECT index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index ASC) AS columns FROM i_s.statistics WHERE (table_schema, table_name) = (?, ?) GROUP BY index_name とかが好きです!
— yoku0825 (@yoku0825) May 2, 2021
8.0ではmulti-column index中でもASC, DESCをカラムごとに設定できるので、この点も今後改善が必要です。
というわけで、明日からこれを使ってみて、適宜改良を加えていきます。 なにか問題があればissueを投げてもらえば見てみます。
そもそもコンセプト的に使い物になるかもわからないので、testもありません。悪しからず。