Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use Generated Columns + index instead of functional index in MySQL". The content in the article is simple and clear, and it is easy to learn and understand. Please follow Xiaobian's train of thought to study and learn "how to use Generated Columns + index instead of functional index in MySQL".

Recently, a project has encountered a performance problem, and I share the optimization method here (this solution is not invented by anyone, but is the official alternative for mysql to implement functional indexes).

The problem is this: we need to take the corresponding item from a JSON field as a condition for the where clause, as follows:

CREATE TABLE `xxxxx` (`id`varchar (96) DEFAULT NULL, `gid` varchar (96) DEFAULT NULL, `user_ id` varchar (900) DEFAULT NULL, `order_ no` varchar (96) DEFAULT NULL, `request_ time` varchar (96) DEFAULT NULL, `code` varchar (96) DEFAULT NULL, `msg` varchar (96) DEFAULT NULL, `request_ conetent` text, `request_ response` text, `product_ code` varchar (96) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8##SQL is as follows: select id from table_name where json_extract (`request_ conetent`,'$.vclN') +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -+-+ | 1 | SIMPLE | xxxxxx | NULL | ALL | NULL | 85 | 100.00 | Using where | +-+-+ -+

Everyone knows that MYSQL does not support functional indexes, so how to optimize it?

Although MYSQL does not support functional indexes, there is an alternative: Generated Columns + index instead of functional indexes.

# add a computed column to the request_record table and create a functional index on the column

Alter table table_name add column carno varchar (100) generated always as (json_extract (`request_ conetent`,'$.vclN') VIRTUAL,add key idx_carno (carno)

# the table structure is as follows:

CREATE TABLE `xxxxxx` (

`id`varchar (96) DEFAULT NULL

`gid` varchar (96) DEFAULT NULL

`user_ id` varchar (900) DEFAULT NULL

`order_ no` varchar (96) DEFAULT NULL

`request_ time`varchar (96) DEFAULT NULL

`code` varchar (96) DEFAULT NULL

`msg` varchar (96) DEFAULT NULL

`request_ conetent` text

`request_ response` text

`product_ code` varchar (600) DEFAULT NULL

`carno` varchar (100) GENERATED ALWAYS AS (json_extract (`request_ conetent`,'$vclN') VIRTUAL

KEY `idx_ carno` (`carno`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

# query with newly added computed columns (carno)

Mysql > explain select id from request_record where carno='xxxxxx'

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | SIMPLE | xxxxxxx | NULL | ref | idx_carno | idx_carno | 303 | const | 1 | 100.00 | NULL |

+-- +

As can be seen from the implementation plan, the index has been taken.

Thank you for your reading, the above is the content of "how to use Generated Columns + index in MySQL instead of functional index". After the study of this article, I believe you have a deeper understanding of how to use Generated Columns + index instead of functional index in MySQL. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.