在上篇文章学习了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 -