Go to the first, previous, next, last section, table of contents.


12 MySQL の最大性能をひきだすには

Optimization is a complicated task because it ultimately requires understanding of the whole system. While it may be possible to do some local optimizations with small knowledge of your system/application, the more optimal you want your system to become the more you will have to know about it.

So this chapter will try to explain and give some examples of different ways to optimize MySQL. But remember that there are always some (increasingly harder) ways to make the system even faster left to do.

12.1 最適化 概要

システムを速くするための最も重要な部分は、もちろん基礎的な設計です。 You also need to know that kinds of things your system will be doing. That is your bottlenecks are.

主なボトルネックは:

12.2 システム、コンパイル時、スタートパラメターのチューニング

We start with the system level things sine some of these decisions have to be made very early. In other cases a fast look at this part may suffice because it not that important for the big gains. But it is always nice to have a feeling about how much one gould gain by chancing things at this level.

使うべき OS はとても重要です! 複数の CPU を持つマシンでは、 Solaris (なぜなら、Solaris のスレッドはとても素晴らしく動作するから)、 あるいは、Linux ( kernel 2.2 は良い SMP をサポートしているから) を 使用すべきです。 32bit マシンの Linux では、2G bytes のファイルサイズの 制限があります。 これは新しいファイルシステム (XFS) のリリース時に なくなって欲しいものです。

Because we have not run production MySQL on that many platforms we advice you to test your intended platform before choosing it if possible.

Other tips:

12.2.1 MySQL の速度に影響するコンパイルとリンク方法

次のテストの多くは Linux 上で MySQL ベンチマークで行われました。 しかし、これらは他の OS についてもいくつかの指標を与えます。

-static でのリンク時に最速の実行形式を得ます。 TCP/IP の代わりに Unix ソケットをデータベースへの 接続に使用することも、より良い性能を与えます。

Linux では、pgcc-O6 でコンパイルした時に最速のコードを得られます。 このオプションで `sql_yacc.cc' をコンパイルすると、gcc/pgcc は 全ての関数をインラインにするために多くのメモリを必要とするので、200M のメモリが必要です。 libstdc++ ライブラリの増加を避けるためには、 MySQL の configure 時に CXX=gcc も設定すべきです。

より良いコンパイラー、そしてコンパイラーのオプションは、10-30% の 速度の向上をもたらします。 これはあなた自身が MySQL をコンパイルする 時に重要なことです!

Intel では、例えば pgcc か Cygnus CodeFusion compiler で最速となります。 我々は 新しい Fujitsu コンパイラーでもテストしましたが、 MySQL を最適化オプションでコンパイルするには、 まだ完全なバグ・フリーではありませんでした。

Here is a list of some mesurements that we have done:

TCX により配布されている MySQL-Linux 配布は pgcc でコンパイルされて いますが、 but we had to go back to regular gcc because of a bug in pgcc that would generate the code that does not run on AMD. We will continue using gcc until that bug is resolved. In the meantime, if you have a non-AMD machine, you can get a faster binary by compiling with pgcc. The Linux binary is linked statically.

12.2.2 Disk の問題

12.2.2.1 データベースとテーブルにシンボリックリンクを使用する

テーブルとデータベースのファイルを MySQL のデータベースディレクトリーから 違う場所に移動し、 それに対してシンボリックリンクを張ることができます。 これは例えばディスク容量が少なくなって、データベースを移動したい場合にそうしたくなるでしょう。

MySQL が、テーブルがシンボリックリンクであることに気づいた場合、 symlink を解析し、代わりにその実体のテーブルを使用します。 これは realpath() コールをサポートする全てのシステムで働きます(少 なくとも Linux と Solaris は realpath() をサポートします!)。 realpath() をサポー トしないシステム上では、symlink とテーブルを 同時に使用すべきでありません! テーブルはテーブルの更新後に矛盾する事になるでしょう。

MySQL はデフォルトではデータベース同士のリンクをサポートしません。 が、データベース間のシンボリックリンクを作成しない限り、これは正常に働くでしょう。 MySQL データディレクトリに db1 データベースがあるとして、 仮に db2db1 のシンボリックリンクにしたとします:

shell> cd /path/to/datadir
shell> ln -s db1 db2

すると、db1 中の tbl_a テーブルは db2tbl_a テーブルとして見えます。 もしあるスレッドが db1.tbl_a を更新し、他のスレッドが db2.tbl_a を 更新した場合、 問題が発生します。

こういった使用をどうしてもしたい場合、`mysys/mf_format.c' を以下のように変更します:

if (!lstat(to,&stat_buff))  /* Check if it's a symbolic link */
    if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))

Change the code to this:

if (realpath(to,buff))

12.2.3 サーバーパラメーターのチューニング

mysqld サーバーが使用している現在のバッファサイズを次で得ることができます:

shell> mysqld --help

この結果、全ての mysqld オプションと次のようなコンフィグ可能変数のリスト を得られます。出力結果にはデフォルト値が含まれ、以下のように表示されます:

