MySQL データベースの最適化に関する 9 つのヒント

1. 最も適切なフィールド属性を選択する

Mysql 、大量のデータの保存を適切にサポートできるリレーショナル データベースですが、一般的に、データベース内のテーブルが小さいほど、そのテーブルで実行されるクエリが高速になります。したがって、テーブルを作成するときに、より良いパフォーマンスを得るために、テーブル内のフィールドの幅をできるだけ小さくすることができます。

たとえば、郵便番号フィールドを定義するときに、 char(255)，データベースに不要なスペースが追加されることは明らかです。char char(6)でもタスクを十分に実行できるため、 varchar型を使用することは冗長です。同様に、可能であれば、整数フィールドを定義するにはBIGINTではなくMEDIUMINT使用する必要があります。

2. フィールドをNOT NULLに設定してみる

可能であれば、フィールドをNOT NULLに設定して、将来のクエリを実行するときにデータベースが NULL 値を比較する必要がないようにしてください。

「都道府県」や「性別」などの一部のテキスト フィールドについては、ENUM (列挙) 型として定義できます。 MySQL では、ENUM 型は数値データとして扱われ、数値データはテキストデータよりもはるかに高速に処理されるためです。この方法でデータベースのパフォーマンスを向上させることができます。

3. サブクエリの代わりにJOINを使用する

MySQL はバージョン 4.1 以降で SQL サブクエリをサポートしています。この手法を使用すると、SELECT ステートメントを使用してシングルトン クエリ結果を作成し、その結果を別のクエリのフィルター条件として使用できます。

たとえば、顧客基本情報テーブルに注文がない顧客を削除する場合は、次の図に示すように、サブクエリを使用して、まず販売情報テーブルから注文したすべての顧客の顧客 ID を取得し、その結果をメインクエリに渡すことができます。

JOIN を使用してこのタスクを完了すると、速度が大幅に向上します。特に、salesinfo テーブルの CustomerID にインデックスがある場合は、パフォーマンスが向上します。クエリは次のとおりです。

JOIN がより効率的である理由は、MySQL では論理的に 2 つのステップを必要とするこのクエリを完了するためにメモリ内に一時テーブルを作成する必要がないためです。

さらに、アプリケーションに多数の JOIN クエリがある場合は、JOIN される 2 つのテーブル内のフィールドにインデックスが付けられていることを確認する必要があります。このようにして、MySQL は JOIN SQL ステートメントを最適化するメカニズムを開始します。また、JOIN に使用するフィールドは同じタイプである必要があります。

たとえば、 DECIMAL フィールドを INT フィールドと JOIN すると、MySQL はそれらのインデックスを使用できません。これらのSTRING型の場合も、同じ文字セットが必要です。 (2 つのテーブルの文字セットは同じではない可能性があります)。

内部inner join等価結合とも呼ばれ、 left/right join外部結合です。

A.id、A.name、B.id、B.name を A から選択し、B を A.id=B.id で結合します。

A から A.id、A.name、B.id、B.name を選択し、B で右結合します。A.id= B.id;

A.id =B.id の INNER JOIN から A.id、A.name、B.id、B.name を選択します。

inner join等価結合であり、返される行数が少ないため、 inner joinがパフォーマンスが高速であることがさまざまな面で確認されています。ただし、次のような一部のステートメントでは暗黙的に等結合が使用されることを覚えておく必要があります。

A.id、A.name、B.id、B.name を A、B から選択します。WHERE A.id = B.id;

推奨事項:可能な場合はinner joinを使用します。

SQL には、内部結合、 left join right join 、右結合、 full join結合有inner join 。実際には、それらの間に大きな違いはなく、クエリ結果のみが異なります。

たとえば、次の 2 つのテーブルがあるとします。

Ordersテーブルは、外部キーId_Pを介してPersonsテーブルに関連付けられています。

inner join結合: クエリのために 2 つのテーブルが結合されると、2 つのテーブルに完全に一致する結果セットのみが保持されます。

2 つのテーブルをクエリするには、 inner joinを使用します。SQL は次のとおりです。

