MySQL 関数インデックス最適化ソリューション

MySQL 関数インデックス最適化ソリューション

MySQL を使用する場合、多くの開発者は一部の列に対して関数計算を実行することが多く、その結果、インデックスを使用できなくなります。データ量が多いと、クエリの効率が低下します。この状況に対処するために、この記事では MySQL 5.7 と MySQL 8.0 をさまざまな方法で最適化します。

1. MySQL 5.7

MySQL 5.7 は関数インデックスをサポートしていないため、関数インデックスに遭遇した場合は、それを修正する必要があります。そうしないと、クエリ対象のフィールドにインデックスがあっても、実行中にインデックスを使用できず、テーブル全体のスキャンが実行されます。大量のデータを持つテーブルのクエリ時間は長くなります。具体的なケースは以下のとおりです。

1.1 テストテーブルとデータを作成する

mysql> testdb を使用します。
データベースが変更されました
mysql> テーブル tb_function(id int primary key auto_increment,name varchar(100),create_time datetime); を作成します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> tb_function(name,create_time) に値を挿入します('anniuadaOAIFAPUHIA','2020-07-01 12:00:00');
クエリは正常、1 行が影響を受けました (0.02 秒)

mysql> tb_function(name,creatE_time) に値を挿入します('CWQSsar3qcssg','2020-07-01 15:00:00');
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> tb_function(name,creatE_time) に値を挿入します('vxfqrt2adafz','2020-07-01 21:30:00');
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> tb_function(name,creatE_time) に値を挿入します('etxzwrwbdhegqgaheqhag','2020-07-02 01:30:00');
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> tb_function(name,creatE_time) に値を挿入します('awrs433fsgvsfwtwg','2020-07-02 03:30:00');
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> tb_function(name,creatE_time) に値を挿入します('awrs433fsgvsfwtwg','2020-07-02 07:32:00');
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> tb_function(name,creatE_time) に値を挿入します('awrs433fsgvsfwtwg','2020-07-02 10:32:00');
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> tb_function(name,creatE_time) に値を挿入します('tuilklmdadq','2020-07-02 15:32:00');
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> tb_function(name,creatE_time) に値を挿入します('wesv2wqdshehq','2020-07-02 20:32:00');
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> tb_function(name,creatE_time) に値を挿入します('89yoijnlkwr1','2020-07-03 02:56:00');
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> tb_function(name,creatE_time) に値を挿入します('olj;nsaaq','2020-07-03 08:41:00');
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> tb_function(name,creatE_time) に値を挿入します('ygo;jkdsaq','2020-07-03 16:20:00'); 
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> tb_function から * を選択します。
+----+-----------------------+---------------------+
| ID | 名前 | 作成時刻 |
+----+-----------------------+---------------------+
| 1 | anniuadaOAIFAPUHIA | 2020-07-01 12:00:00 |
| 2 | CWQSsar3qcssg | 2020-07-01 15:00:00 |
| 3 | vxfqrt2adafz | 2020-07-01 21:30:00 |
| 4 | etxzwrwbdhegqgaheqhag | 2020-07-02 01:30:00 |
| 5 | awrs433fsgvsfwtwg | 2020-07-02 03:30:00 |
| 6 | awrs433fsgvsfwtwg | 2020-07-02 07:32:00 |
| 7 | awrs433fsgvsfwtwg | 2020-07-02 10:32:00 |
| 8 | tuilklmdadq | 2020-07-02 15:32:00 |
| 9 | wesv2wqdshehq | 2020-07-02 20:32:00 |
| 10 | 89yoijnlkwr1 | 2020-07-03 02:56:00 |
| 11 | olj;nsaaq | 2020-07-03 08:41:00 |
| 12 | ygo;jkdsaq | 2020-07-03 16:20:00 |
+----+-----------------------+---------------------+
セット内の行数は 12 です (0.00 秒)

1.2 インデックスを作成する

create_timeフィールドにインデックスを作成する

mysql> テーブル tb_function を変更し、キー idx_create_time(create_time) を追加します。
クエリは正常、影響を受けた行は 0 行 (0.13 秒)
レコード: 0 重複: 0 警告: 0

