完全なMySQL学習ノート

MyISAM と InnoDB

対比	マイISAM	翻訳
主キーと外部キー	サポートされていません	サポート
取引	サポートされていません	サポート
行テーブルロック	テーブルロック: 1 つのレコードを操作する場合でも、テーブル全体がロックされるため、同時実行性の高い操作には適していません。	行ロックは、他の行に影響を与えずに操作中に行のみをロックするため、高い同時実行性に適しています。
キャッシュ	インデックスのみをキャッシュし、他のデータはキャッシュしない	キャッシュインデックスと実データは大量のメモリを必要とし、メモリサイズはパフォーマンスに影響します。
表領域	小さい	大きい
集中	パフォーマンス	取引
デフォルトのインストール	はい	はい

パフォーマンスの低下と SQL の速度低下の理由:

クエリの書き方が不適切
インデックス失敗
関連クエリ内の結合が多すぎる (設計上の欠陥または避けられない要件)
サーバーのチューニングと各種パラメータ設定（バッファ、スレッドパラメータ）

MySQL 実行順序

手書き
機械の読み取りはから始まる

SQL結合

テーブル

 mysql> tbl_dept から * を選択します。
 +----+----------+--------+
 | id | 部門名 | 場所追加 |          
 +----+----------+--------+
 | 1 | ラウンド | 11 |
 | 2 | HR | 12 |
 | 3 | MK | 13 |
 | 4 | MIS | 14 |
 | 5 | FD | 15 |
 +----+----------+--------+
 セット内の行数は 5 です (0.00 秒)

b テーブル

 +----+------+--------+
 | ID | 名前 | 部門 ID |
 +----+------+--------+
 | 1 | z3 | 1 |
 | 2 | z4 | 1 |
 | 3 | z5 | 1 |
 | 4 | w5 | 2 |
 | 5 | w6 | 2 |
 | 6 | s7 | 3 |
 | 7 | s8 | 4 |
 | 8 | s9 | 51 |
 +----+------+--------+
セット内の行数は 8 です (0.00 秒)

mysql不支持全連接

使用以下方式可以實現全連接

 mysql> tbl_dept a から * を選択し、 tbl_emp b を a.id=b.deptId で右結合します。
     -> 結合
     -> tbl_dept a から * を選択し、tbl_emp b を a.id=b.deptId で左結合します。
 +------+----------+--------+-------+-------+-------+
 | id | deptName | locAdd | id | 名前 | deptId |
 +------+----------+--------+-------+-------+-------+
 | 1 | RD | 11 | 1 | z3 | 1 |
 | 1 | RD | 11 | 2 | z4 | 1 |
 | 1 | RD | 11 | 3 | z5 | 1 |
 | 2 | HR | 12 | 4 | w5 | 2 |
 | 2 | HR | 12 | 5 | w6 | 2 |
 | 3 | MK | 13 | 6 | s7 | 3 |
 | 4 | MIS | 14 | 7 | s8 | 4 |
 | NULL | NULL | NULL | 8 | s9 | 51 |
 | 5 | FD | 15 | NULL | NULL | NULL |
 +------+----------+--------+-------+-------+-------+
 セット内の行数は 9 です (0.00 秒)

aの一意性とbの一意性

 mysql> select * from tbl_dept a left join tbl_emp b on a.id=b.deptId (b.id は null)
     -> 結合
     -> select * from tbl_dept a right join tbl_emp b on a.id=b.deptId where a.id is null;
 +------+----------+--------+-------+-------+-------+
 | id | deptName | locAdd | id | 名前 | deptId |
 +------+----------+--------+-------+-------+-------+
 | 5 | FD | 15 | NULL | NULL | NULL |
 | NULL | NULL | NULL | 8 | s9 | 51 |
 +------+----------+--------+-------+-------+-------+
 セットに2行（0.01秒）

索引

インデックスの定義:

インデックスは、SQLがデータを効率的に取得するのに役立つデータ構造です。インデックスの本質は、數據結構です。

簡単に言えば、排好序的快速查找數據結構である。

データベースシステムでは、データに加えて、特定の検索アルゴリズムを満たすデータ構造も保持されます。これらのデータ構造は、何らかの方法 (参照) でデータを指し示すため、これらのデータ構造に高度な検索アルゴリズムを実装できます。このデータ構造はインデックスです。次の図は例です。

一般的に、インデックスも非常に大きいため、インデックスファイルとしてディスクに保存されることがよくあります。

インデックスについて話すときは、特に指定がない限り、通常は B ツリー (多方向検索ツリー、必ずしもバイナリではない) 構造で編成されたインデックスを指します。

このうち、クラスター化インデックス、セカンダリインデックス、複合インデックス、プレフィックスインデックス、ユニークインデックスはすべてデフォルトで B+ ツリーインデックスを使用しており、総称してインデックスと呼ばれます。もちろん、B+ ツリーインデックスの他にハッシュインデックスもあります。

インデックスの長所と短所

1. 利点

大学図書館の図書番号の索引付けと同様に、データ検索の効率が向上し、データベースの IO コストが削減されます。

インデックスでデータをソートすると、データのソートコストが削減され、CPU消費が削減されます。

2. デメリット

実際、インデックスは主キーとインデックス付きのフィールドを格納し、エンティティテーブルのレコードを指すテーブルでもあるため、インデックス列もスペースを占有します。

インデックスはクエリ速度を大幅に向上させますが、更新、挿入、削除などのテーブル更新操作は遅くなります。これは、テーブルを更新するときに、MySQL はデータだけでなくインデックスファイルも保存する必要があるためです。インデックスを使用してフィールドを更新するたびに、更新によってキー値が変更された後にインデックス情報が調整されます。

インデックスは効率を向上させるための 1 つの要素にすぎません。大量のデータを含むテーブルの場合、効率を向上させるために単にインデックスを追加するのではなく、最適なインデックスを作成するか、優れたクエリステートメントを記述する必要があります。

インデックス分類

単一値インデックス
ユニークインデックス
総合指数
基本的な構文:

作成する

mytable(cloumnname(length)) に [一意の] インデックス indexName を作成します。

alter mytable に [unique] index [indexName] を (columnname(length)) に追加します。

消去

mytableのインデックス[indexName]を削除します

チェック

table_name\G からインデックスを表示

テーブルにインデックスを追加する方法は 4 つあります。

