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


5 MySQL Optimization

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 or application, the more optimal you want your system to become the more you will have to know about it.

This chapter will try to explain and give some examples of different ways to optimise MySQL. Remember, however, that there are always some (increasingly harder) additional ways to make the system even faster.

5.1 最適化 概要

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

主なボトルネックは:

5.1.1 MySQL Design Limitations/Tradeoffs

When using the MyISAM storage engine, MySQL uses extremely fast table locking (multiple readers / single writers). The biggest problem with this table type is a if you have a mix of a steady stream of updates and slow selects on the same table. If this is a problem with some tables, you can use another table type for these. 「7 MySQL テーブル型」節参照.

MySQL can work with both transactional and not transactional tables. To be able to work smoothly with not transactional tables (which can't rollback if something goes wrong), MySQL has the following rules:

The reason for the above rules is that we can't check these conditions before the query starts to execute. If we encounter a problem after updating a few rows, we can't just rollback as the table type may not support this. We can't stop because in that case the update would be 'half done' which is probably the worst possible scenario. In this case it's better to 'do the best you can' and then continue as if nothing happened.

The above means that one should not use MySQL to check fields content, but one should do this in the application.

5.1.2 Portability

全てのSQLサーバはそれぞれ異なるSQL処理ルーチンにより構成されています。 したがって色々なSQLサーバ上で同じように動作するような(ポータブルな) SQLアプリケーションを書くのは手間がかかります。 確かに単純なインサートやセレクトを行うようなものは簡単ですが、 必要な機能が増えれば増えるほど難しくなっていきます。 高速動作を要求されるSQLプラットフォームの数が増えれば増えるほど そのようなアプリケーションの開発は大変になっていきます。

ポータブルな多機能アプリケーションを開発するためには 沢山のSQLサーバでの動作を検証する必要があります。

You can use the MySQL crash-me program/web-page http://www.mysql.com/information/crash-me.php to find functions, types, and limits you can use with a selection of database servers. Crash-me now tests far from everything possible, but it is still comprehensive with about 450 things tested.

For example, you shouldn't have column names longer 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 at how we have handled this, you can get a feeling for what you have to do to write your application database-independent. The benchmarks 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/information/benchmarks.html for the results from this benchmark.

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

If you strive for database independence, you need to get a good feeling for each SQL server's 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 at 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 an 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 high performance is more important than exactness, as in some web applications, it is possibile to create an application layer that caches all results to give you even higher performance. By letting old results 'expire' after a while, you can keep the cache reasonably fresh. This provides a method to handle high load spikes, in which case you can dynamically increase the cache and set the expire timeout higher until things get back to normal.

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

5.1.3 What Have We Used MySQL For?

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

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

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

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

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

In most cases a new report can simply be done by copying an 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 simple, 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 upgraded one of our servers to a 2 CPU 400 Mhz UltraSPARC, and we are now planning to start handling transactions on the product level, which would mean a ten-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 Version 3.23) 、我々はこれをアプリケーションのいくつかの部分に使用しはじ める事でしょう。

Our initial feelings are that Linux will perform much better on low-to-medium load and Solaris will perform better when you start to get a high load because of extreme 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 makes the machine feel very slow and unresponsive while big batches are going. Hopefully this will be better handled in future Linux Kernels.

5.1.4 The MySQL Benchmark Suite

