快速向mysql中收支很多数据(100万)ITeyesina - 众发娱乐

快速向mysql中收支很多数据(100万)ITeyesina

2019-02-07 11:51:49 | 作者: 鸿哲 | 标签: 数据,句子,运用 | 浏览: 1748

百度空间 | 百度主页 | 登录 Catro.DingSELECT php, js, as3, html, css, java, cpp FROM knowledge ORDER BY i_mastered DESC 主页博客相册|个人档案 |老友  查看文章 
MySql 大数据量快速刺进和句子优化2009-09-11 09:31作业中遇到大约20万的数据刺进操作,程序编完后发现运转超时,批改PHP最大履行时刻到600,仍是超时,查看超时前刺进的数据条数计算一下,大约要处理40~60分钟才干刺进完结,看来程序写的功率太低,得优化了。

测验电脑装备:
CPU:AMD Sempron(tm) Processor
内存:1.5G

句子如下:
$sql = "insert into `test` (`test`) values ($content)";
for ($i=1;$i 1000;$i++) {
mysql_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 $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倍。拜见13.2.5节,“LOAD DATA INFILE语法”。

· 当表有许多索引时,有或许要多做些作业使得LOAD DATA INFILE更快些。运用下列进程:

有挑选地用CREATE TABLE创立表。

履行FLUSH TABLES句子或指令mysqladmin flush-tables。
运用myisamchk keys-used=0 -rq /path/to/db/tbl_name。这将从表中撤销一切索引的运用。

用LOAD DATA INFILE把数据刺进到表中,由于不更新任何索引,因而很快。
假如只想在今后读取表,运用myisampack紧缩它。拜见15.1.3.3节,“紧缩表特性”。

用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体系变量来扩展 键高速缓冲区。拜见7.5.2节,“调理效劳器参数”。

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选项被疏忽。


版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表众发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1
  • 2

    Oracle 简略运用huabian

    运用,数据库,用户
  • 3
  • 4
  • 5

    MySql标准fenghuang

    标准,进程,修正
  • 6

    数据库相关itjob

    触发器,时刻
  • 7
  • 8
  • 9

    oracle extractITeye环球

    部分,类型,时分
  • 10