MySQL 8 の新機能: 非表示のインデックス

MySQL 8 の新機能: 非表示のインデックス

背景

インデックスは諸刃の剣です。クエリ速度は向上しますが、DML 操作も遅くなります。結局のところ、インデックスのメンテナンスには一定のコストがかかります。そのため、インデックスについては、追加すべきものを追加し、不要なものを削除します。前者は加算であり、後者は減算です。しかし、実際の仕事では、誰もが前者を重視し、後者を行うことはめったにありません。理由は後者にあり、それは難しい。難しいのは操作そのものではなく、インデックスが役に立たないことをどうやって確認するかです。

役に立たないインデックスを識別する方法

非表示のインデックスが登場する前は、sys.schema_unused_indexes を使用して未使用のインデックスを識別できました。 MySQL 5.6 では、sys ライブラリがなくても、ビューのベース テーブルを介してクエリを実行できます。

mysql> 作成テーブル sys.schema_unused_indexes\G を表示します
************************** 1. 行 ****************************
        ビュー: schema_unused_indexes
    ビューの作成: CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`schema_unused_indexes` (
`object_schema`、` object_name`、 `index_name`)select` t`.`object_schema`は `object_schema`、` object_name`、 `t`.`index_name` as` index_name` a( ex_usage` `t`に` information_schema`.`Statistics` `s` on index_name` utf8)= `s`.`index_name`)))))))))))))) = 1)および( `s`.`seq_in_index` = 1))` t`.`object_schema`、 `object_name`character_set_client:utf8mb4による注文
照合接続: utf8mb4_0900_ai_ci
セットに 1 行、警告 1 件 (0.00 秒)

しかし、このアプローチにも欠点があります。

1. インスタンスが再起動されると、performance_schema 内のデータはクリアされます。

2. 上記のクエリに基づいてインデックスが削除され、クエリのパフォーマンスが突然低下した場合はどうなるでしょうか?

目に見えないインデックスの出現により、上記の欠点を効果的に補うことができます。インデックスを非表示に設定すると、FORCE INDEX が使用されている場合でも、オプティマイザーは実行プランを選択するときにインデックスを自動的に無視します。

もちろん、これは optimizer_switch 変数の use_invisible_indexes オプションによって決定され、デフォルトではオフになっています。インデックス調整前と調整後のクエリ実行プランの違いを確認したい場合は、次のようにセッションレベルでuse_invisible_indexesの値を調整できます。

mysql> テーブル slowtech.t1\G の作成を表示します
************************** 1. 行 ****************************
   表: t1
テーブルの作成: CREATE TABLE `t1` (
 `id` int(11) NULLではない、
 `name` varchar(10) デフォルト NULL,
 主キー (`id`)、
 キー `idx_name` (`name`) /*!80000 非表示 */
) エンジン=InnoDB デフォルト文字セット=utf8mb4 照合=utf8mb4_0900_ai_ci
セット内の 1 行 (0.00 秒)
mysql> explain select * from slowtech.t1 where name='a';
+----+-------------+--------+-----------+--------+---------------+-------+--------+----------+-----------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+--------+---------------+-------+--------+----------+-----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | where の使用 |
+----+-------------+--------+-----------+--------+---------------+-------+--------+----------+-----------+-------------+
セットに 1 行、警告 1 件 (0.00 秒)
mysql> セッション optimizer_switch="use_invisible_indexes=on" を設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> explain select * from slowtech.t1 where name='a';
+----+-------------+--------+-----------+---------+---------------+-----------+--------+----------+-----------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+---------+---------------+-----------+--------+----------+-----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_name | idx_name | 43 | const | 1 | 100.00 | インデックスを使用 |
+----+-------------+--------+-----------+---------+---------------+-----------+--------+----------+-----------+-------------+
セットに 1 行、警告 1 件 (0.00 秒)

非表示のインデックスに対する一般的な操作

テーブル t1(id int 主キー、name varchar(10)、インデックス idx_name (name) 非表示) を作成します。
テーブル t1 を変更してインデックス idx_name を表示可能にします。
テーブル t1 を変更し、インデックス idx_name を非表示にします。

非表示のインデックスを確認する方法

