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: 01.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 explain select * from tb_function where create_time>=＂2020-07-01＂ and create_time 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: 02.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改写及优化的能力。
想了解更多内容或参与技术交流可以关注微信公众号【数据库干货铺】或进技术交流群沟通。
