【原创】MySQL5.7 虚拟列实现表达式索引

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

MySQL自古以来就不提供函数索引这么复杂的功能。那怎么在MySQL里面实现这样的功能呢? 我们先来看看函数索引的概念。函数索引,也可称为表达式索引,也就是基于字段以特定函数(表达式)建立索引来提升查询性能之需。函数索引的优势在于更加精确的获取所需要的数据。


MySQL 5.7提供了一个新的特性,虚拟列,可以很完美的解决这个问题。

在介绍虚拟列之前,我们来看看在MySQL里面普通索引的范例。

示例表结构:

1
2
CREATE  TABLE  t1 (id  INT  ,rank  INT , log_time DATETIME, nickname  VARCHAR (64)) ENGINE INNODB;
ALTER  TABLE  t1  ADD  PRIMARY  KEY  (id),  ADD  KEY  idx_rank (rank), ADD  KEY  idx_log_time (log_time);


示例表数据量,这里我增加了5000条记录:

1
2
3
4
5
6
7
mysql>  select  count (*)  from  t1;
+ ----------+
count (*) |
+ ----------+
|     5000 |
+ ----------+
1 row  in  set  (0.00 sec)


假设我们来检索2015年4月9号的数据。(结果是有两条记录,id 分别为95和3423。)

1
2
3
4
5
6
7
8
9
10
11
12
mysql>  SELECT  FROM  t1  WHERE  DATE (log_time) =  '2015-04-09' \G
*************************** 1. row ***************************
       id: 95
     rank: 24
log_time: 2015-04-09 05:53:13
nickname: test
*************************** 2. row ***************************
       id: 3423
     rank: 42
log_time: 2015-04-09 02:55:38
nickname: test
rows  in  set  (0.01 sec)

下来我们看看这条语句的查询计划。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain  SELECT  FROM  t1  WHERE  DATE (log_time) =  '2015-04-09' \G
*************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table : t1
    partitions:  NULL
          type:  ALL
possible_keys:  NULL
           key NULL
       key_len:  NULL
           ref:  NULL
          rows : 5000
      filtered: 100.00
         Extra: Using  where
1 row  in  set , 1 warning (0.00 sec)


我们发现TYPE是ALL,扫描的函数是5000,也就是说这条语句进行了一个全表扫描。 虽然给字段log_time 加了索引,但是没有用到,那这个时候怎么办?

在MySQL里面一般这样修改:

1
2
3
4
5
6
7
8
9
10
11
12
mysql>  SELECT  FROM  t1  WHERE  log_time >=  '2015-04-09 00:00:00'  AND  log_time <= '2015-04-10 00:00:00' \G
*************************** 1. row ***************************
       id: 3423
     rank: 42
log_time: 2015-04-09 02:55:38
nickname: test
*************************** 2. row ***************************
       id: 95
     rank: 24
log_time: 2015-04-09 05:53:13
nickname: test
rows  in  set  (0.00 sec)


通过查询结果,发现结果集一致,那再来看看查询计划


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain  SELECT  FROM  t1  WHERE  log_time >=  '2015-04-09 00:00:00'  AND  log_time <=  '2015-04-10 00:00:00' \G
*************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table : t1
    partitions:  NULL
          type: range
possible_keys: idx_log_time
           key : idx_log_time
       key_len: 6
           ref:  NULL
          rows : 2
      filtered: 100.00
         Extra: Using  index  condition
1 row  in  set , 1 warning (0.00 sec)


可以看到这条修改过的语句很好的利用到了idx_log_time这条索引。


那好,这个是之前在MySQL 5.6以及之前的旧版本解决方法,随着MySQL 5.7的发布,虚拟列的出现让这个问题更加简单。

 现在修改下之前的表结构:

1
ALTER  TABLE  t1  ADD  COLUMN  log_date   DATE  AS  ( DATE (log_Time)) stored,  ADD  KEY  idx_log_date (log_date);

这样,增加了一新列,用来存放date(log_time)这个表达式,并且给他加了一列索引。


那么,之前的语句就变成如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql>  SELECT  FROM  t1  WHERE  log_date =  '2015-04-09' \G
*************************** 1. row ***************************
       id: 95
     rank: 24
log_time: 2015-04-09 05:53:13
nickname: test
log_date: 2015-04-09
*************************** 2. row ***************************
       id: 3423
     rank: 42
log_time: 2015-04-09 02:55:38
nickname: test
log_date: 2015-04-09
rows  in  set  (0.00 sec)

执行后结果集和之前的一致。


我们来看看查询计划,发现很好的利用了idx_log_date索引列。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain   SELECT  FROM  t1  WHERE  log_date =  '2015-04-09' \G
*************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table : t1
    partitions:  NULL
          type: ref
possible_keys: idx_log_date
           key : idx_log_date
       key_len: 4
           ref: const
          rows : 2
      filtered: 100.00
         Extra:  NULL
1 row  in  set , 1 warning (0.00 sec)


通过以上介绍,我们看到虚拟列实现起来相对之前的方法来的容易的多。但是这里笔者还是得说上几句。

函数索引的用法以及SQL语句虽然写起来简单,但是在大部分场合下,只能说不得已而为之,是一种设计上的缺陷,后期增加了运维人员的运维难度以及繁琐度。这也就是为什么MySQL 直到5.7才推出了这项类似的功能的原因。






本文转自 david_yeung 51CTO博客,原文链接:http://blog.51cto.com/yueliangdao0608/1712423,如需转载请自行联系原作者
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
344
分享
相关文章
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
399 66
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
574 9
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
310 80
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
MySQL原理简介—9.MySQL索引原理
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
MySQL:CTE 通用表达式
CTE(通用表表达式)为处理复杂查询提供了强大的工具。通过普通CTE,可以简化查询逻辑,提高可读性;通过递归CTE,可以优雅地处理层级结构数据。掌握CTE的使用,对于提升SQL查询能力和优化数据库操作有着重要意义。希望本文能帮助你更好地理解和使用MySQL中的CTE,提高工作效率和代码质量。
23 7
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
129 22
 MySQL秘籍之索引与查询优化实战指南
浅入浅出——MySQL索引
本文介绍了数据库索引的概念和各种索引结构,如哈希表、B+树、InnoDB引擎的索引运作原理等。还分享了覆盖索引、联合索引、最左前缀原则等优化技巧,以及如何避免索引误用,提高数据库性能。