MySQL函数索引及优化 _MySQL

很多开发人员在使用MySQL时经常会在部分列上进行函数计算等，导致无法走索引，在数据量大的时候，查询效率低下。针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化。
1、 MySQL5.7MySQL5.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> altertable tb_function add key idx_create_time(create_time);Query OK, 0 rows affected (0.13 sec)Records: 0Duplicates: 0Warnings: 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函数索引及优化

推荐阅读

王诗晴和程晓玥同框，朋友圈都是有钱人，难怪她要和纪焕博离婚了

「小赵老师谈养生」血糖高多吃蔬菜？尤其春季多吃这两种菜,能有效稳定血糖的''平衡''

糖尿病人适合做啥工作

有的人（男女都有）小便时，声音特别响，急水摩擦冲刷般的哨响，请问这是咋回事

闲谈易趣我要粉丝|个个退的干净，到现在都没有一位复出，当红时期退圈的四位女星

罗汉果和枸杞

雷军小米推十周年感恩季活动：爆款手机最高闪降500元

什么是量比啊什么是量比指标

一才说球|但国产最佳后卫，也失去了悬念，狂轰45分6板9助！广厦输了

昆虫记中谁是一种长得很漂亮的蛾什么是一种很漂亮的蛾它们中最大的来自欧洲

电视剧|《暗恋橘生淮南》男主胡一天，高颜值演员班底，颜控粉的春天到了

朝鲜战争李奇微调走知乎,朝鲜战争李奇微更厉害吗-

湖南可能“合并”的两座城市，合并后长沙地位难保，岳阳恐被超越

IOS系统iOS 14 Beta 6 发布：AirPods Pro 全新空间音频功能现身

长篇|四本值得一看的长篇言情小说，这下再也不用怕书荒了！

北京|北京冬奥会合作伙伴俱乐部主题活动在京举行

白茶保健功效作用,白茶缓解焦虑功效

游侠网|ERSB评级泄露《古惑狼4：时机已到》将登陆次世代

有限公司|海兰信：实际控制人申万秋1140万股质押展期

广东省|广东定了！年底全省禁用！