解析优化MySQL插入方法的五个妙招
mysql 优化 解析 五个 妙招 插入方法
2023-06-13 09:15:01 时间
工作中遇到大概20万的数据插入操作,程序编完后发现运行超时,修改PHP最大执行时间到600,还是超时,检查超时前插入的数据条数推算一下,大概要处理40~60分钟才能插入完成,看来程序写的效率太低,得优化了。
测试电脑配置:
CPU:AMDSempron(tm)Processor
内存:1.5G
语句如下:
$sql="insertinto`test`(`test`)values("$content")";
for($i=1;$i<1000;$i++){
mysql_query($sql);
}
9.43223714828
9.46858215332
9.61053204536
9.24442720413
本人目前为止认为最高效率方式如下:
$sql="insertinto`test`(`test`)values("$content")";
for($i=1;$i<999;$i++){
$sql.=",("$content")";
}
mysql_query($sql);
0.0323481559753
0.0371758937836
0.0419669151306
INSERT语句的速度
插入一个记录需要的时间由下列因素组成,其中的数字表示大约比例:
发送查询给服务器:(2)
分析查询:(2)
插入记录:(1x记录大小)
插入索引:(1x索引)
关闭:(1)
这不考虑打开表的初始开销,每个并发运行的查询打开。
表的大小以logN(B树)的速度减慢索引的插入。
·如果你从不同的客户端插入很多行,能通过INSERTDELAYED语句加快速度。参见13.2.4节,“INSERT语法”。
·用MyISAM,如果在表中没有删除的行,能在SELECT语句正在运行的同时插入行。
·当从一个文本文件装载一个表时,使用LOADDATAINFILE。这通常比使用很多INSERT语句快20倍。
·当表有很多索引时,有可能要多做些工作使得LOADDATAINFILE更快些。使用下列过程:
有选择地用CREATETABLE创建表。
执行FLUSHTABLES语句或命令mysqladminflush-tables。
使用myisamchk--keys-used=0-rq/path/to/db/tbl_name。这将从表中取消所有索引的使用。
用LOADDATAINFILE把数据插入到表中,因为不更新任何索引,因此很快。
如果只想在以后读取表,使用myisampack压缩它。
用myisamchk-r-q/path/to/db/tbl_name重新创建索引。这将在写入磁盘前在内存中创建索引树,并且它更快,因为避免了大量磁盘搜索。结果索引树也被完美地平衡。
执行FLUSHTABLES语句或mysqladminflush-tables命令。
请注意如果插入一个空MyISAM表,LOADDATAINFILE也可以执行前面的优化;主要不同处是可以让myisamchk为创建索引分配更多的临时内存,比执行LOADDATAINFILE语句时为服务器重新创建索引分配得要多。
也可以使用ALTERTABLEtbl_nameDISABLEKEYS代替myisamchk--keys-used=0-rq/path/to/db/tbl_name,使用ALTERTABLEtbl_nameENABLEKEYS代替myisamchk-r-q/path/to/db/tbl_name。使用这种方式,还可以跳过FLUSHTABLES。
·锁定表可以加速用多个语句执行的INSERT操作:
LOCKTABLESaWRITE;
INSERTINTOaVALUES(1,23),(2,34),(4,33);
INSERTINTOaVALUES(8,26),(6,29);
UNLOCKTABLES;
这样性能会提高,因为索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新。如果能用一个语句插入所有的行,就不需要锁定。
对于事务表,应使用BEGIN和COMMIT代替LOCKTABLES来加快插入。
锁定也将降低多连接测试的整体时间,尽管因为它们等候锁定最大等待时间将上升。例如:
Connections2,3,and4do1insert
Connection5does1000inserts
如果不使用锁定,2、3和4将在1和5前完成。如果使用锁定,2、3和4将可能不在1或5前完成,但是整体时间应该快大约40%。
INSERT、UPDATE和DELETE操作在MySQL中是很快的,通过为在一行中多于大约5次连续不断地插入或更新的操作加锁,可以获得更好的整体性能。如果在一行中进行多次插入,可以执行LOCKTABLES,随后立即执行UNLOCKTABLES(大约每1000行)以允许其它的线程访问表。这也会获得好的性能。
INSERT装载数据比LOADDATAINFILE要慢得多,即使是使用上述的策略。
·为了对LOADDATAINFILE和INSERT在MyISAM表得到更快的速度,通过增加key_buffer_size系统变量来扩大键高速缓冲区。
INSERT语法
INSERT[LOW_PRIORITY|DELAYED|HIGH_PRIORITY][IGNORE]
[INTO]tbl_name[(col_name,...)]
VALUES({expr|DEFAULT},...),(...),...
[ONDUPLICATEKEYUPDATEcol_name=expr,...]
或
INSERT[LOW_PRIORITY|DELAYED|HIGH_PRIORITY][IGNORE]
[INTO]tbl_name
SETcol_name={expr|DEFAULT},...
[ONDUPLICATEKEYUPDATEcol_name=expr,...]
或
INSERT[LOW_PRIORITY|HIGH_PRIORITY][IGNORE]
[INTO]tbl_name[(col_name,...)]
SELECT...
[ONDUPLICATEKEYUPDATEcol_name=expr,...]
使用延迟插入操作
服务器把数据行放入一个队列中,并立即给客户端返回一个状态信息,这样客户
端就可以在数据表被真正地插入记录之前继续进行操作了。如果读取者从该数据
表中读取数据,队列中的数据就会被保持着,直到没有读取者为止。接着服务器
开始插入延迟数据行(delayed-row)队列中的数据行。在插入操作的同时,服务器
还要检查是否有新的读取请求到达和等待。如果有,延迟数据行队列就被挂起,
允许读取者继续操作。当没有读取者的时候,服务器再次开始插入延迟的数据行。
这个过程一直进行,直到队列空了为止。
·服务器忽略用于INSERTDELAYED...ONDUPLICATEUPDATE语句的DELAYED。
·因为在行被插入前,语句立刻返回,所以您不能使用LAST_INSERT_ID()来获取AUTO_INCREMENT值。AUTO_INCREMENT值可能由语句生成。
·对于SELECT语句,DELAYED行不可见,直到这些行确实被插入了为止。
·DELAYED在从属复制服务器中被忽略了,因为DELAYED不会在从属服务器中产生与主服务器不一样的数据。
注意,目前在队列中的各行只保存在存储器中,直到它们被插入到表中为止。这意味着,如果您强行中止了mysqld(例如,使用kill-9)
或者如果mysqld意外停止,则所有没有被写入磁盘的行都会丢失。
IGNORE是MySQL相对于标准SQL的扩展。如果在新表中有重复关键字,
或者当STRICT模式启动后出现警告,则使用IGNORE控制ALTERTABLE的运行。
如果没有指定IGNORE,当重复关键字错误发生时,复制操作被放弃,返回前一步骤。
如果指定了IGNORE,则对于有重复关键字的行,只使用第一行,其它有冲突的行被删除。
并且,对错误值进行修正,使之尽量接近正确值。
insertignoreintotb(...)value(...)
这样不用校验是否存在了,有则忽略,无则添加
mysql>INSERTINTOtable(a,b,c)VALUES(1,2,3)
->ONDUPLICATEKEYUPDATEc=c+1;
mysql>UPDATEtableSETc=c+1WHEREa=1;
如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。
注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:
mysql>UPDATEtableSETc=c+1WHEREa=1ORb=2LIMIT1;
如果a=1ORb=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ONDUPLICATEKEY子句。
您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL。
示例:
mysql>INSERTINTOtable(a,b,c)VALUES(1,2,3),(4,5,6)
->ONDUPLICATEKEYUPDATEc=VALUES(a)+VALUES(b);
本语句与以下两个语句作用相同:
mysql>INSERTINTOtable(a,b,c)VALUES(1,2,3)
->ONDUPLICATEKEYUPDATEc=3;
mysql>INSERTINTOtable(a,b,c)VALUES(4,5,6)
->ONDUPLICATEKEYUPDATEc=9;
当您使用ONDUPLICATEKEYUPDATE时,DELAYED选项被忽略。
相关文章
- MySQL Error number: MY-013293; Symbol: ER_CANT_CREATE_ADMIN_THREAD; SQLSTATE: HY000 报错 故障修复 远程处理
- MySQL集群与主从架构的完美结合(mysql集群和主从)
- 深陷困境:MySQL数据库表被锁(mysql数据库表被锁)
- 春天里的MySQL精彩之旅(springmysql)
- 查询MySQL慢查询分析与优化(mysql慢)
- MySQL 配置步骤指南(mysql的配置)
- MySQL编译优化参数实践指南(mysql编译参数)
- 据库攻城狮MySQL:一个有梦想的小攻城狮(mysql一位小数)
- MySQL分页查询优化:提升查询效率(mysql分页查询优化)
- 过长MySQL: 优化锁等待时间的方法(mysql锁等待时间)
- MySQL分库分表查询:优化数据库性能的有效方案(mysql分库分表查询)
- MySQL数据库读写分离:优化数据库性能(mysql数据库读写分离)
- 配置文件MySQL的.ini文件配置:极致优化(mysql.ini)
- MySQL字段存在检测及其重要性(mysql字段存在)
- 【MySQL 集群复制实现高可用及数据安全】(mysql集群复制)
- MySQL高效优化,加快程序运行(mysql高效sql)
- MySQL表锁行锁优化:最佳实践(mysql表锁行锁)
- MySQL 5.6.35:稳定性优化之旅(mysql 5.6.35)
- 使用中间表优化MySQL查询(mysql中使用中间表)
- 如何设置MySQL中BLOB字段的长度限制(mysql中blob长度)
- 深入浅出MySQL中的BLOB数据解析(mysql中blob解析)
- MySQL中AND的使用方法解析(mysql中and的用法)
- ASPNET中采用ashx文件连接MySQL数据库(ashx连接mysql)
- 在CMD中使用命令连接MySQL数据库(cmd命令打开mysql)
- MySQL三表关联优化如何使用索引提高查询性能(mysql三表关联加索引)
- 深入解析MySQL三维数据库的构建与应用(mysql三维数据库)
- 优化数据管理MySQL在企业数据存储中的应用场景(mysql一试用场景)
- 解决MySQL不显示字段类型问题的方法(mysql不显示字段类型)
- Zol提供MySQL下载,让你轻松拥有优秀的数据库管理系统(mysql下载zol)
- 使用MySQL的XML函数解析数据(mysql xml函数)