最全MySQL8.0实战教程 22 MySQL的优化 22.8 SQL优化【1 大批量插入数据】

雨点打透心脏的1/2处 2024-03-25 12:33 53阅读 0赞

#### 最全MySQL8.0实战教程 ####

#### 文章目录 ####

*   *   *  最全MySQL8.0实战教程
         *  22 MySQL的优化
         *   *  22.8 SQL优化【1 大批量插入数据】
             *   *  22.8.1 主键顺序插入
                 *  22.8.2 关闭唯一性校验

[【黑马程序员MySQL知识精讲+mysql实战案例\_零基础mysql数据库入门到高级全套教程】 ][MySQL_mysql_mysql_]

#### 22 MySQL的优化 ####

##### 22.8 SQL优化【1 大批量插入数据】 #####

create table `tb_user` (
      `id` int(11) not null auto_increment,
      `username` varchar(45) not null,
      `password` varchar(96) not null,
      `name` varchar(45) not null,
      `birthday` datetime default null,
      `sex` char(1) default null,
      `email` varchar(45) default null,
      `phone` varchar(45) default null,
      `qq` varchar(32) default null,
      `status` varchar(32) not null comment '用户状态',
      `create_time` datetime not null,
      `update_time` datetime default null,
      primary key (`id`),
      unique key `unique_user_username` (`username`)
    );

![在这里插入图片描述][7e557698fad34d3eb265bf817fb0945f.png_pic_center]

当使用load 命令导入数据的时候，适当的设置可以提高导入的效率。对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：

![在这里插入图片描述][52a5495112c54bdbb4d3e703196bf7a3.png_pic_center]

两个SQL文件，非常的大

![在这里插入图片描述][ac6c9bf197bc409891b8c9bf550e1db0.png_pic_center]

其中sql1的主键是排好序的

![在这里插入图片描述][9b68f9564933477482aead6ae7936a99.png_pic_center]

sql2.log就不是

![在这里插入图片描述][2a41cfc5034448428549aee18776c57f.png_pic_center]

###### 22.8.1 主键顺序插入 ######

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

![在这里插入图片描述][d7716849bcf94dba9d8a010c55a263c9.png_pic_center]

-- 1、首先，检查一个全局系统变量 'local_infile' 的状态， 如果得到如下显示 Value=OFF，则说明这是不可用的
    show global variables like 'local_infile';
     
    -- 2、修改local_infile值为on，开启local_infile
    set global local_infile=1;

![在这里插入图片描述][0d05ab9dd0e64b0faff4f2a03a9a7c58.png_pic_center]

![在这里插入图片描述][d38be9b32a0e4fb1949a7f5b88031895.png_pic_center]

-- 3、加载数据 
    /*
    脚本文件介绍 :
        sql1.log  ----> 主键有序
        sql2.log  ----> 主键无序
    */
    load data local infile 'D:\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';

![在这里插入图片描述][441638affb0a48649868bf599b88dec1.png_pic_center]

sql1 有100万行的数据

执行

![在这里插入图片描述][f51b6096329a4338b2fe99eacb3c573a.png_pic_center]

OK，sql1 用了24s 左右

下面看看sql2【也是100万行】，执行之前，先把当前tb\_user 清空

![在这里插入图片描述][f2b8d182c48848cf89bc11d379418e66.png_pic_center]

OK， 81s 左右，效果很明显

所以，当通过load 向表中加载数据时，尽量保证文件中的主键有序，这样可以提高执行效率

###### 22.8.2 关闭唯一性校验 ######

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

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

其实100万的数据效果也不是特别明显，再上一个级别就看得出来差别了，笔者这里就不试了。

[MySQL_mysql_mysql_]: https://www.bilibili.com/video/BV1iF411z7Pu
[7e557698fad34d3eb265bf817fb0945f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/1e96c4af3d5049dcacee3836d8df4da1.png
[52a5495112c54bdbb4d3e703196bf7a3.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/c5e90379d0f8472588ffa48da2f71475.png
[ac6c9bf197bc409891b8c9bf550e1db0.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/70b5e61c36d240bf8d461c8e4a5efb88.png
[9b68f9564933477482aead6ae7936a99.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/bccbbb49379f4a3188c4134f57250e76.png
[2a41cfc5034448428549aee18776c57f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/980f42f2e0ae479093ce30019096df20.png
[d7716849bcf94dba9d8a010c55a263c9.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/278e6f1d501847009f6beed1f29f79b2.png
[0d05ab9dd0e64b0faff4f2a03a9a7c58.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/2e1c2d2175d241d48f53530688e27959.png
[d38be9b32a0e4fb1949a7f5b88031895.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/6f4437242a9e42c48f487e6da33bb951.png
[441638affb0a48649868bf599b88dec1.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/781ae9202919468bbbcd8947a65c3582.png
[f51b6096329a4338b2fe99eacb3c573a.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/3b64b228584e4e5b9cbe39c74e5d2672.png
[f2b8d182c48848cf89bc11d379418e66.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/3a2111976b584a25b09fe80cd4e34110.png