Possible variables for option --set-variable (-O) are:
back_log              current value: 5
connect_timeout       current value: 5
delayed_insert_timeout  current value: 300
delayed_insert_limit  current value: 100
delayed_queue_size    current value: 1000
flush_time            current value: 0
interactive_timeout   current value: 28800
join_buffer_size      current value: 131072
key_buffer_size       current value: 1048540
lower_case_table_names  current value: 0
long_query_time       current value: 10
max_allowed_packet    current value: 1048576
max_connections       current value: 100
max_connect_errors    current value: 10
max_delayed_threads   current value: 20
max_heap_table_size   current value: 16777216
max_join_size         current value: 4294967295
max_sort_length       current value: 1024
max_tmp_tables        current value: 32
max_write_lock_count  current value: 4294967295
net_buffer_length     current value: 16384
query_buffer_size     current value: 0
record_buffer         current value: 131072
sort_buffer           current value: 2097116
table_cache           current value: 64
thread_concurrency    current value: 10
tmp_table_size        current value: 1048576
thread_stack          current value: 131072
wait_timeout          current value: 28800

もし mysqld サーバーを走らせているなら、以下のコマンドでも変数の値を見ることができます:

shell> mysqladmin variables

これらのオプションは以下のように決めてください。 buffer size, buffer length, stack size は byte 単位で与えます。 これらの値の後ろに `K'`M' を追加すると、キロバイト、メガバイトになります。 例えば、16M は16メガバイトを示します。大文字小文字の区別はなく、 16M16m は同じ意味になります。

稼働中のサーバーの統計情報は SHOW STATUS で参照できます. 「7.23 SHOW 構文 (テーブルやフィールドなどについての情報を得る)」節参照.