This should contain a technical description of the MySQL benchmark suite (and crash-me), but that description is not written yet. Currently, you can get a good idea of the benchmark by looking at the code and results in the `sql-bench' directory in any MySQL source distributions.

This benchmark suite is meant to be a benchmark that will tell any user what things a given SQL implementation performs well or poorly at.

Note that this benchmark is single threaded, so it measures the minimum time for the operations. We plan to in the future add a lot of multi-threaded tests to the benchmark suite.

For example, (run on the same NT 4.0 machine):

Reading 2000000 rows by index Seconds Seconds
mysql 367 249
mysql_odbc 464
db2_odbc 1206
informix_odbc 121126
ms-sql_odbc 1634
oracle_odbc 20800
solid_odbc 877
sybase_odbc 17614
Inserting (350768) rows Seconds Seconds
mysql 381 206
mysql_odbc 619
db2_odbc 3460
informix_odbc 2692
ms-sql_odbc 4012
oracle_odbc 11291
solid_odbc 1801
sybase_odbc 4802

In the above test MySQL was run with a 8M index cache.

We have gathered some more benchmark results at http://www.mysql.com/information/benchmarks.html.

Note that Oracle is not included because they asked to be removed. All Oracle benchmarks have to be passed by Oracle! We believe that makes Oracle benchmarks very biased because the above benchmarks are supposed to show what a standard installation can do for a single client.

To run the benchmark suite, you have to download a MySQL source distribution, install the Perl DBI driver, the Perl DBD driver for the database you want to test and then do:

cd sql-bench
perl run-all-tests --server=#

where # is one of supported servers. You can get a list of all options and supported servers by doing run-all-tests --help.

crash-me tries to determine what features a database supports and what its capabilities and limitations are by actually running queries. For example, it determines:

We can find the result from crash-me on a lot of different databases at http://www.mysql.com/information/crash-me.php.

5.1.5 Using Your Own Benchmarks

You should definitely benchmark your application and database to find out where the bottlenecks are. 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 sufficient, you should at least make a plan for each bottleneck, and decide how to solve it if someday you really need the extra performance.

For an example of portable benchmark programs, look at the MySQL benchmark suite. 「5.1.4 The MySQL Benchmark Suite」節参照. You can take any program from 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 when the system is very heavily loaded. We have had many customers who contact us when they have a (tested) system in production and have encountered load problems. In every one of 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 systems were not already in production.

To avoid problems like this, you should put some effort into benchmarking your whole application under the worst possible load! You can use Super Smack for this, and it is available at: http://www.mysql.com/Downloads/super-smack/super-smack-1.0.tar.gz. As the name suggests, it can bring your system down to its knees if you ask it, so make sure to use it only on your development systems.

5.2 Optimizing SELECTs and Other Queries

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

もしあなたがいかなる 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)はあなたのクエリの 問題を見つけるためのとてもよいツールです。

5.2.1 EXPLAIN 構文 (SELECTについての情報を得る)

    EXPLAIN tbl_name
or  EXPLAIN SELECT select_options

EXPLAIN tbl_name は、 DESCRIBE tbl_nameSHOW COLUMNS FROM tbl_name と同義です。

もし EXPLAIN をともなって SELECT 構文を実行した場合、 MySQL はこの SELECT がいかに動作するかを説明し、 いかにテーブルが結合されるかの情報を与えます。

EXPLAIN の情報を元に、インデックスを使用した速い SELECT を 得るためにテーブルにインデックスを加えなくてはならないという事がわかります。

You should frequently run ANALYZE TABLE to update table statistics such as cardinality of keys which can affect the choices the optimiser makes. 「4.5.2 ANALYZE TABLE Syntax」節参照.

テーブル結合の最適化もオプションによって見ることができます。 SELECT 構文での結合を強制的に最適化するには STRAIGHT_JOIN 節を加え ます。

単純ではない join のために、EXPLAINSELECT 文で使用されている それぞれのテーブルの情報を返します。 テーブルは読まれる順に表示されます。MySQL は one-sweep multi-join method を用いた全ての join を解決します。これは MySQL は最初のテーブルから 一レコード読み込み、二つ目のテーブルからマッチしたレコードを探し、そして三番目を 探すということです。 全てのテーブルが処理される時、選択されたフィールドを出力し、テーブルの一覧は よりマッチするレコードをもつテーブルを見つけるまで back-track されます。 次のレコードはこのテーブルから読まれ、次のテーブルから処理を続けます。

In MySQL version 4.1 the EXPLAIN output was changed to work better with constructs like UNIONs, subqueries and derived tables. Most notable is the addition of two new columns: id and select_type.

EXPLAIN の出力は以下のフィールドを含みます:

id
SELECT identifier, the sequential number of this SELECT within the query.
select_type
Type of SELECT clause, which can be any of the following:
SIMPLE
Simple SELECT (without UNIONs or subqueries).
PRIMARY
Outermost SELECT.
UNION
Second and further UNION SELECTs.
DEPENDENT UNION
Second and further UNION SELECTSs, dependent on outer subquery.
SUBSELECT
First SELECT in subquery.
DEPENDENT SUBSELECT
First SELECT, dependent on outer subquery.
DERIVED
Derived table SELECT.
table
出力レコードが参照されるテーブル
type
join タイプ. join type は以下のものがあります。良い物から順に書いています:
system
テーブルが一レコードだけ持っている (= system table). これは const join type の特別な場合です。
const
テーブルは、最もマッチするレコードを1つもっており、これはクエリの 最初に読まれます。 1 つのレコードであるため、このレコード中のフィールドの値は オプティマイザーによって常数としてみなされます。 1回だけ読まれるので、const テーブルはとても速いです!
eq_ref
前のテーブルのそれぞれのレコードと結合する際、このテーブルから1レコード読まれま す。これは join では const よりも良い形です。 インデックスの全てのパートが join で使用され、かつ、インデックスが UNIQUEPRIMARY KEY であるときに、これは使用されます。
ref
インデックスの値に合ったすべてのレコードは、前のテーブルからレコードと結合するた めに、このテーブルから読まれるでしょう。 もしその join がキーの一番左の接頭部分だけを使用するならば、 あるいは、もしそのキーが UNIQUEPRIMARY KEY でなければ (言い換えるなら、もし join がキーの値を元に一つだけの、レコードを選択できなけれ ば)、ref は使用されます。 もしそのキーがいくつかのマッチするレコードに使用されるだけなら、 join は良い形です。
range
示された範囲内にあるレコードのみが検索されます。 key 項目はどのインデックスが使用されているか示します。 The key_len contains the longest key part that was used. The ref column will be NULL for this type.
index
ALL と同じですが、インデックスツリーが走査される場合のみを除きます。 これは、インデックスファイルはデータファイルよりも小さいため、通常 ALL よ り速いです。
ALL
前のテーブルのレコードとのそれぞれの結合において、全テーブルが走査されます。 もしそのテーブルが最初のテーブルで const 状態ではないなら、通常 これは良くありません。他の状態ではとても悪くなります。 これは普通、レコードががより早いテーブルからから定数値に基づいて検索することがで きるように、 インデックスを追加することにより ALL を避けることが可能です。
possible_keys
possible_keys 項目は、MySQL がテーブルからレコードを見つけるために どのインデックスを使用する事ができたかを示します。 注意: このフィールドはテーブルの順にまったく依存しません。これは、 possible_keys 内のいくつかのキーは、生成されたテーブル順での実行に使用でき ないことを意味します。 この項目が空なら、関連した インデックスは無いということです。この場合、あなたは WHERE 節を 調べることによって、クエリの性能を向上させることができるかもしれません。 もしそれがインデックスに適合したフィールドを参照しているならば。 仮にそうだとすると、適切なインデックスを作成し、 EXPLAIN でクエリを もう一度チェックしてみてください。 テーブルがどんなインデックスを持っているかみるには、SHOW INDEX FROM tbl_name とします。
key
key 項目は、 MySQL が使用すると実際に決めたキー(インデックス)を示します。 どのインデックスも選ばれなかったならば、インデックスは NULL です。 MySQL に possible_keys 項目にあるインデックスを使用するように 強制するにはクエリに USE KEY/IGNORE KEY を使用することができます。 「6.4.1 SELECT 構文」節参照. Also, running myisamchk --analyze ( 「4.4.6.1 myisamchk 起動構文」節参照) or ANALYZE TABLE ( 「4.5.2 ANALYZE TABLE Syntax」節参照) on the table will help the optimiser choose better indexes.
key_len
key_len 項目は、MySQL が使用すると決めたキーの長さを示します。 もし keyNULL なら、長さは NULL です。 注意: これはMySQL がマルチパートキーのいくつのパートを実際に使用 するかを示します。
ref
ref 項目は、テーブルからレコードを select するために、どのフィールドや 定数が key と共に使用されたかを示します。
rows
rows フィールドは、MySQL がクエリを実行するために検査する 必要があると考えているレコードの数を示します。
Extra
このフィールドは、MySQL がどのようにクエリを解決するかの追加情報 を含んでいます。ここで、このフィールドに現れ得る様々なテキスト文字列の説明 をします:
Distinct
MySQL will not continue searching for more rows for the current row combination after it has found the first matching row.
Not exists
MySQL はクエリの LEFT JOIN 最適化を行なうことができて、 LEFT JOIN 判定基準に適合する1つのレコードを見つけた後、前のレコード組 み合わせについてこのテーブルにそれ以上のレコードを検査しません。 Here is an example for this:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
Assume that t2.id is defined with NOT NULL. In this case MySQL will scan t1 and look up the rows in t2 through t1.id. If MySQL finds a matching row in t2, it knows that t2.id can never be NULL, and will not scan through the rest of the rows in t2 that has the same id. In other words, for each row in t1, MySQL only needs to do a single lookup in t2, independent of how many matching rows there are in t2.
range checked for each record (index map: #)
MySQL は使用すべき本当に良いインデックスを見つけられませんでし た。先行するテーブル内に各レコードの結合の代わりに、テーブルからレコード を取り出すためにどのインデックスを使用するかチェックを行ないます。これは とても速いというわけではありませんが、もちろんインデックス無しでの結合を 行なわなければならないよりは速いです。
Using filesort
MySQL は、ソート順でレコードを取り出す方法を見つけ出すため、余 計なパスの実行が必要です。ソートは、join type に一致する全てのレ コードを通して行なわれ、WHERE に適合する全てのレコードについて、 ソートキーとレコードへのポインタを格納します。それから、キーがソートされま す。最後にレコードはソートされた順に取り出されます。
Using index
フィールド情報は、実際のレコードから読み込む追加シークを行なうことはせず に、インデックスツリー内の情報だけを使用するテーブルから取り出されます。 テーブルについての使用される全てのフィールドが同じインデックスの一部であ る時にこれが行なわれます。
Using temporary
クエリを解決できるように、MySQL は、結果を保持するための一時テー ブルを作成する必要があります。これは通常 GROUP BY が行なわれたも のとは別のフィールド上で ORDER BY を行なう場合に発生します。
Using where
どのレコードが次のテーブルに対して適合するか、またはクライアントに送られ るかを制限するために、WHERE 節が使用されます。この情報がなくて、 テーブルが型 ALLindex であれば、クエリ内に何か間違った ものがあります(テーブルから全てのレコードを fetch/examine しようとしてい るのでなければ)。
クエリを可能な限り速く得たい場合、Using filesortUsing temporary に注意すべきです。

EXPLAIN 出力の rows フィールド内のすべての値を増やすことによ り、join がどのように良くなるかの良い表示を得ることができます。これは、 MySQL がクエリ実行の検査をする必要があるレコードのおおよその数を 知らせます。この数は max_join_size 変数でのクエリを制限する時にも使 用します。 「5.5.2 サーバーパラメーターのチューニング」節参照.

以下の例は、EXPLAIN が提供する情報を元に、いかに JOIN が最適化 できるかの例です。

以下のような EXPLAIN で検査する SELECT 構文があるとします:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;

この例では、以下のように仮定します:

最初、いかなる最適化も行われていない状態では、EXPLAIN 構文は 以下の情報を提示します:

table type possible_keys                key  key_len ref  rows  Extra
et    ALL  PRIMARY                      NULL NULL    NULL 74
do    ALL  PRIMARY                      NULL NULL    NULL 2135
et_1  ALL  PRIMARY                      NULL NULL    NULL 74
tt    ALL  AssignedPC,ClientID,ActualPC NULL NULL    NULL 3872
      range checked for each record (key map: 35)

それぞれのテーブルで、typeALL になっています。 これは MySQL が全てのテーブルを全結合することを示します! それぞれのテーブル内の行数分から作った物が調べられるので、とても長い時間がかかり ます! この場合、74 * 2135 * 74 * 3872 = 45,268,558,720 行調べることになります。 テーブルが肥大化したときにかかる時間を考えてください....

一つ問題があります。(まだ) MySQL がフィールドのインデックスを効果的に 使用できていません。 この例の場合では、VARCHARCHAR は、それらが同じ長さで定義されて いれば、変わりがありません。 tt.ActualPCCHAR(10) と定義されており、 et.EMPLOYIDCHAR(15) です。これらの長さは違います。

この不釣り合いを修正するにあたり、ALTER TABLE を使って ActualPC の長さを 10 文字から 15 文字にします:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

これで tt.ActualPC and et.EMPLOYID は両方とも VARCHAR(15) になりました。 EXPLAIN 構文を実行し直すと、以下を提示します:

table type   possible_keys   key     key_len ref         rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL 3872    Using where
do    ALL    PRIMARY         NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY         NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC 1

まだ完全ではありませんが、よりよくなっています(rows 値の 生成量は 74 より小さくなります)。この場合、実行は数秒でしょう。

A second alteration can be made to eliminate the column length mismatches for the tt.AssignedPC = et_1.EMPLOYID and tt.ClientID = do.CUSTNMBR comparisons:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
    ->                MODIFY ClientID   VARCHAR(15);

これで EXPLAIN は以下を出力します:

table type   possible_keys   key      key_len ref           rows Extra
et    ALL    PRIMARY         NULL     NULL    NULL          74
tt    ref    AssignedPC,     ActualPC 15      et.EMPLOYID   52   Using where
             ClientID,
             ActualPC
et_1  eq_ref PRIMARY         PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY         PRIMARY  15      tt.ClientID   1

これは ``ほとんど'' 最良に近いです。

残る問題は、デフォルトでは、MySQL は tt.ActualPC フィールド内の 値がまんべんなく分布していると想定しており、この tt テーブルの場合には 適合しません。 幸運にも、これを MySQL に教えるのはとても簡単です:

shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt
shell> mysqladmin refresh

これで join は ``完璧'' です。 EXPLAIN は以下の結果を示します:

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using where
             ClientID,
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

EXPLAIN の出力中の rows 項目は、 MySQL JOIN オプティマイ ザーによる、``推測'' です; クエリの最適化のために、この数値が実際に近いかどうかをチェックすべきです。 そうでなければ、SELECT ステートメントで STRAIGHT_JOIN を使用 し、FROM 節に異なる順でテーブルを並べることで、良いパフォーマンスを 得られます。

5.2.2 Estimating Query Performance

ほとんどの場合、ディスク・シークを数えることでだいたいのパフォーマンスを予測でき ます。 小さなテーブルでは、通常、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 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 log N). To avoid this, increase the index cache as the data grows. 「5.5.2 サーバーパラメーターのチューニング」節参照.

5.2.3 SELECT クエリの速度

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

Some general tips:

5.2.4 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,... ;

5.2.5 How MySQL Optimizes DISTINCT

DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table.

When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.

If you don't use columns from all used tables, MySQL will stop the scanning of the not used tables as soon as it has found the first match.

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

In the case, assuming t1 is used before t2 (check with EXPLAIN), then MySQL will stop reading from t2 (for that particular row in t1) when the first row in t2 is found.

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

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

RIGHT JOIN is implemented analogously as LEFT JOIN.

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 more 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

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

5.2.7 How MySQL Optimises ORDER BY

In some cases MySQL can uses index to satisfy an ORDER BY or GROUP BY request without doing any extra sorting.

The index can also be used even if the ORDER BY doesn't match the index exactly, as long as all the unused index parts and all the extra are ORDER BY columns are constants in the WHERE clause. The following queries will use the index to resolve the ORDER BY / GROUP BY part:

SELECT * FROM t1 ORDER BY key_part1,key_part2,...
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2
SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC

Some cases where MySQL can not use indexes to resolve the ORDER BY: (Note that MySQL will still use indexes to find the rows that matches the WHERE clause):

In the cases where MySQL have to sort the result, it uses the following algorithm:

You can with EXPLAIN SELECT ... ORDER BY check if MySQL can use indexes to resolve the query. If you get Using filesort in the extra column, then MySQL can't use indexes to resolve the ORDER BY. 「5.2.1 EXPLAIN 構文 (SELECTについての情報を得る)」節参照.

If you want to have a higher ORDER BY speed, you should first see if you can get MySQL to use indexes instead of having to do an extra sorting phase. If this is not possible, then you can do:

MySQL by default sorts all GROUP BY x,y[,...] queries as if you would have specified ORDER BY x,y[,...]. MySQL will optimise away any ORDER BY as above without any speed penalty. If you by in some cases don't want to have the result sorted you can specify ORDER BY NULL:

INSERT INTO foo SELECT a,COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

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

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

5.2.9 INSERT クエリの速度

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

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

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

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

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

5.2.10 UPDATE クエリの速度

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

Also, another way to get fast updates is to delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time if you lock the table.

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

5.2.11 DELETE クエリの速度

If you want to delete all rows in the table, you should use TRUNCATE TABLE table_name. 「6.4.7 TRUNCATE 構文」節参照.

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

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

Unsorted tips for faster systems:

5.3 Locking Issues

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

付録に、異なるロックする方法に関する議論があります。 「E.4 Locking methods」節参照.

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

InnoDB type tables automatically acquire their row locks and BDB type tables their page locks during the processing of SQL statements, not at the start of the transaction.

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

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

ロックが解放されたとき、最初に 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> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

特定の場合での検索を優先的に行いたい場合、 LOW_PRIORITY オプションと共に INSERTUPDATEDELETE 構文、または HIGH_PRIORITY オプションと共に SELECT 構文を使用できます。 また、同様の動作を得るために、 --low-priority-updates オプション と共に mysqld を開始することができます。

SQL_BUFFER_RESULT の使用はさらにテーブル・ロックをより短くするために有効かもしれません。 「6.4.1 SELECT 構文」節参照.

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

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

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

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

BDBInnoDB のテーブルでは、 LOCK TABLES を使用して 明示的にテーブルをロックするか、 ALTER TABLE のようなテーブル中のすべて のレコードを修正するコマンドを実行した場合にのみ、MySQLはテーブル・ロッキングを使用 します。これらのテーブル・タイプについては、 LOCK TABLES を全く使用 しないことを推奨します。 because InnoDB uses automatic row level locking and BDB uses page level locking to ensure transaction isolation.

MySQL バージョン 3.23.7 以上では、 あるスレッドが MyISAM テーブルを読んでいる最中に、 同じテーブルにレコードを挿入する事ができます。 現在、これは挿入が行われる時に、テーブルのレコードを削除した後にホールがない場合にのみ 動作することに注意してください。すべてのホールが新しいデータで満たされた時に、 自動的に同時挿入が再び可能になります。

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

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

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

テーブルロッキングは以下の場合にはあまり良くありません:

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

5.4 Optimizing Database Structure

5.4.1 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 cause a performance hit for every query that accesses more than one column. Because this degenerates so quickly when more than one column is accessed, we believe that this model is not good for general purpose databases.

The more common case is that the index and data are stored together (like in Oracle/Sybase et al). 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, depending on how well the index is cached, saves a disk read. The bad things with this layout are:

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

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 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. 「7 MySQL テーブル型」節参照.

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

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

Indexes are used to find rows with a specific value of one column fast. インデックスがない場合、MySQL は、そのレコードが見つかるまで、 最初のレコードからテーブルをなめていきます。 大きなテーブルでは多くの 手間がかかります。 もし問い合わせの中にインデックスを持ったフィールドが ある場合、MySQL は全てのデータをみることなく、データの途中の位置を速く 得ることができます。 もしあるテーブルが1000レコード持っていたとすると、 順番に頭からなめていくことに比べて、これは少なくとも100倍速いことに なります。 Note that if 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 に格納されます。 文字列は自動的に始めと終りの空白が圧縮されます。 「6.5.7 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 が定数ではありません。

MySQL 4.0 does another optimisation on LIKE. If you use ... LIKE "%string%" and string is longer than 3 characters, MySQL will use the Turbo Boyer-Moore algorithm to initialise the pattern for the string and then use this pattern to perform the search quicker.

Searching using column_name IS NULL will use indexes if column_name is an 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  */

Note that in some cases MySQL will not use an index, even if one would be available. Some of the cases where this happens are:

5.4.4 Column Indexes

MySQL の全てのフィールドはインデックスを持つことができます。 適切なフィールドでのインデックスの使用は、SELECT の 性能を向上する最良の方法です。

キーの最大数と最大インデックス長は storage engine 毎に定義されます。 「7 MySQL テーブル型」節参照。すべてのテーブルハンドラで少なくとも 16 個のキーと少な くとも合計 256 バイトのインデックス長を持つことができます。

CHARVARCHAR フィールドには接頭部にインデックスを持つことが できます。フィールド全体をインデックス化するより、 これははるかに早く、少ないディスク容量でできます。

CREATE TABLE 構文でフィールドにインデックスを作るには、 以下のようにします:

KEY index_name (col_name(length))

以下の例は name フィールドの最初の10文字にインデックスを創り出します:

mysql> CREATE TABLE test (
    ->        name CHAR(200) NOT NULL,
    ->        KEY index_name (name(10)));

BLOBTEXT フィールドでは、そのフィールドの頭の部分に インデックスを張らなくてはなりません。 フィールドの全体にインデックスは張れません。

MySQL バージョン 3.23.23 以降では、特殊な FULLTEXT イン デックスを生成することもできます。これは全文検索に使用されます。 MyISAM テーブル型だけが FULLTEXT インデックスをサポートしま す。これは CHAR, VARCHARTEXT フィールドだけに生成できます。イ ンデックスは常にフィールド全体に対して行なわれ、部分インデックスはサポート されません。詳細は 「6.8 MySQL Full-text Search」節.

5.4.5 Multiple-Column Indexes

MySQL は異なるフィールドのセットに一つのインデックスを持つことができます。 インデックスは最大15個のコラムまで許されます。 (CHARVARCHAR フィールドの接頭部をインデックスとして使用できます)

複数フィールドインデックスは、 ソートされた配列(インデックス化されたフィールドの値が結合されている配列) を扱うことができます。

インデックス化されたコラムに対して、既知の値を WHERE 節で指定した時、 たとえ他のフィールドの値を指定しなくとも、 MySQL は複数フィールドインデックスを使用します。

以下のテーブルがあると仮定してください:

mysql> CREATE TABLE test (
    ->       id INT NOT NULL,
    ->       last_name CHAR(30) NOT NULL,
    ->       first_name CHAR(30) NOT NULL,
    ->       PRIMARY KEY (id),
    ->       INDEX name (last_name,first_name));

name インデックスは、last_namefirst_name にまたがるイン デックスです。 このインデックスは、last_name に対するクエリや、 name インデックスは以下のクエリで使われます:

mysql> SELECT * FROM test WHERE last_name="Widenius";

mysql> SELECT * FROM test WHERE last_name="Widenius"
    ->                    AND first_name="Michael";

mysql> SELECT * FROM test WHERE last_name="Widenius"
    ->                    AND (first_name="Michael" OR first_name="Monty");

mysql> SELECT * FROM test WHERE last_name="Widenius"
    ->                    AND first_name >="M" AND first_name < "N";

しかし name インデックスは以下のクエリでは使用されません:

mysql> SELECT * FROM test WHERE first_name="Michael";

mysql> SELECT * FROM test WHERE last_name="Widenius"
    ->                    OR first_name="Michael";

MySQL がクエリの性能を上げるためにどうインデックスを使用しているか、 より詳しい情報はこちら: 「5.4.3 MySQL はどのようにインデックスを使用するか?」節.

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

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

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

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

MySQL はマルチスレッドなので、同じテーブルで一度に多くのクエリを持て ます。 同じファイル上では2つスレッドはそれぞれ別の異なる状態を持つという 問題を最小化するため、テーブルは同時に動作する各スレッドにより個々に オープンされます。 これは少しメモリを食いますが、パフォーマンスは良くなります。 ISAMMyISAM テーブルでは、データファイルのために 一つのファイル記述子を使用します。 インデックスファイル記述子は全てのスレッド間で共有されます。

You can read more about this topic in the next section. 「5.4.7 MySQL はどのようにテーブルのオープン & クローズを行なうか?」節参照.

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

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

table_cachemax_connections に関連します。 例えば 200 のコネクションを同時に開けるなら、少なくとも 200 * n の テーブル・キャッシュが必要です。 ここで n は join におけるテーブルの 最大数です。 You also need to reserve some extra file descriptors for temporary tables and files.

Make sure that your operating system can handle the number of open file descriptors implied by the table_cache setting. If table_cache is set too high, MySQL may run out of file descriptors and refuse connections, fail to perform queries, and be very unreliable. You also have to take into account that the MyISAM storage engine needs two file descriptors for each unique open table. You can in increase the number of file descriptors available for MySQL with the --open-files-limit=# startup option. 「A.2.16 File Not Found」節参照.

オープンテーブルのキャッシュは最大で table_cache まで大きくなります(デ フォルトは 64, mysqld のオプション -O table_cache=# で変更可能)。 Note that in MySQL may temporarly open even more tables to be able to execute queries.

A not used table is closed and removed from the table cache under the following circumstances:

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

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

If you are opening a table with the HANDLER table_name OPEN statement, a dedicated table object is allocated for the thread. This table object is not shared by other threads an will not be closed until the thread calls HANDLER table_name CLOSE or the thread dies. 「6.4.2 HANDLER Syntax」節参照. When this happens, the table is put back in the table cache (if it isn't full).

You can check if your table cache is too small by checking the mysqld variable Opened_tables. If this is quite big, even if you haven't done a lot of FLUSH TABLES, you should increase your table cache. 「4.5.6.3 SHOW STATUS」節参照.

5.4.8 Drawbacks to Creating Large Numbers of Tables in the Same Database

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

5.5 Optimizing the MySQL Server

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

We start with the system level things since 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. However, it is always nice to have a feeling about how much one could gain by changing things at this level.

使うべき OS はとても重要です! 複数の CPU を持つマシンでは、 Solaris (なぜなら、Solaris のスレッドはとても素晴らしく動作するから)、 あるいは、Linux ( kernel 2.2 は良い SMP をサポートしているから) を 使用すべきです。 32bit マシンの Linux では、2G bytes のファイルサイズの 制限があります。 これは新しいファイルシステム (XFS/ReiserFS) のリリース時に なくなって欲しいものです。 If you have a desperate need for files bigger than 2G on Linux-intel 32 bit, you should get the LFS patch for the ext2 file system.

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

Other tips:

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

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

shell> mysqld --help

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

Possible variables for option --set-variable (-O) are:
back_log                 current value: 5
bdb_cache_size           current value: 1048540
binlog_cache_size        current value: 32768
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_binlog_cache_size    current value: 4294967295
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
myisam_sort_buffer_size  current value: 8388608
net_buffer_length        current value: 16384
net_retry_count          current value: 10
net_read_timeout         current value: 30
net_write_timeout        current value: 60
read_buffer_size         current value: 131072
record_rnd_buffer_size   current value: 131072
slow_launch_time         current value: 2
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

Please note that --set-variable is deprecated since MySQL 4.0, just use --var=option on its own.

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

shell> mysqladmin variables

You can find a full description for all variables in the SHOW VARIABLES section in this manual. 「4.5.6.4 SHOW VARIABLES」節参照.

You can also see some statistics from a running server by issuing the command SHOW STATUS. 「4.5.6.3 SHOW STATUS」節参照.

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 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 read_buffer_size=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 read_buffer_size=100k &

or even:

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

If you are doing a GROUP BY or ORDER BY on files that are much bigger than your available memory you should increase the value of record_rnd_buffer to speed up the reading of rows after the sorting is done.

When you have installed MySQL, the `support-files' directory will contain some different `my.cnf' example files, `my-huge.cnf', `my-large.cnf', `my-medium.cnf', and `my-small.cnf', you can use as a base to optimize your system.

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

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

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

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

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

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

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

-static でのリンク時に最速の実行形式を得ます。

Linux では、pgcc-O3 でコンパイルした時に最速のコードを得られます。 このオプションで `sql_yacc.cc' をコンパイルすると、gcc/pgcc は 全ての関数をインラインにするために多くのメモリを必要とするので、200M のメモリが 必要です。 libstdc++ ライブラリの増加を避けるためには、 MySQL の configure 時に CXX=gcc も設定すべきです。 Note that with some versions of pgcc, the resulting code will only run on true Pentium processors, even if you use the compiler option that you want the resulting code to be working on all x586 type processors (like AMD).

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

