MySQL进阶——插入数据、主键优化

在上篇文章学习了MySQL进阶——索引使用规则，这篇文章学习MySQL进阶——插入数据、主键优化。

插入数据优化

insert指令

SQL插入数据语句一般使用insert命令插入数据，其语法格式如下：

INSERT INTO 表名 (字段1,字段2,...) VALUES(值1,值2,....)     # 插入单条数据
INSERT INTO 表名 (字段1,字段2,...) VALUES (值1,值2,...), (值1,值2,...),(值1,值2,...)  # 批量插入数据

在使用insert插入数据时，为了减少事务的开启与提交，尽量手动提交事务，例如：

start transaction;
INSERT INTO 表名 (字段1,字段2,...) VALUES (值1,值2,...), (值1,值2,...),(值1,值2,...)
INSERT INTO 表名 (字段1,字段2,...) VALUES (值1,值2,...), (值1,值2,...),(值1,值2,...)
INSERT INTO 表名 (字段1,字段2,...) VALUES (值1,值2,...), (值1,值2,...),(值1,值2,...)
commit;

当我们需要一次性批量插入上百万条数据时，使用insert语句插入性能较低，这时可以使用load命令进行插入，load命令可以将本地文件的数据一次性加载在数据库表结构中，其语法格式如下：

load data local infile '本地文件路径' into table 数据表 fields terminated by '文件分割符' lines terminated by '段落分割符';

本地文件的文件内容不是SQL语句，而是具有一定规则的字符，如下图所示：

这里我们用了“,”相隔。

load指令

在使用load指令插入数据前，客户端连接MySQL服务时，需要加上参数：--local_infile，示例代码如下：

mysql --local_infile -u root -p

默认情况下本地加载文件导入数据的开关是关闭的，如下图所示：

可以通过如下代码开启本地加载文件导入数据，

set global local_infile=1;

如下图所示：

接下来我们通过如下代码，将mydata.txt的文件内容导入数据库中，示例代码如下：

load data local infile '/root/mydata.txt' into table UserTable1 fields terminated by ',' lines terminated by '\n';

如下图所示：

这样就成功把mydata.txt文件内容导入数据表中了。

注意：MySQL服务是在docker中，需要将文件拷贝到docker容器中，拷贝代码如下：

docker cp 宿主机文件路径 docker容器:拷贝路径

例如我们将/root/mydata.txt文件拷贝到mysql容器中的/root路径下，可以执行如下代码：

docker cp /root/mydata.txt mysql:/root

主键优化

主键顺序插入比乱序插入效率更高，其原因如下：

数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表，如下图所示：

其逻辑存储结构如下：

在数据组织方式下，插入数据可能会出现页分裂或页合并情况。

页分裂

页可以为空，也可以填充50%，也可以填充100%。每个页包含了2-N行数据，根据主键排列。

主键顺序插入如下图所示：

主键顺序插入直接在每一行后面插入数据，无序对其它页进行处理。

主键乱序插入如下图所示：

在插入ID为50的数据时，首先会将第一页中间位置分割成两页，如下图所示：

接着重新设置链表指针，如下图所示：

注意：不一定是将第一页分割，主要要看插入的ID大小和哪页还没满。

页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页，看看能不能将两个页合并以优化空间使用。

删除了ID为13、14、15、16的数据，且刚好达到了50%，如下图所示：

MySQL就会进行页合并，如下图所示：

所以在设计主键时，尽量遵从以下原则：

• 主键长度尽量少；

• 插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键；

• 尽量不要使用UUID或其他自然无序的主键，例如身份证；

• 操作时，避免对主键的修改；

好了，MySQL进阶——插入数据、主键优化就讲到这里。

公众号：白巧克力LIN

该公众号发布Python、数据库、Linux、Flask、Django、自动化测试、Git、算法、前端、服务器等相关文章！

- END -