mysql インデックスとトランザクションの詳細な解釈

この記事では、mysql のインデックスとトランザクションについて詳しく説明します。必要な方は参考にしてください。

1. インデックスは何をするのでしょうか?

アプリケーションの SQL クエリの実行が非常に遅い場合は、インデックスを構築できるかどうかを検討する必要があります。 。

ほとんどの MySQL インデックス (PRIMARY KEY、UNIQUE、INDEX、および FULLTEXT) は B ツリーに保存されます。空間列タイプのインデックスのみが R ツリーを使用し、MEMORY テーブルはハッシュ インデックスもサポートします。

インデックスは、データを含む行のインデックス値と物理アドレスを格納する並べ替えられたリストです。これは、データが非常に大きい場合、インデックスによってクエリが大幅に高速化されるためです。インデックスを使用すると、特定の行のデータを見つけるためにテーブル全体をスキャンする必要がなく、まずインデックス テーブルを通じてデータの行に対応する物理アドレスを見つけてから、対応するデータにアクセスします。

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

利点: 迅速な取得、I/O の数の削減、および取得の高速化が可能です。 ; グループ化と並べ替えにより、グループ化と並べ替えが高速化されます。

欠点: インデックス自体もテーブルであるため、一般に、インデックス テーブルが占有する領域を占有します。データテーブルの1.5倍である; インデックステーブルのメンテナンスと作成には時間がかかり、データ量が増えると増加するため、データテーブルの変更操作（削除、追加、変更）の効率が低下します。データ テーブルを変更するときに必要です。インデックス テーブル;

3. インデックスの分類

一般的なインデックスの種類は次のとおりです:主キー インデックス、一意のインデックス、通常のインデックス、フルテキスト インデックス、複合インデックス

1、主キー インデックス: つまり、主キー。インデックスは主キー pk_clolum (長さ) に基づいて確立されます。重複は許可されず、null 値も許可されません。

ALTER TABLE 'table_name' ADD PRIMARY KEY('id')；

2、一意のインデックス: インデックスの作成に使用される列の値は一意である必要があり、null 値は使用できません。は許可されています

ALTER TABLE 'table_name' ADD UNIQUE('email')；

3, 通常のインデックス: 制限なしでテーブルインデックス内の通常の列で構築されます

ALTER TABLE 'table_name' ADD INDEX index_name('description')；

4, フルテキストインデックス: 大きなテキスト オブジェクトの列で構築されたインデックス (次のパートで説明します)

ALTER TABLE 'table_name' ADD FULLTEXT('content')；

5, 結合インデックス: 複数の列の値を組み合わせて構築されたインデックス。これらの複数の列には null 値を含めることはできません

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

「左端のプレフィックス」の原則に従い、最も一般的に使用されるものを検索または並べ替えに使用します。列は降順で左端に配置されます。結合インデックスは、col1、col1col2、col1col2col3 の 3 つのインデックスを確立することと同等であり、col2 またはcol3 ではインデックスを使用できません。

結合インデックスを使用する場合、列名の長さが長すぎるためにインデックス キーが大きすぎる可能性があり、その結果、許可されている場合は、col1 とcol2 の最初の数文字しか取得できません。

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

は、col1 の最初の 4 文字と、col2 の最初の 3 文字をインデックスとして使用することを意味します

4. インデックスの実装原則

MySQL のサポート 多くのストレージ エンジンがあり、ストレージ エンジンごとにインデックスのサポートが異なります。そのため、MySQL データベースは、BTree インデックス、B Tree インデックス、ハッシュ インデックス、フルテキスト インデックスなどの複数のインデックス タイプをサポートします。

1. ハッシュ インデックス:

メモリ ストレージ エンジンのみがハッシュ インデックスをサポートします。ハッシュ インデックスは、インデックス列の値を使用して値の hashCode を計算し、その値を含む行を次の場所に保存します。ハッシュアルゴリズムはデータの物理的な位置にアクセスするために使用されるため、アクセス速度は非常に高速ですが、値は 1 つのハッシュコードにのみ対応し、ハッシュ分散方式であるため、ハッシュ インデックスは範囲検索および並べ替え機能をサポートしていません。

