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ドラッグ効果の原理と実装

推薦する

HTML 画像 img にハイパーリンクを追加した後の醜い青い境界線の問題を解決する

HTML画像にハイパーリンクを追加すると醜い青い枠線が表示される次のように:解決: CSS スタイル...

WIN10 システムと Docker 内部コンテナ IP 間の通信方法

1. Windows 版の Docker をインストールしたら、Docker クイックスタート ター...

HTMLにスクリプトを追加する2つの方法と注意点

HTML に <script> スクリプトを追加する方法: 1. HTMLにJavaSc...

Linux で cmake を使用して MySQL をコンパイルおよびインストールするための詳細なチュートリアル

1. cmakeをインストールする1. cmakeの圧縮パッケージを解凍する [root@mysql...

WeChatアプレットがジグソーパズルゲームを実装

この記事では、WeChatアプレットでジグソーパズルゲームを実装するための具体的なコードを参考までに...

Linux で xargs コマンドを使用する詳細なチュートリアル

みなさんこんにちは。私は梁旭です。 Linux を使用しているときに、いくつかのコマンドを連結する必...

Nginx で Angular プロジェクトを展開する際の落とし穴

コンパイル後にAngularプロジェクトをNginxにデプロイする方法をオンラインで検索すると、ほと...

HTMLページ作成に関する私の経験の簡単な要約

Word of Mouth に入社して 3 ~ 4 か月が経ちました。仕事の中で一番の収穫は、ビジュ...

CentOS のファイルと権限の基本操作チュートリアル

序文始める前に、ファイル属性とファイル属性を変更する方法について簡単に理解しておく必要があります。 ...

mysql 5.6.21 のインストールと設定の詳細な手順

1. 概要MySQL バージョン: 5.6.21ダウンロードアドレス: https://dev.my...

優秀なウェブ開発者が開発スキルを向上させるために知っておくべき10のこと

「開発とは、単にコードを書くだけではない」というのは、3EV の Dan Frost 氏の言葉です。...

Vuex環境の詳細な説明

目次Vuex環境を構築する要約するVuex環境を構築するsrcディレクトリにフォルダstoreを作成...

JavaScript で 24 以上の配列メソッドを手動で実装する

目次1. トラバーサルクラス1. 各2. 地図3. すべての4. いくつか5. フィルター6. 減ら...

CSS 擬似要素::マーカーの詳細な説明

この記事では、CSS ::markerの興味深い疑似要素を紹介します。これを使用すると、テキスト番号...

Webフロントエンドのパフォーマンス最適化

ウェブフロントエンド最適化のベストプラクティス: コンテンツWebフロントエンド最適化のベストプラク...