MySQL 函數(shù)索引有哪些優(yōu)化方案

發(fā)布時間：2020-11-02 16:51:31 來源：億速云閱讀：121 作者：Leah 欄目：開發(fā)技術(shù)

這篇文章將為大家詳細講解有關(guān)MySQL 函數(shù)索引有哪些優(yōu)化方案，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

1、 MySQL5.7

MySQL5.7版本中不支持函數(shù)索引，因此遇到函數(shù)索引的時候需要進行修改，否則即使查詢的字段上有索引，執(zhí)行時也無法使用索引而進行全表掃描，數(shù)據(jù)量大的表查詢時間會比較長。具體案例如下：

1.1 創(chuàng)建測試表及數(shù)據(jù)

mysql> use testdb;
Database changed
mysql> create table  tb_function(id int primary key auto_increment,name varchar(100),create_time datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb_function(name,creatE_time) values('anniuadaOAIFAPUHIA','2020-07-01 12:00:00');
Query OK, 1 row affected (0.02 sec)

mysql> insert into tb_function(name,creatE_time) values('CWQSsar3qcssg','2020-07-01 15:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb_function(name,creatE_time) values('vxfqrt2adafz','2020-07-01 21:30:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb_function(name,creatE_time) values('etxzwrwbdhegqgaheqhag','2020-07-02 01:30:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb_function(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 03:30:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_function(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 07:32:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_function(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 10:32:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_function(name,creatE_time) values('tuilklmdadq','2020-07-02 15:32:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_function(name,creatE_time) values('wesv2wqdshehq','2020-07-02 20:32:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_function(name,creatE_time) values('89yoijnlkwr1','2020-07-03 02:56:00');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_function(name,creatE_time) values('olj;nsaaq','2020-07-03 08:41:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb_function(name,creatE_time) values('ygo;jkdsaq','2020-07-03 16:20:00'); 
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_function;
+----+-----------------------+---------------------+
| id | name         | create_time     |
+----+-----------------------+---------------------+
| 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 rows in set (0.00 sec)

1.2 創(chuàng)建索引

在create_time字段上創(chuàng)建索引

mysql> alter table tb_function add key idx_create_time(create_time);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

1.3 按時間查詢

查詢創(chuàng)建時間是2020-07-01那天的所有記錄

mysql> select * from tb_function where  date(create_time)='2020-07-01';
+----+--------------------+---------------------+
| id | name        | create_time     |
+----+--------------------+---------------------+
| 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 rows in set (0.00 sec)

執(zhí)行計劃如下：

mysql> explain select * from tb_function where  date(create_time)='2020-07-01';
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra    |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE   | tb_function | NULL    | ALL | NULL     | NULL | NULL  | NULL |  12 |  100.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

執(zhí)行計劃中可以看出是進行了全面掃描

1.4 優(yōu)化

因MySQL5.7不支持函數(shù)索引，所以需要修改SQL寫法來實現(xiàn)走索引（或者使用虛擬列的方式），上述SQL可以修改為

mysql> select * from tb_function where  create_time>='2020-07-01' and create_time<date_add('2020-07-01',INTERVAL 1 day);
+----+--------------------+---------------------+
| id | name        | create_time     |
+----+--------------------+---------------------+
| 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 rows in set (0.00 sec)

執(zhí)行計劃如下：

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 | table    | partitions | type | possible_keys  | key       | key_len | ref | rows | filtered | Extra         |
+----+-------------+-------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE   | tb_function | NULL    | range | idx_create_time | idx_create_time | 6    | NULL |  3 |  100.00 | Using index condition |
+----+-------------+-------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

可見，修改后，使用了索引。

2、MySQL8.0

MySQL8.0的索引特性增加了函數(shù)索引。其實MySQL5.7中推出了虛擬列的功能，而MySQL8.0的函數(shù)索引也是依據(jù)虛擬列來實現(xiàn)的。將上述的案例在MySQL8.0中實現(xiàn)情況如下文所述。

2.1 創(chuàng)建函數(shù)索引

在將上述的表及數(shù)據(jù)在MySQL8.0的實例上創(chuàng)建，然后創(chuàng)建create_time的函數(shù)索引，SQL如下

mysql> alter table tb_function add key idx_create_time((date(create_time))); --  注意里面字段的括號
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

2.2 按時間查詢

mysql> select * from tb_function where  date(create_time)='2020-07-01';
+----+--------------------+---------------------+
| id | name        | create_time     |
+----+--------------------+---------------------+
| 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 rows in set (0.00 sec)

執(zhí)行計劃如下

mysql> explain select * from tb_function where  date(create_time)='2020-07-01';
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys  | key       | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE   | tb_function | NULL    | ref | idx_create_time | idx_create_time | 4    | const |  3 |  100.00 | NULL |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可見，在MySQL8.0 創(chuàng)建對應(yīng)的函數(shù)索引后，不改變SQL寫法的前提下，查詢的列上進行對應(yīng)的函數(shù)計算后也可以走索引。

關(guān)于MySQL函數(shù)索引的優(yōu)化及MySQL8.0函數(shù)索引還可以有更多的場景進行測試，建議大家多動手試試，提高SQL改寫及優(yōu)化的能力。

關(guān)于MySQL 函數(shù)索引有哪些優(yōu)化方案就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

向AI問一下細節(jié)

MySQL 函數(shù)索引有哪些優(yōu)化方案

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