MySQL使いがOracleを使ったときのカルチャーショック

mysql

というほどでもなく、まぁオペレーション上感じた相違点など

SQLの構文で違うところがある

最初に驚いたのはLIMIT句。あれってOracleではないんですね。PostgreSQLでも使えていたからSQL標準だとばかり思っていた。SQL標準だそうですが、サポートしているRDBMSは少数派のようです。
OracleではROWNUMという仮想的なカラムをWHERE句で条件指定するのが正しいようだ。
他にはテーブルに対して別名をつけるときの"AS"を使わないこと。これはASを入れないでスペースの後に別名をつけられるので簡単だが、気付くまでは何故構文エラーになるのか悩んでしまった。
MySQLの拡張構文がの軒並み使えないのは、まぁ当然。REPLACEとかUPDATE文のIGNOREとか便利でご都合主義的な機能が使えないのはちょっと残念。

接続のコストが高い

MySQLは接続が速いためにコネクションプールを使うのは無駄ではないかという意見が多いが、Oracleでは無いと非常に遅いため、ウェブのバックエンドなどでは必須に近い。コネクションプーリングがもともとOracleなどの接続コストの高いデータベースの性能向上策として発明されたようなので、これを使うのはある意味当然なのかもしれない。
プーリングを行うのはDBのドライバだったり、アプリケーションのフレームワークだったり、ミドルウェアだったりと多彩。一般的な手法の割に安定した定番構成が確立されていないように見えるのが意外。SQLrelayが多用されるのはフロントエンドもLL系のPHPだったり、perlだったりすることが多いような印象。でもSQLrelayの動作が安定しているかというと、色々と癖があって難物なようだ。Javaでアプリケーションを書くときはまた別の定番があるようで、こちらが安定しているのだろうか。

未だに1接続に1プロセスが標準

いわゆる繋ぐ都度にforkするモデル。接続のコストが高いのもこれが一因。
だが、これには対応策があって共有サーバ構成というモードにすればマルチスレッドで動作するようになる。前述の接続プールを使うことが一般的な場合にはそれほどデメリットとはならないためか、共有サーバ構成が標準で使われるようにはなっていない。
MySQLの感覚でザクザク繋いでいたらメモリに沢山のプロセスが上がって応答不能になってしまったりする。
接続モデルを変えるとメモリの確保領域がガラっと変ってくれたりするので、状態監視のときに見るべきところが増えてしまって大変。

SQLのパースコストが高い

実際には狭義のパースはそれほどでもないが、実行計画を立てたりするコストなどトータルでみると非常にコスト高というのが真相のようだ。
だからOracleではリクエストされたSQLをプールして過去に実行されたSQLを一致するものがあればこれらの処理を省くような最適化を行う。DBIとかでいうprepareメソッドを実行するとパースまで行って、実際のexecute時にプレースホルダに変数を入れて実行する。次の実行からはprepare時にプール内にある前回実行したSQLと実行計画が流用できるので、速くなるという仕組み。
MySQLでは同じような仕組みがあるものの性能に対しての寄与があまりないのでインジェクション対策程度に考えられているようだ。実際、PHPのPDO-mysqlではprepareがエミュレーションになっていて、実行時までDBに処理が渡らないようになっている。Oracleではprepared statementが性能的にも非常に意味があるし、アプリケーション側で利用しないとSQLのプールがゴミで押し流されるといったことになる。

内部情報はなんでもビューやら仮想テーブルで見える

MySQLも5.xからInformation schemaという形で内部情報を提供するようになっているが、Oracleではこちらが標準でこれがないと情報が全然得られないといってもいい。
最初は何を調べるにもSELECTで取得しなければならないのが不便だと思ったこともあったが、慣れると便利。色々な情報を結合して分析するようなSQLが書けるようになってくる。Oracle使いがやたらと情報収集用のSQLを書き溜める理由がよく分った。

コストベースオプティマイザ

何だか凄そうな事をやってはいるが、個人的には万能ではないなと思った。
考え方としては、従来の単純に最適化のルールを当てはめていって実行計画を立てる方法ではなく、テーブルの現在の統計情報をベースにCPU時間やディスクI/Oなどを計算してコストが最小となるようなアクセスパスを割り出してくれるらしい。
こういうオプティマイザは上手くいっているところよりも失敗した例ばかりが目につきがちなので、失敗例だけをとり上げて一方的に非難するのは可哀そうだが、やはり時々おかしな実行計画を立てているのをみかける。
統計情報を更新した上でも、実行計画の詳細を見ると何故か全表探索が安いと判断してそれに妙に固執することがあった。ヒント情報を付与してインデックス検索にするとコスト高にはなるように見えるが、実行時間もアクセスするブロックも減るのでコストの見積りがおかしいとしか思えないケースがだった。
初期のCBOが実装されたから随分時間がたったし、ほとんどのケースでは正しい結果を出すのに違いないのだろうが、いかんせん複雑すぎて何かあったときに思わぬ挙動を予測できないのが怖い。
MySQLのオプティマイザもバージョンが上がるたびに色々してくれるようにはなっているが、ドキュメントも全部目を通すことも可能なぐらいの分量。普通の人で理解できるのはこのぐらいじゃないかな。

インデックスやJOINの種類が豊富

CBOを実装したのも、この豊富なインデックスやJOINの方法の組み合わせをルールだけで記述していくのが難しいからじゃないかな、と思った。
MySQLにはないインデックスとして、逆キー索引というのがある。これは対象のカラムの内容を後の文字からインデクシングしてくというもの。何がいいかと言うと、URLを入れるカラムがあったとして、これに対してこのインデックスをはっておくと後方一致で検索ができる。例えば

SELECT * FROM BOOKMARKS WHERE URL LIKE '%.html'

みたいな感じ。通常のインデックスではこれは検索することが出来ないので、全表探索となる。そんな検索をするときには文字を逆に入れておけ、という指摘は恐らく正しくてMySQLではTipsとしてそういうのがあるぐらい。ただアプリケーションに手を入れなくとも、ちょちょいと簡単に高速化できるというのは堕落感たっぷり。
他にはカーディナリティが低いカラムに対して有効なビットマップインデックスとか、関数を作用させた状態に索引をつけてしまうファンクションインデックスとか。そりゃまー、実装すれば高速することが出来るケースもあるかもしれないけどさ、というような機能がある意味素敵。