MySQLで最近見たパフォーマンストピック
---
こんにちは
moznion
https://gyazo.com/1c71fdf9357592d5d11d725512f577ad
最近のマイブームはdockerコンテナのダイエットとOpenAPIのメンテ とあるオンライン処理のクリティカルパスの処理時間を監視しており、エラーバジェットが設定されている エラーバジェットが徐々に減っていくという自称が発生していた
つまり性能が劣化しているということ
おもしろさが無い……
どう直したか
割愛 (！？)
ビジネスロジックに深く関わるので、ここで話してもあまり面白くない
一例: 「初回決済かどうか？」という判別をするために、全ての決済についてそのユーザーに紐付くCOUNT集計クエリが走ったりしていた (つまり、ヘヴィユーザーの決済であったとしても「初回決済かどうか？」を決済毎に判断するために集計していた) のでそのクエリを消し、別テーブルのアトリビュートとして保持することにした
MySQLの挙動依存っぽい問題
こっちのほうが一般に楽しそうなので今日はこっち
Ver 8.0.37 for Linux on x86_64 (MySQL Community Server - GPL)
WHEREの左辺値と右辺値の型がミスマッチするとindexが効かなくなる
そりゃそうという感じだが……
code:sql
CREATE TABLE test (
id varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
reception_number varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (id),
KEY idx_reception_number (reception_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
意図通りのクエリの場合のEXPLAIN
code:sql
mysql> explain select * from test where reception_number = 'xxx' limit 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: idx_reception_number
key: idx_reception_number
key_len: 1022
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.01 sec)
右辺値がうっかり数字になっているEXPLAIN
code:sql
mysql> explain select * from test where reception_number = 123 limit 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: index
possible_keys: idx_reception_number
key: idx_reception_number
key_len: 1022
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where; Using index
1 row in set, 3 warnings (0.02 sec)
type が index になっており、Using where が付与れるようになっている。
テーブルに保存されているデータ傾向によっては統計情報の妙味で以下のようになることがある
code:sql
mysql> explain select * from test where reception_number = 123 limit 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ALL
possible_keys: idx_reception_number
key: NULL
key_len: NULL
ref: NULL
rows: 5181
filtered: 10.00
Extra: Using where
1 row in set, 4 warnings (0.00 sec)
OMG, FULL TABLE SCAN!!!!!
この際、暗黙的な型変換が左辺値 (i.e. 実際にテーブル中に存在しているcolumnの値) に適用される様子。
明示的に記述した以下のクエリと等価:
code:sql
mysql> explain select * from test where cast(reception_number as double) = 123 limit 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ALL
possible_keys: idx_reception_number
key: NULL
key_len: NULL
ref: NULL
rows: 5181
filtered: 10.00
Extra: Using where
1 row in set, 4 warnings (0.00 sec)
なおこの場合、cast結果が数値として評価できない場合のcolumn値は 0 として評価されるので、右辺値が 0 だと意図しないヒットをするようになるし何一つ良いことがない。
結論
マトモなORMを使っていればこのへんはケアされるので心配しなくて良い
この問題についても調査中にたまたま発見しただけであって、実際のプロダクト上での影響は無かった
マトモなORMを使っておらず、かつ型が厳密ではない言語を使っている時は気を付けましょう
それはそうとしてxxx_digits とか xxx_number という名前のcolumnに文字列が入るのは非直感的
とはいえ文字列で取り扱ったほうが取り回し便利なこともあるし、わかる……
0 はじまりの値とか
RailsのActiveRecordのstringがデフォでVARCHAR(255)にマッピングされる
長すぎる！！！！！！！
一般に困ることはないが key_len が非常に長くなる
再掲:
code:sql
mysql> explain select * from test where reception_number = 123 limit 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: index
possible_keys: idx_reception_number
key: idx_reception_number
key_len: 1022
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where; Using index
1 row in set, 3 warnings (0.02 sec)
1022......
key_len が長くなるとインデックスページ（16KBサイズ）内に格納できる行数が減り、より多くのインデックスページが必要となってメモリに乗り切らなくなる可能性がある。
ActiveRecordでテーブル定義をする時に string の limit をデフォルトのまま使うのをやめたほうが良いのではないか
本当に255文字列長は必要？
はい
そんな感じです。日々地道なパフォーマンスのモニタリングと改善をすることによって平穏に過ごしています。