Mysql知识学习

MySQL 8.0 索引特性1-函数索引

PHPer 2020-04-10 1582 0 0

MySQL 8.0 索引特性1-函数索引

函数索引顾名思义就是加给字段加了函数的索引,这里的函数也可以是表达式。所以也叫表达式索引。MySQL 5.7 推出了虚拟列的功能,MySQL8.0的函数索引内部其实也是依据虚拟列来实现的。

我们考虑以下几种场景:

1.对比日期部分的过滤条件

SELECT ...
FROM tb1
WHERE date(time_field1) = current_date;
	

2.两字段做计算

SELECT ...
FROM tb1
WHERE field2 + field3 = 5;
	

3.求某个字段中间某子串

SELECT ...
FROM tb1
WHERE substr(field4, 5, 9) = 'actionsky';
	

4.求某个字段末尾某子串

SELECT ...
FROM tb1
WHERE RIGHT(field4, 9) = 'actionsky';
	

5.求JSON格式的VALUE

SELECT ...
FROM tb1
WHERE CAST(field4 ->> '$.name' AS CHAR(30)) = 'actionsky';
	

以上五个场景如果不用函数索引,改写起来难易不同。不过都要做相关修改,不是过滤条件修正就是表结构变更添加冗余字段加额外索引。

比如第1个场景改写为,

SELECT ...
FROM tb1
WHERE time_field1 >= concat(current_date, ' 00:00:00')
    AND time_field1 <= concat(current_date, '23:59:59');
	

再比如第4个场景的改写,由于是求最末尾的子串,只能添加一个新的冗余字段,并且做相关的计划任务来一定频率的异步更新或者添加触发器来实时更新此字段值

SELECT ...
FROM tb1
WHERE field4_suffix = 'actionsky';
	

那我们看到,改写也可以实现,不过这样的SQL就没有标准化而言,后期不能平滑的迁移了。

MySQL 8.0 推出来了函数索引让这些变得相对容易许多。不过函数索引也有自己的缺陷,就是写法很固定,必须要严格按照定义的函数来写,不然优化器不知所措。

我们来把上面那些场景实例化。示例表结构,

5ed85bafca_normal.png

总记录数

mysql> SELECT COUNT(*)
FROM t_func;
+----------+
| count(*) |
+----------+
|    16384 |
+----------+
1 row in set (0.01 sec)
	

我们把上面几个场景的索引全加上

mysql > ALTER TABLE t_func ADD INDEX idx_log_time ( ( date( log_time ) ) ),
ADD INDEX idx_u1 ( ( rank1 + rank2 ) ),
ADD INDEX idx_suffix_str3 ( ( RIGHT ( str3, 9 ) ) ),
ADD INDEX idx_substr_str1 ( ( substr( str1, 5, 9 ) ) ),
ADD INDEX idx_str2 ( ( CAST( str2 ->> '$.name' AS CHAR ( 9 ) ) ) );
QUERY OK,
0 rows affected ( 1.13 sec ) Records : 0 Duplicates : 0 WARNINGS : 0
	

我们再看下表结构, 发现好几个已经被转换为系统自己的写法了

04f3818a8f_normal.png

MySQL 8.0 还有一个特性,就是可以把系统隐藏的列显示出来。我们用show extened 列出函数索引创建的虚拟列,

d2b3f2da86_normal.png

上面5个随机字符串列名为函数索引隐式创建的虚拟COLUMNS。

我们先来看看场景2,两个整形字段的相加,

mysql> SELECT COUNT(*)
FROM t_func
WHERE rank1 + rank2 = 121;
+----------+
| count(*) |
+----------+
|      878 |
+----------+
1 row in set (0.00 sec)
	

看下执行计划,用到了idx_u1函数索引,

mysql> explain SELECT COUNT(*)
FROM t_func
WHERE rank1 + rank2 = 121\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_func
   partitions: NULL
         type: ref
possible_keys: idx_u1
          key: idx_u1
      key_len: 9
          ref: const
         rows: 878
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
	

那如果我们稍微改下这个SQL的执行计划,发现此时不能用到函数索引,变为全表扫描了,所以要严格按照函数索引的定义来写SQL ...

立即注册

更新于:2020-04-10 21:20:21
    您需要登录后才可以评论。 立即注册
    相关内容

    搜索引擎优化(SEO)相关整理

    为什么百度手机搜索和电脑搜索排名不一样

    百度移动端和PC端蜘蛛的区别

    百度SEO,PC端与移动端排名,为什么不一致?

    百度指数关键词整理

    百度、搜狗、360、谷歌、Bing、神马、头条搜索-各大搜索引擎收录提交入口(...

    Robots.txt详解

    您的站点地图或站点地图索引文件未能正确声明命名空间

    php的curl和php正则获取网站在爱站查询的百度权重

    解决Bing不收录网站的问题

    mysql 中文全文索引模糊查询和like模糊查询的速度对比

    【该文章就是坑】mysql使用中文的全文索引(注意只有5.7.6以上的版本支持)

    MYSQL8.0全文索引使用​

    excel 列索引(数字)转列名-修正亲测版20200414

    推荐内容

    分享几个好用的bt搜索bt资源下载网址网站

    影视电影剧集动漫综艺bt资源在线播放网址网站推荐分享

    全网最新bt磁力搜索引擎bt资源bt网站网址分享

    怎样使用V2Ray代理和SSTap玩如魔兽世界/绝地求生/LOL台服/战地3/黑色沙漠/彩...

    sstap游戏代理教程 从此玩如魔兽世界/绝地求生/LOL台服/战地3/黑色沙漠/彩虹六...

    人气美女女优百度指数排行榜(波多野结衣,苍井空,三上悠亚,深田咏美,桥本有菜,北条...

    最强人造人深田咏美,业界的社交女王

    人生起起落落的三上悠亚,成名前的清纯风,怎么搭配出来的?

    业内第一大长腿,桥本有菜的腿究竟有多长

    波多野结衣,岛国业界著作最多的超级劳模

    日本AV界NO.1,苍井空是多少宅男的疯狂?

    美女女优视频混剪 This Girl-Laza Morgan

    使用V2Ray的mKCP协议加速游戏