懂点IT的耿小厨资料

本文主要介绍懂点IT的耿小厨资料 方法和在新技术下所面对的“挑战”,方便大家深入理解懂点IT的耿小厨资料 过程。本文也将分享懂点IT的耿小厨资料 所遇到的问题和应对策略,怎么解决怎么做的问题。
通过深入本文可以理解代码原理,进行代码文档的下载,也可以查看相应 Demo 部署效果。

很多开发人员在使用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: 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-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<date_add('2020-07-01',INTERVAL 1 day); +----+--------------------+---------------------+ | 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   create_time>='2020-07-01' and create_time<date_add('2020-07-01',INTERVAL 1 day); +----+-------------+-------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+ | 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 in set, 1 warning (0.00 sec)

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

2、 MySQL8.0

MySQL8.0的索引特性增加了函数索引。其实MySQL5.7中推出了虚拟列的功能,而MySQL8.0的函数索引也是依据虚拟列来实现的。将上述的案例在MySQL8.0中实现情况如下文所述。

2.1  创建函数索引

在将上述的表及数据在MySQL8.0的实例上创建,然后创建create_time的函数索引,SQL如下

mysql> 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: 0

2.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改写及优化的能力。

想了解更多内容或参与技术交流可以
懂点IT的耿小厨资料部分资料来自网络,侵权毕设源码联系删除

区块链毕设网(www.qklbishe.com)全网最靠谱的原创区块链毕设代做网站
部分资料来自网络,侵权联系删除!
资源收费仅为搬运整理打赏费用,用户自愿支付 !
qklbishe.com区块链毕设代做网专注|以太坊fabric-计算机|java|毕业设计|代做平台 » 懂点IT的耿小厨资料

提供最优质的资源集合

立即查看 了解详情