≡
  • 网络编程
  • 数据库
  • CMS技巧
  • 软件编程
  • PHP笔记
  • JavaScript
  • MySQL
位置：首页 > 数据库 > MySQL

MySQL 函数索引的优化的简单示例

人气：219 时间：2021-07-13

这篇文章主要为大家详细介绍了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)

MySQL 函数索引的优化方案

 

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

MySQL 函数索引的优化方案

 

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 函数索引的优化方案

执行计划如下：

代码如下：


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)

MySQL 函数索引的优化方案

执行计划中可以看出是进行了全面扫描

 

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 函数索引的优化方案

执行计划如下：

代码如下：


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)

MySQL 函数索引的优化方案

可见，修改后，使用了索引。

 

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

MySQL 函数索引的优化方案

 

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 函数索引的优化方案

执行计划如下

代码如下：


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)

MySQL 函数索引的优化方案

可见，在MySQL8.0 创建对应的函数索引后，不改变SQL写法的前提下，查询的列上进行对应的函数计算后也可以走索引。

关于MySQL函数索引的优化及MySQL8.0函数索引还可以有更多的场景进行测试，建议大家多动手试试，提高SQL改写及优化的能力。

以上就是MySQL 函数索引的优化方案的详细内容，更多关于MySQL 函数索引及优化方案的资料请关注四海网其它相关文章！

本文来自：http://www.q1010.com/177/19485-0.html

注：关于MySQL 函数索引的优化的简单示例的内容就先介绍到这里，更多相关文章的可以留意四海网的其他信息。

关键词：

您可能感兴趣的文章

上一篇：解决MySQL索引性能优化的问题
下一篇：MySQL之select in 子查询优化的简单示例
热门文章
  • mysql 修改character_set_server为utf-8的简单示例
  • 解决MySQL丢失文件localhost.localdomain.pid、mysql.sock的示例
  • MySQL 数据类型binary和varbinary的简单示例
  • MySQL:reading initial communication packet问题解决方法
  • MySql 表类型MYISAM、InnoDB区别
  • bash: mysql: command not found 的解决方法
  • MYSQL默认用户名ROOT修改方法
  • MySQL 常用命令菜鸟教程
  • MySQL 使用命令行新建用户并授予权限
  • MySql 数据库物理文件存放位置查看示例
  • 最新文章
    • MySQL查看死锁与解除死锁的简单示例
    • MySQL 慢查询的功能实例
    • MySQL查看死锁与去除死锁的简单示例
    • MySQL找出未提交事务的SQL的简单示例
    • MySQL锁阻塞的的简单示例
    • MySQL中的binary类型使用操作的示例
    • SQL优化教程之in与range查询的简单示例
    • MySQL 的 21 个规范、优化最佳实践！
    • MySQL 字符类型大小写敏感的简单示例
    • 解决mybatis-plus分页传入参数后sql where条件没有limit分页信息的问题

四海网收集整理一些常用的php代码,JS代码,数据库mysql等技术文章。