InnoDBのページとインデックスの読み方
いつもの雑談、真面目な技術。訳して、いつまじラジオのJです。
上原です。はい。じゃあ本日は、上原持ち込みテーマということで。
最近すごく開発してるんで、その話とかできるといいなぁとか思いつつ。
なんか楽しそうに開発してるもんな、最近。
そう。夜中までずっと開発してるっていう感じなんだけど。
主にパフォーマンスチューニングが主にやってることではあるんですが、最近だと。
そういえば、この前の勉強会かなんかで、参加してないんだけど、なんかDBの話とかしてた?
そうですね、しましたよ。
なんか面白い話あった?
僕ともう2人発表して、もう1人は僕とテーマ丸かぶりだったんですよ。
井野DBのページの、ページベースで見たパフォーマンス的な話をしたんですね。
はいはい。
っていうのと、あと1人はデッドロック周り、どういう範囲でロック取るのかみたいな話かな。
正直僕ちょっとついていけなかったので、そういう話ではありました。
僕の方は昨日LTEを知ったんですけど、その前列の木曜日にそれのフルバージョンをチームの勉強会でまるっと1時間やって、好評でした。
ページってどういう話なの?ページを効率よく読み取るっていう話?
まあそういう話で、結局これってこのクエリでこのインデックスだったら何ページアクセスするかみたいな。
はいはいはい。
っていうような話と、あとはそれってディスクから読んでんの?バッファープールから読んでんの?みたいな。
一応ざっくり言うと結論として持ってったのは、何ページ読むか、そのうちディスクから何ページ読んでるかみたいなところで変わりますよっていうような結論です。
っていうのを長々と1時間くらいチーム勉強会の方で話した。
読み取るページ数が少なければバッファープールから読んでも、ディスクから読んでもあんまり時間自体は変わらなかったりするっていうのは実際あるけどね。
ディスクからでもそう分かんないってことですかね?
そう、結局ページ数が増えることでディスクIOが増えるから結構時間に差が出るけど、ちっちゃければあんまりそこの差ってそんなに変わんなかったりする。
だからLTはあんま分かんないですが、反応が、チーム勉強会はスライド形式でやったものの都度止めながら分かんないのは説明しながらやったんで。
LTは走り切って最後質問だから。どの程度伝わったのかまでは分かんないけど。
チーム勉強会は良かったっすよ。
え?そうなんだ。
2人面白かったって言ったし、1人感動したって言ってました。
すごい。
でもその1人っていうのは最近毎週アウトプットを持ってきてくれてる人ではあるんで、
まあ多分そこの自分のアウトプットと比較してっていう話でそんなことは言ってます。
はいはい。
カバリングインデックスとかの話もした?
LTではしてないけどチーム勉強会はしました。
まあ切っては話せない話で。
実例をもとに話したんですよ。チームであった実例をもとに。
カバリングインデックス自体はそんなに主題ではない。
でもページの概念が分かんないとインデックスの貼り方が分からないよねとは思う。
良い複合インデックスの貼り方が分からないよねって思っちゃうんだけど。
何ですかね。ちょっとすぐにピントが来てないですが。
複合インデックスのカバリングインデックスの効果が分からないっていうのは、
複数のカラムを指定してインデックスを貼る手法である。
それだけでセカンダリインデックスができて、
そこの定義されたカラムに関してはそのセカンダリインデックスに入っています。
検索とかでその複合インデックスに入ってる要素だけで検索されるのであれば、
セカンダリインデックスだけで完結するというか。
いわゆるカバリングインデックスって言うんですよね。
そうだね。要素も取るってなると。
とはいえ、検索対し検索のキーワードだけで使うっていう意味でも使えるから、
そういう意味だとカバリングなんか分かんないけど。
要するにセカンダリインデックスだけで覚えることが一番良くて、
絶対にセカンダリインデックスには主キーが入っているから、
主キーをたどってプライマリインデックス側、
全てのカラムが入っている側を参照すると要素が増えるよね。参照する。
そこをいかないようにするにはどうしたらいいかって。
だから主キーのほうまでいったらそれだけでページ数が増えるから。
だし全カラム持ってるからそれだけ容量も大きいしみたいな話になってくる。
そういう意味合いだよ。
だからその時にわざわざページという概念まで考えてるかっていうと、考えてないかもなっていう。
ページが効くのは結局あれだよね。並び順で。
ページが効くのは並び順?
カワリングインデックスとか符号インデックスで指定した、
一番最初に指定した定義で並ぶじゃん。セカンダリインデックスが。
その並びでごっそりとってくるわけじゃない。
例えばページが並び順とは別のキーワードを種にして検索をかけた場合って、
バラバラのページをとってこないといけなくなって、
読み取るページ数自体は増えてしまうよねみたいなことになるから、
ちゃんと並んでる状態をごっそりとってこないといけないよねみたいな。
並んでる分、一つのページに収まってるっていう。
そういう意味でそういう検索を意識しながら、インデックスを張らないといけないよねとは思うんだよね。
むずいな。
そう考えたらインデックスってそこまで考えると、
これ効いてないな、効いてるなって別にエクスプレイしなくてもわかるよねっていう。
そうですね。その辺の解像度は上がりますね。
そこまで知ってると、どこをどうすればいいのかがわかるというか、
楽になるなって感じはあるっていう。
そうですね。普段ただ繰り返してるだけだとそこまで意識しないですもんね。
しないね。
遅くなって初めて、なんで遅いんだってなって、
そこからさらに興味を持ってやっとページがいくぐらいの。
具体があるからね、一番いいんだろうし、
自分がやっぱり書いて知ってるものだからこそわかる部分がある。
入ってくるものが多いのかもしれないしね。
今回チームの方に関しては実際の具体例を交えた感じのスライドだったから、
より入ってきやすかったんじゃないかなとは個人的には思います。
LT側は逆にその具体例を知らない人たちに話し、
ただ具体例といっても、実際に僕は私物のPCで作ったから、
全然業務の用語は一個も出てないんですけど、
チーム勉強会の時はこれはあの時のあれをイメージして作ってるみたいな感じで言ったんで、
部分的にその業務の用語に置き換えながら説明したっていうのはありますけど、
LTは業務の用語が一切ないままそのまま話してるから、
チームの人たちほどスッとは来なかったかもしれない。
そもそも説明も少ないんで、LTだから。
ページまで意識するのって結構むずいよね、物理。
そうですね。
データベースがどう動いてるかっていうか、
どうデータを管理してるかみたいなのを知らないと結構厳しいだろうなっていう感じはするけどね。
まあ奥深くまでいこうと思えばまだまだあるんだろうなって思うと、
きっと知らないことだらけなんだろうなと思いますもんね。
でも大抵はインデックスとか、あとはなんだろうな、
まあそのSQL実は聞いてませんみたいな、
そのレア分実はここには聞いてませんみたいな。
インデックス聞いてないとかね。
インデックスもそうだし、実はそれ、
まあインデックスか、インデックス聞いてませんとか。
そうですね。
オプティマイザーの誤選択をFORCE INDEXで直す
それこそ今回実例としてあったこととして、
途中でOptimizerが使うインデックス切り替えるせいで、
バッチが遅くなるみたいなのもあって、
それは全然勉強会で話してはないですけど、
まあそういうのも実際体感できたんですね。
実際にそのバッチを動かしていく中で。
なのでそんなのも、
ちょっと本来のミッションとはずれる部分ではあるものの学びになったりなんだり。
今回その実例と勉強会と合わせて、
結構その人にとっては学びでかかったんじゃないかなと思いますね。
Optimizerが変わるっていうのは具体的に何で変わった?
とあるキーで検索かけてて、並びはID順の層としてたんです。
なのでそのインデックスが効いててよかったんですけど、
途中から後半ぐらいになるとOptimizerが何か勘違いして、
別のインデックスの方が効率いいと判断して実行されてて、
何だったかな、何かと何かのインデックス結果を
ガッチャンコしたとこから取るみたいな動きをしだして、
そのせいで遅くなってたんで、
今回はもうインデックスを強制させたんですよね、
フォースインデックスで。
そしたらガッツリ早くなって。
期待するインデックスが選択されてるときは、
1回のクエリはどのくらいかな、
それでも重いは重いんですけど1秒は測ってないのが、
そのインデックス間違うと1つのクエリ20秒くらいかかってる。
なんで、そういうのとかも実際にログで見たりしつつ改善してたんで今回は。
何だったかな、その人が調べた話で、
AIが残りのデータ量を計算に入れて、
だったらこっちの方がいいよねっていう判断をした結果、
ミスってるみたいなんじゃないかっていう推測をAIがしてたって言ってました。
そもそもなんでその判定に入っちゃうんだろうなって思って。
それはオプティマイザーの中身読んでかんとわかんない。
そっか、それは無理あるな、無理あるけど。
なんかトリガーがあるんだろうなと思って、その判断をする。
その思考モードに入るというか、なんだろうな。
でもその思考モードには、思考モードは買ってなくて、
どっかのAIの話を信用するのであれば、
データ量を元に分岐してる可能性は、
推定データ量みたいな、残りの。
リミットで順々に取ってくっていう人いるんですよね。
それによってデータ量がどんどん減ってくるわけですよね、残りが。
その途中から勘違いしなくすっていう感じなんですよね。
なるほど。
まぁなんで、どんどんデータ量が減っていくっていう状況があるんで、
何かしらで勘違いする要素はあるのかもなとは思うというか、
突然変わったっていうわけではない。
なるほど。
一定のデータを読み込んだ後からそうなりだすので。
途中まで速いのが途中から毎回遅くなるみたいな感じだったんで。
その順々に取っていくっていうのはオフセットかなんかでやってるってこと?
そうですね。最後に取得したID取っといて、そこを次からWEARに入れてるって。
そのID以上のやつを取得みたいな。
それみたいな感じかな。
だからオフセットでどんどんちっちゃくなっていくってことか。
そうですね。
オフセットから最後までの量がどんどん減ってって、
ある一定のオフセットまで行くと、
オプティマイザーで違う検索方法を推奨しだすってこと。
そうですね。
そんなあるんや。
だからインデックス矯正したらむっちゃ速くなりましたね。
順々に取ってくるってどういうシチュエーションなの?って思っちゃった。
全データを順々に取って。
全データってことでも多分聞こえない。
そうですね。
結局メモリを心配してっていう感じで、
取ってS3に上げて取ってS3に上げてみたいなことをしてたんです。
そのために一気に取るとアウトモンメモリが発生したんですよ。
発生したんだ。
そこは別ロジックであったんですけど、
後はデータが溜まっていったことによって発生したんで、
元々はそこのケアですね。
特定のデータ取るってどうなんだろう?
どっちがいいんだろうね。
SQLで取るのがいいのか、それとも
MySQLに対して効率よくエクスポートできるツールってそれなりにあると思うから
そこを経由した方がいいのかとか。
そのツールが全然僕は知らないのでちょっとわかんないけど。
MySQLダンプとかさ。
一般的なのがあるから。
そういうのがどうなるって思っちゃったりするけど。
比較してないのと、
あんまそもそもそれ使ったことないんでイメージも湧いてないですが。
あとロードデータとか。
MySQLについてロードデータとかでCSV化したのを取るとか。
データをそういう風に取るのもできるんですね。
ロードデータっていうMySQLに付いてるコマンドでできるんだけど。
MySQL内のコマンド?ロードデータっていう構文みたいなのがあって。
ロードデータ自体はデータを入れる側では使ってるんですよね。
エクスポートもできる。ロードデータじゃないか。
エクスポートできないか。
あれエクスポートどうやってたんだっけ?
でもCSVで出せるんじゃない?
でもそれが早くなったってことだね。
そうそう、インデックス固定して調整したら
全然気にならない速度で終わったんで。
できたんだ、それでもいいかって感じだったけど。
修正もクエリのとこにフォースインデックスって書いただけで早くなったんで。
大したコストもかかってないし。
もともとArt of Memoryが発生したのは
S3に送るところを都度都度ストリームで送るっていうよりは
1回溜め込んでドンって送るっていう実装になったんで
そこで発生してたんで
そこを都度どんどん送っていくように書き換えてみたいなのがあります。
全然関係ない話で20分くらいしてますけど。
関係あるでしょ。
最近の開発はっていうテーマだった気がする。
全期間取得と不要なSQLを減らす
最近の開発、でも似たようなことずっとしてる感じだよね。
インデックスつけたり。
オファースチューニングから入ったからそうなってるのか。
インデックスあったりとか
例えば日付の期間でウェアしてるように見えるんだけど
実は内側の方には聞いてなくて
全件取得してるとか
例えば対象の薬局の全期間のデータを取ってきて
その後に取ってきた後にウェアしてるみたいなのとか
そういうのに対してちゃんと内側で
ビュー使ってるから
ビューっていうかウィズクで作ってるから
1回テンプデイビーにそれ全部入れてからやっちゃってるんで
実際ウェアの期間でしか指定されない
1日とかでしかほとんど指定されないのに
全期間を持ってきてしまっている
SQLとかの修正とか
あとはなんだろう
画面で一覧画面で必要な要素だけを取ってくればいいのに
その裏側にある詳細なデータまで全部
一気に持ってきてるみたいな
それをだから一覧で取るときのエンドポイントと
実際にその詳細が必要になる場面での
エンドポイントを分ける対応とか
そんなのもいい
あとはレイジーロード
要するに都度都度SQLが打たれるような
集計処理とかで
ある特定の単位ごとに
集計処理をかけていくことになると思うんだけど
その都度セレクト
アスター、フロム、テーブル、ウェア、ID
何々
リミット1みたいなのを毎回言ってるわけだけど
これがレイジーロードって呼ばれるもので
一回一回のSQL自体はそんなに重くないんだけど
回数が増えるとそれなりの遅延を生むみたいな
のとかをいいがろうとして
ある特定の単位では一気に取得して
都度都度のSQLが発生しないようにする対応とか
あとはメモ化っていうのをやって
SQLに対して
キャッシュみたいなもんだよね
SQLのキャッシュみたいなもんだけど
SQLの構文
発行されるSQLに対して
その特定のスコープ内でしか生きられないような
インスタンスを作って
SQLを記憶していって
2回目以降同じSQLが打たれた場合は
そのキャッシュを通って返すようにするみたいな
対応とか
WiZookみたいなWindows関数みたいなのとは全然違う?
違くて
それはもうアプリケーション内
今回だとLaravelのPHPのアプリケーションの
一つのインスタンスとして構築して
例えばセレクトアスターフロム
マスターデイビー
ウェアIDなんちゃらみたいなのが来たときに
1回目はもちろんデータベースを参照して返すんだけど
2回目はそのセレクト文をキーにして
それで取得できる
マイスケールの行を記録していて
次同じSQLが打たれたら
このキーにヒットする
ヒットしたら保存したバリューだけを返すみたいな
デイビーの参照はしないみたいな実装をしたりとか
こんな具体的なクエリが毎回何回も打たれるとか同じクエリ
マスターデータに対しては基本
マスターデータだけそうしたんだけど
マスターデータって結局その特定の集計する単位で
一緒のものをほぼほぼ使うことじゃない
だからだいたい同じになるから結構指があるみたいな
それはクエリの中に組み込めるって話ですか?
クエリの中に組み込む
この結果だけはここのメモカから取るみたいな話じゃなくて
SQLとは別にメモ化したものを取ったのを後でガッチャンコするって話
今回の場合だとMySQLのコネクション
MySQLコネクションっていうのをラップして
セレクトとかを拡張して
セレクト打たれた時はメモカを見るように
その単純なSQLだけなんだけど
ラップしてセレクト打たれたら処理が
ラップされた方に行ってメモカを参照するみたいなことをしてる
思ったより改造してるんだな
そうだね
なるほど
SQLiteバッチを14時間から25分へ短縮
今回のリリース僕も一個言えてて
うちの機能ってオンラインでもオフラインでも使えるような機能があって
オフライン用にはオフライン用にデータベースを作ってますと
それを夜間バッチで作っていて
その処理むちゃくちゃ重くなってて最近
14時間ぐらいかかるんですよ
本当は夜中動いて朝に終わってほしいから
4時間とかぐらいで終わってほしいわけです
夕方ぐらいに終わるからどうしたもんかみたいな感じで対応したんですけど
14時間かかったのが25分ぐらいになりました
どんな処理してたんやみたいな感じですけど
変えたの本当ちょっとで
オフラインで使うんで手元にデータベースが必要なんで
SQLiteなわけなんですけど
SQLiteにたくさんデータを書き込んでるんですけど
MySQLではできるけどSQLiteではできない検索みたいな
たとえばIUAを
ダクテンとかハンダクテンとかを無視して
MySQLは検索をかけることができるんですけど
SQLiteはそれができないので
専用にダクテンとか全部取った状態にしたカラムを用意してて
検索かけるときはそっちの検索されたワードも
同じ処理かけて正規化の処理かけた上で検索するっていうことをしてるんですよ
部分的に
へーめんどくさ
そうなんだ
なんでその正規化した後のデータ入れてるカラムは
オフライン用なんでそっちにしかないんで
そのバッジの中でそこのカラムにデータを入れ込んでくるんです
あーそういうこと
それを今までレコード数相当あるレコード一行一行
一生懸命書き込みしてたんですね
IUAは行数分発生してたんです
それをトランザクションで囲ってやって
その薬局ごとに一回の書き込みに変更したら
ごっそり速くなりました
そりゃそうだ
そりゃそう
AZ間遅延と大量クエリの積み重ね
あーこれで速くなるかっていう
なんか全然実装値はそんなことも
実装したのも直したのも僕ですけど
実装値はそんなこと何も考えてないから
そのときそんなデータ量も多くないから
分かんなかった全然気づかなかったけど
なんかそれを入れただけでこんな変わるんかっていうぐらい変わったから
なんかリリース翌日
それ本当にこんな速くなるかって思って
色々と調べたけどなりそうだったから
だいぶやべえ処理してたんだなみたいなのに気づく
今回だと多分それこそ
その処理だと一回一回もそうだけど
今回わかったのが
どっちで処理してたかわかんない
どこで処理してたかわかんないけど
AZが違うことで結構
ネットワーク遅延が発生するから
なんかプライマリーが別のとこに
想定と違うところにいて気づけたけど
なんかAZの違いで結局
クエリを発行すれば発行するだけそのネットワーク遅延を
増やしていることになるから
うちもそれで多分
片方は10分ぐらいで終わるのに
片方は30分ぐらいかかるみたいなことになってたから
ネットワークってやっぱ遅いかそうかな
相対的に見れば
一回一回が結局
それは1ミリぐらいの
1ミリセカンドぐらいの遅延でも一回上がったりね
遅延でも結局100万回とか
10万回とかクエリ打ってたら
遅延が発生することになるわけだから
でも目に見えてないけど
0時ロードとかそうやってセレクト1行ずつ打っていったら
そうなっていくからね
次第に
だってデータベースにとって10万行とか100万行って
別に大したことないじゃん
多くぐらいあるんだからレコード数からしたら
人間の感覚で
1ミリセカンドぐらいだったらまあいいかと思っちゃうんだけど
積み重ねていくとこんなことになってんの?みたいな
ありますもんね
そうなんだよ
割と1ミリセカンドの遅延って聞いて
もういいじゃんってなるんじゃなくて
ああいう処理
築地の処理してたら終わるなこれみたいな
1000件でもう1秒だもんな
アプリしてないと普通にそういう処理
0時ロードとかさっきJが言ったセレクト1件ずつやるとかっていうのが
どれくらい行数になるのかっていうのは
アプリ開発してないと分かんない部分だよなとか思ったりはする
その辺は人によるかもしれないけど面白いところではありますもんね
これでこうだからこうなってるとか
僕はもうすごいそれが好きだから
しかも一人で完結するっていうのもまた魅力の一つ
なんか言ってましたね
普通の画面変更するとか開発だとやっぱり
一人で終わらないから
でもさっき言ったそれこそ一覧と詳細分けるっていうのは画面関係あるから
コミュニケーション活性するんだけど
データベースのインデックス貼るとかAPIのSQLの構成変えるとか
ぐらいだったら全然
深く知れば知るほどインデックス貼って改善したぐらい
それは普通やろぐらいになってきちゃいますもんね
結局さっきアプリケーションのアーキテクチャ側で
キャッシュとか駆使しながらどうするかっていう考えてる方が
かっこよくなってくるんだよ
インデックスは誰でもできるんだろみたいな
でも難しいよねピンポイントなインデックス貼って改善するって
なかなかそれこそ分かってないって難しい部分であるから
テーブル作った時は想定していない機能できて
そんな検索されるようになるのかとかもありますし
AIで変わるパフォーマンス検証
やってて思うのはやっぱりAIがいるおかげで
めっちゃ爆速で検証もできるし
実際のデータに対して参照だけだからさ
SQL変えるとかだったら本番データの
レプリカとか使えば普通に検証できるしみたいな
ステージ環境だったらインデックス貼り替えて戻せばいいから
それもAIにやらせれば検証結果得られるしみたいな
自信持って送り出せる
これによって
でも逆に言うと検証コストを言い訳できなくなるのはそろそろしんどいけど
検証コストはもう言い訳にならないで完全に
それも知ってるか知らないかじゃないですか
そうなんだね
知ってるからコストなくやれる
最初からハードル感じてない人もいれば
やり方わかんないからむちゃくちゃハードル高く感じる人もいるし
だから僕も今回ユウハラさんやったような対応がちゃんと浮かんでやれるのかっていうと怪しいから
やっぱ僕も当然コストに感じるしその辺の検証って
そうなんだよこれって結局僕はもう今までずっとパフォーマンスチューニングやってきた中で
今まで検証って昼とかに合間縫って実装して
手で実装してね手で実装して
それを夜中誰もいないときにそれまでに手順作って
本番で検証スクリプトも作ったりして検証するみたいなことをやってたわけで
やってたみなわけよこれがくそめんどくさいわけ正直な話
だから今回なんかやってて
俺が手でやってたことがすべてAIによって置き換わってるっていう感じ
いやーそうなんだよな
この辺の準備が今まで準備はないけど検証コストが避けない
実装すぐなんだけど検証コスト避けないからちょっとやれませんみたいなのっていっぱいあったと思うんです
いっぱいあった本当にだからなんか
実装も結局時間かかる
結局メモカとかも実装時間かかるよなと思ってて
担保するの難しいよなと思ってたからやっぱりアプリケーションのコスト
アプリケーションのコード改造するみたいな
ラッピングするみたいなのってなかなかできないよなと思ってたけど
やっぱり検証が担保されるのであればそれは別に全然コストじゃなくなるなーとか
ディスクじゃなくなるなーみたいな
SQL最適化でAWS費用を抑える展望
その辺のノウハウはバシバシ共有していただいて
いやそうなんだ共有しないとなこれって思いついて
みんなができた方がやっぱり
まだレイテンシー悪いところ結構あると思うからそこ改善していけば
目標はやっぱりデータベース
AWSのデータベースって高いから
それをいかに安くするか
いかに安くするには我々のパフォーマンスにかかっていて
かつさっきのページの話じゃないけど結局
キャッシュ
今メモリーとかめっちゃ積んでて
InnoDBのバッファプラにキャッシュできる量が多いけど
結局このキャッシュ効率ってどうなのって考えた時に
ページの読み取るページのサイズを
小さくすれば小さくするほどキャッシュ効率がよくなるわけじゃない
要するにメモリ上に載せられるセレクトで得られる結果が
それだけ増えるわけだから
要するにSQLを効率よくすることによって
多分実際的にもっとメモリキャッシュできる量
量は変わらないんだけど
SQLの数が増えていくっていう
そうすれば多分データベースのインスタンスのサイズっていうのを小さくしても
大丈夫になっていくんじゃないのかって思ったりしてるんで
その辺パフォーマンスチューニングが興味ある人たくさんいるんで
共有してもらって他の人にぜひやらせてあげてください
全部持って帰って
そうなんだ
俺もこれまでそれは奪っちゃいけないと思って
口挟んでなかったけど
データベース移行して
AWS移行したりして
データベースの性能が落ちたりして
スロックエリとかパフォーマンス悪いエンドポイントも増えてるって話で
そこに対する具体的な打ち手はあんまり考えられてないっていう感じだったから
じゃあここで直しとくかぐらい
みんなが意識するきっかけにはなったとは思うんで
そのAWSでアラート出るだとか
ウイハラさんがパフォーマンスチューニングしてるだとかっていうのは
確かに意識してるかもね
なんで興味持ってる人は気になってるでしょうし
来週ぐらいに話さないと
それ用の時間使ってもいい気がしますよね、儲けても
まだまだ改善するべきところは残ってるので
ぜひその辺は伝授していってください
はい
なんとこかな
はい、じゃあおめでとうございました
ありがとうございました
