数据库教程:MySQL 函数索引的优化方案分享

很多开发人员在使用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<date_add('2020-07-01',INTERVAL1day); +----+--------------------+---------------------+ |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_functionwherecreate_time>='2020-07-01'andcreate_time<date_add('2020-07-01',INTERVAL1day); +----+-------------+-------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+ |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|Usingindexcondition| +----+-------------+-------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+ 1rowinset,1warning(0.00sec)

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

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数据库学习教程，希望大家多多关注—计算机技术网(www.ctvol.com)!