ホームページ  >  記事  >  データベース  >  MySQL の最適化とインデックス作成の方法

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

PHPz
PHPz転載
2023-06-02 13:58:211084ブラウズ

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

インデックスの本質:

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

インデックスの役割:

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

インデックスの分類

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

  • # #HASH インデックス

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

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

  • ツリー インデックス

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

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

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

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

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

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

  • B ツリー

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

MySQL の最適化とインデックス作成の方法 インデックスベースのデータ スキャンの方が効率的です。

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

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

    主キー インデックス
  • 補助インデックス (セカンダリ インデックス)
    • 一意のインデックス
    • 複合インデックス
    • 通常のインデックス
    • カバード インデックス
プライマリ キー インデックスは比較的優れたインデックスを備えていますが、通常、SQL の最適化では、補助インデックスの改善や補足を行います。

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

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

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

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

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

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

  • *.MYD (D=data) はデータを保存します
  • ##*.MYI (I=index) はインデックスを格納します

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

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

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

    B+树在InnoDB落地:

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

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

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

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

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

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

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

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

    相关面试题

    • 为什么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 サイトの他の関連記事を参照してください。

声明:
この記事はyisu.comで複製されています。侵害がある場合は、admin@php.cn までご連絡ください。