利用存储过程进行大数据量查询_大量数据存储利用存储过程 - CSDN
  • mysql利用存储过程存储大数据量

    千次阅读 2017-12-29 13:19:29
    Mysql利用存储过程插入400W条数据 CREATE TABLE dept( /*部门表*/ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/ dname VARCHAR(20) NOT NULL DEFAULT "",/*名称*/ loc VARCHAR(13) NOT NULL ...

    Mysql利用存储过程插入400W条数据

    CREATE TABLE dept( /*部门表*/
     deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
     dname VARCHAR(20) NOT NULL DEFAULT "",/*名称*/
     loc VARCHAR(13) NOT NULL DEFAULT "" /*地点*/
    )ENGINE=MyISAM DEFAULT CHARSET=utf8;
    复制代码
    CREATE TABLE emp( /*EMP雇员表*/
    empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*编号*/
    ename VARCHAR(20) NOT NULL DEFAULT "",/*名字*/
      job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
      mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*是哪个及编号*/
      hiredate DATE NOT NULL,/*入职时间*/
      sal DECIMAL(7,2) NOT NULL,/*薪水*/
      comm DECIMAL(7,2) NOT NULL,/*红利*/
      deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
    )ENGINE=MyISAM DEFAULT CHARSET=utf8
    复制代码
    CREATE TABLE salgrade( /*工资级别表*/
     grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
     losal DECIMAL(17,2) NOT NULL,
     hisal DECIMAL(17,2) NOT NULL
    )ENGINE=MyISAM DEFAULT CHARSET=utf8;
    INSERT INTO salgrade VALUES (1,700,1200);
    INSERT INTO salgrade VALUES (2,1201,1400);
    INSERT INTO salgrade VALUES (3,1401,2000);
    INSERT INTO salgrade VALUES (4,2001,3000);
    INSERT INTO salgrade VALUES (5,3001,9999);
    #随机产生字符串
    #定义一个新的命令结束符
    delimiter $$
    #删除自动以函数
    drop function rand_string $$

    创建函数:

    复制代码
    #rand_string(n INT) rand_string 是函数名(n INT)  //该函数传参一个整数
    create function rand_string(n INT)
    returns varchar(255) 
    begin
    declare chars_str varchar(100) default 
     'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    declare return_str varchar(255) default '';
    declare i int default 0;
    while i < n do
     set return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));
     set i = i +1;
     end while;
    return return_str;
    end $$
    复制代码

     再来一个函数

    复制代码
    create function rand_num( )
    returns int(5)
    begin
     declare i int default 0;
     set i = floor(10+rand()*500);
    return i;
     end $$
    复制代码

     创建存储过程

    复制代码
    create procedure insert_emp(in start int(10),in max_num int(10))
    begin
    declare i int default 0;
    #set autocommit = 0 把autocommit设置成0
    set autocommit = 0;
    repeat
    set i = i +1;
    insert into emp values ((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
    until i = max_num
    end repeat;
    commit;
    end $$
    复制代码
    delimiter ;

    调用刚刚写好的函数,从100001号开始,产生4000000记录

    call insert_emp(100001,4000000);

    转载出自:https://www.cnblogs.com/wt645631686/p/6868192.html
    展开全文
  • MySQL数据库如何解决大数据量存储问题

    万次阅读 多人点赞 2014-08-25 09:11:46
    利用MySQL数据库如何解决大数据量存储问题? 各位高手您们好,我最近接手公司里一个比较棘手的问题,关于如何利用MySQL存储大数据量的问题,主要是数据库中的两张历史数据表,一张模拟量历史数据和一张开关量历史...

    利用MySQL数据库如何解决大数据量存储问题?

    各位高手您们好,我最近接手公司里一个比较棘手的问题,关于如何利用MySQL存储大数据量的问题,主要是数据库中的两张历史数据表,一张模拟量历史数据和一张开关量历史数据表,这两张表字段设计的很简单(OrderNo,Value,DataTime)。基本上每张表每天可以增加几千万条数据,我想问如何存储数据才能不影响检索速度呢?需不需要换oracle数据库呢?因为我是数据库方面的新手,希望可以说的详细一点,万分感谢!!?-0-#暂时可以先考虑用infobright 这是mysql的数据仓库解决方案如果这都满足不了需求 再考虑hadoop 

    暂时可以先考虑用infobright  这是mysql的数据仓库解决方案


    如果这都满足不了需求 再考虑hadoop



    提问:如何设计或优化千万级别的大表?此外无其他信息,个人觉得这个话题有点范,就只好简单说下该如何做,对于一个存储设计,必须考虑业务特点,收集的信息如下:
    1.数据的容量:1-3年内会大概多少条数据,每条数据大概多少字节; 

    2.数据项:是否有大字段,那些字段的值是否经常被更新; 
    3.数据查询SQL条件:哪些数据项的列名称经常出现在WHERE、GROUP BY、ORDER BY子句中等; 
    4.数据更新类SQL条件:有多少列经常出现UPDATE或DELETE 的WHERE子句中; 
    5.SQL量的统计比,如:SELECT:UPDATE+DELETE:INSERT=多少? 

    6.预计大表及相关联的SQL,每天总的执行量在何数量级? 
    7.表中的数据:更新为主的业务 还是 查询为主的业务 
    8.打算采用什么数据库物理服务器,以及数据库服务器架构? 
    9.并发如何? 
    10.存储引擎选择InnoDB还是MyISAM? 

    大致明白以上10个问题,至于如何设计此类的大表,应该什么都清楚了! 

    至于优化若是指创建好的表,不能变动表结构的话,那建议InnoDB引擎,多利用点内存,减轻磁盘IO负载,因为IO往往是数据库服务器的瓶颈 

    另外对优化索引结构去解决性能问题的话,建议优先考虑修改类SQL语句,使他们更快些,不得已只靠索引组织结构的方式,当然此话前提是, 
    索引已经创建的非常好,若是读为主,可以考虑打开query_cache, 

    以及调整一些参数值:sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size 

    更多信息参见:
    MySQL数据库服务器端核心参数详解和推荐配置
    mysqlops.com/2011/10/26
    您好,主要是检索某段时间内的模拟量值(select * from table where datatime between t1 and t2 ),目前打算使用分表,分区的方式解决

    不纸上谈兵,说一下我的思路以及我的解决,抛砖引玉了 
    我最近正在解决这个问题 
    我现在的公司有三张表,是5亿的数据,每天张表每天的增量是100w 
    每张表大概在10个columns左右 
    下面是我做的测试和对比 
    1.首先看engine,在大数据量情况下,在没有做分区的情况下 
    mysiam比innodb在只读的情况下,效率要高13%左右 
    2.在做了partition之后,你可以去读一下mysql的官方文档,其实对于partition,专门是对myisam做的优化,对于innodb,所有的数据是存在ibdata里面的,所以即使你可以看到schema变了,其实没有本质的变化 
    在分区出于同一个physical disk下面的情况下,提升大概只有1% 
    在分区在不同的physical disk下,我分到了三个不同的disks下,提升大概在3%,其实所谓的吞吐量,由很多因素决定的,比如你的explain parition时候可以看到,record在那一个分区,如果每个分区都有,其实本质上没有解决读的问题,这样只会提升写的效率。 
    另外一个问题在于,分区,你怎么分,如果一张表,有三个column都是经常被用于做查询条件的,其实是一件很悲惨的事情,因为你没有办法对所有的sql做针对性的分区,如果你只是如mysql官方文档上说的,只对时间做一个分区,而且你也只用时间查询的话,恭喜你 
    3.表主要用来读还是写,其实这个问题是不充分的,应该这样问,你在写入的时候,同时并发的查询多么?我的问题还比较简单,因为mongodb的shredding支持不能,在crush之后,还是回到mysql,所以在通常情况下,9am-9pm,写入的情况很多,这个时候我会做一个view,view是基于最近被插入或者经常被查询的,通过做view来分离读取,就是说写是在table上的,读在进行逻辑判断前是在view上操作的 
    4做一些archive table,比如先对这些大表做很多已有的统计分析,然后通过已有的分析+增量来解决 
    5如果你用mysiam,还有一个问题你要注意,如果你的.configure的时候,加了一个max index length参数的时候,当你的record数大于制定长度的时候,这个index会被disable 

    照你的需求来看,可以有两种方式,一种是分表,另一种是分区
    首先是分表,就像你自己所说的,可以按月分表,可以按用户ID分表等等,至于采用哪种方式分表,要看你的业务逻辑了,分表不好的地方就是查询有时候需要跨多个表。

    然后是分区,分区可以将表分离在若干不同的表空间上,用分而治之的方法来支撑无限膨胀的大表,给大表在物理一级的可管理性。将大表分割成较小的分区可以改善表的维护、备份、恢复、事务及查询性能。分区的好处是分区的优点:

    1 增强可用性:如果表的一个分区由于系统故障而不能使用,表的其余好的分区仍然可以使用;

    2 减少关闭时间:如果系统故障只影响表的一部分分区,那么只有这部分分区需要修复,故能比整个大表修复花的时间更少;

    3 维护轻松:如果需要重建表,独立管理每个分区比管理单个大表要轻松得多;

    4 均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能;

    5 改善性能:对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行,可使运行速度更快;

    6 分区对用户透明,最终用户感觉不到分区的存在。


     如今随着互联网的发展,数据的量级也是撑指数的增长,从GB到TB到PB。对数据的各种操作也是愈加的困难,传统的关系性数据库已经无法满足快速查询与插入数据的需求。这个时候NoSQL的出现暂时解决了这一危机。它通过降低数据的安全性,减少对事务的支持,减少对复杂查询的支持,来获取性能上的提升。但是,在有些场合NoSQL一些折衷是无法满足使用场景的,就比如有些使用场景是绝对要有事务与安全指标的。这个时候NoSQL肯定是无法满足的,所以还是需要使用关系性数据库。

      虽然关系型数据库在海量数据中逊色于NoSQL数据库,但是如果你操作正确,它的性能还是会满足你的需求的。针对数据的不同操作,其优化方向也是不尽相同。对于数据移植,查询和插入等操作,可以从不同的方向去考虑。而在优化的时候还需要考虑其他相关操作是否会产生影响。就比如你可以通过创建索引提高查询性能,但是这会导致插入数据的时候因为要建立更新索引导致插入性能降低,你是否可以接受这一降低那。所以,对数据库的优化是要考虑多个方向,寻找一个折衷的最佳方案。

      一:查询优化


      1:创建索引。

      最简单也是最常用的优化就是查询。因为对于CRUD操作,read操作是占据了绝大部分的比例,所以read的性能基本上决定了应用的性能。对于查询性能最常用的就是创建索引。经过测试,2000万条记录,每条记录200字节两列varchar类型的。当不使用索引的时候查询一条记录需要一分钟,而当创建了索引的时候查询时间可以忽略。但是,当你在已有数据上添加索引的时候,则需要耗费非常大的时间。我插入2000万条记录之后,再创建索引大约话费了几十分钟的样子。

      创建索引的弊端和场合。虽然创建索引可以很大程度上优化查询的速度,但是弊端也是很明显的。一个是在插入数据的时候,创建索引也需要消耗部分的时间,这就使得插入性能在一定程度上降低;另一个很明显的是数据文件变的更大。在列上创建索引的时候,每条索引的长度是和你创建列的时候制定的长度相同的。比如你创建varchar(100),当你在该列上创建索引,那么索引的长度则是102字节,因为长度超过64字节则会额外增加2字节记录索引的长度。


      从上图可以看到我在YCSB_KEY这一列(长度100)上创建了一个名字为index_ycsb_key的索引,每条索引长度都为102,想象一下当数据变的巨大无比的时候,索引的大小也是不可以小觑的。而且从这也可以看出,索引的长度和列类型的长度还不同,比如varchar它是变长的字符类型(请看MySQL数据类型分析),实际存储长度是是实际字符的大小,但是索引却是你声明的长度的大小。你创建列的时候声明100字节,那么索引长度就是这个字节再加上2,它不管你实际存储是多大。

      除了创建索引需要消耗时间,索引文件体积会变的越来越大之外,创建索引也需要看的你存储数据的特征。当你存储数据很大一部分都是重复记录,那这个时候创建索引是百害而无一利。请先查看MySQL索引介绍。所以,当很多数据重复的时候,索引带来的查询提升的效果是可以直接忽略的,但是这个时候你还要承受插入数据的时候创建索引带来的性能消耗。

      2:缓存的配置。

      在MySQL中有多种多样的缓存,有的缓存负责缓存查询语句,也有的负责缓存查询数据。这些缓存内容客户端无法操作,是由server端来维护的。它会随着你查询与修改等相应不同操作进行不断更新。通过其配置文件我们可以看到在MySQL中的缓存:


      在这里主要分析query cache,它是主要用来缓存查询数据。当你想使用该cache,必须把query_cache_size大小设置为非0。当设置大小为非0的时候,server会就会缓存每次查询返回的结果,到下次相同查询server就直接从缓存获取数据,而不是再执行查询。能缓存的数据量就和你的size大小设置有关,所以当你设置的足够大,数据可以完全缓存到内存,速度就会非常之快。

      但是,query cache也有它的弊端。当你对数据表做任何的更新操作(update/insert/delete)等操作,server为了保证缓存与数据库的一致性,会强制刷新缓存数据,导致缓存数据全部失效。所以,当一个表格的更新数据表操作非常多的话,query cache是不会起到查询提升的性能,还会影响其他操作的性能。

      3:slow_query_log分析。

      其实对于查询性能提升,最重要也是最根本的手段也是slow_query的设置。


      当你设置slow_query_log为on的时候,server端会对每次的查询进行记录,当超过你设置的慢查询时间(long_query_time)的时候就把该条查询记录到日志。而你对性能进行优化的时候,就可以分析慢查询日志,对慢查询的查询语句进行有目的的优化。可以通过创建各种索引,可以通过分表等操作。那为什么要分库分表那,当不分库分表的时候那个地方是限制性能的地方啊。下面我们就简单介绍。

      4:分库分表

      分库分表应该算是查询优化的杀手锏了。上述各种措施在数据量达到一定等级之后,能起到优化的作用已经不明显了。这个时候就必须对数据量进行分流。分流一般有分库与分表两种措施。而分表又有垂直切分与水平切分两种方式。下面我们就针对每一种方式简单介绍。

      对于mysql,其数据文件是以文件形式存储在磁盘上的。当一个数据文件过大的时候,操作系统对大文件的操作就会比较麻烦与耗时,而且有的操作系统就不支持大文件,所以这个时候就必须分表了。另外对于mysql常用的存储引擎是Innodb,它的底层数据结构是B+树。当其数据文件过大的时候,B+树就会从层次和节点上比较多,当查询一个节点的时候可能会查询很多层次,而这必定会导致多次IO操作进行装载进内存,肯定会耗时的。除此之外还有Innodb对于B+树的锁机制。对每个节点进行加锁,那么当更改表结构的时候,这时候就会树进行加锁,当表文件大的时候,这可以认为是不可实现的。 

      所以综上我们就必须进行分表与分库的操作。


     二:数据转移

      当数据量达到一定等级之后,那么移库将是一个非常慎重又危险的工作。在移库中保证前后数据的一致性,各种突发情况的处理,移库过程中数据的变迁,每一个都是一个非常困难的问题。

      2.1:插入数据

      当进行数据迁移的时候,肯定会存在大数据的重新导入,你可以选择直接load文件,有的时候可能就需要代码插入了。这个时候就需要对插入语句进行一定的优化了。这个时候可以使用INSERT DELAYED语句,该语句是当你发出插入请求的时候,部马上就插入到数据库而是放在缓存里面,等待时机成熟之后再进行插入。

    待补充。。。

    mysql大数据量处理

    一、概述
    分表是个目前算是比较炒的比较流行的概念,特别是在大负载的情况下,分表是一个良好分散数据库压力的好方法。
    首先要了解为什么要分表,分表的好处是什么。我们先来大概了解以下一个数据库执行SQL的过程:
    接收到SQL --> 放入SQL执行队列 --> 使用分析器分解SQL --> 按照分析结果进行数据的提取或者修改 --> 返回处理结果
    当然,这个流程图不一定正确,这只是我自己主观意识上这么我认为。那么这个处理过程当中,最容易出现问题的是什么?就是说,如果前一个SQL没有执行完毕的话,后面的SQL是不会执行的,因为为了保证数据的完整性,必须对数据表文件进行锁定,包括共享锁和独享锁两种锁定。共享锁是在锁定的期间,其它线程也可以访问这个数据文件,但是不允许修改操作,相应的,独享锁就是整个文件就是归一个线程所有,其它线程无法访问这个数据文件。一般MySQL中最快的存储引擎MyISAM,它是基于表锁定的,就是说如果一锁定的话,那么整个数据文件外部都无法访问,必须等前一个操作完成后,才能接收下一个操作,那么在这个前一个操作没有执行完成,后一个操作等待在队列里无法执行的情况叫做阻塞,一般我们通俗意义上叫做“锁表”。
    锁表直接导致的后果是什么?就是大量的SQL无法立即执行,必须等队列前面的SQL全部执行完毕才能继续执行。这个无法执行的SQL就会导致没有结果,或者延迟严重,影响用户体验。
    特别是对于一些使用比较频繁的表,比如SNS系统中的用户信息表、论坛系统中的帖子表等等,都是访问量大很大的表,为了保证数据的快速提取返回给用户,必须使用一些处理方式来解决这个问题,这个就是我今天要聊到的分表技术。
    分表技术顾名思义,就是把若干个存储相同类型数据的表分成几个表分表存储,在提取数据的时候,不同的用户访问不同的表,互不冲突,减少锁表的几率。比如,目前保存用户分表有两个表,一个是user_1表,还有一个是 user_2 表,两个表保存了不同的用户信息,user_1 保存了前10万的用户信息,user_2保存了后10万名用户的信息,现在如果同时查询用户 heiyeluren1 和 heiyeluren2 这个两个用户,那么就是分表从不同的表提取出来,减少锁表的可能。
    我下面要讲述的两种分表方法我自己都没有实验过,不保证准确能用,只是提供一个设计思路。下面关于分表的例子我假设是在一个贴吧系统的基础上来进行处理和构建的。(如果没有用过贴吧的用户赶紧Google一下)
    二、基于基础表的分表处理
    这个基于基础表的分表处理方式大致的思想就是:一个主要表,保存了所有的基本信息,如果某个项目需要找到它所存储的表,那么必须从这个基础表中查找出对应的表名等项目,好直接访问这个表。如果觉得这个基础表速度不够快,可以完全把整个基础表保存在缓存或者内存中,方便有效的查询。
    我们基于贴吧的情况,构建假设如下的3张表:
    1. 贴吧版块表: 保存贴吧中版块的信息
    2. 贴吧主题表:保存贴吧中版块中的主题信息,用于浏览
    3. 贴吧回复表:保存主题的原始内容和回复内容
    “贴吧版块表”包含如下字段:
    版块ID       board_id          int(10)
    版块名称    board_name      char(50)
    子表ID       table_id            smallint(5)
    产生时间    created             datetime
    “贴吧主题表”包含如下字段:
    主题ID          topic_id        int(10)
    主题名称        topic_name     char(255)
    版块ID          board_id          int(10)
    创建时间       created           datetime
    “贴吧回复表”的字段如下:
    回复ID        reply_id           int(10)
    回复内容      reply_text        text
    主题ID        topic_id           int(10)
    版块ID        board_id         int(10)
    创建时间      created            datetime
    那么上面保存了我们整个贴吧中的表结构信息,三个表对应的关系是:
    版块 --> 多个主题
    主题 --> 多个回复
    那么就是说,表文件大小的关系是:
    版块表文件 < 主题表文件 < 回复表文件
    所以基本可以确定需要对主题表和回复表进行分表,已增加我们数据检索查询更改时候的速度和性能。
    看了上面的表结构,会明显发现,在“版块表”中保存了一个"table_id"字段,这个字段就是用于保存一个版块对应的主题和回复都是分表保存在什么表里的。
    比如我们有一个叫做“PHP”的贴吧,board_id是1,子表ID也是1,那么这条记录就是:
    board_id | board_name | table_id | created
    1 | PHP | 1 | 2007-01-19 00:30:12
    相应的,如果我需要提取“PHP”吧里的所有主题,那么就必须按照表里保存的table_id来组合一个存储了主题的表名称,比如我们主题表的前缀是“topic_”,那么组合出来“PHP”吧对应的主题表应该是:“topic_1”,那么我们执行:
    SELECT * FROM topic_1 WHERE board_id = 1 ORDER BY topic_id DESC LIMIT 10
    这样就能够获取这个主题下面回复列表,方便我们进行查看,如果需要查看某个主题下面的回复,我们可以继续使用版块表中保存的“table_id”来进行查询。比如我们回复表的前缀是“reply_”,那么就可以组合出“PHP”吧的ID为1的主题的回复:
    SELECT * FROM reply_1 WHERE topic_id = 1 ORDER BY reply_id DESC LIMIT 10
    这里,我们能够清晰的看到,其实我们这里使用了基础表,基础表就是我们的版块表。那么相应的,肯定会说:基础表的数据量大了以后如何保证它的速度和效率?
    当然,我们就必须使得这个基础表保持最好的速度和性能,比如,可以采用MySQL的内存表来存储,或者保存在内存当中,比如Memcache之类的内存缓存等等,可以按照实际情况来进行调整。
    一般基于基础表的分表机制在SNS、交友、论坛等Web2.0网站中是个比较不错的解决方案,在这些网站中,完全可以单独使用一个表来来保存基本标识和目标表之间的关系。使用表保存对应关系的好处是以后扩展非常方便,只需要增加一个表记录。
    优势】增加删除节点非常方便,为后期升级维护带来很大便利
    劣势】需要增加表或者对某一个表进行操作,还是无法离开数据库,会产生瓶颈
    三、基于Hash算法的分表处理
    我们知道Hash表就是通过某个特殊的Hash算法计算出的一个值,这个值必须是惟一的,并且能够使用这个计算出来的值查找到需要的值,这个叫做哈希表。
    我们在分表里的hash算法跟这个思想类似:通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名,然后访问相应的表。
    继续拿上面的贴吧来说,每个贴吧有版块名称和版块ID,那么这两项值是固定的,并且是惟一的,那么我们就可以考虑通过对这两项值中的一项进行一些运算得出一个目标表的名称。
    现在假如我们针对我们这个贴吧系统,假设系统最大允许1亿条数据,考虑每个表保存100万条记录,那么整个系统就不超过100个表就能够容纳。按照这个标准,我们假设在贴吧的版块ID上进行hash,获得一个key值,这个值就是我们的表名,然后访问相应的表。
    我们构造一个简单的hash算法:
    function get_hash($id){
         $str = bin2hex($id);
         $hash = substr($str, 0, 4);
         if (strlen($hash)<4){
             $hash = str_pad($hash, 4, "0");
         }
         return $hash;
    }
    算法大致就是传入一个版块ID值,然后函数返回一个4位的字符串,如果字符串长度不够,使用0进行补全。
    比如:get_hash(1),输出的结果是“3100”,输入:get_hash(23819),得到的结果是:3233,那么我们经过简单的跟表前缀组合,就能够访问这个表了。那么我们需要访问ID为1的内容时候哦,组合的表将是:topic_3100、reply_3100,那么就可以直接对目标表进行访问了。
    当然,使用hash算法后,有部分数据是可能在同一个表的,这一点跟hash表不同,hash表是尽量解决冲突,我们这里不需要,当然同样需要预测和分析表数据可能保存的表名。
    如果需要存储的数据更多,同样的,可以对版块的名字进行hash操作,比如也是上面的二进制转换成十六进制,因为汉字比数字和字母要多很多,那么重复几率更小,但是可能组合成的表就更多了,相应就必须考虑一些其它的问题。
    归根结底,使用hash方式的话必须选择一个好的hash算法,才能生成更多的表,然数据查询的更迅速。
    优点hash算法直接得出目标表名称,效率很高】通过
    劣势】扩展性比较差,选择了一个hash算法,定义了多少数据量,以后只能在这个数据量上跑,不能超过过这个数据量,可扩展性稍差
    四、其它问题
    1. 搜索问题
    现在我们已经进行分表了,那么就无法直接对表进行搜索,因为你无法对可能系统中已经存在的几十或者几百个表进行检索,所以搜索必须借助第三方的组件来进行,比如Lucene作为站内搜索引擎是个不错的选择。
    2. 表文件问题
    我们知道MySQL的MyISAM引擎每个表都会生成三个文件,*.frm、*.MYD、*.MYI 三个文件,分表用来保存表结构、表数据和表索引。Linux下面每个目录下的文件数量最好不要超过1000个,不然检索数据将更慢,那么每个表都会生成三个文件,相应的如果分表超过300个表,那么将检索非常慢,所以这时候就必须再进行分,比如在进行数据库的分离。
    使用基础表,我们可以新增加一个字段,用来保存这个表保存在什么数据。使用Hash的方式,我们必须截取hash值中第几位来作为数据库的名字。这样,完好的解决这个问题。
    五、总结
    在大负载应用当中,数据库一直是个很重要的瓶颈,必须要突破,本文讲解了两种分表的方式,希望对很多人能够有启发的作用。当然,本文代码和设想没有经过任何代码测试,所以无法保证设计的完全准确实用,具体还是需要读者在使用过程当中认真分析实施。
    文章写的比较匆忙,质量可能无法保证,遇到错误,不要见怪,欢迎提出批评指教,谢谢~~~~!

    展开全文
  • sqlite存储大数据量数据之浅见

    万次阅读 2012-07-21 20:40:55
     老猫从j2me开发一路走来,对于sqlite真是喜忧参半,囍的是在一个单机PDA环境下内嵌sqlite可以利用关系型数据库对数据进行操作,开发人员无须为以文本文件形式存储数据进行查询,修改(定位而头疼),想当初三万...
    老猫最近刚开始学习android,android中对于数据存储的方式有好几种,经过两天的学习,对于sqlite存储数据的性能和用法做一浅显的说明:
         老猫从j2me开发一路走来,对于sqlite真是喜忧参半,囍的是在一个单机PDA环境下内嵌sqlite可以利用关系型数据库对数据进行操作,开发人员无须为以文本文件形式存储的数据进行查询,修改(定位而头疼),想当初三万条数据在j2me弱弱的文本操作环境下我用了将近60个文件进行存储并进行定位查询,修改操作,想当然速度不会很快,一条数据的查询时间将近3到4秒。忧的是:sqlite毕竟是个内嵌的关系型数据库,它存储数据是有限的,在j2me环境下老猫曾测试存储一万条数据就开始内存溢出了,闹心呀
       从j2me转向android,可以看到API中对sqlite专门提供了操作的接口,于是我在考虑android环境下我如果给了它足够大的存储空间,他是否还会对我视而不见,(老猫一直认为sqlite存储较大量数据还是可以实现的,至于制约他的因素可能就是存储空间)。
      这里我抛开真机或模拟器自带的内存,通过sd卡大容量的空间来存放我们的sqlite数据库。
        第一:模拟器环境下:
            去网站下载SQLite Expert Professiona工具,此工具是一个可视化创建sqlite数据库的工具很好用,下载地址:
           安装完成后,界面如下图所示:
       
             1、创建数据库:
                  在工具条栏选择File---new database,此时会弹出数据库添加界面,这里我们添加data file为dictionary,给database alias(sqlite是文本数据库)添加为c:\ dictionary     添加完成后点击 Ok 按钮,此时在左侧的列表中会看到你刚刚新创建的数据库 dictionary。
          2、创建表
               选中dictionary右键可以看到NEW TABLE,点击它,这里我们创建的表名为:t_wods,方式和你创建mysql表一样,填写字段,类型,字段大小等等,这里我们创建两个字段:english,chinese,都为varchar类型长度为20。
          3.录入信息,为了测试方便,我直接将两万条的数据导入到该表中,现在dictionary文件的大小为5M.
            此时我们的数据库就创建完成。对于c盘下的dictionary文件,我们先放下,下面开始给模拟器创建sd卡镜像。
       3、(sd卡镜像文件的创建转载于别处,谢谢原作者)
                   创建一个SD卡镜像文件。
                  打开cmd,输入如下命令:mksdcard 1024M sdcard.img

               该命令会在当前目录下生成一个sdcard.img文件,该文件就是Android模拟器的SD卡镜像文件。1024M表示1024兆,即该SD卡有1G的容量,也可用K做单位(1M=1024K),K,M必须大写。目前Android支持8M~128GSD

    运行带有SD卡的模拟器
          创建了SD卡镜像文件,只是创建了一个文件,还不能在模拟器中直接用,要在模拟器中可看到该SD卡,方法有两种,如下:


    ◆在cmd中,命令如下:emulator -sdcard e:sdcard.img

             ◆在Eclipse中,在Run->Run Configurations...菜单里面的Target标签页里面,输入启动参数



       以上工作完成,我们可以开始写代码了:


      为了测试,我只写了一个小demo,大家能看懂即可:
       public class Dictionary extends Activity  implements OnClickListener, TextWatcher{
    private final String DATABASE_PATH = android.os.Environment
       .getExternalStorageDirectory().getAbsolutePath()
       + "/dictionary";
    private final String DATABASE_FILENAME = "dictionary.db3";
    SQLiteDatabase database;
    Button btnSelectWord;
    AutoCompleteTextView actvWord;
    @Override
    public void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.main);
         // 打开数据库,database是在Main类中定义的一个SQLiteDatabase类型的变量
      database = openDatabase();
      // 下面的代码装载了相关组件,并设置了相应的事件
      btnSelectWord = (Button) findViewById(R.id.btnSelectWord);
      actvWord = (AutoCompleteTextView) findViewById(R.id.actvWord);
      btnSelectWord.setOnClickListener(this);
      actvWord.addTextChangedListener(this);
    }
    public void onClick(View view)
    {
      String sql = "select chinese from t_words where english=?";
        Cursor cursor = database.rawQuery(sql, new String[]
      { actvWord.getText().toString() });
      String result = "未找到该信息.";
      //  如果查找english信息,显示其chinese信息
      if (cursor.getCount() > 0)
      {
       //  必须使用moveToFirst方法将记录指针移动到第1条记录的位置
       cursor.moveToFirst();
       result = cursor.getString(cursor.getColumnIndex("chinese"));
       Log.i("tran", "success"+result);
      }
      //  显示查询结果对话框
      new AlertDialog.Builder(this).setTitle("查询结果").setMessage(result)
        .setPositiveButton("关闭", null).show();

    }
    private SQLiteDatabase openDatabase() {
      try {
       // 获得dictionary.db文件的绝对路径
       String databaseFilename = DATABASE_PATH + "/" + DATABASE_FILENAME;
       File dir = new File(DATABASE_PATH);
       // 如果/sdcard/dictionary目录中存在,创建这个目录
       if (!dir.exists())
        dir.mkdir();
       // 如果在/sdcard/dictionary目录中不存在
       // dictionary.db文件,则从res\raw目录中复制这个文件到
       // SD卡的目录(/sdcard/dictionary)
       if (!(new File(databaseFilename)).exists()) {
        // 获得封装dictionary.db文件的InputStream对象
        InputStream is = getResources().openRawResource(
          R.raw.dictionary);
        FileOutputStream fos = new FileOutputStream(databaseFilename);
        byte[] buffer = new byte[8192];
        int count = 0;
        // 开始复制dictionary.db文件
        while ((count = is.read(buffer)) > 0) {
         fos.write(buffer, 0, count);
        }

        fos.close();
        is.close();
       }
       // 打开/sdcard/dictionary目录中的dictionary.db文件
       SQLiteDatabase database = SQLiteDatabase.openOrCreateDatabase(
         databaseFilename, null);
       return database;
      } catch (Exception e) {
      }
      return null;
    }
    @Override
    public void afterTextChanged(Editable s) {
      
    }
    @Override
    public void beforeTextChanged(CharSequence s, int start, int count,
       int after) {
      
    }
    @Override
    public void onTextChanged(CharSequence s, int start, int before, int count) {
      
    }

    }
    运行程序效果如下(该数据在库中的第20004条):

    示例二(该数据在19259行)


    第二、真机环境下


    模拟器上测试通过,下面在真机上进行测试:
    首先将,本人用的是 华为c8500 真机sd卡下新建dictionary文件夹,然后将dictionary文件拷贝到里面,从项目目录bin文件夹下降dictionary.apk文件也拷贝到sd卡下,安装测试,由于不好切图,就不上了,总之数据也能够正常查询出来。

    以上过程只是个人对中型数据量的数据做的测试观点,当然如果硬是要存储几十万上百万的数据,估计够呛,对于上面的过程有想法的朋友可以留言交流,老猫也是新手一个,谢谢大家。
    原文地址:http://blog.csdn.net/chenhuijie666/article/details/7091283
    展开全文
  • 各位高手您们好,我最近接手公司里一个比较棘手的问题,关于如何利用MySQL存储大数据量的问题,主要是数据库中的两张历史数据表,一张模拟量历史数据和一张开关量历史数据表,这两张表字段设计的很简单(OrderNo,...


    各位高手您们好,我最近接手公司里一个比较棘手的问题,关于如何利用MySQL存储大数据量的问题,主要是数据库中的两张历史数据表,一张模拟量历史数据和一张开关量历史数据表,这两张表字段设计的很简单(OrderNo,Value,DataTime)。基本上每张表每天可以增加几千万条数据,我想问如何存储数据才能不影响检索速度呢?需不需要换oracle数据库呢?因为我是数据库方面的新手,希望可以说的详细一点,万分感谢!!?-0-#暂时可以先考虑用infobright 这是mysql的数据仓库解决方案如果这都满足不了需求 再考虑hadoop 

    暂时可以先考虑用infobright  这是MySQL的数据仓库解决方案


    如果这都满足不了需求 再考虑Hadoop



    提问:如何设计或优化千万级别的大表?此外无其他信息,个人觉得这个话题有点范,就只好简单说下该如何做,对于一个存储设计,必须考虑业务特点,收集的信息如下:
    1.数据的容量:1-3年内会大概多少条数据,每条数据大概多少字节; 

    2.数据项:是否有大字段,那些字段的值是否经常被更新; 
    3.数据查询SQL条件:哪些数据项的列名称经常出现在WHERE、GROUP BY、ORDER BY子句中等; 
    4.数据更新类SQL条件:有多少列经常出现UPDATE或DELETE 的WHERE子句中; 
    5.SQL量的统计比,如:SELECT:UPDATE+DELETE:INSERT=多少? 

    6.预计大表及相关联的SQL,每天总的执行量在何数量级? 
    7.表中的数据:更新为主的业务 还是 查询为主的业务 
    8.打算采用什么数据库物理服务器,以及数据库服务器架构? 
    9.并发如何? 
    10.存储引擎选择InnoDB还是MyISAM? 

    大致明白以上10个问题,至于如何设计此类的大表,应该什么都清楚了! 

    至于优化若是指创建好的表,不能变动表结构的话,那建议InnoDB引擎,多利用点内存,减轻磁盘IO负载,因为IO往往是数据库服务器的瓶颈 

    另外对优化索引结构去解决性能问题的话,建议优先考虑修改类SQL语句,使他们更快些,不得已只靠索引组织结构的方式,当然此话前提是, 
    索引已经创建的非常好,若是读为主,可以考虑打开query_cache, 

    以及调整一些参数值:sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size 

    更多信息参见:
    MySQL数据库服务器端核心参数详解和推荐配置
    mysqlops.com/2011/10/26
    您好,主要是检索某段时间内的模拟量值(select * from table where datatime between t1 and t2 ),目前打算使用分表,分区的方式解决

    不纸上谈兵,说一下我的思路以及我的解决,抛砖引玉了 
    我最近正在解决这个问题 
    我现在的公司有三张表,是5亿的数据,每天张表每天的增量是100w 
    每张表大概在10个columns左右 
    下面是我做的测试和对比 
    1.首先看engine,在大数据量情况下,在没有做分区的情况下 
    mysiam比innodb在只读的情况下,效率要高13%左右 
    2.在做了partition之后,你可以去读一下mysql的官方文档,其实对于partition,专门是对myisam做的优化,对于innodb,所有的数据是存在ibdata里面的,所以即使你可以看到schema变了,其实没有本质的变化 
    在分区出于同一个physical disk下面的情况下,提升大概只有1% 
    在分区在不同的physical disk下,我分到了三个不同的disks下,提升大概在3%,其实所谓的吞吐量,由很多因素决定的,比如你的explain parition时候可以看到,record在那一个分区,如果每个分区都有,其实本质上没有解决读的问题,这样只会提升写的效率。 
    另外一个问题在于,分区,你怎么分,如果一张表,有三个column都是经常被用于做查询条件的,其实是一件很悲惨的事情,因为你没有办法对所有的sql做针对性的分区,如果你只是如mysql官方文档上说的,只对时间做一个分区,而且你也只用时间查询的话,恭喜你 
    3.表主要用来读还是写,其实这个问题是不充分的,应该这样问,你在写入的时候,同时并发的查询多么?我的问题还比较简单,因为mongodb的shredding支持不能,在crush之后,还是回到mysql,所以在通常情况下,9am-9pm,写入的情况很多,这个时候我会做一个view,view是基于最近被插入或者经常被查询的,通过做view来分离读取,就是说写是在table上的,读在进行逻辑判断前是在view上操作的 
    4做一些archive table,比如先对这些大表做很多已有的统计分析,然后通过已有的分析+增量来解决 
    5如果你用mysiam,还有一个问题你要注意,如果你的.configure的时候,加了一个max index length参数的时候,当你的record数大于制定长度的时候,这个index会被disable 

    照你的需求来看,可以有两种方式,一种是分表,另一种是分区
    首先是分表,就像你自己所说的,可以按月分表,可以按用户ID分表等等,至于采用哪种方式分表,要看你的业务逻辑了,分表不好的地方就是查询有时候需要跨多个表。

    然后是分区,分区可以将表分离在若干不同的表空间上,用分而治之的方法来支撑无限膨胀的大表,给大表在物理一级的可管理性。将大表分割成较小的分区可以改善表的维护、备份、恢复、事务及查询性能。分区的好处是分区的优点:

    1 增强可用性:如果表的一个分区由于系统故障而不能使用,表的其余好的分区仍然可以使用;

    2 减少关闭时间:如果系统故障只影响表的一部分分区,那么只有这部分分区需要修复,故能比整个大表修复花的时间更少;

    3 维护轻松:如果需要重建表,独立管理每个分区比管理单个大表要轻松得多;

    4 均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能;

    5 改善性能:对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行,可使运行速度更快;

    6 分区对用户透明,最终用户感觉不到分区的存在。


     如今随着互联网的发展,数据的量级也是撑指数的增长,从GB到TB到PB。对数据的各种操作也是愈加的困难,传统的关系性数据库已经无法满足快速查询与插入数据的需求。这个时候NoSQL的出现暂时解决了这一危机。它通过降低数据的安全性,减少对事务的支持,减少对复杂查询的支持,来获取性能上的提升。但是,在有些场合NoSQL一些折衷是无法满足使用场景的,就比如有些使用场景是绝对要有事务与安全指标的。这个时候NoSQL肯定是无法满足的,所以还是需要使用关系性数据库。

      虽然关系型数据库在海量数据中逊色于NoSQL数据库,但是如果你操作正确,它的性能还是会满足你的需求的。针对数据的不同操作,其优化方向也是不尽相同。对于数据移植,查询和插入等操作,可以从不同的方向去考虑。而在优化的时候还需要考虑其他相关操作是否会产生影响。就比如你可以通过创建索引提高查询性能,但是这会导致插入数据的时候因为要建立更新索引导致插入性能降低,你是否可以接受这一降低那。所以,对数据库的优化是要考虑多个方向,寻找一个折衷的最佳方案。

      一:查询优化


      1:创建索引。

      最简单也是最常用的优化就是查询。因为对于CRUD操作,read操作是占据了绝大部分的比例,所以read的性能基本上决定了应用的性能。对于查询性能最常用的就是创建索引。经过测试,2000万条记录,每条记录200字节两列varchar类型的。当不使用索引的时候查询一条记录需要一分钟,而当创建了索引的时候查询时间可以忽略。但是,当你在已有数据上添加索引的时候,则需要耗费非常大的时间。我插入2000万条记录之后,再创建索引大约话费了几十分钟的样子。

      创建索引的弊端和场合。虽然创建索引可以很大程度上优化查询的速度,但是弊端也是很明显的。一个是在插入数据的时候,创建索引也需要消耗部分的时间,这就使得插入性能在一定程度上降低;另一个很明显的是数据文件变的更大。在列上创建索引的时候,每条索引的长度是和你创建列的时候制定的长度相同的。比如你创建varchar(100),当你在该列上创建索引,那么索引的长度则是102字节,因为长度超过64字节则会额外增加2字节记录索引的长度。


      从上图可以看到我在YCSB_KEY这一列(长度100)上创建了一个名字为index_ycsb_key的索引,每条索引长度都为102,想象一下当数据变的巨大无比的时候,索引的大小也是不可以小觑的。而且从这也可以看出,索引的长度和列类型的长度还不同,比如varchar它是变长的字符类型(请看MySQL数据类型分析),实际存储长度是是实际字符的大小,但是索引却是你声明的长度的大小。你创建列的时候声明100字节,那么索引长度就是这个字节再加上2,它不管你实际存储是多大。

      除了创建索引需要消耗时间,索引文件体积会变的越来越大之外,创建索引也需要看的你存储数据的特征。当你存储数据很大一部分都是重复记录,那这个时候创建索引是百害而无一利。请先查看MySQL索引介绍。所以,当很多数据重复的时候,索引带来的查询提升的效果是可以直接忽略的,但是这个时候你还要承受插入数据的时候创建索引带来的性能消耗。

      2:缓存的配置。

      在MySQL中有多种多样的缓存,有的缓存负责缓存查询语句,也有的负责缓存查询数据。这些缓存内容客户端无法操作,是由server端来维护的。它会随着你查询与修改等相应不同操作进行不断更新。通过其配置文件我们可以看到在MySQL中的缓存:


      在这里主要分析query cache,它是主要用来缓存查询数据。当你想使用该cache,必须把query_cache_size大小设置为非0。当设置大小为非0的时候,server会就会缓存每次查询返回的结果,到下次相同查询server就直接从缓存获取数据,而不是再执行查询。能缓存的数据量就和你的size大小设置有关,所以当你设置的足够大,数据可以完全缓存到内存,速度就会非常之快。

      但是,query cache也有它的弊端。当你对数据表做任何的更新操作(update/insert/delete)等操作,server为了保证缓存与数据库的一致性,会强制刷新缓存数据,导致缓存数据全部失效。所以,当一个表格的更新数据表操作非常多的话,query cache是不会起到查询提升的性能,还会影响其他操作的性能。

      3:slow_query_log分析。

      其实对于查询性能提升,最重要也是最根本的手段也是slow_query的设置。


      当你设置slow_query_log为on的时候,server端会对每次的查询进行记录,当超过你设置的慢查询时间(long_query_time)的时候就把该条查询记录到日志。而你对性能进行优化的时候,就可以分析慢查询日志,对慢查询的查询语句进行有目的的优化。可以通过创建各种索引,可以通过分表等操作。那为什么要分库分表那,当不分库分表的时候那个地方是限制性能的地方啊。下面我们就简单介绍。

      4:分库分表

      分库分表应该算是查询优化的杀手锏了。上述各种措施在数据量达到一定等级之后,能起到优化的作用已经不明显了。这个时候就必须对数据量进行分流。分流一般有分库与分表两种措施。而分表又有垂直切分与水平切分两种方式。下面我们就针对每一种方式简单介绍。

      对于mysql,其数据文件是以文件形式存储在磁盘上的。当一个数据文件过大的时候,操作系统对大文件的操作就会比较麻烦与耗时,而且有的操作系统就不支持大文件,所以这个时候就必须分表了。另外对于mysql常用的存储引擎是Innodb,它的底层数据结构是B+树。当其数据文件过大的时候,B+树就会从层次和节点上比较多,当查询一个节点的时候可能会查询很多层次,而这必定会导致多次IO操作进行装载进内存,肯定会耗时的。除此之外还有Innodb对于B+树的锁机制。对每个节点进行加锁,那么当更改表结构的时候,这时候就会树进行加锁,当表文件大的时候,这可以认为是不可实现的。 

      所以综上我们就必须进行分表与分库的操作。


     二:数据转移

      当数据量达到一定等级之后,那么移库将是一个非常慎重又危险的工作。在移库中保证前后数据的一致性,各种突发情况的处理,移库过程中数据的变迁,每一个都是一个非常困难的问题。

      2.1:插入数据

      当进行数据迁移的时候,肯定会存在大数据的重新导入,你可以选择直接load文件,有的时候可能就需要代码插入了。这个时候就需要对插入语句进行一定的优化了。这个时候可以使用INSERT DELAYED语句,该语句是当你发出插入请求的时候,部马上就插入到数据库而是放在缓存里面,等待时机成熟之后再进行插入。

    待补充。。。

    mysql大数据量处理

    一、概述
    分表是个目前算是比较炒的比较流行的概念,特别是在大负载的情况下,分表是一个良好分散数据库压力的好方法。
    首先要了解为什么要分表,分表的好处是什么。我们先来大概了解以下一个数据库执行SQL的过程:
    接收到SQL --> 放入SQL执行队列 --> 使用分析器分解SQL --> 按照分析结果进行数据的提取或者修改 --> 返回处理结果
    当然,这个流程图不一定正确,这只是我自己主观意识上这么我认为。那么这个处理过程当中,最容易出现问题的是什么?就是说,如果前一个SQL没有执行完毕的话,后面的SQL是不会执行的,因为为了保证数据的完整性,必须对数据表文件进行锁定,包括共享锁和独享锁两种锁定。共享锁是在锁定的期间,其它线程也可以访问这个数据文件,但是不允许修改操作,相应的,独享锁就是整个文件就是归一个线程所有,其它线程无法访问这个数据文件。一般MySQL中最快的存储引擎MyISAM,它是基于表锁定的,就是说如果一锁定的话,那么整个数据文件外部都无法访问,必须等前一个操作完成后,才能接收下一个操作,那么在这个前一个操作没有执行完成,后一个操作等待在队列里无法执行的情况叫做阻塞,一般我们通俗意义上叫做“锁表”。
    锁表直接导致的后果是什么?就是大量的SQL无法立即执行,必须等队列前面的SQL全部执行完毕才能继续执行。这个无法执行的SQL就会导致没有结果,或者延迟严重,影响用户体验。
    特别是对于一些使用比较频繁的表,比如SNS系统中的用户信息表、论坛系统中的帖子表等等,都是访问量大很大的表,为了保证数据的快速提取返回给用户,必须使用一些处理方式来解决这个问题,这个就是我今天要聊到的分表技术。
    分表技术顾名思义,就是把若干个存储相同类型数据的表分成几个表分表存储,在提取数据的时候,不同的用户访问不同的表,互不冲突,减少锁表的几率。比如,目前保存用户分表有两个表,一个是user_1表,还有一个是 user_2 表,两个表保存了不同的用户信息,user_1 保存了前10万的用户信息,user_2保存了后10万名用户的信息,现在如果同时查询用户 heiyeluren1 和 heiyeluren2 这个两个用户,那么就是分表从不同的表提取出来,减少锁表的可能。
    我下面要讲述的两种分表方法我自己都没有实验过,不保证准确能用,只是提供一个设计思路。下面关于分表的例子我假设是在一个贴吧系统的基础上来进行处理和构建的。(如果没有用过贴吧的用户赶紧Google一下)
    二、基于基础表的分表处理
    这个基于基础表的分表处理方式大致的思想就是:一个主要表,保存了所有的基本信息,如果某个项目需要找到它所存储的表,那么必须从这个基础表中查找出对应的表名等项目,好直接访问这个表。如果觉得这个基础表速度不够快,可以完全把整个基础表保存在缓存或者内存中,方便有效的查询。
    我们基于贴吧的情况,构建假设如下的3张表:
    1. 贴吧版块表: 保存贴吧中版块的信息
    2. 贴吧主题表:保存贴吧中版块中的主题信息,用于浏览
    3. 贴吧回复表:保存主题的原始内容和回复内容
    “贴吧版块表”包含如下字段:
    版块ID       board_id          int(10)
    版块名称    board_name      char(50)
    子表ID       table_id            smallint(5)
    产生时间    created             datetime
    “贴吧主题表”包含如下字段:
    主题ID          topic_id        int(10)
    主题名称        topic_name     char(255)
    版块ID          board_id          int(10)
    创建时间       created           datetime
    “贴吧回复表”的字段如下:
    回复ID        reply_id           int(10)
    回复内容      reply_text        text
    主题ID        topic_id           int(10)
    版块ID        board_id         int(10)
    创建时间      created            datetime
    那么上面保存了我们整个贴吧中的表结构信息,三个表对应的关系是:
    版块 --> 多个主题
    主题 --> 多个回复
    那么就是说,表文件大小的关系是:
    版块表文件 < 主题表文件 < 回复表文件
    所以基本可以确定需要对主题表和回复表进行分表,已增加我们数据检索查询更改时候的速度和性能。
    看了上面的表结构,会明显发现,在“版块表”中保存了一个"table_id"字段,这个字段就是用于保存一个版块对应的主题和回复都是分表保存在什么表里的。
    比如我们有一个叫做“PHP”的贴吧,board_id是1,子表ID也是1,那么这条记录就是:
    board_id | board_name | table_id | created
    1 | PHP | 1 | 2007-01-19 00:30:12
    相应的,如果我需要提取“PHP”吧里的所有主题,那么就必须按照表里保存的table_id来组合一个存储了主题的表名称,比如我们主题表的前缀是“topic_”,那么组合出来“PHP”吧对应的主题表应该是:“topic_1”,那么我们执行:
    SELECT * FROM topic_1 WHERE board_id = 1 ORDER BY topic_id DESC LIMIT 10
    这样就能够获取这个主题下面回复列表,方便我们进行查看,如果需要查看某个主题下面的回复,我们可以继续使用版块表中保存的“table_id”来进行查询。比如我们回复表的前缀是“reply_”,那么就可以组合出“PHP”吧的ID为1的主题的回复:
    SELECT * FROM reply_1 WHERE topic_id = 1 ORDER BY reply_id DESC LIMIT 10
    这里,我们能够清晰的看到,其实我们这里使用了基础表,基础表就是我们的版块表。那么相应的,肯定会说:基础表的数据量大了以后如何保证它的速度和效率?
    当然,我们就必须使得这个基础表保持最好的速度和性能,比如,可以采用MySQL的内存表来存储,或者保存在内存当中,比如Memcache之类的内存缓存等等,可以按照实际情况来进行调整。
    一般基于基础表的分表机制在SNS、交友、论坛等Web2.0网站中是个比较不错的解决方案,在这些网站中,完全可以单独使用一个表来来保存基本标识和目标表之间的关系。使用表保存对应关系的好处是以后扩展非常方便,只需要增加一个表记录。
    优势】增加删除节点非常方便,为后期升级维护带来很大便利
    劣势】需要增加表或者对某一个表进行操作,还是无法离开数据库,会产生瓶颈
    三、基于Hash算法的分表处理
    我们知道Hash表就是通过某个特殊的Hash算法计算出的一个值,这个值必须是惟一的,并且能够使用这个计算出来的值查找到需要的值,这个叫做哈希表。
    我们在分表里的hash算法跟这个思想类似:通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名,然后访问相应的表。
    继续拿上面的贴吧来说,每个贴吧有版块名称和版块ID,那么这两项值是固定的,并且是惟一的,那么我们就可以考虑通过对这两项值中的一项进行一些运算得出一个目标表的名称。
    现在假如我们针对我们这个贴吧系统,假设系统最大允许1亿条数据,考虑每个表保存100万条记录,那么整个系统就不超过100个表就能够容纳。按照这个标准,我们假设在贴吧的版块ID上进行hash,获得一个key值,这个值就是我们的表名,然后访问相应的表。
    我们构造一个简单的hash算法:
    function get_hash($id){
         $str = bin2hex($id);
         $hash = substr($str, 0, 4);
         if (strlen($hash)<4){
             $hash = str_pad($hash, 4, "0");
         }
         return $hash;
    }
    算法大致就是传入一个版块ID值,然后函数返回一个4位的字符串,如果字符串长度不够,使用0进行补全。
    比如:get_hash(1),输出的结果是“3100”,输入:get_hash(23819),得到的结果是:3233,那么我们经过简单的跟表前缀组合,就能够访问这个表了。那么我们需要访问ID为1的内容时候哦,组合的表将是:topic_3100、reply_3100,那么就可以直接对目标表进行访问了。
    当然,使用hash算法后,有部分数据是可能在同一个表的,这一点跟hash表不同,hash表是尽量解决冲突,我们这里不需要,当然同样需要预测和分析表数据可能保存的表名。
    如果需要存储的数据更多,同样的,可以对版块的名字进行hash操作,比如也是上面的二进制转换成十六进制,因为汉字比数字和字母要多很多,那么重复几率更小,但是可能组合成的表就更多了,相应就必须考虑一些其它的问题。
    归根结底,使用hash方式的话必须选择一个好的hash算法,才能生成更多的表,然数据查询的更迅速。
    优点hash算法直接得出目标表名称,效率很高】通过
    劣势】扩展性比较差,选择了一个hash算法,定义了多少数据量,以后只能在这个数据量上跑,不能超过过这个数据量,可扩展性稍差
    四、其它问题
    1. 搜索问题
    现在我们已经进行分表了,那么就无法直接对表进行搜索,因为你无法对可能系统中已经存在的几十或者几百个表进行检索,所以搜索必须借助第三方的组件来进行,比如Lucene作为站内搜索引擎是个不错的选择。
    2. 表文件问题
    我们知道MySQL的MyISAM引擎每个表都会生成三个文件,*.frm、*.MYD、*.MYI 三个文件,分表用来保存表结构、表数据和表索引。Linux下面每个目录下的文件数量最好不要超过1000个,不然检索数据将更慢,那么每个表都会生成三个文件,相应的如果分表超过300个表,那么将检索非常慢,所以这时候就必须再进行分,比如在进行数据库的分离。
    使用基础表,我们可以新增加一个字段,用来保存这个表保存在什么数据。使用Hash的方式,我们必须截取hash值中第几位来作为数据库的名字。这样,完好的解决这个问题。
    五、总结
    在大负载应用当中,数据库一直是个很重要的瓶颈,必须要突破,本文讲解了两种分表的方式,希望对很多人能够有启发的作用。当然,本文代码和设想没有经过任何代码测试,所以无法保证设计的完全准确实用,具体还是需要读者在使用过程当中认真分析实施。
    文章写的比较匆忙,质量可能无法保证,遇到错误,不要见怪,欢迎提出批评指教,谢谢~~~~!

    文章来源:http://blog.csdn.net/likika2012/article/details/38816037
    展开全文
  • 当我们进行测试数据或者进行某种大数据量的时候,可以用一下语句进行大批量插入, 通过我的测试,10万条数据插入时间为7秒左右,100万条记录插入时间为65秒,200万插入为150秒。 我机器配置为 CPU I5 2450M 内存4G...
  • 因为存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化,所以速度就要慢一些。 如题: ...
  • oracle存储过程数据增删查改及调用

    万次阅读 多人点赞 2013-11-09 14:42:46
    存储过程存储过程是 SQL, PL/SQL, Java 语句的组合, 它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是,代码存储一次但是能够被多个程序使用。是存放在数据库服务器上的SQL语句...
  • Oracle 大数据量查询优化

    千次阅读 2018-01-18 22:38:54
    前言:平常写的SQL可能主要以实现查询出结果为主,但如果数据量,就会突出SQL查询语句优化的性能独特之处.一般的数据库设计都会建索引查询,这样较全盘扫描查询的确快了不少.下面总结下SQL查询语句的几个优化效率的...
  • MySQL数据库存储过程讲解与实例

    万次阅读 多人点赞 2019-12-31 15:43:15
    SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。...
  • 2. 存储何种数据? 3. 数据特点是什么? 4. 存在哪些技术挑战? 5. 解决方案有哪些? 6. md5散列桶的方法需要注意哪些问题? 7. 测试结果是什么? 解决方案: 1 需求背景 该应用场景为 DMP(Data...
  • MySQL使用存储过程批量插入数据

    千次阅读 2018-08-25 18:18:41
    -------------MySQL批量插入测试数据----------------------- CREATE TABLE `students_table` ( --定义测试表  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  `age` tinyint(4)...
  • 今天说一说如何利用存储过程快速插入数据。 SQL 语句需要先编译然后执行,而存储过程是一组为了完成特定功能的 SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字来调用执行它。 存储过程可以用控制...
  • 通过将一个表拆分成更小的单个表,只访问一小部分数据查询可以执行得更快,因为需要扫描的数据较少。而且可以更快地执行维护任务(如重建索引或备份表)。  实现分区操作时可以不拆分表,而将表物理地放置在...
  • -- MySQL测试任务:使用存储过程,往表中插入千万级数据,根据索引优化速度 -- 1.使用索引查询 -- 2.不使用索引查 -- 3.比较两者查询速度的差异 -- 1.创建索引测试表 DROP TABLE IF EXISTS index_test; CREATE ...
  • Oracle使用存储过程新建表

    千次阅读 2017-04-10 22:45:53
    Oracle使用存储过程新建表一只正在努力进步的小菜鸟需求 : 由于日志表数据量 , 公司要求将每天的数据数据按照当天的时间来存放到当天的表中例如 : 原本存在一张Button点击记录表( BUTTON_CLICK_LOG ) , 其中每天...
  • Mybatis传list参数调用oracle存储过程

    万次阅读 热门讨论 2016-04-19 09:36:35
    怎么利用MyBatis传List类型参数到数据库存储过程中实现批量插入数据? MyBatis中参数是List类型时怎么处理?大家都知道MyBatis批处理大量数据是很难做到事务回滚的(事务由Spring管理),都将逻辑写在存储中又是...
  • oracle存储过程详解

    万次阅读 2018-05-15 13:45:54
    Transact-SQL中的存储过程,非常类似于Java语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。... 存储过程中可以包含逻辑控制语句和数据...
  • SqlServer 查询存储过程最后执行时间

    千次阅读 2017-10-11 22:13:27
    下列语句利用系统自带的视图数据表,查询存储过程的最后执行情况: SELECT a.name AS 存储过程名称, a.create_date AS 创建日期, a.modify_date AS 修改日期, b.last_execution_time AS 最后执行日期, b
  • SQL Server DBA调优日记第一篇,大数据量查询记录数优化及原理探讨。现象、解决、模拟、原理探讨。
1 2 3 4 5 ... 20
收藏数 480,374
精华内容 192,149
关键字:

利用存储过程进行大数据量查询