MySQL删除和插入数据很慢的问题解决

公司开发人员在测试环境中执行一条 insert 语句时，需要花费 10 几秒才可以执行成功。查看测试环境数据库性能、数据量、死锁等信息，均为发现异常。最后通过修改日志写入方式解决此问题。

1. 修改办法

修改/etc/my.cnf文件，将 innodb_flush_log_at_trx_commit = 1改为0， 但这样就要承担数据库Crash后，1秒内未存储到数据库数据丢失可能的风险。MySQL文档中对该参数的描述如下：

If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

2. 参数说明

  • 0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作
  • 1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认
  • 2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作

3. 注意事项

当设置为0时，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

当设置为1时，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。

当设置为2时，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数，当两个参数都设置为1的时候写入性能最差，推荐做法是innodb_flush_log_at_trx_commit=2，sync_binlog=500 或1000。

到此这篇关于MySQL删除和插入数据很慢的问题解决的文章就介绍到这了,更多相关MySQL删除和插入数据很慢内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • 详解Navicat远程连接mysql很慢

     最终解决办法在最后一张图片 如果你连接远程数据库(Linux系统)并且很慢(具体症状为第一次连接打开所有的表貌似不是很慢,正常的,但是隔几分钟后任意打开一张表就很慢,即时数据很少的表依然很慢),我们用命令找到配置文件(mysql5.7的) sudo vi /etc/my.cnf 此时一般会提示你输入密码,那么你输入密码后,再执行编辑命令 sudo vi /etc/my.cnf 点击键盘insert键此时进入编辑模式,找到对应的位置,把需要配置的(如下)添加进去 [mysqld] #远程连接忽略

  • 解决MySQL Sending data导致查询很慢问题的方法与思路

    解决MySQL Sending data导致查询很慢问题的方法与思路

    最近帮忙定位一个mysql查询很慢的问题,定位过程综合各种方法.理论.工具,很有代表性,分享给大家. [问题现象] 使用sphinx支持倒排索引,但sphinx从mysql查询源数据的时候,查询的记录数才几万条,但查询的速度非常慢,大概要4~5分钟左右 [处理过程] 1)explain 首先怀疑索引没有建好,于是使用explain查看查询计划,结果如下: 从explain的结果来看,整个语句的索引设计是没有问题的,除了第一个表因为业务需要进行整表扫描外,其它的表都是通过索引访问 2)show p

  • MySQL数据库远程连接很慢的解决方案

    有次同事提出开发使用的MySQL数据库连接很慢,因为我们的MySQL开发数据库是单独一台机器部署的,所以认为可能是网络连接问题导致的. 在进行 ping和route后发现网络通信都是正常的,而且在MySQL机器上进行本地连接发现是很快的,所以网络问题基本上被排除了.以前也遇到过一次这样的问题,可后来就不知怎么突然好了,这次又遭遇这样的问题,所以想看看是不是MySQL的配置问题.在查询MySQL相关文档和网络搜索后,发现了一个配置似乎可以解决这样的问题,就是在MySQL的配置文件中增加如下配置参数

  • 禁止mysql做域名解析(解决远程访问mysql时很慢)

    当远程访问mysql时,mysql会解析域名,会导致访问速度很慢,加上下面这个配置可解决此问题 禁止mysql做域名解析 [mysqld] skip-name-resolve PHP远程连接MYSQL速度慢,有时远程连接到MYSQL用时4-20秒不等,本地连接MYSQL正常,出现这种问题的主要原因是,默认安装的MYSQL开启了DNS的反向解析,在MY.INI(WINDOWS系统下)或MY.CNF(UNIX或LINUX系统下)文件的[mysqld]下加入skip-name-resolve这一句.

  • 解决远程连接mysql很慢的方法(mysql_connect 打开连接慢)

    在进行 ping和route后发现网络通信都是正常的,而且在mysql机器上进行本地连接发现是很快的,所以网络问题基本上被排除了.以前也遇到过一次这样的问题,可后来就不知怎么突然好了,这次又遭遇这样的问题,所以想看看是不是mysql的配置问题.在查询mysql相关文档和网络搜索后,发现了一个配置似乎可以解决这样的问题,就是在mysql的配置文件中增加如下配置参数: [mysqld]skip-name-resolve 在linux下配置文件是/etc/my.cnf,在windows下配置文件是my

  • Mysql查询很慢卡在sending data的原因及解决思路讲解

    因为编写了一个Python程序,密集的操作了一个Mysql库,之前数据量不大时,没发现很慢,后来越来越慢,以为只是数据量大了的原因,但是后来慢到不能忍受了,查了半天,索引能用的都用上了,执行一次还是要3到4秒,不能忍受了. 于是把一些可以缓存的查询全部用redis缓存了起来,大大加速了应用. 但是还是有一些没办法缓存的,或者说,每次查询都是不一样的结果的就没办法了.用navicat的查询概况可以看到卡住的地方是在:Sending data一段,用时3.5秒,占了99%的查询时间. 在网上查了一些

  • MySQL删除和插入数据很慢的问题解决

    公司开发人员在测试环境中执行一条 insert 语句时,需要花费 10 几秒才可以执行成功.查看测试环境数据库性能.数据量.死锁等信息,均为发现异常.最后通过修改日志写入方式解决此问题. 1. 修改办法 修改/etc/my.cnf文件,将 innodb_flush_log_at_trx_commit = 1改为0, 但这样就要承担数据库Crash后,1秒内未存储到数据库数据丢失可能的风险.MySQL文档中对该参数的描述如下: If the value of innodb_flush_log_at

  • Mysql避免重复插入数据的4种方式

    Mysql避免重复插入数据的4种方式

    最常见的方式就是为字段设置主键或唯一索引,当插入重复数据时,抛出错误,程序终止,但这会给后续处理带来麻烦,因此需要对插入语句做特殊处理,尽量避开或忽略异常,下面我简单介绍一下,感兴趣的朋友可以尝试一下: 这里为了方便演示,我新建了一个user测试表,主要有id,username,sex,address这4个字段,其中主键为id(自增),同时对username字段设置了唯一索引: 01 insert ignore into 即插入数据时,如果数据存在,则忽略此次插入,前提条件是插入的数据字段设置了

  • mysql中批量插入数据(1万、10万、100万、1000万、1亿级别)

    目录 方法一: 方法二: 总结: 硬件:windows7+8G内存+i3-4170处理器+4核CPU 首先贴上数据库的操作类BaseDao: import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; impor

  • Mysql删除重复的数据 Mysql数据去重复

    MySQL数据库中查询重复数据 select * from employee group by emp_name having count (*)>1; Mysql  查询可以删除的重复数据 select t1.* from employee t1 where (t1.emp_name) in (select t4.emp_name from (select t2.emp_name from employee t2 group by t2.emp_name having count(*)>1)

  • JDBC连接MySQL数据库批量插入数据过程详解

    这篇文章主要介绍了JDBC连接MySQL数据库批量插入数据过程详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 1.读取本地json数据 2.jdbc理解数据库 3.批量插入 maven 引入jar包: <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2

  • php往mysql中批量插入数据实例教程

    前言 假如说我有这样一个表,我想往这个表里面插入大量数据 CREATE TABLE IF NOT EXISTS `user_info` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键', `name` varchar(255) NOT NULL default '' COMMENT '姓名', `age` int(11) NOT NULL default '0' COMMENT '年龄', PRIMARY KEY (`id`) ) ENG

  • 解决Linux下Tomcat向MySQL插入数据中文乱码问题

    一.问题 在windows上面使用eclipse开发的项目在windows上面运行一切正常,部署到腾讯云时出现向MySQL数据库中插入数据是中文乱码 二.解决办法 1.尝试一 直接在linux上面使用insert语句插入中文,正常 2.尝试二 在tomcat配置文件server.xml中加入useBodyEncodingForURI="true",不行 3.尝试三 在tomcat配置文件server.xml中再加入URIEncoding="UTF-8",不行 4.尝

  • mysql插入数据INSERT INTO SET的优势

    往mysql数据库中插入数据.以前常用 INSERT INTO 表名 (列名1,列名2-) VALUES(列值1,列值2); 如果在PHP程序中,就会写成如下示例(往商品库里增加商品) $sql = "INSERT INTO products (p_title,p_price.........) VALUES ('$p_title','$p_price'............)"; 缺点是当这个表的列特别多时,会特别乱. 1.前后需要对应,容易写错顺序. 2.后期改动(增加列,减少列

  • MySQL数据库操作DML 插入数据,删除数据,更新数据

    MySQL数据库操作DML 插入数据,删除数据,更新数据

    目录 DML介绍 数据插入 数据修改 数据删除 DML介绍 DML是指数据操作语言,英文全称是Data Manipulation Language,用来对数据库中表的数据记录进行更新. 关键字: 插入insert 删除delete 更新update 数据插入 insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...): //向表中插入某些 insert into 表 values (值1,值2,值3...); //向表中插入所有列 这里的两种方式,第一

  • MySQL删除数据，表文件大小依然没变的原因

    对于运行很长时间的数据库来说,往往会出现表占用存储空间过大的问题,可是将许多没用的表删除之后,表文件的大小并没有改变,想解决这个问题,就需要了解 InnoDB 如何回收表空间的. 对于一张表来说,占用空间重要分为两部分,表结构和表数据.通常来说,表结构定义占用的空间很小.所以空间的问题主要和表数据有关. 在 MySQL 8.0 前,表结构存储在以 .frm 为后缀的文件里.在 8.0,允许将表结构定义在系统数据表中. 关于表数据的存放 可以将表数据存在共享表空间,或者单独的文件中,通过 inno

随机推荐