MySQL SQLパフォーマンスとtransactd その１

よく、SQLが遅いといった話を耳にしますが、サーバー側がどう処理して遅いのかまで書いたものがあまり見当たらないので、Transactdの開発経験を生かし、その使い方と合わせて書いてみたいと思います。

MySQLは、プラガブルデータベースエンジンという仕組みでさまざまなデータベースエンジンを利用できるようになっています。その内部は、データベースエンジンの操作インターフェースを定義してエンジンごとに実装をするというC++のポリモーフィズムを利用したものとなっています。インターフェースはclass handlerで定義され、SQLの解析結果からhandlerインターフェースのメソッドを組み合わせて実行し結果を得ます。

これからサーバー側の内部の説明をしてゆきますが、各エンジンごとの詳細ではなく、主にhandlerインターフェースの操作レベルの話になります。SQLのパフォーマンスで最も重要なのは内部でアクセスするレコード数です。少なければ少ないほどパフォーマンスは良くなります。handlerインターフェースを使ってどのようにレコードアクセスするかに注目してください。

select count(*) from tablename

まずはselect count(*)から。このSQL文はtablenameのテーブルのレコード数を調べますが、handlerにはrecords()というレコード数を返してくれる、そのもののような関数があります。

「ような」としたのは、innodbは正確な値を返してくれないからです。驚いたことに「およそ」の数しか返って来ないのです。理由は、リピータブルリードや、リードコミッテッドなどトランザクション分離レベルのために正確な値がわからないからだそうです。（コミット済数を返してくれればそれでいいように思いますが）

それでMySQLはどうするかというと、全レコードスキャンします。100万レコード（行）があれば、先頭から最後までアクセスして数を返します。恐ろしい話です。レコードが少ないうちは瞬時に値が返りますが、レコードが多くなるとディスクアクセス、CPUとも最大パワーを使います。スキャンはhandler::ha_index_init()にてprimaryキーの初期化をしてhandler::ha_index_first()で先頭レコードに移動し、handler::ha_index_next()がエラーを返すまでループします。そのテーブル全体（キー全体）がinnodbキャッシュにあれば、ディスクアクセスしなくて済みますが、なければディスクアクセスを伴います。その際、最適化のためにキーリードオンリーをinnodbに指示して、キーのみを読み取っています。

transactdでテーブルのレコード数を調べる

transactdでは、テーブルのレコード数を調べる関数としてtable::recordCount(bool estimate=true)というメソッドを用意しています。この引数bool estimateは、おおよそでよければtrueを指定します。この結果はinnodbのhandler::records()とほぼ同じ値です。

falseならMySQLと同じように、全レコードスキャンします。先ほど「ほぼ同じ」と書いたのは、innodbのhandler::records()はレコードがあるのにゼロを返してきたりするので、少し変えているからです。レコード数を取得した直後に削除や追加によって変わってしまうことがあるにせよ（すぐには変化しないことの方が多い）、有るか無いかは重要ですので、1以下の時はもう少し正確な値を得るために、実際にテーブルをスキャンしてレコードを数えて返します。そのため、estimate=trueでも数レコードをスキャンすることがあります。estimate=trueのときの精度は、innodbの実装に依存しますが、20%程度の誤差はよくあります。

まとめ　(select count(*) from tablename)

MySQL(innodb)の場合、大きなテーブルで高速に現在のレコード数を調べるのは困難です。MySQL(innodb)のselect count(*) from tablenameは全レコードをスキャンします。将来も含めたレコード数を想定して使いましょう。具体的には、数十レコード程度なら気にせず使えば良いでしょう。数百より多い場合になると、ハードウェア（ディスク、メモリなど）や同時アクセス数とパフォーマンスの要求レベルに応じて使う使わないを判断しましょう。

transactdのtable::recordCount(estimate = true)のアクセスパフォーマンスは、テーブルのレコード数に関係なくほぼ一定です。対してestimate = falseではMySQL(innodb)の場合と同様です。transactdはestimate引数にて、プログラマがコードでパフォーマンスと精度を選択できます。

余談ですが、transactdクライアントは Actian社の PSQL(Btrieve)というデータベースにもそのまま使えます。PSQLの場合、recordCount()は、常にコミット済のレコード数を瞬時に返してくれます。

次のその２では、select * from tablename where fieldname = xxxを取り上げたいと思います。