2. フルテキスト インデックス:

FULLTEXT (フルテキスト) インデックスは、MyISAM と InnoDB にのみ使用できます。フルテキスト インデックスの生成には非常に時間がかかります。そしてスペースを消費します。大きなテキスト オブジェクトまたは大きな CHAR 型データの場合、通常のインデックスを使用しても、テキストの最初の数文字を一致させることは可能ですが、テキストの途中のいくつかの単語を一致させたい場合は、次のようにする必要があります。 LIKE %word% 一致させるには処理に時間がかかり、応答時間が大幅に増加します。この場合、FULLTEXT インデックスを使用すると、単語のリストが生成されます。この単語のリストに基づいてインデックスが作成されます。 FULLTEXT はテーブルの作成時に作成することも、必要に応じて ALTER または CREATE INDEX を使用して追加することもできます。

//创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
id INT(10) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
my_text text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
FULLTEXT(my_text));

//创建表以后，在需要的时候添加FULLTEXT索引
ALTER my_table ADD FULLTEXT ft_index(my_text);
CREATE INDEX ft_index ON my_table(my_text);

より大きなデータ セットの場合は、FULLTEXT インデックスを持たないテーブルにデータを追加してから、FULLTEXT を追加します。インデックスは、すでに FULLTEXT インデックスがあるテーブルにデータを追加するよりも高速です。

MySQL に付属のフルテキスト インデックスは、MyISAM ストレージ エンジンでのみ使用できます。他のデータ エンジンの場合、フルテキスト インデックスは有効になりません。

MySQL では、全文インデックスの切り離しは英語で利用できますが、現在中国語ではサポートされていません。

MySQL では、取得する文字列が短すぎると、期待した結果を取得できません。また、取得する文字列にストップ ワードが含まれる場合は、その文字列の長さが 4 バイト以上である必要があります。無視されます。

3. BTree インデックスと B Tree インデックス

BTree インデックス

BTree は、ツリーの次数を d (d>1) とします。高さが h の場合、BTree は次の条件を満たす必要があります:

各リーフ ノードの高さは同じで、h に等しい;

各非リーフ ノードは、n-1 個のキーと n 個のポインタで構成されます。ここで、d

リーフ ノード ポインタはすべて null;

非リーフ ノードのキーはすべて [key, data] です。 ] タプルのうち、key はインデックスとしてのキーを表し、data はキー値が配置されている行のデータです。

# BTree の構造は次のとおりです。

# in BTree の構造では、二分探索法を使用できます。一般に、ツリーの高さは h*log(n) です。 、通常は約 3 であるため、BTree は非常に効率的な検索構造です。

B Tree Index

B Tree は BTree の変形です。d をツリーの次数、h をツリーの高さとします。B Tree と BTree の主な違いは次のとおりです。

B ツリーの非リーフ ノードはデータを格納せず、キー値のみを格納します。

B ツリーのリーフ ノードにはポインタがなく、すべてのキー値がリーフ ノードに表示されます。キーに格納されているキーの値 対応するデータの物理アドレス

B ツリーの構造は次のとおりです。 ## 一般に、B Tree は BTree よりも実装に適しています。 外部メモリのインデックス構造は、ストレージ エンジン設計の専門家が外部メモリ (ディスク) のストレージ構造、つまりディスクのセクタを巧みに利用しているためです。ページ (ページ) の整数倍で、ページはストレージ内の単位です。通常、デフォルトは 4K であるため、インデックス構造のノードはページのサイズになるように設計され、「先読み」原理が使用されます。外部メモリのデータを読み出すたびにノード全体のデータをメモリに読み込んでメモリに格納する必要があるため、メモリの読み取り速度は外部メモリの読み取り I/O 速度の数百倍であることが知られています。検索速度を向上させる鍵は、ディスク I/O をできるだけ少なくすることです。そうすれば、各ノードのキーの数が大きくなるほど、ツリーの高さが小さくなり、必要な I/O 回数が少なくなることがわかります。したがって、一般に、B Tree は BTree よりも高速です。これは、B Tree の非リーフ ノードにデータが格納されないため、より多くのデータをキーとして格納できるためです。

シーケンシャルインデックスを備えたB TREE

多くのストレージエンジンはBツリーに基づいて最適化されており、隣接するリーフノードにポインタを追加し、シーケンシャルアクセスポインタを備えたBツリーを形成しています。間隔検索の効率を向上させるため、最初の値が見つかる限り、後続の値を順番に検索できます。

B Tree の構造は次のとおりです。

MySQL のインデックス構造の実装原理を分析した後、見てみましょう。ストレージ エンジンはインデックス構造をどのように実装しますか? MySQL で最も一般的な 2 つのストレージ エンジンは、それぞれ非クラスター化インデックスとクラスター化インデックスを実装する MyISAM と InnoDB です。

まず、インデックスの分類では、インデックスのキーが主キーであるかどうかに応じて、「主インデックス」と「補助インデックス」に分けることができます。 . 主キーの値を使用して作成されたインデックスを「主インデックス」、それ以外を「補助インデックス」と呼びます。したがって、主インデックスは 1 つだけ存在でき、補助インデックスは多数存在する可能性があります。

MyISAM—非クラスター化インデックス

MyISAM ストレージ エンジンは非クラスター化インデックスを使用します。非クラスター化インデックスのプライマリ インデックスと補助インデックスはほぼ同じです。プライマリ インデックスの繰り返しは許可されず、null 値は許可されず、そのリーフ ノードのキーには、キー値に対応するデータを指す物理アドレスが格納されます。 ノンクラスタードインデックスのデータテーブルとインデックステーブルは別々に保存されます。

ノンクラスタード インデックスのデータは、データの挿入順序に従って保存されます。したがって、非クラスター化インデックスは単一データのクエリに適しています。挿入順序はキー値の影響を受けません。

FULLTEXT インデックスは MyISAM でのみ使用できます。

非クラスター化インデックスのプライマリ インデックスと補助インデックスは同じコンテンツを指しているため、最初は補助インデックスが必要な理由がわかりませんでした。後で、インデックスがクエリに使用されないことに気づきました。それらの場所は、WHERE ステートメントと ORDER BY ステートメントの直後ではありませんか? では、クエリ条件が主キーではない場合はどうすればよいでしょうか? このとき、補助インデックスが必要になります。

InnoDB—クラスター化インデックス

クラスター化インデックスのプライマリ インデックスのリーフ ノードにはキー値自体に対応するデータが格納され、補助インデックスのリーフ ノードには対応するキーが格納されます。 value データの主キーの値。したがって、主キーの値の長さは短いほど良く、型は単純であるほど良くなります。

クラスター化インデックス データと主キー インデックスは一緒に保存されます。

クラスタードインデックスデータは主キーの順に保存されます。したがって、必要なディスク I/O が少なくなり、クエリが高速化できる主キー インデックスによる間隔検索に適しています。しかし、この理由からも、クラスター化インデックスの挿入順序は、主キーの単調な順序で挿入するのが最適です。そうしないと、頻繁にページ分割が発生し、パフォーマンスに重大な影響を及ぼします。

InnoDB では、インデックス列のみを検索する必要がある場合は、他の列を追加しないようにしてください。これにより、クエリの効率が向上します。

使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据。

因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低委会成本，因为这时不用维护辅助索引。但是辅助索引会占用更多的空间。

聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要减压主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。

下图可以形象的说明聚簇索引和非聚簇索引的区别

五、索引的使用策略

什么时候要使用索引？

主键自动建立唯一索引；

经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；

作为排序的列要建立索引；

查询中与其他表关联的字段，外键关系建立索引

高并发条件下倾向组合索引；

什么时候不要使用索引？

经常增删改的列不要建立索引；

