MySQL の最適化とインデックス作成の方法

インデックスの簡単な紹介

インデックスの本質:

• MySQL インデックスや他のリレーショナル データベースのインデックスの本質は次のとおりです。たった一文で、スペースを時間に置き換えます。

インデックスの役割:

• インデックス リレーショナル データベースは、行データの取得を高速化するために使用されます。テーブル (ディスクストレージ) データ構造

インデックスの分類

データ構造上のカテゴリ:

• # #HASH インデックス

• 等しい値の一致は非常に効率的です

• 範囲検索はサポートされていません

• ツリー インデックス

• 二分木、再帰的二分探索法、左が小さく右が大きい

• バランス型バイナリツリー、バイナリ ツリーからバランスのとれたバイナリ ツリーへ。主な理由は左利きと右利きです。

• 欠点 1、IO 回数が多すぎます

• #欠点 2、IO 使用率が高くなく、IO 飽和

• マルチパス分散検索ツリー (B ツリー)

• ツリーの高さを大幅に削減する機能

• B ツリー

• 左の機能を使用した機能クローズド比較方式

• ルート ノード サポート ノードにはデータ領域がなく、リーフ ノードのみにデータ領域が含まれます (端的に言えば、ルート ノードと子ノードが存在していても)

データ 13 を検索すると、次のようになります。ルート ノードと子ノードの両方を見つけますが、常にリーフ ノードを見つけます。

バイナリ ツリー バランス型バイナリ ツリー、B ツリーの比較:

画像は、次の場合の状況を示しています。これは自動増加する主キーです 次:

バイナリ ツリーは明らかにリレーショナル データベース インデックスには適していません (テーブル全体のスキャンと変わりません)。

バランスのとれたバイナリ ツリーに関しては、この状況は解決されますが、ツリーが細くて高くなってしまい、前述の IO クエリが多すぎて IO 使用率が低下する原因にもなります。

B-tree はこれら 2 つの問題を明らかに解決しています。したがって、MySQL がこの場合でも B-tree を使用し、これらの機能強化を行った理由を以下で説明します。

#B ツリーと B ツリーの比較:

B B ツリー上のツリーの最適化:

IO 効率が高くなります (B ツリーの各ノードはデータ領域を保持しますが、B ツリーは保持しません。データの一部をクエリするために 3 つのレイヤーをトラバースする必要がある場合、明らかに B ツリー クエリの IO 消費量は小さくなります)

範囲検索の効率が高くなります (図に示すように、B ツリーは自然なリンク リスト形式であり、最後のチェーン構造に従って検索するだけで済みます)

インデックスベースのデータ スキャンの方が効率的です。

インデックス タイプの分類

インデックス タイプは 2 つのカテゴリに分類できます:

主キー インデックス
補助インデックス (セカンダリ インデックス)
一意のインデックス
複合インデックス
通常のインデックス
カバード インデックス

プライマリ キー インデックスは比較的優れたインデックスを備えていますが、通常、SQL の最適化では、補助インデックスの改善や補足を行います。

B ツリーはストレージ エンジン レベルで実装されます

2 つのテーブルをそれぞれ作成します
• test_innodb

  (InnoDB をストレージ エンジンとして使用) test_myisam (ストレージ エンジンとして MyISAM を使用) 次の図は、2 つのテーブル ディスク実装に関連するファイルを示しています。2 つのストレージ エンジンは、B ツリー ディスク実装では完全に異なります。

B ツリーは MyISAM に実装されています:

• .frm ファイルこのテーブルの ID フィールドや名前フィールドなどのテーブル スケルトン ファイルはここに保存されます

*.MYD (D=data) はデータを保存します

• ##*.MYI (I=index) はインデックスを格納します

たとえば、次のように実行すると、次に SQL ステートメントを実行し、MyISAM で、最初に test_myisam.MYI で 103 を見つけ、次にアドレス 0x194281 を取得し、次に test_myisam.MYD でデータを見つけて返します。

• うわぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁん

  • 如果test_myisam表中，id为主键索引，name也是一个索引，那么在test_myisam.MYI中则会有两个平级的B+树，这也导致MyISAM引擎中主键索引和二级索引是没有主次之分的，是平级关系。因为这种机制在MyISAM引擎中，有可能使用多个索引，在InnoDB中则不会出现这种情况。

  B+树在InnoDB落地：

  

  

  • InnoDB不像MyISAM来独立一个MYD 文件来存储数据，它的数据直接存储在叶子结点关键字对应的数据区在这保存这一个id列所有行的详细记录。

  • InnoDB 主键索引和辅助索引关系

  我们现在执行如下SQL语句,他会先去找辅助索引，然后找到辅助索引下101的主键，再去回表（二次扫描）根据主键索引查询103这条数据将其返回。

  SELECT id,name from test_myisam where name ='zhangsan'

  这里就有一个问题了，为什么不像MyISAM在辅助索引下直接记录磁盘地址，而是要多此一举再去回表扫描主键索引，这个问题在下面相关面试题中回答，记一下这个问题是这里来的。

  

  相关面试题

  • 为什么MySQL选择B+树作为索引结构

  这个就不说了，上文应该讲清楚了。

  • B+树在MyISAM和InnoDB落地区别。

  这个可以总结一下，MyISAM落地数据储存会有三个类型文件 ，.frm文件是表骨架文件，.MYD（D=data）则储存数据 ，.MYI （I=index）则储存索引，MyISAM引擎中主键索引和二级索引平级关系，在MyISAM引擎中，有可能使用多个索引，InnoDB则相反，主键索引和二级索有严格的主次之分在InnoDB一条语句只能用一个索引要么不用。

  • 如何判断一条sql语句是否使用了索引。

  可以通过执行计划来判断 可以在sql语句前explain/ desc

  set global optimizer_trace='enabled=on' 打开执行计划开关他将会把每一条查询sql执行计划记录在information_schema 库中OPTIMIZER_TRACE表中

  • 为什么主键索引最好选择自增列？

  自增列，数据插入时整个索引树是只有右边在增加的，相对来说索引树的变动更小。

  • 为什么经常变动的列不建议使用索引？

  和上一个问题原因一样，当一个索引经常发生变化，那么就意味这，这个缩印树也要经常发生变化。4

  • 为什么说重复度高的列，不建议建立索引？

  这个原因是因为离散性，比如说，一张一百万数据的表，其中一个字段代表性别，0代表男1代表女，把这字段加了索引，那么在索引树上，将会有大量的重复数据。而我们常见的索引建立一般都是驱动型的。其目的是，尽可能的删减数据的查询范围，这个显然是不匹配的。

  • 什么是联合索引

  联合索引是一个包含了多个功效的索引，他只是一个索引而不是多个，

  其次，单列索引是一种特殊的联合索引

  联合索引的创立要遵循最左前置原则（最常用列>离散度>占用空间小）

  • 什么是覆盖索引

  通过索引项信息可直接返回所需要查询的索引列，该索引被称之为覆盖索引，说白了就是不需要做回表操作，可以从二级索引中直接取到所需数据。

  • 什么是ICP机制

  索引下推，简单点来说就是，在sql执行过程中，面对where多条件过滤时，通过一个索引，完成数据搜索和过滤条件其，特点能减少io操作。

  • 在InnoDB表中不可能没有主键对还是不对原因是什么？

  首先这句话是对的，但是情况有三种:

  • つまり、このフィールドを主キーとして手動で指定すると、このフィールドはクラスター化インデックスとして使用されます。

  • 主キーが明示的に指定されていない場合は、次の 2 つの状況が考えられます。

  • 彼は、最初の UK (一意のキー) を検索します。主キーインデックス インデックス作成を整理します。

  • 主キーも UK も指定されていない場合、rowId (InnoDB テーブルの各レコードには非表示 (6 バイト) rowId があります) がクラスター化インデックスとして使用されます。

  • #テーブルリターン操作とは

  InnoDB の補助インデックスに基づくクエリの内容、補助インデックスから直接取得することはできず、主キー インデックスに基づく二次スキャンが必要な操作はテーブル リターン操作と呼ばれます。

  • InnoDB の補助インデックスのリーフ ノード データ領域は、MyISAM のようにディスク アドレスを記録するのではなく、主キー インデックスの値を記録するのはなぜですか。

  理由は実は非常に単純で、主キーインデックスのデータ構造が頻繁に変化するためで、補助インデックスデータ領域にディスクアドレスが記録されているとします。 10 個の補助インデックスがある場合、主キーのインデックス構造が変更されると、補助インデックスに 1 つずつ通知する必要があり、主キーのインデックス構造は頻繁に変更され、追加と削除はその

  データ構造に影響を与える可能性があります。
  

以上がMySQL の最適化とインデックス作成の方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。