首页 / MYSQL / MySQL函数索引及优化
MySQL函数索引及优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL函数索引及优化,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含7074字,纯文字阅读大概需要11分钟。
内容图文
![MySQL函数索引及优化](/upload/InfoBanner/zyjiaocheng/1186/5d776e90fb074ab08f24bf933c0970de.jpg)
很多开发人员在使用MySQL时经常会在部分列上进行函数计算等,导致无法走索引,在数据量大的时候,查询效率低下。针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化。
1、 MySQL5.7
MySQL5.7版本中不支持函数索引,因此 遇到函数索引的时候需要进行修改,否则即使查询的字段上有索引,执行时也无法使用索引而进行全表扫描,数据量大的表查询时间会比较长。具体案例如下:
1.1 创建测试表及数据
mysql>use testdb; Database changed mysql>createtable tb_function(id intprimarykey auto_increment,name varchar(100),create_time datetime); Query OK, 0 rows affected (0.01 sec) mysql>insertinto tb_function(name,creatE_time) values(‘anniuadaOAIFAPUHIA‘,‘2020-07-01 12:00:00‘); Query OK, 1 row affected (0.02 sec) mysql>insertinto tb_function(name,creatE_time) values(‘CWQSsar3qcssg‘,‘2020-07-01 15:00:00‘); Query OK, 1 row affected (0.01 sec) mysql>insertinto tb_function(name,creatE_time) values(‘vxfqrt2adafz‘,‘2020-07-01 21:30:00‘); Query OK, 1 row affected (0.01 sec) mysql>insertinto tb_function(name,creatE_time) values(‘etxzwrwbdhegqgaheqhag‘,‘2020-07-02 01:30:00‘); Query OK, 1 row affected (0.01 sec) mysql>insertinto tb_function(name,creatE_time) values(‘awrs433fsgvsfwtwg‘,‘2020-07-02 03:30:00‘); Query OK, 1 row affected (0.00 sec) mysql>insertinto tb_function(name,creatE_time) values(‘awrs433fsgvsfwtwg‘,‘2020-07-02 07:32:00‘); Query OK, 1 row affected (0.00 sec) mysql>insertinto tb_function(name,creatE_time) values(‘awrs433fsgvsfwtwg‘,‘2020-07-02 10:32:00‘); Query OK, 1 row affected (0.00 sec) mysql>insertinto tb_function(name,creatE_time) values(‘tuilklmdadq‘,‘2020-07-02 15:32:00‘); Query OK, 1 row affected (0.00 sec) mysql>insertinto tb_function(name,creatE_time) values(‘wesv2wqdshehq‘,‘2020-07-02 20:32:00‘); Query OK, 1 row affected (0.00 sec) mysql>insertinto tb_function(name,creatE_time) values(‘89yoijnlkwr1‘,‘2020-07-03 02:56:00‘); Query OK, 1 row affected (0.00 sec) mysql>insertinto tb_function(name,creatE_time) values(‘olj;nsaaq‘,‘2020-07-03 08:41:00‘); Query OK, 1 row affected (0.01 sec) mysql>insertinto 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-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|+----+-----------------------+---------------------+12 rows inset (0.00 sec)
1.2 创建索引
在create_time字段上创建索引
mysql>altertable tb_function addkey idx_create_time(create_time); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0
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-0112:00:00||2| CWQSsar3qcssg |2020-07-0115:00:00||3| vxfqrt2adafz |2020-07-0121:30:00|+----+--------------------+---------------------+3 rows inset (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 inset, 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<date_add(‘2020-07-01‘,INTERVAL 1day); +----+--------------------+---------------------+| 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|+----+--------------------+---------------------+3 rows inset (0.00 sec)
执行计划如下:
mysql> explain select*from tb_function where create_time>=‘2020-07-01‘and create_time<date_add(‘2020-07-01‘,INTERVAL 1day); +----+-------------+-------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+| 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 inset, 1 warning (0.00 sec)
可见,修改后,使用了索引。
2、 MySQL8.0
MySQL8.0的索引特性增加了函数索引。其实MySQL5.7中推出了虚拟列的功能,而MySQL8.0的函数索引也是依据虚拟列来实现的。将上述的案例在MySQL8.0中实现情况如下文所述。
2.1 创建函数索引
在将上述的表及数据在MySQL8.0的实例上创建,然后创建create_time的函数索引,SQL如下
mysql>altertable tb_function addkey idx_create_time((date(create_time))); -- 注意里面字段的括号 Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0
2.2 按时间查询
mysql>select*from tb_function where date(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|+----+--------------------+---------------------+3 rows inset (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 inset, 1 warning (0.00 sec)
可见,在MySQL8.0 创建对应的函数索引后,不改变SQL写法的前提下,查询的列上进行对应的函数计算后也可以走索引。
关于MySQL函数索引的优化及MySQL8.0函数索引还可以有更多的场景进行测试,建议大家多动手试试,提高SQL改写及优化的能力。
想了解更多内容或参与技术交流可以关注微信公众号【数据库干货铺】或进技术交流群沟通。
原文:https://www.cnblogs.com/gjc592/p/13233377.html
内容总结
以上是互联网集市为您收集整理的MySQL函数索引及优化全部内容,希望文章能够帮你解决MySQL函数索引及优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。