MySQL インデックス構造

BTree インデックス
ハッシュインデックス
全文索引
Rツリー

どのような場合にインデックスを作成する必要がありますか?

主キーは自動的に一意のインデックスを作成します
クエリ条件として頻繁に使用されるフィールドにはインデックスを付ける必要がある
クエリ内の他のテーブルに関連するフィールド、外部キー関係がインデックス化されます
頻繁に更新されるフィールドは、更新のたびにレコードが更新されるだけでなくインデックスも更新されるため、インデックスの作成には適していません。
where条件で使用されていないフィールドにはインデックスを作成しない
単一キー/組み合わせインデックス選択の問題は誰ですか? (同時実行性が高い場合は、結合インデックスが推奨されます)
クエリ内のソートフィールドは、インデックスを介してソートフィールドにアクセスすると、ソート速度が大幅に向上します。
クエリ内のフィールドをカウントまたはグループ化する

インデックスを作成しない方が良い場合

テーブル内のレコードが少ない
DML文を頻繁に操作するテーブル
テーブルフィールドのデータは繰り返され、均等に分散されるため、最も頻繁にクエリされ、並べ替えられるデータ列のみがインデックス化されます。データ列に多くの繰り返しコンテンツが含まれている場合、インデックス化しても実質的な効果はあまりないことに注意してください。

パフォーマンス分析

要点を説明する

何ができるでしょうか？

表の読み順
データ読み取り操作の操作タイプ
使用できるインデックス
実際に使用されているインデックスはどれですか?
テーブル間の参照
オプティマイザによって各テーブルにクエリされる行数

イドの3つの状況

IDは同じで、実行順序は上から下です
idが異なります。サブクエリの場合はid番号が増加し、idが大きいほど優先度が高くなります。
IDは同じだが異なっており、同時に存在する

選択タイプ

SIMPLE シンプルなクエリ
PRIMARY メインクエリ（最も外側のクエリ）
サブクエリ
DERIUED クエリのサブクエリの一時テーブル
連合
UNION RESULT 結合クエリの結果

タイプ：：

タイプはアクセスタイプの配置を示し、これはより重要な指標である

最高から最低の順に次のとおりです。

system > const > eq_ref> ref > range > index > ALL ;

一般的に言えば、クエリが少なくとも範囲レベル、できればrefレベルに到達することを確認する必要があります。

- - - - - - - - - - - - - - - - - - - - - - - タイプ - - - - - - - - - - - - - - - - - - - - - - - - - - -

system: テーブルにはレコードの行が 1 つだけあります (システムテーブルと同じ)。これは const 型の特別な列であり、通常は表示されないため無視できます。
const: 検索がインデックスを通じて 1 回実行されることを示します。const は主キーまたは一意のインデックスを比較するために使用されます。一致するデータは 1 行のみなので、MySQL は主キーを where リストに配置することでクエリを定数にすばやく変換できます。
eq_ref: ユニークインデックススキャン。テーブル内に一致するレコードは 1 つだけ。主キーまたはユニークインデックススキャンによく使用されます (2 つのテーブルは多対 1 または 1 対 1 の関係で、接続されたテーブルが 1 つの場合、そのクエリは eq_ref です)
ref: 非一意インデックススキャンは、単一の値に一致するすべての行を返します。これは基本的にインデックスアクセスであり、単一の値に一致するすべての行を返しますが、検索とスキャンの組み合わせである複合条件で複数の行が見つかる場合があります。
範囲: インデックスを使用して行を選択し、指定された範囲内の行のみを取得します。キー列には、使用されているインデックスが表示されます。通常、where ステートメントには between、<、>、in などのクエリが表示されます。この範囲スキャンインデックスは、完全なテーブルスキャンよりも優れています。
index: index と ALL の違いは、index はインデックスツリーのみをトラバースし、インデックスファイルは通常データファイルよりも小さいことです。
ALL: テーブル全体のスキャン

- - - - - - - - - - - - - - - - - - - - - - - タイプ - - - - - - - - - - - - - - - - - - - - - - - - - - -

possible_keys: 適用可能なインデックスを表示します（理論的に）
キー: 実際に使用されるインデックス。クエリでカバーインデックスが使用されている場合、インデックスはキーにのみ表示されます。
key_len: インデックスで使用されるバイト数を示します。短いほど精度が損なわれません。kenlen によって表示される値は、インデックスフィールドの最大可能長であり、実際に使用される長さではありません。kenlen はテーブル定義に基づいて計算され、テーブルから取得されるものではありません。

key_lenの長さ: 13 char(4)*utf8(3)+nullが許可される(1)=13

ref: インデックスのどの列が使用されているか、可能な場合は定数、およびインデックス列の値を参照するために使用される列または定数を示します。

行数: テーブルの統計とインデックスの選択に基づいて、必要なレコードを見つけるために読み取る必要がある行数を大まかに計算します。

インデックスが作成されていない場合は、t1 t2 テーブルをクエリします。t2 テーブルに対応する t1 テーブルの ID。t2 テーブルの col1 の値は 'ac' である必要があります。

Id フィールドの場合、テーブル t1 はテーブル t2 の 1 対多に相当します。

t1 テーブルのタイプは eq_ref で、これはユニークインデックススキャンを意味します。テーブル内にはそれに一致するレコードが 1 つだけあります。t2 テーブルには、t1 テーブルの id に対応する col 値が 1 つだけあります。t2 テーブルの主キー id インデックスクエリによると、t1 テーブルから 1 行が読み取られ、t2 テーブルから 640 行が読み取られます。

インデックス作成後

t1 は 1 行を読み取り、t2 は 142 行を読み取り、ref は非一意インデックススキャンを実行し、単一の値に一致するすべての行を返し、t2 の id に対応する列のすべての行を返しますが、t1 には id に対応する列の行が 1 つしかないため、型は eq_ref です。

余分な

他の列に収まらない重要な情報が含まれています

\G : 縦に並び替えて表示

ファイルソートの使用: これは、MySQL がテーブル内のインデックスの順序でデータを読み取る代わりに、外部インデックスを使用してデータをソートすることを意味します。MySQL でインデックスを使用してソートを完了できない操作は、ファイルソートと呼ばれます。ボックスで囲まれていない図は複合インデックスを確立していますが、col3 を直接使用してソートすると、空中城塞になります。MySQL では、ファイルソフトを使用するしかありません。

