MySQL 函数索引注意点

老叶茶馆 2023-09-13

163

今天看文章，看到一篇比较有意思的文章，给大家分享下

先放链接：Putting the Fun in MySQL Functional Indexes MySQL Functional Indexes, https://www.percona.com/blog/putting-the-fun-in-mysql-functional-indexes/

在本地创建如下表：

root@mysql3306.sock>[employees]>show create table emp1 \G
*************************** 1. row ***************************
       Table: emp1
Create Table: CREATE TABLE `emp1` (
`emp_no` int NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `ix_firstname` (`first_name`),
  KEY `ix_3` (`emp_no`,`first_name`),
  KEY `idx_t1` (`first_name`,`emp_no` DESC),
  KEY `idx_t5` (((`emp_no` + 1))),
  KEY `idx_emp1_n1` ((substr(`birth_date`,1,4))),
  KEY `idx_emp1_n2` ((month(`birth_date`)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

数据如下：

root@mysql3306.sock>[employees]>select * from emp1 limit 10 ;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)

先看下面两个例子：

root@mysql3306.sock>[employees]>desc select * from emp1 where emp_no=10002  limit 10 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp1  | NULL       | const | PRIMARY,ix_3  | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)


root@mysql3306.sock>[employees]>desc select * from emp1 where emp_no='10002'  limit 10 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp1  | NULL       | const | PRIMARY,ix_3  | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

如上所示两个都可以使用到了索引。

现在开始看函数式索引情况：

root@mysql3306.sock>[employees]>desc select * from emp1 where emp_no+1=10002  limit 10 ;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp1  | NULL       | ref  | idx_t5        | idx_t5 | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


root@mysql3306.sock>[employees]>desc select * from emp1 where emp_no+1='10002'  limit 10 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp1  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299202 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

上面两个例子一个使用了索引一个没有！

说明函数式索引严格按照左右两边必须样式一样才可以。

如下所示：

root@mysql3306.sock>[employees]>desc select * from emp1 where emp_no+1= cast( '10002' as unsigned )   limit 10 ;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | emp1  | NULL       | ref  | idx_t5        | idx_t5 | 8       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

那问题就来了，我们使用函数索引的时候，必须清楚函数的结果类型以便更好的使用它：

  KEY `idx_emp1_n1` ((substr(`birth_date`,1,4))),
  KEY `idx_emp1_n2` ((month(`birth_date`)))

我们再结合上面的两个索引说明下：

root@mysql3306.sock>[employees]>desc select emp1.* ,  substring(birth_date,1,4)  ,month(birth_date)  from emp1 where substring(birth_date,1,4)='1964' limit 10 ;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | emp1  | NULL       | ref  | idx_emp1_n1   | idx_emp1_n1 | 19      | const | 43460 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)


root@mysql3306.sock>[employees]>desc select emp1.* ,  substring(birth_date,1,4)  ,month(birth_date)  from emp1 where substring(birth_date,1,4)=1964 limit 10 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp1  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299202 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


root@mysql3306.sock>[employees]>desc select emp1.* ,  substring(birth_date,1,4)  ,month(birth_date)  from emp1 where month(birth_date)=9 limit 10 ;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | emp1  | NULL       | ref  | idx_emp1_n2   | idx_emp1_n2 | 5       | const | 44756 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)


root@mysql3306.sock>[employees]>desc select emp1.* ,  substring(birth_date,1,4)  ,month(birth_date)  from emp1 where month(birth_date)='9' limit 10 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp1  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299202 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

在上面的四个例子中

KEY `idx_emp1_n1` ((substr(`birth_date`,1,4)))

这个索引比较容易，是字符串。但是

KEY `idx_emp1_n2` ((month(`birth_date`)))

这个就比较难很难看出是数字还是字符串。

在此我提供如下方案，用hex函数：

root@mysql3306.sock>[employees]>select hex(emp_no+1)a ,emp_no+1 a1 ,
 hex( substring(birth_date,1,4) ) b,substring(birth_date,1,4) b1 
 ,hex( month(birth_date)) c,month(birth_date) c1  from emp1 limit 3 ;
+------+-------+----------+------+------+------+
| a    | a1    | b        | b1   | c    | c1   |
+------+-------+----------+------+------+------+
| 2712 | 10002 | 31393533 | 1953 | 9    |    9 |
| 2713 | 10003 | 31393634 | 1964 | 6    |    6 |
| 2714 | 10004 | 31393539 | 1959 | C    |   12 |
+------+-------+----------+------+------+------+
3 rows in set (0.00 sec)

从上面的结果中可以看出除了hex( substring(birth_date,1,4) ) 其他的都是基于16进制的数字：

2712
root@mysql3306.sock>[employees]>select 2*power(16,3) + 7*power(16,2)+ 1*power(16,1)+2*power(16,0) a ;
+-------+
| a     |
+-------+
| 10002 |
+-------+
1 row in set (0.00 sec)

我们可以通过hex函数来判断函数结果的类型，然后根据这个来判断我们想使用函数索引的时候是否加单引号还是去掉单引号。

全文完。

Enjoy MySQL ：）

《深入浅出MGR》视频课程

戳此小程序即可直达B站

https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0

文章推荐：

MySQL的match函数在sp中使用的BUG解析
MySQL 8.0.29 instant DDL 数据腐化问题分析
探索GreatADM：图形化部署MGR的全新体验
芬达：openEuler+GreatSQL+dbops玩转MGR
GreatSQL删除表分区特别慢的原因分析
MySQL对derived table的优化处理与使用限制
MySQL一次大量内存消耗的跟踪
分布式编译系统的搭建
MySQL Shell 8.0.32 for GreatSQL编译安装
MySQL运行时的可观测性
Myloader导入更快吗？并没有。。。

自打有了GIPKs，DBA和开发再也不用battle了
重现一条简单SQL的优化过程

想看更多技术好文，点个“在看”吧！

文章转载自老叶茶馆，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL 函数索引注意点

评论