【データベース】MySQLクエリーの最適化

公衆番号へようこそ:【
愛コード】
バックグラウンドの返信が必要な場合
2019贈呈
1 Tの学习资料よ!!
背景
この急速な発展の時代に、時間はますます重要になって、流れも非常に速くて、一部の人は大きくなって、一部の人は年を取っています.うっかりして、2019はもう3分の1を過ぎてしまいました.この4ヶ月の収穫を振り返って、何を知っていますか?メッセージを共有してくださいね.
**本題に戻ります:MySQLの検索はどのように更に速くて、更に合理的ですか?インデックス以外に学ぶことはありますか?**
げんり
何かをよりよく学ぶには、その原理と働き方から身につけやすい.道理は君たちはみな知っているから,私はくだらないことを言わない.
MySQLはクエリーリクエストを送信しますが、いったいどんな仕事をしましたか?
次の図はMySQLクエリ実行フローチャートです.

クライアントは、サーバにクエリーを送信します.

サーバは、クエリー・キャッシュを確認し、キャッシュにヒットした場合、すぐにクエリーのキャッシュ内の結果を返します.そうでなければ次の段階に進みます.

3.サービス側はSQL解析、前処理を行い、オプティマイザによって対応する実行計画を生成する.4.MySQLは、オプティマイザが生成した実行計画に基づいて、ストレージエンジンのAPIを呼び出してクエリーを実行します.5.結果をクライアントに返します.
MySQLクエリーが遅くなったのは何ですか?
MySQLの場合、クエリのオーバーヘッドを測定する最も簡単な3つの指標は次のとおりです.

応答時間

スキャンの行数

が返す行数

クエリーのオーバーヘッドを完璧に測定できる指標はありませんが、MySQLが内部でクエリーを実行する際にどのくらいのデータにアクセスする必要があるかをほぼ反映し、クエリーの実行時間を推定することができます.
クエリが遅い原因は、クエリの余分なデータが多すぎるためです.よくある理由は次のとおりです.
1.不要なレコードを問い合せます.
2.複数のテーブルが関連付けられている場合、すべての列を返します.
3.常にすべての列を取り出す
一般的な最適化テクニック
1.インデックス
最も簡単で効果的な方法は、条件にインデックス(プライマリ・キー・インデックス、通常のインデックス、一意のインデックスなど)を追加することです.注意:インデックスは別のスペースストレージを開くため、インデックスを追加する必要はありません.
2.関連サブクエリ
MySQLのサブクエリの実装は非常に悪いです.例えば次の

SELECT * FROM book WHERE book_id IN (SELECT book_id FROM author WHERE author_id = 1)

MySQLはIN()リストのオプションに特別な最適化ポリシーを持っており、MySQLはサブクエリを先に実行してauthorを含むすべてのものを返すと一般的に考えられています.idが1のbook_id.
MySQLの実行時にそうしたいかもしれません.

SELECT GROUP_CONCAT(book_id) FROM author WHERE author_id = 1
SELECT * FROM book WHERE book_id IN (1,21,3,45,656,766,213,123)

ただし、MySQLは関連する外部テーブルをサブクエリに圧縮します.次のようになります.

SELECT * FROM book WHERE EXISTS 
(SELECT * FROM author WHERE author_id = 1 AND book.book_id = author.book_id)

理由:サブクエリにbook_が必要なためid、だからMySQLは先にこのサブクエリを実行することができないと思って、先にbookに対して全表スキャンを行って、それからbook_によってidはサブクエリを行う.具体的にはEXPLAINのSQLで分析できます.
提案:1.サブクエリの代わりに左外部ジョイン(LEFTOUTER JOIN)を使用します.

SELECT * from book LEFT OUTER JOIN author USING(book_id) WHERE author.author_id = 1

影響要因:データテーブルの配置位置など、具体的な適用シーンは、どの性能が良いかを自分でexplainするしかありません.
2.ONまたはUSING句の列にインデックスがあることを確認し、インデックスを作成する際に関連する順序を考慮します.
3.UNION使用
UNIONの各句がLIMITによって結果セットの一部のみを取ることができれば,あるいは結果セットを順番に並べてからマージすることができればよい.最初の例:authorテーブルとuserテーブルの両方のテーブルが一時テーブルに格納され、最初の20個が一時テーブルから取り出されます.

(SELECT first_name FROM author ORDER BY last_name)
UNION ALL
(SELECT first_name FROM user ORDER BY last_name)
LIMIT 20

上記のように、かなり改善されています.

(SELECT first_name FROM author ORDER BY last_name LIMIT 20)
UNION ALL
(SELECT first_name FROM user ORDER BY last_name LIMIT 20)
LIMIT 20

4.最大値と最小値
たとえば、最小値を求める第1のシナリオ:

SELECT MIN(id) FROM article WHERE author = 'zero'

2つ目のシナリオ:

SELECT id FROM article USE INDEX(PRIMARY) WHERE author = 'zero' LIMIT 1

1つ目のシナリオと比較すると、効果は実際には同じですが、それらの性能は少し異なります.具体的には、自分の具体的なシーンを分析し、選択してください.
5.COUNT()クエリー
たとえば、記事idが25より大きい数を統計したい場合は、次のようにします.

EXPLAIN SELECT COUNT(*) FROM article WHERE id >25

もう1つの考え方は、まず文章の総数を調べて、25以下の数を減らすことができます.考えを提供するだけで、具体的な効果はやはりあなたの具体的な状況で、自分で比較して、優を選んで選択します.

