欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL 函数索引的优化方案

程序员文章站 2022-03-21 12:54:43
很多开发人员在使用mysql时经常会在部分列上进行函数计算等,导致无法走索引,在数据量大的时候,查询效率低下。针对此种情况本文从mysql5.7 及mysql8.0中分别进行不同方式的优化。1、 my...

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

1、 mysql5.7

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

1.1 创建测试表及数据

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

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那天的所有记录

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)

执行计划如下:

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可以修改为

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)

执行计划如下:

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如下

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)

执行计划如下

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 函数索引及优化方案的资料请关注其它相关文章!