MySQLクエリ最適化: 100万件のデータに対するテーブル最適化ソリューション

1. 2つのクエリエンジン（myIsamエンジン）のクエリ速度

InnoDB はテーブル内の特定の行数を保存しません。つまり、select count(*) from table を実行する場合、InnoDB はテーブル全体をスキャンして行数を計算する必要があります。

MyISAM は保存された行の数を単純に読み取ります。

count(*) ステートメントに where 条件が含まれている場合、2 つのテーブルの操作が若干異なることに注意してください。InnoDB タイプのテーブルでは、count(*) または count(primary key) と where col 条件が使用されます。 col 列は、一意制約インデックスを持つテーブルの主キー以外の列です。これにより、クエリが非常に高速になります。これにより、テーブル全体のスキャンが回避されます。

要約:

MySQL では、レコード数が 300 万件 (MyISAM エンジン) の場合、count(*) を使用して条件 (インデックスを正しく設定) でレコードの総数を照会すると、実行時間は正常です。頻繁に読み取られるデータの場合は、myIsam エンジンを使用することをお勧めします。

2. 数百万のデータを扱うMySQLのページング問題

開発中はページングを頻繁に使用します。コア技術は、制限を使用してデータを読み取ることです。ページングに制限を使用するテスト中に、次のデータが得られます。

ニュースから*を選択 IDで並べ替え 降順 制限 0,10
0.003秒かかりました select * from news order by id desc limit 10000,10
ニュースからID順、降順、制限100000,10で*を選択するのに0.058秒かかりました 
ニュースからID順、降順、制限1000000,10で*を選択するのに0.575秒かかりました
7.28秒かかりました

データ量が多い場合、ページング開始点が大きくなるほどクエリ速度が遅くなることを発見し、驚きました。100 万件以上のレコードのクエリ速度にはすでに 7 秒かかります。これは受け入れられない数字です!

改善計画1

ニュースから*を選択 
id > (ニュースから id を選択、id 降順で並べ替え、制限 1000000、1)
ID降順で並び替え 
制限 0,10

クエリ時間は 0.365 秒で、効率性の向上が非常に明らかです。 ！どのように機能しますか? ? ?

条件を使用して ID をフィルタリングします。サブクエリ (select id from news order by id desc limit 1000000, 1) では、ID フィールドのみをクエリするため、select * や複数のフィールドを選択する場合と比べて、クエリのオーバーヘッドが大幅に削減されます。

改善案2

連続IDのシステムに適しており、非常に高速です。

ニュースから*を選択 
IDが1000000から1000010の間 
ID降順で並び替え

条件と不連続な ID を含むクエリには適していません。とても早いです！

3. MySQL条件クエリと数百万のデータを含むページングクエリを実行する際の注意点

前のセクションに引き続き、クエリ条件を追加します。

ニュースからIDを選択 
ここで、cate = 1
ID降順で並び替え 
制限 500000,10 
クエリ時間 20秒

恐ろしいスピードですね！ ！最初のセクションの知識を使用して、以下を最適化します。

ニュースから*を選択
cate = 1 かつ id > (cate = 1 の news から id を選択、id で並べ替え、desc 制限 500000,1) 
ID降順で並び替え 
制限 0,10 
クエリ時間 15秒

最適化の効果は明ら​​かではなく、条件の影響は依然として非常に大きいです。この場合、SQL 文をどのように最適化しても、操作効率の問題を解決することはできません。次に、アイデアを変更します。記事 ID と分類情報のみを記録するインデックス テーブルを作成し、記事コンテンツの大きなフィールドを分離します。

表ニュース2 [記事表エンジン myisam 文字セット utf-8]

id int 11 主キーは自動的に増加する

cate int 11 インデックス

データを書き込むときは、2 つのテーブルを同期します。クエリを実行するときは、news2 を使用して条件付きクエリを実行できます。

ニュースから*を選択
cate = 1 かつ id > (select id from news2 where cate = 1 order by id desc limit 500000,1) の場合 
ID降順で並び替え 
制限 0,10

