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の他の関連記事に注目してください。

以下もご興味があるかもしれません: