bitsCN.com
工作中遇到大概20万的数据插入操作,程序编完后发现运行超时,修改PHP最大执行时间到600,还是超时,检查超时前插入的数据条数推算一下,大概要处理40~60分钟才能插入完成,看来程序写的效率太低,得优化了。
测试电脑配置:
CPU:AMD Sempron(tm) Processor
内存:1.5G
语句如下:
$sql = "insert into `test` (`test`) values ('$content')";
for ($i=1;$imysql_query($sql);
}
mysql_unbuffered_query 运行三次执行时间分别为:
9.85321879387
9.43223714828
9.46858215332
mysql_query 执行时间分别为:
10.0020229816
9.61053204536
9.24442720413
本人目前为止认为最高效率方式如下:
$sql = "insert into `test` (`test`) values ('$content')";
for ($i=1;$i$sql .= ",('$content')";
}
mysql_query($sql);
执行时间为:
0.0323481559753
0.0371758937836
0.0419669151306
INSERT语句的速度
插入一个记录需要的时间由下列因素组成,其中的数字表示大约比例:
连接:(3)
发送查询给服务器:(2)
分析查询:(2)
插入记录:(1x记录大小)
插入索引:(1x索引)
关闭:(1)
这不考虑打开表的初始开销,每个并发运行的查询打开。
表的大小以logN (B树)的速度减慢索引的插入。
加快插入的一些方法:
如果同时从同一个客户端插入很多行,使用含多个VALUE的INSERT语句同时插入几行。这比使用单行INSERT语句快(在某些情况下快几倍)。如果你正向一个非空表添加数据,可以调节bulk_insert_buffer_size变量,使数据插入更快。参见5.3.3节,“服务器系统变量”。
如果你从不同的客户端插入很多行,能通过INSERT DELAYED语句加快速度。参见13.2.4节,“INSERT语法”。
用MyISAM,如果在表中没有删除的行,能在SELECT语句正在运行的同时插入行。
当从一个文本文件装载一个表时,使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。
当表有很多索引时,有可能要多做些工作使得LOAD DATA INFILE更快些。使用下列过程:
有选择地用CREATE TABLE创建表。
执行FLUSH TABLES语句或命令mysqladmin flush-tables。
使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。这将从表中取消所有索引的使用。
用LOAD DATA INFILE把数据插入到表中,因为不更新任何索引,因此很快。
如果只想在以后读取表,使用myisampack压缩它。
用myisamchk -r -q /path/to/db/tbl_name重新创建索引。这将在写入磁盘前在内存中创建索引树,并且它更快,因为避免了大量磁盘搜索。结果索引树也被完美地平衡。
执行FLUSH TABLES语句或mysqladmin flush-tables命令。
请注意如果插入一个空MyISAM表,LOAD DATA INFILE也可以执行前面的优化;主要不同处是可以让myisamchk为创建索引分配更多的临时内存,比执行LOAD DATA INFILE语句时为服务器重新创建索引分配得要多。
也可以使用ALTER TABLE tbl_name DISABLE KEYS代替myisamchk --keys-used=0 -rq/path/to/db/tbl_name,使用ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q/path/to/db/tbl_name。使用这种方式,还可以跳过FLUSH TABLES。
锁定表可以加速用多个语句执行的INSERT操作:
LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;
这样性能会提高,因为索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新。如果能用一个语句插入所有的行,就不需要锁定。
对于事务表,应使用BEGIN和COMMIT代替LOCK TABLES来加快插入。
锁定也将降低多连接测试的整体时间,尽管因为它们等候锁定最大等待时间将上升。例如:
Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts
如果不使用锁定,2、3和4将在1和5前完成。如果使用锁定,2、3和4将可能不在1或5前完成,但是整体时间应该快大约40%。
INSERT、UPDATE和DELETE操作在MySQL中是很快的,通过为在一行中多于大约5次连续不断地插入或更新的操作加锁,可以获得更好的整体性能。如果在一行中进行多次插入,可以执行LOCK TABLES,随后立即执行UNLOCK TABLES(大约每1000行)以允许其它的线程访问表。这也会获得好的性能。
INSERT装载数据比LOAD DATA INFILE要慢得多,即使是使用上述的策略。
为了对LOAD DATA INFILE和INSERT在MyISAM表得到更快的速度,通过增加key_buffer_size系统变量来扩大 键高速缓冲区。
INSERT语法
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
一、DELAYED 的使用
使用延迟插入操作
DELAYED调节符应用于INSERT和REPLACE语句。当DELAYED插入操作到达的时候,
服务器把数据行放入一个队列中,并立即给客户端返回一个状态信息,这样客户
端就可以在数据表被真正地插入记录之前继续进行操作了。如果读取者从该数据
表中读取数据,队列中的数据就会被保持着,直到没有读取者为止。接着服务器
开始插入延迟数据行(delayed-row)队列中的数据行。在插入操作的同时,服务器
还要检查是否有新的读取请求到达和等待。如果有,延迟数据行队列就被挂起,
允许读取者继续操作。当没有读取者的时候,服务器再次开始插入延迟的数据行。
这个过程一直进行,直到队列空了为止。
几点要注意事项:
INSERT DELAYED应该仅用于指定值清单的INSERT语句。服务器忽略用于INSERT DELAYED...SELECT语句的DELAYED。
服务器忽略用于INSERT DELAYED...ON DUPLICATE UPDATE语句的DELAYED。
因为在行被插入前,语句立刻返回,所以您不能使用LAST_INSERT_ID()来获取AUTO_INCREMENT值。AUTO_INCREMENT值可能由语句生成。
对于SELECT语句,DELAYED行不可见,直到这些行确实被插入了为止。
DELAYED在从属复制服务器中被忽略了,因为DELAYED不会在从属服务器中产生与主服务器不一样的数据。
注意,目前在队列中的各行只保存在存储器中,直到它们被插入到表中为止。这意味着,如果您强行中止了mysqld(例如,使用kill -9)
或者如果mysqld意外停止,则所有没有被写入磁盘的行都会丢失。
二、IGNORE的使用
IGNORE是MySQL相对于标准SQL的扩展。如果在新表中有重复关键字,
或者当STRICT模式启动后出现警告,则使用IGNORE控制ALTER TABLE的运行。
如果没有指定IGNORE,当重复关键字错误发生时,复制操作被放弃,返回前一步骤。
如果指定了IGNORE,则对于有重复关键字的行,只使用第一行,其它有冲突的行被删除。
并且,对错误值进行修正,使之尽量接近正确值。
insert ignore into tb(...) value(...)
这样不用校验是否存在了,有则忽略,无则添加
三、ON DUPLICATE KEY UPDATE的使用
如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
-> ON DUPLICATE KEY UPDATE c=c+1;
mysql> UPDATE table SET c=c+1 WHERE a=1;
如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。
注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:
mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。
您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。 VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL。
示例:
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
-> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
本语句与以下两个语句作用相同:
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
-> ON DUPLICATE KEY UPDATE c=3;
mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)
-> ON DUPLICATE KEY UPDATE c=9;
当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。