p.LastName、p.FirstName、o.OrderNo を選択
人物 p から
INNER JOINの順序
ON p.Id_P=o.Id_P and 1=1 -- 複数の条件を接続するには and を使用します ORDER BY p.LastName

クエリ結果セット:

この接続方法では、Orders テーブルの Id_P フィールドに一致するものが Persons テーブル内に見つからない場合、そのフィールドはリストされません。

注: select * from a,bだけで、直交積が得られます。たとえば、テーブル a に 5 つのレコードがあり、テーブル b に 3 つのレコードがある場合、最終結果は 5*3=15 レコードになります。

しかし、2 つのテーブルを結合する場合select * from a,b where a.id = b.id 、意味が変わり、次の式と同等になります。

a.id = b.id で内部結合 b から * を選択します。 -- つまり、内部結合です。

ただし、この書き込み方法は標準に準拠しておらず、 sqlserverなどの特定のデータベースでのみ機能する可能性があります。このように書かないことをお勧めします。内部結合として記述するのが最適です。

内部結合クエリ ( select * from a join b on a.id = b.id ) と関連クエリ(select * from a , b where a.id = b.id)の違い

left join 、2 つのテーブルを結合するときに、右側のテーブルに一致するレコードがない場合でも、左側のテーブルのすべての行を返します。

2 つのテーブルをクエリするには、左結合を使用します。SQL は次のとおりです。

p.LastName、p.FirstName、o.OrderNo を選択
人物 p から
LEFT JOIN 注文 o
オン p.Id_P=o.Id_P
ORDER BY p.LastName

クエリ結果は次のとおりです。

左側のテーブル ( Personsテーブル) のLastName Bushの行のId_Pフィールドは右側のテーブル ( Ordersテーブル) に一致しませんが、クエリ結果にはその行が保持されていることがわかります。

right join, 2 つのテーブルが結合されると、左側のテーブルに一致するレコードがない場合でも、右側のテーブルのすべての行が返されます。

右結合を使用して 2 つのテーブルをクエリします。SQL は次のとおりです。

p.LastName、p.FirstName、o.OrderNo を選択
人物 p から
右結合注文o
オン p.Id_P=o.Id_P
ORDER BY p.LastName

クエリ結果は次のとおりです。

Orders テーブルの最後のレコードの Id_P フィールド値は 65 です。左側のテーブルには一致するレコードはありませんが、保持されています。

full join 、2 つのテーブルに対して結合クエリを実行するときに、左側のテーブルと右側のテーブル内の一致しない行をすべて返します。

2 つのテーブルをクエリするには、完全結合を使用します。SQL は次のとおりです。

p.LastName、p.FirstName、o.OrderNo を選択
人物 p から
FULL JOIN 注文 o
オン p.Id_P=o.Id_P
ORDER BY p.LastName

クエリ結果は次のとおりです。

クエリの結果は、 left joinとright joinの結合です。

4. 手動で作成した一時テーブルの代わりにUNIONを使用する

MySQL はバージョン 4.0 以降、一時テーブルの使用を必要とする 2 つ以上のselectクエリを 1 つのクエリに組み合わせることができるユニオン クエリをサポートしています。クライアント クエリ セッションが終了すると、データベースが整理され効率的になるように一時テーブルが自動的に削除されます。 unionを使用してクエリを作成する場合、複数の select ステートメントを接続するキーワードとして union を使用するだけです。すべてのselectステートメントのフィールドの数は同じである必要があることに注意してください。次の例は、union を使用したクエリを示しています。

重複する結果セットがないことが確認できる場合、または重複する結果セットを気にしない場合は、union ではなく union all を使用するようにしてください。union と union all の主な違いは、前者は、並べ替え、大量の CPU 操作の追加、リソース消費と待ち時間の増加を伴う一意のフィルタリング操作を実行する前に、2 つ以上の結果セットをマージする必要があることです。

5. 事務

Sub-Queries 、結合、およびユニオンを使用してさまざまなクエリを作成できますが、1 つまたはいくつかのクエリだけですべてのデータベース操作を完了できるわけではありません。多くの場合、タスクを完了するには一連のステートメントが必要です。ただし、この場合、このブロック内のステートメントの実行に失敗すると、ブロック全体の動作が不確実になります。