ansi_mode.
もし mysqld--ansi オプションで起動されているなら、ON. 「5.2 ANSI モードでの MySQL の実行」節参照.
back_log
MySQL が持てる未解決の接続要求の数です。これは MySQL スレッドがものすごく多くの接続要求をとても短い時間に得た時に、働き ます。接続のチェックと新しいスレッドの開始はメインスレッドにすこし時間 (しかしほんのわずか)がかかります。back_log は、MySQL が瞬間的に新 しい要求への回答を停止する前に、この短い時間の間にスタックできる接続数です。 短い期間に多くの接続を期待する場合にだけ、これを増加する必要があります。 いいかえるなら、これは TCP/IP 接続の入力 listen キューのサイズです。 オペレーティングシステムはこのキューの大きさを制限しています。 UNIX システムコール listen(2) のマニュアルページに、さらに詳細があります。 この値を最大限にしたい場合、お使いのOSのドキュメントを見てください。 back_log をこのOSの制限値より多く取ってもは全く効果ありません。
thread_cache_size
How many threads we should keep keep in a cache for reuse. When a client disconnects the clients threads is put in the cache if there isn't more than thread_cache_size threads from before. All new threads are first taken from the cache and only when the cache is empty a new thread is created. This variable can be increased to improve performance if you have a lot of new connections (Normally this doesn't however give a notable performance improvement if you have a good thread implementation).
concurrent_inserts
もし ON (これはデフォルトです) なら、MySQL は、 SELECT クエリが実行されている MyISAM テーブルに対して、 同時に INSERT が使用できるようにします。 このオプションは mysqld のオプションに --safe--skip-new を 指定することで OFF にできます。
connect_timeout
mysqld サーバーが接続パケットを待つ秒数。 (Bad handshake を返すまでの秒数)
delayed_insert_timeout
どれぐらい INSERT DELAYED スレッドが INSERT 文を待つべきか。
delayed_insert_limit
delayed_insert_limit 個のレコードを挿入した後、 INSERT DELAYED ハンドラーは 待たされている SELECT 文がないかチェックします。 もしあるなら、 挿入を続ける前にそれらの実行を許します。
delay_key_write
もし enabled であれば(これがデフォルト)、MySQLCREATE TABLE 文の DELAY_KEY_WRITE オプションを尊重します。 DELAY_KEY_WRITEオプションを指定されて作られたテーブルのキーバッファは、 毎回のインデックスの更新にはフラッシュされず、ただテーブルが閉じられたときにだけ フラッシュされます。 これはキーの書き出しを速くしますが、もしこれを使用するなら、 全てのテーブルを myisamchk --fast --force で自動的に検査するようにすべきです。 もし mysqld--delay-key-write_for_all_tables オプション付きで 起動した場合、これは、全てのテーブルが delay_key_write オプション指定されて 作成されたものとして扱われる事に注意してください。 このフラグは、mysqld--skip-new--safe-mode オプション で起動すれば無効にすることも出来ます。
delayed_queue_size
How big a queue (in rows) should be allocated for handling INSERT DELAYED. If the queue becomes full, any client that does INSERT DELAYED will wait until there is room in the queue again.
flush_time
これがもし、非ゼロにセットされたなら、毎 flush_time 秒ごとに 全てのテーブルが閉じられます。 (リソースの解放とDiskへのsyncのために)
init_file
サーバー起動時に、--init-file オプションに与えたファイルの名前です。 このファイルには、サーバー起動時に実行したい SQL 文を書いておきます。
interactive_timeout
The number of seconds the server waits for activity on a interactive connection before closing it. インタラクティブ クライアントを作るには、 C API mysql_real_connect()CLIENT_INTERACTIVE オプションを セットします。 See also wait_timeout.
join_buffer_size
このバッファは(インデックス無しの)完全な結合に使用されます。それは2つの テーブル間の完全な結合ごとに1回割り当てられます。インデックスの追加がで きない時、より速い完全な結合を得るために、これを増加してください。通常、 速い結合を得る一番良い方法は、インデックスを追加することです。
key_buffer_size
インデックス・ブロックはバッファされ、そして全てのスレッドに共有されます。 key_buffer_size はインデックス・ブロックのためのバッファ・サイズです。 Increase this get better index handling (for all reads and multiple writes) to as much as you can afford; 64M on a 256M machine that mainly runs MySQL is quite common. If you however make this too big (more than 50 % of your total memory?) your system may start to page and become REAL slow. Remember that because MySQL does not cache data read that you will have to leave some room for the OS filesystem cache. You can check the performance of the key buffer by doing show status and examine the variables Key_read_requests, Key_reads, Key_write_requests and Key_writes. The Key_reads/Key_read_request ratio should normally be < 0.01. The Key_write/Key_write_requests is usually near 1 if you are using mostly updates/deletes but may be much smaller if you tend to do updates that affect a lot of rows at the same time or if you are using delay_key_write. 「7.23 SHOW 構文 (テーブルやフィールドなどについての情報を得る)」節参照. To get even more speed when writing many rows at the same time use LOCK TABLES. 「7.27 LOCK TABLES/UNLOCK TABLES 構文」節参照.
lower_case_table_names
Change all table names to lower case on disk.
long_query_time
もしあるクエリがこの値(秒)より時間がかかれば、Slow_queries カウンター が増やされます。
max_allowed_packet
一つのパケットの最大サイズ。メッセージバッファは net_buffer_length バイトに初期化されますが、 max_allowed_packetまで大きくすることができます。このデフォルト値は、 大きなパケット、間違ったパケットをを受けるには小さい値です。 大きな BLOB を使用している場合は、これを増加する必要があります。使用した い最大の BLOB と同じくらい大きくするべきです。
max_connections
許される同時クライアントの数。これを増加する場合は、mysqld が持つファイ ルディスクリプタの数を増やす必要があるでしょう。 後述のファイルデスクリプターの制限の説明を参照のこと。 「20.2.4 Too many connections エラー」節参照.
max_connect_errors
もしあるホストからの接続中断がこの値を以上になった場合、これ以後、 そのホストからの接続を拒絶します。FLUSH HOSTS コマンドで ホストの拒否を解除できます。
max_delayed_threads
この値を超えて INSERT DELAYED を扱うスレッドを起動できません。 もし全ての INSERT DELAYED スレッドが使用されていて、さらに新しいテーブルに データを挿入しようとすると、そのレコードは DELAYED が与えられていない 場合と同様に挿入されます。
max_join_size
max_join_size より多いレコードを触るとエラーが返ります。長い時間をかけて 百万行を返すような WHERE なしの結合を作成するようなユーザを持って いる場合にこれを設定してください。
max_heap_table_size
Don't allow creation of heap tables bigger than this.
max_sort_length
BLOB または TEXT 項目上でソートする時に使用するバイト数。 (最初の max_sort_length バイトだけがそれぞれの値で使用でき、残りは無視されます)
max_tmp_tables
(このオプションはまだなにも行いません). クライアントが同時にオープンできるテーブル数の最大値。
max_write_lock_count
After this many write locks, allow some read locks to run in between.
net_buffer_length
通信バッファがクエリ間でこのサイズにリセットされます。これは通常は変更す べきではありませんが、とても小さなメモリしかない場合は、これを期待される クエリのサイズに設定してください。 ( これは、クライアントから送られてくるSQL文の長さ分あればいいでしょう。 もし構文がこの値をこえた場合、バッファは自動的に大きくなります。 ただし max_allowed_packet バイトまでです)
net_retry_count
If a read on a communication port is interrupted, retry this many times before giving up. This value should be quite high on FreeBSD as internal interrupts is sent to all threads.
record_buffer
順序スキャンを行う各スレッドが、スキャンするテーブル毎に、このサイズのバッ ファを割り当てます。多くの順序スキャンを行う場合は、これを増加させてくだ さい。
query_buffer_size
The initial allocation of the query buffer. If most of your queries are long (like when inserting blobs), you should increase this!
skip_show_databases
これは、 PROCESS_PRIV 権限を持っていないユーザーが SHOW DATABASES する事を阻止します。 もし、他人のデータベースや テーブルを見ようとする人がいる事を、あなたが心配するならば、 これはセキュリティを強化できます。
sort_buffer
ソートを行う必要がある各スレッドがこのサイズのバッファを割り当てます。よ り速い ORDER BY または GROUP BY のためにはこれを増やしてく ださい。 「20.5 MySQL が一時ファイルを格納する場所」節参照.
table_cache
全てのスレッドについてのオープンテーブルの数。これを増加する場合は、 mysqldが要求するオープンファイルディスクリプタの数も 増加することに注意しないといけません。 MySQL はユニークテーブル毎に2つのファイルディスクリプタを必要と します。 後述のファイルデスクリプターの制限の説明を参照のこと。 You can check if you need to increase the table cache by checking the Opened_tables variable. 「7.23 SHOW 構文 (テーブルやフィールドなどについての情報を得る)」節参照. If this variable is big and you don't do FLUSH TABLES a lot (which just forces all tables to be closed and reopenend), then you should increase the value of this variable. テーブルキャッシュがどのように働くかはこちらを参照 「12.2.4 MySQL はどのようにテーブルのオープン & クローズを行なうか?」節.
tmp_table_size
メモリー内の 一時テーブルがこの値を超えようとした場合、MySQL は 自動的に、これを、disk ベースの MyISAM テーブルに変換します。 多くの先進的な GROUP BY クエリを行う場合は、 この tmp_table_size を増加してください。
thread_stack
各スレッドのスタックの大きさ。crash-me によって検出される多く の制限がこれに依存します。デフォルトでは、通常のオペレーションに対して 十分とってあります。 「12.7 Using your own benchmarks」節参照.
wait_timeout
サーバーがコネクションを閉じるまでにアクティブなコネクションを待つ秒数。 interactive_timeout も参照のこと。

