MySQL高级-SQL优化-insert优化-批量插入-手动提交事务-主键顺序插入

1、批量插入

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

1.1、大批量插入数据

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

1.2、启动Linux中的mysql服务

[root@localhost ~]# ps -ef | grep mysql
root       4013   2155  0 19:14 pts/0    00:00:00 grep --color=auto mysql
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# ps -ef | grep mysql
mysql      4054      1 24 19:15 ?        00:00:01 /usr/sbin/mysqld
root       4112   2155  0 19:15 pts/0    00:00:00 grep --color=auto mysql
[root@localhost ~]# 

1.3、客户端连接到mysql数据库，加上参数 --local-infile

[root@localhost ~]# mysql --local-infile -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.30 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

1.4、查询当前会话中 local_infile 系统变量的值。

mysql> create database atguigu;
Query OK, 1 row affected (0.01 sec)

mysql> select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

mysql>

1.5、开启从本地文件加载数据到服务器的功能

mysql> set global local_infile=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

mysql>

1.6、创建表 tb_user 结构

mysql> use atguigu;
Database changed
mysql> CREATE TABLE `tb_user` (
    ->   `id` INT(11) NOT NULL AUTO_INCREMENT,
    ->   `username` VARCHAR(50) NOT NULL,
    ->   `password` VARCHAR(50) NOT NULL,
    ->   `name` VARCHAR(20) NOT NULL,
    ->   `birthday` DATE DEFAULT NULL,
    ->   `sex` CHAR(1) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `unique_user_username` (`username`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> show tables;
+-------------------+
| Tables_in_atguigu |
+-------------------+
| tb_user           |
+-------------------+
1 row in set (0.00 sec)

mysql>

1.7、上传文件到 /root

1.8、查看 是否 上传成功

[root@localhost ~]# ll
总用量 56504
-rw-------. 1 root root     1533 12月  4 2023 anaconda-ks.cfg
-rw-r--r--. 1 root root      191 6月  25 13:29 dump6379.rdb
-rw-r--r--. 1 root root      191 6月  25 13:31 dump6380.rdb
-rw-r--r--. 1 root root      191 6月  25 13:32 dump6381.rdb
-rw-r--r--. 1 root root      583 6月  24 11:13 dump.rdb
-rw-r--r--. 1 root root     1564 12月  4 2023 initial-setup-ks.cfg
-rw-r--r--. 1 root root 57650380 2月  22 2022 load_user_100w_sort.sql
drwxr-xr-x. 3 root root       18 1月   8 18:11 minio
-rw-r--r--. 1 root root      141 6月  25 17:00 nodes-6379.conf
-rw-------. 1 root root   178567 6月   3 13:50 nohup.out
drwxr-xr-x. 2 root root        6 12月  4 2023 公共
drwxr-xr-x. 2 root root        6 12月  4 2023 模板
drwxr-xr-x. 2 root root        6 12月  4 2023 视频
drwxr-xr-x. 2 root root        6 12月  4 2023 图片
drwxr-xr-x. 2 root root        6 12月  4 2023 文档
drwxr-xr-x. 2 root root        6 12月  4 2023 下载
drwxr-xr-x. 2 root root        6 12月  4 2023 音乐
drwxr-xr-x. 2 root root        6 12月  4 2023 桌面
[root@localhost ~]# 

1.9、查看 load_user_100w_sort.sql 文件前十行数据

[root@localhost ~]# head load_user_100w_sort.sql 
1,jdTmmKQlwu1,jdTmmKQlwu,jdTmmKQlwu,2020-10-13,1
2,BTJOeWjRiw2,BTJOeWjRiw,BTJOeWjRiw,2020-6-12,2
3,waQTJIIlHI3,waQTJIIlHI,waQTJIIlHI,2020-6-2,0
4,XmeFHwozIo4,XmeFHwozIo,XmeFHwozIo,2020-1-11,1
5,xRrvQSHcZn5,xRrvQSHcZn,xRrvQSHcZn,2020-10-18,2
6,gTDfGFNLEj6,gTDfGFNLEj,gTDfGFNLEj,2020-1-13,0
7,nBETIlVCle7,nBETIlVCle,nBETIlVCle,2020-9-27,1
8,vmePKKZjJU8,vmePKKZjJU,vmePKKZjJU,2020-10-20,2
9,pWjaLhJVaB9,pWjaLhJVaB,pWjaLhJVaB,2020-5-7,0
10,zimgGFPEQe10,zimgGFPEQe,zimgGFPEQe,2020-8-1,1
[root@localhost ~]# 

1.10、执行load指令将准备好的数据，加载到表结构中

mysql> load data local infile '/root/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n';
Query OK, 1000000 rows affected (21.47 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select count(*) from tb_user;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.10 sec)

mysql> 

2、手动提交事务

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

3、主键顺序插入

主键乱序插入：8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入：1 2 3 4 5 6 7 8 9 15 21 88 89
主键顺序插入性能高于乱序插入