特定のデータを 2 つの関連するテーブルに同時に挿入したいとします。次のような状況が発生する可能性があります。最初のテーブルが正常に更新された後、データベースで予期しない状況が突然発生し、2 番目のテーブルでの操作が完了しなくなります。これにより、データが不完全になったり、データベースのデータが破損したりします。この状況を回避するには、ステートメント ブロック内のすべてのステートメントが成功または失敗することを保証するトランザクションを使用する必要があります。

つまり、データベース内のデータの一貫性と整合性が維持されます。トランザクションは BEGIN キーワードで始まり、COMMIT キーワードで終了します。この期間中に SQL ステートメントが失敗した場合、Rollback コマンドを使用してデータベースを開始前の状態に復元できます。

始める; 
INSERTINTOsalesinfoSET顧客ID=14;
UPDATEinventorySETQuantity=11WHEREitem='book';
専念;

トランザクションのもう 1 つの機能は、複数のユーザーが同時に同じデータ ソースを使用する場合に、データベースをロックすることでユーザーに安全なアクセス メカニズムを提供し、ユーザーの操作が他のユーザーによって妨害されないようにすることです。

一般的に、トランザクションは、 Atomicity （不可分性とも呼ばれる）、 Consistency （ Isolation性とも呼ばれる）、およびDurability 4 つの条件（ACID）を満たす必要があります。

• 原子性:トランザクション内のすべての操作は完了するか、まったく完了しないかのいずれかであり、中間段階で終了することはありません。トランザクションの実行中にエラーが発生した場合、トランザクションが実行されなかったかのように、トランザクションの開始時の状態にロールバックされます。
• 一貫性:トランザクションの開始前およびトランザクションの終了後にデータベースの整合性が損なわれることはありません。つまり、書き込まれるデータは、データの正確性と連結、後続のデータベースがスケジュールされたタスクを自発的に完了する機能など、事前に設定されたすべてのルールに完全に準拠している必要があります。
• 分離:データベースでは、複数のトランザクションが同時にデータの読み取り、書き込み、変更を行うことができます。分離により、複数のトランザクションが同時に実行されるときに、相互実行によるデータの不整合を防ぐことができます。トランザクション分離には、 Read uncommitted 、 Read Read committed 、 repeateable read 、 Serializableなど、さまざまなレベルがあります。
• 永続性:トランザクションが完了すると、データへの変更は永続的になり、システムに障害が発生しても失われません。

トランザクションの同時実行性の問題:

• ダーティリード:トランザクションAはトランザクションBによって更新されたデータを読み取り、その後Bは操作をロールバックするため、Aによって読み取られたデータはダーティデータになります。
• 反復不可能な読み取り: トランザクション A は同じトランザクションを複数回読み取ります。トランザクション A が同じトランザクションを複数回読み取るプロセス中に、トランザクション B はデータを更新してコミットします。これにより、トランザクション A が同じデータを複数回読み取ると、一貫性のない結果が発生します。
• ファントム リード:システム管理者 A は、データベース内のすべての学生の成績を特定のスコアから ABCDE の成績に変更しましたが、システム管理者 B は、このとき特定のスコアのレコードを挿入しました。システム管理者 A が変更を終えると、変更されていないレコードがまだ残っていることに気づき、錯覚のようになります。これをファントム リードと呼びます。
• 概要:非反復読み取りとファントム読み取りは混同されやすいです。非反復読み取りは変更に重点を置いていますが、ファントム読み取りは追加または削除に重点を置いています。非反復読み取りの問題を解決するには、条件を満たす行のみをロックする必要があります。ファントム読み取りの問題を解決するには、テーブルをロックする必要があります。

MySQL トランザクション分離レベル

トランザクション制御ステートメント:

BEGINまたはSTART TRANSACTION : トランザクションを明示的に開始します。

