MySQL functional index optimization solution

MySQL functional index optimization solution

When using MySQL, many developers often perform function calculations on some columns, which results in the inability to use indexes. When the amount of data is large, query efficiency is low. To address this situation, this article optimizes MySQL 5.7 and MySQL 8.0 in different ways.

1. MySQL 5.7

MySQL 5.7 does not support function indexes, so when you encounter a function index, you need to modify it. Otherwise, even if there is an index on the queried field, the index cannot be used during execution and the full table scan will be performed. The query time for tables with large amounts of data will be longer. The specific cases are as follows:

1.1 Create test tables and data

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 an index

Create an index on the create_time field

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 Query by time

Query all records created on 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)

The execution plan is as follows:

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)

It can be seen from the execution plan that a full scan was performed

1.4 Optimization

Since MySQL 5.7 does not support functional indexes, you need to modify the SQL writing to implement indexing (or use virtual columns). The above SQL can be modified to

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)

The execution plan is as follows:

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)

It can be seen that after the modification, the index is used.

2. MySQL 8.0

The index feature of MySQL 8.0 adds functional index. In fact, the virtual column function was introduced in MySQL 5.7, and the functional index of MySQL 8.0 is also implemented based on virtual columns. The implementation of the above case in MySQL 8.0 is described as follows.

2.1 Creating a Functional Index

Create the above table and data on the MySQL 8.0 instance, and then create a function index of create_time. The SQL is as follows:

mysql> alter table tb_function add key idx_create_time((date(create_time))); -- Note the brackets around the fields Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

2.2 Query by time

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)

The execution plan is as follows

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)

It can be seen that after creating the corresponding functional index in MySQL 8.0, the corresponding function calculation can also be performed on the query column without changing the SQL writing method.

There are more scenarios that can be tested for the optimization of MySQL function indexes and MySQL 8.0 function indexes. It is recommended that you give it a try to improve your SQL rewriting and optimization capabilities.

The above is the detailed content of the optimization plan for MySQL functional index. For more information about MySQL functional index and optimization plan, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • MySQL optimization solution: enable slow query log
  • A brief discussion on MySQL select optimization solution
  • MySQL query optimization: a table optimization solution for 1 million data
  • Reasons and optimization solutions for slow MySQL limit paging with large offsets
  • A brief discussion on MySQL large table optimization solution
  • MySQL Optimization Solution Reference
  • Several common optimization solutions for MySQL

<<:  Vue project implements file download progress bar function

>>:  Detailed explanation of several error handling when Nginx fails to start

Recommend

Using CSS to implement loading animation of Android system

There are two common loading icons on the web, on...

Docker /var/lib/docker/aufs/mnt directory cleaning method

The company's service uses docker, and the di...

Sample code for implementing mobile expansion and collapse effects with pure CSS3

This article introduces the sample code for imple...

Summary of Binlog usage of MySQL database (must read)

I won't go into details about how important b...

Vue handwriting loading animation project

When the page is not responding, displaying the l...

Summary on Positioning in CSS

There are four types of positioning in CSS, which...

Mobile browser Viewport parameters (web front-end design)

Mobile browsers place web pages in a virtual "...

Mac installation mysqlclient process analysis

Try installing via pip in a virtual environment: ...

Dynamically add tables in HTML_PowerNode Java Academy

Without further ado, I will post the code for you...

Problems and solutions encountered when using v-model to two-way bind the values ​​of parent-child components in Vue

Table of contents Scenario Try to solve solve Sce...

Solution for installing opencv 3.2.0 in Ubuntu 18.04

Download opencv.zip Install the dependencies ahea...

Mysql implementation of full-text search and keyword scoring method example

1. Introduction Today a colleague asked me how to...

Detailed usage of docker-maven-plugin

Table of contents Docker-Maven-Plugin Maven plugi...

5 common scenarios and examples of JavaScript destructuring assignment

Table of contents Preface 1. Extract data 2. Alia...

Setting up shared folders in Ubuntu virtual machine of VMWare14.0.0

This is my first blog post. Due to time constrain...