我々は Cygnus CodeFusion と Fujitsu コンパイラーでもテストしましたが、 MySQL を最適化オプションでコンパイルするには、 どちらもまだ完全なバグ・フリーではありませんでした。

When you compile MySQL you should only include support for the character sets that you are going to use. (Option --with-charset=xxx). The standard MySQL binary distributions are compiled with support for all character sets.

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

The MySQL-Linux distribution provided by MySQL AB used to be compiled with 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 standard MySQL Linux binary is linked statically to get it faster and more portable.

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

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

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

5.5.5 How MySQL uses DNS

When a new thread connects to mysqld, mysqld will span a new thread to handle the request. This thread will first check if the hostname is in the hostname cache. If not the thread will call gethostbyaddr_r() and gethostbyname_r() to resolve the hostname.

If the operating system doesn't support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr() and gethostbyname() instead. Note that in this case no other thread can resolve other hostnames that is not in the hostname cache until the first thread is ready.

You can disable DNS host lookup by starting mysqld with --skip-name-resolve. In this case you can however only use IP names in the MySQL privilege tables.

If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookop with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompile mysqld.

You can disable the hostname cache with --skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts.

If you don't want to allow connections over TCP/IP, you can do this by starting mysqld with --skip-networking.

5.5.6 SET 構文