条件 id > は news2 テーブルを使用することに注意してください。

実行時間は1.23秒。実行時間が約20倍も短縮されていることがわかります。 ！データが 100,000 程度になると、クエリ時間を 0.5 秒程度に抑えることができ、徐々に許容できる値に近づいています。

しかし、1 秒はサーバーにとって依然として許容できない値です。 ！最適化する他の方法はありますか? ?私たちは素晴らしいバリエーションを試しました:

news2 のストレージ エンジンを innodb に変更すると、驚くべき結果が得られます。

ニュースから*を選択
cate = 1 かつ id > (select id from news2 where cate = 1 order by id desc limit 500000,1) の場合 
ID降順で並び替え 
制限 0,10

わずか0.2秒しかかかりません。本当に速いです。

4. MySQLストレージエンジンmyIsamとinnodbの違い

MySQL には複数のストレージ エンジンがあり、MyISAM と InnoDB はよく使用される 2 つのエンジンです。ここでは、これら 2 つのエンジンに関する基本的な概念をいくつか紹介します (詳細な紹介ではありません)。

従来の ISAM タイプに基づく MyISAM ストレージ エンジンは、全文検索をサポートしますが、トランザクション セーフではなく、外部キーをサポートしません。各 MyISAM テーブルは 3 つのファイルに保存されます。frm ファイルにはテーブル定義が保存され、データ ファイルは MYD (MYData)、インデックス ファイルは MYI (MYIndex) です。

InnoDB は、ロールバック、クラッシュ リカバリ、マルチバージョン同時実行制御、ACID トランザクション、行レベル ロック (InnoDB テーブルの行ロックは絶対的ではありません。MySQL が SQL ステートメントを実行するときにスキャンする範囲を決定できない場合、InnoDB テーブルは、SQL ステートメントのような操作中にテーブル全体をロックします) をサポートし、Oracle 型と一致する非ロック読み取り方式を提供するトランザクション エンジンです。 InnoDB はテーブルとインデックスをテーブルスペースに保存します。テーブルスペースには複数のファイルを含めることができます。

主な違い

MyISAM は非トランザクションセーフですが、InnoDB はトランザクションセーフです。

MyISAM ロックの粒度はテーブル レベルですが、InnoDB は行レベルのロックをサポートします。

MyISAM はフルテキスト インデックスをサポートしていますが、InnoDB はサポートしていません。

MyISAM は比較的シンプルなので、InnoDB よりも効率的です。小規模なアプリケーションでは MyISAM の使用を検討できます。

MyISAM テーブルはファイル形式で保存されます。クロスプラットフォーム データ転送で MyISAM ストレージを使用すると、多くのトラブルを回避できます。

InnoDB テーブルは MyISAM テーブルよりも安全です。データを失うことなく、非トランザクション テーブルからトランザクション テーブルに切り替えることができます (alter table tablename type=innodb)。

アプリケーションシナリオ

MyISAM は非トランザクション テーブルを管理します。高速な保存と取得、および全文検索機能を提供します。アプリケーションで多数の SELECT クエリを実行する必要がある場合は、MyISAM の方が適しています。

InnoDB はトランザクション処理アプリケーション用に設計されており、ACID トランザクションのサポートを含む多くの機能を備えています。アプリケーションで多数の INSERT または UPDATE 操作を実行する必要がある場合は、複数ユーザーの同時操作のパフォーマンスを向上させるために InnoDB を使用する必要があります。

MySQL ストレージ エンジンとインデックス

