mysql 数据插入优化方法之concurrent_insert

当一个线程对一个表执行一个DELAYED语句时,如果不存在这样的处理程序,一个处理器线程被创建以处理对于该表的所有DELAYED语句。

通常来说,在MyISAM里读写操作是串行的,但当对同一个表进行查询和插入操作时,为了降低锁竞争的频率,根据concurrent_insert的设置,MyISAM是可以并行处理查询和插入的:

当concurrent_insert=0时,不允许并发插入功能。
当concurrent_insert=1时,允许对没有洞洞的表使用并发插入,新数据位于数据文件结尾(缺省)。
当concurrent_insert=2时,不管表有没有洞洞,都允许在数据文件结尾并发插入。

这样看来,把concurrent_insert设置为2是很划算的,至于由此产生的文件碎片,可以定期使用OPTIMIZE TABLE语法优化。

max_write_lock_count:

缺省情况下,写操作的优先级要高于读操作的优先级,即便是先发送的读请求,后发送的写请求,此时也会优先处理写请求,然后再处理读请求。这就造成一 个问题:一旦我发出若干个写请求,就会堵塞所有的读请求,直到写请求全都处理完,才有机会处理读请求。此时可以考虑使用 max_write_lock_count:

max_write_lock_count=1

有了这样的设置,当系统处理一个写操作后,就会暂停写操作,给读操作执行的机会。

low-priority-updates:

我们还可以更干脆点,直接降低写操作的优先级,给读操作更高的优先级。

low-priority-updates=1

综合来看,concurrent_insert=2是绝对推荐的,至于max_write_lock_count=1和low-priority- updates=1,则视情况而定,如果可以降低写操作的优先级,则使用low-priority-updates=1,否则使用 max_write_lock_count=1。

set-variable = max_allowed_packet=1M
set-variable = net_buffer_length=2K

在myisam engine下

1. 尽量使用insert into table_name values (…), (…..),(…..)这样形式插入数据,避免使用inset into table_name values (); inset into table_name values (); inset into table_name values ();

2 增加bulk_insert_buffer_size(默认8M)

3 如果是非空表,使用alter table table_name disable keys,然后load data infile,导入完数据在执行:

alter table table_name enable keys. 如果是空表,就不需要这个操作,因为myisam表在空表中导入数据时,是先导入数据然后建立indexs。

4 在插入数据时考虑使用:insert delayed….这样操作实际mysql把insert操作放到队列里面,进行相对集中的插入,速度更快。

5. 使用load data infile 比使用insert 操作快近20倍,尽量使用此操作。

在innodb engine下

1.导入数据之前执行set unique_checks=0来禁止对唯一索引的检查,数据导入完成之后再运行set unique_checks=1.

2. 导入数据之前执行set foreign_key_checks=0来禁止对外键的检查,数据导入完成之后再执行set foreign_key_checks=1.

3.导入数据之前执行set autocommit=0禁止自动事务的自动提交,数据导入完成之后,执行set autocommit=1 恢复自动提交操作。

使用innodb engine的表,物理存储都是按PK的顺序存的。不能使用类似于myisam一样disable keys.

硬件上提高磁盘的I/0对插入速度很有好处(所以如果进行大数据量的导入导出工作,尽量在比较NB的硬件上进行,能缩减完成的时间,已经防止出现问题)。

当一个线程对一个表执行一个DELAYED语句时,如果不存在这样的处理程序,一个处理器线程被创建以处理对于该表的所有DELAYED语句。

线程检查处理程序是否已经获得了一个DELAYED锁;如果没有,它告诉处理程序去获得。即使其他的线程有在表上的一个READ或WRITE锁,也能获得 DELAYED锁。然而,处理程序将等待所有ALTER TABLE锁或FLUSH TABLES以保证表结构是最新的。

线程执行INSERT语句,但不是将行写入表,它把最后一行的副本放进被处理器线程管理的一个队列。任何语法错误都能被线程发觉并报告给客户程序。

顾客不能报告结果行的重复次数或AUTO_INCREMENT值;它不能从服务器获得它们,因为INSERT在插入操作完成前返回。如果你使用C API,同样原因,mysql_info()函数不返回任何有意义的东西。

