精华内容
下载资源
问答
  • 需求:将文本文件中包含的一千万int型id数据插入mysql中,并求得出现频率最高的前10条。 文本文件:http://pan.baidu.com/s/1gd08g3D。内容是一行一个int型id。 本文只探讨mysql插入速度。‍‍分别从‍‍数据库‍...

    需求:将文本文件中包含的一千万int型id数据插入mysql中,并求得出现频率最高的前10条。

    文本文件:http://pan.baidu.com/s/1gd08g3D。内容是一行一个int型id。

    本文只探讨mysql插入速度。分别从数据库代码两方面进行总结。




    • 首先有表test:

    CREATE TABLE `test` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `testId` INT NULL,
      PRIMARY KEY (`id`),
      INDEX `testId` (`testId` ASC));


    • 最基本的实现方式:

    import java.io.BufferedReader;
    import java.io.File;
    import java.io.FileReader;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.Statement;
    
    public class InserterBasic {
    	
    	public static void main(String[] args) throws Exception {
    		File file = new File("D://Downloads//BaiduYunDownload//id.txt");
    		BufferedReader reader = null;
    		reader = new BufferedReader(new FileReader(file));
    		String tempString = null;
    		
    		Class.forName("com.mysql.jdbc.Driver");
    		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
    		Statement statement = con.createStatement();
    		
    		while ((tempString = reader.readLine()) != null) {
    			statement.execute("insert into test (testId) value ("+tempString+")");
    		}
    		
    		statement.close();
    		con.close();
    		reader.close();
    	}
    }

    这是随手即可写出的最简单实现。但它面临着几个问题:首先读文件代码和JDBC代码混淆在一起,其次无法检测插入速度不利于对比。


    • 加入速度检测代码

    使用LinkedList作为一个queue,将读出的文件中数据读入其中

    再通过Timer不断的检测这个queue的长度,以此来确认入库速度。

    import java.io.BufferedReader;
    import java.io.File;
    import java.io.FileReader;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.Statement;
    import java.util.LinkedList;
    import java.util.Queue;
    import java.util.TimerTask;
    
    public class InserterControl {
    	//包含一千万数据的容器
    	static Queue<String> queue = new LinkedList<String>();
    	static int prevSize = 0;
    	
    	public static void main(String[] args) throws Exception {
    		InserterControl inserter = new InserterControl();
    		inserter.read();
    		inserter.control();
    		inserter.insert();
    	}
    	
    	/**
    	 * 监控
    	 */
    	void control(){
    		new java.util.Timer().schedule(new TimerTask() {
    			@Override
    			public void run() {
    				int thisSize = queue.size();
    				System.out.println((prevSize-thisSize)/10+"条/秒");
    				prevSize = thisSize;
    			}
    		}, 0, 1000*10);		//每10秒钟统计一次入库速度
    	}
    	
    	/**
    	 * 读文件
    	 * @throws Exception
    	 */
    	void read() throws Exception{
    		File file = new File("D://Downloads//BaiduYunDownload//id.txt");
    		BufferedReader reader = null;
    		reader = new BufferedReader(new FileReader(file));
    		String tempString = null;
    		while ((tempString = reader.readLine()) != null) {
    			queue.offer(tempString);
    		}
    		prevSize = queue.size();
    		reader.close();
    	}
    	
    	/**
    	 * 入库
    	 * @throws Exception
    	 */
    	void insert() throws Exception {
    		Class.forName("com.mysql.jdbc.Driver");
    		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
    		Statement statement = con.createStatement();
    		while(!queue.isEmpty()){
    			statement.execute("insert into test (testId) value ("+queue.poll()+")");
    		}
    		statement.close();
    		con.close();
    	}
    }

    输出前10条结果:

    39条/秒
    37条/秒
    38条/秒
    37条/秒
    35条/秒
    29条/秒
    25条/秒
    36条/秒
    35条/秒
    35条/秒

    平均速度:34.5条/秒。粗略估计80小时候完成。结果令人沮丧。



    /**对数据库参数进行调整***************************************************************************************/

    • 调整 innodb_flush_log_at_trx_commit=0。重启。truncate test;

    (其实直接将存储引擎改成MyISAM,速度可直接到达9000条/秒~10000条/秒)

    再来一次:

    8764条/秒
    9128条/秒
    7439条/秒
    6129条/秒
    7578条/秒
    6711条/秒
    7105条/秒
    6754条/秒
    7175条/秒
    6855条/秒

    平均速度:7363.8条/秒,预计20分钟即可完成。效果拔群,轻而易举的就将速度翻了210倍。


    • 删除索引

    此表在建表之初加入索引,是为了提高其后的组函数查询效率,但是在如果只考虑插入数据的速度,索引显示是个累赘。试试把索引删掉会如何。此处删除testId上索引的同时也删除了主键。使用java代码来模拟自增主键:

    static int id = 0;
    synchronized static int getId(){
        return id++;
    }
    
    //或者是使用java.util.concurrent.atomic.AtomicInteger进行id的自增
    static AtomicInteger id = new AtomicInteger(0);
    truncate test;
    
    ALTER TABLE `test` 
    CHANGE COLUMN `id` `id` INT(11) NULL ,
    DROP INDEX `testId` ,
    DROP PRIMARY KEY;

    输出:

    平均速度:8150.6条/秒。每秒写入增加近1000。相信这个差距会随着数据量的扩充而不断增大。





    /**java代码方面*******************************************************************************************/

    • 禁用自动提交

    con.setAutoCommit(false);
    
    //...
    
    int i=0;
    while(!queue.isEmpty()){
    	if(i%30000==0){
    		con.commit();
    	}
    	i++;
    	//...

    输出:

    平均速度:8635.1条/秒。提升近500。


    • 多线程

    使用多个线程,模拟多连接。

    //
    static Queue<String> queue = new ConcurrentLinkedQueue<String>();
    
    void doInsert(){
    	for(int i=0; i<10; i++){
    		new Thread(){
    			@Override
    			public void run() {
    				try {
    					insert();
    				} catch (Exception e) {
    					e.printStackTrace();
    				}
    			};
    		}.start();
    	}
    }

    输出:

    18823条/秒
    17953条/秒
    18485条/秒
    18511条/秒
    18473条/秒
    16979条/秒
    16091条/秒
    17270条/秒
    17041条/秒
    17777条/秒

    平均速度:15891条/秒。又有了进一倍的提升。




    /************************************************************************************************/

    总结:

    1. 经过上述调整,插入速度提升了450倍左右。十分钟左右即可完成。喜大普奔。

    2. innodb_flush_log_at_trx_commit=0对InnoDB的插入速度影响显著。需要深入研究。

    3. 诸如此类统计需求,更好的选择是MyISAM。



    转载于:https://my.oschina.net/roadom/blog/380840

    展开全文
  • 现每个月有一张EXCEL表,数据量大约在800W行左右,需要按分公司每月导入数据库(有6家分公司,数据加在一起需要导入的有5000万条)。其中还有各种逻辑,需要按记录中的某字段取出比例信息进行计算,计算结果与原表中...
  • 需求:将文本文件中包含的一千万int型id数据插入mysql中,并求得出现频率最高的前10条。本文只探讨mysql插入速度。‍‍分别从‍‍数据库‍‍和‍‍代码‍‍两方面进行总结。‍‍首先有表test:CREATETABLE`test`(`id...

    需求:将文本文件中包含的一千万int型id数据插入mysql中,并求得出现频率最高的前10条。

    本文只探讨mysql插入速度。‍‍分别从‍‍数据库‍‍和‍‍代码‍‍两方面进行总结。‍‍

    首先有表test:

    CREATE TABLE `test` (

    `id` INT NOT NULL AUTO_INCREMENT,

    `testId` INT NULL,

    PRIMARY KEY (`id`),

    INDEX `testId` (`testId` ASC));

    最基本的实现方式:

    import java.io.BufferedReader;

    import java.io.File;

    import java.io.FileReader;

    import java.sql.Connection;

    import java.sql.DriverManager;

    import java.sql.Statement;

    public class InserterBasic {

    public static void main(String[] args) throws Exception {

    File file = new File("D://Downloads//BaiduYunDownload//id.txt");

    BufferedReader reader = null;

    reader = new BufferedReader(new FileReader(file));

    String tempString = null;

    Class.forName("com.mysql.jdbc.Driver");

    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");

    Statement statement = con.createStatement();

    while ((tempString = reader.readLine()) != null) {

    statement.execute("insert into test (testId) value ("+tempString+")");

    }

    statement.close();

    con.close();

    reader.close();

    }

    }

    这是随手即可写出的最简单实现。但它面临着几个问题:首先读文件代码和JDBC代码混淆在一起,其次无法检测插入速度不利于对比。

    加入速度检测代码

    使用LinkedList作为一个queue,将读出的文件中数据读入其中

    再通过Timer不断的检测这个queue的长度,以此来确认入库速度。

    import java.io.BufferedReader;

    import java.io.File;

    import java.io.FileReader;

    import java.sql.Connection;

    import java.sql.DriverManager;

    import java.sql.Statement;

    import java.util.LinkedList;

    import java.util.Queue;

    import java.util.TimerTask;

    public class InserterControl {

    //包含一千万数据的容器

    static Queue queue = new LinkedList();

    static int prevSize = 0;

    public static void main(String[] args) throws Exception {

    InserterControl inserter = new InserterControl();

    inserter.read();

    inserter.control();

    inserter.insert();

    }

    /**

    * 监控

    */

    void control(){

    new java.util.Timer().schedule(new TimerTask() {

    @Override

    public void run() {

    int thisSize = queue.size();

    System.out.println((prevSize-thisSize)/10+"条/秒");

    prevSize = thisSize;

    }

    }, 0, 1000*10);//每10秒钟统计一次入库速度

    }

    /**

    * 读文件

    * @throws Exception

    */

    void read() throws Exception{

    File file = new File("D://Downloads//BaiduYunDownload//id.txt");

    BufferedReader reader = null;

    reader = new BufferedReader(new FileReader(file));

    String tempString = null;

    while ((tempString = reader.readLine()) != null) {

    queue.offer(tempString);

    }

    prevSize = queue.size();

    reader.close();

    }

    /**

    * 入库

    * @throws Exception

    */

    void insert() throws Exception {

    Class.forName("com.mysql.jdbc.Driver");

    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");

    Statement statement = con.createStatement();

    while(!queue.isEmpty()){

    statement.execute("insert into test (testId) value ("+queue.poll()+")");

    }

    statement.close();

    con.close();

    }

    }

    输出前10条结果:

    39条/秒

    37条/秒

    38条/秒

    37条/秒

    35条/秒

    29条/秒

    25条/秒

    36条/秒

    35条/秒

    35条/秒

    平均速度:34.5条/秒。粗略估计80小时候完成。结果令人沮丧。

    /**对数据库参数进行调整***************************************************************************************/

    调整 innodb_flush_log_at_trx_commit=0。重启。truncate test;

    (其实直接将存储引擎改成MyISAM,速度可直接到达9000条/秒~10000条/秒)

    再来一次:

    8764条/秒

    9128条/秒

    7439条/秒

    6129条/秒

    7578条/秒

    6711条/秒

    7105条/秒

    6754条/秒

    7175条/秒

    6855条/秒

    平均速度:7363.8条/秒,预计20分钟即可完成。效果拔群,轻而易举的就将速度翻了210倍。

    删除索引

    此表在建表之初加入索引,是为了提高其后的组函数查询效率,但是在如果只考虑插入数据的速度,索引显示是个累赘。试试把索引删掉会如何。此处删除testId上索引的同时也删除了主键。使用java代码来模拟自增主键:

    static int id = 0;

    synchronized static int getId(){

    return id++;

    }

    //或者是使用java.util.concurrent.atomic.AtomicInteger进行id的自增

    static AtomicInteger id = new AtomicInteger(0);

    truncate test;

    ALTER TABLE `test`

    CHANGE COLUMN `id` `id` INT(11) NULL ,

    DROP INDEX `testId` ,

    DROP PRIMARY KEY;

    输出:

    平均速度:8150.6条/秒。每秒写入增加近1000。相信这个差距会随着数据量的扩充而不断增大。

    /**java代码方面*******************************************************************************************/

    禁用自动提交

    con.setAutoCommit(false);

    //...

    int i=0;

    while(!queue.isEmpty()){

    if(i%30000==0){

    con.commit();

    }

    i++;

    //...

    输出:

    平均速度:8635.1条/秒。提升近500。

    多线程

    使用多个线程,模拟多连接。

    //

    static Queue queue = new ConcurrentLinkedQueue();

    void doInsert(){

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

    new Thread(){

    @Override

    public void run() {

    try {

    insert();

    } catch (Exception e) {

    e.printStackTrace();

    }

    };

    }.start();

    }

    }

    输出:

    18823条/秒

    17953条/秒

    18485条/秒

    18511条/秒

    18473条/秒

    16979条/秒

    16091条/秒

    17270条/秒

    17041条/秒

    17777条/秒

    平均速度:15891条/秒。又有了进一倍的提升。

    /************************************************************************************************/

    总结:

    1. 经过上述调整,插入速度提升了450倍左右。十分钟左右即可完成。喜大普奔。

    2.innodb_flush_log_at_trx_commit=0对InnoDB的插入速度影响显著。需要深入研究。

    3. 诸如此类统计需求,更好的选择是MyISAM。

    展开全文
  • 需求:将文本文件中包含的一千万int型id数据插入mysql中,并求得出现频率最高的前10条。文本文件:http://pan.baidu.com/s/1gd08g3D。内容是一行一个int型id。本文只探讨mysql插入速度。‍‍分别从‍‍数据库‍‍和...

    需求:将文本文件中包含的一千万int型id数据插入mysql中,并求得出现频率最高的前10条。

    文本文件:http://pan.baidu.com/s/1gd08g3D。内容是一行一个int型id。

    本文只探讨mysql插入速度。‍‍分别从‍‍数据库‍‍和‍‍代码‍‍两方面进行总结。‍‍

    首先有表test:

    CREATE TABLE `test` (

    `id` INT NOT NULL AUTO_INCREMENT,

    `testId` INT NULL,

    PRIMARY KEY (`id`),

    INDEX `testId` (`testId` ASC));

    最基本的实现方式:

    import java.io.BufferedReader;

    import java.io.File;

    import java.io.FileReader;

    import java.sql.Connection;

    import java.sql.DriverManager;

    import java.sql.Statement;

    public class InserterBasic {

    public static void main(String[] args) throws Exception {

    File file = new File("D://Downloads//BaiduYunDownload//id.txt");

    BufferedReader reader = null;

    reader = new BufferedReader(new FileReader(file));

    String tempString = null;

    Class.forName("com.mysql.jdbc.Driver");

    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");

    Statement statement = con.createStatement();

    while ((tempString = reader.readLine()) != null) {

    statement.execute("insert into test (testId) value ("+tempString+")");

    }

    statement.close();

    con.close();

    reader.close();

    }

    }

    这是随手即可写出的最简单实现。但它面临着几个问题:首先读文件代码和JDBC代码混淆在一起,其次无法检测插入速度不利于对比。

    加入速度检测代码

    使用LinkedList作为一个queue,将读出的文件中数据读入其中

    再通过Timer不断的检测这个queue的长度,以此来确认入库速度。

    import java.io.BufferedReader;

    import java.io.File;

    import java.io.FileReader;

    import java.sql.Connection;

    import java.sql.DriverManager;

    import java.sql.Statement;

    import java.util.LinkedList;

    import java.util.Queue;

    import java.util.TimerTask;

    public class InserterControl {

    //包含一千万数据的容器

    static Queuequeue = new LinkedList();

    static int prevSize = 0;

    public static void main(String[] args) throws Exception {

    InserterControl inserter = new InserterControl();

    inserter.read();

    inserter.control();

    inserter.insert();

    }

    /**

    * 监控

    */

    void control(){

    new java.util.Timer().schedule(new TimerTask() {

    @Override

    public void run() {

    int thisSize = queue.size();

    System.out.println((prevSize-thisSize)/10+"条/秒");

    prevSize = thisSize;

    }

    }, 0, 1000*10);//每10秒钟统计一次入库速度

    }

    /**

    * 读文件

    * @throws Exception

    */

    void read() throws Exception{

    File file = new File("D://Downloads//BaiduYunDownload//id.txt");

    BufferedReader reader = null;

    reader = new BufferedReader(new FileReader(file));

    String tempString = null;

    while ((tempString = reader.readLine()) != null) {

    queue.offer(tempString);

    }

    prevSize = queue.size();

    reader.close();

    }

    /**

    * 入库

    * @throws Exception

    */

    void insert() throws Exception {

    Class.forName("com.mysql.jdbc.Driver");

    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");

    Statement statement = con.createStatement();

    while(!queue.isEmpty()){

    statement.execute("insert into test (testId) value ("+queue.poll()+")");

    }

    statement.close();

    con.close();

    }

    }

    输出前10条结果:

    39条/秒

    37条/秒

    38条/秒

    37条/秒

    35条/秒

    29条/秒

    25条/秒

    36条/秒

    35条/秒

    35条/秒

    平均速度:34.5条/秒。粗略估计80小时候完成。结果令人沮丧。

    /**对数据库参数进行调整***************************************************************************************/

    调整 innodb_flush_log_at_trx_commit=0。重启。truncate test;

    (其实直接将存储引擎改成MyISAM,速度可直接到达9000条/秒~10000条/秒)

    再来一次:

    8764条/秒

    9128条/秒

    7439条/秒

    6129条/秒

    7578条/秒

    6711条/秒

    7105条/秒

    6754条/秒

    7175条/秒

    6855条/秒

    平均速度:7363.8条/秒,预计20分钟即可完成。效果拔群,轻而易举的就将速度翻了210倍。

    删除索引

    此表在建表之初加入索引,是为了提高其后的组函数查询效率,但是在如果只考虑插入数据的速度,索引显示是个累赘。试试把索引删掉会如何。此处删除testId上索引的同时也删除了主键。使用java代码来模拟自增主键:

    static int id = 0;

    synchronized static int getId(){

    return id++;

    }

    //或者是使用java.util.concurrent.atomic.AtomicInteger进行id的自增

    static AtomicInteger id = new AtomicInteger(0);

    truncate test;

    ALTER TABLE `test`

    CHANGE COLUMN `id` `id` INT(11) NULL ,

    DROP INDEX `testId` ,

    DROP PRIMARY KEY;

    输出:

    平均速度:8150.6条/秒。每秒写入增加近1000。相信这个差距会随着数据量的扩充而不断增大。

    /**java代码方面*******************************************************************************************/

    禁用自动提交

    con.setAutoCommit(false);

    //...

    int i=0;

    while(!queue.isEmpty()){

    if(i%30000==0){

    con.commit();

    }

    i++;

    //...

    输出:

    平均速度:8635.1条/秒。提升近500。

    多线程

    使用多个线程,模拟多连接。

    //

    static Queuequeue = new ConcurrentLinkedQueue();

    void doInsert(){

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

    new Thread(){

    @Override

    public void run() {

    try {

    insert();

    } catch (Exception e) {

    e.printStackTrace();

    }

    };

    }.start();

    }

    }

    输出:

    18823条/秒

    17953条/秒

    18485条/秒

    18511条/秒

    18473条/秒

    16979条/秒

    16091条/秒

    17270条/秒

    17041条/秒

    17777条/秒

    平均速度:15891条/秒。又有了进一倍的提升。

    /************************************************************************************************/

    总结:

    1. 经过上述调整,插入速度提升了450倍左右。十分钟左右即可完成。喜大普奔。

    2.innodb_flush_log_at_trx_commit=0对InnoDB的插入速度影响显著。需要深入研究。

    3. 诸如此类统计需求,更好的选择是MyISAM。

    展开全文
  • 我们的产品目标客户主要是学校,根据产品经理给的业务需求,决定按学期分表,即按主表数据入库时间按学期存放到对应分表,将原来超大的主表拆分成无数的小表,拆分后单表最大数据量为300万。 经过一段时间的考察研究...


    前言

    之前在公司开发的一个产品,数据量巨大,疫情期间更是单月数据量增长超过100万,我们的单机MySQL数据库查询速度已经完全力不从心了,于是走上了分表的道路。我们的产品目标客户主要是学校,根据产品经理给的业务需求,决定按学期分表,即按主表数据入库时间按学期存放到对应分表,将原来超大的主表拆分成无数的小表,拆分后单表最大数据量为300万。
    经过一段时间的考察研究决定使用ShardingJDBC来简化分表后的业务开发工作,ShardingJDBC的主要作用是根据设定好的路由规则将SQL语句路由到对应的数据库及分表上,由于我们使用的是单库分表,因此只涉及表的路由。在做业务开发的过程中踩过一些坑,也有了一些经验,于是在这里把代码抽取出来做成Demo来做一个分享。


    提示:以下是本篇文章正文内容,下面案例可供参考

    一、框架搭建

    先介绍一下Demo使用的框架和版本,主要是SpringBoot全家桶2.2.6版,MyBatisPlus的2.3.3版以及我们今天的主角ShardingJDBC的4.0.1版本。
    主要说下ShardingJDBC框架的集成与配置,首先在pom.xml引入依赖:

    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>${sharding-sphere.version}</version>
    </dependency>
    

    接下来在application.yml中加入配置:

    spring:
      shardingsphere:
        datasource:
          names: ds
          ds:
            type: com.zaxxer.hikari.HikariDataSource
            driver-class-name: com.mysql.jdbc.Driver
            jdbc-url: jdbc:mysql://localhost:3306/shardingjdbc?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&tinyInt1isBit=false
            username: root
            password: admin123
        sharding:
          tables:
            t_order:
              actual-data-nodes: ds.t_order_$->{2020..2021}_$->{1..12}
              table-strategy:
                standard:
                  sharding-column: create_time
                  precise-algorithm-class-name: io.ian.demo.core.shardingjdbc.DatePreciseShardingAlgorithm
                  range-algorithm-class-name: io.ian.demo.core.shardingjdbc.DateRangeShardingAlgorithm
            t_order_item:
              actual-data-nodes: ds.t_order_item_$->{2020..2021}_$->{1..12}
              table-strategy:
                standard:
                  sharding-column: order_create_time
                  precise-algorithm-class-name: io.ian.demo.core.shardingjdbc.DatePreciseShardingAlgorithm
                  range-algorithm-class-name: io.ian.demo.core.shardingjdbc.DateRangeShardingAlgorithm
          binding-tables:
            - t_order,t_order_item
          defaultDataSourceName: ds
        props:
          sql:
            show: true
    

    由于我们是单库分表,因此只配置一个数据源ds。主要说下表的配置,先看表结构:

    t_order

    t_order_item
    t_order和t_order_item表以order_id字段关联,为一对多关系。t_order_item表的order_create_time字段值与关联的t_order表的create_time值相同,create_time字段和order_create_time字段分别为两表的分表字段。在ShardingJDBC中t_order和t_order_item表均为逻辑表,实际的表是例如t_order_2020_6这种带月后缀的表,这里我们为了简化分表逻辑进行了按月分表。具体的配置方式可以参考官方配置手册

    二、代码编写

    下面我们着重看一下自定义的分表逻辑类DatePreciseShardingAlgorithm和DateRangeShardingAlgorithm:

    import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
    import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
    
    import java.util.Collection;
    import java.util.Date;
    
    public class DatePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
    
        @Override
        public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> preciseShardingValue) {
            Date date = preciseShardingValue.getValue();
            String suffix = ShardingUtils.getSuffixByYearMonth(date);
            for (String tableName : availableTargetNames) {
                if (tableName.endsWith(suffix)) {
                    return tableName;
                }
            }
            throw new IllegalArgumentException("未找到匹配的数据表");
        }
    }
    

    DatePreciseShardingAlgorithm实现了PreciseShardingAlgorithm接口,用于条件精确匹配时的表路由(=和in)。availableTargetNames就是我们在配置中配的actual-data-nodes,ds.t_order_$->{2020…2021}_$->{1…12}表示实际表为ds.t_order_2020_1到ds.t_order_2021_12共两年24张表。preciseShardingValue.getValue()获取到的是我们执行sql传递的时间参数,我们在这里用Date去做查询条件,不要使用String的日期。

    import com.google.common.collect.Range;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
    import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
    
    import java.util.*;
    
    @Slf4j
    public class DateRangeShardingAlgorithm implements RangeShardingAlgorithm<Date> {
    
        @Override
        public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> rangeShardingValue) {
            List<String> list = new ArrayList<>();
            log.info("availableTargetNames : " + availableTargetNames);
            log.info(rangeShardingValue.toString());
            Range<Date> valueRange = rangeShardingValue.getValueRange();
            Date lowerDate = valueRange.lowerEndpoint();
            Date upperDate = valueRange.upperEndpoint();
            String lowerSuffix = ShardingUtils.getSuffixByYearMonth(lowerDate);
            String upperSuffix = ShardingUtils.getSuffixByYearMonth(upperDate);
            TreeSet<String> suffixList = ShardingUtils.getSuffixListForRange(lowerSuffix, upperSuffix);
            for (String tableName : availableTargetNames) {
                if (containTableName(suffixList, tableName)) {
                    list.add(tableName);
                }
            }
            log.info("match tableNames-----------------------" + list.toString());
            return list;
        }
    
        private boolean containTableName(Set<String> suffixList, String tableName) {
            boolean flag = false;
            for (String s : suffixList) {
                if (tableName.endsWith(s)) {
                    flag = true;
                    break;
                }
            }
            return flag;
        }
    }
    

    DateRangeShardingAlgorithm 实现了RangeShardingAlgorithm接口,用于条件范围匹配时的路由(between、<、>等)。valueRange.lowerEndpoint()和valueRange.upperEndpoint()分别获取范围的上下边接,我们这两个时间的跨度去计算目标表的后缀,然后在可用的表中选取路由的目标表。

    下面我们来看下增删改查的业务代码与不分表时有哪些变化。

    新增t_order数据时我们给createTime赋值,在执行sql时会触发DatePreciseShardingAlgorithm中的精确匹配,直接把数据插入到createTime对应的分表,在代码上完全没有任何改动。
    修改、单条删除、单条查询在分表前查询条件只需要id主键即可,但在分表后我们应该加上时间参数,可以是精确时间也可以是时间段,这样才能快速找到目标表,否则会在所有分表执行sql导致性能十分低下。

    最麻烦的分页查询

    首先希望大家能看下官方文档针对分页的章节
    我们为了获得总数据条数使用的count语句将会对所有分表进行查询然后归并结果,根据笔者在产品开发中的实际经验,当数据量到百万千万级别时,这个查询耗费的时间是不可接受的,因此在数据量巨大时,传统的分页查询方式是不可取的。为了解决这个问题从业务上我们可以不展示总数据量和总页数,而改用无限下拉滚动的方式来展示分页数据,但如果产品一定要展示呢?由于我们是按时间分表,因此从业务上看,除了当前月,其他的分表数据总量应该已经固定不再增长了,因此可以把当前月之前月份的数据总数做统计并存入中间表,这个数据再加上当前月的分表数据量就可以算出总数据量了,有了总数计算总页数也就简单了。但事实上我们的需求一般不会是对所有数据进行分页查询,而是有条件的对数据进行分页查询,比如本例中就是根据用户名进行订单的分页查询,那么我们的总数统计就应该按用户名去统计存入中间表。

    下面简单介绍一下无限下拉滚动分页查询的实现:

    /**
     * 从第二页开始lastCreateTime和lastRowNum为必选参数
     */
    @ApiOperation(value = "无限滚动分页查询", httpMethod = "GET")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "lastCreateTime", value = "上一页最后一条数据的创建时间", dataType = "String", paramType = "query"),
            @ApiImplicitParam(name = "lastRowNum", value = "上一页最后一条数据的行号", dataType = "int", paramType = "query"),
            @ApiImplicitParam(name = "size", value = "每页条数", dataType = "int", paramType = "query"),
            @ApiImplicitParam(name = "userName", value = "用户名", dataType = "String", paramType = "query")
    })
    @GetMapping("/listPageByTime")
    public ResponseEntity<RestResult> listPageByTime(String lastCreateTime, Integer lastRowNum, Integer size, String userName) {
        return ResponseEntity.ok(RestResult.getSuccessRestResult(orderService.listPageByTime(lastCreateTime, lastRowNum, size, userName)));
    }
    

    查询第一页数据时只需要提供每页条数参数即可,如果需要根据用户名查询可以加入用户名参数。从第二页开始就需要传入上一页最后一条数据的创建时间,以及上一页最后一条数据的行号。

    @Override
    public List<Order> listPageByTime(String lastCreateTime, Integer lastRowNum, Integer size, String userName) {
        //lastCreateTime 有助于快速定位当前查询的分表 ,如果是第一页则可不传,默认使用当前时间
        Date date = StringUtils.isBlank(lastCreateTime) ? new Date() : DateUtils.parseTime(lastCreateTime);
        String suffix = ShardingUtils.getSuffixByYearMonth(date);
        int resultSize = size == null ? 10 : size;
        //rowNum用于获取当前页数据的起始位置,如果是第一页可以不传,默认为0
        int rowNum = lastRowNum == null ? 0 : lastRowNum;
        List<Order> orderList = baseMapper.listByRowNum(suffix, resultSize, rowNum, userName);
        if (orderList.size() > 0) {
            while (orderList.size() < resultSize) { //查询出的数据不足 找更早的分表补足
                if ("2020_6".equals(suffix)) {    //假设最早的分表为 t_order_2020_6
                    break;
                }
                suffix = ShardingUtils.getPrevSuffix(suffix);
                List<Order> tempOrderList = baseMapper.listByRowNum(suffix, resultSize - orderList.size(), 0, userName);
                if (tempOrderList.size() > 0) {
                    orderList.addAll(tempOrderList);
                }
            }
            //获取orderList中数据的时间范围 查询子表数据
            Wrapper<OrderItem> orderItemWrapper = new EntityWrapper<OrderItem>()
                    .between("order_create_time", orderList.get(orderList.size() - 1).getCreateTime(), orderList.get(0).getCreateTime());
            this.appendOrderItem(orderList, orderItemWrapper);
        }
        return orderList;
    }
    

    上一页的最后一条数据的创建时间用于计算上次查询结束时数据所在的分表,此次查询也从该分表开始。如果该分表剩余数据不足每页条数,则继续查上一个月的分表,直到查询出的数据达到每页条数或已超过最早月份的分表。

    SELECT
        A.*
    FROM
        (
        SELECT
            @rownum := @rownum + 1 AS rowNum,
            t.*
        FROM
            t_order_${suffix} t,
            ( SELECT @rownum := 0 ) r
        <trim prefix="WHERE" prefixOverrides="AND|OR">
            <if test="userName != null and userName != ''">
               AND t.user_name = #{userName}
            </if>
        </trim>
        ORDER BY
            create_time DESC,id
        ) A
    WHERE
        A.rowNum > #{rowNum}
        LIMIT #{size}
    

    从上面SQL语句可以看出,行号指的是当前分表里数据排序后的序号。因此我们可以根据上一页最后一条数据行号找到当前页的数据起始位置。
    该分页查询方式经过实际测试在单分表数据量超过百万,总数据量超过千万级别时仍然可以在1秒内完成请求响应,当然查询条件需要建立索引。


    三、题外话

    强烈建议在数据量特别巨大的表使用长整型作为主键数据类型,在数据量超过百万同时用长整型数据作为外键查询时,在都建立索引的情况下,查询速度比字符类型外键快上十倍到百倍!当然长整型作为主键在传递到前端时会有JS精度丢失问题,这个问题可以通过转字符串传递解决。

    源码下载

    觉得有用的朋友顺手给个STAR吧

    展开全文
  • 对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,可能每天花费在数据导入上的时间就会长达几个小时之久。因此,优化数据库插入性能是很有意义的。  网络上...
  • 转自:https://blog.csdn.net/h330531987/article/details/76039795对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,可能每天花费在数据导入上的时间就会...
  • 在实际应用中,我们经常遇到千万级,甚至更大的数据量。如果没有一个快速的插入方法,则会事倍功半,花费大量的时间。在参加阿里的天池大数据算法竞赛中(流行音乐趋势预测),我遇到了这样的问题,在没有优化数据库...
  • Neo4j图数据库千万级节点数据加载方式对比: 如果对大量数据进行初始化加载,那么就用Neo4j-import;增量数据的同步加载又不想暂停数据库服务,那就要用load csv,如果可以忍受数据库的短暂停服,那么Batch-...
  • 这里写自定义目录标题Kettle使用初体验之批量数据入库实验场景问题结论实验步骤实验一:使用文本文件作为输入... 有多个千万级的数据文件需要导入到Oracle数据库中,如何快速入库。 文件是文本CSV格式,列分隔符为&...
  • 入库2.读入数据总结 前言 在日常开发过程中我们可能会遇见这种场景,就是为了优化查询我们需要对某张表中的某个字段添加索引,但表中存在大量数据,而且正式库的表正在被当前系统所使用,我们不可能停掉服务去...
  • 数据库:mysql 操作系统:linux cpu:4 内存:32G 如表:tababc, 10个col,按时间月分区。...有没有什么好的方法能提高load data 入库的方法。 (mysql 采用的是innodb引擎,buffer给了20G左右。)
  • 千万行的数据,订单提交数据库,sql sever处理要5分多钟,如果频繁入库/取数的话.....要知道,为了支撑起业务人员的数据分析,以及日常不考虑计算逻辑和技术难度,IT人员也是要花费很大的心血...
  • 建议你们看到文末,不会亏待...近千万行的数据,订单提交数据库,sql sever处理要5分多钟,如果频繁入库/取数的话..... 要知道,为了支撑起业务人员的数据分析,以及日常不考虑计算逻辑和技术难度,IT人员也是要花...
  • 比如当多商户入驻后,order表数据超过千万级的时候,查询和更新都是按照商户ID来进行操作,那么就可以按商户ID进行分表,例如商户ID为1的交易记录存储到order_1表,同理商户ID为2的存储到order_2中,以此类推可以...
  • 数据量并发提交

    2011-10-18 14:55:41
     3、系统表数据已经很庞大,估计上千万级数据了。  4、系统架构主要是Action---&gt;Service 结构,也就是Action用于处理页面的跳转,Service层用于具体的业务处理, Action和service是1对多的...
  • 定义了批量操作的接口,基于PreparedStatement和反射原理实现方法,适用于有计划的批量导入的业务需求,例如(1)定期导入大量数据到业务表,(2)接口接收批量数据入库!经实践,几千万级的导入,可以在几分钟内完成。 ...
  • ogg 当同步某张表的源端有大量并发操作时,如INSERT UPDATE千万级数据记录操作。同步链路的入库进程不能及时的处理,将产生数据同步延时。此时可以使用OGG的range参数据将单个进程拆分为多个进程,同时读取trail文件...
  • 数据分组后,然后开启对应个数的线程,每个线程处理一个分组数据的任务(每个分组后的数据量从几十万至千万级),最初思路是从原库取出1000条数据后,再进入入库,设置了PreparedStatement 的 fetchSize为1000, ...
  • 性能优化实战

    2021-03-19 21:34:01
    主要针对系统在首次部署的时候,千万级数据的情况下,部署快,消耗内存少作为主要目标。 前期准备 (1)做性能优化首先需要工具,目前主流的阿里开源的arthars ,以及jdk自带的命令,还有收费版的jprofile,不得不...
  • 支持千万级数据迁移,支持自定义迁移列,自定义迁移数据结构 在您无法使用load或dump命令,或者所迁移表结构不同,需要对数据做处理时使用。 标准化多线程识读器,无需关注识读过程,只需关注入库前数据的处理 本地...
  • 2.负载能力强,支持千万级数据 从缓存技术、数据库设计、代码优化等多个角度入手进行优化,内容可文本存储,让信息和会员数据量支持达到了千万级。 3.前台模板风格方便制作和使用 4.支持生成Html和PHP动态访问,也...
  • 2.负载能力强,支持千万级数据 从缓存技术、数据库设计、代码优化等多个角度入手进行优化,内容可文本存储,让信息和会员数据量支持达到了千万级。 3.前台模板风格方便制作和使用 4.支持生成Html和PHP动态访问,...
  • ASP.NET性能优化探讨

    2010-07-22 22:53:00
    数据库优化 要提高数据库的运行效率,需要从... (2) 计算非常复杂、而且记录条数非常巨大时(千万条级别),要先在数据库外面以文件系统方式计算处理完成之后入库追加到数据表。 (3)数据表记录太

空空如也

空空如也

1 2
收藏数 25
精华内容 10
关键字:

千万级数据入库