1.3 時間によるクエリ

2020-07-01 に作成されたすべてのレコードをクエリする

mysql> tb_function から * を選択します。 where date(create_time)='2020-07-01';
+----+--------------------+---------------------+
| ID | 名前 | 作成時刻 |
+----+--------------------+---------------------+
| 1 | anniuadaOAIFAPUHIA | 2020-07-01 12:00:00 |
| 2 | CWQSsar3qcssg | 2020-07-01 15:00:00 |
| 3 | vxfqrt2adafz | 2020-07-01 21:30:00 |
+----+--------------------+---------------------+
セット内の 3 行 (0.00 秒)

実行計画は次のとおりです。

mysql> explain select * from tb_function where date(create_time)='2020-07-01';
+----+--------------+-------------+------------+--------+---------------+-------+-------+--------+---------+----------+----------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+------------+--------+---------------+-------+-------+--------+---------+----------+----------+
| 1 | SIMPLE | tb_function | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | where の使用 |
+----+--------------+-------------+------------+--------+---------------+-------+-------+--------+---------+----------+----------+
セットに 1 行、警告 1 件 (0.00 秒)

実行計画から、フルスキャンが実行されたことがわかります。

1.4 最適化

MySQL 5.7は関数インデックスをサポートしていないため、インデックスを実装するにはSQLの記述を変更する必要があります(または仮想列を使用します)。上記のSQLは次のように変更できます。

mysql> select * from tb_function where create_time>='2020-07-01' and create_time<date_add('2020-07-01',INTERVAL 1 day);
+----+--------------------+---------------------+
| ID | 名前 | 作成時刻 |
+----+--------------------+---------------------+
| 1 | anniuadaOAIFAPUHIA | 2020-07-01 12:00:00 |
| 2 | CWQSsar3qcssg | 2020-07-01 15:00:00 |
| 3 | vxfqrt2adafz | 2020-07-01 21:30:00 |
+----+--------------------+---------------------+
セット内の 3 行 (0.00 秒)

実行計画は次のとおりです。

mysql> explain select * from tb_function where create_time>='2020-07-01' and create_time<date_add('2020-07-01',INTERVAL 1 day);
+----+--------------+-------------+-----------+--------+-----------------+-------+-------+-------+--------+------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+-----------+--------+-----------------+-------+-------+-------+--------+------------------------+
| 1 | SIMPLE | tb_function | NULL | 範囲 | idx_create_time | idx_create_time | 6 | NULL | 3 | 100.00 | インデックス条件を使用 |
+----+--------------+-------------+-----------+--------+-----------------+-------+-------+-------+--------+------------------------+
セットに 1 行、警告 1 件 (0.00 秒)

変更後、インデックスが使用されていることがわかります。

2. MySQL 8.0

MySQL 8.0 のインデックス機能では、関数インデックスが追加されます。実際、仮想列機能はMySQL 5.7で導入され、MySQL 8.0の機能インデックスも仮想列に基づいて実装されています。上記のケースを MySQL 8.0 で実装すると以下のようになります。

2.1 関数インデックスの作成

MySQL 8.0 インスタンスに上記のテーブルとデータを作成し、create_time の関数インデックスを作成します。SQL は次のようになります。

mysql> alter table tb_function add key idx_create_time((date(create_time))); -- フィールドを囲む括弧に注意してください クエリは正常、0 行が影響を受けました (0.10 秒)
レコード: 0 重複: 0 警告: 0

2.2 時間によるクエリ

mysql> tb_function から * を選択します。 where date(create_time)='2020-07-01';
+----+--------------------+---------------------+
| ID | 名前 | 作成時刻 |
+----+--------------------+---------------------+
| 1 | anniuadaOAIFAPUHIA | 2020-07-01 12:00:00 |
| 2 | CWQSsar3qcssg | 2020-07-01 15:00:00 |
| 3 | vxfqrt2adafz | 2020-07-01 21:30:00 |
+----+--------------------+---------------------+
セット内の 3 行 (0.00 秒)

実行計画は以下のとおりです

