精华内容
下载资源
问答
  • mysql 如何分批 读取数据
    2021-01-18 22:16:08

    情景是数据库里有一系列的post(新闻类),现在想根据post表中有的post_date(时间),view(浏览数量),或者post_like表的likeCount进行排序,并分批读取数据,limit在25

    比如说post_date:

    SELECT post.*, user.user_avatar, user.user_name,

    (SELECT COUNT(*) FROM comment

    WHERE comment.post_id = post.post_id) AS commentCount,

    (SELECT COUNT(*) FROM post_like

    WHERE post_like.post_id = post.post_id) AS likeCount

    FROM post, user where post.user_id=user.user_id and post.post_date < "2018-04-05 12:12:35" order by post.post_date desc limit 25

    这里post_date可以抓取最后一条的post_date用来做下次获取的标记值。

    但是问题是:如果我想通过view或者likeCount排序分批读取数据,获取的数据中没有可以用来做标记,拿来进行下次对比的标记值。

    mysql语句如下:

    SELECT post.*, user.user_avatar, user.user_name,

    (SELECT COUNT(*) FROM comment

    WHERE comment.post_id = post.post_id) AS commentCount,

    (SELECT COUNT(*) FROM post_like

    WHERE post_like.post_id = post.post_id) AS likeCount

    FROM post, user where post.user_id=user.user_id order by likeCount desc limit 25

    大家一般都是怎么解决的?谢谢

    更多相关内容
  • MySQL批量插入数据,分批提交笔记

    千次阅读 2019-07-12 13:12:33
    前言:之前就接触过MySQL的批量...1.使用普通的分批提交,每次提交一万条记录,耗时如下: 2.使用高速提交,关闭MySQL服务器的预处理,开启批量添加: 耗时如下: 总结:两种情况下,开起了高效批量提交,...

    前言:之前就接触过MySQL的批量提交,但是相对而言,虽然比单条提交速度快很多但是,如果数据量太大,高达百万级别或者更高,就需要进一步地提高速度。

    准备:创建一个测试的数据表,如本次例子:test_user

    1.使用普通的分批提交,每次提交一万条记录,耗时如下:

    2.使用高速提交,关闭MySQL服务器的预处理,开启批量添加:

    耗时如下:

    总结:两种情况下,开起了高效批量提交,插入速度提升四倍左右,每次运行程序,时间上可能会随着机器不一样或者MySQL版本不一样而有这细微差别,整体效果差不多。

    程序源码如下:

    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.util.Date;
    
    public class MysqlBatchUtil {
        private String sql = "INSERT INTO test_user(user_account,user_password,user_nickname) VALUES (?,?,?)";
        private String connectStr = "jdbc:mysql://localhost:3306/mytest";
        private String username = "root";
        private String password = "liaojiaxi";
    
        private void doStore() throws ClassNotFoundException, SQLException, IOException {
            Class.forName("com.mysql.jdbc.Driver");
    //下面这行注释掉即是普通批量提交
            connectStr += "?useServerPrepStmts=false&rewriteBatchedStatements=true";//此处是测试高效批次插入,去掉之后执行时普通批次插入
    
            Connection conn = (Connection) DriverManager.getConnection(connectStr, username, password);
            conn.setAutoCommit(false); // 设置手动提交
            int count = 0;
            try (PreparedStatement psts = conn.prepareStatement(sql)) {
                Date begin = new Date();
                for (int k = 0; k < 100; k++) {
                    for (int i = 0; i < 10000; i++) {
                        psts.setString(1, count + "@aa.com");
                        psts.setString(2, count + "@password");
                        psts.setString(3, count + "@nickname");
                        psts.addBatch();          // 加入批量处理
                        count++;
                    }
                    psts.executeBatch(); // 执行批量处理
                    conn.commit();  // 提交
                    System.out.println("正在插入第" + (k + 1) + "批数据。。。");
                }
    
                Date end = new Date();
                System.out.println("运行时间=" + (end.getTime() - begin.getTime())+"毫秒");
            }
            conn.close();
    
        }
    
        public static void main(String[] args) {
            try {
                new MysqlBatchUtil().doStore();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    数据表建表语句:

    CREATE TABLE `test_user` (
      `user_id` int(8) NOT NULL AUTO_INCREMENT,
      `user_account` varchar(20) NOT NULL,
      `user_password` varchar(20) NOT NULL,
      `user_nickname` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`user_id`),
      KEY `id_index` (`user_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=latin1

    附:

    关于MySQL的useServerPrepStmts的探讨可参考文章:https://cs-css.iteye.com/blog/1847772

    展开全文
  • mysql批量提交的优化

    2021-01-27 20:15:07
    背景用户修改布局时,需要批量更新mysql的xxxx_layout_xxxx表。批量操作的数据量是2-30条/次。批量操作是这次项目在技术上比 较关键的一个点,之前批量操作做过性能上的测试,mysql端问题不大,7000+tps,Java端的...

    .背景

    用户修改布局时,需要批量更新mysql的xxxx_layout_xxxx表。批量操作的数据量是2-30条/次。批量操作是这次项目在技术上比 较关键的一个点,之前批量操作做过性能上的测试,mysql端问题不大,7000+tps,Java端的效率有些差,有优化空间。

    对批量的性能进行了测试,优化。过程如下。

    经测试,批量更新30条记录的时间是35ms。由于数据在mysql服务端中会有内存缓存,批量更新30条的时间用了35ms,感觉有些长,试图找出原因。

    使用截包工具(这里用的ethereal),抓取mysql的数据包,下面是一次批量更新的数据包:

    4df9673c1c77bb35ebe43a2413d56908.png可以看出,批量更新时,每条update语句都去mysql请求了一次。并没有打包发给mysql。这种批量的效率肯定不会高。同样方法试了下oracle数据库,oracle驱动做的就很好,一次批量是打包在同一个请求中,是真正的批量提交,效率自然比mysql高。

    找了些资料,发现mysql默认情况确实是不支持batch。为了解决上面的问题,需要给JDBC连接加上参数rewriteBatchedStatements=true,并且jdbc driver需要升级到5.1.8以上才支持这个参数。

    增加参数rewriteBatchedStatements=true,driver版本升到5.1.17后,再次测试,批量更新30条的时间从35ms降到了11ms。截包后,可以看出底层的机制,已经变成批量提交:

    c3b7cf15fb45e16548f52054a80c4cfe.png

    查看包的内容可以发现,这条请求里,封装了30条update语句

    f4a716b0b4dc99466d341cefe76f1795.png

    横坐标: 一次批量更新的条数。纵坐标:更新100次所用时间(ms)

    可见,当批量条数增加时,rewriteBatchedStatements=true的性能有很大优势。即使数量少时,也还是有一定优势。

    结论:

    使用rewriteBatchedStatements=true参数,对批量操作,性能有较大提高,从官方解释上看,对普通操作没有影响。 从网上资料和自己的测试上看,暂时没有发现rewriteBatchedStatements=true参数Driver版本5.1.17的问题。 因此,本项目中计划采取下面优化措施:

    JDBC Driver版本从5.0.4升级到5.1.17。

    连接属性中加入rewriteBatchedStatements=true参数

    附:

    测试环境:

    mysql JDBC 3.0.4/3.1.17。

    客户端: 普通PC机。

    连接池数: 1-10。

    10线程并发,批量更新30条记录(索引有效),循环更新100次。

    批量更新主要代码:

    mmpSqlMapClient.startTransaction(); // 使用事务

    mmpSqlMapClient.startBatch(); // 批量提交

    for (ChannelLayoutDO channelLayout: userChannelLayoutList) {              mmpSqlMapClient.update(“UserChannelLayoutDAO.updateSort”, channelLayout);

    }

    mmpSqlMapClient.executeBatch();

    mmpSqlMapClient.commitTransaction();

    展开全文
  • Batch提交Batch提交是用一个Connection来对若干个SQL进行一次性、批量提交MySQL处理。但有一个问题是,如果某个SQL挂了而其他的并不会受影响,这样就无法保证原子性,只能再封装一层Transaction来实现。同时,...

    Batch提交

    Batch提交是用一个Connection来对若干个SQL进行一次性、批量提交给MySQL处理。但有一个问题是,如果某个SQL

    挂了而其他的并不会受影响,这样就无法保证原子性,只能再封装一层Transaction来实现。

    同时,Batch处理并不是每个Server都支持的,需要看具体的产品和版本号。

    代码

    我们通过观察具体的客户端实现代码来了解批量提交是如何实现的

    spring-jdbc-5.1.3.RELEASE中的实现如下:

    @Override

    public int[] batchUpdate(String sql, SqlParameterSource[] batchArgs) {

    if (batchArgs.length == 0) {

    return new int[0];

    }

    ParsedSql parsedSql = getParsedSql(sql);

    PreparedStatementCreatorFactory pscf = getPreparedStatementCreatorFactory(parsedSql, batchArgs[0]);

    // 调用另外一个batchUpdate方法

    return getJdbcOperations().batchUpdate(

    pscf.getSql(),

    new BatchPreparedStatementSetter() {

    @Override

    public void setValues(PreparedStatement ps, int i) throws SQLException {

    Object[] values = NamedParameterUtils.buildValueArray(parsedSql, batchArgs[i], null);

    pscf.newPreparedStatementSetter(values).setValues(ps);

    }

    @Override

    public int getBatchSize() {

    return batchArgs.length;

    }

    });

    }

    第二个batchUpdate方法如下:

    @Override

    public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {

    if (logger.isDebugEnabled()) {

    logger.debug("Executing SQL batch update [" + sql + "]");

    }

    // 调用execute方法,传入sql和回调接口

    int[] result = execute(sql, (PreparedStatementCallback) ps -> {

    try {

    int batchSize = pss.getBatchSize();

    InterruptibleBatchPreparedStatementSetter ipss =

    (pss instanceof InterruptibleBatchPreparedStatementSetter ?

    (InterruptibleBatchPreparedStatementSetter) pss : null);

    if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {

    // 如果支持批处理操作则依次将每个SQL的参数进行填充

    for (int i = 0; i < batchSize; i++) {

    pss.setValues(ps, i);

    if (ipss != null && ipss.isBatchExhausted(i)) {

    break;

    }

    ps.addBatch();

    }

    // 执行批处理函数(底层交给jdbc driver的实现类去执行,如mysql-connector-java)

    return ps.executeBatch();

    }

    else {

    List rowsAffected = new ArrayList<>();

    for (int i = 0; i < batchSize; i++) {

    pss.setValues(ps, i);

    if (ipss != null && ipss.isBatchExhausted(i)) {

    break;

    }

    rowsAffected.add(ps.executeUpdate());

    }

    int[] rowsAffectedArray = new int[rowsAffected.size()];

    for (int i = 0; i < rowsAffectedArray.length; i++) {

    rowsAffectedArray[i] = rowsAffected.get(i);

    }

    return rowsAffectedArray;

    }

    }

    finally {

    if (pss instanceof ParameterDisposer) {

    ((ParameterDisposer) pss).cleanupParameters();

    }

    }

    });

    Assert.state(result != null, "No result array");

    return result;

    }

    调用的execute方法如下:

    public T execute(PreparedStatementCreator psc, PreparedStatementCallback action)

    throws DataAccessException {

    Assert.notNull(psc, "PreparedStatementCreator must not be null");

    Assert.notNull(action, "Callback object must not be null");

    if (logger.isDebugEnabled()) {

    String sql = getSql(psc);

    logger.debug("Executing prepared SQL statement" + (sql != null ? " [" + sql + "]" : ""));

    }

    Connection con = DataSourceUtils.getConnection(obtainDataSource());

    PreparedStatement ps = null;

    try {

    ps = psc.createPreparedStatement(con);

    applyStatementSettings(ps);

    T result = action.doInPreparedStatement(ps);

    handleWarnings(ps);

    return result;

    }

    catch (SQLException ex) {

    // Release Connection early, to avoid potential connection pool deadlock

    // in the case when the exception translator hasn't been initialized yet.

    if (psc instanceof ParameterDisposer) {

    ((ParameterDisposer) psc).cleanupParameters();

    }

    String sql = getSql(psc);

    JdbcUtils.closeStatement(ps);

    ps = null;

    DataSourceUtils.releaseConnection(con, getDataSource());

    con = null;

    throw translateException("PreparedStatementCallback", sql, ex);

    }

    finally {

    if (psc instanceof ParameterDisposer) {

    ((ParameterDisposer) psc).cleanupParameters();

    }

    JdbcUtils.closeStatement(ps);

    DataSourceUtils.releaseConnection(con, getDataSource());

    }

    }

    上面是spring-jdbc的batchUpdate逻辑的实现,但是具体的业务执行是由Server来执行的,所以不同的Server的实现机制不同可能对性能有较大影响。

    以MySQL为例,在mysql-connector-java-5.1.43的实现如下:

    public boolean canRewriteAsMultiValueInsertAtSqlLevel() throws SQLException {

    return this.parseInfo.canRewriteAsMultiValueInsert;

    }

    @Override

    protected long[] executeBatchInternal() throws SQLException {

    synchronized (checkClosed().getConnectionMutex()) {

    if (this.connection.isReadOnly()) {

    throw new SQLException(Messages.getString("PreparedStatement.25") + Messages.getString("PreparedStatement.26"),

    SQLError.SQL_STATE_ILLEGAL_ARGUMENT);

    }

    if (this.batchedArgs == null || this.batchedArgs.size() == 0) {

    return new long[0];

    }

    // we timeout the entire batch, not individual statements

    int batchTimeout = this.timeoutInMillis;

    this.timeoutInMillis = 0;

    resetCancelledState();

    try {

    statementBegins();

    clearWarnings();

    // 注意这里的条件,batchHasPlainStatements默认初始化就是false,而rewriteBatchedStatements

    // 需要在jdbc url里制定,否则不会走合并SQL的逻辑,如jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&rewriteBatchedStatements=true

    if (!this.batchHasPlainStatements && this.connection.getRewriteBatchedStatements()) {

    if (canRewriteAsMultiValueInsertAtSqlLevel()) {

    return executeBatchedInserts(batchTimeout);

    }

    if (this.connection.versionMeetsMinimum(4, 1, 0) && !this.batchHasPlainStatements && this.batchedArgs != null

    && this.batchedArgs.size() > 3 /* cost of option setting rt-wise */) {

    return executePreparedBatchAsMultiStatement(batchTimeout);

    }

    }

    return executeBatchSerially(batchTimeout);

    } finally {

    this.statementExecuting.set(false);

    clearBatch();

    }

    }

    }

    /**

    * Rewrites the already prepared statement into a multi-value insert

    * statement of 'statementsPerBatch' values and executes the entire batch

    * using this new statement.

    *

    * @return update counts in the same fashion as executeBatch()

    *

    * @throws SQLException

    */

    protected long[] executeBatchedInserts(int batchTimeout) throws SQLException {

    ......

    }

    如何整合

    根据DML的类型是insert、update还是delete,会走不同方法:

    如果是insert语句,满成条件情况下,会整合成:”insert into xxx_table values (xx),(yy),(zz)…”这样的语句。

    如果是update\delete语句,满成条件情况下,会整合成:”update t set … where id = 1; update t set … where id = 2;

    update t set … where id = 3 …“这样的语句。

    总结

    如果想要达到MySQL真正batchUpdate效果,需要有以下几个条件:

    需要在jdbcUrl后添加参数rewriteBatchedStatements=true

    this.batchHasPlainStatements为false

    如果是update\delete 语句,还需要mysql版本>=4.1.0,并且batch的数量>3

    References

    https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

    https://blog.csdn.net/qq271859852/article/details/79562262

    本文首次发布于 S.L’s Blog, 作者 @stuartlau ,

    转载请保留原文链接.

    展开全文
  • 使用JDBC连接MySQL数据库进行数据插入的时候,特别是大批量数据连续插入(10W+),如何提高效率呢? 在JDBC编程接口中Statement 有两个方法特别值得注意: void addBatch() throws SQLException Adds a set of ...
  • MySQL 批量提交优化

    2021-01-18 22:16:08
    用户修改布局时,需要批量更新mysql的xxxx_layout_xxxx表。批量操作的数据量是2-30条/次。批量操作是这次项目在技术上比较关键的一个点,之前批量操作做过性能上的测试,mysql端问题不大,7000+tps,Java端的效率...
  • MySQL存储过程游标实现分批COMMIT

    千次阅读 2020-08-31 20:21:51
    存储过程分批COMMIT海量数据分批提交 海量数据分批提交 DROP PROCEDURE IF EXISTS proc_role; CREATE PROCEDURE proc_role ( ) BEGIN DECLARE done int DEFAULT FALSE; DECLARE v_cnt int DEFAULT 0; DECLARE v_...
  • mysql游标批量操作

    2021-02-02 01:39:51
    BEGINDECLARE _uuid VARCHAR(60);DECLARE _insertNum INT DEFAULT 0;DECLARE done int DEFAULT 0;#定义游标DECLARE rs_cursor CURSOR FORSELECT uuid,(10-IFNULL(rateNum,0)) as insertNum from product pLEFT JOIN...
  • 今天小编就为大家分享一篇关于MyBatis通过BATCH批量提交的方法,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧
  • 【转载原因:亲测提升了100倍左右】 ...昨天研究了一下mysql的批处理,最初发现很慢 10000条 每次1000 243秒 50000条 每次500条 1295秒 代码如下: package main; import java.sql.*; public class JdbcS...
  • php – mysql提交和事务

    2021-01-19 20:24:27
    如果启用了自动提交,InnoDB会自动封闭事务中的每个SQL查询,这相当于START TRANSACTION;查询;承诺;.如果在InnoDB中明确使用启动自动提交的START TRANSACTION,那么在START TRANSACTION语句之后执行的任何查询都将被...
  • 经常需要定期对某些表删除历史数据,通常这样的表的数据又是非常巨大,为了减轻对...下面是MySQL版本: 1 delimiter $$ 2 drop procedure if exists proc_delete_old_data; 3 create procedure proc_delet...
  • 即 pyspark,通过 spark sql 从 hive 或 mysql 等存储层读取相关数据,对应 spark 的 dataframe 对象,经过一系列的数据变化操作后(若需要),将最终数据通过 pipeline,并利用 spark 的分布式形式分批写入 ...
  • 提交难点一.给leader进程带来了不公平二.兼顾redo和binlog顺序的对应三.事务redo与binlog的写流程与fsync时机(没有引进组提交时的流程)四.为什么要组提交?(简单组提交下的弊病,硬件资源速度的不一致性,带来的...
  • MyBatis 针对大批量数据分批提交更新

    千次阅读 2020-02-29 16:38:22
    在使用 Mybatis 进行数据批量新增的时候,有时候数据过大时需要进行分批处理。这个时候就需要一些特殊的处理了。 以下代码,博主未做测试,仅用作收藏,方便以后使用。 public void saveTemp(List<...
  • 主要介绍了Java实现批量向mysql写入数据的方法,涉及java基于JDBC连接mysql数据库及写入数据的相关操作技巧,非常简单实用,需要的朋友可以参考下
  • int[] executeBatch() 这个方法用来执行SQL语句,这个方法会将批处理中所有SQL语句执行 mysql默认批处理是关闭的,所以我们还需要去打开mysql的批处理: 我们需要在mysql的url地址中加入一下参数: ...
  • 对于在springboot项目中用for循环与数据库的交互的过程,当数据量非常大时,为了减少与数据库的交互次数,减轻数据库的压力,需将数据做分段提交: Java的service层: for(List<OrderMessageDto> oderList ...
  • 背景一般我们数据量大的时候,然后就需要进行分页,一般...下面我们就来实验下:准备数据建一个测试表引擎为MyISAM(插入数据没有事务提交,插入速度快)的表。​CREATE TABLE USER (id INT ( 20 ) NOT NULL auto_incr...
  • } 但当member中数据量达到百万条时,说会出现执行超时的错误: Fatal error: Maximum execution time of 30 seconds exceeded in D: 那么怎么现实分批处理呢,比如前1000条数据处理结束后,再处理1001至2000条,然后...
  • DROP PROCEDURE IF EXISTS big_table_update; CREATE PROCEDURE big_table_update() BEGIN DECLARE v_primary_key_start, v_primary_key_end BIGINT(20) DEFAULT 0; DECLARE v_update_limit INT(10) DEFAULT 50000...
  • 测试目的:验证总记录数下,每次提交多条记录,与一条记录提交一次,性能的差异。测试步骤:1、创建测试表:create table tcommittable(t_id int(10),t_name varchar(20),PRIMARY KEY(t_id));2、分批次提交事务过程...
  • MySql大批量插入数据的方法

    千次阅读 2021-01-18 18:43:30
    1. 使用事务来插入:使用拼接sql的方法,在批量插入的时候,mysql支持这种语法insert into table values (a,b,c),(a1,b1,c1),...这种方法在拼接字符串之后,执行sql语句都会在一个事务当中执行查询网上的说法,每个...
  • 幸运的是,MySQL提供了一些方法用于批量录入数据,使得向表中添加数据变得容易了。本节以及下一节,将介绍这些方法。本节将介绍SQL语言级的解决方法。1、基本语法语法:LOAD DATA [LOCAL] INFILE 'file_name.txt' ...
  • mysql默认的sql语句的最大限制是1M(mysql5.7的客户端默认是16M,服务端默认是4M),可以根据设置查看。官方解释是适当增大 max_allowed_packet 参数可以使client端到server端传递大数据时,系统能够分配更多的扩展...
  • 您需要使用mysqli extension才能使用此功能.$link = mysqli_connect("localhost", "my_user", "my_password", "world");/* check connection */if (mysqli_connect_errno()) {printf("Connect failed: %s\n", mysqli...
  • 上面,你可以适当调整$splitNum的大小,比如调整为50,只要你的内存、bulk_insert_buffer_size、max_allowed_packet足够大,这点数值也可以调整为1000,这样,就是1000条记录分为1次提交到数据库,SQL插入速度立马...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,641
精华内容 1,056
关键字:

mysql 分批提交

mysql 订阅