MySQL はとてもスケーラブルなアルゴリズムを使用します。そのため 通常はとても少ないメモリで動作し、またより良い性能を得るために MySQL に多くのメモリを与えることができます。

When tuning a MySQL server, the two most important variables to use are key_buffer_size and table_cache. You should first feel confident that you have got these right before trying to change any of the other variables.

多くのメモリ(>=256M)と多くのテーブルを持っていて、適度のクライアント数で最大性能を得た い場合、次のようなものを使用します:

shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \
           -O sort_buffer=4M -O record_buffer=1M &

If you have only 128M and only a few tables, but you still do a lot of sorting, you can use something like:

shell> safe_mysqld -O key_buffer=16M -O sort_buffer=1M

多くの接続で少ないメモリしかない場合、次のようなものを使用します:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
           -O record_buffer=100k &

or even:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
           -O table_cache=32 -O record_buffer=8k -O net_buffer=1K &

もしとても多くの接続があり、mysqld が各接続に対して少ないメモリーで 動作するように設定されていれば、スワップの読み書きによるパフォーマンスの低下がおきます。 もちろん十分なメモリーがあれば、全ての接続にたいして mysqld のパフォーマンス良くなります。

mysqld へのオプションを変更する場合、そのサーバのインスタンスにだけに 有効であることに注意して下さい。

パラメータ変更の効果を見るには、このようにします:

shell> mysqld -O key_buffer=32m --help

--help オプションは最後につけてください。 その他のオプションを --help の後につけると、そのオプションは反映されません。

12.2.4 MySQL はどのようにテーブルのオープン & クローズを行なうか?

table_cache , max_connections , max_tmp_tables は サーバーが開いた状態にしておく事が出来るファイルの最大数に影響します。 もしこれらの変数のうちの一つ、あるいは複数を増加させるなら、 あなたのオペレーティング・システムの、1 プロセスあたりに開かれる ファイル・デスクリプタ の最大値を増やすことになるかもしれません。 多くのシステムではこの制限を増やすことが可能です。 これをどうやって行うかは、あなたの使用している OS のドキュメントを見てください。 制限値の変更方法は、システムによってまちまちだからです。

table_cachemax_connections に関連します。 例えば 200 のコネクションを同時に開けるなら、少なくとも 200 * n の テーブル・キャッシュが必要です。 ここで n は join におけるテーブルの 最大数です。