SET [GLOBAL | SESSION] sql_variable=expression, [[GLOBAL | SESSION] sql_variable=expression...]

SET OPTION はサーバやクライアントの操作に影響する様々なオプションを設定します. 現在のセッションが終わるか,またはあなたが異なった値にオプションを設定するまで, 設定されたオプション値は残っています.

SET sets various options that affect the operation of the server or your client.

The following examples shows the different syntaxes one can use to set variables:

In old MySQL versions we allowed the use of the SET OPTION syntax, but this syntax is now deprecated.

In MySQL 4.0.3 we added the GLOBAL and SESSION options and access to most important startup variables.

LOCAL can be used as a synonym for SESSION.

If you set several variables on the same command line, the last used GLOBAL | SESSION mode is used.

SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;

The @@variable_name syntax is supported to make MySQL syntax compatible with some other databases.

The different system variables one can set are described in the system variable section of this manual. 「6.1.5 System Variables」節参照.

If you are using SESSION (the default) the option you set remains in effect until the current session ends, or until you set the option to a different value. If you use GLOBAL, which require the SUPER privilege, the option is remembered and used for new connections until the server restarts. If you want to make an option permanent, you should set it in one of the MySQL option files. 「4.1.2 `my.cnf' オプションファイル」節参照.

To avoid wrong usage MySQL will give an error if you use SET GLOBAL with a variable that can only be used with SET SESSION or if you are not using SET GLOBAL with a global variable.

If you want to set a SESSION variable to the GLOBAL value or a GLOBAL value to the MySQL default value, you can set it to DEFAULT.

SET max_join_size=DEFAULT;

This is identical to:

SET @@session.max_join_size=@@global.max_join_size;

If you want to restrict the maximum value a startup option can be set to with the SET command, you can specify this by using the --maximum-variable-name command line option. 「4.1.1 mysqld コマンド行オプション」節参照.

You can get a list of most variables with SHOW VARIABLES. 「4.5.6.4 SHOW VARIABLES」節参照. You can get the value for a specific value with the @@[global.|local.]variable_name syntax:

SHOW VARIABLES like "max_join_size";
SHOW GLOBAL VARIABLES like "max_join_size";
SELECT @@max_join_size, @@global.max_join_size;

Here follows a description of the variables that uses a the variables that uses a non-standard SET syntax and some of the other variables. The other variable definitions can be found in the system variable section, among the startup options or in the description of SHOW VARIABLES. 「6.1.5 System Variables」節参照. 「4.1.1 mysqld コマンド行オプション」節参照. 「4.5.6.4 SHOW VARIABLES」節参照.

CHARACTER SET character_set_name | DEFAULT
これは指定されたマッピングに従って、すべての文字列をクライアントからクライアント にマップします. 現在、character_set_name に指定できるオプションは cp1251_koi8 だけですが, MySQL のソース中にある `sql/convert.cc' ファイルを編集することによって, 容易に新しいマッピングを加えることができます. 標準のマッピングに戻すには、 character_set_nameDEFAULT を指定します。 CHARACTER SET オプションを設定するための構文は、 他のオプションを設定する構文と異なっていることに注意してください.
PASSWORD = PASSWORD('some password')
現在のユーザのパスワードを設定します。いかなる非匿名のユーザも、 自分自身パスワードを変えることができます!
PASSWORD FOR user = PASSWORD('some password')
現在ログインしているホストの特定ユーザのパスワードを設定します。 mysql データベースにアクセスができるユーザーだけが実行できます。 ユーザは user@hostname 形式で与えなくてはなりません。 ここで userhostname は、mysql.user テーブルの User, Host フィールドに登録されていなくてはなりません。 例えば、UserHost フィールドが 'bob''%.loc.gov' ならば、以下のようにします:
mysql> SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass");
Which is equivalent to:
mysql> UPDATE mysql.user SET password=PASSWORD("newpass")
    ->                   WHERE user="bob" AND host="%.loc.gov";
