MYSQL クエリを最適化するにはどうすればよいですか? mysql クエリの最適化方法の概要

この記事の内容は、Python でのプロセス プールの簡単な実装コードです。必要な方は参考にしていただければ幸いです。

1. where、order by、group by

に使用されるすべての列にインデックスを追加します (インデックスを除く)。レコードが一意にマークされることを保証し、MySQL サーバーがデータベースからより速く結果を取得できるようにすることもできます。インデックスは並べ替えにおいても非常に重要な役割を果たします。

Mysql のインデックスは追加のスペースを占有し、挿入、削除、更新のパフォーマンスがある程度低下する可能性があります。ただし、テーブルに 10 行を超えるデータがある場合は、インデックスを作成すると検索の実行時間を大幅に短縮できます。

運用環境でクエリがどのように動作するかをより明確に理解するために、「最悪の場合のデータ サンプル」を使用して MySql クエリをテストすることを強くお勧めします。

500 行を超えるデータベース テーブルで次のクエリ ステートメントを実行しているとします。

mysql>select customer_id, customer_name from customers where customer_id='345546'

上記のクエリは、Mysql サーバーにフル テーブル スキャンを実行させて、データを取得します。求めた。

モデルでは、Mysql はクエリ ステートメントのパフォーマンスを分析するための特別な Explain ステートメントを提供します。キーワードの後に​​クエリ ステートメントを追加すると、MySql はオプティマイザがそのステートメントに関して持っているすべての情報を表示します。

Explain ステートメントを使用して上記のクエリを分析すると、次の分析結果が得られます:

mysql> explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

ご覧のとおり、オプティマイザーは非常に重要な情報を表示します。 -データベーステーブルを調整します。まず、キー列が Null であるため、MySql はテーブル全体のスキャンを実行します。次に、MySql サーバーは、このクエリを完了するために 500 行のデータをスキャンすることを明らかにしました。

上記のクエリを最適化するには、customer_id 列にインデックス m を追加するだけです:

mysql> Create index customer_id ON customers (customer_Id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Explain ステートメントを再度実行すると、次の結果を取得します。

mysql> Explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | customer_id   | customer_id | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

上記の出力結果から、MySQL サーバーがインデックス customer_id を使用してテーブルをクエリすることは明らかです。スキャンされる行数が 1 であることがわかります。このクエリは 500 行のテーブルに対してのみ実行していますが、より大きなデータ セットを取得する場合、インデックスはさらに最適化されます。

2. Union を使用して Like ステートメントを最適化する

場合によっては、比較のためにクエリ内で or 演算子を使用する必要があります。 or キーワードが where 句で頻繁に使用されすぎると、MySQL オプティマイザがレコードを取得するために誤ってフル テーブル スキャンを選択する可能性があります。 Union 句を使用すると、特にクエリの一方に最適化されたインデックスがあり、もう一方のクエリにも最適化されたインデックスがある場合に、クエリの実行が高速になります。

たとえば、first_name と last_name にインデックスがある場合、次のクエリ ステートメントを実行します。

mysql> select * from students where first_name like 'Ade%' or last_name like 'Ade%'

上記のクエリと次のクエリは、 Union クエリ ステートメントを最大限に利用する 2 つのクエリをマージする場合と比較すると、速度は大幅に遅くなります。

mysql> select * from students where first_name like 'Ade%' union all select * from students where last_name like 'Ade%'

3. 先頭にワイルドカード文字を含む式の使用を避ける

クエリの先頭にワイルドカード文字がある場合、Mysql はインデックスを使用できません。上記の Student テーブルを例にとると、次のクエリにより MySQL はテーブル全体のスキャンを実行し、適切なタイミングで first_name フィールドにインデックスを追加します。

mysql> select * from students where first_name like '%Ade'

Explain 分析を使用すると、次の結果が得られます:

mysql> explain select * from students where first_name like  '%Ade'  ;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

上に示したように、MySQL は 500 行のデータすべてをスキャンするため、クエリが非常に遅くなります。

4. MySQL の全文検索を最大限に活用する

データのクエリにワイルドカードを使用する必要があるが、データベースのパフォーマンスを低下させたくない場合は、使用を検討してください。 MySQL の全文検索 (FTS) はワイルドカード クエリよりもはるかに高速だからです。これに加えて、FTS はより高品質の関連結果を返すことができます。

全文検索インデックスを学生サンプル テーブルに追加するステートメントは次のとおりです。

mysql> alter table students add fulltext(first_name, last_name)';
mysql> select * from students where match(first_name, last_name) against ('Ade');

上の例では、検索キーワード Ade を指定しました。列 (first_name、last_name) を一致させたいと考えています。クエリ オプティマイザーが上記のステートメントを実行すると、次の結果が得られます:

mysql> explain Select * from students where match(first_name, last_name) AGAINST ('Ade');
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table    | partitions | type     | possible_keys | key        | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | students | NULL       | fulltext | first_name    | first_name | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+

5. データベース スキーマの最適化

正規化

最初に、すべてのデータベース テーブルを正規化します。可能であれば、多少の損失はあるでしょう。たとえば、顧客と注文のデータを記録するために 2 つのテーブルを作成する必要がある場合は、注文テーブル内の顧客 ID で顧客を参照する必要があり、その逆ではありません。以下の図は、データの冗長性を持たずに設計されたデータベース アーキテクチャを示しています。

さらに、同じデータ型クラスを使用して、同様の値を格納します。

最適なデータ型を使用する

MySQL は、integer、float、double、date、datetime、varchar、text などのさまざまなデータ型をサポートします。データベース テーブルを設計するときは、特性を満たすことができる最も短いデータ型を使用するようにしてください。

たとえば、システム ユーザー テーブルを設計していて、ユーザー数が 100 を超えない場合は、user_ud に「TINYINT」タイプを使用する必要があります。このタイプの値の範囲は -128 ～ 128 です。 。フィールドに日付型の値を格納する必要がある場合は、クエリ時に複雑な型変換を実行する必要がないため、日時型を使用することをお勧めします。

値がすべて数値型の場合は、Integer を使用します。計算を実行するとき、Integer 型の値は Text 型の値よりも高速です。

NULL を避ける

NULL は、列に値がないことを意味します。これらのタイプの値はデータベースの結果に悪影響を与える可能性があるため、可能であれば避けてください。たとえば、データベース内のすべての注文の金額の合計を取得する必要があるが、注文レコード内の金額が NULL である場合、NULL ポインターに注意を払わないと、計算で例外が発生する可能性があります。結果。場合によっては、列のデフォルト値を定義する必要がある場合があります。

以上がMYSQL クエリを最適化するにはどうすればよいですか? mysql クエリの最適化方法の概要の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。