MySQL-アクセス方法

15034 ワード
前言
最近MySQLを学んで、記録することを決定して、どれだけ書くことができて、不定期に更新して、頑張ります.
本文
いくつかの部分に分けて、大体次のようにします.
  • 文字セットと比較規則
  • 行フォーマットとデータページ
  • InnoDBインデックス
  • アクセス方法と接続
  • explainとサブクエリ最適化
  • redoとundoログ
  • MVCCとロック
    • ここでは第4部
  • アクセス方法とは?
  • MySQLはデータベースで、行ごとに格納され、MySQLはあなたのSQL文に基づいて特定の行またはいくつかの行の実行方法、すなわち を見つける.

    • アクセス方法
    アクセス方法は大きく6つに分けられますが、SQL文が上記のように実行されているかどうかをどのように確認しますか?MySQLは、explain文を提供します.ここでは、次の出力列のtypeフィールド値に注目するだけでいいです.
    テストのためにテーブルを作成し、次のSQLはこのテーブルに基づいています.
    CREATE TABLE `single_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int(11) DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key2` (`key2`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    次の1つを見てみましょう.
    一、conf
    プライマリ・キー列または唯一の2次インデックスのNULL以外の等値クエリ
  • 一意の2次インデックスまたはクラスタインデックス
  • 唯一の2次インデックスはNULLの数を制限せず、複数の列に一致するのはconf方式ではない
    • である.
  • 唯一の2次インデックスへのアクセスは、まず自分のB+ でプライマリ・キー列の値を取得し、
    • をテーブルに戻す必要があります.
  • プライマリ・キーまたは一意の2次インデックスが複数のカラムで構成されている場合は、
    • をスムーズに1等値で一致させる必要があります.
    1つのレコードに正確に一致している場合にのみ、confアクセスできます.
    2つの例を見てみましょう
  • クラスタリングインデックス
    • mysql>
mysql> explain select * from single_table where id = 100;
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
  • 一意の2次インデックス
    • mysql>
mysql> explain select * from single_table where key2 = 100;
+----+-------------+--------------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | const | idx_key2      | idx_key2 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

    二、ref
    二次インデックス列と定数等値の比較
  • はユニークなインデックスではなく、 の複数のレコードに一致する可能性がある.ここではNULLの等値一致
    • は含まれていないことに注意する.
  • 必ずしもクエリ列の2次インデックスを歩くとは限らない.2次インデックス列がどれだけのレコードに一致するか、何回テーブルに戻るか、そのコストが全テーブルスキャンより大きいかによって~~~
    • である.
    2次インデックス・カラムに複数のカラムがある場合、各カラムが等値で一致する必要はありません.左から一致を開始するだけでrefにアクセスできます.
    //   
mysql>
mysql> explain select * from single_table where key_part1 = 'one_part1';
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | ref  | idx_key_part  | idx_key_part | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

//    
mysql>
mysql> explain select * from single_table where key_part2 = 'one_part1';
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | single_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

    三、ref_or_null
    これはよく理解して、refの基礎の上で1つのIS NULLについての条件をプラスします
    mysql>
mysql> explain select * from single_table where key_part1 = 'one_part1' or key_part1 is null;
+----+-------------+--------------+------------+-------------+---------------+--------------+---------+-------+------+----------+-----------------------+
| id | select_type | table        | partitions | type        | possible_keys | key          | key_len | ref   | rows | filtered | Extra
  |
+----+-------------+--------------+------------+-------------+---------------+--------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | single_table | NULL       | ref_or_null | idx_key_part  | idx_key_part | 303     | const |    2 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------------+---------------+--------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (1.55 sec)

    2つの の記録区間が形成されている
    四、range
    クラスタインデックスまたは2次インデックスの区間クエリー
  • 単点区間
  • 範囲区間
    • mysql>
mysql> explain select * from single_table where id > 10;
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | single_table | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 1000 |   100.00 | Using where |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> explain select * from single_table where key2 < 10;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | single_table | NULL       | range | idx_key2      | idx_key2 | 5       | NULL |   10 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

    もう一つ例を見てみましょう
    mysql>
mysql> explain select * from single_table where key2 > 10;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | single_table | NULL       | ALL  | idx_key2      | NULL | NULL    | NULL | 1000 |    98.90 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

    ここはALLですが、まだ話していませんが、全表スキャンだと推測していますが、なぜrange方式ではなく全表スキャンなのでしょうか.
  • key2 > 10という条件下で持ち出すにはリターンテーブルの記録が多すぎるため、コストはクラスタインデックスの小さな
    • を直接スキャンするよりもましだ.
    五、index
    クエリー列と条件列インデックス列
    単純で乱暴:
  • クエリー・カラムインデックス・カラム
  • 条件列インデックス列
    • mysql>
mysql> explain select key_part1, key_part3 from single_table where key_part2 = 'one_part1';
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 
| id | select_type | table        | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    | 
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 
|  1 | SIMPLE      | single_table | NULL       | index | NULL          | idx_key_part | 909     | NULL | 1000 |    10.00 | Using where; Using index | 
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 
1 row in set, 1 warning (0.00 sec)

    なぜ2級インデックスに違いないのですか?
  • の正確な一致主キー列はconf
    • である.
  • 範囲マッチングプライマリ・キー列はrange
    • である.
    六、all
    最後に、テーブル全体をスキャンし、クラスタインデックスを直接スキャンすることは、最も時間のかかるアクセス方法です.
    ここでは、これらのアクセス方法について簡単に説明します.MySQLの接続を見てみましょう.
    せつぞく
    通常、クエリは複数のテーブルに関連する場合はあまり多くありません.これらの複数のテーブル間のクエリは と呼ばれます.
    接続の本質は,各接続テーブルのレコードを取り出して順番に一致する組合せを結果セットに加えることである.
    一、デカルト積
    接続がまだ登場していないとき、私たちは何枚かの表を調べてどうやって遊びますか?
    mysql>
mysql> select * from t1, t2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    |    2 | b    |
|    2 | b    |    2 | b    |
|    1 | a    |    3 | c    |
|    2 | b    |    3 | c    |
|    1 | a    |    4 | d    |
|    2 | b    |    4 | d    |
+------+------+------+------+
6 rows in set (0.20 sec)

    簡単に2枚の表のデータをつなぎ合わせて、数学の上で という名前があります
  • この方法は、三七二十一にかかわらず、クエリ条件を満たすカラムのみが最後の結果セット
    • に追加される.
    二、内外接続
    様々な使用状況を満たすために、MySQLは を提供する.
    彼らはクエリに関連する複数のテーブルを区分した- ( )と ( )
    遊び方は、まず を調べて、手に入れた記録を に行って二次スクリーニングを行います
    2枚以上の表は、1枚の表を とすることができ、その他の表は とし、再帰処理する.
    ないぶせつぞく
  • 四種類の書き方
    • SELECT * FROM t1 JOIN t2;

SELECT * FROM t1 INNER JOIN t2;

SELECT * FROM t1 CROSS JOIN t2;

SELECT * FROM t1, t2;
  • 駆動テーブルの記録は、最後の結果セット
    • に加わることなく、駆動テーブルに一致する記録が見つからない.
  • の場合、 と は互いに交換可能であり、最後のクエリ結果
    • には影響しない.
    がいぶせつぞく
  • のレコードは、 に一致するレコードがない場合でも、結果セット
    • に追加する必要がある.
  • 選択方式によって、外部接続は2種類に分けられます
  • 左外接続:左側を選択する表は
    • である.
  • 右外部接続:右側を選択する表は
    • である.
  • 左(外)接続
    • SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON      [WHERE       ];
  • 右(外)接続
    • SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON      [WHERE       ];

    外部接続後は必ずon句を接続しなければならない外部接続後は必ずon句を接続しなければならない外部接続後は必ずon句を接続しなければならない
    クエリー条件
  • where句
  • 内接続であれ外接続であれ、WHERE句中のフィルタ条件に合致しない記録は、最後の結果セット
    • に加わることはない.
  • on句
  • は に対して、すべて参加します
  • は に対して、加入に合致し、NULL充填
    • に合致しない.
  • は に対して、加入に合致し、NULL充填
    • に合致しない.
  • は に対して、加入に合致し、NULL充填
    • に合致しない.

  • 内接続に対して、on句はwhere句と等価である
    • .
    一般的な場合
  • 単表のみのフィルタ条件をWHERE句中
    • に入れる.
  • 両表に係るフィルタ条件をON句中
    • に入れる.
  • 一般的にON句に入れるフィルタ条件を接続条件
    • と呼ぶ. については、 における記録が においてON句の条件を満たす記録が見つからない場合でも に加える
    詳細なクエリーについては、次のルールに従います.
  • は、on句に適合する駆動テーブルに記録を先に持ち出し、on句が駆動テーブルに関与しない場合は、全駆動テーブルに記録する
  • これらの適合記録に基づいて、on句を用いて被駆動テーブルに記録される
  • 被駆動テーブルに関係しない場合、被駆動テーブルは全て加入する
  • マッチングされた被駆動テーブルレコードは
    • に追加する.
  • が一致しない場合、nullは
    • を充填する.


  • 残りの不整合な駆動テーブルレコードは、nullを使用して
    • を充填する. と は、何度も読む価値のある2つの言葉です.
    次はいくつかのテストの例です.
  • 被駆動テーブル不整合on句
    • mysql> select * from t1 left join t2 on t2.m2 = 1;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    | NULL | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)
  • 被駆動テーブルマッチングon句
    • mysql> select * from t1 left join t2 on t2.m2 = 2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    |    2 | b    |
|    2 | b    |    2 | b    |
+------+------+------+------+
2 rows in set (0.00 sec)
  • 駆動テーブル不整合on句
    • mysql> select * from t1 left join t2 on t1.m1 = 3;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    | NULL | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)
  • 駆動テーブルマッチング部on句
    • mysql> select * from t1 left join t2 on t1.m1 = 1;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    |    2 | b    |
|    1 | a    |    3 | c    |
|    1 | a    |    4 | d    |
|    2 | b    | NULL | NULL |
+------+------+------+------+
4 rows in set (0.00 sec)

    接続クエリー
  • ネストされたループ接続<Nested-Loop Join>
  • 先
  • 1 1サイクル検索
    • アクセスは1回のみであり、 アクセス回数は クエリーで得られたレコード数に依存する.
  • ブロックベースのネストされたループ接続<Block Nested-Loop Join>
  • join buffer
    • を用いる.
  • join_buffer_sizeデフォルト262144バイト、最小128バイト、
  • join bufferが1本の記録しか入れないほど小さくなると、
    • に退化する.

    インデックスは常に考慮すべき最適化スキームです
    CXF WebService
    【zz】C#フォームダイアログモード