SQL_AUTO_IS_NULL = 0 | 1
1 (デフォルト) を設定すると、次のようにして、auto_increment レコー ドを持つテーブルで、最後に挿入されたレコードを見つけることができます: WHERE auto_increment_column IS NULL。これは、Access のようないくつ かの ODBC プログラムによって使用されます。
AUTOCOMMIT= 0 | 1
1 を設定すると、テーブルへのすべての変更はすぐに行なわれます。 複数コマンドトランザクションを開始するためには、BEGIN ステートメ ントを使用する必要があります。 「6.7.1 BEGIN/COMMIT/ROLLBACK 構文」節参照。 0 を設定すると、そのトランザクションを許可/破棄するために、 COMMIT / ROLLBACK を使用する必要があります。 「6.7.1 BEGIN/COMMIT/ROLLBACK 構文」節参照。 注意: 非 AUTOCOMMIT モードから AUTOCOMMIT モードに変更する時、 MySQL はすべてのオープントランザクションを自動的に COMMIT します。
BIG_TABLES = 0 | 1 (ver.4.0.3未満では SQL_BIG_TABLES)
0 の場合、全ての一時テーブルはメモリーではなくディスクに書き出されます。 これは少し遅くなりますが、多くの一時テーブルを必要とする大きな SELECT を 実行しても、The table tbl_name is full エラーが出なくなります。 新しい接続におけるこの値のデフォルト値は 1 (メモリーに一時テーブルを作る) です。
SQL_BIG_SELECTS = 0 | 1
1 の場合、とても時間のかかる SELECT が実行された場合、 MySQL はそれを中止します。 これはあまり芳しくない(間違った) WHERE 構文が発行されたときに役立ちます。 max_join_size 行以上を検討するような SELECT が 大きなクエリと定義されます。 新しい接続におけるこの値のデフォルト値は 0 です。 (全ての SELECT 構文を許します)
SQL_BUFFER_RESULT = 0 | 1
SQL_BUFFER_RESULT は一時ファイルに置かれる SELECT からの結果 を強制的に置きます。これは MySQL がテーブルロックを早く解放する手 助けをし、クライアントへ結果セットを送信するために長い時間が掛かる場合に役 立ちます。
LOW_PRIORITY_UPDATES = 0 | 1
1 の場合、全ての INSERT, UPDATE, DELETE, LOCK TABLE WRITE構文は、 対象となるテーブルを処理中の SELECTLOCK TABLE READ がなくなるまで待ちます。 ver.4.0.3 未満では SQL_LOW_PRIORITY_UPDATES オプションになります.
MAX_JOIN_SIZE = value | DEFAULT
おそらく value よりも多いレコードの組合せを試験する必要があるような SELECT を許可しません。この値を設定することで、キーが使用されないで あろう SELECT と長い時間が掛かるであろう SELECT を捕捉できま す。これを DEFAULT 以外の値に設定すると、SQL_BIG_SELECTS フ ラグをリセットします。SQL_BIG_SELECTS フラグを再び設定すると、 SQL_MAX_JOIN_SIZE 変数は無視されます。-O max_join_size=#mysqld を起動することで、この変数のデフォルト値を設定できます。T ver.4.0.3 未満では SQL_MAX_JOIN_SIZE オプションになります. Note that if the result of the query is already in the query cache, the above check will not be made. Instead, MySQL will send the result to the client. Since the query result is already computed and it will not burden the server to send the result to the client.
QUERY_CACHE_TYPE = OFF | ON | DEMAND
QUERY_CACHE_TYPE = 0 | 1 | 2
Set query cache setting for this thread.
Option Description
0 or OFF Don't cache or retrieve results.
1 or ON Cache all results except SELECT SQL_NO_CACHE ... queries.
2 or DEMAND Cache only SELECT SQL_CACHE ... queries.
SQL_SAFE_UPDATES = 0 | 1
1 に設定すると、WHERE 節内でキーや LIMIT を使用しない で UPDATE または DELETE を行なおうとした場合に MySQL がアボートします。これは、手で SQL コマンドを生成する時の間 違った更新の捕捉を可能にします。
SQL_SELECT_LIMIT = value | DEFAULT
SELECT 構文から返されるレコードの最大値。 もし SELECTLIMIT 節を使用している場合、LIMITSQL_SELECT_LIMIT の値より優先されます。 新しい接続におけるこの値の標準値は ``unlimited''. もしリミットを変えているならば、SQL_SELECT_LIMITDEFAULT を指定することにより、標準値に戻すことができます。
SQL_LOG_OFF = 0 | 1
この値が 1 の場合、もしクライアントが SUPER 権限 (version4.0.2以上。それ未満では PROCESS権限) を持っているならば、 このクライアントのログが行われません。 これは更新ログに影響しません!
SQL_LOG_UPDATE = 0 | 1
0 の場合、もしクライアントが SUPER 権限 (version4.0.2以上。それ未満では PROCESS権限) を持っているならば、 このクライアントの更新ログの記録は行われません。 これは通常のログには影響しません!
SQL_QUOTE_SHOW_CREATE = 0 | 1
If set to 1, SHOW CREATE TABLE will quote table and column names. This is on by default, for replication of tables with fancy column names to work. 「4.5.6.8 SHOW CREATE TABLE」節.
TIMESTAMP = timestamp_value | DEFAULT
クライアントに時間を設定します。 もしレコードのリストアに更新ログを使用する場合、オリジナルのタイムスタンプを得る ために使用します。 timestamp_value should be a UNIX Epoch timestamp, not a MySQL timestamp.
LAST_INSERT_ID = #
LAST_INSERT_ID() からの返り値を設定します。 テーブルを更新するコマンド中に LAST_INSERT_ID() を使用した場合、 これは更新ログに保存されます。
INSERT_ID = #
AUTO_INCREMENT 値を挿入する時、 INSERT , ALTER TABLE コマンドに従って使用される値をセットします。 これは更新ログによって使用されます。

