MySQL 函数索引的优化方案
作者：数据库干货铺 发布时间：2024-01-23 03:13:36
标签：MySQL,函数,索引,优化
很多开发人员在使用MySQL时经常会在部分列上进行函数计算等，导致无法走索引，在数据量大的时候，查询效率低下。针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化。
1、 MySQL5.7
MySQL5.7版本中不支持函数索引，因此 遇到函数索引的时候需要进行修改，否则即使查询的字段上有索引，执行时也无法使用索引而进行全表扫描，数据量大的表查询时间会比较长。具体案例如下：
1.1 创建测试表及数据
mysql> use testdb;
Database changed
mysql> create table tb_function(id int primary key auto_increment,name varchar(100),create_time datetime);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tb_function(name,creatE_time) values('anniuadaOAIFAPUHIA','2020-07-01 12:00:00');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tb_function(name,creatE_time) values('CWQSsar3qcssg','2020-07-01 15:00:00');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tb_function(name,creatE_time) values('vxfqrt2adafz','2020-07-01 21:30:00');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tb_function(name,creatE_time) values('etxzwrwbdhegqgaheqhag','2020-07-02 01:30:00');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tb_function(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 03:30:00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb_function(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 07:32:00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb_function(name,creatE_time) values('awrs433fsgvsfwtwg','2020-07-02 10:32:00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb_function(name,creatE_time) values('tuilklmdadq','2020-07-02 15:32:00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb_function(name,creatE_time) values('wesv2wqdshehq','2020-07-02 20:32:00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb_function(name,creatE_time) values('89yoijnlkwr1','2020-07-03 02:56:00');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb_function(name,creatE_time) values('olj;nsaaq','2020-07-03 08:41:00');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tb_function(name,creatE_time) values('ygo;jkdsaq','2020-07-03 16:20:00');
Query OK, 1 row affected (0.01 sec)
mysql> select * from tb_function;
+----+-----------------------+---------------------+
| id | name | create_time |
+----+-----------------------+---------------------+
| 1 | anniuadaOAIFAPUHIA | 2020-07-01 12:00:00 |
| 2 | CWQSsar3qcssg | 2020-07-01 15:00:00 |
| 3 | vxfqrt2adafz | 2020-07-01 21:30:00 |
| 4 | etxzwrwbdhegqgaheqhag | 2020-07-02 01:30:00 |
| 5 | awrs433fsgvsfwtwg | 2020-07-02 03:30:00 |
| 6 | awrs433fsgvsfwtwg | 2020-07-02 07:32:00 |
| 7 | awrs433fsgvsfwtwg | 2020-07-02 10:32:00 |
| 8 | tuilklmdadq | 2020-07-02 15:32:00 |
| 9 | wesv2wqdshehq | 2020-07-02 20:32:00 |
| 10 | 89yoijnlkwr1 | 2020-07-03 02:56:00 |
| 11 | olj;nsaaq | 2020-07-03 08:41:00 |
| 12 | ygo;jkdsaq | 2020-07-03 16:20:00 |
+----+-----------------------+---------------------+
12 rows in set (0.00 sec)
1.2 创建索引
在create_time字段上创建索引
mysql> alter table tb_function add key idx_create_time(create_time);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
1.3 按时间查询
查询创建时间是2020-07-01那天的所有记录
mysql> select * from tb_function where date(create_time)='2020-07-01';
+----+--------------------+---------------------+
| id | name | create_time |
+----+--------------------+---------------------+
| 1 | anniuadaOAIFAPUHIA | 2020-07-01 12:00:00 |
| 2 | CWQSsar3qcssg | 2020-07-01 15:00:00 |
| 3 | vxfqrt2adafz | 2020-07-01 21:30:00 |
+----+--------------------+---------------------+
3 rows in set (0.00 sec)
执行计划如下：
mysql> explain select * from tb_function where date(create_time)='2020-07-01';
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_function | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
执行计划中可以看出是进行了全面扫描
1.4 优化
因MySQL5.7不支持函数索引，所以需要修改SQL写法来实现走索引（或者使用虚拟列的方式），上述SQL可以修改为
mysql> select * from tb_function where create_time>='2020-07-01' and create_time<date_add('2020-07-01',INTERVAL 1 day);
+----+--------------------+---------------------+
| id | name | create_time |
+----+--------------------+---------------------+
| 1 | anniuadaOAIFAPUHIA | 2020-07-01 12:00:00 |
| 2 | CWQSsar3qcssg | 2020-07-01 15:00:00 |
| 3 | vxfqrt2adafz | 2020-07-01 21:30:00 |
+----+--------------------+---------------------+
3 rows in set (0.00 sec)
执行计划如下：
mysql> explain select * from tb_function where create_time>='2020-07-01' and create_time<date_add('2020-07-01',INTERVAL 1 day);
+----+-------------+-------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_function | NULL | range | idx_create_time | idx_create_time | 6 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+-------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
可见，修改后，使用了索引。
2、MySQL8.0
MySQL8.0的索引特性增加了函数索引。其实MySQL5.7中推出了虚拟列的功能，而MySQL8.0的函数索引也是依据虚拟列来实现的。将上述的案例在MySQL8.0中实现情况如下文所述。
2.1 创建函数索引
在将上述的表及数据在MySQL8.0的实例上创建，然后创建create_time的函数索引，SQL如下
mysql> alter table tb_function add key idx_create_time((date(create_time))); -- 注意里面字段的括号
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
2.2 按时间查询
mysql> select * from tb_function where date(create_time)='2020-07-01';
+----+--------------------+---------------------+
| id | name | create_time |
+----+--------------------+---------------------+
| 1 | anniuadaOAIFAPUHIA | 2020-07-01 12:00:00 |
| 2 | CWQSsar3qcssg | 2020-07-01 15:00:00 |
| 3 | vxfqrt2adafz | 2020-07-01 21:30:00 |
+----+--------------------+---------------------+
3 rows in set (0.00 sec)
执行计划如下
mysql> explain select * from tb_function where date(create_time)='2020-07-01';
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_function | NULL | ref | idx_create_time | idx_create_time | 4 | const | 3 | 100.00 | NULL |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
可见，在MySQL8.0 创建对应的函数索引后，不改变SQL写法的前提下，查询的列上进行对应的函数计算后也可以走索引。
关于MySQL函数索引的优化及MySQL8.0函数索引还可以有更多的场景进行测试，建议大家多动手试试，提高SQL改写及优化的能力。
来源：https://www.cnblogs.com/gjc592/p/13233377.html
0
投稿猜你喜欢
- 查询死锁进程语句select request_session_id spid, OBJECT_NAME(resource_associate
一 实战1 Django_lab\urls.py# -*- coding: utf-8 -*-from django.c- 1:数据源Hollywood Movie Dataset: 好莱坞2006-2011数据集实验目的: 实现 统计2006-2011的数据综合
- JPGjpg全名是JPEG 。JPEG 图片以 24 位颜色存储单个光栅图像。JPEG 是与平台无关的格式，支持最高级别的压缩，不过，这种压
- 随着大数据时代的到来，数据将如同煤电气油一样，成为我们最重要的能源之一，然而这种能源是可以源源不断产生、可再生的。而Python爬虫作为获取
- 表单验证是WEB开发中经常遇到的问题，我们以前常见的做法是：在客户端对表单域进行内容的检查，看是否是满足一定的要求或满足一定的结构，比如：是
- 1、前言在我们日常的开发工作中，为了代码的健壮性，我们通常会对方法中的业务代码进行try-except的包装，以防止在发生异常的时候进程被中
- 一、 在数据库排序查询优化上的差异。在讲解这个内容之前，为了读者能够清楚我讲的内容，我要先谈一个概念。命中率，它是指从内存中取得数据而不从磁
- 布局管理就是管理图形窗口中各个部件的位置和排列。图形窗口中的大量部件也需要通过布局管理，对部件进行整理分组、排列定位，才能使界面整齐有序、美
- 英文原文：http://www.usabilitypost.com/2009/04/15/8-characteristics-of-succ
- 这些包可以独立使用，也可以与其他包一起使用以满足复杂的业务需求。Integration Services 可以提取和转换来自多种源（如 XM
- 一般情况下，tensorflow里面变量初始化过程为： #variables ........... #......
- PHP下载图片后文件打开显示损坏问题用php写个图片下载方法，测试发现下载的图片大小都没问题，但是无法打开文件。解决方法如下：首先打开文件下
- 1. 问题虽然scrapy能够完美且快速的抓取静态页面，但是在现实中，目前绝大多数网站的页面都是动态页面，动态页面中的部分内容是
- opencv中内置了张正友的棋盘格标定法，通过一些姿态各异的棋盘格图像，就能标定相机的内外参数。角点检测第一步是角点检测，首先需要读取棋盘格
- 爬取TOP500的音乐信息，包括排名情况、歌曲名、歌曲时间。网页版酷狗不能手动翻页进行下一步的浏览，仔细观察第一页的URL：http://w
- 本文主要跟大家介绍了Golang巧用defer进行错误处理的相关内容，分享出来供大家参考学习，下面来看看详细的介绍：问题引入毫无疑问，错误处
- 本文实例讲述了python实现获取序列中最小的几个元素。分享给大家供大家参考。具体方法如下：import heapq import rand
- #!/usr/bin/python #-*-coding:utf-8-*-from PyQt4.QtGu
- 所以就怀疑是否编码问题，或者文件权限问题，或者是不是函数不支持问题，经过排查发现原来是万网的L1主机不支持fsockopen，在文件uc_c
