遅いSQLの最適化の提案

遅いSQLの最適化の提案
前言:仕事の过程の中で、私は天眼のデータを処理する时多くの遅いSQLの问题に遭遇しました.また、同僚の仕事の中で出会った問題と結びつけて、どのように大量のクエリーを最適化する時に少しの経験を蓄積して、いくつかの整理をして、不足なところ、一緒に改善を検討したいと思っています.
1.スローSQLログについて

（1）    ：SET GLOBAL slow_query_log = 1;　　　#     ，       ，mysql     
（2）      ：SHOW VARIABLES LIKE '%slow_query_log%';
（3）    ：SET GLOBAL long_query_time=3;
（4）    ：SHOW 【GLOBAL】 VARIABLES LIKE 'long_query_time%';　　#                
（5）        my.cnf    ， [mysqld]   ：
　　[mysqld]
　　slow_query_log = 1;　　#  
　　slow_query_log_file=/var/lib/mysql/atguigu-slow.log　　　#     ，     host_name-slow.log
　　long_query_time=3;　　  #         SQL    ，   >10
　　log_output=FILE

2.SQL文について
1.SQLでは範囲クエリーは避け、使用する必要がある場合はAND条件の最後に使用する必要があります.
エラーの例

  <if test="begin != null and begin != '' and end != null and end != ''" >
    t.`year_month` between #{begin,jdbcType=VARCHAR} AND  #{end,jdbcType=VARCHAR}
  </if>
  <if test="mobileNumber != null and mobileNumber != ''">
    AND mobile_number = #{mobileNumber,jdbcType=VARCHAR}
  </if>

正しい例

    <if test="mobileNumber != null and mobileNumber != ''">
         mobile_number = #{mobileNumber,jdbcType=VARCHAR}
    </if>
    <if test="begin != null and begin != '' and end != null and end != ''" >
          AND (t.`year_month` between #{begin,jdbcType=VARCHAR} AND  #{end,jdbcType=VARCHAR})
    </if>

2.SQLでは、インデックス列で演算関数を使用しないようにします.これにより、インデックスが移動せず、クエリが遅くなる可能性があります.一部の集約関数はインデックス化でき、具体的な問題、具体的な分析が必要です.
3.ビッグデータ量の場合、SQLではlikeを使用してファジイクエリを行うことはできません.特に、前のファジイを使用することはできません.これではインデックスを移動できません.百万レベルのデータは、全文インデックスを作成しようとします.全文索引が作成できない場合はElasticSearchを使用して、問合せが必要なフィールドをESに入れて問合せを行い、返された結果に基づいて整合度が最大のデータをとる.

        ：MySQL    5.6   
CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`)
        ：
SELECT * FROM `student` WHERE MATCH(`name`) AGAINST(' ')

4.SQLのインデックス列はnull値を避けなければならない.null値があればインデックスを確立することができるが、nullが多すぎるとクエリー効率が低く、インデックスを維持するのはかえって資源を浪費する.nullは、データ自体に影響を及ぼさない特定の値にデフォルト設定できます.
5.大量のデータを照会する場合、or条件接続があると、後でインデックスを移動できなくなり、orをunion all可行インデックスに変更し、クエリーの効率を高める
ネット上ではmysqlでインデックスにヒットできない様々な状況をまとめた記事がよく見られますが、orを使った文ではインデックスにヒットできないという説があります.この言い方は実は正確ではありません.正しい結論はmysql 5からです.0以降、or接続のフィールドに独立したインデックスがあれば、インデックスをヒットさせることができます.ここでindex_merge特性.
mysql 5.0バージョン以前は1つのsqlで1つのインデックスしか選択できませんでしたが、sqlでorキーが使用されている場合、既存のインデックスは失効し、テーブル全体がスキャンされます.どのインデックスを行ってもmysqlは条件に合ったデータを一度に検索できないので、インデックスを放棄するしかありません.
インデックスの最左接頭辞(複数カラムインデックス)が範囲クエリーに遭遇した後、後のフィールドはインデックスを移動しません.各カラムにインデックスが設定されている場合、範囲クエリー後のインデックスフィールドも有効になります.
6.クエリーのデータ量がスタックオーバーフローする可能性があるかどうかを評価します.ある場合は、実装方法を変更して、データベース・クエリー・フェーズでクエリーのマッチングを完了する必要があります.
7.また、インデックスの有効化はクエリー条件と関係があり、異なる入力パラメータによって、時間範囲インデックスなどのインデックスの使用状況が異なり、入力時間スパンが全テーブルスキャンに近い場合、インデックスを移動しません.
8.クエリ述語はインデックスの主要な境界を使用していません.言い換えればselect*であり、インデックスを移動しない可能性があります.【注意は可能ですが、入力条件に関係しています】
例えば、あなたが調べたのはSELECT*FROMT WHERE Y=XXXです.TテーブルにY値を含むコンビネーションインデックスがある場合、オプティマイザは1行のスキャンが必要であると考えられます.この場合、オプティマイザはTABLE ACCESS FULLを選択する可能性がありますが、SELECT Y FROM T WHERE Y=XXXに変更すると、オプティマイザはBツリーから対応する値を見つけることができるため、直接インデックスにY値を見つけることができます.
3.SQLクエリ最適化explainについて
explainコマンドは、SQL文の実行計画を表示し、そのSQL文がインデックスを使用しているかどうか、テーブル全体をスキャンしているかどうかを表示します.expainからの情報は10列あり、それぞれid、select_type、table、type、possible_keys、key、key_len、ref、rows、Extraです
1.typeは、MySQLがテーブルに必要なローを見つける方法を表し、「アクセスタイプ」とも呼ばれます.
よく使われるタイプは、ALL、index、range、ref、eq_です.ref,const,system,NULL(左から右へ、性能が悪いから良い)
ALL

Full Table Scan， MySQL            

range

         ，          

const、system

  MySQL          ，         ，        。      where   ，MySQL             ,system const     ，             ，  system

2.possible_keysはMySQLがどのインデックスを使用してテーブルにレコードを見つけることができるかを指摘し、クエリーに関連するフィールドにインデックスが存在する場合、そのインデックスはリストされますが、必ずしもクエリーで使用されるとは限りません.

     NULL，        。     ，              EXPLAIN    

3.key

key   MySQL        （  ）。        ，  NULL。

4.rows

  MySQL              ，