精华内容
下载资源
问答
  • 继承关系存储树状结构数据--查询优化 关于树状结构数据在关系型数据库中的存储,上一篇博文:《树状结构数据库表设计》 中有过介绍讨论,也给出了查询方法。但关于采用继承关系存储时,写的查询函数查询效率很低...

    继承关系存储树状结构数据--查询优化

    关于树状结构数据在关系型数据库中的存储,上一篇博文:《树状结构数据库表设计》   中有过介绍和讨论,也给出了查询方法。但关于采用继承关系存储时,写的查询函数查询效率很低,数据在5000条时遍历出某个节点的全部父辈节点时,需要耗时2分钟。这个查询的节点在第十一层,并不深,而且数据量也不大,这个耗时是不能接受的。

             这是上一篇博文中提到的查询函数:

    BEGIN
    DECLARE sParentList varchar(1000); 
    DECLARE sParentTemp varchar(1000);
    SET sParentTemp =cast(rootId as CHAR);  
    WHILE sParentTemp is not NULL DO 
    IF (sParentList is not null) THEN 
    SET sParentList = concat(sParentTemp,',',sParentList);
    ELSE 
    SET sParentList = concat(sParentTemp);
    END IF; 
    SELECT GROUP_CONCAT(parent_dir_id) INTO sParentTemp FROM tb_directory where FIND_IN_SET(dir_id,sParentTemp)>0; 
    END WHILE; 
    RETURN sParentList; 
    END
    

    查询语句:select * from tb_directory wherefind_in_set(dir_id,getParentListTest(4208));

    上面的查询函数中,是将查询结果父id拼接到sParentTemp中,而实际中我们用到的树结构中的节点只有一个父节点,这里我就将查询函数修改了。如下:

    BEGIN
    DECLARE sParentList varchar(1000); 
    DECLARE sParentTemp INT;
    SET sParentTemp =rootId;  
    WHILE sParentTemp !=0 DO 
    IF (sParentList is not null) THEN 
    SET sParentList = concat(sParentTemp,',',sParentList);
    ELSE 
    SET sParentList = concat(sParentTemp);
    END IF; 
    SELECT parent_dir_id INTO sParentTemp FROM tb_directory where dir_id=sParentTemp; 
    END WHILE; 
    RETURN sParentList; 
    END
    

    查询语句:select * from tb_directory wherefind_in_set(dir_id,getParentList(4208));

    测试了下查询时间,在上述同样的数据环境下,遍历出同一节点全部父辈节点,上述查询耗时1s以内,这个就完全可以接受了。

    疑问:

             分开查询:我使用如下两条语句执行,查询速度都很快

                       selectgetParentListTest(4208) into @temp;

                       select * from tb_directorywhere find_in_set(dir_id,@temp);

    简单查了下关于sql的查询原理,还是没太弄明白原因。初步觉的应该跟where的搜索方式有关。
    展开全文
  • 查询优化技术 (1) 代数优化 (2) 基于存储路径的优化 (3) 基于代价估算的优化 整体过程: 将查询转换成语法树; 根据关系的等价变化规则对语法树进行代数优化; 选择底层的操作算法,对于语法树中的每个操作,根据...
        
    • 查询优化技术

      (1) 代数优化

      (2) 基于存储路径的优化

      (3) 基于代价估算的优化

      整体过程:

      将查询转换成语法树;

      根据关系的等价变化规则对语法树进行代数优化;

      选择底层的操作算法,对于语法树中的每个操作,根据存储路径、数据存储分布、存储数据的聚簇等信息选择具体的执行算法;

      生成查询计划,查询计划由一系列内部操作组成,这些内部操作按照一定的次序构成不同的执行方案,估算每个执行方案的代价,选择最小的一个。

    • 代数优化策略

      (1) 基本原则

      减少查询处理的中间结果的大小

      (2) 先执行投影、选择等操作,后执行联接、笛卡尔积等操作

      (3) 先做小关系之间的联接,后做与大关系的联接

      (4) 一般系统都采用基于规则的启发式查询优化方法

      一般的规则包括:

      1° 尽可能早的进行选择操作

      2° 投影操作和选择操作尽量同时进行,减少扫描关系的次数

      3° 将投影和其前或其后的联接操作同时进行,避免因为去掉某些属性而重复扫描

      4° 将某些联接操作同在它前面的笛卡尔积结合起来,合并成为一个联接操作(特别是等值连接的情况)

      5° 存储公共子表达式(此时要考虑子表达式结果集的大小、从外存中读入结果集的时间)

      (5) 关系代数表达式的查询优化是由DBMS的DML编译器自动完成的

      (6) 代数优化只改变查询语句中操作的次序和组合,不涉及底层的存取路径

    • 基于存储路径的优化

      (1) 每种操作有多种实现算法,具体选择哪种算法,要根据底层的存储路径的方式

      (2) 选择操作的启发式规则

      1° 小的关系一律顺序扫描,即便选择的列上有索引

      2° 如果选择条件是 <主键=值>,则查询结果最多是一个元组(主键的唯一性),此时使用主键索引(一般的RDBMS会自动创建主键索引,例如MYSQL)

      3° 如果选择条件是 <非主属性=值>,并且选择列上有索引。若估算查询结果元组数目少,则使用索引;若估算查询结果元组数目多,则使用顺序扫描

      4° 如果选择条件是非等值查询,同3°

      5° 对于AND合取条件,如果有涉及这些属性的组合索引,使用组合索引扫描;如果某些属性上有索引,则对其进行索引扫描后验证;没有任何索引,使用顺序扫描

      6° 对于OR析取条件,只要任意一个条件没有索引,就只能用顺序扫描的方式

      7° 如果关系按照条件中的属性进行排序且为关于该属性的等值查询,在使用顺序扫描的地方可以考虑二分查找

      (3) 连接操作的启发式规则

      1° 如果两个表都按照连接属性排序,则使用排序合并

      2° 如果一个表在连接属性上有索引,使用索引连接

      3° 如果1°,2°都不满足,其中一个表较小,则使用散列连接

      4° 嵌套循环时,外循环使用记录数少的表

    • 基于代价估算的因素

      (1) 访问存储器的代价

      (2) 生成中间文件的存储代价

      (3) 计算代价:搜索、排序、合并、计算等的代价

      (4) 内存使用代价:查询执行需要的内存缓冲区数目

      (5) 通信代价:数据在不同数据库结点中传送的代价

    • 为了估算代价,DBMS会在数据字典中存储查询优化器所需的各种统计信息

      例如:元组总数、元组占用的块数、平均元组长度、块因子(一个块中能够存放的元组数量)、连接选择性(???)、索引的层数、索引的选择基数、索引的叶结点数

    • 具体估算代价的计算 P111

    • 解释执行的系统一般包括代数优化 + 基于规则的启发式存储路径优化,全面的优化会延长系统响应时间,所以只能做部分优化;

      编译执行的系统查询优化和查询执行分开,因此可以使用以上的全部优化方法进行比较精细复杂的优化。

    展开全文
  • MySQL千万级数据分区存储查询优化 作为传统的关系型数据库,MySQL因其体积小、速度快、总体拥有成本低受到中小企业的热捧,但是对于大数据量(百万级以上)的操作显得有些力不从心,这里我结合之前开发的一个web...

    MySQL千万级数据分区存储及查询优化

    作为传统的关系型数据库,MySQL因其体积小、速度快、总体拥有成本低受到中小企业的热捧,但是对于大数据量(百万级以上)的操作显得有些力不从心,这里我结合之前开发的一个web系统来介绍一下MySQL数据库在千万级数据量的情况下如何优化提升查询速度。

    一、基本业务需求

    该系统包括硬件系统和软件系统,由中科院计算所开发的无线传感器网络负责实时数据的监测和回传到MySQL数据库,我们开发的软件系统负责对数据进行实时计算,可视化展示及异常事件报警监测。宫殿的温湿度等数据都存储在data表中,由于业务需要,data表中旧的数据要求不能删除,经过初步估算,一年的数据量大概为1200万条,之前的系统当数据量到达百万级时查询响应速度很慢,导致数据加载延迟很大,所以很有必要进行数据库的优化查询,提升响应速度。

    结合故宫温湿度监测系统EasiWeb 7.1的data表查询,这里主要从以下三个方面详解MySQL的分区优化技术:

    (1)EasiWeb 7.1系统data表基于分表、分区和索引的优化方案对比。

    (2)EasiWeb 7.1系统中采用的优化方案及实施步骤

    (3)系统模拟产生1500万数据的优化前后对比测试

    二、data表优化方案选择

        针对故宫系统大数据量时提升响应速度及运行性能的问题,我们团队通过研究和论证,提出了三种方案:

    2.1 data表分表存储,联表查询

    原理解释

       分表即将一个表结构分解为多个子表,这些子表可以同一个数据库下,也可以在不同的数据库下,查询的时候通过代码控制,生成多条查询语句,进行多项子表联查,最后汇总结果,整体上的查询结果与单表一样,但平均相应速度更快。

    实现方式

       采用merge分表,划分的标准可以选取时间(collectTime)作为参数。主表类似于一个壳子,逻辑上封装了子表,实际上数据都是存储在子表中。我们在每年的1月1日创建一个子表data_20XX,然后将这些子表union起来构成一个主表。对于插入操作,最新的数据将会被插入到最后一个子表中;对于查询操作,通过'data'主表查询的时候,查询引擎会根据查询语句口控制选取要查询的子表集合,实现等效查询。

    优缺点

       优点是merge分表可以很方便得实现分表,在进行查询的时候封装了查询过程,用户编写的代码较少。

       缺点是破坏了data表的结构,并且在新建子表的时候由于定时器延迟,可能导致个别数据被错误的存储。

    2.2 data表分区存储

    原理解释

        分区把存放数据的文件分成了许多小块,存储在磁盘中不同的区域,通过提升磁盘I/O能力来提升查询速度。分区不会更改data表的结构,发生变化的是存储方式。

    实现方式

        采用range分区,根据数据的时间字段(collectTime)实现分区存储,以年份为基准,不同的区域存储的是不同年份的数据,可以采用合并语句进行分区的合并,分区操作由MySQL暗箱完成,从用户的角度看,data表不会改变,程序代码无需更改。

    优缺点

       优点是range分区实现方便,没有破坏data表的结构,用户无需更改dao层代码和查询方式。而且可以提前预设分区,比如今年是2017年,用户可以将数据分区预设到2020年,方式灵活,便于扩充。

        缺点是数据存储依赖于分区的存储磁盘,一旦磁盘损坏,则会造成数据的丢失。

    2.3 data表更换为Myisam搜索引擎

    原理解释

    MySQL提供Myisam和InnoDB类型的搜索引擎,两种搜索引擎侧重点不同,可以根据实际的需要搭配使用,以达到最优的相应效果。Myisam引擎可以平均分布I/O,获得更快的速度,InnoDB注重事务处理,适合高并发操作。

    图1 BTREE存储结构

        从图中就可以看出,B+Tree的内部结点不存储数据,只存储指针,而叶子结点则只存储数据,不存储指针。并且在其每个叶子节点上增加了一个指向数据的指针。

        MyISAM引擎的索引结构为B+Tree,其中B+Tree的数据域存储的内容为实际数据的地址,也就是说它的索引和实际的数据是分开的,只不过是用索引指向了实际的数据,这种索引就是所谓的非聚集索引。

     实现方式

       修改data表的搜索引擎为Myisam,其它数据表不做更改。

    优缺点

       Myisam优点数据文件和索引文件可以放置在不同的目录,平均分布I/O。缺点是不合适高并发操作,对事务处理(修改和删除操作)的支持较差。

        InnoDB优点是提供了具有提交、回滚和崩溃恢复能力的事务安全,适合高并发操作的事务处理。缺点是处理效率相对Myisam较差并且会占用更多的磁盘空间以保留数据和索引。

        由于data表主要涉及的是查询和插入操作,提高速度是第一需求,所以可以将data表的搜索引擎改为Myisam。

    2.4 综合比较

    实现方式

       分表后的数据实际存储在子表中,总表只是一个外壳,merge分表编码较少,更改了表的结构。 
       分区后的数据存储的文件分成了许多小块,不更改表的结构。

    提高性能

        分表侧重点是数据分表存储,联表查询,注重提高MySQL的并发能力。

       分区侧重于突破磁盘的读写能力,从而达到提高MySQL性能的目的。

    实现的难易度

        采用merge分表与range分区两种方式难易度差不多,如果是用其他分表方式就比分区更为复杂。

        range分区实现是比较简单的,建立分区的表和平常的表没有什么区。

        两种方式基本上对开发端代码都是透明的。

    三、最终采用的优化思路

         故宫系统中的data表并发访问量不大,所以通过分表提高访问速度和并发效果不太显著,而且还可能破坏原有表的结构。而分区可以提高磁盘的读写能力,配合Myisam搜索引擎可以很大幅度提升查询速度。

        所以我们采用data表分区存储+Myisam搜索引擎+建立索引的方式来优化数据库的查询

    四、实施步骤及相关操作简介

    实施步骤

    1、将data表的搜索引擎由InnoDB更改为MyISAM

    ALTER TABLE `data` ENGINE=MyISAM;

    2、建立以collectime、originAddr字段的BTREE索引

    ALTER TABLE `data`

    ADD INDEX `collectTime` (`collectTime`) USING BTREE ,

    ADD INDEX `nodeId` (`originAddr`) USING BTREE ;

    3、分区之前将collectTime由char类型改为datetime/date类型,才能进行分区操作(注:char类型不支持分区操作)。

    ALTER TABLE `data`

    MODIFY COLUMN `collectTime`  datetime NOT NULL AFTER `ID`;

    4、采用range分区可以保证分区均匀

    注:这里由于最早的数据从12年开始,我们采用了半年分一个区,预分区到2021年1月份,实际分区结合具体情况而定。

    ALTER TABLE `data`

    partition by range(to_days(collectTime))

    (

    partition P0 values less than (to_days('2012-01-01')),

    partition P1 values less than (to_days('2012-07-01')),

    partition P2 values less than (to_days('2013-01-01')),

    partition P3 values less than (to_days('2013-07-01')),

    partition P4 values less than (to_days('2014-01-01')),

    partition P5 values less than (to_days('2014-07-01')),

    partition P6 values less than (to_days('2015-01-01')),

    partition P7 values less than (to_days('2015-07-01')),

    partition P8 values less than (to_days('2016-01-01')),

    partition P9 values less than (to_days('2016-07-01')),

    partition P10 values less than (to_days('2017-01-01')),

    partition P11 values less than (to_days('2017-07-01')),

    partition P12 values less than (to_days('2018-01-01')),

    partition P12 values less than (to_days('2018-07-01')),

    partition P12 values less than (to_days('2019-01-01')),

    partition P12 values less than (to_days('2019-07-01')),

    partition P12 values less than (to_days('2020-01-01')),

    partition P12 values less than (to_days('2020-07-01')),

    partition P12 values less than (to_days('2021-01-01')),

    )

    5、分区情况查询

    SELECT  *  FROM

      INFORMATION_SCHEMA.partitions

    WHERE

      TABLE_SCHEMA = schema()

      AND TABLE_NAME='data'; 

    图2 查看分区信息

    相关操作简介

    1、由于range分区函数无法识别char型字段,所以要在分区之前将collectTime由char类型改为datetime类型,才能进行range分区操作。

    2、采用range分区时,要用to_days(collectime)的分区方式,采用这种方式,在查询的时候只会在相应的分区查找,而如果不加to_days(),在查询的时候,会对全表进行扫描。

    3、分区优化后,查询速度提升主要体现在非跨区查询的时候,当查询条件均属于一个区域时,数据库可以快速定位到所查分区,而不会扫描全表。

    4、每次插入数据的时候,数据库会判定对应的collectTime属于哪个分区,从而存储到对应的分区中,不会影响其它分区。

    五、1500万数据测试对比

    1.经过数十次的多条件跨区与不跨区查询测试,相比没有做优化的data表,优化后查询速度提升90%以上。

    2、不跨区域查询响应速度<=0.5s,跨区查询在第一次比较慢,但之后在翻页查询的时候,相应速度<=1s。

    3、查询的时候由于计算机性能差异,所以同样的查询在不同的机器上查询速度会有所不用,我们采用的测试环境为i7 4500U酷睿 2核 4线程处理器 8G RAM 普通硬盘。

    表1 data表查询对比测试结果

    编号

    测试条件

    collectTime字段

    优化前

    查询时间

    优化后

    查询时间

    1

    NodeID,data1,data2,collectTime

    2017/1/19—2017/4/19

    2.59s

    0.74s

    2

    NodeID,data1,data2,collectTime

    2017/1/19—2017/7/19

    16.64s

    0.96s

    3

    NodeID,data1,data2,collectTime

    2016/8/19—2017/2/19

    34.3s

    2.49s

    4

    NodeID,data1,data2,collectTime

    2016/1/19—2017/1/19

    46.52s

    2.50s

    5

    NodeID,data1,data2,collectTime

    2015/2/19—2017/3/19

    78.12s

    3.73s

    6

    NodeID,data1,data2,collectTime

    2015/3/19—2017/4/19

    250.33s

    4.42s

    7

    NodeID,data1,data2,collectTime

    2015/1/19—2017/4/19

    226.10s

    4.39s

    8

    NodeID,data1,data2,collectTime

    2014/4/19—2017/4/19

    410.22s

    5.50s

    9

    NodeID,data1,data2,collectTime

    2014/2/19—2017/4/19

    437.50s

    5.50s

    10

    NodeID,data1,data2,collectTime

    2014/1/19—2017/4/19

    558.05s

    5.70s

    11

    NodeID,data1,data2,collectTime

    2012/4/19—2017/4/19

    --(响应时间过长)

    8.70s

    六、测试查询数据部分截图

     

    展开全文
  • 存储解密和优化

    2020-08-12 10:29:41
    存储解密和优化 1.文件存储 2.sp存储 3.ContentProvider 4.网络存储 5.SQLite存储 存储的方式选择: 数据量,数据结构,查询快慢 Ormlite LitePal GreenDao DBflow 数据库存储框架 框架实现...

    存储解密和优化

        1.文件存储
        
        2.sp存储

        3.ContentProvider 

        4.网络存储

        5.SQLite存储

        
        存储的方式选择: 数据量,数据结构,查询快慢

        Ormlite  LitePal GreenDao  DBflow  数据库存储框架

        
        框架实现原理:
        
        1.关系映射型 ( 将mode 映射成表)


            优点:方便快捷
        
        
            缺点:无法确定类对象中哪些成员变量不需要创建表字段,导致资源浪费

        2.注解加反射:

            
            优点:随心所欲创建开发者想要的表字段,不消耗资源


            缺点:需要自己定义的注解和处理注解

            
            反射(对效率有影响,但很小)
        
            

         框架步骤:

            1.创建数据库
                   
            2.创建表

            3.表的相关操作
            
            (缓存表字段)    
            分库分表,多角色,多权限
            

    展开全文
  • B+树上的叶子结点存储关键字以及相应记录的地址,叶子结点以上各层作为索引使用。在数据库中,B+树叶子结点的上层都是索引,为了方便查找,不涉及记录的地址,每个叶子结点存放相应的记录,也就是元组。并且元组按...
  • 给大家提供一些优化方面的方向思路,而具体业务场景的不同,使用的MySQL服务版本不同,都会使得优化方案的制定也不同。 1.1 MySQL介绍 ​MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网...
  • 排序引起的慢查询,通常不是那么容易发现,经常数据分布有关系。往往在业务刚开始时并没有什么问题,但是随着业务的发展,数据分布呈现一种特定的规律,导致了慢查询,或者并不是什么慢查询,但是随着并发请求数...
  • 论述了基于关系数据库SQL的查询方法,提出了基于关系数据库SQL的优化技术:更新系统统计信息、优化系统产品设计、合理使用系统索引、避免或简化排序、使用存储过程、改进数据库崩溃恢复技术和优化操作环境等。
  • 存储库用于保存在学习有关postgresSQL的课程期间实现/编写的注释,代码图表。 数据库设计过程 第一步是问我们要存储什么样的东西? 那东西有什么特性呢? 最后,这些属性中的每一个包含什么类型的数据? SQL...
  • Oracle查询优化

    2018-04-07 18:10:51
    按照优化层次可将查询优化分为:代数优化物理优化。代数优化是指关系代数表达式的优化,即按照一定的规则,通过对关系代数表达式进行等价交换,改变代数表达式中操作的次序组合,是查询执行更高效;物理优化则是...
  • 成本估算和查询优化 背景 查询优化器尝试查找SQL语句的最佳执行计划。 在所有考虑的候选计划中,优化器选择估计成本最低的计划。 优化器使用可用的统计信息来估算成本。 由于数据库具有许多内部统计数据和可用的工具...
  • HBase查询优化

    2018-08-05 13:54:00
    1.概述 ...本篇博客将从客户端优化和服务端优化两个方面来介绍,如何提高查询HBase的效率。 2.内容 这里,我们先给大家介绍如何从客户端优化查询速度。 2.1 客户端优化 客户端查询HBase,均通过...
  • 因为在关系数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作该表的类型),在Oracle和SQL Server等数据库中只有一种存储引擎,所有数据存储管理机制都是一样的。而M...
  • SQL查询优化器初探

    2017-07-17 21:06:57
    查询优化器是关系型数据库管理系统的核心之一,决定对特定的查询使用哪些索引、哪些关联算法,从而使其高效运行。查询优化器是SQL Server针对用户的请求进行内部优化,生成执行计划并传输给存储引擎来操作数据,最终...
  • 什么是DTO DTO(Data Transfer Object) :数据传输对象, ...数据传输对象与数据交互对象或数据访问对象之间是一个不具备有任何行为除了存储和检索的数据。 普通的关联查询关系 student学生表 private Inte...
  • 基于多重加权树的查询优化方法,研究了其并行查询计划模型、并行查询计划的复杂性模型和查询优化算法;语义查询优化方法将一个查询变换成一个或数个语义等价的查询,进而寻找并执行这些等价查询中具有较好实现策略的...
  • 众包查询的理解和优化 团队成员 弗洛里安·克兰(Florian Chlan) 弗朗索瓦·法奎特 约阿希姆·雨果诺(Joachim Hugonot)(团队负责人) 西蒙·罗德里格斯(Simon Rodriguez) 克里斯托夫·萨博(Kristof Szabo...
  • HBase查询优化 1.概述 HBase是一个实时的非关系型数据库,用来存储海量数据。但是,在实际使用场景中,在使用HBase API查询HBase中的数据时,有时会发现数据查询会很慢。本篇博客将从客户端优化服务端优化两个...
  • 存储库演示了使用追逐算法的语义查询优化。 有两种实现。 基于战术的(在LtacChase.v中实​​现) 在Chase.v中实​​施的Gallina一号 两者具有不同的权衡。 Ltac的实现可以重复利用Coq的丰富知识,例如...

空空如也

空空如也

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

关系存储和查询优化