大批量插入数据的优化,优化insert语句,优化order by语句,优化子查询,limit优化。

大批量插入数据

当使用load命令导入数据的时候,适当的设置可以提高导入的效率。尽量保证文件的主键有序,效率更高。

load导入的语法:

-- 需要注意,要开启local_infile

-- 语法
load data local infile 文件路径 into table 表名 fields terminated by 每列分隔符 lines terminated by 每行分割符;

-- 例如:
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';

对于 InnoDB 类型的表,有以下几种方式可以提高导入的效率:

1、主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率。如果InnoDB表没有主键,那么系统会自动默认创建一个内部列作为主键,所以如果可以给表创建一个主键,将可以利用这点,来提高导入数据的效率。

-- 1、首先,检查一个全局系统变量 'local_infile' 的状态, 如果得到如下显示 Value=OFF,则说明这是不可用的
show global variables like 'local_infile';
 
-- 2、修改local_infile值为on,开启local_infile
set global local_infile=1;
 
-- 3、加载数据 
/*
脚本文件介绍 :
    sql1.log  ----> 主键有序
    sql2.log  ----> 主键无序
*/
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';

-- 主键有序导入会更快

示例

2、关闭唯一性校验

在导入数据前执行SET UNIQUE_CHECKS=0关闭唯一性校验,在导入结束后执行SET UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率。

-- 关闭唯一性校验
SET UNIQUE_CHECKS=0;
 
truncate table tb_user;
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
 
SET UNIQUE_CHECKS=1;

优化insert语句

1、一个insert语句中插入多个值

如果需要同时对一张表插入很多行数据时,应该尽量使用多个值表的insert语句,这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

-- 原始方式为:
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');

-- 优化后的方案为 : 
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

2、在事务中进行数据插入

需要先关闭自动事务提交。本质上还是缩减客户端与数据库之间的连接、关闭等消耗。

begin;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;

3、数据有序插入

-- 优化前
insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');
 
 
-- 优化后
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');

优化order by语句

第一种是通过对返回数据进行排序,也就是通常说的 filesort 排序,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

第二种通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高。

建立合适的索引可以优化。

优化子查询

多表查询的效率要高于子查询。

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询是可以被更高效的连接(JOIN)替代。

结果

system > const > eq_ref > ref> range > index > ALL

连接(Join)查询之所以更有效率一些 ,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

limit优化

一般分页查询时,通过创建覆盖索引能够比较好地提高性能。

一个常见又非常头疼的问题就是 limit 900000,10 ,此时需要MySQL排序前900010 记录,仅仅返回900000 - 900010 的记录,其他记录丢弃,查询排序的代价非常大 。

1、优化思路一

在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。

2、优化思路二

该方案适用于主键自增的表,可以把Limit 查询转换成某个位置的查询 。


声明:内容来源于B站视频《2022黑马程序员最新MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程》,博客内容仅作学习参考使用。



博主个人公众号
版权声明 ▶ 本网站名称:陶小桃Blog
▶ 本文链接:https://www.52txr.cn/2022/mysql37.html
▶ 本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长进行核实删除。
▶ 转载本站文章需要遵守:商业转载请联系站长,非商业转载请注明出处!!

最后修改:2022 年 06 月 15 日
如果觉得我的文章对你有用,请随意赞赏