一時テーブルの使用: 一時テーブルは中間結果を保存するために使用されます。MySQL はクエリ結果をソートするときに一時テーブルを使用します。一般的に、order by ソートや group by グループ化で使用されます。上記のテーブルでは複合インデックス col1_col2 が確立されていますが、col2 で直接グループ化すると、MySQL はファイルソフトを実行して一時テーブルを作成する必要があります。
using index は、対応する選択操作でカバーインデックスが使用され、テーブルのデータ行へのアクセスが回避されることを示します。using where が同時に出現する場合は、インデックスがインデックスキー値の検索を実行するために使用されることを意味します。using where が出現しない場合は、検索アクションを実行する代わりに、インデックスがデータの読み取りに使用されることを意味します。
where を使うということは where フィルタリングを使うということである
結合バッファをプライベートに使用するとリンクキャッシュが使用される
不可能バッファの where 句の値は常に false であり、タプルの取得には使用できません。
select テーブルが最適化され、group by 句がない場合、インデックスに基づいて min/max 操作が最適化されるか、MyISAM ストレージエンジンで count(*) 操作が実行されます。最適化は、実行操作の実行を待たずに、クエリ実行プランの生成段階で完了します。
distinctiveはdistinct操作を最適化し、最初に一致するタプルを見つけた直後に同一値の検索を停止します。

場合

インデックスの最適化

単一テーブルの最適化

 存在しない場合はテーブルを作成します `article`(
 
 `id` INT(10) UNSIGNED NOT NULL 主キー AUTO_INCREMENT,
 `author_id` INT (10) UNSIGNED NOT NULL,
 `category_id` INT(10) UNSIGNED NOT NULL , 
 `views` INT(10) UNSIGNED NOT NULL , 
 `コメント` INT(10) UNSIGNED NOT NULL,
 `title` VARBINARY(255) NOT NULL、
 `content` テキストが NULL ではありません
 );
 `article`(`author_id`,`category_id`,`views`,`comments`,`title`,`content`) に値を挿入します
 (1,1,1,1,'1','1')、
 (2,2,2,2,'2','2')、
 (1,1,3,3,'3','3');
 
 記事から*を選択;
 mysql> category_id = 1 かつ comments > 1 の場合、記事から id、author_id を選択し、views desc limit 1 で並べ替えます。
 +----+-----------+
 | id | 著者ID |
 +----+-----------+
 | 3 | 1 |
 +----+-----------+
 セット内の 1 行 (0.00 秒)
 
 mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc li
 1を模倣する;
 +----+-------------+----------+-----------+--------+-------+-------+--------+---------+-----------------------------+-----+-------+---------+----------+-----------------------------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+----------+-----------+--------+-------+-------+--------+---------+-----------------------------+-----+-------+---------+----------+-----------------------------+
 | 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | where の使用; filesort の使用 |
 +----+-------------+----------+-----------+--------+-------+-------+--------+---------+-----------------------------+-----+-------+---------+----------+-----------------------------+
 セットに 1 行、警告 1 件 (0.00 秒)

クエリは完了しているものの、type が all であり、Extra に using filesort が表示されており、クエリ効率が非常に低いことがわかります。

最適化が必要

インデックスを作成する

article(category_id,comments,views) にインデックス idx_article_ccv を作成します。

クエリ

 mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
 +----+-------------+----------+-----------+--------+-------+-------+--------+---------------------------------------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+----------+-----------+--------+-------+-------+--------+---------------------------------------+
 | 1 | SIMPLE | article | NULL | range | inx_article_ccv | inx_article_ccv | 8 | NULL | 1 | 100.00 | インデックス条件を使用; filesort を使用 |
 +----+-------------+----------+-----------+--------+-------+-------+--------+---------------------------------------+
 セットに 1 行、警告 1 件 (0.00 秒)

ここで、型が範囲に変更され、テーブル全体のクエリが範囲クエリに変更され、少し最適化されていることがわかります。

ただし、extra は依然として filesort を使用しているため、インデックスの最適化が成功していないことがわかります。

そこでインデックスを削除します

記事のインデックス idx_article_ccv を削除します。

新しいインデックスを作成し、範囲を除外する

article(category_id,views) にインデックス idx_article_cv を作成します。
 mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
 +----+-------------+----------+-----------+--------+----------------+----------------+----------+---------+-----------+-------------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+----------+-----------+--------+----------------+----------------+----------+--------+-----------+-------------+
 | 1 | SIMPLE | article | NULL | ref | idx_article_cv | idx_article_cv | 4 | const | 2 | 33.33 | where の使用 |
 +----+-------------+----------+-----------+--------+----------------+----------------+----------+--------+-----------+-------------+
 セットに 1 行、警告 1 件 (0.00 秒)
この時点で最適化が成功していることがわかります。型はrefに変更され、extraはusing whereに変更されました。

この実験では、別のテストを追加して、インデックスを作成するときに最後にコメントを配置することも可能であることがわかりました。mysql> create index idx_article_cvc on article(category_id,views,comments);
 クエリは正常、影響を受けた行は 0 行 (0.02 秒)
 レコード: 0 重複: 0 警告: 0
 
 mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
 +----+-------------+----------+-----------+--------+-----------------+---------+---------+-----------+-------------+-------------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+----------+-----------+--------+-----------------+---------+---------+-----------+-------------+-------------+
 | 1 | SIMPLE | article | NULL | ref | idx_article_cvc | idx_article_cvc | 4 | const | 2 | 33.33 | where の使用 |
 +----+-------------+----------+-----------+--------+-----------------+---------+---------+-----------+-------------+-------------+
 セットに 1 行、警告 1 件 (0.00 秒)

この時点で最適化が成功していることがわかります。型はrefに変更され、extraはusing whereに変更されました。
この実験では、別のテストを追加し、インデックスを作成するときに最後にコメントを配置することも可能であることがわかりました。

ここで、type はまだ ref であり、extra はまだ usingwhere であることがわかりますが、インデックス作成位置が変更され、範囲クエリフィールドが最後に移動されています。