• COMMIT: COMMIT WORKも使用できますが、この 2 つは同等です。 COMMITトランザクションをコミットし、データベースに加えられたすべての変更を永続的にします。
• ロールバック: Rollback workも使用できますが、この 2 つは同等です。ロールバックはユーザーのトランザクションを終了し、進行中のコミットされていない変更を元に戻します。
• SAVEPOINT 識別子: SAVEPOINT使用すると、トランザクション内にセーブポイントを作成できます。トランザクション内には複数の SAVEPOINT が存在する場合があります。
• RELEASE SAVEPOINT 識別子:トランザクションのセーブポイントを削除します。セーブポイントが指定されていない場合、このステートメントを実行すると例外がスローされます。
• ROLLBACK TO 識別子:トランザクションをマークされたポイントまでロールバックします。
• SET TRANSACTION:トランザクション分離レベルを設定するために使用されます。 InnoDB ストレージ エンジンによって提供されるトランザクション分離レベルは、 READ UNCOMMITTED 、 READ COMMITTED 、 REPEATABLE READ 、およびSERLALIZABLEです。

6. 外部キーを使用する

ロックテーブル方式ではデータの整合性は維持できますが、データの関連性を保証することはできません。このとき、外部キーを使用できます。たとえば、外部キーを使用すると、各販売レコードが既存の顧客を指していることを保証できます。

ここで、外部キーはcustomerinfoテーブルのcustomeridをsalesinfoテーブルのcustomeridにマップできます。有効なcustomeridを持たないレコードは更新されず、 salesinfoに挿入されません。

CREATE TABLE customerinfo(customerid int primary key) engine = innodb;

CREATE TABLE salesinfo( salesid int not null、customerid int not null、主キー(customerid、salesid)、外部キー(customerid) は、削除カスケードで customerinfo(customerid) を参照します)engine = innodb;

例のパラメータ「 on delete cascade 」に注意してください。このパラメータにより、 customerinfoテーブル内の顧客レコードが削除されると、そのレコードも自動的に削除されます。 MySQL で外部キーを使用する場合は、テーブルを作成するときに、テーブル タイプをトランザクション セーフ テーブルInnoDBタイプとして定義することを忘れないでください。このタイプは、mysql テーブルのデフォルトのタイプではありません。定義方法は、 CREATE TABLEステートメントにengine=innoDBを追加することです。

7. テーブルをロックする

トランザクションはデータベースの整合性を維持するための非常に優れた方法ですが、特に大規模なアプリケーション システムでは、その排他性がデータベースのパフォーマンスに影響を及ぼすことがあります。トランザクションの実行中はデータベースがロックされるため、他のユーザー要求はトランザクションが終了するまで一時的に待機することしかできません。

データベース システムを少数のユーザーのみが使用する場合、トランザクションの影響は大きな問題にはなりませんが、電子商取引 Web サイトへのアクセスなど、数千のユーザーが同時にデータベース システムにアクセスする場合、深刻な応答遅延が発生します。

実際、場合によってはテーブルをロックすることでパフォーマンスが向上することがあります。次の例は、前の例のトランザクション機能を完了するためにテーブルをロックする方法です。

ここでは、 selectステートメントを使用して初期データを取得し、いくつかの計算を実行し、 updateステートメントを使用して新しい値をテーブルに更新します。 WRITE キーワードを含む LOCKTABLE ステートメントは、 UNLOCKTABLESコマンドが実行されるまで、 inventoryへのデータの挿入、更新、または削除のためのその他のアクセスを実行できないようにします。

8. インデックスを使用する

インデックス作成は、データベースのパフォーマンスを向上させる一般的な方法です。これにより、データベース サーバーは、インデックスがない場合よりもはるかに高速に特定の行を取得できます。これは、クエリにMAX(),MIN()和ORDERBYなどのコマンドが含まれている場合に特に当てはまります。

では、どのフィールドをインデックス化すればよいのでしょうか?

一般的に、 join 、 whereステートメント、 orderbyソートに使用されるフィールドにインデックスを作成する必要があります。多数の繰り返し値を含むデータベースのフィールドにはインデックスを作成しないようにしてください。ENUM 型のフィールドの場合、多数の繰り返し値が表示される可能性が非常に高くなります。

