MySQL 函数索引的优化方案

2023-07-15 20:01:03 146

很多开发人员在使用MySQL时经常会在部分列上进行函数计算等，导致无法走索引，在数据量大的时候，查询效率低下。针对此种情况本文从MySQL5.7及MySQL8.0中分别进行不同方式的优化。

1、MySQL5.7

MySQL5.7版本中不支持函数索引，因此遇到函数索引的时候需要进行修改，否则即使查询的字段上有索引，执行时也无法使用索引而进行全表扫描，数据量大的表查询时间会比较长。具体案例如下：

1.1创建测试表及数据

mysql>usetestdb;
Databasechanged
mysql>createtabletb_function(idintprimarykeyauto_increment,namevarchar(100),create_timedatetime);
QueryOK,0rowsaffected(0.01sec)

mysql>insertintotb_function(name,creatE_time)values('anniuadaOAIFAPUHIA','2020-07-0112:00:00');
QueryOK,1rowaffected(0.02sec)

mysql>insertintotb_function(name,creatE_time)values('CWQSsar3qcssg','2020-07-0115:00:00');
QueryOK,1rowaffected(0.01sec)

mysql>insertintotb_function(name,creatE_time)values('vxfqrt2adafz','2020-07-0121:30:00');
QueryOK,1rowaffected(0.01sec)

mysql>insertintotb_function(name,creatE_time)values('etxzwrwbdhegqgaheqhag','2020-07-0201:30:00');
QueryOK,1rowaffected(0.01sec)

mysql>insertintotb_function(name,creatE_time)values('awrs433fsgvsfwtwg','2020-07-0203:30:00');
QueryOK,1rowaffected(0.00sec)

mysql>insertintotb_function(name,creatE_time)values('awrs433fsgvsfwtwg','2020-07-0207:32:00');
QueryOK,1rowaffected(0.00sec)

mysql>insertintotb_function(name,creatE_time)values('awrs433fsgvsfwtwg','2020-07-0210:32:00');
QueryOK,1rowaffected(0.00sec)

mysql>insertintotb_function(name,creatE_time)values('tuilklmdadq','2020-07-0215:32:00');
QueryOK,1rowaffected(0.00sec)

mysql>insertintotb_function(name,creatE_time)values('wesv2wqdshehq','2020-07-0220:32:00');
QueryOK,1rowaffected(0.00sec)

mysql>insertintotb_function(name,creatE_time)values('89yoijnlkwr1','2020-07-0302:56:00');
QueryOK,1rowaffected(0.00sec)

mysql>insertintotb_function(name,creatE_time)values('olj;nsaaq','2020-07-0308:41:00');
QueryOK,1rowaffected(0.01sec)

mysql>insertintotb_function(name,creatE_time)values('ygo;jkdsaq','2020-07-0316:20:00');
QueryOK,1rowaffected(0.01sec)

mysql>select*fromtb_function;
+----+-----------------------+---------------------+
|id|name|create_time|
+----+-----------------------+---------------------+
|1|anniuadaOAIFAPUHIA|2020-07-0112:00:00|
|2|CWQSsar3qcssg|2020-07-0115:00:00|
|3|vxfqrt2adafz|2020-07-0121:30:00|
|4|etxzwrwbdhegqgaheqhag|2020-07-0201:30:00|
|5|awrs433fsgvsfwtwg|2020-07-0203:30:00|
|6|awrs433fsgvsfwtwg|2020-07-0207:32:00|
|7|awrs433fsgvsfwtwg|2020-07-0210:32:00|
|8|tuilklmdadq|2020-07-0215:32:00|
|9|wesv2wqdshehq|2020-07-0220:32:00|
|10|89yoijnlkwr1|2020-07-0302:56:00|
|11|olj;nsaaq|2020-07-0308:41:00|
|12|ygo;jkdsaq|2020-07-0316:20:00|
+----+-----------------------+---------------------+
12rowsinset(0.00sec)

1.2创建索引

在create_time字段上创建索引

mysql>altertabletb_functionaddkeyidx_create_time(create_time);
QueryOK,0rowsaffected(0.13sec)
Records:0Duplicates:0Warnings:0

1.3按时间查询

查询创建时间是2020-07-01那天的所有记录

mysql>select*fromtb_functionwheredate(create_time)='2020-07-01';
+----+--------------------+---------------------+
|id|name|create_time|
+----+--------------------+---------------------+
|1|anniuadaOAIFAPUHIA|2020-07-0112:00:00|
|2|CWQSsar3qcssg|2020-07-0115:00:00|
|3|vxfqrt2adafz|2020-07-0121:30:00|
+----+--------------------+---------------------+
3rowsinset(0.00sec)

执行计划如下：

mysql>explainselect*fromtb_functionwheredate(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|Usingwhere|
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1rowinset,1warning(0.00sec)

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

1.4优化

因MySQL5.7不支持函数索引，所以需要修改SQL写法来实现走索引（或者使用虚拟列的方式），上述SQL可以修改为

mysql>select*fromtb_functionwherecreate_time>='2020-07-01'andcreate_time
执行计划如下：
mysql>explainselect*fromtb_functionwherecreate_time>='2020-07-01'andcreate_time
可见，修改后，使用了索引。
2、MySQL8.0
MySQL8.0的索引特性增加了函数索引。其实MySQL5.7中推出了虚拟列的功能，而MySQL8.0的函数索引也是依据虚拟列来实现的。将上述的案例在MySQL8.0中实现情况如下文所述。
2.1创建函数索引


在将上述的表及数据在MySQL8.0的实例上创建，然后创建create_time的函数索引，SQL如下
mysql>altertabletb_functionaddkeyidx_create_time((date(create_time)));--注意里面字段的括号
QueryOK,0rowsaffected(0.10sec)
Records:0Duplicates:0Warnings:0
2.2按时间查询
mysql>select*fromtb_functionwheredate(create_time)='2020-07-01';
+----+--------------------+---------------------+
|id|name|create_time|
+----+--------------------+---------------------+
|1|anniuadaOAIFAPUHIA|2020-07-0112:00:00|
|2|CWQSsar3qcssg|2020-07-0115:00:00|
|3|vxfqrt2adafz|2020-07-0121:30:00|
+----+--------------------+---------------------+
3rowsinset(0.00sec)
执行计划如下
mysql>explainselect*fromtb_functionwheredate(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|
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1rowinset,1warning(0.00sec)
可见，在MySQL8.0创建对应的函数索引后，不改变SQL写法的前提下，查询的列上进行对应的函数计算后也可以走索引。
关于MySQL函数索引的优化及MySQL8.0函数索引还可以有更多的场景进行测试，建议大家多动手试试，提高SQL改写及优化的能力。
以上就是MySQL函数索引的优化方案的详细内容，更多关于MySQL函数索引及优化方案的资料请关注毛票票其它相关文章！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

MySQL 函数索引的优化方案

热门推荐

随机推荐