mysql> explain select * from tb_function where date(create_time)='2020-07-01';
+----+--------------+-------------+-----------+-------+---------------------------------+--------+-------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+-----------+-------+---------------------------------+--------+-------+------+------+------+
| 1 | SIMPLE | tb_function | NULL | ref | idx_create_time | idx_create_time | 4 | const | 3 | 100.00 | NULL |
+----+--------------+-------------+-----------+-------+---------------------------------+--------+-------+------+------+------+
セットに 1 行、警告 1 件 (0.00 秒)

MySQL 8.0で対応する関数インデックスを作成した後、SQLの記述方法を変更せずに、クエリ列に対して対応する関数計算も実行できることがわかります。

MySQL 関数インデックスと MySQL 8.0 関数インデックスの最適化をテストできるシナリオは他にもあります。SQL の書き換えと最適化機能を向上させるために、試してみることをお勧めします。

上記はMySQL機能インデックスの最適化計画の詳細な内容です。MySQL機能インデックスと最適化計画の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQL最適化ソリューション: スロークエリログを有効にする
  • MySQL 選択最適化ソリューションに関する簡単な説明
  • MySQLクエリ最適化: 100万件のデータに対するテーブル最適化ソリューション
  • 大きなオフセットによる MySQL 制限ページングが遅い理由と最適化ソリューション
  • MySQLの大規模テーブル最適化ソリューションについての簡単な説明
  • MySQL 最適化ソリューション リファレンス
  • MySQLの一般的な最適化ソリューション

<<:  Vue プロジェクトはファイルダウンロードの進行状況バー機能を実装します

>>:  jsドラッグ効果の原理と実装

推薦する

MySQL 主キー ID を生成する方法 (自己増分、一意、不規則)

目次1. uuid関数を使用して、一意かつ不規則な主キーIDを生成します。 2. idの自動成長1....

Linux ドライバ開発でよく使われる関数 copy_from_user open read write の詳細な説明

目次Linux ドライバーの共通機能 (copy_from_user open read write...

Linux 上でプライベート Git サーバーを構築するための詳細なチュートリアル

1. サーバーのセットアップリモート リポジトリは実際にはローカル リポジトリと何ら変わりなく、純粋...

SQLシリアル番号取得コード例

この記事は主にSQLシリアル番号取得コード例を紹介します。記事ではサンプルコードを詳細に紹介しており...

MySQLのバージョンアップ方法を超詳しく解説

目次1. はじめに2. データベースをバックアップする3. オリジナルのMysqlをアンインストール...

Vue3 でパンくず関数コンポーネントをカプセル化するいくつかの方法

目次序文1. パンくずリストはなぜ必要なのでしょうか? 2. 一次包装1. 実装のアイデア2. コー...

ウェブページ印刷細線表+ページ印刷究極の戦略

最近、クライアントのために印刷していたとき、ページのヘッダーを印刷するのではなく、表の内容だけを印刷...

MySQL binlog_ignore_dbパラメータの具体的な使用法

序文:前の記事を読んだ後、binlog はデータベースで実行されたすべての DDL および DML ...

Vue3はフロントエンドのログを出力するためにaxiosインターセプターを使用する

目次1. はじめに2. axiosインターセプターを使用してフロントエンドログを出力する1. はじめ...

プロジェクトにaxiosをカプセル化する実際のプロセス

目次序文axiosカプセル化の利点パッケージのアイデア設定の優先順位axiosインスタンス構成1. ...

MySQL インデックス失敗の原理

目次1. インデックス失敗の理由2. インデックスの秩序が崩れる状況を見てみましょう。 - インデッ...

WeChatアプレットがシンプルな計算機機能を実装

この記事では、WeChatアプレットの計算機機能を実装するための具体的なコードを参考までに紹介します...

MySQL マルチテーブル共同クエリ操作例の分析

この記事では、MySQL のマルチテーブル共同クエリ操作について説明します。ご参考までに、詳細は以下...

MySQL パスワード変更方法の概要

MySQL 5.7 より前のバージョンのパスワードを変更する方法:方法1: SET PASSWORD...

ウェブページ作成時のHTMLタグの使用に注意してください

この記事では、Web ページの作成を学習するときに注意すべき HTML タグに関するいくつかの問題を...