データベースにはインデックスが必要です。インデックスがないと、検索プロセスは順次検索になり、O(n) の時間計算量はほとんど耐えられません。キーワードがツリーのノードに格納されている限り、B+ ツリーを使用して単一のキーワードのみで構成されるテーブルにインデックスを付ける方法を想像するのは非常に簡単です。データベースのレコードに複数のフィールドが含まれている場合、B+ ツリーは主キーのみを格納できます。主キー以外のフィールドが取得されると、主キー インデックスは機能を失い、順次検索になります。この時点で、取得する 2 番目の列に 2 番目のインデックス セットを作成する必要があります。 インデックスは個別の B+ ツリーとして編成されます。複数の B+ ツリーが同じテーブル データ セットにアクセスする問題を解決する一般的な方法は 2 つあります。1 つはクラスター化インデックスと呼ばれ、もう 1 つは非クラスター化インデックス (セカンダリ インデックス) と呼ばれます。どちらの名前もインデックスと呼ばれますが、これは別個のインデックス タイプではなく、データを保存する方法です。クラスター化インデックス ストレージの場合、行データと主キー B+ ツリーは一緒に保存され、セカンダリ キー B+ ツリーにはセカンダリ キーと主キーのみが格納されます。主キーと非主キー B+ ツリーは、ほぼ 2 種類のツリーです。非クラスター化インデックス ストレージの場合、主キー B+ ツリーは、主キーではなく、リーフ ノードの実際のデータ行へのポインターを格納します。

InnoDB はクラスター化インデックスを使用して主キーを B+ ツリーに編成し、行データをリーフ ノードに格納します。「where id = 14」などの条件を使用して主キーを検索すると、B+ ツリー検索アルゴリズムに従って対応するリーフ ノードを見つけ、行データを取得できます。 Name 列に対して条件付き検索を実行する場合は、2 つの手順が必要です。最初の手順では、補助インデックス B+ ツリーで Name を取得し、そのリーフ ノードに到達して対応する主キーを取得します。 2 番目のステップでは、主キーを使用して主インデックス B+ ツリーで別の B+ ツリー検索操作を実行し、最終的にリーフ ノードに到達してデータの行全体を取得します。

MyISM は非クラスター化インデックスを使用します。非クラスター化インデックスの 2 つの B+ ツリーは見た目に違いはありません。ノードの構造はまったく同じですが、格納されているコンテンツは異なります。主キー インデックス B+ ツリーのノードには主キーが格納され、セカンダリ キー インデックス B+ ツリーのノードにはセカンダリ キーが格納されます。テーブル データは独立した場所に保存されます。これら 2 つの B+ ツリーのリーフ ノードは、1 つのアドレスを使用して実際のテーブル データを指します。テーブル データの場合、これら 2 つのキーに違いはありません。インデックス ツリーは独立しているため、セカンダリ キーによる検索ではプライマリ キーのインデックス ツリーにアクセスする必要はありません。

2 つのインデックスの違いをより明確に示すために、次に示すように 4 行のデータを格納するテーブルを想像してみましょう。 Id はプライマリ インデックスであり、Name はセカンダリ インデックスです。この図は、クラスター化インデックスと非クラスター化インデックスの違いを明確に示しています。

ここではクラスター化インデックスに焦点を当てます。補助インデックスを使用した検索ごとに 2 回の B+ ツリー検索が必要になるため、クラスター化インデックスの効率は非クラスター化インデックスよりも明らかに低いようです。これは冗長ではないでしょうか。クラスター化インデックスの利点は何ですか?

1 行データとリーフノードは一緒に保存されるため、主キーと行データは一緒にメモリにロードされます。リーフノードが見つかると、すぐに行データを返すことができます。データが主キーIDに従って整理されている場合は、データをより速く取得できます。

2 補助インデックスのポインタとしてアドレス値を使用する代わりに、主キーを「ポインタ」として使用する利点は、行を移動したりデータ ページを分割したりするときに補助インデックスのメンテナンス作業が軽減されることです。主キー値をポインタとして使用すると、補助インデックスがより多くのスペースを占めることになりますが、行を移動するときに InnoDB が補助インデックスの「ポインタ」を更新する必要がないという利点があります。つまり、データベース内のデータが変更されると（以前の B+ ツリーのノード分割とページ分割）、行の位置（実装では 16K Page で配置されますが、これについては後で説明します）が変わります。クラスター化インデックスを使用すると、主キー B+ ツリーのノードがどのように変更されても、補助インデックス ツリーは影響を受けません。

したがって、数百万以上のデータになると、MySQL InnoDB のインデックス パフォーマンスはさらに向上します。

