13 SELECT文とその他のクエリの最適化

7940 ワード
まず、すべての文に影響を与える要因の1つは、ライセンスの設定が複雑であるほど、コストがかかります.
GRANT文を実行するときは簡単なライセンスを使用します.お客様が文を実行するときは、MySQLのライセンスチェックのオーバーヘッドを削減できます.たとえば、テーブル・レベルまたはカラム・レベルの権限が付与されていない場合、サーバはtables_をチェックする必要はありません.privとcolumns_privテーブルの内容.同様に、アカウントを制限しない場合、サーバはリソースを統計する必要はありません.クエリーの量が高い場合は、ライセンスチェックのオーバーヘッドを低減するために、簡略化されたライセンス構造を使用するのに時間がかかります.
質問が特定のMySQL式または関数に関連している場合は、mysqlクライアントプログラムに付属するBENCHMARK()関数を使用してタイミングテストを実行できます.構文はBENCHMARK(loop_count,expression)です.例:
mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)

以上の結果をPentiumII 400 MHzシステムで得た.MySQLはシステム上で0.32秒以内に100000個の簡単な+式演算を実行できることを示しています.
すべてのMySQL関数は高度に最適化されるべきですが、いくつかの例外がある可能性があります.BENCHMARK()は、クエリに問題があるかどうかを特定する優れたツールです.
EXPLAIN構文(SELECT関連情報取得)
EXPLAIN tbl_name
 ：
EXPLAIN [EXTENDED] SELECT select_options

EXPLAIN文は、DESCRIBEの同義語として使用したり、MySQLがSELECT文をどのように実行するかについての情報を取得したりすることができます.
  • EXPLAIN tbl_nameはDESCRIBE tbl_nameまたはSHOW COLUMNS FROM tbl_nameの同義語.
  • SELECT文の前にキーワードEXPLAINを置くと、MySQLはSELECTをどのように処理するかを説明し、テーブルの結合と結合の順序を提供します.

    • EXPLAINの2番目の使い方
    EXPLAINを使用すると、インデックスを使用して記録を探すより速いSELECTを得るために、テーブルにインデックスを追加する必要がある時期がわかります.
    不正なインデックスの使用で問題が発生した場合は、ANALYZE TABLEを実行してテーブルの統計(キーワードセットのポテンシャルなど)を更新すると、オプティマイザの選択に影響します.
    オプティマイザがテーブルを最適な順序で結合するかどうかもわかります.オプティマイザがSELECT文をテーブルの名前順に結合するように強制するには、文はSTRAGHT_JOINはSELECTの冒頭だけではありません.
    EXPLAINは、SELECT文に使用されるテーブルごとに1行の情報を返します.テーブルは、クエリの処理中にMySQLに読み込まれる順序でリストされます.MySQLは、複数の結合(single-sweep multi-join)を一度にスキャンすることで、すべての結合を解決します.これは、MySQLが最初のテーブルからローを読み、2番目のテーブルで一致するローを見つけ、3番目のテーブルなどを意味します.すべてのテーブルが処理されると、選択したカラムが出力され、より多くの一致するローがあるテーブルが見つかるまでテーブル・リストに戻ります.このテーブルから次の行を読み込み、次のテーブルの処理を続行します.
    EXTENDEDキーワードを使用すると、EXPLAINは追加情報を生成し、SHOW WARNINGSで閲覧することができる.この情報は、オプティマイザがSELECT文のテーブルとカラム名を定義し、最適化ルールを書き換えて実行した後にSELECT文がどのようになるかを示し、最適化プロセスの他の注釈も含まれる可能性があります.
    EXPLAINの各出力行は、テーブルに関する情報を提供し、各行は次の列を含む.
  •     id

    • SELECT識別子.これはSELECTの照会シリアル番号です.
  •      select_type

    • SELECTタイプは、以下のいずれかです.
    
-         SIMPLE

  SELECT(   UNION    ) 


-        PRIMARY

    SELECT


-        UNION

UNION         SELECT  


-         DEPENDENT UNION

UNION         SELECT  ，        


-        UNION RESULT

UNION   。


-         SUBQUERY

        SELECT


-         DEPENDENT SUBQUERY

        SELECT，        



-         DERIVED

    SELECT(FROM      )



  •      table

    • 出力されたローが参照するテーブル.
  •      type

    • 結合タイプ.次に、最適なタイプから最悪のタイプにソートする結合タイプを示します.
    
-        system

     (=   )。  const         。


-        const

         ，           。      ，                    。const   ，         ！

const        PRIMARY 

KEY UNIQUE        。       ，tbl_name    const ：

SELECT * from tbl_name WHERE primary_key=1；
 
