MySQL 函数索引的优化方案

很多开发人员在使用MySQL时经常会在部分列上进行函数计算等，导致无法走索引，在数据量大的时候，查询效率低下。针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化。

1、 MySQL5.7

MySQL5.7版本中不支持函数索引，因此 遇到函数索引的时候需要进行修改，否则即使查询的字段上有索引，执行时也无法使用索引而进行全表扫描，数据量大的表查询时间会比较长。具体案例如下：

1.1 创建测试表及数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

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 创建索引

在create_time字段上创建索引

?

1 2 3

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 按时间查询

查询创建时间是2020-07-01那天的所有记录

?

1 2 3 4 5 6 7 8 9

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)

执行计划如下：

?

1 2 3 4 5 6 7

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)

执行计划中可以看出是进行了全面扫描

1.4 优化

因MySQL5.7不支持函数索引，所以需要修改SQL写法来实现走索引（或者使用虚拟列的方式），上述SQL可以修改为

?

1 2 3 4 5 6 7 8 9

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)

执行计划如下：

?

1 2 3 4 5 6 7

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的索引特性增加了函数索引。其实MySQL5.7中推出了虚拟列的功能，而MySQL8.0的函数索引也是依据虚拟列来实现的。将上述的案例在MySQL8.0中实现情况如下文所述。

2.1 创建函数索引

在将上述的表及数据在MySQL8.0的实例上创建，然后创建create_time的函数索引，SQL如下

?

1 2 3

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 按时间查询

?

1 2 3 4 5 6 7 8 9

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)

执行计划如下

?

1 2 3 4 5 6 7

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 创建对应的函数索引后，不改变SQL写法的前提下，查询的列上进行对应的函数计算后也可以走索引。

关于MySQL函数索引的优化及MySQL8.0函数索引还可以有更多的场景进行测试，建议大家多动手试试，提高SQL改写及优化的能力。

以上就是MySQL 函数索引的优化方案的详细内容，更多关于MySQL 函数索引及优化方案的资料请关注服务器之家其它相关文章！

原文链接：https://www.cnblogs.com/gjc592/p/13233377.html