有大量重复的列不建立索引；

表记录太少不要建立索引；

在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的；

在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了；

LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引；

在索引的列上使用表达式或者函数会使索引失效，例如：select from users where YEAR(adddate) from users where adddate

在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。

在查询条件中使用会导致索引失效。

在查询条件中使用IS NULL会导致索引失效。

在查询条件中使用OR连接多个条件会导致索引失效，这时应该改为两次查询，然后用UNION ALL连接起来。

尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；

只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快--不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

六、索引的优化

1、最左前缀

索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引那么以下3中情况可以使用索引：col1，，，其它的列，比如，，col2，col3等等都是不能使用索引的。

根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边，以此类推。

2、带索引的模糊查询优化

在上面已经提到，使用LIKE进行模糊查询的时候，'%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化（上文有讲到）。

为检索的条件构建全文索引，然后使用

SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);

事务介绍

首先，什么是事务？事务就是一段sql 语句的批处理，但是这个批处理是一个atom（原子），不可分割，要么都执行，要么回滚（rollback）都不执行。

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

• トランザクション処理を使用すると、データベースの整合性を維持し、SQL ステートメントのバッチがすべて実行されるか、まったく実行されないかを確認できます。

• トランザクションは、挿入、更新、削除ステートメントの管理に使用されます

一般的に、トランザクションは 4 つの条件 (ACID) を満たす必要があります。 )、一貫性 (安定性)、分離性 (分離)、耐久性 (信頼性)

• 1. トランザクションの原子性: 一連のトランザクションは成功するか取り消されます。

• 2. 安定性: 不正なデータ (外部キー制約など) がある場合、トランザクションは取り消されます。

• 3. 分離: トランザクションは独立して実行されます。ある取引の結果が他の取引に影響を与える場合、他の取引は撤回されます。トランザクションを 100% 分離するには、速度を犠牲にする必要があります。

• 4. 信頼性: ソフトウェアまたはハードウェアがクラッシュした後、InnoDB データ テーブル ドライバーはログ ファイルを使用して再構築し、変更します。信頼性と高速性は両立しません。innodb_flush_log_at_trx_commit オプションは、トランザクションをログに保存するタイミングを決定します。

トランザクションの同時実行性は、トランザクションの分離によって発生するダーティ読み取り、ファントム読み取り、および反復不可能な読み取りを実行しません。

• ダーティ読み取り: トランザクション A は、コミットされていないトランザクション B によって変更されたデータを読み取ります。トランザクション B が途中でロールバックに失敗した場合、トランザクション A はこの時点でダーティ データを読み取ります。たとえば、トランザクション A が金額を変更すると、トランザクション B はトランザクション A の更新結果を読み取ります。ただし、トランザクション A が後でロールバックすると、トランザクション B が読み取るのはダーティ データになります。

• Non-repeatable read: 同じトランザクション内で、同じデータを読み取った結果は一貫性がありません。トランザクション B がデータを更新する前にトランザクション A が読み取り、その後トランザクション B が更新してコミットし、再度トランザクション A が読み取ります。このとき、2 回読み取られるデータは異なります。

