博客年龄:8年1个月
访问:?
文章:8篇

Mysql insert性能优化

2010-01-05 11:45 阅读(?)评论(0)

Mysql优化之加速INSERT插入一条记录花费的时间由以下几个因素决定,数字表示影响的比例:

  连接:(3)

  发送查询给服务器:(2)

  解析查询:(2)

  插入记录:(1*记录大小)

  插入索引:(1*索引数量)

  关闭:(1)

此处没有考虑初始化时打开数据表的开销,因为每次运行查询只会做这么一次。

如果是B-tree索引,随着索引数量的增加,插入记录的速度以logN的比例下降。

可以用以下几种方法来提高插入速度:

  如果要在同一个客户端在同一时间内插入很多记录,可以使用INSERT语句附带有多个values值。这种做法比使用单一值的INSERT语句快多了(在一些情况下比较快)。如果是往一个非空数据表增加记录,可以调整变量bulk_insert_buffer_size的值使其更快。

  如果要从不用的客户端插入大量记录,使用INSERT DELAYED语句也可以提高速度。

  对应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命令

注意,在Mysql 4.0起,可以运行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,23);

     INSERT INTO a VALUES(8,7);

     UNLOCK TABLES;

     这对性能提高的好处在于:直到所有的INSERT语句都完成之后,索引缓存一次性刷新到磁盘中。通常情况下,有多少次INSERT语句就会有多少次索引缓存刷新到磁盘中的开销。如果能在一个语句中一次性插入多个值的话,显然锁表操作也没有必要了。对于事务表而言,用BEGIN/COMMIT代替LOCK TABLES来提高速度。锁表也会降低多次连接测试的总时间,尽管每个独立连接为了等待锁的最大等待时间也会增加。

     Connection 1 does 1000 inserts

     Connection 2,3 and 4 do 1 insert

     Connection 5 does 1000 inserts

     如果没有锁表,则连接2,3,4会在1,5之前完成。如果锁表了,则连接2,3,4可能在1,5之后才能完成,但总时间可能只需要40%。Mysql的INSERT、UPDATE、DELETE操作都非常快,不过在一个语句中如果超过5个插入或者更新时最好加锁以得到更好的性能。如果要一次性做很多次插入,最好在每个循环的前后加上LOCK TABLES和UNLOCK TABLES,从而让其他进程也能访问数据表;这么做性能依然不错。INSERT总比LOAD DATA INFILE插入数据慢,因为二者实现策略有分明的不同。

     想要MyISAM表更快,在LOAD DATA INFILE和INSERT时都可以增加系统变量key_buffer_size的值。

     

 

分享到:
   阅读(?)评论(0)
上一篇: oracle 安装 下一篇:该日志被锁定
 
表  情:
加载中...
 

请各位遵纪守法并注意语言文明