5. MySQLパフォーマンス最適化に関する経験

a. クエリごとにクエリを最適化する

ほとんどの MySQL サーバーではクエリ キャッシュが有効になっています。これはパフォーマンスを向上させる最も効果的な方法の 1 つであり、MySQL データベース エンジンによって処理されます。多数の同一クエリが複数回実行されると、クエリ結果はキャッシュに格納されるため、後続の同一クエリはテーブルを操作せずにキャッシュされた結果に直接アクセスできます。

ここでの主な問題は、この問題がプログラマーによって見落とされやすいことです。一部のクエリ ステートメントにより、MySQL がキャッシュを使用しなくなるためです。

次の例を見てください。

// クエリキャッシュが有効になっていません

$r = mysql_query("username FROM user WHERE signup_date >= CURDATE() を選択してください");

// クエリキャッシュを有効にする

$today = date("Ymd");

$r = mysql_query("username FROM user WHERE signup_date >= '$today'");

上記の 2 つの SQL ステートメントの違いは CURDATE() です。MySQL クエリ キャッシュはこの関数では機能しません。したがって、NOW() や RAND() などの SQL 関数やその他の同様の関数では、これらの関数の戻り値が不確実かつ不安定であるため、クエリ キャッシュは有効になりません。したがって、キャッシュを有効にするには、MySQL 関数を変数に置き換えるだけです。

b. EXPLAINの使い方を学ぶ

EXPLAIN キーワードを使用すると、MySQL が SQL ステートメントをどのように処理するかを確認できます。

cate = 1 のニュースから id、title、cate を選択します。

クエリが遅い場合は、cate フィールドにインデックスを追加するとクエリが高速化されます。

c. 1行のデータのみ必要な場合はLIMIT 1を使用する

テーブルをクエリし、必要なデータが 1 つだけの場合は、制限 1 を使用します。

d. インデックスを正しく使用する

インデックスは必ずしも主キーまたは一意のフィールド用であるとは限りません。テーブル内に検索、写真撮影、条件などによく使用するフィールドがある場合は、そのフィールドのインデックスを作成してください。

e. ORDER BY RAND() を使用しない

非常に非効率的なランダムクエリです。

f. SELECT *を避ける

データベースから読み取るデータが増えるほど、クエリの速度は遅くなります。また、データベース サーバーと Web サーバーが 2 つの独立したサーバーである場合、ネットワーク転送の負荷が増加します。必要なものは何でも取るという良い習慣を身につけなければなりません。

g. VARCHARの代わりにENUMを使用する

ENUM 型は非常に高速かつコンパクトです。実際には、TINYINT が格納されますが、文字列として表示されます。これはオプションのリストを作成するのに最適です。

「性別」、「国」、「民族」、「州」、「部門」など、値の数が限られていることが分かっているフィールドがある場合は、VARCHAR ではなく ENUM を使用する必要があります。

h. NOT NULLの使用

NULL 値を使用する特別な理由がない限り、列を常に NOT NULL にしておく必要があります。少し議論の余地があるように思われるかもしれませんが、読み続けてください。

まず、「空」と「NULL」（INT の場合は 0 と NULL）の違いは何か考えてみましょう。それらの間に違いがないと思われる場合は、NULL を使用しないでください。 (Oracle では、NULL と Empty は同じ文字列であることをご存知でしたか?)

NULL にはスペースが必要ないと想定しないでください。NULL には追加のスペースが必要であり、比較を行うときにプログラムがより複雑になります。 もちろん、これは NULL を使用できないという意味ではありません。現実は非常に複雑であり、NULL 値を使用する必要があるケースが依然としていくつかあります。

以下はMySQLのドキュメントからの抜粋です。

「NULL 列には、その値が NULL であるかどうかを記録するために行に追加のスペースが必要です。MyISAM テーブルの場合、各 NULL 列は 1 ビット余分に必要となり、最も近いバイトに切り上げられます。」

i. IPアドレスはUNSIGNED INTとして保存されます