当行被插入到表中时,更新日志有处理器线程更新。在多行插入的情况下,当第一行被插入时,更新日志被更新。
在每写入delayed_insert_limit行后,处理器检查是否任何SELECT语句仍然是未完成,如果这样,在继续之前允许执行这些语句。

当处理器在它的队列中没有更多行时,表被解锁。如果在delayed_insert_timeout秒内没有收到新的INSERT DELAYED命令,处理器终止。

如果已经有多于delayed_queue_size行在一个特定的处理器队列中未解决,线程等待直到队列有空间。这有助于保证mysqld服务器对延迟的内存队列不使用所有内存。

处理器线程将在Command列的MySQL进程表中显示delayed_insert。如果你执行一个FLUSH TABLES命令或以KILL thread_id杀死它,它将被杀死,然而,它在退出前首先将所有排队的行存进表中。在这期间,这次它将不从其他线程接受任何新的INSERT命令。如 果你在它之后执行一个INSERT DELAYED,将创建一个新的处理器线程。

注意,上述意味着,如果有一个INSERT DELAYED处理器已经运行,INSERT DELAYED命令有比正常INSERT更高的优先级!其他更新命令将必须等到INSERT DELAY排队变空、杀死处理器线程(用KILL thread_id)或执行FLUSH TABLES。

下列状态变量提供了关于INSERT DELAYED命令的信息: Delayed_insert_threads 处理器线程数量

Delayed_writes 用INSERT DELAYED被写入的行的数量
Not_flushed_delayed_rows 等待被写入的行数字

高并发insert语句的解决方法

前言

1、防止数据多次修改

1.1 、insert方案

1、添加uniqpue进行解决(重复则是更新)

insert一般没什么问题,直接控制好unique就可以,这样的话,就不会插入两条(如果重复了则,进行更新操作)

2、update方案

1、redis分布式锁、消息队列(每次只插入一个)

2、mysql锁(更新可以使用乐观锁)

2、高并发下的安全性

1、在线的网站上去执行一个大的DELETE或INSERT查询,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的(update也是,如果没有指定唯一主键或者索引的话,会锁表),表一锁住了,别的操作都进不来了。所以一定要非常小心

2、如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你泊WEB服务Crash,还可能会让你的整台服务器马上掛了。 >

2.1、解决方案

2.1.1、表的调整

把表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。(如果有一百多个字段是很恐怖的)

示例一:

在Users表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个人信息外,你并不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢?这样会让你的表有更好的性能,大家想想是不是,大量的时候,我对于用户表来说,只有用户ID,用户名,口令,用户角色等会被经常使用。小一点的表总是会有好的性能。

示例二:

你有一个叫“last_login”的字段,它会在每次用户登录时被更新。但是,每次更新时会导致该表的查询缓存被清空。所以,你可以把这个字段放到另一个表中,这样就不会影响你对用户ID,用户名,用户角色的不停地读取了,因为查询缓存会帮你增加很多性能。hp程序员之家

另外,需要注意的是,这些被分出去的字段所形成的表,我们是认为不会经常是join的,否则,这样的性能会比不分割表的时候时还要差,而且,会是极数级的下降

(0)