たとえば、 customerinfoの " province " フィールドです。このようなフィールドにインデックスを作成しても役に立ちません。逆に、データベースのパフォーマンスが低下する可能性もあります。テーブルを作成するときに同時に適切なインデックスを作成することも、 ALTERTABLEまたはCREATEINDEXを使用して後でインデックスを作成することもできます。

さらに、MySQL はバージョン 3.23.23 以降で全文インデックスと検索をサポートしています。 MySQL の全文インデックスはFULLTEXTタイプのインデックスですが、MyISAM タイプのテーブルにのみ使用できます。大規模なデータベースの場合、 FULLTEXTインデックスのないテーブルにデータをロードし、 ALTERTABLEまたはCREATEINDEX INDEX を使用してインデックスを作成すると、非常に高速になります。ただし、すでにFULLTEXTインデックスがあるテーブルにデータをロードすると、実行プロセスが非常に遅くなります。

9. クエリ文を最適化する

9.1 サブクエリを使用しない

例: SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');

MySQL 5.5 では、内部実行プランナーは、最初に内部テーブル t2 をチェックするのではなく、最初に外部テーブルをチェックしてから内部テーブルと一致させるという方法でサブクエリを実行します。外部テーブルのデータが非常に大きい場合、クエリ速度は非常に遅くなります。

MariaDB10/MySQL5.6では、結合メソッドを使用して最適化されています。このSQL文は自動的に次のように変換されます。

t1 から t1.* を選択し、t2 を t1.id = t2.id として結合します。

ただし、最適化はSELECTに対してのみ有効であり、 UPDATE / DELETEサブクエリには有効ではないことに注意してください。実稼働環境ではサブクエリは避けてください。

9.2 関数インデックスを避ける

例：

SELECT * FROM t WHERE YEAR(d) >= 2016;

MySQL は Oracle のような関数インデックスをサポートしていないため、d フィールドにインデックスがあっても、テーブル全体が直接スキャンされます。

次のように変更する必要があります —–>

SELECT * FROM t WHERE d >= '2016-01-01';

9.3 ORをINに置き換える

非効率的なクエリ

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

—–> 効率的なクエリ

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

9.4 LIKE二重パーセント記号はインデックスを使用できません

SELECT * FROM t WHERE name LIKE '%de%';

—–>

SELECT * FROM t WHERE name LIKE 'de%';

現在、MySQL 5.7 のみがフルテキスト インデックスをサポートしています (中国語をサポート)

9.5 適切なレコードを読む LIMIT M,N

t から * を選択し、1 を指定します。

—–>

SELECT * FROM t WHERE 1 LIMIT 10;

9.6 データ型の不一致を避ける

SELECT * FROM t WHERE id = '19';

—–>

SELECT * FROM t WHERE id = 19;

9.7 グループ統計はソートを禁止する可能性がある

SELECT goods_id,count(*) FROM t GROUP BY goods_id;

デフォルトでは、MySQL はGROUP BY col1 、 col2 … 内のすべてのフィールドをソートします。クエリにGROUP BYが含まれており、結果の並べ替えのオーバーヘッドを回避したい場合は、 ORDER BY NULLを指定して並べ替えを抑制できます。

—–>

SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

9.8 ランダムなレコード取得を避ける

SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;

MySQL は関数インデックスをサポートしていないため、テーブル全体のスキャンが発生します—–>

SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;

9.9 不要なORDER BYソートを防ぐ

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;

—–>

count(1) を user u から LEFT JOIN user_info i ON u.id = i.user_id で選択します。

9.10 バッチ挿入

t (id, name) に VALUES(1,'Bea') を挿入します。
t (id, name) に VALUES(2,'Belle') を挿入します。
t (id, name) に VALUES(3,'Bernice') を挿入します。

—–>

t (id, name) に VALUES(1,'Bea'), (2,'Belle'),(3,'Bernice') を挿入します。

これで、MySQL データベースを最適化する 9 つのテクニックに関するこの記事は終了です。MySQL データベースの最適化に関連するその他のテクニックについては、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。