多くのプログラマーは、整数 IP の代わりに文字列 IP を格納するために VARCHAR(15) フィールドを作成します。整数を使用して保存する場合、必要なバイト数は 4 バイトのみで、固定長フィールドを使用できます。さらに、これはクエリにおいて、特に IP が ip1 と ip2 の間であるなどの WHERE 条件を使用する必要がある場合に利点をもたらします。

IP アドレスは 32 ビットの符号なし整数全体を使用するため、UNSIGNED INT を使用する必要があります。

j. 固定長テーブルは高速です

テーブル内のすべてのフィールドが「固定長」の場合、テーブル全体は「静的」または「固定長」であると見なされます。 たとえば、テーブルには VARCHAR、TEXT、BLOB などのタイプのフィールドがありません。これらのフィールドのいずれかを含めると、テーブルは「固定長の静的テーブル」ではなくなり、MySQL エンジンは別の方法で処理します。

固定長テーブルを使用すると、MySQL の検索が高速化されるためパフォーマンスが向上します。また、固定長にすると次のデータのオフセットを計算しやすくなるため、読み取りも自然に高速になります。フィールドの長さが固定されていない場合、次のエントリを検索するたびに、プログラムは主キーを検索する必要があります。

また、固定長テーブルはキャッシュや再構築が簡単です。ただし、唯一の副作用は、固定長フィールドでは、使用するかどうかに関係なく、その分のスペースが割り当てられるため、いくらかのスペースが無駄になることです。

k. 垂直分割

「垂直分割」とは、データベース内のテーブルを列ごとに複数のテーブルに分割する方法であり、これによりテーブルの複雑さとフィールドの数を減らし、最適化の目的を達成できます。これらの分離されたフィールドによって形成されたテーブルを頻繁に結合しないでください。そうしないと、フィールドが分​​離されていない場合よりもパフォーマンスが悪くなり、パフォーマンスが急激に低下します。

l. 大きなDELETEまたはINSERTステートメントを分割する

稼働中の Web サイトで大規模な DELETE または INSERT クエリを実行する場合は、操作によって Web サイト全体がダウンしないように十分注意する必要があります。これら 2 つの操作によりテーブルがロックされるため、テーブルがロックされると、他の操作は実行できなくなります。

Apache には多くの子プロセスまたはスレッドが存在します。したがって、非常に効率的に動作し、サーバーでは、サーバーのリソース、特にメモリを大量に占有するサブプロセス、スレッド、データベース リンクが多すぎることを望んでいません。

テーブルを 30 秒などの一定期間ロックすると、トラフィック量の多いサイトでは、この 30 秒間に蓄積されたアクセス プロセス/スレッド、データベース リンク、および開いているファイルの数によって、Web サービスがクラッシュするだけでなく、サーバー全体が即座にハングアップする可能性があります。

m. 列が小さいほど速くなります

ほとんどのデータベース エンジンにとって、ディスク操作はおそらく最も重大なボトルネックです。したがって、このような状況では、データをコンパクトにすると、ハードドライブへのアクセス回数が減るため、非常に役立ちます。

n. 適切なストレージエンジンを選択する

MySQL には MyISAM と InnoDB という 2 つのストレージ エンジンがあり、それぞれに長所と短所があります。

MyISAM は、大量のクエリを必要とする一部のアプリケーションには適していますが、書き込み操作が多いアプリケーションには適していません。フィールドを更新するだけの場合でも、テーブル全体がロックされ、読み取り操作が完了するまで他のプロセス (読み取りプロセスも含む) は操作できません。さらに、MyISAM は SELECT COUNT(*) などの計算が非常に高速です。

InnoDB の傾向として、非常に複雑なストレージ エンジンになることがあり、一部の小規模なアプリケーションでは、MyISAM よりも遅くなります。 「行ロック」をサポートしているため、書き込み操作が多いほどパフォーマンスが向上します。さらに、トランザクションなどのより高度なアプリケーションもサポートします。

これで、MySQL クエリの最適化と 100 万レコードのテーブル最適化ソリューションに関するこの記事は終了です。MySQL クエリの最適化に関する関連コンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。