[SQL]Mysql Indexの使用方法

6588 ワード
use index sql Index mysql テキストリンク

1

  • ほとんどのMysqlインデックス(PRIMARY KEY、UNIQUE、INDEX、FULTEXT)は、Bツリーに格納されます.
  • 例外、空間データ型はR-tree
    • を使用
  • メモリテーブルは、ハッシュインデックス
    • もサポートする
  • InnoDBは、FULTEXTインデックスとして逆組みリストを使用します.

    • 2 B-tree



  • 上部はルートノード、中央はブランチノード、下部はリーフノードです.

  • リーフノードには、実際のデータベースに記録されたアドレスがあります.

  • BツリーのBはバランスツリー、Bツリーはバランスツリーです.バランスツリーとは、根から葉までの距離がバランスしていることを意味します.

  • ルートノードからリーフノードへの各ステップB−Treeは、二乗的に増加する.
    -したがって、データ量が多くても、実際にデータを検索した回数は思ったより多くはありません.

  • その結果、B-Treeを使用する場合、ディスクI/Oに要する時間はデータ量に関係なく、性能の均一性が確保される.
  • 図では、120のキー値をクエリーしたい場合、最上位のルートノード100から、クエリーする部分のサイズを順番に比較し、実際のデータベースに記録されているアドレスを検索するために範囲を狭めることができます.
  • を2つのインデックスで割った:130および180、35および65.
  • 100は35と65、130と180の間で比較され、130と180の間を選択すると、以下で3つ比較される.
  • このようにノードに沿って下に行くと、120というキーが見つかります.
  • 時間の複雑さはO(logN)であった.
    • 主に
  • の範囲を検索するために使用されます.

    • 3.索引の使用例


    パフォーマンスの比較に使用するクエリーは次のとおりです.
    注:クエリ実行情報は、ノードの重複を示すTREE出力形式で表示されます.EXPLAIN ANALYZEは常にTREE出力形式を使用します.
  • MySQL 8.0.21以降のバージョンでは、FORMAT=TREEを使用して選択的に明示的に指定できます.非TREE形式はサポートされていません.

    • 1.EXPLAIN:EXPLAIN MySQLサーバが実行するクエリー、すなわち実行計画を理解するために使用します。


    explainはMySQL 8.0.19以降をサポートします.
    オプティマイザからクエリーを実行する計画を取得して表示します.
  • 例
    • EXPLAIN 
SELECT *
FROM table t 
WHERE 1=1
	AND virtual_card_yn = 'N' 
	AND card_seq > 0 
       - explain format = json 으로 주면 더 많은 정보를 알려줍니다.
   - MySQL Workbench를 사용하면 Visual Explain를 활용할 수 있습니다. 

       - 가장 직관적으로 Explain을 확인할 수 있습니다.

    2.EXPLAIN ANALYZE:下記の通り。

    1. 예상 실행 비용 (일부 반복자는 비용 모델에서 설명되지 않으므로 추정치에 포함되지 않습니다.) 
2. 반환된 행의 예상 수 
3. 첫 번째 행을 반환하는 시간 모든 행을 반환하는 데 걸리는 시간(실제 비용)
4. (여러 개의 루프가 있는 경우 이 그림은 루프당 평균 시간을 나타냅니다.) iterator가  반환한 행 수 루프 수
    クエリ実行の推定コスト
    結果行の予想数
    結果の最初のローを返す時間(ミリ秒)
    すべての結果を返す時間(ミリ秒)
    反復器が返すローの数
    ループ実行回数

    3.使用例

  • 特定のテーブルのインデックスをチェック
    • SHOW index FROM table_name
  • インデックス追加クエリー
    • ALTER TABLE tablename ADD INDEX indexname (column1, column2);
    ADD INDEX idx_age (col1); インデックスの設定方法
    インデックスを正しく設定するには、インデックスを個別に宣言する必要があります.
    //X -> INDEX(id, age)
    //O -> INDEX(id), INDEX(age)
  • インデックス
    • を使用
    SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index)
  WHERE col1=1 AND col2=2 AND col3=3;

    実習

  • code = SHOW INDEX FROM cust_card_apply_hist
  • を1つだけ持ってみると
    • です
    `Table` : cust_card_apply_hist
`Key_name` : idx_cust_card_apply_hist_01
`Seq_in_index` : 3
`Column_name` : APPLY_TYPE_CD
`Index_type` : BTREE
`Cardinality` : 53588

  • EXPLAIN ANALYZEで比較
    	- 2-1. use index **사용안하고** :
    -> Filter: (ccah.PLATFORM_CD = 'A') (cost=6229.55 rows=6109) (actual time=0.100..50.178 rows=34828 loops=1)
    -> Table scan on ccah (cost=6229.55 rows=61093) (actual time=0.097..42.047 rows=104972 loops=1)

  • 2-2. use indexを使用するには:
    -> -> Filter: (ccah.PLATFORM_CD = 'A') (cost=6229.55 rows=6109) (actual time=0.092..56.711 rows=34828 loops=1)
    -> Table scan on ccah (cost=6229.55 rows=61093) (actual time=0.089..48.410 rows=104972 loops=1)
    実際の時間が減ったことがわかります.

    • Ubuntu16.04環境でのNuclioのコンパイル
    12日目オンラインレッスンのJavaScript