• ファントム読み取り: (同じトランザクション内で、同じクエリが異なる結果を複数回返します。トランザクション B はテーブル内のレコード数をクエリし、トランザクション A はテーブルにレコードを挿入します。この説明はインターネット上に多数あり、より権威があると思われる専門家もいますが、実験を行った結果、それらは正しくありません。ので、これには注意する必要があります）。次のような実験を行うことができます。トランザクション A はレコード数をクエリし、トランザクション B はレコード (主キーの値は 6) を挿入し、コミットします。その後、トランザクション A がレコード数をクエリし、レコード数が一致していないことがわかります。変更されましたが、この時点で主キー値が 6 のレコードが挿入されており、レコードが競合していることが判明し、幻覚のように感じられました。

違い

1. ダーティ リードとノンリピータブル リード: ダーティ リードは、トランザクションがコミットされていないトランザクションの更新されたデータを読み取ることを意味します。非反復読み取りとは、同じトランザクション内で複数回読み取られたデータが異なることを意味します。

2. 非反復読み取りとファントム読み取りの違い: 前者はデータを複数回読み取る点で異なり、後者は異なる方法でデータを読み取ります。

#分離レベル

##分離レベルの変更はロック サイクルに影響します

• mysql は上記の 4 つの分離レベルをサポートしており、デフォルトは反復読み取りです。

MySQL には 3 つのレベルのロックがあります: ページ レベル、テーブルレベル、行レベル。

MyISAM および MEMORY ストレージ エンジンはテーブル レベルのロックを使用します。 BDB ストレージ エンジンはページ レベルのロックを使用しますが、テーブル レベルのロックもサポートします。 InnoDB ストレージ エンジンは行レベルのロックとテーブル レベルのロックの両方をサポートしていますが、デフォルトでは

は行レベルのロックを使用します。

MySQL のこれら 3 つのロックの特徴は、次のように大まかに要約できます。 1. テーブル レベルのロック: 低いオーバーヘッド、高速なロック、大きなロック粒度、最も高いロック競合の可能性。同時実行性は最低です。テーブル レベルのロックを使用すると、複数のスレッドが同時にデータ テーブルからデータを読み取ることができますが、別のスレッドがデータを書き込みたい場合は、最初に排他的アクセスを取得する必要があります (排他的テーブル ロックがデフォルトで追加されます)。読み取りロック) ) データを更新するとき、他のスレッドはテーブルにアクセス (読み取り) できるようになるまで、更新が完了するまで待機する必要があります (排他的書き込みロック (テーブル書き込みロック))

2。高いオーバーヘッドと遅いロックが発生します。ロックの粒度は最も小さく、ロックの競合の可能性は最も低く、同時実行性は最も高くなります。

3、页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

原则上数据表有一个读锁时，其它进程无法对此表进行更新操作，但在一定条件下，MyISAM表也支持查询和插入操作的并发进行。

一般MyISAM引擎的表也支持查询和插入操作的并发进行（原则上数据表有一个读锁时，其它进程无法对此表进行更新操作）

MyISAM引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2：

a、concurrent_insert为0，不允许并发插入。 　　　　
b、concurrent_insert为1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。 　　　　
c、concurrent_insert为2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

如果有读写请求同时进行的话，MYSQL将会优先执行写操作。这样MyISAM表在进行大量的更新操作时（特别是更新的字段中存在索引的情况下），会造成查询操作很难获得读锁，从而导致查询阻塞。

我们还可以调整MyISAM读写的优先级别：

　　a、通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
　　b、通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
　　c、通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

MyISAM使用的是 flock 类的函数，直接就是对整个文件进行锁定（叫做文件锁定），MyISAM的数据表是按照单个文件存储的，可以针对单个表文件进行锁定；

InnoDB使用的是 fcntl 类的函数，可以对文件中局部数据进行锁定（叫做行锁定），InnoDB是一整个文件，把索引、数据、结构全部保存在 ibdata 文件里，所以必须用行锁定。

事物控制语句：

BEGIN或START TRANSACTION；显式地开启一个事务；     
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。
COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；      
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；      
SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；     
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；     
ROLLBACK TO identifier；把事务回滚到标记点；     
SET TRANSACTION；用来设置事务的隔离级别。
InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务     
ROLLBACK 事务回滚    
COMMIT 事务确认

2、直接用 SET 来改变 My

SQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交     
SET AUTOCOMMIT=1 开启自动提交

注意点

1、如果事务中sql正确运行，后面没有commit，结果是不会更新到数据库的，所以需要手动添加commit。

2、如果事务中部分sql语句出现错误，那么错误语句后面不会执行。而我们可能会认为正确操作会回滚撤销，但是实际上并没有撤销正确的操作，此时如果再无错情况下进行一次commit，之前的正确操作会生效，数据库会进行更新。

以上がmysql インデックスとトランザクションの詳細な解釈の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。