相关推荐

  • mysql 数据插入优化方法之concurrent_insert

    当一个线程对一个表执行一个DELAYED语句时,如果不存在这样的处理程序,一个处理器线程被创建以处理对于该表的所有DELAYED语句. 通常来说,在MyISAM里读写操作是串行的,但当对同一个表进行查询和插入操作时,为了降低锁竞争的频率,根据concurrent_insert的设置,MyISAM是可以并行处理查询和插入的: 当concurrent_insert=0时,不允许并发插入功能. 当concurrent_insert=1时,允许对没有洞洞的表使用并发插入,新数据位于数据文件结尾(缺省).

  • mysql 数据插入优化方法

    通常来说,在MyISAM里读写操作是串行的,但当对同一个表进行查询和插入操作时,为了降低锁竞争的频率,根据concurrent_insert的设置,MyISAM是可以并行处理查询和插入的: 当concurrent_insert=0时,不允许并发插入功能. 当concurrent_insert=1时,允许对没有洞洞的表使用并发插入,新数据位于数据文件结尾(缺省). 当concurrent_insert=2时,不管表有没有洞洞,都允许在数据文件结尾并发插入. 这样看来,把concurrent_ins

  • MYSQL开发性能研究之批量插入数据的优化方法

    一.我们遇到了什么问题 在标准SQL里面,我们通常会写下如下的SQL insert语句. INSERT INTO TBL_TEST (id) VALUES(1); 很显然,在MYSQL中,这样的方式也是可行的.但是当我们需要批量插入数据的时候,这样的语句却会出现性能问题.例如说,如果有需要插入100000条数据,那么就需要有100000条insert语句,每一句都需要提交到关系引擎那里去解析,优化,然后才能够到达存储引擎做真的插入工作. 正是由于性能的瓶颈问题,MYSQL官方文档也就提到了使用批

  • MySql批量插入优化Sql执行效率实例详解

    MySql批量插入优化Sql执行效率实例详解 itemcontractprice数量1万左右,每条itemcontractprice 插入5条日志. updateInsertSql.AppendFormat("UPDATE itemcontractprice AS p INNER JOIN foreigncurrency AS f ON p.ForeignCurrencyId = f.ContractPriceId SET p.RemainPrice = f.RemainPrice * {0},

  • MySQL性能全面优化方法参考,从CPU,文件系统选择到mysql.cnf参数优化

    本文整理了一些MySQL的通用优化方法,做个简单的总结分享,旨在帮助那些没有专职MySQL DBA的企业做好基本的优化工作,至于具体的SQL优化,大部分通过加适当的索引即可达到效果,更复杂的就需要具体分析了,可以参考本站的一些优化案例或者联系我们 1.硬件层相关优化 1.1.CPU相关 在服务器的BIOS设置中,可调整下面的几个配置,目的是发挥CPU最大性能,或者避免经典的NUMA问题: 1.选择Performance Per Watt Optimized(DAPC)模式,发挥CPU最大性能,跑

  • php中实现xml与mysql数据相互转换的方法

    本文实例讲述了php中实现xml与mysql数据相互转换的方法.分享给大家供大家参考.具体分析如下: 这里提供了一个mysql2xml转换类,可以快速的把xml转换成mysql同时反之也可以把mysql转换成xml,下面看代码. mysql2xml.php类文件,用于备份MySQL数据的,代码如下: 复制代码 代码如下: <?php class MySQL2XML {         protected $conn;         protected $result;         prot

  • mysql数据备份与恢复实现方法分析

    本文实例讲述了mysql数据备份与恢复实现方法.分享给大家供大家参考,具体如下: 本文内容: 复制文件法 利用mysqldump 利用select into outfile 其它(列举但不介绍) 首发日期:2018-04-19 有些时候,在备份之前要先做flush tables ,确保所有数据都被写入到磁盘中. 复制文件法: 对于myisam存储引擎的数据库,它的表结构(.frm).数据(.myd)和索引(.myi)都单独成文件,可以直接复制这三个文件到备份空间就可以成功备份了. 至于还原,只需

  • mysql 数据插入和更新及删除详情

    目录 1.插入 2.更新 3.删除 1.插入 INSERT INTO customers( customers.cust_address, customers.cust_city, customers.cust_state, customers.cust_zip, customers.cust_country, customers.cust_contact, customers.cust_email ) VALUES('zhangsan','good','111','ca','dasdsa','

  • docker实现MySQL数据同步的方法

    大家好,今天分享一下docker的一个实战 我们使用docker 实现MySQL 的数据同步 搜索mysql 镜像 [root@localhost ~]# docker search mysql NAME DESCRIPTION STARS OFFICIAL AUTOMATED mysql MySQL is a widely used, open-source relation- 12222 [OK] mariadb MariaDB Server is a high performing ope

  • 计算机二级考试MySQL常考点 8种MySQL数据库设计优化方法

    MySQL数据库设计的8种优化方法,具体内容如下 1.选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快.因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小.例如,在定义邮政编码这个字段时,如果将其设置为CHAR(255),显然给数据库增加了不必要的空间,甚至使用VARCHAR这种类型也是多余的,因为CHAR(6)就可以很好的完成任务了.同样的,如果可以的话,我们应该使用MEDIUMINT而不是B

随机推荐