回城传送–》《32天SQL筑基》

文章目录

零、前言

今天是学习 SQL 打卡的第 14 天，每天我会提供一篇文章供群成员阅读（ 不需要订阅付钱 ）。

希望大家先自己思考，如果实在没有想法，再看下面的解题思路，自己再实现一遍。在小虚竹JAVA社区 中对应的 【打卡贴】打卡，今天的任务就算完成了，养成每天学习打卡的好习惯。

虚竹哥会组织大家一起学习同一篇文章，所以有什么问题都可以在群里问，群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。

我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 SQL 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：SQL进阶-索引的函数索引

一、练习题目

题目链接

难度

SQL进阶-索引的函数索引

★★★☆☆

二、SQL思路

SQL进阶-索引的函数索引

【第14天】SQL进阶-索引的函数索引（SQL 小虚竹)_mysql


【第14天】SQL进阶-索引的函数索引（SQL 小虚竹)_数据_02

初始化数据

drop table if  exists `question_practice_detail`;

CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` TIMESTAMP NOT NULL
);

INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03 10:00:00');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09 11:00:00');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15 12:00:00');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13 13:00:00');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13 14:00:00');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14 15:00:00');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15 16:00:00');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09 17:00:00');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15 18:00:00');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13 19:00:00');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13 20:00:00');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14 21:00:00');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15 22:00:00');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16 23:00:00');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18 01:00:00');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13 02:00:00');
INSERT INTO question_practice_detail VALUES(17,6543,111,'right','2022-08-13 02:00:00');

解法

要求处理：

  • 四个选项，选择一个正确答案

分析：

  • 这也是一个比较新的知识点，mysql8.x（从MySQL 8.0.13版本开始支持）开始支持函数索引。
  • 还记得前面虚竹哥传授的索引失效口诀吗？再啰嗦一遍：模型数或运最快
    其中的数 代表查询条件里对索引列使用函数，索引会失效
  • 实战演练一下

创建普通索引：
CREATE INDEX idx_date ON question_practice_detail(date);

explain select * 
from question_practice_detail
where date<='2021-08-13 23:59:59' and date>="2021-08-13 00:00:00"

【第14天】SQL进阶-索引的函数索引（SQL 小虚竹)_sql_03


使用date()函数导致索引失效

explain select * 
from question_practice_detail
where date(date)='2021-08-13'

【第14天】SQL进阶-索引的函数索引（SQL 小虚竹)_sql_04

所以5.7以及之前的版本函数会导致索引失效，这是完全没问题的。
但8.x后开始支持函数索引，这时再说索引会失效，是有前提的。如果虚竹哥是面试官，会故意挖坑，考虑对新特性的掌握和索引的掌握情况。

创建函数索引：
CREATE INDEX idx_fun_date ON question_practice_detail((date(date)));

【第14天】SQL进阶-索引的函数索引（SQL 小虚竹)_java_05

  • 再查看索引是否有生效
explain select * 
from question_practice_detail
where date(date)='2021-08-13'

【第14天】SQL进阶-索引的函数索引（SQL 小虚竹)_sql_06

结果表明，索引生效了。
函数索引yyds

三、扩展

现在行业里大部分还在使用5.7的版本，对于索引列使用函数会导致索引失效问题，有没有解决方案呢？
既然虚竹哥提出这个问题，那肯定是有解决方案的。答案就是使用虚拟列

mysql5.7支持2种虚拟列virtual columns 和 stored columns 。

  • virtual：只是在读行的时候计算结果，但在物理上是不存储，因此不占存储空间，且仅支持在InnoDB引擎上建二级索引。
  • stored：是当行数据进行插入或更新时计算并存储的，是需要占用物理空间的，支持在MyISAM和InnoDB引擎创建索引。
  • mysql5.7 默认的虚拟列类型为virtual columns
  • 使用虚拟列注意事项
  • 衍生列的定义可以修改，但virtual和stored之间不能相互转换，必要时需要删除重建
  • 虚拟列字段只读，不支持 INSRET 和 UPDATE
  • 只能引用本表的非 generated column 字段，不可以引用其它表的字段
  • 使用的表达式和操作符必须是 Immutable 属性，比如不能使用 CONNECTION_ID(), CURRENT_USER(), NOW()
  • 可以将已存在的普通列转化为stored类型的衍生列，但virtual类型不行；同样的，可以将stored类型的衍生列转化为普通列，但virtual类型的不行
  • 虚拟列定义不允许使用自增 (AUTO_INCREMENT)，也不允许使用自增基列
  • 虚拟列允许修改表达式，但不允许修改存储方式（只能通过删除重新创建来修改）
  • 如果虚拟列用作索引，会有一个缺点值会存储两次。一次用作虚拟列的值，一次用作索引中的值
  • 虚拟列的使用场景
  • 虚拟列可以简化和统一查询，将复杂条件定义为生成的列，可以在查询时直接使用虚拟列（代替视图）
  • 存储虚拟列可以用作实例化缓存，以用于动态计算成本高昂的复杂条件
  • 虚拟列可以模拟功能索引，并且可以使用索引，这对与无法直接使用索引的列（JSON 列）非常有用。
  • 创建虚拟列的语法：

ALTER TABLE 表名称 add column 虚拟列名称 虚拟列类型 [GENERATED ALWAYS] as (表达式) [VIRTUAL | STORED];

实战演练：
对 question_practice_detail 表创建虚拟列 date_year

alter table question_practice_detail add column date_month int(2) GENERATED ALWAYS as (month(date)) STORED;

对虚拟列增加普通索引

CREATE INDEX idx_date_month ON question_practice_detail(date_month);

  • 查看下这时候表的索引情况

SHOW INDEX FROM question_practice_detail;

【第14天】SQL进阶-索引的函数索引（SQL 小虚竹)_java_07

explain select *
from question_practice_detail
where date_month = 8

【第14天】SQL进阶-索引的函数索引（SQL 小虚竹)_数据库_08

  • 删除虚拟列的语法：

alter table 表名称 drop column 虚拟列名称
alter table question_practice_detail drop column date_year;

答案

嗯，这题的答案选。。评论区大声告诉虚竹哥。

四、参考：

如何利用mysql5.7提供的虚拟列来提高查询效率

我是虚竹哥，我们明天见~