5.6 Disk の問題

5.6.1 シンボリックリンクの使用

テーブルとデータベースのファイルを MySQL のデータベースディレクトリーから 違う場所に移動し、 それに対してシンボリックリンクを張ることができます。例 えば、もっと空き容量のあるファイルシステムへデータベースを移すため、あるい はテーブルを異なるディスクに広げることでシステムのスピードを増加するために これを行ないたくなるでしょう。

The recommended way to do this, is to just symlink databases to a different disk and only symlink tables as a last resort.

5.6.1.1 Using Symbolic Links for Databases

The way to symlink a database is to first create a directory on some disk where you have free space and then create a symlink to it from the MySQL database directory.

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test mysqld-datadir

MySQL は一つのディレクトリを複数のデータベースにリンクすることは サポートしていません。シンボリックリンクを持つデータベースディレクトリの複 製は、データベース間のシンボリックリンクを作成しなければ、正常に動作します。 MySQL データディレクトリに db1 データベースがあるとして、 db1 を指すシンボリックリンク db2 を作成します。

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 (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode)))

to

if (1)

On Windows you can use internal symbolic links to directories by compiling MySQL with -DUSE_SYMDIR. This allows you to put different databases on different disks. 「2.6.2.5 Windows 上で、異なるディスクにデータを分割する」節参照.

5.6.1.2 Using Symbolic Links for Tables

Before MySQL 4.0 you should not symlink tables, if you are not very careful with them. The problem is that if you run ALTER TABLE, REPAIR TABLE or OPTIMIZE TABLE on a symlinked table, the symlinks will be removed and replaced by the original files. This happens because the above command works by creating a temporary file in the database directory and when the command is complete, replace the original file with the temporary file.

You should not symlink tables on systems that don't have a fully working realpath() call. (At least Linux and Solaris support realpath())

In MySQL 4.0 symlinks are fully supported only for MyISAM tables. For other table types you will probably get strange problems when doing any of the above mentioned commands.

The handling of symbolic links in MySQL 4.0 works the following way (this is mostly relevant only for MyISAM tables).

Things that are not yet supported:


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