ACID屬性包括原子性、一致性、隔離性和持久性,是數據庫設計的基石。 1.原子性確保事務要么完全成功,要么完全失敗。 2.一致性保證數據庫在事務前後保持一致狀態。 3.隔離性確保事務之間互不干擾。 4.持久性確保事務提交後數據永久保存。

MySQL既是數據庫管理系統(DBMS),也與編程語言緊密相關。 1)作為DBMS,MySQL用於存儲、組織和檢索數據,優化索引可提高查詢性能。 2)通過SQL與編程語言結合,嵌入在如Python中,使用ORM工具如SQLAlchemy可簡化操作。 3)性能優化包括索引、查詢、緩存、分庫分錶和事務管理。

MySQL使用SQL命令管理數據。 1.基本命令包括SELECT、INSERT、UPDATE和DELETE。 2.高級用法涉及JOIN、子查詢和聚合函數。 3.常見錯誤有語法、邏輯和性能問題。 4.優化技巧包括使用索引、避免SELECT*和使用LIMIT。

MySQL是一種高效的關係型數據庫管理系統,適用於存儲和管理數據。其優勢包括高性能查詢、靈活的事務處理和豐富的數據類型。實際應用中,MySQL常用於電商平台、社交網絡和內容管理系統,但需注意性能優化、數據安全和擴展性。

SQL和MySQL的關係是標準語言與具體實現的關係。 1.SQL是用於管理和操作關係數據庫的標準語言,允許進行數據的增、刪、改、查。 2.MySQL是一個具體的數據庫管理系統,使用SQL作為其操作語言,並提供高效的數據存儲和管理。

InnoDB使用redologs和undologs確保數據一致性和可靠性。 1.redologs記錄數據頁修改,確保崩潰恢復和事務持久性。 2.undologs記錄數據原始值,支持事務回滾和MVCC。

EXPLAIN命令的關鍵指標包括type、key、rows和Extra。 1)type反映查詢的訪問類型,值越高效率越高,如const優於ALL。 2)key顯示使用的索引,NULL表示無索引。 3)rows預估掃描行數,影響查詢性能。 4)Extra提供額外信息,如Usingfilesort提示需要優化。

Usingtemporary在MySQL查詢中表示需要創建臨時表,常見於使用DISTINCT、GROUPBY或非索引列的ORDERBY。可以通過優化索引和重寫查詢避免其出現,提升查詢性能。具體來說,Usingtemporary出現在EXPLAIN輸出中時,意味著MySQL需要創建臨時表來處理查詢。這通常發生在以下情況:1)使用DISTINCT或GROUPBY時進行去重或分組;2)ORDERBY包含非索引列時進行排序;3)使用複雜的子查詢或聯接操作。優化方法包括:1)為ORDERBY和GROUPB


熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

AI Hentai Generator
免費產生 AI 無盡。

熱門文章

熱工具

ZendStudio 13.5.1 Mac
強大的PHP整合開發環境

PhpStorm Mac 版本
最新(2018.2.1 )專業的PHP整合開發工具

SecLists
SecLists是最終安全測試人員的伙伴。它是一個包含各種類型清單的集合,這些清單在安全評估過程中經常使用,而且都在一個地方。 SecLists透過方便地提供安全測試人員可能需要的所有列表,幫助提高安全測試的效率和生產力。清單類型包括使用者名稱、密碼、URL、模糊測試有效載荷、敏感資料模式、Web shell等等。測試人員只需將此儲存庫拉到新的測試機上,他就可以存取所需的每種類型的清單。

DVWA
Damn Vulnerable Web App (DVWA) 是一個PHP/MySQL的Web應用程序,非常容易受到攻擊。它的主要目標是成為安全專業人員在合法環境中測試自己的技能和工具的輔助工具,幫助Web開發人員更好地理解保護網路應用程式的過程,並幫助教師/學生在課堂環境中教授/學習Web應用程式安全性。 DVWA的目標是透過簡單直接的介面練習一些最常見的Web漏洞,難度各不相同。請注意,該軟體中

VSCode Windows 64位元 下載
微軟推出的免費、功能強大的一款IDE編輯器