精华内容
下载资源
问答
  • 最近遇到一个left join,表关联查询的SQL,很简单,如下: select count(0) from tableA left...tableA和tableB做左关联查询,但是速度巨慢,慢的想砸电脑~ tableA数据大概5万,tableB数据大概7万,2个的col0...

    最近遇到一个left join,表关联查询的SQL,很简单,如下:

    select count(0) from tableA left join tableB on tableA.col01 = tableB.col01 where col02 = ?

    tableA和tableB做左关联查询,但是速度巨慢,慢的想砸电脑~

    tableA表数据大概5万,tableB数据大概7万,2个表的col01列都添加了索引

    但是还是无比巨慢,然后百度了各种方式:

    • 1、左表数据量保持最低;
    • 2、加入索引;
    • 3、where里面尽可能过滤数据

    但是没任何用处,最后从别人那里才得知,原来是表数据字符编码导致的问题,具体原因如下:

     字符集排序规则
    tableAutf8mb4utf8mb4_general_ci
    tableButf8utf8_general_ci

    2个表的字符编码不一样,导致left join查询速度非常的慢

    所以,需要把2个表的字符编码,改成一致即可

    展开全文
  • 大家在开发过程中总能遇到某个数据需要加个字段数据的情况,然而有些数据要关联到大,就会造成总体查询效率大大降低的情况,这篇文章提供一个小思路,小技巧分享给大家。 上代码(需求场景是在主数据中加入一个...

    大家在开发过程中总能遇到某个数据表需要加个字段数据的情况,然而有些数据要关联到大表,就会造成总体查询效率大大降低的情况,这篇文章提供一个小思路,小技巧分享给大家。
    上代码(需求场景是在主数据中加入一个统计金额的数据,统计金额需要关联某个大表,造成大表连大表的尴尬情况):

    public PageInfo<Map> pageBill(Map params,Integer pageNum, Integer pageSize) {
    		//分页 分页场景使用更佳
            PageMethod.startPage(pageNum, pageSize); 
            //主数据查询
            List<Map> list = someBillMapper.listBill(params);
            if(list != null && list.size() > 0){
                /**
                   先将从数据需要用到的id提炼出来,此id是主数据与从数据关联的id(a.id = b.pid),
                   通过此id,从数据查询就可以精确高效的查询出需要的数据。
                */
                StringBuilder sb = new StringBuilder();
                for (Map m :list) {
                    sb.append(","+m.get("id"));
                }
                /**
                	从数据(通过上面得到的id直接查询需要的数据:
                	大概理解就是: select id,pid,amount from table where id in (提炼出来的id集)
                	此为简化的 实际场景复杂的多。
                */
                List<Map> otherAccount = someBillMapper.getOtherAccount(sb.toString().substring(1));
                /**
                	核心1:将查询出来的从数据通过map集合存起来,
                	key为从数据的关联pid(主 a.id = b.pid 从关系),value为整行map数据(id,pid,amount)
                */
                Map comMap = new LinkedHashMap();
                for (Map m :otherAccount) {
                    comMap.put(m.get("pid"),m);
                }
    			/**
    				核心2:遍历主数据,比对id,id相等将从数据插入主数据,实现主数据增加从数据。
    			*/
                for (Map p : list ) {
                    if(comMap.containsKey(p.get("id"))){
                        Map son= (Map) comMap.get(p.get("id"));
                        p.put("amount",son.get("amount"));
                    }
                }
            }
            PageInfo<Map> pageInfo = new PageInfo<Map>(list);
            return pageInfo;
        }
    

    (注意:一定要避免出现for循环里面再for循环(假如分页出来是100条,两个for循环是100+100,如果是双for循环就是100*100=10000次了),也不要在for循环里面进行数据库操作)
    大体就是这种思路,公司因为出台慢SQL标准,所以要尽量避免一个sql关联太多表查询,需要将业务拆分,以前的多表关联都要拆成单表,双表,在代码里跑,可以减轻数据库的负担同时减少慢sql的数量。不管几个表,拆解思路基本都是这样子。

    展开全文
  •  1、软件设计初期,需要一对一关联应该设计在一张大表里,这样虽然字段多些,但是对于查询速度提升是非常明显的,特别是在排序的情况下。  2、我们在关联的时候可能需要排序的之前其中的一张或者两张,...
  • 最后创建视图,为这几张查询结果,视图: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `vrs_emp` AS select `a`.`EmpSysID` AS `EmpSysID`,`a`.`EmpNo` AS `EmpNo`,`a`.`EmpName...
  • SQL两表关联查询where条件中等号两端字段顺序对效率的影响   现有两A(大)、B(小)作关联查询,SQL语句如下:   SQL1:select * from A,B where A.id = B.id   SQL2:select * from A,B where B.id = A.id ...
    SQL两表关联查询where条件中等号两端字段顺序对效率的影响 
    
     
    现有两表A(大)、B(小)作关联查询,SQL语句如下:
     
    SQL1:select * from A,B where A.id = B.id
     
    SQL2:select * from A,B where B.id = A.id
     
    在写SQL的时候可能这个顺序都是随便写的,当然SQL1和SQL2 执行的结果是完全相同的,那效率是否也一样呢,答案是否定的。
     
    SQL1 相当于将A表的id取出来,然后遍历B表进行查询,SQL2相当于将B表的id取出来,然后遍历A表进行查询。
     
    如果表A的记录条数为m,表B的记录条数为n,SQL1比较的次数为n的m次方,SQL2比较的次数为m的n次方。
     

    所以在一般情况下,表A大,表B小的时候,SQL2的效率更高。

    http://www.2cto.com/database/201306/223035.html

    展开全文
  • 干货 | Elasticsearch多表关联设计指南

    万次阅读 多人点赞 2019-03-24 23:45:37
    Elasticsearch多表关联问题是讨论最多的问题之一,如:博客和评论的关系,用户和爱好的关系。 多表关联通常指:1对多,或者多对多。 本文以星球问题会出发点,引申出ES多表关联认知,分析了4种关联关系的适用场景...

    Elasticsearch 最少必要知识实战教程直播回放

    0、题记

    Elasticsearch多表关联问题是讨论最多的问题之一,如:博客和评论的关系,用户和爱好的关系。
    多表关联通常指:1对多,或者多对多。
    本文以星球问题会出发点,引申出ES多表关联认知,分析了4种关联关系的适用场景、优点、缺点,
    希望对你有所启发,为你的多表关联方案选型、实战提供帮助。

    1、抛出问题

    1.1 星球典型问题

    在这里插入图片描述

    1.2 社区典型问题

    在这里插入图片描述

    1.3 QQ群典型问题

    关系型数据库中的多表之间的关联查询,ES中有什么好的解决方案?
    如果我把关联关系的表迁移到ES中放到一个type下,文档结构除了对象之间的嵌套还有什么好的解决方案?

    2、基础认知

    2.1 关系型数据库

    关系数据库是专门为关系设计的,有如下特点

    • 可以通过主键唯一地标识每个实体(如Mysql中的行)。
    • 实体 规范化 。唯一实体的数据只存储一次,而相关实体只存储它的主键。只能在一个具体位置修改这个实体的数据。
    • 实体可以进行关联查询,可以跨实体搜索。
    • 支持ACID特性,即:单个实体的变化是 原子的 , 一致的 , 隔离的 , 和 持久的 。
    • 大多数关系数据库支持跨多个实体的 ACID 事务。

    关系型数据库的缺陷

    • 第一:全文检索有限的支持能力。 这点,postgresql已部分支持,但相对有限。
    • 第二:多表关联查询的耗时很长,甚至不可用。之前系统开发中使用过Mysql8个表做关联查询,一次查询等待十分钟+,基本不可用。

    2.2 Elasticsearch

    Elasticsearch ,和大多数 NoSQL 数据库类似,是扁平化的。索引是独立文档的集合体。 文档是否匹配搜索请求取决于它是否包含所有的所需信息和关联程度。

    Elasticsearch 中单个文档的数据变更是满足ACID的, 而涉及多个文档时则不支持事务。当一个事务部分失败时,无法回滚索引数据到前一个状态。

    扁平化有以下优势

    1. 索引过程是快速和无锁的。
    2. 搜索过程是快速和无锁的。
    3. 因为每个文档相互都是独立的,大规模数据可以在多个节点上进行分布。

    2.3 Mysql VS Elasticsearch

    • mysql才擅长关系管理,而ES擅长的是检索。

    • Medcl也曾强调:“如果可能,尽量在设计时使用扁平的文档模型。” Elasticsearch的关联存储、检索、聚合操作势必会有非常大的性能开销。
      在这里插入图片描述

    3 Elasticsearch关联关系如何存储

    关联关系仍然非常重要。某些时候,我们需要缩小扁平化和现实世界关系模型的差异。
    以下四种常用的方法,用来在 Elasticsearch 中进行关联数据的管理:

    3.1 应用端关联

    这是普遍使用的技术,即在应用接口层面来处理关联关系。
    针对星球问题实践,

    1. 存储层面:独立两个索引存储。
    2. 实际业务层面分两次请求:

    第一次查询返回:Top5中文姓名和成绩;
    根据第一次查询的结果,第二次查询返回:Top5中文姓名和英文姓名;

    将第一次查询结果和第二次查询结果组合后,返回给用户。
    即:实际业务层面是进行了两次查询,统一返回给用户。用户是无感知的。

    适用场景数据量少的业务场景。
    优点:数据量少时,用户体验好。
    缺点:数据量大,两次查询耗时肯定会比较长,影响用户体验。

    引申场景:关系型数据库和ES 结合,各取所长。将关系型数据库全量同步到 ES 存储,不做冗余存储。
    如前所述:ES 擅长的是检索,而 MySQL 才擅长关系管理。所以可以考虑二者结合,使用 ES 多索引建立相同的别名,针对别名检索到对应 ID 后再回 MySQL 查询,业务层面通过关联 ID join 出需要的数据。

    3.2 宽表冗余存储

    对应于官方文档中的“Data denormalization”,官方直接翻译为:“非规范化你的数据”,总感觉规范化是什么鬼,不好理解。
    通俗解释就是:冗余存储,对每个文档保持一定数量的冗余数据可以在需要访问时避免进行关联。

    这点通过logstash 同步关联数据到ES时,通常会建议:先通过视图对Mysql数据做好多表关联,然后同步视图数据到ES。此处的视图就是宽表。

    针对星球问题实践:姓名、英文名、成绩两张表合为一张表存储。

    适用场景:一对多或者多对多关联。

    优点:速度快。因为每个文档都包含了所需的所有信息,当这些信息需要在查询进行匹配时,并不需要进行昂贵的关联操作。

    缺点:索引更新或删除数据,应用程序不得不处理宽表的冗余数据;
    由于冗余存储,导致某些搜索和聚合操作可能无法按照预期工作。

    3.3 嵌套文档(Nested)存储

    Nested类型是ES Mapping定义的集合类型之一,它是比object类型更NB的支持独立检索的类型。
    举例:有一个文档描述了一个帖子和一个包含帖子上所有评论的内部对象评论。可以借助 Nested 实现。

    实践注意1:当使用嵌套文档时,使用通用的查询方式是无法访问到的,必须使用合适的查询方式(nested query、nested filter、nested facet等),很多场景下,使用嵌套文档的复杂度在于索引阶段对关联关系的组织拼装。

    推荐实践:https://blog.csdn.net/laoyang360/article/details/82950393

    实践注意2

    index.mapping.nested_fields.limit 缺省值是50。即:一个索引中最大允许拥有50个nested类型的数据。
    index.mapping.nested_objects.limit 缺省值是10000。即:1个文档中所有nested类型json对象数据的总量是10000。
    

    适用场景:1 对少量,子文档偶尔更新、查询频繁的场景。
    如果需要索引对象数组并保持数组中每个对象的独立性,则应使用嵌套 Nested 数据类型而不是对象 Oject 数据类型。

    优点:nested文档可以将父子关系的两部分数据(举例:博客+评论)关联起来,可以基于nested类型做任何的查询。
    缺点:查询相对较慢,更新子文档需要更新整篇文档。

    3.4 父子文档存储

    注意:6.X之前的版本的父子文档存储在相同索引的不同type中。而6.X之上的版本,单索引下已不存在多type的概念。父子文档Join的都是基于相同索引相同type实现的。

    Join类型是ES Mapping定义的类型之一,用于在同一索引的文档中创建父/子关系。 关系部分定义文档中的一组可能关系,每个关系是父名称和子名称。

    实践参考:https://blog.csdn.net/laoyang360/article/details/79774481

    适用场景:子文档数据量要明显多于父文档的数据量,存在1 对多量的关系;子文档更新频繁的场景。

    举例:1 个产品和供应商之间是1对N的关联关系。
    当使用父子文档时,使用has_child 或者has_parent做父子关联查询。

    优点:父子文档可独立更新。
    缺点:维护Join关系需要占据部分内存,查询较Nested更耗资源。

    4 小结

    在这里插入图片描述

    1. 在Elasticsearch开发实战中对于多表关联的设计要突破关系型数据库设计的思维定式
    2. 不建议在es做join操作,parent-child能实现部分功能,但是它的开销比较大,如果可能,尽量在设计时使用扁平的文档模型。
    3. 尽量将业务转化为没有关联关系的文档形式,在文档建模处多下功夫,以提升检索效率。
    4. Nested&Join父子文选型必须考虑性能问题。 nested 类型检索使得检索效率慢几倍,父子Join 类型检索会使得检索效率慢几百倍。

    以上内容,实际官方文档都有明确的描述。我把内容加上自己的理解,作了精炼和解读。
    再次强调:第一手资料的重要性。但本着“再显而易见的道理,也有N多人不知道”的原则,一定要读英文官方文档,加深认知理解。

    Elasticsearch多表关联你是如何做的呢?欢迎留言写下您的思考。

    参考:
    [1] https://www.elastic.co/guide/en/elasticsearch/guide/current/relations.html
    [2] rockybean 教程

    在这里插入图片描述
    铭毅天下——Elasticsearch基础、进阶、实战第一公众号

    展开全文
  • 提高多表关联数据查询效率

    千次阅读 2016-11-22 09:15:08
    前期做了一个项目,使用sqlservice数据库,后来发现数据量越多,对应的系统反应速度就越慢,7万多条数据反应要5~6秒,后来试了一下,看看视图是否能提高点效率,虽然网上说是没用的,可是自己还是试了一下,结果的确...
  • MySQL多表关联查询效率高点还是多次单表查询效率高,为什么? 《阿里巴巴JAVA开发手册》里面写超过三张表禁止join 这是为什么?这样的话那sql要怎么写?...
  • 索引的第5个优点是可以加快连接的速度。要说明这个问题,首先要降到连接的3种算法,分别是:hash,nested loop,merge ,这里主要讲hash,nested loop这2种算法。现在有A、B两个,A数据量1000w,B数据2000...
  • 详解MongoDB中的多表关联查询($lookup) 一. 聚合框架  聚合框架是MongoDB的高级查询语言,它允许我们通过转换和合并多个文档中的数据来生成新的单个文档中不存在的信息。 聚合管道操作主要包含下面几个部分:...
  • Oracle多表关联update优化

    千次阅读 2016-10-10 22:21:20
    我们都知道,在Oracle数据库中,对单进行更新采用标准update语法效率是非常高的,语法为UPDATE 名称 SET 列名称 = 新值 WHERE 列名称 = 某值,如下SQL... 如果涉及关联表,并把一个的字段值更新到另一个中...
  • 问题7:数据库多表关联方式

    千次阅读 2016-12-30 15:43:13
    在关系型数据库中,多表关联方式是影响性能最大的技术。为什么会把mysql定义为中小型数据库?主要原因是mysql是不支持hash join的。...虽然单的数据量大了,但是查询速度快了非常多,从几十秒降到
  • 在实时数仓中,我们经常需要做维表关联,但是用户维一般在业务数据库中,业务方是不允许大数据部门直接到业务数据库进行维表关联,因为这会影响线上业务。此时我们需要将用户维采集到大数据平台,然后事实就...
  • 超大表关联的优化方法

    千次阅读 2016-05-13 13:36:07
    但随着数据量多了,之间的关联效率就开始差强人意了。 我们今天模拟一下两个大之间关联的优化,核心思想是分表。
  • oracle多表关联更新

    千次阅读 2019-06-25 14:02:25
    oracle的更新语句不通MSSQL那么简单易写,就算写出来了,但执行时可能会报 这是由于set哪里的子查询查出了多行数据值,oracle规定一对一更新数据,所以提示出错。要解决这样必须保证查出来的值一一对应。 原理 ...
  • ORACLE大表关联更新提速方法

    千次阅读 2019-04-04 10:09:41
    前提知识 在oracle数据库中,每一行记录都有一个该记录的唯一标识rowid,rowid一旦确定不会随意变动。rowid由10个字节存储,在数据库查询中...在两关联更新时,一般都会在上建立索引。在上建立索引时,ora...
  • mysql 关联查询

    千次阅读 2018-08-29 12:02:50
    数据库在通过连接两张或多张表来返回...所有查询出的结果都是能够在连接的中有对应记录; t_employee: t_dept: select e.empName,d.deptName from t_employee e INNER JOIN t_dept d ON e.dept = ...
  • MySQL 影响SQL查询速度

    千次阅读 2017-08-08 15:57:27
    查询速度为什么会慢SQL请求处理步骤 客户端发送SQL请求给MySQL服务器 MySQL服务器会在查询缓存中进行检查,查看是否可以在查询缓存中命中 服务端会对SQL进行解析、预处理再由优化器生成对应的执行计划 根据执行计划...
  • ELK生态:ES中的数据关联查询

    千次阅读 2020-03-28 11:46:52
    先说下结论,如果不做特殊处理,es是无法完成类似与Join的关联查询的。 2、ES如何做关联 官网里面有几种支持关联查询的办法: 2.1 应用程序做关联 这个没有什么好说的,其实不算真正的关联,需要先查询...
  • 临时表关联数据时效率低的问题
  • 讲解关联查询底层算法中的:嵌套循环连接、块嵌套连接、索引嵌套循环连接
  • SQL慢查询优化之联表查询

    千次阅读 2018-04-24 15:04:08
    MySQL 表关联的算法是 Nest Loop Join,是通过驱动的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个查询数据,然后合并结果。 2、驱动定义: ## 制定了联接条件时,...
  • SQL表关联中的逻辑与效率

    千次阅读 2015-05-15 16:39:38
    感觉SQL的关联蛮有意思的,在这里写一下自己对这玩意的认识的总结吧。本篇主要是通过目前接触到的两个数据库–vertica和impala(Oracle和MySql也有,但是个人用的不多)的执行计划来写的,优化器做到差不多地步的...
  • Mysql中使用中间提高统计查询速度

    万次阅读 2014-03-05 20:44:00
    对于数据量较大的,在其上进行统计查询通常会效率很低,并且还要考虑统计查询是 否会对在线的应用产生负面影响。通常在这种情况下,使用中间可以提高统计查询的效率, 下面通过对session 的统计来介绍中间的...
  • 连接顺序对查询效率的影响

    千次阅读 2019-05-23 19:58:23
    带条件查询表连接1000条,poetries为主(数据长度4.52 MB) 不带条件查询表连接13219条,poetries为主(数据长度4.52 MB) 带条件查询表连接1000条,poet为主(数据长度160.00 KB) 不带条件查询表连接...
  • 优化方案3: 采用cache+broadcast方式,提升至20s(该方案将小缓存至内存,进行map侧关联) 方案具体实施 ----方案2:or 改成 union(运行35s) I  select  aa.ip  from  (  SELECT  bb.ip ip  FROM  (  ...
  • A,B两个数据规模十几万,数据规模都不大,单机MySQL够用了,在单机的基础上要关联的数据,先说一个极端情况,A,B两个都没有索引,并且关联是笛卡尔积,那关联结果会爆炸式增长,可能到亿级别,这个时候网络...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 98,495
精华内容 39,398
关键字:

表关联影响表查询速度