フクロウの深夜翻訳:MYSQLクエリーを最適化する方法
1.すべての
インデックスは、唯一のタグ付きレコードを確保するだけでなく、MySQLサーバがデータベースから結果をより迅速に取得することもできます.インデックスはソートにおいても非常に大きな役割を果たします.
Mysqlのインデックスは、追加のスペースを占有し、挿入、削除、更新のパフォーマンスをある程度低下させる可能性があります.しかし、テーブルに10行以上のデータがある場合、インデックスは検索の実行時間を大幅に短縮します.
「最悪のデータサンプル」を使用してMySqlクエリーをテストすることを強くお勧めします.これにより、クエリーの本番での動作をより明確に理解できます.
500行を超えるデータベース・テーブルで次のクエリ文を実行しているとします.
上記のクエリは、検索したデータを取得するためにMysqlサーバにフルテーブルスキャンを実行させます.
型番、Mysqlは特別な
上記のクエリをexplain文で解析すると、次のような解析結果が得られます.
オプティマイザは、データベース・テーブルの微調整に役立つ非常に重要な情報を示しています.まず、key列がNullであるため、MySqlは全テーブルスキャンを実行します.次に、MySqlサーバは、500行のデータをスキャンしてクエリを完了することを明らかにしました.
上記のクエリを最適化するには、
explain文を再度実行すると、次の結果が得られます.
上記の出力結果から、MySQLサーバがインデックスcustomer_を使用することは明らかです.idはテーブルをクエリーします.スキャンが必要な行数は1と見られます.ロー数500のテーブルでこのクエリー文を実行しただけですが、インデックスはより大きなデータセットを取得するときに最適化されます.
2.UnionでLike文を最適化する
クエリーでorオペレータを使用して比較する必要がある場合があります.orキーがwhere句で使用頻度が高すぎると、MySQLオプティマイザが誤って全テーブルスキャンを選択してレコードを取得する可能性があります.union句は、特に1つのクエリに最適化インデックスがあり、別のクエリにも最適化インデックスがある場合に、クエリの実行がより速くなります.
たとえば、
上記のクエリは、次にunionを使用してクエリ文を十分に利用する2つのクエリをマージするのに比べて、かなり遅いです.
3.プリアンブル付きエクスプレッションの使用を避ける
クエリにプリアンブルが存在する場合、Mysqlはインデックスを使用できません.上記のstudentテーブルを例にとると、以下のクエリによりMySQLは全テーブルスキャンを実行し、タイムリー
explain分析を使用して、次の結果を得ました.
上記のように、Mysqlは500行のデータをすべてスキャンし、クエリを極めて遅くします.
4.MySQLの全文検索を活用
ワイルドカードを使用してデータをクエリーしているが、データベースのパフォーマンスを低下させたくない場合は、ワイルドカードクエリーよりもずっと速いため、MySQLの全文検索(FTS)を使用することを考慮する必要があります.それ以外に、FTSは更に品質のもっと良い相関結果を返すことができる.
全文検索インデックスをstudentサンプルに追加する文は、次のとおりです.
上記の例では、検索キーワード
5.データベースアーキテクチャの最適化
正規化
まず、損失が発生する可能性がある場合でも、すべてのデータベース・テーブルを正規化します.たとえば、customersとordersのデータを記録するために2つのテーブルを作成する必要がある場合は、orderテーブルで顧客idで顧客を参照する必要があります.逆ではありません.次の図は、データ冗長性がなく設計されたデータベース・アーキテクチャを示しています.
それ以外に、同じ値に対して同じデータ型クラスを使用して格納します.
最適なデータ型の使用
MySQLでは、integer、float、double、date、datetime、varchar、textなど、さまざまなデータ型がサポートされています.データベース・テーブルを設計する場合は、プロパティを満たす最短のデータ型をできるだけ使用する必要があります.
たとえば、システム・ユーザー・テーブルを設計して100人を超えない場合は、user_udは、−128〜128の範囲の値をとる「TINYINT」タイプを使用する.フィールドにdate型の値を格納する必要がある場合は、クエリー時に複雑なタイプ変換を行う必要がないため、datetimeタイプを使用するとよい.
値がすべて数値タイプの場合はIntegerを使用します.計算を行う場合、Integerタイプの値はテキストタイプの値よりも速くなります.
NULLを避ける
NULLは、カラムに値がないことを示します.データベースの結果を損なうため、このタイプの値はできるだけ避けなければなりません.たとえば、データベース内のすべての受注金額の合計を取得する必要がありますが、ある受注レコードの金額はnullであり、空のポインタに注意しないと、計算結果に異常が発生する可能性があります.場合によっては、カラムのデフォルト値を定義する必要があります.
より多くの開発技術、面接チュートリアル、インターネット会社のプッシュを知りたいなら、私の微信の公衆番号に注目してください.不定期で福祉が支給されますよ~
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 |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+オプティマイザは、データベース・テーブルの微調整に役立つ非常に重要な情報を示しています.まず、key列が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: 0explain文を再度実行すると、次の結果が得られます.
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句は、特に1つのクエリに最適化インデックスがあり、別のクエリにも最適化インデックスがある場合に、クエリの実行がより速くなります.
たとえば、
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は更に品質のもっと良い相関結果を返すことができる.
全文検索インデックスをstudentサンプルに追加する文は、次のとおりです.
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.データベースアーキテクチャの最適化
正規化
まず、損失が発生する可能性がある場合でも、すべてのデータベース・テーブルを正規化します.たとえば、customersとordersのデータを記録するために2つのテーブルを作成する必要がある場合は、orderテーブルで顧客idで顧客を参照する必要があります.逆ではありません.次の図は、データ冗長性がなく設計されたデータベース・アーキテクチャを示しています.
それ以外に、同じ値に対して同じデータ型クラスを使用して格納します.
最適なデータ型の使用
MySQLでは、integer、float、double、date、datetime、varchar、textなど、さまざまなデータ型がサポートされています.データベース・テーブルを設計する場合は、プロパティを満たす最短のデータ型をできるだけ使用する必要があります.
たとえば、システム・ユーザー・テーブルを設計して100人を超えない場合は、user_udは、−128〜128の範囲の値をとる「TINYINT」タイプを使用する.フィールドにdate型の値を格納する必要がある場合は、クエリー時に複雑なタイプ変換を行う必要がないため、datetimeタイプを使用するとよい.
値がすべて数値タイプの場合はIntegerを使用します.計算を行う場合、Integerタイプの値はテキストタイプの値よりも速くなります.
NULLを避ける
NULLは、カラムに値がないことを示します.データベースの結果を損なうため、このタイプの値はできるだけ避けなければなりません.たとえば、データベース内のすべての受注金額の合計を取得する必要がありますが、ある受注レコードの金額はnullであり、空のポインタに注意しないと、計算結果に異常が発生する可能性があります.場合によっては、カラムのデフォルト値を定義する必要があります.
より多くの開発技術、面接チュートリアル、インターネット会社のプッシュを知りたいなら、私の微信の公衆番号に注目してください.不定期で福祉が支給されますよ~