を満たすレコードのみが仮想テーブル VT7 に挿入されます。
SELECT: 2 回目の SELECT 操作を実行し、指定された列を選択して、仮想テーブル VT8
に挿入します。
DISTINCT: 重複データを削除し、仮想テーブル VT9 を生成します。
ORDER BY: < に従って仮想テーブル VT9 内のレコードを処理します。 ;order_by_list> ソート操作により仮想テーブル VT10 が生成されます 11)
LIMIT: 指定行のレコードを取り出し、仮想テーブル VT11 を生成して返します。クエリ ユーザーデータベース アーキテクチャ
14. MySQL の基本アーキテクチャについて話しますか?
MySQL論理アーキテクチャ図は主に 3 つの層に分かれています:
- クライアント: 最上位のサービスは MySQL に固有のものではなく、ほとんどのネットワーク ベースのクライアント/サーバー ツールまたはサービスは同様のアーキテクチャを備えています。接続処理、認可認証、セキュリティなど。
- サーバー層: クエリ解析、分析、最適化、キャッシュ、およびすべての組み込み関数 (日付、時刻、数学関数、暗号化関数など) を含む、MySQL のコア サービス関数のほとんどがこの層にあります。すべてのクロスストレージ エンジン機能 (ストアド プロシージャ、トリガー、ビューなど) がこの層に実装されます。
- ストレージ エンジン層: 3 番目の層にはストレージ エンジンが含まれます。ストレージ エンジンは、MySQL でのデータの保存と取得を担当します。サーバー層は API を介してストレージ エンジンと通信します。これらのインターフェイスは、異なるストレージ エンジン間の違いを保護し、上位層のクエリ プロセスに対してこれらの違いを透過的にします。
15. SQL クエリ ステートメントは MySQL でどのように実行されますか?
- 最初にステートメント
に権限があるかどうかを確認します 。権限がない場合は、エラー メッセージが直接返されます。権限がある場合は、最初にキャッシュがクエリされます (前にMySQL8.0バージョン）。
- キャッシュがない場合、アナライザーは
文法分析を実行し、SQL ステートメント内の select などの重要な要素を抽出し、SQL ステートメントに構文エラーがあるかどうかを判断します。キーワードは正しいなど。
- 構文分析後、MySQL サーバーはクエリ ステートメントを最適化し、実行計画を決定します。
- クエリの最適化が完了したら、生成された実行計画
に従ってデータベース エンジン インターフェイス を呼び出し、実行結果を返します。
ストレージ エンジン
16.MySQL の一般的なストレージ エンジンは何ですか?
主なストレージ エンジンと機能は次のとおりです:
| 関数 |
MylSAM |
MEMORY |
InnoDB |
| ストレージ制限 |
256TB |
RAM |
64TB |
| サポート トランザクション |
No |
No |
Yes |
| 全文インデックス作成のサポート |
Yes |
No |
Yes |
| ツリー インデックスのサポート |
Yes |
はい |
はい |
| サポート ハッシュ インデックス |
No |
Yes |
Yes |
| データ キャッシュのサポート |
No |
N/A |
Yes |
| 外部キーのサポート |
No |
No |
Yes |
MySQL 5.5 より前は、デフォルトのストレージ エンジンは MylSAM でしたが、5.5 以降は InnoDB になりました。
InnoDB でサポートされるハッシュ インデックスは適応的です。InnoDB は、テーブルの使用状況に基づいてテーブルのハッシュ インデックスを自動的に生成します。テーブル内にハッシュ インデックスを生成するために人間の介入は許可されていません。
InnoDB は、MySQL 5.6 以降、フルテキスト インデックス作成をサポートします。
17. ストレージ エンジンはどのように選択すればよいですか?
これを大まかに選択できます:
- ほとんどの場合、デフォルトの InnoDB を使用するだけで十分です。コミット、ロールバック、およびリカバリのためのトランザクション セキュリティ (ACID 互換性) 機能を提供し、同時実行制御が必要な場合は、InnoDB が最初の選択肢となります。
- データ テーブルが主にレコードの挿入とクエリに使用される場合、MyISAM エンジンの方が処理効率が高くなります。
- データが一時的に保存されるだけで、データ量が大きくなく、高いデータ セキュリティが必要ない場合は、MEMORY エンジンのデータをメモリに保存することを選択できます。このエンジンは、次のように使用されます。クエリや中間結果を保存するための MySQL の一時テーブル。
使用するエンジンはニーズに応じて柔軟に選択可能ストレージ エンジンはテーブルベースであるため、データベース内の複数のテーブルで異なるエンジンを使用して、さまざまなパフォーマンスや実際のニーズを満たすことができます。適切なストレージ エンジンを使用すると、データベース全体のパフォーマンスが向上します。
18.InnoDB と MylSAM の主な違いは何ですか?
追記: MySQL8.0 は徐々に普及しつつありますが、面接でなければ、MylSAM についてあまり知る必要はありません。
1. ストレージ構造 : 各 MyISAM はディスク上の 3 つのファイルに保存され、すべての InnoDB テーブルは同じデータ ファイルに保存されます ( InnoDB テーブルのサイズは、オペレーティング システム ファイルのサイズによってのみ制限されます (通常は 2 GB)。
2. トランザクション サポート: MyISAM はトランザクション サポートを提供しませんが、InnoDB はトランザクション (コミット)、ロールバック (ロールバック)、およびクラッシュ回復機能 (クラッシュ回復機能) を備えたトランザクション サポートを提供します。特徴。
3 最小ロック粒度: MyISAM はテーブル レベルのロックのみをサポートします。更新中にテーブル全体がロックされるため、他のクエリや更新がブロックされます。InnoDB は行レベルのロックをサポートします。
4. インデックス タイプ: MyISAM のインデックスはクラスター化インデックスであり、データ構造は B ツリーです。InnoDB のインデックスは非クラスター化インデックスで、データ構造は B -木。
5. 主キーが必要です: MyISAM では、インデックスと主キーのないテーブルの存在が許可されます。主キーがない場合、または空でない一意の場合、InnoDB は自動的に 6 ワードの番号を生成します。セクションの主キー (ユーザーには表示されません) 、データはメイン インデックスの一部であり、追加のインデックスはメイン インデックスの値を保存します。
6. テーブル内の特定の行数: MyISAM はテーブル内の行の合計数を保存します。table; から count() を選択すると、値は直接取得されます; InnoDB はテーブルを保存しません。行の総数は、select count() from table を使用する場合、テーブル全体を走査します。しかし、wehre 条件を追加した後、MyISAM と InnoDB がそれを処理します。同じやり方で。
7. 外部キーのサポート: MyISAM は外部キーをサポートしませんが、InnoDB は外部キーをサポートします。
ログ19.MySQL ログ ファイルとは何ですか?それぞれの機能を紹介しますか？
MySQL ログ ファイルには次のようなものがあります。
- エラー ログ (エラー ログ): エラー ログ ファイルは次のとおりです。 MySQL にとって非常に重要 起動、操作、およびシャットダウンのプロセスが記録されるため、MySQL の問題を特定するのに役立ちます。
- スロー クエリ ログ (スロー クエリ ログ): スロー クエリ ログは、実行時間がlong_query_time 変数で定義された長さを超えるクエリ ステートメントを記録するために使用されます。スロークエリログを通じて、どのクエリステートメントの実行効率が低く最適化されているかを知ることができます。
- 一般クエリ ログ (一般ログ): 一般クエリ ログには、リクエストが正しく実行されたかどうかに関係なく、MySQL データベースにリクエストされたすべての情報が記録されます。
- バイナリ ログ (bin ログ): バイナリ ログに関しては、データベースによって実行されたすべての DDL および DML ステートメント (データ クエリ ステートメント select、show などを除く) が記録されます。イベントの形式とバイナリ ファイルで保存されます。
InnoDB ストレージ エンジン固有のログ ファイルも 2 つあります:
-
Redo ログ (redo ログ): REDO ログは、InnoDB ストレージ エンジンのトランザクション ログを記録するため、非常に重要です。
-
ロールバック ログ(undo ログ): ロールバック ログは、InnoDB エンジンによって提供されるログでもあり、名前が示すように、ロールバック ログの役割はデータをロールバックすることです。トランザクションがデータベースを変更すると、InnoDB エンジンは REDO ログを記録するだけでなく、対応する UNDO ログも生成します。トランザクションの実行が失敗するかロールバックが呼び出され、トランザクションがロールバックされた場合、UNDO ログ内の情報はデータを復元するために使用できます。変更前の状態までスクロールします。
20.binlog と redo ログの違いは何ですか?
- bin ログは、InnoDB や MyISAM などのストレージ エンジンのログを含む、データベースに関連するすべてのログ レコードを記録しますが、REDO ログは InnoDB ストレージ エンジンのログのみを記録します。
- 記録内容が異なります bin ログはトランザクションの具体的な操作内容を記録する、つまり論理的なログです。 REDO ログには、各ページ (ページ) への物理的な変更が記録されます。
- 書き込み時間が異なります。bin ログはトランザクションが送信される前にのみ送信されます。つまり、ディスクに 1 回だけ書き込まれます。トランザクションの進行中、REDO ertry は常に REDO ログに書き込まれます。
- 書き込み方法も異なり、Redo ログは書き込みと消去を繰り返すのに対し、bin ログは追記書き込みであり、既に書き込まれたファイルは上書きされません。
21. update ステートメントの実行方法は理解できましたか?
Update ステートメントの実行はサーバー層とエンジン層の連携によって完了しますが、テーブルにデータを書き込むだけでなく、対応するログも記録する必要があります。
- #エグゼキュータは、まずエンジンを探して行 ID=2 を取得します。 ID は主キーであり、ストレージ エンジンはデータを取得してこの行を見つけます。 ID=2 の行が配置されているデータ ページがすでにメモリ内にある場合は、そのデータ ページが直接エグゼキュータに返されます。そうでない場合は、最初にディスクからメモリに読み込まれてから返される必要があります。
- エグゼキュータはエンジンによって与えられた行データを取得し、この値に 1 を加えます。たとえば、以前は N でしたが、現在は N 1 になり、新しい行を取得します。データを入力し、エンジン インターフェイスを呼び出して、「この行に新しいデータを入力してください」と書き込みます。
- エンジンは、この新しいデータ行をメモリに更新し、更新操作を REDO ログに記録します。この時点で、REDO ログは準備状態になります。次に、実行が完了し、いつでもトランザクションを送信できることを実行者に通知します。
- エグゼキューターは、この操作のバイナリログを生成し、そのバイナリログをディスクに書き込みます。
- エグゼキューターはエンジンのコミット トランザクション インターフェイスを呼び出し、エンジンは書き込まれたばかりの REDO ログをコミット状態に変更し、更新が完了します。
上の図からわかるように、MySQL は update ステートメントを実行すると、サービス層でステートメントを解析して実行し、エンジン層でデータを抽出して保存します。同時にサービス層でも、この層はバイナリログを書き込み、InnoDB に REDO ログを書き込みます。 それだけではなく、REDO ログの書き込みには 2 つの段階があります。1 つは binlog 書き込み前の prepare 状態の書き込み、もう 1 つは binlog 書き込み後の 書き込みです。コミットステータス。
22. では、なぜ 2 段階の提出があるのでしょうか? なぜ 2 段階で提出するのですか?直接提出することはできないのでしょうか？ 2 段階コミット方法を使用する代わりに、「単一段階」コミットを使用すると仮定できます。つまり、最初に REDO ログを書き込んでからバイナリログを書き込むか、最初にバイナリログを書き込むかのいずれかです。そしてREDOログを書き込みます。これら 2 つの方法で送信すると、元のデータベースの状態と復元されたデータベースの状態が一致しなくなります。
最初に REDO ログを書き込み、次に binlog を書き込みます:
REDO ログを書き込んだ後、データには クラッシュ セーフ機能が追加されるため、システムはクラッシュすると、データはトランザクションが開始される前の状態に復元されます。ただし、REDO ログが完了し、バイナリログが書き込まれる前にシステムがクラッシュした場合、システムはクラッシュします。現時点では、binlog は上記の更新ステートメントを保存しないため、binlog を使用してデータベースをバックアップまたは復元すると、上記の更新ステートメントが失われます。その結果、行 id=2 のデータは更新されません。
最初に binlog に書き込み、次にログをやり直します:
binlog を書き込んだ後、すべてのステートメントが保存されます。 binlog を通じてコピーまたは復元されたデータベース内の行 id=2 は、a=1 に更新されます。ただし、REDO ログが書き込まれる前にシステムがクラッシュした場合、REDO ログに記録されたトランザクションは無効となり、実際のデータベースの id=2 行のデータは更新されません。
簡単に言うと、REDO ログと binlog の両方を使用してトランザクションのコミット ステータスを表すことができ、2 フェーズ コミットは 2 つの状態の論理的な一貫性を保つことです。
23.redo log怎么刷入磁盘的知道吗？
redo log的写入不是直接落到磁盘，而是在内存中设置了一片称之为redo log buffer的连续内存空间，也就是redo 日志缓冲区。
什么时候会刷入磁盘？
在如下的一些情况中，log buffer的数据会刷入磁盘：
log buffer 的大小是有限的，如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。如果当前写入 log buffer 的redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
在事务提交时，为了保证持久性，会把log buffer中的日志全部刷到磁盘。注意，这时候，除了本事务的，可能还会刷入其它事务的日志。
有一个后台线程，大约每秒都会刷新一次log buffer中的redo log到磁盘。
重做日志缓存、重做日志文件都是以块（block） 的方式进行保存的，称之为重做日志块（redo log block） ,块的大小是固定的512字节。我们的redo log它是固定大小的，可以看作是一个逻辑上的 log group，由一定数量的log block 组成。
它的写入方式是从头到尾开始写，写到末尾又回到开头循环写。
其中有两个标记位置：
write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到磁盘。
当write_pos追上checkpoint时，表示redo log日志已经写满。这时候就不能接着往里写数据了，需要执行checkpoint规则腾出可写空间。
所谓的checkpoint规则，就是checkpoint触发后，将buffer中日志页都刷到磁盘。
SQL 优化
24.慢SQL如何定位呢？
慢SQL的监控主要通过两个途径：
-
慢查询日志：开启MySQL的慢查询日志，再通过一些工具比如mysqldumpslow去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。
-
服务监控：可以在业务的基建中加入对慢SQL的监控，常见的方案有字节码插桩、连接池扩展、ORM框架过程，对服务运行中的慢SQL进行监控和告警。
25.有哪些方式优化慢SQL？
慢SQL的优化，主要从两个方面考虑，SQL语句本身的优化，以及数据库设计的优化。
避免不必要的列
这个是老生常谈，但还是经常会出的情况，SQL查询的时候，应该只查询需要的列，而不要包含额外的列，像slect * 这种写法应该尽量避免。
分页优化
在数据量比较大，分页比较深的情况下，需要考虑分页的优化。
例如：
select * from table where type = 2 and level = 9 order by id asc limit 190289,10;
ログイン後にコピー
优化方案：
select * from table where id >
(select * from table where type = 2 and level = 9 order by id asc limit 190
ログイン後にコピー
索引优化
合理地设计和使用索引，是优化慢SQL的利器。
利用覆盖索引
InnoDB使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引
例如对于如下查询：
select name from test where city='上海'
ログイン後にコピー
我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取
alter table test add index idx_city_name (city, name);
ログイン後にコピー
低版本避免使用or查询
在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题。
避免使用 != 或者 操作符
SQL中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引
解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描
例如，把column’aaa’，改成column>’aaa’ or column，就可以使用索引了
适当使用前缀索引
适当地使用前缀所云，可以降低索引的空间占用，提高索引的查询效率。
比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引
alter table test add index index2(email(6));
ログイン後にコピー
PS:需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引
避免列上函数运算
要避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率
select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;
ログイン後にコピー
正确使用联合索引
使用联合索引的时候，注意最左匹配原则。
JOIN优化
优化子查询
尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大
小表驱动大表
关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL内部会遍历驱动表，再去连接被驱动表。
比如left join，左表就是驱动表，A表小于B表，建立连接的次数就少，查询速度就被加快了。
select name from A left join B ;
ログイン後にコピー
适当增加冗余字段
增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略
避免使用JOIN关联太多的表
《阿里巴巴Java开发手册》规定不要join超过三张表，第一join太多降低查询的速度，第二join的buffer会占用更多的内存。
如果不可避免要join多张表，可以考虑使用数据异构的方式异构到ES中查询。
排序优化
利用索引扫描做排序
MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的
但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢
因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行
例如：
--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;
ログイン後にコピー
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序
UNION优化
条件下推
MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引
最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化
此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高。
26.怎么看执行计划（explain），如何理解其中各个字段的含义？
explain是sql优化的利器，除了优化慢sql，平时的sql编写，也应该先explain，查看一下执行计划，看看是否还有优化的空间。
直接在 select 语句之前增加explain关键字，就会返回执行计划的信息。
id 列: MySQL は各 select ステートメントに一意の ID 値を割り当てます
select_type 列、アソシエーション、ユニオン、サブクエリなどに従って分類されたクエリ タイプ。一般的なクエリ タイプには、SIMPLE と PRIMARY があります。
#table 列: Explain の行がどのテーブルにアクセスしているかを示します。
-
#type 列: 最も重要な列の 1 つ。 MySQL がテーブル内の行を検索する方法を決定する関連付けのタイプまたはアクセス タイプを表します。
パフォーマンスは最高から最低まで: システム > const > eq_ref >gt; ref > フルテキスト > ref_or_null > Index_merge > unique_subquery >gt; Index_subquery > range > Index >gt; ALL
-
system
system: テーブルにレコードが 1 行しかない場合 (システム テーブル)、データ量は非常に少なく、ディスク IO は多くの場合必要ありません。非常に高速です。
-
const
const: primary key 主キーまたは ## を示します。 #unique# がクエリ中にヒットしました ## 一意のインデックス、つまり接続された部分は定数 (const) 値です。このタイプのスキャンは非常に効率的で、返されるデータの量が少なく、非常に高速です。
eq_ref-
eq_ref
: 主キー primary key または unique key インデックスを実行中にヒットします。クエリ、type は eq_ref です。
ref_or_null-
ref_or_null
: この接続タイプは ref に似ていますが、異なる点は、MySQL が追加で検索することです。 #NULL 値を持つ ## 行を含むアイテム。
index_merge
-
index_merge
: インデックス マージ最適化メソッドが使用されており、クエリで 3 つ以上のインデックスが使用されています。
unique_subquery
-
unique_subquery
: 次の IN サブクエリを置き換えると、サブクエリは一意のセットを返します。
index_subquery
-
index_subquery
: unique_subquery とは異なり、一意でないインデックスに使用され、重複した値を返す可能性があります。 。
range
-
range
: インデックスを使用して行を選択し、指定された範囲内の行のみを取得します。簡単に言うと、インデックス付きフィールドに対して指定された範囲内のデータを取得することです。 where ステートメントでは、bettween...and、、>、 を使用します。 、in およびその他の条件付きクエリ type はすべて range です。
index
-
index
: Index と ALL は実際にテーブル全体を読み取りますが、その違いは次のとおりです。その理由は、index はインデックス ツリーを走査することによって読み取られるのに対し、ALL はハード ディスクから読み取られるためです。
ALL
- 言うまでもなく、フルテーブルスキャンです。
possible_keys- 列: クエリが検索に使用できるインデックスを示します。これは、インデックスを使用して SQL を最適化する場合により重要です。
key- 列: この列は、mysql がテーブルへのアクセスを最適化するために実際に使用するインデックスを示します。一般に、インデックスが無効かどうかを判断するために使用されます。
key_len- 列: MySQL が何を使用するかを示します
ref- 列: ref 列がそれを示しますインデックス列と等しい値として一致する値です。一般的なものは、const (定数)、func、NULL、フィールド名です。
rows- 列: これも重要なフィールドです。統計情報に基づいて、MySQL クエリ オプティマイザーは、結果を見つけるために SQL がスキャンする必要があるデータ行を推定します。この値は、SQL の効率を非常に直感的に示します。原則として、行数が少ないほど優れています。
#Extra 列: 他の列に収まらない追加情報が表示されます。追加と呼ばれていますが、いくつかの重要な情報もあります: -
Using Index: MySQL がテーブルを返さないようにカバー インデックスを使用することを示します
Using where: ストレージ エンジンが取得された後にフィルタリングが実行されることを示します - 一時テーブルを使用: クエリ結果を並べ替えるときに一時テーブルが使用されることを示します。
- インデックス
- MySQL面接においてインデックスは最優先事項とも言え、完全に勝ち取らなければなりません。
27. インデックスの分類について簡単に説明してもらえますか?
3 つの異なる次元からインデックスを分類します:
たとえば、基本的な使用法の観点から:
主キー インデックス: InnoDB の主キーはデフォルトのインデックスです。データ列の繰り返しや NULL は許可されません。テーブルには主キーを 1 つだけ持つことができます。 一意のインデックス: データ列の重複は許可されず、NULL 値が許可され、テーブルでは複数の列が一意のインデックスを作成できます。
- 通常のインデックス: 基本的なインデックス タイプ、一意性の制限なし、NULL 値が許可されます。
- 結合インデックス: 複数の列の値が結合検索用のインデックスを形成し、インデックスの結合よりも効率的です
- 28. インデックスを使用するとクエリが高速化されるのはなぜですか?
- 従来のクエリ方法では、テーブルを順番に走査します。クエリされるデータの数に関係なく、MySQL はテーブル データを最初から最後まで走査する必要があります。
インデックスを追加した後、MySQL は通常、BTREE アルゴリズムを通じてインデックス ファイルを生成します。データベースにクエリを実行するときは、走査するインデックス ファイルを見つけて、比較的小さなインデックス データを検索し、それを対応するデータにマッピングします。検索効率を大幅に向上させることができます。
本の目次から該当する内容を探すのと同じです。
#29. インデックス作成時の注意点は何ですか？
インデックスは SQL パフォーマンスを最適化するための強力なツールですが、インデックスのメンテナンスにもコストがかかるため、インデックスを作成するときは次の点にも注意する必要があります。インデックスはクエリ アプリケーションで構築する必要があります。頻繁に使用されるフィールド
WHERE 判定、順序ソート、結合に使用されるフィールド (上) にインデックスを作成します。 -
インデックスの数は適切である必要があります
インデックスはスペースを占有する必要があり、更新中にも維持する必要があります。 -
性別など、区別性の低いフィールドにはインデックスを作成しないでください。
分散が低すぎるフィールドの場合、スキャンされる行の数は制限されます。 -
頻繁に更新される値は主キーやインデックスとして使用しないでください
インデックス ファイルの維持にはコストがかかり、ページ分割や IO の増加にもつながります回。 -
#結合インデックスは、左端のプレフィックス マッチング原則を満たすために、高度なハッシュ (高度な区別) を持つ値を前に置きます
- 単一列インデックスを変更するのではなく、複合インデックスを作成します。 結合インデックスは複数の単一列インデックスを置き換えます (単一列インデックスの場合、MySQL は基本的に 1 つのインデックスしか使用できないため、複数の条件クエリが頻繁に使用される場合は結合インデックスを使用する方が適しています)
- フィールドが長すぎる場合は、プレフィックス インデックスを使用します。フィールド値が比較的長い場合、インデックス作成により多くのスペースが消費され、検索が非常に遅くなります。フィールドの前の部分をインターセプトすることでインデックスを作成できます。これはプレフィックス インデックスと呼ばれます。
順序のない値 (ID カード、UUID など) をインデックスとして使用することはお勧めできません。
- 主キーが不確かな場合、リーフ ノードが頻繁に分割されると、ディスクが表示されます。ストレージの断片化
- #30. どのような状況でインデックスが失敗しますか?
クエリ条件に or が含まれているため、インデックスが失敗する可能性があります。
フィールド タイプが文字列の場合、where を引用符で囲む必要があります。そうしないとインデックスが失敗します。暗黙的な型変換のため
のようなワイルドカードはインデックスの失敗を引き起こす可能性があります。
結合インデックス。クエリ時の条件列が結合インデックスの最初の列ではないため、インデックスは無効になります。 - mysql の組み込み関数をインデックス列に使用すると、インデックスが無効になります。
- インデックス列 (-、*、/ など) に対して操作を実行すると、インデックスが無効になります。
- インデックス フィールドで (!= または , not in) を使用すると、インデックス エラーが発生する可能性があります。
- インデックスフィールドで is null または is not null を使用すると、インデックスエラーが発生する可能性があります。
- 左結合クエリまたは右結合クエリに関連付けられたフィールドのエンコード形式が異なるため、インデックスのエラーが発生する可能性があります。
- MySQL オプティマイザは、テーブル全体のスキャンを使用した方がインデックスを使用するよりも高速であると推定するため、インデックスは使用されません。
-
- 31. インデックスが適さないシナリオは何ですか?
- データ量が比較的少ないテーブルはインデックス付けに適していません
- 頻繁に更新されるフィールドはインデックス付けに適していません
離散性の低いフィールドはインデックス付けに適していませんインデックス作成に適している (性別など)
- 32. より多くのインデックスを作成した方がよいでしょうか? ＃＃＃＃＃＃もちろん違います。
-
- インデックスはディスク領域を占有します
インデックスによりクエリの効率は向上しますが、テーブルの更新効率は低下します## ＃。たとえば、テーブルが追加、削除、または変更されるたびに、MySQL はデータを保存するだけでなく、対応するインデックス ファイルを保存または更新する必要があります。
33.MySQL インデックスがどのようなデータ構造を使用しているかご存知ですか? - MySQL のデフォルトのストレージ エンジンは InnoDB で、B ツリー構造化インデックスを使用します。
- B ツリー: リーフ ノードのみがデータを保存し、非リーフ ノードはキー値のみを保存します。リーフ ノードは双方向ポインタを使用して接続され、最下位のリーフ ノードは双方向の順序付きリンク リストを形成します。
この図には 2 つの重要なポイントがあります:
- 最も外側のブロックはディスク ブロックと呼ばれます。各ディスク ブロックには、ルートなどの複数のデータ項目 (ピンク色で表示) とポインター (黄色/灰色で表示) が含まれていることがわかります。ノード ディスクにはデータ項目が含まれていますこれには、ポインタ P1、P2、および P3 が含まれます。P1 は 17 未満のディスク ブロックを表し、P2 は 17 と 35 の間のディスク ブロックを表し、P3 は 35 より大きいディスク ブロックを表します。実際のデータはリーフ ノード、つまり 3、4、5...、65 に存在します。非リーフ ノードには実際のデータは格納されず、検索方向をガイドするデータ項目のみが格納されます。たとえば、17 と 35 は実際にはデータ テーブルに存在しません。
- リーフ ノードは双方向ポインタを使用して接続されており、最下位のリーフ ノードは双方向の順序付きリンク リストを形成し、範囲クエリが可能です。
34. B ツリーにはいくつのデータを保存できますか?
#インデックス フィールドの型が bigint で、長さが 8 バイトであると仮定します。 InnoDB ソース コードではポインター サイズが 6 バイトに設定されており、合計 14 バイトになります。非リーフ ノード (1 ページ) は、そのようなユニット (キー値ポインター) を 16384/14=1170 個保存できます。これは、ポインターが 1170 個あることを意味します。
木の深さが2の場合、葉ノードは1170^2個あり、格納できるデータは1170117016=21902400になります。
データを検索する場合、1 ページの検索は 1 つの IO を表します。つまり、約 2,000 万のテーブルの場合、データのクエリには最大 3 回のディスク アクセスが必要です。
したがって、InnoDB の B ツリーの深さは通常 1 ～ 3 レイヤーであり、数千万のデータ ストレージを満たすことができます。
35. なぜ通常のバイナリ ツリーではなく B ツリーを使用するのでしょうか?
この問題は、クエリが十分に速いかどうか、効率が安定しているかどうか、保存されるデータの量、ディスク検索の数など、いくつかの側面から見ることができます。
通常のバイナリ ツリーを使用しないのはなぜでしょうか?
通常のバイナリツリーは縮退しますが、リンクリストに縮退するとフルテーブルスキャンと同等になります。二分探索木と比較して、バランス二分木は検索効率がより安定し、全体的な検索速度が速くなります。
バイナリ ツリーのバランスを取ってみませんか?
#データを読み取る場合、データはディスクからメモリに読み込まれます。ツリーのようなデータ構造をインデックスとして使用する場合、データを検索するたびにディスク (ディスク ブロック) からノードを読み取る必要がありますが、バランスのとれたバイナリ ツリーでは、ノードごとに 1 つのキー値とデータのみが保存されます。 B ツリーであれば、より多くのノード データを格納でき、ツリーの高さも低くなるため、ディスクの読み取り回数が減り、クエリ効率が向上します。 36. なぜ B ツリーではなく B ツリーを使用するのでしょうか? B ツリーと比較すると、B には次の利点があります:
- B ツリーの亜種であり、B ツリーが解決できるすべての問題を解決できます。 。 B Tree によって解決された 2 つの主要な問題: 各ノードにはより多くのキーワードとより多くのパスが保存されます
- データベースとテーブルをスキャンする強力な機能
必要に応じてテーブルに対してフル テーブル スキャンを実行するには、リーフ ノードを走査するだけでよく、すべてのデータを取得するために B ツリー全体を走査する必要はありません。
- B ツリーは、B ツリーよりも強力なディスク読み取りおよび書き込み機能を備え、IO 回数が少なくなります。ルート ノードとブランチ ノードはデータ領域を保存しないため、ノード より多くのキーワードを保存でき、より多くのキーワードを一度にディスクにロードでき、IO 回数が削減されます。
- 強力なソート機能リーフ ノードには次のデータ領域へのポインタがあるため、データはリンク リストを形成します。
- 効率がより安定しますB ツリーは常にリーフ ノードでデータを取得するため、IO 数は安定します。
37. ハッシュ インデックスと B ツリー インデックスの違いは何ですか?
B ツリーは範囲クエリを実行できますが、ハッシュ インデックスは実行できません。 - B ツリーはジョイント インデックスの左端の原則をサポートしていますが、ハッシュ インデックスはそれをサポートしていません。
- B ツリーはソートによる順序をサポートしていますが、ハッシュ インデックスはそれをサポートしていません。
- ハッシュ インデックスは、同等のクエリに対して B ツリーよりも効率的です。
- B ツリーがあいまいクエリに like を使用する場合、like の後の単語 (% で始まるなど) が最適化の役割を果たす可能性があり、ハッシュ インデックスはあいまいクエリをまったく実行できません。
-
38. クラスター化インデックスと非クラスター化インデックスの違いは何ですか? まず、クラスター化インデックスは新しいインデックスではなく、データ保存方法であることを理解してください。クラスタリングとは、データ行と隣接するキー値がコンパクトにまとめて格納されることを意味します。私たちがよく知っている 2 つのストレージ エンジン - MyISAM は非クラスター化インデックスを使用し、InnoDB はクラスター化インデックスを使用します。
次のように言えます:
インデックスのデータ構造はツリーです クラスタードインデックスのインデックスとデータはツリーに格納されます ツリーのリーフノード非クラスター化インデックス インデックスとデータは同じツリー内にありません。 -
- 一个表中只能拥有一个聚簇索引，而非聚簇索引一个表可以存在多个。
- 聚簇索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
- 聚簇索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；
39.回表了解吗？
在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。
例如:select * from user where name = ‘张三’;
40.覆盖索引了解吗？
在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用辅助索引中就能够取得，不用去查主键索引，这时候使用的索引就叫做覆盖索引，避免了回表。
比如，select name from user where name = ‘张三’;
41.什么是最左前缀原则/最左匹配原则？
注意：最左前缀原则、最左匹配原则、最左前缀匹配原则这三个都是一个概念。
最左匹配原则：在InnoDB的联合索引中，查询的时候只有匹配了前一个/左边的值之后，才能匹配下一个。
根据最左匹配原则，我们创建了一个组合索引，如 (a1,a2,a3)，相当于创建了（a1）、(a1,a2)和 (a1,a2,a3) 三个索引。
为什么不从最左开始查，就无法匹配呢？
比如有一个user表，我们给 name 和 age 建立了一个组合索引。
ALTER TABLE user add INDEX comidx_name_phone (name,age);
ログイン後にコピー
组合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 (name 在左边，age 在右边)。
从这张图可以看出来，name 是有序的，age 是无序的。当 name 相等的时候， age 才是有序的。
这个时候我们使用where name= ‘张三‘ and age = ‘20 ‘去查询数据的时候， B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name 相同的时候再比较age。但是如果查询条件没有 name，就不知道下一步应该查哪个 节点，因为建立搜索树的时候 name 是第一个比较因子，所以就没用上索引。
42.什么是索引下推优化？
索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。
- 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL Server，MySQL Server进行过滤条件的判断。
- 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL Server将这一部分判断条件下推给存储引擎，然后由存储引擎通过判断索引是否符合MySQL Server传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
例如一张表，建了一个联合索引（name, age），查询语句：select * from t_user where name like '张%' and age=10;，由于name使用了范围查询，根据最左匹配原则：
不使用ICP，引擎层查找到name like '张%'的数据，再由Server层去过滤age=10这个条件，这样一来，就回表了两次，浪费了联合索引的另外一个字段age。
但是，使用了索引下推优化，把where的条件放到了引擎层执行，直接根据name like '张%' and age=10的条件进行过滤，减少了回表的次数。
索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
锁
43.MySQL中有哪几种锁，列举一下？
如果按锁粒度划分，有以下3种：
- テーブル ロック: 低いオーバーヘッド、高速なロック、強力なロック力、高いロック競合の可能性、最も低い同時実行性、デッドロックなし。
- 行ロック: オーバーヘッドが高く、ロックが遅い。デッドロックが発生する可能性がある。ロックの粒度が小さく、ロック競合の可能性が低く、同時実行性が高い。
- ページ ロック: コストとロック速度はテーブル ロックと行ロックの間です。デッドロックが発生する可能性があります。ロックの粒度はテーブル ロックと行ロックの間で、同時実行性は平均です。
互換性の点では、次の 2 つのタイプがあります。
- 共有ロック (S ロック) は、読み取りロック (読み取りロック) とも呼ばれ、互いにブロックしません。
- 排他的ロック (X ロック)、書き込みロック (書き込みロック) とも呼ばれ、排他的ロックはブロッキングです。一定期間内に 1 つのリクエストのみが書き込みを実行でき、他のロックによる読み取りと書き込みが禁止されます。データ。
44. InnoDB での行ロックの実装について話しますか?
このようなユーザー テーブルを使用して、4 行のデータが挿入される行レベルのロックを表します。主キーの値は 1、6、8、12 で、クラスター化インデックス構造が簡素化され、データ レコードのみが保持されるようになりました。
#InnoDB の行ロックの主な実装は次のとおりです:
レコードロックとは、レコードの行を直接ロックすることです。一意のインデックス (一意のインデックスとクラスター化インデックスを含む) を使用して同等のクエリを実行し、レコードを正確に照合する場合、レコードは直接ロックされます。たとえば、select * from t where id =6 for update; は id=6 でレコードをロックします。
ギャップ ロックのギャップは、レコード間の 2 つの論理部分を指します。データが入力されていないのは、左開き右スペースです。
#ギャップロックとは、一定のギャップ間隔をロックすることです。等価クエリまたは範囲クエリを使用して
record にヒットしなかった場合、対応するギャップ間隔はロックされます。たとえば、select * from t where id =3 for update;または select * from t where id > 1 and id は変換されます (1,6 ) 範囲ロック。 <code>
Next-key Lock Pro キー ロック
Left右閉区間 を開きます。たとえば、上記の (1,6]、(6,8] など)
プロキー ロックは、レコード ロック (Record Locks) とギャップの組み合わせです。ロック (ギャップ ロック) 、つまり、レコード自体をロックすることに加えて、インデックス間のギャップもロックする必要があります。範囲クエリを使用していくつかの
record レコードにヒットすると、即時のキー間隔は次のようになります。注: 一時キー ロックによってロックされる間隔には、最後のレコードの右側の一時キー間隔が含まれます。たとえば、select * from t where id > 5 and id は (4, 7], (7, ∞) をロックします。MySQL のデフォルトの行ロック タイプは <code>Next-Key Locks です。一意のインデックスが使用され、等しい値のクエリが一致する場合レコードの場合、ネクスト キー ロック (ネクスト キー ロック) はレコード ロックに縮退し、一致するレコードがない場合はギャップ ロックに縮退します。
ギャップ ロックand Next-Key Locks は、ファントム読み取りの問題を解決するために使用されます。READ COMMITTED分離レベルでは、Gap Locks および Next-Key Locks無効になります!
上記は行ロックの 3 つの実装アルゴリズムです。さらに、Insert Intention Lock もあります。
Insert Intention Lock
T1 トランザクションがある場合は、(1,6) 間隔の意図ロックを追加します。これで、T2 トランザクションが存在します。 ID が 4 のデータを挿入しようとしています。(1,6) の範囲で挿入意図のロックを取得します。また、ID が 3 のデータを挿入しようとしている T3 トランザクションもあります。また、(1,6) 範囲の挿入意図ロックも取得しますが、2 つの挿入意図ロックは相互に排他的ではありません。
45. 意図ロックは Do です。何か知っていますか?
インテンション ロックはテーブル レベルのロックであり、挿入インテンション ロックと混同しないでください。
インテンション ロックは InnoDB の多粒度ロックをサポートしているようで、これにより問題が解決されます。テーブルのロックと行ロックの共存の問題。
テーブルにテーブル ロックを追加する必要がある場合、テーブル内にロックされているデータ行があるかどうかを判断して、追加が成功するかどうかを判断する必要があります。
インテンション ロックがない場合は、テーブル内のすべてのデータ行を走査して、行ロックがあるかどうかを判断する必要があります。
インテンション ロックがある場合は、テーブル レベルのロックが発生します。テーブル内のデータ行がロックされているかどうかを一度に直接判断できます。
インテンションロックでは、実行するトランザクションAが行ロック(書き込みロック)を掛ける前に、データベースが自動的にテーブルのインテント排他ロックをトランザクションAに掛けます。トランザクション B がテーブルのミューテックス ロックを申請すると、テーブルに意図的な排他ロックが存在するため失敗し、テーブルにミューテックス ロックを申請するとトランザクション B はブロックされます。
#46. MySQL の楽観的ロックと悲観的ロックについて理解していますか?
悲観的ロックは、それによって保護されているデータが常に非常に危険であると信じています。トランザクションが悲観的ロックを取得すると、他のトランザクションはデータを変更できなくなり、ロックが解放されるのを待ってから実行することしかできなくなります。
データベースの行ロック、テーブル ロック、読み取りロック、書き込みロックはすべて悲観的ロックです。
オプティミスティック ロックは、データがあまり頻繁に変更されないと考えます。
オプティミスティックロックは通常、テーブルにバージョン(version)またはタイムスタンプ(タイムスタンプ)を追加することで実装されますが、このうち最もよく使われるのはバージョンです。
トランザクションがデータベースからデータをフェッチすると、データのバージョン (v1) も取得されます。トランザクションがデータへの変更を完了し、テーブルに更新しようとすると、時間がかかります。以前に取り出されたバージョンを削除します。データ内の v1 と最新バージョン v2 を比較し、v1=v2 の場合、データ変更期間中に他のトランザクションがデータを変更しなかったことを意味します。この時点で、トランザクションは変更を許可されていますテーブル内のデータと、変更中にバージョンが変更されます。データが変更されたことを示すには 1 を追加します。
v1 が v2 と等しくない場合は、データ変更期間中に他のトランザクションによってデータが変更されたことを意味します。現時点では、データをテーブルに更新することはできません。一般的な解決策は次のとおりです。ユーザーに通知して再操作してもらいます。悲観的ロックとは異なり、楽観的ロックは通常、開発者によって実装されます。
47.MySQL でデッドロックの問題に遭遇したことがありますか?どのように解決しましたか?
デッドロックのトラブルシューティングの一般的な手順は次のとおりです。
(1) デッドロック ログを確認して、エンジンの innodb ステータスを確認します。
(2) デッドロック SQL を確認します
(3) SQL ロック状況の分析
(4) デッドロックケースのシミュレーション
#(5) デッドロック ログの分析#(6) デッドロック ロック結果の分析
もちろん、これは単純なプロセスの説明にすぎません。実際、運用環境におけるデッドロックはあらゆる種類の奇妙なものであり、トラブルシューティングと解決はそれほど簡単ではありません。
トランザクション
48. MySQL トランザクションの 4 つの主な特徴は何ですか?
原子性: トランザクションは全体として実行され、トランザクションに含まれるデータベース上の操作はすべて実行されるか、まったく実行されません。
- 一貫性: トランザクションの開始前とトランザクションの終了後にデータが破壊されないことを意味します。アカウント A がアカウント B に 10 元を送金した場合、成功または成功に関係なく、A と B の合計金額は変わりません。失敗の。
- 分離: 複数のトランザクションが同時にアクセスする場合、トランザクションは互いに分離されます。つまり、1 つのトランザクションは他のトランザクションの実行効果に影響を与えません。一言で言えば、物事の間に矛盾がないことを意味します。
- 永続性: トランザクションの完了後、トランザクションによってデータベースに加えられた操作上の変更がデータベースに永続的に保存されることを示します。
- 49. それでは、ACID はどのような保証に依存しているのでしょうか?
トランザクションの分離- は、データベース ロック メカニズムによって実現されます。
トランザクションの一貫性- は、UNDO ログによって保証されます: UNDO ログは、トランザクションの挿入、更新、および削除操作を記録する論理ログです。ロールバックする場合は、逆の削除が行われます。データを復元するための更新および挿入操作。 トランザクションの
アトミック性 - と 永続性 は、REDO ログによって保証されます。REDO ログは物理ログである REDO ログと呼ばれます。トランザクションが送信されると、永続化のために最初にトランザクションのすべてのログを REDO ログに書き込む必要があり、トランザクションはコミット操作まで完了しません。
#50. トランザクションの分離レベルは何ですか? MySQL のデフォルトの分離レベルは何ですか?
コミットされていない読み取り
- コミットされた読み取り
- 反復可能な読み取り)
- シリアル化可能
-
MySQL のデフォルトのトランザクション分離レベルは、Repeatable Read (Repeatable Read) です。
51.ファントムリード、ダーティリード、ノンリピートリードとは何ですか?
- トランザクション A と B が交互に実行され、トランザクション A はトランザクション B のコミットされていないデータを読み取ります。これは ダーティ リーディング です。
- トランザクション スコープ内で、2 つの同一のクエリが同じレコードを読み取りますが、異なるデータを返します。これは、反復不可能な読み取りです。
- トランザクション A は範囲の結果セットをクエリし、別の同時トランザクション B はこの範囲にデータを挿入/削除し、サイレントにコミットします。その後、トランザクション A は同じ範囲を再度クエリし、2 回読み取ります。結果セットは次のとおりです。違います、これは ファントム リーディング です。
さまざまな分離レベル、同時トランザクションで発生する可能性のある問題:
| 分離レベル |
ダーティ リード |
反復不可能な読み取り |
ファントム読み取り |
| コミットされていない読み取り コミットされていない読み取り |
Yes | Yes |
はい |
| コミットされた読み取りコミットされた読み取り |
いいえ |
はい |
いいえ |
| Repeatable ReadRepeatable Read |
No |
No |
Yes |
## Serialzable Serializable | ダメダメダメ＃＃＃＃＃＃＃＃＃＃＃＃52. トランザクションのさまざまな分離レベルはどのように実装されますか?
Read uncommitted
Read uncommitted は、言うまでもなく、ロックなしで読み取る原則が採用されています。
- トランザクション読み取りは他のトランザクションの読み取りと書き込みをロックせず、ブロックしません。
- トランザクション書き込みは他のトランザクションの書き込みをブロックしますが、他のトランザクションの読み取りはブロックしません。
コミットされた読み取りおよび反復可能な読み取り
コミットされた読み取りおよび反復可能な読み取りレベルでは、ReadView および MVCC が使用されます。また、各トランザクションは、表示できるバージョン (ReadView) のみを読み取ることができます。
- READ COMMITTED: データを読み取る前に毎回 ReadView を生成します
- REPEATABLE READ: 初めてデータを読み取るときに ReadView を生成します
Serialization
シリアル化の実装では、読み取りと書き込みの両方をロックする原則が採用されています。
シリアル化の場合、同じ行トランザクションに対して、write は 書き込みロック を追加し、read は 読み取りロックを追加します。読み取り/書き込みロックの競合が発生した場合、後でアクセスされるトランザクションは、実行を続行する前に、前のトランザクションの完了を待つ必要があります。
53.MVCCについて理解していますか？それはどのように達成されるのでしょうか?
MVCC (Multi Version Concurrency Control)、中国語名はマルチバージョン同時実行制御で、簡単に言うと、データの履歴バージョンを維持することで、同時アクセス時の読み取り一貫性の問題を解決します。その実装に関しては、Implicit フィールド、Undo ログ、バージョン チェーン、スナップショットの読み取りと現在の読み取り、および Read View といういくつかの重要なポイントを把握する必要があります。
バージョン チェーン
InnoDB ストレージ エンジンの場合、レコードの各行には 2 つの非表示列がありますDB_TRX_ID、DB_ROLL_PTR
- ##DB_TRX_ID
、トランザクション ID、変更されるたびに、トランザクション ID は DB_TRX_ID にコピーされます;
- DB_ROLL_PTR
、ロールバック ポインター、ロールバックセグメントのアンドゥログを指します。
レコードが 1 行だけ含まれる user テーブルがあり、そのときに挿入されたトランザクション ID が 80 であるとします。この時点でのこのレコードのサンプル画像は次のとおりです。
次の 2 つの DB_TRX_ID は 100、##トランザクション #200 は、このレコードに対して update 操作を実行します。プロセス全体は次のとおりです:
各変更は最初に ## されるため、 # Undo
ログが記録され、DB_ROLL_PTR を使用して undo ログ アドレスを指します。したがって、このレコードの変更ログが連結されて バージョン チェーン が形成され、そのバージョン チェーンの先頭ノードがカレント レコードの最新値 であると考えることができます。次のように:
ReadView
Read Committed および Repeatable Read# の場合# #分離レベルの観点からは、送信されたトランザクション変更のレコードを読み取る必要があります。つまり、バージョン チェーン内の特定のバージョンの変更が送信されない場合、そのバージョンのレコードを読み取ることはできません。したがって、Read Committed および Repeatable Read 分離レベルの下で、現在のトランザクションがバージョン チェーン内のどのバージョンを読み取ることができるかを決定する必要があります。そこで、この問題を解決するために ReadView という概念が導入されました。 Read View は、トランザクション Snapshot read
が実行されたときに生成される読み取りビューであり、特定のスケジュールに記録されたスナップショットに相当します。このスナップショットを通じて、次の情報を取得できます。
m_ids: ReadView の生成時に現在のシステムでアクティブな読み取りおよび書き込みトランザクションのトランザクション ID リストを表します。 
min_trx_id: ReadView 生成時に現在のシステムでアクティブな読み取りおよび書き込みトランザクションの中で最小のトランザクション ID、つまり m_ids の最小値を示します。
- max_trx_id: ReadView の生成時にシステム内の次のトランザクションに割り当てる必要がある ID 値を示します。
- creator_trx_id: ReadView を生成したトランザクションのトランザクション ID を示します
- この ReadView を使用すると、特定のレコードにアクセスするときに、以下の手順に従うだけで特定のレコードを確認できます。レコードの一部 バージョンが表示されるかどうか:
-
- アクセスされたバージョンの DB_TRX_ID 属性値が ReadView の Creator_trx_id 値と同じである場合、現在のトランザクションが独自の変更されたレコードにアクセスしていることを意味するため、このバージョンは現在のトランザクションからアクセスできます。
- アクセスされたバージョンの DB_TRX_ID 属性値が ReadView の min_trx_id 値より小さい場合、このバージョンを生成したトランザクションは現在のトランザクションが ReadView を生成する前にコミットされていることを示しているため、このバージョンには次の方法でアクセスできます。現在のトランザクション。
- アクセスされたバージョンの DB_TRX_ID 属性値が ReadView の max_trx_id 値より大きい場合、このバージョンを生成したトランザクションは、現在のトランザクションが ReadView を生成した後に開かれたことを意味するため、このバージョンにはアクセスできません。現在のトランザクション。
- アクセスされたバージョンの DB_TRX_ID 属性値が ReadView の min_trx_id と max_trx_id の間にある場合、trx_id 属性値が m_ids リストにあるかどうかを確認する必要があります。そうであれば、このバージョンのトランザクションは ReadView の作成時に生成されました。アクティブな場合は、このバージョンにアクセスできません。そうでない場合は、ReadView の作成時にこのバージョンを生成したトランザクションがコミットされており、このバージョンにアクセスできることを意味します。
データの特定のバージョンが現在のトランザクションに表示されない場合は、バージョン チェーンに従ってデータの次のバージョンを見つけ、引き続き上記の手順に従って可視性を確認します。 、バージョンまで チェーン内の最後のバージョン。最新バージョンが表示されない場合は、トランザクションに対してレコードが完全に非表示であり、クエリ結果にはそのレコードが含まれていないことを意味します。
MySQL では、READ COMMITTED 分離レベルと REPEATABLE READ 分離レベルの大きな違いは、ReadView を生成するタイミングが異なることです。
READ COMMITTED は、データを読み取るたびに Generate a ReadView であり、他のトランザクションによって送信されたデータを毎回確実に読み取ることができます。REPEATABLE READ は A ReadView は、データが初めて読み取られるときに生成されるため、後続の読み取り結果が完全に一貫していることが保証されます。
高可用性/パフォーマンス
54. データベースの読み取りと書き込みの分離を理解していますか?
読み取りと書き込みの分離の基本原則は、データベースの読み取りおよび書き込み操作を異なるノードに分散させることです。基本的なアーキテクチャ図は次のとおりです:
Read
- データベース サーバーは、1 つのマスターと 1 つのスレーブ、または 1 つのマスターと複数のスレーブのいずれかのマスター/スレーブ クラスターを構築します。
- データベース ホストは読み取りおよび書き込み操作を担当し、スレーブは読み取り操作のみを担当します。
- データベース ホストはレプリケーションを通じてデータをスレーブ マシンに同期し、各データベース サーバーはすべてのビジネス データを保存します。
- ビジネス サーバーは、書き込み操作をデータベース ホストに送信し、読み取り操作をデータベース スレーブに送信します。
55. 読み取りと書き込みの分離の割り当てを実現するにはどうすればよいですか?
読み取り操作と書き込み操作を分離して、異なるデータベース サーバーにアクセスするには、通常、プログラム コードのカプセル化とミドルウェアのカプセル化の 2 つの方法があります。
1. プログラム コードのカプセル化
プログラム コードのカプセル化とは、コード内のデータ アクセス層を抽象化することを指します (したがって、一部の記事ではこの方法を「中間層のカプセル化」と呼んでいます) ) 読み取り操作と書き込み操作の分離とデータベース サーバー接続の管理を実現します。たとえば、Hibernate に基づく単純なカプセル化により、読み取りと書き込みの分離を実現できます。
現在のオープン ソース実装ソリューションの中で、淘宝網の TDDL (淘宝分散データ層、ニックネーム: ヘッダー) はall big）は比較的有名です。
2. ミドルウェアのカプセル化
ミドルウェアのカプセル化とは、読み取り操作と書き込み操作の分離とデータベース サーバー接続の管理を実現する独立したシステムを指します。ミドルウェアはSQL互換のプロトコルを業務サーバに提供するため、業務サーバ自体が読み書きを分離する必要がありません。
業務サーバにとって、ミドルウェアへのアクセスとデータベースへのアクセスに違いはなく、業務サーバから見るとミドルウェアはデータベースサーバとなります。
基本的な構造は次のとおりです:
56. マスター/スレーブ レプリケーションの原理を理解していますか?
- マスター データの書き込み、binlog の更新
- マスターは binlog をスレーブにプッシュするダンプ スレッドを作成します
- スレーブがマスターに接続すると、IO スレッドを作成してbinlog を受信し、リレー ログに記録します。 リレー ログ
#スレーブは SQL スレッドを開始してリレー ログ イベントを読み取り、スレーブ上で実行して同期を完了します。- スレーブは独自の binglog を記録します。
-
57. マスターとスレーブの同期遅延にどう対処するか?
マスタ/スレーブ同期遅延の原因
サーバーはクライアントが接続するために N 個のリンクを開くため、大規模な同時更新操作が行われますが、サーバーからバイナリログを読み取るスレッドは 1 つだけです。もう少し時間がかかるか、特定の SQL でテーブルをロックする必要があるため、マスター サーバー上に大量の SQL バックログが存在し、スレーブ サーバーに同期されません。これは、マスターとスレーブの不一致、つまりマスターとスレーブの遅延につながります。
マスター/スレーブ同期遅延の解決策
マスター/スレーブ レプリケーション遅延を解決するには、いくつかの一般的な方法があります:
たとえば、アカウントの登録が完了した後、ログイン時にアカウントを読み取る読み取り操作は次のようになります。メインデータベースサーバーにも送信されます。この方法はビジネスとの結びつきが強く、ビジネスへの侵入と影響が大きく、新人プログラマーがこのようなコードの書き方を知らないとバグが発生します。
これは、通常「二次読み取り」と呼ばれるもので、二次読み取りです。ビジネスに束縛されず、基盤となるデータベースによってアクセスされる API をカプセル化するだけで済みます。実装コストは小さいです。欠点は、セカンダリ読み取りが多い場合、ホストに対する読み取り操作の負荷が大幅に増加することです。たとえば、ハッカーがアカウントを暴力的にクラッキングすると、大量の二次読み取り操作が発生し、ホストが読み取り操作の圧力に耐えられなくなり、崩壊する可能性があります。
例えば、ユーザー管理システムの場合、登録業務やログイン業務などの読み書き操作はすべてホストにアクセスしますが、ユーザーの紹介、恋愛、レベルなどのサービスは、ユーザーが変わっても読み書き分離が可能です。ログインできない場合に比べて、ビジネスへの影響ははるかに小さいため、許容できます。
58. データベースは通常どのように分割していますか?
- データベースの垂直分割: テーブルに基づいて、異なるビジネス所属に応じて、異なるテーブルが異なるデータベースに分割されます。
- 水平サブデータベース: フィールドに基づいて、特定の戦略 (ハッシュ、範囲など) に従って、1 つのデータベース内のデータが分割されます。ライブラリ内の複数に分割します。
#59. テーブルはどのように分割しますか?
- 水平テーブル分割: フィールドと特定の戦略 (ハッシュ、範囲など) に基づいて、1 つのテーブル内のデータを複数のテーブルに分割します。
- テーブルの垂直分割: フィールドに基づいて、フィールドのアクティビティに従って、テーブル内のフィールドが異なるテーブル (メイン テーブルと拡張テーブル) に分割されます。
#60. 水平テーブルシャーディングのルーティング方法は何ですか?
ルーティングとは何ですか?それは、データをどのテーブルに分割するかということです。
水平テーブルシャーディングには 3 つの主要なルーティング方法があります:
-
範囲ルーティング: 順序付けされたデータ列を選択します (シェーピング、タイムスタンプなど)。ルートとして 条件に応じて、異なるセグメントが異なるデータベース テーブルに分散されます。
一部の決済システムを観察すると、決済会社が時間ごとにテーブルを分割しているため、1 年以内の支払記録しか確認できないことがわかります。
レンジ ルーティング設計の複雑さは、主にセグメント サイズの選択に反映されます。セグメントが小さすぎると、セグメント化後のサブテーブルが多すぎて、メンテナンスの複雑さが増加します。セグメントが大きすぎると、単一のテーブルでパフォーマンスの問題が発生する可能性があります。一般に、セグメント サイズは 100 万から 2,000 万の間であることが推奨されます。ビジネスに基づいて適切なセグメント サイズを選択する必要があります。
レンジ ルーティングの利点は、データの増加に応じて新しいテーブルをスムーズに拡張できることです。たとえば、現在のユーザー数が 100 万人である場合、その数が 1,000 万人に増加した場合、新しいテーブルを追加するだけで済み、元のデータを変更する必要はありません。範囲ルーティングの比較的暗黙的な欠点は、分散が不均一であることです。テーブルが 1,000 万に従ってテーブルに分割されている場合、あるセグメントに格納される実際のデータ量が 1,000 のみになる可能性がありますが、別のセグメントには実際のデータ量が格納されません。 900です。1万です。
-
ハッシュ ルーティング: ハッシュ操作の特定の列 (または特定の列の組み合わせ) の値を選択し、ハッシュ結果に基づいてそれを異なるデータベース テーブルに分散します。 。
また、注文 ID を例にとると、最初から 4 つのデータベース テーブルを計画している場合、ルーティング アルゴリズムは単純に ID % 4 の値を使用して、データが保存されるデータベース テーブル番号を表すことができます。が属し、ID は です。注文 12 は番号 50 のサブテーブルに配置され、ID 13 の注文は番号 61 のサブテーブルに配置されます。
ハッシュ ルーティング設計の複雑さは、主に初期のテーブル数の選択に反映されます。テーブルが多すぎると保守が面倒になり、テーブルが少なすぎると単一のテーブルでパフォーマンスの問題が発生する可能性があります。ハッシュルーティングを使用すると、サブテーブルの数を増やすのが非常に面倒になり、すべてのデータを再分散する必要があります。ハッシュ ルーティングの長所と短所は、基本的にレンジ ルーティングとは逆です。ハッシュ ルーティングの利点は、テーブルが比較的均等に分散されることです。欠点は、新しいテーブルを拡張するのが面倒で、すべてのデータを再分散する必要があることです。
-
ルーティングの構成: ルーティングの構成は、独立したテーブルを使用してルーティング情報を記録するルーティング テーブルです。注文 ID を例として、新しい order_router テーブルを追加します。このテーブルには、orderjd と tablejd という 2 つの列が含まれています。対応する table_id は、orderjd に基づいてクエリできます。
構成ルーティング設計はシンプルで非常に柔軟に使用でき、特にテーブルを拡張する場合に、指定したデータを移行してからルーティング テーブルを変更するだけで済みます。
ルーティングを構成するデメリットは、複数回クエリを実行する必要があるため、全体のパフォーマンスに影響することと、ルーティング テーブル自体が大きすぎる場合 (たとえば、ルーティング テーブルをデータベースとテーブルに再度分割すると、無限ループのルーティング アルゴリズムの選択の問題に直面することになります。
61. ダウンタイムなしで容量拡張を実現するにはどうすればよいですか?
実のところ、ダウンタイムなしの拡張は非常に面倒でリスクの高い操作ですが、もちろん、面接の方がはるかに簡単に答えることができます。
- #第 2 段階: オンライン二重書き込み、新しいデータベースへのクエリ
同期と履歴の検証を完了するdata
第 3 段階: 古いライブラリがオフラインになる
sharding-jdbc
Mycat
63. それでは、サブデータベースとサブテーブルによってどのような問題が発生すると思いますか? - サブデータベースの観点から:
-
トランザクションの問題
リレーショナル データベースを使用する場合には大きな違いがありますトランザクションの整合性が保証されるためです。 - データベースが分割された後は、単一マシンのトランザクションは必要なくなるため、分散トランザクションを使用して解決する必要があります。
クロスデータベース JOIN の問題
1 つのデータベース内にいる場合、JOIN を使用してテーブルをクエリすることもできますが、データベースをまたいだ後は、データベースでは、JOIN は使用できなくなりました。 -
現時点での解決策は、ビジネスコードで相関付ける
です。つまり、最初に1つのテーブルのデータを確認し、次に得られた結果から別のテーブルを確認してから、コードを使用します。相関して最終結果を取得します。
このメソッドは実装が少し複雑ですが、許容範囲です。
また、適切に冗長である可能性があるフィールドもいくつかあります。たとえば、前のテーブルには相関 ID が保存されていましたが、ビジネスでは、対応する名前またはその他のフィールドを返す必要があることがよくありました。現時点では、これらのフィールドを現在のテーブルに冗長的に追加して、関連付けが必要な操作を削除できます。
もう 1 つの方法は、データ異質性
です。バイナリログ同期やその他の方法を通じて、クロスデータベース結合が必要なデータを ES などのストレージ構造に異性化し、ES を通じてクエリします。 #サブテーブルの観点から:
# クロスノード数、順序付け、グループ化、集計関数の問題
ビジネス コードによってのみ実装できます。またはミドルウェアを使用して、各テーブルのデータを要約、並べ替え、ページングして返すことができます。
データ移行、容量計画の方法、拡張が再び必要になるかどうか将来など、すべて考慮する必要がある問題です。
データベース テーブルが分割されると、データベース自体の主キー生成メカニズムに依存できなくなります。グローバルな主キーが一意であることを保証するには、何らかの手段が必要です。
これは依然として自己増加ですが、自己増加ステップ サイズが設定されています。たとえば、現在 3 つのテーブルがあり、ステップ サイズは 3 に設定されており、3 つのテーブルの初期 ID 値はそれぞれ 1、2、3 です。このようにして、最初のテーブルの ID の増加は 1、4、7 になります。 2 番目のテーブルは 2、5、8 です。 3 番目のテーブルは 3、6、9 であるため、重複はありません。
UUID、これは最も単純ですが、不連続な主キーの挿入により深刻なページ分割が発生し、パフォーマンスが低下します。
分散 ID、より有名なのは Twitter のオープンソース ソンフレーク スノーフレーク アルゴリズムです
運用と保守
64.百万レベル以上のデータを削除するにはどうすればよいですか?
インデックスについて: インデックスには追加のメンテナンス コストが必要であり、インデックス ファイルは別個のファイルであるため、データを追加、変更、または削除すると、インデックス ファイルに対する追加の操作が発生します。これらの操作 追加 IOを消費する必要があり、追加・変更・削除の実行効率が低下します。
したがって、データベース内の何百万ものデータを削除する場合、MySQL 公式マニュアルを参照すると、データの削除速度は作成されたインデックスの数に正比例することがわかります。
- #したがって、何百万ものデータを削除したい場合は、最初にインデックスを削除します
#次に、不要なデータを削除します
オンライン データベース データの量が数百万、数千万に達すると、テーブルが長時間ロックされる可能性があるため、フィールドの追加はそれほど簡単ではありません。
大きなテーブルにフィールドを追加するには、通常、次の方法があります。
中間テーブルを介して変換する-
一時的な新しいテーブルを作成して変換する古いテーブル 構造を完全にコピーし、フィールドを追加し、古いテーブルのデータをコピーし、古いテーブルを削除し、新しいテーブルに古いテーブルの名前を付けます。この方法では、一部のデータが失われる可能性があります。
pt-online-schema-change を使用します-
pt-online-schema-change
は、percona 社によって開発されたツールです。オンラインで使用 テーブル構造を変更する原則も、中間テーブルを通じて行われます。
最初にスレーブ データベースに追加してから、マスター/スレーブ切り替えを実行します-
テーブルに大量のデータがあり、ホット テーブル (読み取りおよび書き込みが特に頻繁に行われる場合)、まずスレーブ データベースに追加し、次にマスターとスレーブを切り替えて、切り替え後に他のいくつかのノードにフィールドを追加することを検討できます。
66. MySQL データベースの CPU が急増した場合はどうすればよいですか?
トラブルシューティング プロセス:
(1) top コマンドを使用して、mysqld が原因であるか、他の理由が原因であるかを観察して判断します。
(2) mysqld が原因の場合は、processlist を表示し、セッションのステータスを確認し、リソースを消費する SQL が実行されているかどうかを確認します。
(3) 消費量の多い SQL を見つけて、実行計画が正確かどうか、インデックスが欠落していないか、データ量が多すぎるかどうかを確認します。
処理:
(1) これらのスレッドを強制終了します (CPU 使用率が減少するかどうかを観察します)。
(2) 対応する調整を行います (インデックスの追加、SQL の変更など)。 、メモリパラメータを変更します)
(3) これらの SQL を再実行します。
その他の状況:
各 SQL ステートメントが多くのリソースを消費しないにもかかわらず、突然多数のセッションが接続され、CPU の使用率が急増する可能性もあります。アプリケーションに連絡する必要があります。接続数が急激に増加する理由を分析し、接続数の制限など、対応する調整を行ってみましょう。
[関連する推奨事項:
mysql ビデオ チュートリアル]
#char
45. 意図ロックは Do です。何か知っていますか? 
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
