How to use functional Index in MySQL5.7
This article to share with you is about how to use the function index in MySQL 5.7, Xiaobian think it is very practical, so share it with you to learn, I hope you can gain something after reading this article, not much to say, follow Xiaobian to see it.
In versions before MySQL 5.7, functional indexes cannot be used for indexes, and virtual columns are not supported. The following SQL execution time returns to the full table scan:
select * from t1 where mod(mode_id,8)=1
MySQL 5.7 supports virtual columns and can use functional indexes.
Determine database version:
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.18-log |
+------------+
View table structure:
mysql> show create table t_func_mod \G
*************************** 1. row ***************************
Table: t_func_mod
Create Table: CREATE TABLE `t_func_mod` (
`id` int(11) NOT NULL,
`mod_id` int(11) GENERATED ALWAYS AS ((`id` % 8)) VIRTUAL,
PRIMARY KEY (`id`),
KEY `idx_mod_id` (`mod_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Insert test data:
mysql> insert into t_func_mod values(1,default);
Query OK, 1 row affected (0.08 sec)
mysql> insert into t_func_mod values(2,default);
Query OK, 1 row affected (0.06 sec)
mysql> insert into t_func_mod values(3,default);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_func_mod values(4,default);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_func_mod values(5,default);
Query OK, 1 row affected (0.04 sec)
mysql> insert into t_func_mod values(6,default);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_func_mod values(7,default);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_func_mod values(8,default);
Query OK, 1 row affected (0.00 sec)
Note: When inserting data, use default for virtual columns, otherwise an error will be reported.
View Test Sheet Data:
mysql> select * from t_func_mod;
+----+--------+
| id | mod_id |
+----+--------+
| 8 | 0 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
+----+--------+
Check the execution plan through explain, and the index has been used:
mysql> explain select * from t_func_mod where mod_id=5;
----+-------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t_func_mod | NULL | ref | idx_mod_id | idx_mod_id | 5 | const | 1 | 100.00 | Using index |
The above is how to use the function index in MySQL 5.7. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.