デュアルテーブル最適化

 `class`が存在しない場合はテーブルを作成します(
 `id` INT(10) UNSIGNED NOT NULL 主キー AUTO_INCREMENT,
 `カード` INT (10) UNSIGNED NOT NULL
 );
 存在しない場合はテーブルを作成します `book`(
 `bookid` INT(10) UNSIGNED NOT NULL 主キー AUTO_INCREMENT,
 `カード` INT (10) UNSIGNED NOT NULL
 );
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 クラス(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
  
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 book(card)VALUES(FLOOR(1+(RAND()*20)))に挿入します。
 
 mysql> book(card) にインデックス Y を作成します。
  select * from book left join class on book.card=class.card; を説明します。
 +----+-------------+--------+-----------+--------+-------+---------------+-------+-------+-------+--------+---------+----------------------------------------------------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+--------+-----------+--------+-------+---------------+-------+-------+-------+--------+---------+----------------------------------------------------+
 | 1 | SIMPLE | ブック | NULL | インデックス | NULL | Y | 4 | NULL | 20 | 100.00 | インデックスを使用 |
 | 1 | SIMPLE | クラス | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | where の使用; 結合バッファーの使用 (ブロック ネスト ループ) |
 +----+-------------+--------+-----------+--------+-------+---------------+-------+-------+-------+--------+---------+----------------------------------------------------+
 セットに 2 行、警告 1 件 (0.00 秒)

大きな違いはなく、依然として完全なテーブルクエリであることがわかります。これは、2 つのテーブルが左結合でクエリされるためです。左側のテーブルは完全にクエリされる必要があります。この時点では、右側のテーブルにインデックスを作成するだけで十分です。

逆に、右側のリンクは左側のテーブルにインデックスされている必要があります。

適切なテーブルにインデックスを作成する

 クラスにインデックス Y を作成します。
 select * from book left join class on book.card=class.card; を説明します。
 +----+-------------+--------+-----------+---------+---------------+-------+---------+----------------+------+------------+-------------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+--------+-----------+---------+---------------+-------+---------+----------------+------+------------+-------------+
 | 1 | SIMPLE | ブック | NULL | インデックス | NULL | Y | 4 | NULL | 20 | 100.00 | インデックスを使用 |
 | 1 | SIMPLE | クラス | NULL | ref | Y | Y | 4 | db01.book.card | 1 | 100.00 | インデックスを使用 |
 +----+-------------+--------+-----------+---------+---------------+-------+---------+----------------+------+------------+-------------+
 セットに 2 行、警告 1 件 (0.00 秒)

正しいテーブルは 1 回だけクエリされることがわかります。。タイプはrefです

3つのテーブルの最適化

 存在しない場合はテーブルを作成します `phone`(
 `phoneid` INT(10) UNSIGNED NOT NULL 主キー AUTO_INCREMENT,
 `カード` INT (10) UNSIGNED NOT NULL
 )エンジン = INNODB;
 
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。
 電話(カード)にVALUES(FLOOR(1+(RAND()*20)))を挿入します。

まずすべてのインデックスを削除します

 本のインデックス Y をドロップします。
 クラスのインデックス Y を削除します。
 説明: select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
 +----+-------------+--------+-----------+--------+---------------+-------+-------+-------+----------+----------+----------------------------------------------------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+--------+-----------+--------+---------------+-------+-------+-------+----------+----------+----------------------------------------------------+
 | 1 | SIMPLE | クラス | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
 | 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | where の使用; 結合バッファーの使用 (ブロック ネスト ループ) |
 | 1 | SIMPLE | phone | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | where の使用; 結合バッファーの使用 (ブロック ネスト ループ) |
 +----+-------------+--------+-----------+--------+---------------+-------+-------+-------+----------+----------+----------------------------------------------------+
 セットに 3 行、警告 1 件 (0.00 秒)

インデックスを作成する

 book(card)にインデックスyを作成します。
 
 phone(card)にインデックスzを作成します。
 説明: select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
 +----+-------------+--------+------------+---------+---------------+-------+----------+----------------+------+------------+-------------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+--------+------------+---------+---------------+-------+----------+----------------+------+------------+-------------+
 | 1 | SIMPLE | クラス | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
 | 1 | SIMPLE | book | NULL | ref | y | y | 4 | db01.class.card | 1 | 100.00 | インデックスを使用 |
 | 1 | SIMPLE | 電話 | NULL | ref | z | z | 4 | db01.book.card | 1 | 100.00 | インデックスを使用 |
 +----+-------------+--------+------------+---------+---------------+-------+----------+----------------+------+------------+-------------+
 セットに 3 行、警告 1 件 (0.00 秒)

インデックスが正常に作成されたことがわかります。。ただし、 left join 最左表必須全部查詢。

 クラス(card)にインデックスxを作成します。
 説明: select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
 +----+-------------+---------+-----------+----------+---------------+-------+----------+----------------+------+------------+-------------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+---------+-----------+----------+---------------+-------+----------+----------------+------+------------+-------------+
 | 1 | SIMPLE | クラス | NULL | インデックス | NULL | x | 4 | NULL | 20 | 100.00 | インデックスを使用 |
 | 1 | SIMPLE | book | NULL | ref | y | y | 4 | db01.class.card | 1 | 100.00 | インデックスを使用 |
 | 1 | SIMPLE | 電話 | NULL | ref | z | z | 4 | db01.book.card | 1 | 100.00 | インデックスを使用 |
 +----+-------------+---------+-----------+----------+---------------+-------+----------+----------------+------+------------+-------------+
 セットに 3 行、警告 1 件 (0.00 秒)

結果は同じだ

テーブルを作成

 テーブルスタッフの作成(
 id INT 主キー AUTO_INCREMENT、
 `name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'Name',
 `age` INT NOT NULL DEFAULT 0 COMMENT'年齢',
 `pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'位置',
 `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'ジョブエントリ時間'
 )CHARSET utf8 COMMENT'従業員記録テーブル';
 staffs(`name`,`age`,`pos`,`add_time`) に VALUES('z3',22,'manager',NOW()) を挿入します。
 staffs(`name`,`age`,`pos`,`add_time`) に VALUES('7月',23,'dev',NOW()) を挿入します。
 staffs(`name`,`age`,`pos`,`add_time`) に VALUES('2000',23,'dev',NOW()) を挿入します。
インデックスを作成します。 ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);

インデックスのヒント

1. 先頭のインデックスは消えず、中間のインデックスは壊れない: 複合インデックスを作成するときは、先頭のインデックスを含める必要があります。中間のインデックスをスキップして、後続のインデックスを直接使用することはできません。後続のインデックスを使用するには、中間のインデックスを追加する必要があります (最初に後続のインデックスを使用してから中間のインデックスを使用することはできますが、後続のインデックスを直接使用して中間のインデックスをスキップすることはできません) (where の場合)

上の図から、名前を省略するとインデックスが使用できないことがわかります。

 mysql> explain select * from staffs where name='july';
 +----+-------------+----------+-----------+----------+--------------------------+--------------------------+--------+-------+-------+-------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+----------+-----------+----------+--------------------------+--------------------------+--------+-------+-------+-------+
 | 1 | SIMPLE | スタッフ | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
 +----+-------------+----------+-----------+----------+--------------------------+--------------------------+--------+-------+-------+-------+
 セットに 1 行、警告 1 件 (0.00 秒)
 
 mysql> explain select * from staffs where name='july' and pos='dev';
 +----+-------------+----------+------------+----------+--------------------------+--------------------------+--------+--------+----------+-------------------------+
 | id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
 +----+-------------+----------+------------+----------+--------------------------+--------------------------+--------+--------+----------+-------------------------+
 | 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 33.33 | インデックス条件の使用 |
 +----+-------------+----------+------------+----------+--------------------------+--------------------------+--------+--------+----------+-------------------------+
 セットに 1 行、警告 1 件 (0.00 秒)

ステートメントから、中間のインデックスをスキップした後も key_len は変更されないことがわかり、インデックス pos が使用されていないことが証明されます。

2. インデックス列に対しては演算や型変換などの操作は実行できません。
3. ストレージエンジンは、インデックス内の範囲条件の右側の列を使用できません（インデックス列の計算が少なくなります）
4. 選択の使用を減らすために、カバーリングインデックス、つまりインデックスのみにアクセスするクエリを使用するようにしてください。
5. less (!=, <>, <, >) は null ではなく、null です。
6. '%' で始まるとインデックスが失敗します (インデックスの失敗を回避するには、カバーインデックスを使用します) カバーインデックス: (作成されるインデックスの順序は、クエリされるフィールドの順序と一致している必要があります)
7. 文字列が一重引用符で囲まれていない場合、インデックスは無効になります（MySQL は文字列型を強制的に変換するため、インデックスが無効になります）
8. 接続に失敗する原因となるため、以下を使用してください。

インデックスケース

インデックス(a, b, c)を想定

Yはすべてのインデックスが使用されていることを意味し、Nはいずれも使用されていないことを意味します。

where文	インデックスは使用されていますか?
ここでa=3、c=5	(bは途中で切れている) aは使われているがcは使われていない
ここで、a=3、b=4、c=5	はい
ここで、a=3、c=5、b=4	ここでMySQLはステートメントのソートを自動的に最適化します
ただし、a=3、b>4、c=5	a,bが使用される
ここで、a=3、bは「k%」、c=5である。	Yは他の定数と同様にインデックスで始まる
ただし、b=3、c=4	いいえ
ただし、a=3、c>5、b=4	Y：MySQLは、インデックスが無効になる前にステートメントソート範囲Cを自動的に最適化します
ここで、b = 3およびc = 4およびa = 2	Y：MySQLは、ステートメントソートを自動的に最適化します
ここで、c = 5およびb = 4およびa = 3	Y：MySQLは、ステートメントの順序を自動的に最適化します

インデックス（a、b、c、d）を仮定します

 テーブルTest03を作成します（
 id int null auto_incrementではないプライマリキー、
 int（10）、
 b int（10）、
 C int（10）、
 D int（10）、
 
 test03（a、b、c、d）値に挿入（3,4,5,6）;
 test03（a、b、c、d）値に挿入（3,4,5,6）;
 test03（a、b、c、d）値に挿入（3,4,5,6）;
 test03（a、b、c、d）値に挿入（3,4,5,6）;
 
 test03（a、b、c、d）でインデックスidx_test03_abcdを作成します。

###

ここで、a = 3およびb> 4およびc = 5	AとBを使用すると、B後のすべてのインデックスが無効になります。
ここで、a = 3およびb = 4およびd = 6順にc	AとBは実際に使用されますが、ソートに使用され、MySQLではカウントされません。
ここで、a = 3およびb = 4次のc	AとBは実際に使用されますが、ソートに使用され、MySQLではカウントされません。
ここで、a = 3およびb = 4次のd	aとbを使用して、ここでCをスキップすると、filesortが使用されます
ここで、a = 3およびd = 6次のb、c	Aが使用され、ソートはインデックスBとcを使用します。
ここで、a = 3およびd = 6次のc、b	bがスキップされてcを並べ替えるため、aを使用するとfilesortを使用することができます。
ここで、a = 3およびb = 4次のb、c	yすべてを使用します
ここで、a = 3およびb = 4およびd＆## 61;	bはcとbを並べ替える前に、bが既に決定されているため、bを使用するため、bはbを使用しています。

グループごとにグループごとに並べ替えます。

インデックス最適化による注文

Orderbyの状態	余分な
ここで、aによる> 4注文	インデックスを使用する場所を使用します
ここで、a> 4注文、b	インデックスを使用する場所を使用します
ここで、bによる> 4注文	場所を使用して、indexを使用して、filesortを使用します（注文の背後にあるリーダーはそこにありません）
ここで、bによる> 4注文、a	場所を使用して、インデックスを使用して、filesortを使用します（注文による注文）
ここで、bによるa = const order、c	場所で使用されるインデックスの左端の接頭辞が定数として定義されている場合、注文はインデックスを使用できます
ここで、a = constおよびb = const order c	インデックスの左端のプレフィックスが定数として定義されている場合、次に順序でインデックスを使用できます
ここで、bcによるa = constおよびb> 3の注文	インデックスを使用する場所を使用します
ASC、B DESC、C DESCによる注文	一貫性のないリフトを並べ替えます

exsites

 Aを選択します。
 上記のクエリは、存在するa.length（）の結果を実行します
 resultSet = [];
 for（int i = 0; i <a.length; i ++）{if（exists（a [i] .id）{// b.id = a.idからselect 1を実行してレコードがあるかどうかを確認します。
 
 : : : : : : : : : : : : : : :

MySQLスロークエリログコマンド

「％slow_query_log％」などの変数を表示します。

MySQLスロークエリログを有効にするかどうかを示します

グローバルslow_query_log = 0を設定します。

MySQLスロークエリログを閉じます

グローバルslow_query_log = 1を設定します。

MySQLスロークエリログをオンにします

'％long_query_time％'などの変数を表示します。

表示するのにどれくらい時間がかかりますか？

グローバルlong_quert_time = 10を設定します。

遅いクエリ時間を10秒に変更します。

「％slow_queries％」などのグローバルステータスを表示します。

スロークエリステートメントの数を示します

[root@iz0jlh1zn42cgftmrf6p6sz data]＃cat mysql-slow.log

LinuxクエリスローSQL

関数操作バッチ挿入データ

 テーブル部を作成します（
    id int unsignedプライマリキーAuto_increment、
    Deptno Mediunint unsigned Not Nullデフォルト0、
    dname varchar（20）null default ''、
    loc varchar（13）nullデフォルトではない ''
 ）Engine = InnoDBデフォルトcharset = gbk;
 テーブルEMPを作成します（
    id int unsignedプライマリキーAuto_increment、
    : : : : : : : : : : : : : : :

 'log_bin_trust_function_creators'のような変数を表示します。
 グローバルlog_bin_trust_function_creators = 1を設定します。

創建函數：隨機產生部門編號隨機產生字符串

DELIMITER $$は、SQLがすべて使用されているためですが、機能を作成するプロセスは何度も使用する必要があります。

//関数1を定義します

 デリミッター$$
 functionrand_string（n int）を作成するvarchar（255）を返します
 始める
    宣言chars_set varchar（100）デフォルト 'abcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyz';
    declare return_str varchar（255）default '';
    declare i int default 0;
    私がしますが
       set return_str = concat（return_str、substring（chars_set、floor（1 + rand（）*52）、1））;
       i = i + 1を設定します。
    while;
    return return_str;
 $$を終了します
//関数2を定義します

 デリミッター$$
 functionrand_num（）returns int（5）を作成する
 始める
    declare i int default 0;
    i = floor（100 + rand（）*10）を設定します。
    私を返します。
 $$を終了します
//ストアドプロシージャ1を定義します1

 デリミッター$$
 手順insert_empを作成する（start int（10）、in max_num int（10））
 始める
    declare i int default 0;
    autocommit = 0を設定します。
    繰り返す
    i = i + 1を設定します。
    emp（empno、enname、job、mgr、hiredate、sal、comm、deptno）値（（start + i）、rand_string（6）、 'salesman'、0001、curdate（）、2000,400、rand_num（））;
    i = max_numまで  
    繰り返し終了します。
    専念;
 $$を終了します
//ストアドプロシージャ2を定義します

 デリミッター$$
 手順insert_dept（start int（10）、in max_num int（10））を作成する
 始める
    declare i int default 0;
    autocommit = 0を設定します。
    繰り返す
    i = i + 1を設定します。
    Insert Into Dept（deptno、dname、loc）values（（start + i）、rand_string（10）、rand_string（8））;
    i = max_numまで  
    繰り返し終了します。
    専念;
 $$を終了します
// Data Delimiterを挿入します。
 insert_dept（100,10）を呼び出します。
 insert_emp（100001,500000）を呼び出します。

プロファイル分析SQLを表示します

 mysql>「プロファイリング」などの変数を表示します。
 +---------------------+
 | variable_name |
 +---------------------+
 |プロファイリング|
 +---------------------+
 セット内の 1 行 (0.00 秒)
 
 mysql> set profiling = on;
 クエリOK、0行の影響を受ける、1つの警告（0.00秒）
 
 mysql>「プロファイリング」などの変数を表示します。
 +---------------------+
 | variable_name |
 +---------------------+
 |プロファイリング|
 +---------------------+
 セットの1列（0.01秒）

いくつかの挿入ステートメントを書く

クエリ操作ステートメントの速度を表示します

 mysql> showプロファイル。
 +----------+------------+----------------------------------------------------------------+
 | query_id |
 +----------+------------+----------------------------------------------------------------+
 |
 | 0.00018850 |
 |。
 | 0.00023900 |
 | 0.00031125 |。
 |。
 | 0.00023725 |。
 | 8.00023825 |。
 |。
 |。
 | 11 | .00024550 |。
 |。
 +----------+------------+----------------------------------------------------------------+
 セットの12行、1つの警告（0.00秒）

クエリプロセスSQLライフサイクルを表示します

 mysql> showプロファイルcpu、query 3のブロックio。
 +----------------------+----------+----------+------------+--------------+---------------+
 |
 +----------------------+----------+----------+------------+--------------+---------------+
 | 0.000040 |
 |
 |
 |オープンテーブル|
 | Query End |
 |閉じるテーブル|
 | 0.000010 |
 | 0.000006 |
 +----------------------+----------+----------+------------+--------------+---------------+
 セットの8行、1つの警告（0.00秒）
 
 mysql> showプロファイルcpu、query for query 12のブロックio。
 +----------------------+----------+----------+------------+--------------+---------------+
 |
 +----------------------+----------+----------+------------+--------------+---------------+
 |
 |
 |オープンテーブル|
 | init |
 |システムロック|
 |最適化|
 | 0.000010 |
 | 0.000005 |
 | TMPテーブル|
 | 0.000001 |
 |
 |
 |
 |
 | Query End |
 | TMPテーブル|
 | Query End |
 | 0.000004 |
 |
 | 0.000004 |
 +----------------------+----------+----------+------------+--------------+---------------+
 セットの20行、1つの警告（0.00秒）

上記の4つのいずれかが表示される場合、クエリステートメントを最適化する必要があります

グローバルクエリログ

  グローバルgeneral_log = 1を設定します。
 グローバルlog_output = 'table'を設定します。

その後、あなたが書いたSQLステートメントは、MySQLライブラリのGeneral_logテーブルに記録されます

 select * from mysql.general_log;
 mysql> select * from mysql.general_log;
 +----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
 | event_time |
 +----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
 | 2021-12-06 11：53.457242 |
 +----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
 セット内の 1 行 (0.00 秒)

mysqlロック

読み取りロック（共有ロック）：同じデータについて、互いに影響を与えることなく複数の読み取り操作を同時に実行できます。
書き込みロック（排他的ロック）：現在の書き込み操作が完了していない場合、他の書き込みロックと読み取りロックをブロックします。
行ロック：InnoDBエンジンに向かって偏っており、大きなオーバーヘッドと遅いロックがあり、デッドロックが発生します。ロックの粒度は最小で、ロック競合の確率は最低で、並行性は高くなります。
テーブルロック：小さなオーバーヘッドと高速ロック、ロック競合の最高の確率、および最も低い並行性を備えたMyisamエンジンに偏っています。

下のテストテーブルロック

 big_dataを使用してください。
 
 テーブルMyLockを作成します（
 id int null primary key auto_increment、
 名前varchar（20）デフォルト ''
 ）エンジンmyisam;
 
 mylock（name）valuesに挿入（ 'a'）;
 mylock（name）valuesに挿入（ 'b'）;
 mylock（name）values（ 'c'）に挿入;
 mylock（name）valuesに挿入（ 'd'）;
 mylock（name）valuesに挿入（ 'e'）;
 
 mylockから *を選択します。

ロックコマンド

 ロックテーブルmylock読み、本の書き込み; ##ロックmylock書き込みロックブックを読む
 開いたテーブルを表示します##ロック解除テーブル; ##キャンセルロックを表示します

テーブルロック：ロックを読み取ります

 ##読み取りロックを追加した後、mysql>ロックテーブルmylock read; ## 1
 クエリは正常、影響を受けた行は 0 行 (0.00 秒)
 
 mysql> select * from mylock; ## 1
 +----+------+
 | id |
 +----+------+
 |
 |
 | 3 |
 | 4 |
 | 5 |
 +----+------+
 セット内の行数は 5 です (0.00 秒)
 
 mysql> mylock set name = 'a2'ここで## 1;
 エラー1099（HY000）：テーブル「MyLock」は読み取りロックでロックされていて、更新できません
 ##現在の読み取りロックによってロックされたテーブルを変更できません
 エラー1100（HY000）：テーブル「本」はロックテーブルでロックされていません

2つのコマンドを区別するために、1は元のMySQLコマンド端末の操作と見なされ、2は新しく作成されたMySQL端末と見なされます

新しいMySQL端子コマンド操作を作成します

 ##新しいMySQLターミナルコマンド操作mysql> mylock set name = 'a3'を更新します

ブロッキング操作が見つかります

元のMySQLコマンド端子のロックをキャンセルします

 テーブルのロックを解除; ## 1
 クエリOK、1行の影響を受ける（2分1.46秒）## 2
 一致する行：1変更：1警告：0 ## 2

あなたはそれが2分以上ブロックされていることがわかります

概要：ロックテーブルを読んだ後、MyLock：1。Query操作：現在のクライアント（ターミナルコマンド操作1）がクエリテーブルMyLockを実行できます

他のクライアント（ターミナルコマンド操作2）は、テーブルMyLock 2をクエリすることもできます。DML操作（追加、削除、および変更）

テーブルロック：書き込みロック

 mysql>ロックテーブルmylock write;
 クエリは正常、影響を受けた行は 0 行 (0.00 秒)
現在のセッションに書き込みロックを追加しますmyLockテーブルmysql>更新mylock set name = 'a4'where id = 1;
 クエリOK、1行の影響を受ける（0.00秒）
 一致する行：1の変更：1警告：0
 
 mysql> select * from mylock;
 +----+------+
 | id |
 +----+------+
 | 1 |
 |
 | 3 |
 | 4 |
 | 5 |
 +----+------+
 mysql> select * from book;
 エラー1100（HY000）：テーブル「本」はロックテーブルでロックされていません

他のテーブルは操作できませんが、ロックされたテーブルを操作できることがわかります

新しいクライアントを開いて、ロックされたテーブルをテストします

 mysql> select * from mylock;
 
 セットの5列（2分30.92秒）

新しいクライアントの書き込みロックによってロックされたテーブルは、操作（追加、削除、変更、およびチェック）がブロックされるとブロックされることがわかりました。

する

分析テーブルロック

 mysql>「テーブル％」のようなステータスを表示します。
 +---------------------------------+
 | variable_name |
 +---------------------------------+
 |
 |
 |
 |
 |
 +---------------------------------+
 セット内の行数は 5 です (0.00 秒)

行ロック

INNODBの行ロックモード

INNODBは、次の2種類のラインロックを実装します。

共有ロック：s共有ロックと呼ばれる読み取りロックとも呼ばれます。
排他的なロック（X）とも呼ばれ、排他的なロックと呼ばれます。

更新、削除、およびステートメントを挿入するために、INNODBは、関係するデータセットに排他的ロック（x）を自動的に追加します。

通常の選択ステートメントの場合、InnoDBはロックを追加しません。

レコードセットは、次のステートメントを通じて共有ロックまたは排他的ロックで表示できます。

 共有ロック（s）：select * from table_name where ... lockin share mode
 
 排他的ロック（x）：select * from table_name where ... for update

ラインロックはトランザクションをサポートしているため、こちらを確認してください

トランザクション

トランザクションは、SQLステートメントで構成される一連の論理処理ユニットです

Atomicity：トランザクションは、データ上のすべての操作を実行するか、まったく実行しない原子動作ユニットです。
一貫性：データは、トランザクションの開始時と完了時に一貫性を保つ必要があります。これは、データの整合性を維持するために、すべての関連データをトランザクションの変更に適用する必要があります。
分離：データベースは、外部の同時操作の影響を受けない「独立した」環境でトランザクションが実行されることを保証するための特定の分離メカニズムを提供します。これは、トランザクションプロセスの中間状態が外側には見えないことを意味し、その逆も同様です。
耐久性：トランザクションが完了した後、データ上の操作は永続的であり、システム障害がある場合でも維持できます。

同時トランザクションによって引き起こされる問題：

更新は失われ、汚い読書、再繰り返しの読書、ファンタジーの読書が失われます

酸性特性	意味
原子性	トランザクションは、データの変更が成功するか失敗する原子操作ユニットです。
一貫性のある	データは、トランザクションの開始時と完了時に一貫している必要があります。
分離	データベースシステムは、外部の同時操作の影響を受けずに「独立した」環境でトランザクションが実行されるように、特定の分離メカニズムを提供します。
耐久性	トランザクションが完了した後、データの変更は永続的になります。

同時トランザクション処理によって引き起こされる問題

質問	意味
紛失したアップデート	2つ以上のトランザクションが同じ行を選択すると、変更された初期トランザクションの値は、後続のトランザクションの値によって上書きされます。
汚れた読み取り	トランザクションがデータにアクセスし、データの変更があり、この変更がデータベースに送信されていない場合、別のトランザクションもデータにアクセスし、データを使用します。
繰り返しのない読み取り	トランザクションは、特定のデータを読んだ後、以前に読み取られたデータを読み取りますが、以前に読まれたデータと矛盾していることがわかります。
Phantomは読みます	トランザクションは、同じクエリ条件で照会されたデータを再読しますが、他のトランザクションがクエリ条件を満たす新しいデータを挿入していることがわかります。

トランザクション分離レベル

上記のトランザクションの並行性の問題を解決するために、データベースはこの問題を解決するための特定のトランザクション分離メカニズムを提供します。データベースのトランザクション分離が厳しくなるほど、並行性の副作用は小さくなりますが、トランザクションの分離は本質的に「シリアル化」されているため、価格が大きくなります。

データベースの4つの分離レベルがあります。低から高く、読み取り、読み取り、繰り返しの読み取り、およびシリアル化可能なものがあります。

分離レベル	紛失したアップデート	汚い読書	繰り返し読むことはできません	ファンタジーリーディング
コミットされていないことを読んでください	×	√	√	√
コミットされた読み物を読んでください	×	×	√	√
繰り返し読み取り（デフォルト）	×	×	×	√
シリアル化可能	×	×	×	×

注： √ 代表可能出現， × 代表不會出現。

MySQLデータベースのデフォルトの分離レベルは、再現可能な読み取り、表示方法です。

 「tx_isolation」のような変数を表示します。

ロックテストテーブルの構築、ケースの準備作業

 テーブルtest_innodb_lockを作成する（
    id int（11）、
    名前varchar（16）、
    セックスバルチャー（1）
 ）Engine = InnoDBデフォルトcharset = utf8;
 
 test_innodb_lock値に挿入（1、 '100'、 '1'）;
 test_innodb_lock値に挿入（3、 '3'、 '1'）;
 test_innodb_lock値に挿入（4、 '400'、 '0'）;
 test_innodb_lock値に挿入（5、 '500'、 '1'）;
 test_innodb_lock値に挿入（6、 '600'、 '0'）;
 test_innodb_lock値に挿入（7、 '700'、 '0'）;
 test_innodb_lock値に挿入（8、 '800'、 '1'）;
 test_innodb_lock値に挿入（9、 '900'、 '1'）;
 test_innodb_lock値に挿入（1、 '200'、 '0'）;
 
 Test_innodb_lock（id）でインデックスidx_test_innodb_lock_idを作成します。
 index_innodb_lock_name on test_innodb_lock（name）;

ロックテスト

自動トランザクションの提出がロックを自動的に追加してロックを解放するため、2つの端子テストを開きて自動トランザクションの送信をオフにすることをお勧めします。

 mysql> set autocommit = 0;

 mysql> set autocommit = 0;

クエリには効果がないことがわかります

左を更新します

 mysql> update test_innodb_lock set name = '100'ここでid = 3;
 クエリは正常、影響を受けた行は 0 行 (0.00 秒)
 一致する行：1変更：0警告：0

左を更新します

右側を更新した後、操作を停止します

 mysql>更新test_innodb_lock set name = '340' where id = 3;
 エラー1205（HY000）：トランザクションを再起動してください

ロックがリリースされるか、トランザクションがコミットされるまでブロックが実行されることがわかります。

對于innodb引擎來說，對某一行數據進行DML（增刪改）操作會對操作的那行添加排它鎖

他のトランザクションはこの声明の行を実行することはできませんが、他の行からデータを操作できます。

無索引行鎖會升級成表鎖：如果不通過索引條件檢索數據，那么innodb會對表中所有記錄加鎖，實際效果和表鎖一樣

操作のパフォーマンス時にインデックスを使用することを忘れないでください：InnoDBエンジンインデックスの失敗タイムロックはテーブルロックにアップグレードされます

 mysql>更新test_innodb_lock set sex = '2' where name = 400;
 クエリは正常、影響を受けた行は 0 行 (0.00 秒)
 一致する行：2の変更：0警告：0

この名前には、ここに単一の引用インデックスがないことに注意してください。

 mysql> update test_innodb_lock set sex = '3' where id = 3;
 クエリOK、1行の影響を受ける（23.20秒）
 一致する行：1の変更：1警告：0

私は、他の行操作もブロッキング状態に閉じ込められていることがわかりました。

もともとは、1列のデータのみをロックしていましたが、名前フィールドに単一の引用符を追加するのを忘れていたため、インデックスはすべてのテーブルをロックしました。

ギャップロック

データを取得し、共有または排他的なロックを要求する代わりに、この範囲の条件には存在しないレコードがあります

 mysql> select * from test_innodb_lock;
 +------+------+
 | id |
 +------+------+
 | 1 100 |
 | 3 |
 | 400 |
 | 500 |
 | 600 |
 | 700 |
 | 800 |
 | 900 |
 |
 +------+------+
 ID2のデータはありません

ロックの要求を確認してください

 mysql>「innodb_row_lock％」のようなステータスを表示します。
 +------------------------------------+
 | variable_name |
 +------------------------------------+
 |
 |
 |
 |
 |
 +-----------------------------------+
 セット内の行数は 5 です (0.00 秒)
 innodb_row_lock_current_waits：現在待っているロックの数
 innodb_row_lock_time：システムの起動から今までの合計ロック時間
 innodb_row_lock_time_avg：それぞれの待機に費やされる平均時間
 innodb_row_lock_time_max：システムの起動から今までの最長時間を待つのに時間がかかる時間
 innodb_row_lock_waits：システムが開始されてからの総合数時間の総数

ロックサマリー

行レベルのロックにより、InnoDBストレージエンジンはテーブルロックメカニズムよりも高いパフォーマンス損失をもたらす可能性がありますが、全体的な並行性処理機能はMyisamのテーブルロックとはほど遠いものです。システムが並行している場合、INNODBの全体的なパフォーマンスはMyisamと比較して比較的明らかな利点があります。

ただし、Innodbの列レベルのロックは、不適切に使用する場合、Innodbの全体的なパフォーマンスもMyisamよりも高くないかもしれませんが、さらに悪いかもしれません。

最適化の提案：