精华内容
下载资源
问答
  • mysql8.0插入慢之sync_binlog(一)
    2021-01-21 00:29:26

    Mysql配置参数sync_binlog说明

    ySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。

    默认,sync_binlog=0,表示MySQL不控制binlog的刷新,由文件系统自己控制它的缓存的刷新。这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。

    如果sync_binlog>0,表示每sync_binlog次事务提交,MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了,表示每次事务提交,MySQL都会把binlog刷下去,是最安全但是性能损耗最大的设置。这样的话,在数据库所在的主机操作系统损坏或者突然掉电的情况下,系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO,但是设置sync_binlog=1,多个事务同时提交,同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解,但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

    所以很多MySQL DBA设置的sync_binlog并不是最安全的1,而是100或者是0。这样牺牲一定的一致性,可以获得更高的并发和性能。

    sync_binlog你可以调大一些10000 20000 或者100 比较一下效果 找到合适的值设置一下看看!

    本作品采用《CC 协议》,转载必须注明作者和本文链接

    胡军

    更多相关内容
  • mysql8.0 的安装就不说了,上网搜索一大堆的教程,再写到博文里面就有点多余了哦咱们来说说 5.6 升级到 8.0 之后插入数据的问题写一个存储过程循环往表里面插入 3000 条数据先来看 5.6 的表现:1.4s 还是可以接受...

    mysql8.0 的安装就不说了,上网搜索一大堆的教程,再写到博文里面就有点多余了哦

    咱们来说说 5.6 升级到 8.0 之后插入数据慢的问题

    写一个存储过程循环往表里面插入 3000 条数据

    先来看 5.6 的表现:

    1.4s 还是可以接受的吧

    212020713_1_20210104104418666

    再来看 8.0 的表现:

    9.07s 比 5.6 慢了好多好多 难以接受吧

    212020713_2_20210104104419416

    并且我的还报错了

    具体错误:

    [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

    为什么会报这个错误?

    only_full_group_by :使用这个就是使用和 oracle 一样的 group 规则,select 的列都要在 group 中,或者本身是聚合列 (SUM,AVG,MAX,MIN) 才行。5.6 版本中没有这约束。更高版本就有这个问题了。去掉就可以了。

    执行命令(这是临时的做法重启 mysql 服务之后就失效了 永久的改变你可以去修改 my.cnf 配置文件 懒得去搞了能解决问题就行啊):

    SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

    然后重启 mysqld 服务

    这还没到重点,上边只是做了对比和报错的解决,实质性的慢的问题还没解决

    插入慢一方面的原因是因为 mysql8.0 一些设置是默认开启的(5.7 是默认关闭的),而这些设置可能会严重影响数据库性能

    影响插入效率的因素之一是 8.0 默认开启了 log-bin 功能,导致性能严重下降,在确定不用该功能的前提下只要关闭了该配置即可:

    vim /etc/my.cnf#将这句话放进去就完事 或者写 skip-log-bin 作用是一样的看看哪个管用吧disable_log_bin

    然后我们再来看 mysql8.0 的插入效果:

    提升到了 4.327s 但是效果仍然差强人意 和 5.6 比起来还是不行啊 差着好几倍呢

    212020713_3_20210104104419588

    很多人到这就戛然而止了 我相信 mysql8.0 尽然推出来就不可能比 mysql5.6 5.7 差!要不人家推 8.0 干啥?总不能越搞越差吧?

    再牛逼的梦想抵不过傻逼似的坚持!死磕到底!

    我们先来说解决方式再去解释,执行命令:

    set global innodb_flush_log_at_trx_commit = 2;set global sync_binlog = 100000;

    然后看效果:

    212020713_4_20210104104419682

    是不是感觉好神奇啊!卧槽!优化完竟然比 5.7 还快?为什么呢?

    解释一下:

    mysql 这么多年了也经受住了考验,不要怀疑人家,就算是卡也是我们自己的问题,所以调优才是最主要的,甭想着还他妈的用 5.6 吧,调优调优调优,包括 nginx 也是如何进行 nginx 调优 多说几句 你的 nginx 无法抵御洪水攻击你难不成还怪 nginx 性能不行?还是调优调优调优!innodb_flush_log_at_trx_commit 参数

    安全性考虑,这个参数默认是 1

    innodb_flush_log_at_trx_commit 默认值为 1,可设置为 0、1、2

    innodb_flush_log_at_trx_commit 设置为 0,log buffer 将每秒一次地写入 log file 中,并且 log file 的 flush (刷到磁盘) 操作同时进行。该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。

    innodb_flush_log_at_trx_commit 设置为 1,每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file,并且 flush (刷到磁盘) 中去。

    innodb_flush_log_at_trx_commit 设置为 2,每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file. 但是 flush (刷到磁盘) 操作并不会同时进行。该模式下,MySQL 会每秒执行一次 flush (刷到磁盘) 操作。sync_binlog 参数

    sync_binlog 默认值为 1,可设置为 [0,N)

    当 sync_binlog =0,像操作系统刷其他文件的机制一样,MySQL 不会同步到磁盘中去而是依赖操作系统来刷新 binary log。

    当 sync_binlog =N (N>0) ,MySQL 在每写 N 次 二进制日志 binary log 时,会使用 fdatasync () 函数将它的写二进制日志 binary log 同步到磁盘中去。

    综上两条命令搞定:

    set global innodb_flush_log_at_trx_commit = 2;set global sync_binlog = 2000;

    如果你想让他变回来变的更慢那就:

    set global innodb_flush_log_at_trx_commit = 1;set global sync_binlog = 1; 本作品采用《CC 协议》,转载必须注明作者和本文链接

    胡军

    展开全文
  • mycat连接数据库8.0以上用1.6.4有bug,经常无法插入;使用1.6.5以上版本 程序连接报错query_cache_size。 本资源基于1.6.7基础之上进行优化
  • 因为开发环境和测试环境所使用的mysql数据库的版本不一样,开发环境使用的时候57而测试环境使用的是8.0,而在Mysql8.0版本中默认是开启sql_mode = only_full_group_by。 我们使用分组查询时,出现在select字段后面...

    问题描述

    在这里插入图片描述

    因为开发环境和正式环境所使用的mysql数据库的版本不一样,开发环境使用的时候5.7而正式环境使用的是8.0,而在Mysql的8.0版本中默认是开启sql_mode = only_full_group_by。

    我们使用分组查询时,出现在select字段后面的只能是group by后面的分组字段,或使用聚合函数包裹着的字段。

    如何解决(永久性)

    1.在MySQL下执行语句

    SELECT @@sql_mode
    

    2.将查询结果中的ONLY_FULL_GROUP_BY去掉然后复制

    • 找到MySQL的配置文件windows 默认为my.ini 在[mysqld]下面增加配置如下:
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    

    最后打开任务管理器,重启mysql即可。

    解决2(暂时性重启mysql失效)

    1. 执行如下语句:
    set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
    set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
    

    2.使用如下语句查看:

    SELECT @@sql_mode
    

    3.没有ONLY_FULL_GROUP_BY即可。

    扩展

    mysql8.0中有一个环境变量sql_mode,定义了mysql应该支持的sql语法,数据校验等!

    ONLY_FULL_GROUP_BY

    对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么将认为这个SQL是不合法的,因为列不在GROUP BY从句中

    STRICT_TRANS_TABLES

    在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做任何限制

    NO_ZERO_IN_DATE

    在严格模式,不接受月或日部分为0的日期。如果使用IGNORE选项,我们为类似的日期插入’0000-00-00’。在非严格模式,可以接受该日期,但会生成警告。

    NO_ZERO_DATE

    在严格模式,不要将 '0000-00-00’做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式,可以接受该日期,但会生成警告

    ERROR_FOR_DIVISION_BY_ZERO

    在严格模式,在INSERT或UPDATE过程中,如果被零除(或MOD(X,0)),则产生错误(否则为警告)。如果未给出该模式,被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中,MySQL生成被零除警告,但操作结果为NULL。

    NO_AUTO_CREATE_USER

    防止GRANT自动创建新用户,除非还指定了密码。

    NO_ENGINE_SUBSTITUTION

    如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常。

    展开全文
  • 对于数据量较大的插入更新,因io/cpu等性能瓶颈,会产生大量的时间消耗,目前主流的优化主要包括预编译、单条sql插入多条数据、事务插入等,下面详细介绍一下:  单条插入(Mybatis)  INSERT INTO SYS_CITY ...
  • 可以插入的方式由插入完整的记录、插入记录的一部分、插入多条记录、插入另一个查询的结果,下面分别介绍这些内容。 1. 为表的所有字段插入数据 使用基本的INSERT语句插入数据要求指定表名称和插入到新纪录中的值...

    PART1. 插入数据

    可以插入的方式由插入完整的记录、插入记录的一部分、插入多条记录、插入另一个查询的结果,下面分别介绍这些内容。

    1. 为表的所有字段插入数据

    使用基本的INSERT语句插入数据要求指定表名称和插入到新纪录中的值。

    INSERT INTO table_name (column_list) VALUES (value_list);

    注意:虽然使用INSERT插入数据时可以忽略插入数据的列名称,但是值如果不包含列名称,那么VALUES关键字后面的值不仅要求完整而且顺序必须和表定义时列的顺序相同,如果表的结构被修改,对列进行增加、删除或者位置改变操作,这些操作将使得用这种方式插入数据时的顺序也同时改变。如果指定列名称,则不会受到表结构改变的影响。

    2. 为表的指定字段插入数据

    为表的指定字段插入数据,就是在INSERT语句中只向部分字段中插入值,而其他字段的值为表定义时的默认值。

    注意:要保证每一插入值的类型和对应列的数据类型匹配,如果类型不同,将无法插入,并且MySQL会产生错误。

    3. 同时插入多条记录

    INSERT 语句可以同时向数据表中插入多条记录,插入时指定多个值列表,每个值列表之间用逗号分隔开,基本语法格式如下:

    INSERT INTO table_name (column_list)
    VALUES (value_list1), (value_list2), ... ,(value_listn);

    使用INSERT同时插入多条记录时,MySQL会返回一些在执行单行插入时没有的额外信息,这些信息的含义如下:

    Records:表明插入的记录条数。

    Duplicates:表明插入时被忽略的记录,原因可能是这些记录包含了重复的主键值。

    Warnings:表明有问题的数据值,例如发生数据类型转换。

    注意:一个同时插入多行记录的INSERT语句等于多个单行插入的INSERT语句,但是单行的INSERT语句在处理过程中效率更高。因为MySQL执行单行INSERT语句插入多行数据比使用多条INSERT语句快,所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。

    4. 将查询结果插入到表中

    INSERT语句用来给数据表插入记录时直嘀咕插入记录的列值。INSERT还可以将SELECT语句查询的结果插入到表中,只需要一个INSERT语句和一个SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。

    INSERT INTO table_name1 (column_list1)
    SELECT (column_list2) FROM table_name2 WHERE (condition)

    table_name1 指定待插入数据的表;column_list1指定待插入表中要插入数据的哪些列;table_name2指定插入数据是从哪个表中查询出来的;column_list2指定数据来源表的查询列,该列表必须和column_list1列表中的字段个数相同,数据类型相同;condition指定select语句的查询条件。

    PART2. 更新数据

    MySQL中使用update语句更新表中的记录,可以更新特定的行或者同时更新所有的行。

    UPDATE table_name
    SET column_name1 = value1, column_name2 = value2, ... , column_namen = valuen
    WHERE (condition);

    column_name1,column_name2,......,column_namen 为指定更新的字段的名称;value1,value2,……,valuen为相对应的指定字段的更新值;condition指定更新的记录需要满足的条件。更新多列时,每个“列-值”对之间用逗号隔开,最后一列之后不需要逗号。

    注意:保证UPDATE以WHERE子句结束,通过WHERE子句指定被更新的记录所需要满足的条件,如果忽略WHERE子句,MySQL将更新表中所有的行。

    PART3. 删除数据

    从数据表中删除数据使用DELETE语句,DELETE语句允许WHERE子句指定删除条件。

    DELETE FROM table_name [WHERE <condition>];

    table_name 指定要执行删除操作的表;“[WHERE <condition>]”为可选参数,指定删除条件,如果没有WHERE子句,DELETE语句将删除表中的所有记录。

    注意:如果想删除表中的所有记录,还可以使用TRUNCATE TABLE语句。TRUNCATE将直接删除原来的表,并删除创建一个表,其语法结构为TRUNCATE TABLE table_name。TRUNCATE 直接删除表而不是删除记录,因此执行速度比DELETE快。

    PART4. 为表增加计算列

    计算列:简单来说就是某一列的值时通过别的列计算得来的。例如a列值为1、b列值为2,c列值不需要手动插入,定义a+b的结果为c的值,那么c就是计算列,是通过别的列计算得来的。

    col_name data_type [GENERATED ALWAYS] AS (expression)
    [VIRTUAL | STORED] [UNIQUE [KEY]]  [COMMENT comment]
    [NOT NULL | NULL] [[PRIMARY] KEY]

    创建测试表tb1:

     更新数据中的数据:

    字段c中的数据始终时字段a和字段b的和,随着字段a和字段b中数据的变化,自动重新计算a+b的值。

    PART5. MySQL8.0 的新特性——DDL的原子化

     在MySQL8.0版本中,InnoDB表的DDL支持事务完整性,即DDL操作要么成功要么回滚。DDL操作回滚日志写入到data dictionary数据字典表 mysql.innodb_ddl_log(该表是隐藏的表,通过show tables无法看到)中,用于回滚操作。通过设置参数,可将DDL操作日志打印输出到MySQL错误日志中。

    MySQL5.7中会执行成功部分的DDL.

    -- end

    展开全文
  • mySQL 8.0 插入语句报错

    2021-01-19 19:38:03
    INSERTINTOVALUES(#{orderId},#{payment},#{paymentType},#{postFee},#{status},#{createTime},#{updateTime},#{paymentTime},#{consig...INSERT INTOVALUES(#{orderId},#{payment},#{paymentType},#{postFee},#{sta...
  • 先贴本人配置文件:为docker安装的MySQL 挂载的配置文件 # Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify...
  • 主要介绍了mysql技巧:提高插入数据(添加记录)的速度,需要的朋友可以参考下
  • 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第08章 插入更新与删除数据(共12页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第09章 索引(共13页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 ...
  • 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第08章 插入更新与删除数据(共12页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 第09章 索引(共13页).ppt 完整版 MySQL8.0从入门到精通 MySQL数据库教程 ...
  • mysql8.0版本更新内容

    2021-01-26 14:13:10
    MySQL从5.7一跃直接到8.0,这其中的缘由,咱就不关心那么多了,有兴趣的朋友自行百度,本次的版本更新,在功能上主要有以下6点: ...  优化器索引  通用表表达式  窗口函数 ... 在MySQL8.0需要分.
  • MySQL从5.7一跃直接到8.0,本次的版本更新,在功能上主要有以下6点: 1,账户与安全 2,优化器索引 3,通用表表达式 4,窗口函数 5,InnoDB 增强 6,JSON 增强一、账户与安全1.用户的创建与授权 在MySQL5.7的版本:...
  • MySQL的sql_mode合理设置 sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也...
  • mysql8.0配置文件优化

    2021-01-19 20:13:24
    [client]port= 3306socket= /tmp/mysql.sock[mysqld]port= 3306socket= /tmp/mysql.sockbasedir= /usr/local/mysqldatadir= /data/mysqlpid-file = /data/mysql/mysql.piduser=mysqlbind-address = 0.0.0.0server-id...
  • 最近做项目用到mysql8,有个业务场景是创建完资料后跳到资料详情。按常理直接返回个insert id了事。问题就此发生了,每次输出的自增值居然是一样的。然后就进行手动调试 SELECT AUTO_INCREMENT FROM information_...
  • MySQL8.0使用performance_schema分析一条SQL的具体执行时间 新版的MySQL已经彻底移除了以前ProFile的大多数功能 第一步 SELECT * FROM performance_schema.setup_actors; 这是用于配置是否为新的前台server线程(与...
  • mysql的性能优化无法一蹴而就,必须一步一步慢慢来,从各个方面进行优化,最终性能就会有大的提升。一、介绍对mysql优化是一个综合性的技术,主要包括表的设计合理化(符合3NF)添加适当索引(index) [四种: 普通索引、...
  • 搭配RabbitMQ做数据并发插入压测时,发现mysql8.0的速度比5.7版本的还要插入一条5字段的数据平均花了80ms,不能满足业务并发需要,因此简单研究了一下mysql8.0性能如此低下的原因 结论: 经过google和自测,发现...
  • Mysql中的sync_binlog参数

    2021-02-05 09:42:24
    sync_binlog=1 or NThis makes MySQL synchronize the binary log’s contents to disk each time it commits a transaction默认情况下,并不是每次写入时都将binlog与硬盘同步。因此如果操作系统或机器(不仅仅是...
  • 敏感数据可以与带有标签的数据穿插在一起,例如 公开 未分类 其他 当然,您可以在MySQL Audit中打开常规的插入/更新/选择审计。但是在这种情况下,您将审计所有的更改。如果您只想审计敏感数据是否已更改,下面是您...
  • 我的版本是mysql8.0以上的 Windows解决办法: 先查询查看sql_mode中的内容 select @@GLOBAL.sql_mode; +-------------------------------------------------------------------------------------------------------...
  • 今天我们来说下 mysql8 关于only_full_group_by的问题,相信每个人在使用group by 函数时会突然跳出一个报错信息: ERROR1055(42000): Expression#7ofSELECTlistisnotinGROUPBYclauseandcontains nonaggregated ...
  • 这是由于explicit_defaults_for_timestamp默认值值在5.7之中是off,在8.0之中默认值改为了on -- 查看explicit_defaults_for_timestamp默认值 SHOW GLOBAL VARIABLES LIKE "explicit_defaults_for_timestamp"; -- ...
  • mysql8.0配置文件详解

    千次阅读 2021-01-19 23:31:35
    [client]port= 3306socket= /tmp/mysql.sock[mysqld]port= 3306socket= /tmp/mysql.sockbasedir= /usr/local/mysqldatadir= /data/mysqlpid-file = /data/mysql/mysql.piduser=mysqlbind-address = 0.0.0.0server-id...
  • 插入记录 -- 插入含有json数组的记录 数组格式 INSERT INTO t_json(id,sname,info) VALUES( 1, 'name1', JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME())); # CURTIME() 时间函数 20:24:23.000000 -- 插入含有json...
  • Mysql8.0参数优化 内容: 采用yum安装的mysql, 会直接读取/etc/my.cnf作为配置文件。 /etc/my.cnf配置文件可以优化,添加内容,以下为核心参数含义: innodb_buffer_pool 注:缓冲池位于主内存中,InnoDB用它来缓存被...
  • 最近刚刚新装了mysql8.0,在使用时遇到插入错误解决方法 在设置了自增主键id值后,插入数据时,总是提示报错! 插入数据 INSERT INTO maoyan_top100(rank,img,name,star,release_time,score,plot) VALUES(‘1’,‘1...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 16,142
精华内容 6,456
关键字:

mysql8更新更慢_mysql8.0插入慢

mysql 订阅