オープンテーブルのキャッシュは最大で table_cache まで大きくなります(デ フォルトは 64, mysqld のオプション -O table_cache=# で変更可能)。 キャッシュが一杯になって、他のスレッドがテーブルのオープンを試みた時、 または mysqladmin refresh mysqladmin flush-tables を使用した場合を除いて、テーブルはクローズされません。

キャッシュがいっぱいになった場合、サーバーは以下の処置を取って キャッシュを使用できるように配置します:

テーブルは各同時アクセスに (再び) オープンされます。これは、同じテーブルで2つのスレッ ドが実行されている場合、または同じクエリで(AS で)テーブルを2回アクセス する場合、テーブルは2回オープンする必要があることを意味します。最初のテーブルのオー プンは2つのファイル記述子を使用し、続くテーブルの各使用は1つだけのファイル記述 子を使用します。

12.2.5 Drawbacks of creating large numbers of tables in the same database

もしたくさんのファイルが一つのディレクトリにある場合、オープン、クローズ、 作成操作は遅くなるでしょう、 もし SELECT 文を多くのテーブルに対し 実行した場合、テーブルキャッシュが一杯ならば、このオーバーヘッドは多くなるでしょう。 なぜなら、それぞれのテーブルにつきオープンし、クローズしなくてはならないからです。 このオーバーヘッドを緩和するには、テーブルキャッシュを大きくします。

12.2.6 何故そんなに多くのテーブルをオープンするのか?

mysqladmin status を実行すると、次のようなものが得られます:

Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

確かにたった6個しかテーブルがないのに、このような結果が出るので少々戸惑われるかもしれません。

MySQL はマルチスレッドなので、同じテーブルで一度に多くのクエリを持て ます。同じファイル上で異なる状態を持つ2つのスレッドで、問題を最小化する ため、同時に動作する各スレッドのためテーブルを再びオープンします。これはいくつ かのメモリとデータファイルについての一つの拡張ファイル記述子を使用します。 インデックスファイル記述子は全てのスレッド間で共有されます。

12.2.7 MySQL はどのようにメモリを使用するのか?

以下に mysqld サーバーがどのようにメモリーを使用するか、いくつか示します。 サーバーに与える変数名は、サーバーのメモリーの使用方に関連した名前となっています。

mysqld 実行時、ps や他のプログラムは、それが多くのメモリ を使用していると報告するでしょう。これは異なったメモリアドレス上のスレッ ドスタックによって発生します。例えば、Solaris ps はスタック間の未使用メ モリを使用メモリとして計算します。'swap -s' で有効なスワップをチェックす ることでこれを確かめられます。我々は市販のメモリリーク検出プログラムで mysqld をテストしました。そのため、メモリリークは全くありません。

12.2.8 MySQL はどのようにテーブルをロックするか

MySQL の全てのロックはデッドロックフリーです。これは、常にクエ リ開始時に一度ロックを必要とする全てを要求し、常に同じ順でテーブルをロッ クすることで管理されます。

MySQL が使用する WRITE ロックは次のように働きます:

MySQLREAD ロックは次のように動きます:

ロックが解放されたとき、最初に write ロックキュー内のスレッドに、その後 read ロックキュー内のスレッドにロックを与えます。

これは、同じテーブルで多くの更新をする場合、SELECT 構文は update がなくなるまで待たされることを意味します。

同じテーブルで多くの INSERT と多くの SELECT を行う場合、これを解決するに は、他のテーブルに行を挿入して、たまに、その一時テーブルから全てのレコー ドをもう一方のテーブルに update します。

これは次のコードで行えます:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> insert into real_table select * from insert_table;
mysql> delete from insert_table;
mysql> UNLOCK TABLES;

決まりきった検索に優先権を与えるため、INSERT 構文で LOW_PRIORITYHIGH_PRIORITY オプションが使用できます。 「7.16 INSERT構文」節参照.

一つのキューだけを使用するように `mysys/thr_lock.c' 内のロックコードを変更 することもできます。この場合、いくつかのアプリケーションのために、 write ロックは read ロックと同じ優先順位にします。

12.2.9 テーブル・ロッキングの問題

MySQL のテーブル・ロッキングのコードはデッドロック・フリーです。

MySQL はとても速いロックスピードを得るために、 (レコードのロックやフィールドのロックの代わりに) テーブルのロックを使用します。 大きなテーブルには、 テーブルのロックはレコードのロックよりはるかに良いですが、 いくつかの落とし穴があります。

MySQL 3.23.7 以上では、あるスレッドが MyISAM テーブルを読んでいる最中に、 同じテーブルにレコードを挿入する事ができます。 これは現在、削除がない場合にのみ動きます。

テーブルのロックはたくさんのスレッドが同時にあるテーブルから読み出すのを可能にしますが、 もしあるスレッドがあるテーブルに対し書込み要求を出す場合、 それはまず最初に排他的なアクセスを得なければなりません. その更新の間、更新が完了するまで、この特定のテーブルにアクセスしようとする 他の全てのスレッドが待たされることになります。

通常データベースの更新は SELECT よりも重要とされるため、 テーブルを更新する全てのスレッドが、テーブルから情報を検索するスレッドよりも 高い優先順位を持ちます。 これは ある特定のテーブルに対して、多くの重いクエリが発行され た場合に、 更新が不完全に終わらないことを確実にするためです。

MySQL 3.23.7 から、max_write_lock_count 変数が使用できます。 これは、一つのテーブルに対して、指定された回数だけ INSERT が行われた後に、 SELECT を発行するようにします。

この1つの主な問題が以下です:

この問題のいくつかの可能な解決は以下の通りです:

12.3 可能な限りデータを小さくする

One of the most basic optimization is to get your data (and indexes) to take as little space on the disk (and in memory) as possible. This can give huge improvements because disk reads are faster and normally less main memory will also be used. Indexing also takes less resources if done on smaller columns.

MySQL supports a lot of different table types and row formats. Choosing the right table format may give you a big performance gain. 「8 MySQL table types」節参照.

以下に挙げるテクニックを使用すれば、テーブルでのパフォーマンスの向上、 保存領域の縮小化が可能でしょう:

12.4 MySQL はどのようにインデックスを使用するか?

Indexes are used to find find a row with a specific calue on one column fast. インデックスがない場合、MySQL は、そのレコードが見つかるまで、 最初のレコードからテーブルをなめていきます。 大きなテーブルでは多くの 手間がかかります。 もし問い合わせの中にインデックスを持ったフィールドが ある場合、MySQL は全てのデータをみることなく、データの途中の位置を速く 得ることができます。 もしあるテーブルが1000レコード持っていたとすると、 順番に頭からなめていくことに比べて、これは少なくとも100倍速いことに なります。 Note that is you need to access almost all 1000 rows it is faster to read sequentially because we then avoid disk seeks.

全ての MySQL インデックス, PRIMARY, UNIQUE そして INDEX() は B tree に格納されます。文字列は自動的に始めと終りの空白が圧縮されます。 「7.30 CREATE INDEX 構文」節参照.

Indexes are used to:

以下のような SELECT 文を発行したとします:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

もし col1col2 に複数フィールドインデックスが定義されている場合、 すぐに思ったとおりの結果が得られます。 If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index will find fewer rows and using that index to fetch the rows.

もしテーブルが複数フィールドインデックスを持つなら、インデックスの接頭部一番左の部分 がレコードを見つけるための最適化に使用されます。例えば、3つのフィールド (col1,col2,col3) に対して一つのインデックスを持っていたとします。 すると、これは (col1), (col1,col2) , (col1,col2,col3) でインデックスがサーチされます。

もし一番左に指定しているフィールドがインデックスを作成していないなら、 MySQL は部分的なインデックスを使用しません。

以下のような SELECT 文を発行したとします:

mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

もしインデックスが (col1,col2,col3) にあるならば、 上に示した最初のクエリだけがインデックスを使用します。二番目三番目のクエリは インデックスを持つフィールドを発動しますが、 (col2)(col2,col3) は、(col1,col2,col3) の 接頭部一番左に指定されていません。

MySQL は、もし LIKE がワイルドカードから始まっていなくて、 ある種固定された文字で始まっているなら、 LIKE の評価にインデックスを使用します。 例えば、以下の SELECT 文はインデックスを使用します:

mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

一つ目の文は、"Patrick" <= key_col < "Patricl" となるレコードだけ、考慮されます。 二つ目の文は、"Pat" <= key_col < "Pau" となるレコードだけ、考慮されます。

以下の SELECT 文はインデックスを使用しません:

mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col;

最初の文は、LIKE がワイルドカード文字で始まっています。 二つ目の文は、LIKE が定数ではありません。

Searching using column_name IS NULL will use indexes if column_name is a index.

MySQL は通常、一番少ないレコード数を見つけるインデックスを使用します。 インデックスは、以下に示す演算子を用いて比較できるフィールドに対して、使用されます: =, >, >=, <, <=, BETWEEN そして 'something%' の様に頭にワイルドカードがない LIKE

WHERE 節内の全ての AND にかからないインデックスは、 全くクエリの最適化に使用されません。 In other words: To be able to use an index, a prefix of the index must be used in every AND group.

以下の WHERE 節はインデックスを使用します:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
          /* optimized like "index_part1='hello'" */
... WHERE index1=1 and index2=2 or index1=3 and index3=3;
          /* Can use index on index1 but not on index2 or index 3 */

以下の WHERE 節はインデックスを使用しません

... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* Index is not used in both AND parts */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows */

12.5 データをアクセス・更新するクエリの速度

最初に、全てのクエリに影響する一つの事柄をのべます: より複雑な権限の設定を行うと、オーバーヘッドが多くなります。

もしあなたがいかなる GRANT 文も行っていなければ、 MySQL はパーミッションの検査を少ししか最適化しないでしょう。 So if you have a very high volume it may be worth the time to avoid grants. Otherwise more permission check results in a larger overhead.

もし MySQL 関数のあるものが確実に問題を引き起こしているのならば、 常に MySQL クライアント側は以下のようになります:

mysql> select benchmark(1000000,1+1);
+------------------------+
| benchmark(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)

上の例では MySQL は 1,000,000 + 式を 0.32 秒で実行できています (たんなる PentiumII 400MHz マシンで)。

全ての MySQL 関数は最適化されていますが、 いくつかは例外があるかもしれません。 benchmark(loop_count,expression)はあなたのクエリの 問題を見つけるためのとてもよいツールです。

12.5.1 クエリの性能評価

ほとんどの場合、ディスク・シークを数えることでだいたいのパフォーマンスを予測できます。 小さなテーブルでは、通常、1回のディスク・シークでレコードを見つけれるでしょう (インデックスがたぶんキャッシュされるので)。 大きなテーブルでは、 おおよその予測として、(B++ ツリーインデックスを使用している場合)、 log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1 シーク、1レコードを見つけるのに必要となるでしょう。

MySQL では、インデックス・ブロックは通常 1024 バイトで、 データ・ポインターは通常 4 バイトです。 これは、 インデックス長が 3 (medium integer) 、データが 500,000 レコードあるテーブルでは、 log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 シークとなります。

As the above index would require about 500,000 * 7 * 3/2 = 5.2M, (assuming that the index buffers are filled to 2/3 (which is typical) ) you will probably have much of the index in memory and you will probably only need 1-2 calls to read data from the OS to find the row.

書き込み時には、上の場合、新しいインデックスを書き込める場所を 探し出すのに、4 シークかかり、さらに、通常、インデックスを更新し、 レコードを書くのに 2 シーク必要です。

Note that the above doesn't mean that your application will slowly degenerate by N log N! As long as everything is cached by the OS or SQL server things will only go marginally slower while the table gets bigger. After the data gets too big to be cached, things will start to go much slower until your applications is only bound by disk-seeks (which increase by N log N). To avoid this increase the index cache as the data grows. 「12.2.3 サーバーパラメーターのチューニング」節参照.

12.5.2 SELECT クエリの速度

通常、遅い SELECT ... WHERE を速くするには、 まず最初にインデックスがあるかどうかをチェックします。 「12.4 MySQL はどのようにインデックスを使用するか?」節参照. 違うテーブルを参照する場合には、普通はインデックスをともなうべきです。 EXPLAIN コマンドを使用すれば、SELECT でどのインデックスが 使用されているか確認できます。 「7.24 EXPLAIN 構文 (SELECTについての情報を得る)」節参照.

Some general tips:

12.5.3 MySQL はどのように WHERE 節を最適化するか?

where の最適化は、where がほとんど SELECT で使用されるため、 SELECT に置かれています。 しかし、同じ最適化は DELETEUPDATE 文でも使用されます。

このセクションの最適化の説明はまだ不十分です。 なぜなら、 MySQL はとても多くの最適化を行っており、 それら全てについての説明を書ける時間が、我々はとれません。

MySQLによる最適化のいくつかを以下に示します:

以下はとても速いクエリの例です:

mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
           WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

以下のクエリはインデックスツリーのみを使用します(インデックス化されているフィールドは 数値型と仮定します):

mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM tbl_name
           WHERE key_part1=val1 AND key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

以下のクエリは、ソートされた行の検索にインデックスを使用します:

mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...
mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...

12.5.4 MySQL はどのように LEFT JOIN を最適化するか?

A LEFT JOIN BMySQL に以下のように組みこまれています:

The table read order forced by LEFT JOIN and STRAIGHT JOIN will help the join optimizer (which calculates in which order tables should be joined) to do its work much quickly as there are fewer table permutations to check.

Note that the above means that if you do a query of type:

SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key

Then MySQL will do a full scan on b as the LEFT JOIN will force it to be read before d.

The fix in this case is to change the query to:

SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key

12.5.5 MySQL はどのように LIMIT を最適化するか?

In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING:

12.5.6 INSERT クエリの速度

レコードを挿入する時間はおおよそ次からなります:

ここで (数字) は比例時間です。これは、テーブルのオープンにかかる初期オーバーヘッ ド(これは同時に動作する各クエリ毎に1回行なわれます)は考慮されていません。

テーブルのサイズはインデックスの挿入を N log N で遅くします (B-tree)。

INSERT を速くするいくつかの方法:

LOAD DATA INFILEINSERT を共に速くするためには、 キーバッファを増やします。 「12.2.3 サーバーパラメーターのチューニング」節参照.

12.5.7 UPDATE クエリの速度

UPDATE クエリは SELECT クエリのように最適化されますが、 書き込みのオーバーヘッドが加わります。 書き込み速度は、更新されるデータの大きさ、更新されるインデックス数に 依ります。 変更されないインデックスは更新されません。

Also another way to get fast updates is to delay updates and then do many updates in a row later. 一文で多くの更新を行えば、もしテーブルがロックされていれば、 一文で一個づつ更新するよりも、とても速くなります。

Not that with dynamic record format updating a record with to a longer total length may split the record. So if you do this often it is very important to OPTIMIZE TABLE sometimes. 「7.10 OPTIMIZE TABLE構文」節参照.

12.5.8 DELETE クエリの速度

1レコードを削除する時間は、ちょうどインデックスの数に比例します。 より速くレコードを削除したいなら、インデックス・キャッシュを 増やします。 「12.2.3 サーバーパラメーターのチューニング」節参照.

テーブル中からレコードの大きな部分を消すよりも、 全てのレコードを消すほうが、とても速いです。

12.6 最適化に関するその他の助言

Unsorted tips for faster systems:

12.7 Using your own benchmarks

You should definitely benchmark your application and database to find out where is the bottlenecks. By fixing it (or by replacing the bottleneck with a 'dummy module') you can then easily identify the next bottleneck (and so on). Even if the overall performance for your application is 'good enough' you should at least make a 'plan', for each bottleneck, how to solve it if you someday 'really need it fix it'.

For some example portable benchmark programs look at the MySQL benchmark suite. 「13 MySQL ベンチマークスイート」節参照. You can take any program this suite and modify it for your needs. By doing this, you can try different solutions to your problem and test which is really the fastest solution for you.

It is very common that some problems only occur then the system is very heavily loaded. And we have had many customer who contacts us then they have a (tested) system in production and have have got load problems. In every on these cases so far it has been problems with basic design (table scans are NOT good at high load) or OS/Library issues. Most of this would be a LOT easier to fix if the system where not already in production.

To avoid probles like this you should put some effort into benchmarking your whole appliction under the worst possible load!

12.8 Design choices

MySQL は、レコードのデータとインデックスを別のファイルに保存します。 他の多くの(ほとんど全て)データベースでは、同じファイルにデータとインデックスを 混在させて保存します。 我々は、最近のシステムには、MySQL の選択の方が 良いと信じています。

Another way to store the row data is to keep the information for each column in a separate area (examples are SDBM and Focus). This will get a performance hit for every query that access more than one column. Because this degenerates so quickly when more that when one columns are accessed we believe that this model is not good for general purpose databases.

The more common case is there the index and data are stored together (like in Oracle/Sybase at all). In this case you will find the row information at the leaf page of the index. The good thing with this layout is that it in many cases (depends on how well the index is cached) saves a disk read. The bad things with this layout is:

*
Table scanning is much slower because you have to read through the indexes to get at the data.
*
You loose a lot of space as you must duplicate indexes from the nodes (as you can't store the row in the nodes)
*
Deletes will degenerate the table over times (as indexes in nodes are usually not updated on delete).
*
You can't use only the index table to retrieve data for a query.
*
The index data is harder to cache.

12.9 MySQL design limitations/tradeoffs

Because MySQL uses extremely fast table locking (multiple readers / single writers) the biggest remaining problem is a mix of a steady stream of inserts and slow selects on the same table.

We belive that for a huge number of systems the extremely fast performance in other cases make this choice a win. This case is usually also possible to solve by having multiple copies of the table. But it takes more effort and hardware.

We are also working on some extension to solve this problem for some common application niches.

12.10 Portability

Because all SQL servers implement different parts of SQL it takes work to write portable SQL applications. For very simple selects/inserts it is very easy but the more you need the harder it gets. And if you want a application that is fast with many databases it becomes even harder!

To make a complex application portable you need to choose a number of SQL server that it should work with.

When you can use the MySQL crash-me program/web-page http://www.mysql.com/crash-me-choose.htmy to find functions, types and limits you can use with a selection of database servers. Crash-me now test a long way from everything possible but it still is vエcomprehensive with about 450 things tested.

For example, you shouldn't have longer column names than 18 characters if you want to be able to use Informix or DB2.

Both the MySQL benchmarks and Crash-me programs are very database independent. By taking a look of how we have handled this, you can get a feeling of what you have to do to write your application database independent. The benchmark themselves can be found in the `sql-bench' directory in the MySQL source distribution. They are written in Perl with DBI database interface (which solves the access part of the problem.

See http://www.mysql.com/benchmark.html the results from this benchmark.

As you can see in these results all databases has some weak points. That is they have different design compromises that lead to different behavior.

If you strive for database independence you need to get a good feeling of each SQL servers bottlenecks. MySQL is VERY fast in retrieving and updating things, but will have a problem in mixing slow readers/writers on the same table. Oracle on the other hand has a big problem when you try to access rows that you have recently updated (until they are flushed to disk). Transaction databases in general are not very good in generating summary tables from log tables as in this case row locking is almost useless.

To get your application 'really database independent' you need to define a easy extendable interface through which you manipulate your data. As C++ is available on most systems, it makes sense to use a C++ classes interface to the databases.

If you use some specific feature for some database (like the REPLACE command in MySQL), you should code a method for the other SQL servers to implement the same feature (but slower). With MySQL you can use the /*! */ syntax to add MySQL specific keywords to a query. The code inside /**/ will be treated as a comment (ignored) by most other SQL servers.

If REAL high performance is more important than exactness, like in some web applications. A possibility is to create a application layer that caches all results to give you even higher performance. By just letting old results 'expire' after a while you can keep the cache reasonable fresh. This is quite nice in case of extremely high load, in which case you can dynamically increase the cache to be bigger and set the expire timeout higher until things gets back to normal.

In this case the table creating information should contain information of the initial size of the cache and how often the table should normally be refreshed.

12.11 What have we used MySQL for?

MySQL の初期の開発期には、MySQL は我々のもっとも大口の顧客に合うように 機能が作成されました。 彼らは、いくつかのスウェーデン最大手の小売り業者 のために、倉庫に入れている(商品)データを取り扱います。

我々は、すべての店から、全ボーナス・カード取扱高の、その週間まとめを得ます。 そして、その店のオーナにとって有益な情報、その店の広告キャンペーンが お客にどの程度影響を及ぼすか、を提供することが、我々に求められています。

そのデータは、とても大きくて (約700万/月 回の取り扱い)、 我々はその顧客に提供する必要のあるデータを 4〜10年分、持っています。 我々は、カスタマーから、彼らがこのデータからできる新しいレポートに ”即時に”アクセスしたいという、要求を受けました。

我々はこれを、全ての月ごとの情報を圧縮した 'transaction' テーブルに 保存することで解決しました。 We have a set of simple macros/script that generate summary tables grouped by different criterias (product group, customer id, store ...) from the transaction tables. そのレポートは Web ページで、これは小さな perl スクリプトで動的に 作成されます。 この perl script は Web Page を分析し、SQL 文を 実行し、結果を挿入します。 Now we would have used PHP or mod_perl instead but they where not available at that time.

画像データのために、我々は簡単なツールを C でかきました。 これは SQL のクエリの結果を元に(結果にいくつか処理をして) GIF を提供します。 これも動的に perl スクリプト(HTML ファイルを分析する)から実行されます。

In most cases a new report can simple by done by copying a existing script and modifying the SQL query in it. In some cases we will need to add more fields to an existing summary table or generate a new one, but this is also quite simply as we keep all transactions tables on disk. (Currently we have at least 50G of transactions tables and 200G of other customer data).

We also let our customers access the summary tables directly with ODBC so that the advanced users can themselves experiment with the data.

我々はこれらを Sun Ultra sparcstation (2x200 Mz) で扱っていますが、 なんの問題もありません。 We recently upgrade one of our servers to a 2 CPU 400 Mz Ultra sparc and we are now planing to start handling transactions on the product level, which would mean a 10 fold increase of data. We think we can keep up with this by just adding more disk to our systems.

We are also experimenting with Intel-Linux to be able to get more cpu power cheaper. 現在、我々はバイナリ互換のデータベースフォーマットを持っており (new in 3.23) 、我々はこれをアプリケーションのいくつかの部分に使用しはじめる事でしょう。

Our initial feelings are that Linux will perform much better on low to medium load but Solaris will perform better when you start to get a a high load because of extrema disk IO, but we don't yet have anything conclusive about this. After some discussion with a Linux Kernel developer this might be a side effect of Linux giving so much resources to the batch job that the interactive performance gets very low. This make the machine feel very slow and unresponsive while big batches are going. Hopefully this will be better handled in future Linux Kernels.


Go to the first, previous, next, last section, table of contents.