mysql> information_schema.statistics から table_schema、table_name、index_name、column_name、is_visible を選択します。ここで、is_visible='no' です。
+--------------+-------------+-------------+-------------+-------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | IS_VISIBLE |
+--------------+-------------+-------------+-------------+-------------+
| slowtech | t1 | idx_name | 名前 | NO |
+--------------+-------------+-------------+-------------+-------------+
セット内の 1 行 (0.00 秒)

知らせ

1. 主キーインデックスを非表示に設定することはできません。

要約する

以上が、私がご紹介したMySQL 8の新機能「Invisible Indexes」です。お役に立てれば幸いです。ご質問がございましたら、メッセージを残していただければ、すぐに返信させていただきます。

以下もご興味があるかもしれません:
  • MySQL 8.0 の新機能: アトミック DDL ステートメントのサポート
  • MySQL 8の新機能である降順インデックスの基礎となる実装の詳細な説明
  • MySQL 8.0 の新機能: ハッシュ結合
  • MySQL 8.0 の新機能の落とし穴と解決策についての簡単な説明 (要約)
  • MySQL 8 の新機能: 降順インデックスの詳細
  • MySQL 8 の新機能: 永続的なグローバル変数を変更する方法
  • MySQL 8 の新機能: 自動増分主キーの永続性に関する詳細な説明
  • MySQL 8.0 の新機能の分析 - トランザクション データ ディクショナリとアトミック DDL

<<:  Vue で CSS カスタム変数を使用する方法

>>:  Linux のファイル権限とグループ変更コマンドの詳細な説明

推薦する

IDEA を MYSQL データベースに接続するための構成時に失敗する問題の解決策

この記事では、主に、IDEA を MYSQL データベースに接続するための構成時に失敗する問題の解決...

面接では、select...for update がテーブルをロックするのか、それとも行をロックするのか尋ねられました。

目次確認する:例の検証と組み合わせるselect クエリ ステートメントはロックされませんが、sel...

Linux の Centos7 に Mysql5.7.19 をインストールする詳細なチュートリアル

1. MySQLをダウンロードするURL: https://dev.mysql.com/downlo...

CentOS 8にJenkinsをインストールする方法

CentOS 8 に Jenkins をインストールするには、root アカウントまたは sudo ...

HTML テーブルタグチュートリアル (20): 行の背景色属性 BGCOLOR

BGCOLOR 属性を使用して、行の背景色を設定できます。基本的な構文<TR BGcolor...

Docker、プレーヤー機能を備えたCMSオンデマンドシステムを構築

目次文章1. 機械を準備する2. Dockerをインストールする1. 依存パッケージをインストールす...

CSS3で跳ねるボールのアニメーションを実現

私は通常、大手ウェブサイトの特別ページや製品リリースページを訪問するのが好きです。なぜなら、たくさん...

mysql5.7.21.zip インストールチュートリアル

mysql5.7.21 zipの詳細なインストール手順は次のとおりです。 1. 解凍して指定されたデ...

Dockerイメージのインポートとエクスポートの実装

GitLabのDocker使用法gitlab ドッカー起動コマンド docker run -d -p...

ウェブページエクスペリエンス: 計画と設計

1. デザインの方向性を明確にする<br />まず、どのユーザーを対象にデザインするのか...

Vueはユーザーログインとトークン検証を実装します

フロントエンドとバックエンドを完全に分離する場合、Vue プロジェクトでトークン検証を実装する一般的...

MySQL InnoDB インデックス拡張の詳細な説明

インデックス拡張: InnoDB は、プライマリ キー列をそのインデックスに追加することで、各セカン...

js でショッピングモールのシミュレーションを実現

HTML、CSS、JSフロントエンドを学習中の皆さん、今回はショッピングモールの事例の実装をシェアし...

Nginxを使用してストリーミングメディアサーバーを構築し、ライブブロードキャスト機能を実現する

前面に書かれた近年、ライブストリーミング業界は非常に人気が高まっています。伝統的な業界でのライブスト...

LinuxにKafkaをインストールする

目次1.1 前提条件としてのJava環境1.2 Zookeeperのインストールと設定1.3 Kaf...