SELECT * from tbl_name
WHERE primary_key_part1=1  primary_key_part2=2；

-        eq_ref

               ，        。           ，  const  。                      UNIQUE PRIMARY KEY。

eq_ref      =            。                             。


       ，MySQL    eq_ref     ref_tables：

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;
 
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;


-       ref

               ，                  。               ，      UNIQUE PRIMARY KEY（    ，                  ），   ref。             ，         。

ref      = <=>         。

       ，MySQL    ref     ref_tables：

SELECT * FROM ref_table WHERE key_column=expr;
 
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;
 
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;

-       ref_or_null

       ref，     MySQL        NULL   。                   。

       ，MySQL    ref_or_null     ref_tables：

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;


-        index_merge

                  。      ，key            ，key_len                。


-        unique_subquery

           IN    ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery         ，         ，    。

-          index_subquery

        unique_subquery。    IN   ，                   ：

value IN (SELECT key_column FROM single_table WHERE some_expr)

-          range

         ，          。key          。key_len              。     ref  NULL。

   =、<>、>、>=、、BETWEEN  IN   ，          ，    range：

SELECT * FROM tbl_name
WHERE key_column = 10;
 
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
 
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
 
SELECT * FROM tbl_name
WHERE key_part1= 10 AND key_part2 IN (10,20,30);

-          index

      ALL  ，          。    ALL ，              。

                 ，MySQL         。


-          ALL

               ，        。          const  ，     ，           。                ALL，                      。


  •     possible_keys

    • possible_keys列は、MySQLがテーブルにローを見つけるためにどのインデックスを使用できるかを示します.なお、この列はEXPLAIN出力に示すテーブルの順序とは完全に独立している.これはpossible_keysのいくつかのキーは、実際には生成されたテーブルの順序で使用できません.
    カラムがNULLの場合、関連するインデックスはありません.この場合、WHERE句が特定の列またはインデックスに適した列を参照しているかどうかを確認することで、クエリーのパフォーマンスを向上させることができます.もしそうであれば、適切なインデックスを作成し、EXPLAINでクエリーを再度チェックします.
    1枚のテーブルに何のインデックスがあるかを見極めるためにSHOW INDEX FROM tbl_を使いますname. 
  •     key

    • key列にはMySQLが実際に使用を決定するキー(インデックス)が表示されます.インデックスが選択されていない場合は、キーはNULLです.MySQLにpossibleの使用を強制または無視するにはkeys列のインデックスは、クエリーでFORCE INDEX、USE INDEX、またはIGNORE INDEXを使用します.
    MyISAMテーブルとBDテーブルの場合、ANALYZE TABLEを実行すると、オプティマイザがより良いインデックスを選択できるようになります.MyISAMテーブルではmyisamchk--analyzeを使用できます.
  •       key_len

    • key_len列にはMySQLが使用するキーの長さを決定します.キーがNULLの場合、長さはNULLです.注意key_len値は、MySQLが実際に複数のキーワードのいくつかの部分を使用することを決定します.
  •       ref

    • ref列には、keyとともにテーブルから行を選択する列または定数が表示されます.
  •       rows

    • rows列には、MySQLがクエリーを実行する際にチェックしなければならないローの数が表示されます.
  •     Extra

    • この列には、MySQLによるクエリー解決の詳細が含まれています.列に表示できる異なるテキスト文字列について説明します.
    
-         Distinct

MySQL   1     ，               。


-        Not exists

MySQL       LEFT JOIN  ，  1   LEFT JOIN     ，                    。

                   ：

SELECT *  t1 LEFT JOIN t2 ON t1.id=t2.id
  WHERE t2.id IS NULL；

  t2.id   NOT NULL。      ，MySQL  t1.id    t1   t2   。  MySQL t2         ，   t2.id    NULL，      t2     id   。    ，  t1    ，MySQL    t2     ，  t2          。


-         range checked for each record (index map: #)

MySQL             ，                ，          。           ，MySQL        range index_merge        。

     ，               。


-        Using filesort

MySQL         ，             。                   WHERE                     。        ，         。


-         Using index

                                   。                  ，       。

-       Using temporary

      ，MySQL              。                    GROUP BY ORDER BY   。


-        Using where

WHERE                      。                ，  Extra   Using where        ALL index，          。

           ，   Using filesort  Using temporary Extra 。

-        Using sort_union(...), Using union(...), Using intersect(...)

         index_merge          


-        Using index for group-by

       Using index  ，Using index for group-by  MySQL       ，      GROUP BY DISTINCT      ，               。  ，           ，       ，         。

    EXPLAIN   rows     ，                 。         MySQL            。    max_join_size       ，               SELECT  。
    Promiseの実装から見た有限状態機
    新しい可視化処理データの言語であるVedea