EXPLAIN SELECT (SELECT COUNT(*) FROM article) - COUNT(*) FROM article WHERE id <=25

余談:色の異なる商品の数を区別する必要がある場合は、次のようにすることができます.

seelct count(color = 'blue' OR NULL) as blue,COUNT(color = 'red' OR NULL)  AS RED FROM items

6.GROUP BYとDISTINCT
最適化の最も効果的な方法は、インデックスを使用することです.しかしGROUP BYが間違っている場合があり、インデックスは無効になります.たとえば、2つの個別のインデックスを1つの組合せインデックスに結合します.すなわち、where条件フィールドのインデックスとgroupbyのパケットフィールドのインデックスを1つに結合します.
解決方法:この関数インデックスを参照
7.limitページング
次のクエリは、非常に一般的です.

select film_id,description from film order by title limit 50,5;

しかし、この表が大きい場合、この50が100654になると、ここMySQLは100654+5個のデータをスキャンし、100654個を捨てて最後の5個だけ行きます.1つの考え方:

select film_id,description from film inner join (select film_id from film order by title limit 50,5) as lim USING(film_id);

このアイデアは、関連付けを遅らせることでクエリーの効率を大幅に向上させ、MySQLができるだけ少ないページをスキャンできるようにすることです.アクセスが必要なレコードを取得した後、さらに関連付けられたカラムは、元のテーブルで必要なすべてのカラムをクエリーします.以上は必ずしもあなたに合っているわけではありませんが、具体的にはexplainの比較が必要です.
まとめ:全体的には、できるだけ全テーブルスキャンを少なくし、できるだけインデックスを使用して最適化します.最後に、実際のシーンでexplainでより良いsqlソリューションがあるかどうかを分析することが多い.
インデックスが無効になるシーン
1.暗黙的な変換によりインデックスが失効する.この点は重視すべきである.開発中によく犯す間違いでもある.テーブルのフィールドtu_mdnはvarchar 2(20)と定義が、クエリ時にこのフィールドをnumberタイプとしてwhere条件でOracleに渡すと、インデックスが失効する.

      ：select * from test where tu_mdn=13333333333;
      ：select * from test where tu_mdn='13333333333';

2.インデックス列の演算によるインデックス失効とは、インデックス列の演算*(+、-、/、!)を含む

      ：select * from test where id-1=9;
      ：select * from test where id=10;

3.内部関数を使用するとインデックスが無効になる.このような場合には、関数ベースのインデックスを作成する必要がある.

//      ：
select * from test where round(id)=10; //  ，  id          
 //     ：        
create index test_id_fbi_idx on test(round(id));
//   
select * from test where round(id)=10; 

4.空の変数値を比較演算子(シンボル)と直接比較しないでください.変数が空の場合は、IS NULLまたはIS NOT NULLを使用して比較するか、ISNULL関数を使用します.
5.SQLコードで二重引用符を使用しないでください.文字定数は単一引用符を使用するためです.オブジェクト名を限定する必要がない場合は、(ANSI SQL規格ではない)カッコを使用して名前を囲むことができます.
6.次の使用はインデックスを無効にします.使用しないでください.
a.<>、not in、not exist、!=
b. like "%_"パーセンテージは以前(インデックス作成時にreverse(columnName)という方法で処理できます)
c.複合インデックス内の第1位置以外のインデックス列を単独で参照する.オプティマイザは、常にインデックスの最初のカラムを使用する必要があります.インデックスが複数のカラムに確立されている場合、最初のカラムがwhere句で参照されている場合にのみ、インデックスの使用を選択します.
d.文字型フィールドが数字の場合where条件に引用符を付けない.
e.変数がtimes変数である、テーブルのフィールドがdate変数である場合.あるいは逆の状況.
一時的にこんなにたくさん統計して、もっとあれば後で補充します.
MySQLのEXPLAINの使用
EXPLAINはSQLの実行状況分析を分析するためのものです
EXPLAINコマンドの出力内容は以下の通りです.

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

各列の意味は次のとおりです.

id:SELECTクエリの識別子.SELECTごとに一意の識別子が自動的に割り当てられます.

select_type:SELECT検索のタイプ.

table:クエリーされたテーブル

partitions:一致するパーティション

type:joinタイプ

possible_keys:今回のクエリで選択可能なインデックス

key:今回のクエリで正確に使用するインデックス.

ref:keyとともに使用されるフィールドまたは定数は

です.

rows:このクエリが合計何行スキャンされたかを表示します.これは推定値です.

filtered:このクエリ条件でフィルタされたデータの割合を示す

.

extra:追加情報

もっと詳しくはこの「性能最適化神器Explain使用分析」または「高性能MySQL」を参照してください.
まとめ
クエリの最適化の目的は、結果を迅速に得ることです.SQLを書くたびに、次の点を考えなければなりません.

テーブル全体のクエリーが必要かどうか、および返されるデータが適切かどうか.

インデックスが必要かどうか、インデックスが適切かどうか.

より良い解決策があるかどうか.

最後に
Java、ビッグデータに興味があればQRコードを長く押して注目してください.私はあなたたちに価値をもたらすように努力します.あなたに少しでも役に立つと思う人は、「いいね」や「転送」を手伝ってください.公式アカウント「愛コード」に注目し、2019に返信するには関連資料がありますよ.