精华内容
下载资源
问答
  • 1.对树形数据结构的查询,且层级较少,采用添加分级字段的方式优化 2.针对前端使用ztree ,list直接返回结果集,存在授权问题的查询,采用添加层次码的方案 1.针对需要树结构的查询 在有限的多级目录情况下,可以...

    在有限的经验下,主要讨论以下两部分内容

    1.对树形数据结构的查询,且层级较少,采用添加分级字段的方式优化
    2.针对前端使用ztree ,list直接返回结果集,存在授权问题的查询,采用添加层次码的方案

    1.针对需要树结构的查询
    • 在有限的多级目录情况下,可以添加分级字段,(1级目录,2级目录,3级目录),
    • 1.在这个基础上假设只有三级目录,那么我们可以直接分3次查询,此时得到了3个关于1、 2、 3层级对应的list;
    • 2.上一步的操作,使我们极大的改善了数据库查询的效率,只需要3次查询即可拿到所有的数据,但同样,需要解决拼树的问题
    • 3.对树的拼接,在目前大部分服务器使用多核的情况下,推荐使用java8 Stream操作对原始list进行加工。

    基于以上假设,单条数据如下,即正常查询list中数据泛型为Regions

    		@Data
    		public class Regions {
    		    private Integer objectId;//id
    		    private String regionName;//区域名称
    		    private String regionCode;//区域编码
    		    private Integer level;//区域级别
    		    private String parentCode;//上级区域编号
    		}
    

    返回到页面的对象

    @Data
    	public class TreeNode {
    	    private String regionName;
    	    private String regionCode;
    	    private String parentCode;
    	    List<TreeNode> children;
    	}
    

    算法写法

    import javax.annotation.Resource;
    import java.util.List;
    import java.util.stream.Collectors;
    
    import static java.util.stream.Collectors.toList;
    
    @Service
    public class RegionServiceImpl implements RegionService {
        @Resource
        private RegionsMapper regionsMapper;
    
        @Override
        public List<TreeNode> getRegionTree() {
        	//根据层级关系查询了3次
            List<Regions> level1 = regionsMapper.getListByLevel(1);
            List<Regions> level2 = regionsMapper.getListByLevel(2);
            List<Regions> level3 = regionsMapper.getListByLevel(3);
            //先将最末级转为TreeNode 的形式
            List<TreeNode> node3 = level3.stream().map(l3 -> {
                TreeNode treeNode = new TreeNode();
                treeNode.setRegionCode(l3.getRegionCode());
                treeNode.setRegionName(l3.getRegionName());
                treeNode.setParentCode(l3.getParentCode());
                //没有下级children 不做处理
                return treeNode;
            }).collect(toList());
    
            //对二级目录进行抽象
            List<TreeNode> node2 = level2.stream().map(a -> {
                TreeNode treeNode = new TreeNode();
                treeNode.setRegionName(a.getRegionName());
                treeNode.setRegionCode(a.getRegionCode());
                treeNode.setParentCode(a.getParentCode());
                treeNode.setChildren(node3.stream().filter(l3 -> l3.getParentCode().equals(a.getRegionCode())).collect(toList()));
                return treeNode;
            }).collect(toList());
            //对一级目录继续抽象并返回
            List<TreeNode> node1 = level1.stream().map(a -> {
                TreeNode treeNode = new TreeNode();
                treeNode.setRegionName(a.getRegionName());
                treeNode.setRegionCode(a.getRegionCode());
                treeNode.setParentCode(a.getParentCode());
                treeNode.setChildren(node2.stream().filter(l2 -> l2.getParentCode().equals(a.getRegionCode())).collect(toList()));
                return treeNode;
            }).collect(toList());
            return node1;
        }
    }
    
    

    在这里插入图片描述
    1000条数据,耗时1s多一点,这里作者连的是远程数据库加上网速也不是很乐观,若网络连接畅通,还会快一些。

    备注 :若层级较多时,可以进一步简化(抽象),以for循环的形式读取每个层级的数据,然后以同样以for循环的形式对数据进行从低到高的封装,一个方法做循环层级读取数据,另一个方法做对多个层级进行 树的推导
    2.针对zTree授权树的查询优化
    • 前言:
    • 前端使用zTree插件时,可以进一步简化后端的查询,也不存在树的结构化返回,实际上,如果没有权限的管理,我们只需要拿到所有的数据,并对数据字段进行取别名,使其符合zTree的规范即可。

    • 当目录层级存在权限时,比如子目录存在权限,但父目录并没有授权,但在前端界面中,我们同样需要加载父级目录显示给用户,以维持整个目录的结构。

    • 思考

      理论上,我们从上往下进行递归,每验证一个目录需要验证 它的子目录 是否存在权限,如果存在权限,那么就将这个目录存放到list中,并继续递归它的下级目录
      弊端 :当数据量较大时,其需要不断查询下级目录,并不断做验证权限的查询,验证 本级及下级是否存在权限 理论上也需要做递归,这样就导致了大量的查询操作,使效率极大的被限制。

    • 解决方案

      加入层次码(同样是不可重复的)字段,即 比如上级菜单的层次码为 xxx ,那么下级菜单的层次码 必定为xxxyyy 或xxxzzz,并且它的多层子目录的层次码必定为xxx开头。
      基于以上的假设,我们的思路如下:

      • 1.查询到所有的已授权目录的层次码,并标识为这样的一个List 集合:List<Authority> authList Authority 主要字段为 ccm(层次码)

      • 2.查询所有的目录,即不管是否存在授权都进行查询 ,存放于:List<Menu> menus ,Menu的主要字段是业务字段,及ccm 字段,注意menus 是可以直接返回到前端的一个list,我们现在要做的只是筛选授权的数据,并返回即可

      • 3.使用java 8 Stream 方法,根据层次码进行筛选,代码如下:

        public List<Menus> getAuthroityMenus(){
            List<Menus> menus = new ArrayList<>();//假设这里查询了数据库,获得了所有的目录
            List<Authority> authList = new ArrayList<>();//假设这里查询了数据库,得到了所有授权的目录
            
           return menus.stream().filter(a->{
                //a,表示每一个menu对象
                //同样的,下方的b 对应每一个Authority对象
                //遍历授权目录,如果 授权目录的ccm   以此刻的Menu.ccm 开头,则表示这个menu是已授权的上级,
                if (authList.stream().filter(b->b.getCcm().startsWith(a.getCcm())).count()>0){
                    return true;
                }
                return false;
            }).collect(Collectors.toList());
            
        }
    

    java8 的写法有不了解的可以搜下相关资料,博主自己也写过一些java 8相关的内容,这里就不做具体的细节介绍了。
    有疑问或有其他更好的方法,也希望大家在评论区积极发言!

    展开全文
  • MySQL 慢查询优化

    2021-01-19 21:09:20
    为什么查询速度会慢1.慢是指一个查询的响应时间长。...MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询。将结果返回给客户端2.数据访问是否向数据库请求了不需要的数据是否扫描额外的记录3.查询的方...

    为什么查询速度会慢

    1.慢是指一个查询的响应时间长。一个查询的过程:客户端发送一条查询给服务器

    服务器端先检查查询缓存,如果命中了缓存,则立可返回存储在缓存中的结果。否则进入下一个阶段

    服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划。

    MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询。

    将结果返回给客户端

    2.数据访问是否向数据库请求了不需要的数据

    是否扫描额外的记录

    3.查询的方式一个复杂的查询还是多个简单的查询

    切分查询(将大查询切分成小查询,循环完成小查询)

    分解关联查询

    慢查询分析

    问题SQL

    把复杂的SQL分成多个简单SQL并执行,查看具体那个字段会慢,区分度不高。

    EXPLAIN

    显示SQL如何使用索引的执行计划。

    执行计划的参数:

    table 显示这一行的数据是关于哪张表的

    type 显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

    possible_keys 显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

    key 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引

    key_len 使用的索引的长度。在不损失精确性的情况下,长度越短越好

    ref 显示索引的哪一列被使用了,如果可能的话,是一个常数

    rows 扫描请求数据的行数

    Extra 关于MYSQL如何解析查询的额外信息

    PROFILE

    显示SQL执行消耗系统资源的信息。

    查询执行的过程MySQL客户端/服务器通信协议是“半双工”的。客服端/服务器端都可以向对方发送数据,但不能同时发生。所以我们无法也无须将一个消息切成小块独立来发送。

    这种协议没办法进行流量控制。

    客户端发送请求的数据包大小由参数max_allowed_packet限制。如果查询太大,服务端会拒绝接受更多的数据并抛出相应的错误。

    服务器端返回的多个数据包,客户端必须完整接受。

    1.查询状态 SHOW FULL PROCESSLIST

    mysql>SHOW FULL PROCESSLIST;

    Id User Host db Command Time State Info

    ------ ------ --------------- ------------ ------- ------ ------ -----------------------

    1 root localhost:61316 laravel_blog Query 0 (NULL) show FULL processlist

    2 root localhost:61319 (NULL) Sleep 94 (NULL)

    对于一个连接,或者说一个线程,任何时刻都有一个状态,该状态表示了MySQL当前正在做什么。

    2.查询缓存

    -- 查看缓存是否开启 (query_cache_type 为 ON 表示已经开启

    mysql> show variables like '%query_cache%';

    +------------------------------+----------+

    | Variable_name | Value |

    +------------------------------+----------+

    | have_query_cache | YES |

    | query_cache_limit | 1048576 |

    | query_cache_min_res_unit | 4096 |

    | query_cache_size | 20971520 |

    | query_cache_type | ON |

    | query_cache_wlock_invalidate | OFF |

    +------------------------------+----------+

    检查sql是否命中缓存。命中则检查一次用户权限后返回,这个检查是通过一个对大小写敏感的哈希查找实现的。两次查询只要有一个字节的不同就会失败。否则将进入下一个阶段。

    当sql中有不确定的数据时,则不会被缓存。例如用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表,其查询结果都不会被缓存。

    3.查询优化

    语法解析器和预处理

    MySQL通过关键字将sql语句进行解析,并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如sql中是否使用了错误的关键字或者关键字的顺序是否正确等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等。

    查询优化器

    经过前面的步骤生成的语法树被认为是合法的了,并且由优化器将其转化成查询计划。多数情况下,一条查询可以有很多种执行方式,最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

    MySQL使用基于成本的优化器,通过计算成本选择其中最小的一个。通过SHOW STATUS LIKE 'Last_query_cost';查看成本。成本的最小单位是随机读取一个4K数据页的成本。

    MySQL的查询优化器是一个非常复杂的部件,它使用了非常多的优化策略来生成一个最优的执行计划:重新定义关联表的顺序

    将外连接转化成内连接

    使用等价变换规则

    优化count()、min()、max()

    预估并转化为常数表达式

    覆盖索引描述

    子查询优化

    提前终止查询

    等值传播

    列表IN()的比较

    上面列举了一些,随着MySQL的不断发展,优化器使用的优化策略也在不断的进化。

    查询执行引擎

    在完成解析和优化阶段以后,MySQL会生成对应的执行计划,查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成,这些接口被称为handler API。

    查询过程中的每一张表由一个handler实例表示。实际上,MySQL在查询优化阶段就为每一张表创建了一个handler实例,优化器可以根据这些实例的接口来获取表的相关信息,包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能,但其底层仅有几十个接口,这些接口像搭积木一样完成了一次查询的大部分操作。

    返回结果

    查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据,MySQL仍然会返回这个查询的相关信息,比如该查询影响到的行数以及执行时间等。

    如果查询缓存被打开且这个查询可以被缓存,MySQL也会将结果存放到缓存中。

    结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时,就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存,也可以让客户端第一时间获得返回结果。需要注意的是,结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送,再通过TCP协议进行传输,在传输过程中,可能对MySQL的数据包进行缓存然后批量发送。

    以上内容希望帮助到大家,更多PHP大厂PDF、PHP进阶架构视频资料、PHP精好文可以关注公众号:PHP开源社区 或者访问:

    展开全文
  • 手册上查询优化器概述查询优化器的任务是发现执行SQL查询的最佳方案。大多数查询优化器,包括MySQL的查询优化器,总或多或少地在所有可能的查询评估方案中搜索最佳方案。对于联接查询,MySQL优化器所调查的可能的...

    手册上查询优化器概述

    查询优化器的任务是发现执行SQL查询的最佳方案。大多数查询优化器,包括MySQL的查询优化器,总或多或少地在所有可能的查询评估方案中搜索最佳方案。对于联接查询,MySQL优化器所调查的可能的方案数随查询中所引用的表的数目呈指数增长。对于小数量的表(典型小于7-10),这不是一个问题。然而,当提交的查询更大时,查询优化所花的时间会很容易地成为服务器性能的主要瓶颈。

    查询优化的一个更加灵活的方法是允许用户控制优化器详尽地搜索最佳查询评估方案。一般思想是优化器调查的方案越少,它编译一个查询所花费的时间越少。另一方面,因为优化器跳过了一些方案,它可能错过一个最佳方案。

    优化器关于方案数量评估的行为可以通过两个系统变量来控制:

    optimizer_prune_level变量告诉优化器根据对每个表访问的行数的估计跳过某些方案。我们的试验显示该类“有根据的猜测”很少错过最佳方案,并且可以大大降低查询编辑次数。这就是为什么默认情况该选项为on(optimizer_prune_level=1)。然而,如果你认为优化器错过了一个更好的查询方案,则该选项可以关闭(optimizer_prune_level=0),风险是查询编辑花费的时间更长。请注意即使使用该启发,优化器仍然可以探测呈指数数目的方案。

    ptimizer_search_depth变量告诉优化器对于每个未完成的“未来的”方案,应查看多深,以评估是否应对它进一步扩大。optimizer_search_depth值较小会使查询编辑次数大大减小。例如,如果optimizer_search_depth接近于查询中表的数量,对12、13或更多表的查询很可能需要几小时甚至几天的时间来编译。同时,如果用optimizer_search_depth等于3或4编辑,对于同一个查询,编译器编译时间可以少于1分钟。如果不能确定合理的optimizer_search_depth值,该变量可以设置为0,告诉优化器自动确定该值。

    我们可以通过show variables 来查看这些参数

    fcaf560f93d76aaf70a6922cf3becfea.png 

    备注(手册网址:http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter)

    个人理解

    从官方手册上看,可以理解为,MySQL采用了基于开销的优化器,以确定处理查询的最解方式,也就是说执行查询之前,都会先选择一条自以为最优的方案,然后执行这个方案来获取结果。在很多情况下,MySQL能够计算最佳的可能查询计划,但在某些情况下,MySQL没有关于数据的足够信息,或者是提供太多的相关数据信息,估测就不那么友好了。

    但是感觉手册上,并没有说MySQL怎么去寻找最优方案呢?

    通过查询相应的资料,个人理解如下

    MySQL优化器中,一个主要的目标是只要可能就是用索引,而且使用条件最严格的索引来尽可能多、尽可能快地排除那些不符合索引条件的数据行,说白了就是选择怎样使用索引,当然优化器还受其他的影响。为了更直观,下面将通过例子来说明。

    创建一个表:

    CREATE TABLE t8(

    id1 INT NOT NULL ,

    id2 INT NOT NULL,

    KEY id1_key(`id1`),

    KEY id2_key(`id2`)

    ) ENGINE=MYISAM DEFAULT CHARSET=utf8;

    1

    2

    3

    4

    5

    6

    7

    插入几行数据如下:

    65313e9a64dda8a39ba414467c0669f5.png 

    当我执行如下查询语句时候,查询优化器会怎样进行优化呢?

    select * from t8 where id1=1 and id2=0;

    当然,MySQL不会傻到,从t8表中的一行开始,然后一行行的去比较,id1与id2。优化器会先分析数据表,得知有索引id1_key与id2_key,如果先判断id1_key的话,然后需要从4行数据中排除3行数据;如果先判断id2_key的话,然后需要从2行中排除1行。对人来说,这两种方式没有什么区别,但是对于程序而言,先判断id2_key需要较少的计算和磁盘输入输出。因此,查询优化器会规定程序,先去检验id2_key索引,然后在从中挑出id2为0的数据行。

    通过下图,我们可以看出,可以选择的索引有id1_key与id2_key,但是实际用到的索引只有id2_key

    aab7ffdb5e5bcafc785e09dea12c3a05.png 

    如果将SQL语句改为 select * from t8 where id1=1 and id2=0;执行情况也是一样的,不区分前后。如下图:

    af0f1dcb5a8df6c3a8a1044a0d0d758a.png

    当然,如果将程序,修改为如下

    select * from t8 where id1=5 and id2=0;

    也可以分析得出,会使用id1_key索引

    1f632dd6112572b86c5a0fc0a1e9583c.png

    当然,如果在创建一个复合索引

    ALTER TABLE t8 ADD KEY id1_id2_key(`id1`,`id2`)

    此时,在此执行select * from t8 where id1=1 and id2=0; 当然会考虑使用id1_id2_key索引。

    c120ba4c15039b3abc415069fa8f4670.png 

    通过上面的例子,可以理解查询优化器在查询的时候,是选择哪一个索引作为最合适的索引。除此,也提示我们,要慎重选择创建索引。如,上面创建了三个索引(id1_key、id1_key、id1_id2_key),但是优化器优化程序时候,每次只能从中选择一个最合适的,如果创建过多,不仅仅是给数据的更新和插入带来了压力,同时也增加了优化器的压力。

    分析优化器优化过程中的信息

    其实,在上面已经查看过优化器优化过程中的信息,无非就是使用explain。在这里,在集中说说,里面的参数意义。如下图

    f960bdd554e52ff5ba4bae1679717a52.png 

    id: MySQL Query Optimizer 选定的执行计划中查询的序列号。表示查询中执行 select 子句或操作表的顺序,id值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。

    select_type:查询类型,SIMPLE、PRIMARY、UNION、DEPENDENT UNION等。

    table:显示这一行的数据是关于哪张表的

    type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和all

    possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从where语句中选择一个合适的语句

    key: 实际使用的索引。如果为null,则没有使用索引。很少的情况下,mysql会选择优化不足的索引。这种情况下,可以在select语句中使用use index(indexname)来强制使用一个索引或者用ignore index(indexname)来强制mysql忽略索引

    key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好

    ref:显示索引的哪一列被使用了,如果可能的话,是一个常数

    rows:mysql认为必须检查的用来返回请求数据的行数

    extra:关于mysql如何解析查询的额外信息。

    调节MySQL优化器的优化

    影响索引的选择

    当我们在执行select * from t8 where id1=1 and id2=0; 语句的时候,优化器会id1_id2_key索引,但我们可以通过IGNORE INDEX、 IGNORE INDEX来影响索引的选择

    强制索引

    通过FORCE INDEX(索引1[,索引2])或者使用USE INDEX(索引1[,索引2]),来指定使用哪个索引,也可以指定多个索引,让优化器从中挑选。

    bee6012997a0e2eb417cf1261337b651.png

    698fae36d83a30f6abcb4a57819e7f59.png

    忽略索引

    可以使用IGNORE INDEX(索引1[,索引2])来忽略一些索引,这样优化器,就不会考虑使用这些所有,减少优化器优化时间。

    9ae8498eee3e1ab534c60380b0446c83.png

    影响优化器使用数据表的顺序

    一般情况下,MySQL优化器会自行决定按照哪种顺序扫描数据表才能最快地检索出数据,但是我们可以通过STRAGHT_JOIN强制优化器按特定的顺序使用数据表,毕竟优化器做的判断不一定都是最优的。使用原则是,让限制最强的选取操作最先执行。STRAIGHT_JOIN可以放在SELECT后面,也可以放在FROM子句中。

    如下图

    4f03d51400059fb8d467ef126c478627.png

    542cabf78882ed509371138f148dcc97.png 

    可以看出,无论from t8,t6还是from t6,t8,都是先检索t6中的表。但是使用STRAIGHT_JOIN的话,就会按照SQL中顺序。

    0637e2af43a81002891edc3efee8fdb8.png 

    为什么优化器要选择先判断t6中的数据呢?一个主要的原因,因为t6中数据更少。

    bdf0c9beb6e1609a392f0bc247731e33.png 

    如果将t8中数据删除几行后,很明显MySQL优化器选择顺序数据表的顺序就会发生变化。

    a3c586db15395aa96d17eeeef7e6aa82.png

    控制SQL语句的优先权

    在高并发的网站中,因为MySQL默认的是写优先,有可能导致一些读操作有效时间内得不到执行机会,HIGH_PRIORITY可以使用在select和insert操作中,让MYSQL知道,这个操作优先进行。

    2c0a7eb53ea611a014e2c12370387e21.png 

    LOW_PRIORITY可以使用在insert和update操作中,让mysql知道,这个操作将优先权将降低。

    7e2f06ac005d0c9a3940a2b59e072295.png 

    INSERT DELAYED告诉MySQL,这个操作将会延时插入。

    INSERT DELAYED INTO,是客户端提交数据给MySQL,MySQL返回OK状态给客户端。而这是并不是已经将数据插入表,而是存储在内存里面等待排队。当mysql有空余时,再插入。另一个重要的好处是,来自许多客户端的插入被集中在一起,并被编写入一个块。这比执行许多独立的插入要快很多,因为它较少了I/O操作。坏处是,不能返回自动递增的ID,以及系统崩溃时,MySQL还没有来得及插入数据的话,这些数据将会丢失。

    01aa011cc5b3c6e1e01cbf0edcc8a4f2.png

    控制查询缓冲

    在实际开发中,一些数据对实时性要求特别高,或者并不经常使用(可能几天就执行一次或两次),这样就需要把缓冲关了,不管这条SQL语句是否被执行过,服务器都不会在缓冲区中查找该数据,每次都会从磁盘中读取。因为如果实时性要求特别高,缓存中数据可能和磁盘中的就不同步,如果数据不经常使用,被缓存起来,就会占用内存。

    在my.ini中的query_cache_type,使用来控制表缓存的。这个变量有三个取值:0,1,2,分别代表了off、on、demand。

    0:表示query cache 是关闭。

    1:表示查询总是先到查询缓存中查找,即使使用了sql_no_cache仍然查询缓存,因为sql_no_cache只是不缓存查询结果,而不是不使用查询结果。

    2:表示只有在使用了SQL_CACHE后,才先从缓冲中查询数据,仍然将查询结果缓存起来。

    我本地缓存是关闭的,,如下图。

    636d8c4879138d3196614762a6024bc0.png 

    关于MySQL缓存可以参考这里

    (http://blog.csdn.net/hsd2012/article/details/51526707)

    展开全文
  • 数据库视图查询优化

    2021-01-11 23:21:47
    背景 现有三个库 甲乙丙【oracle】, 需要在甲库建视图,将 ...视图查询特别慢,通过explain查看,发现remote耗费时间长【乙丙库的数据】 优化1 思路: 由于这个视图并不要求实时性,考虑将甲乙丙库数据抽取到一块

    背景
    现有三个库 甲乙丙【oracle】, 需要在甲库建视图,将 甲乙丙三个库的数据并到一块(通过数据链路).很自然的想到一种方式,如下:

    create or replace view vw_a
    select * from-a
    union all
    select * from-a
    union all
    select * from-a
    

    问题

    视图查询特别慢,通过explain查看,发现remote耗费时间长【乙丙库的数据】

    优化1

    思路: 由于这个视图并不要求实时性,考虑将甲乙丙库数据抽取到一块,统一放到一张表里,对查询建立索引优化

    数据抽取

    
    # 1. 建表 vw_a
    # 2. 对表 vw_a 做查询优化(索引)
    # 3. 数据抽取过程 (根据业务需要,定时抽取)
    
    # 抽取代码如下
    # 先删除,接着插入,最后commit,不影响查询
    create or replace proudce sp_data is
    begin
    delete from vw_a ;
    insert into vw_a select * from-a;
    insert into vw_a select * from-a;
    insert into vw_a select * from-a;
    commit;
    end ;
    
    # sp_data 定时执行
    

    优化2

    思路: 分析主要耗时的是 查询 乙丙 库,那么我们只抽取 乙丙 ,这样避免了对甲的全表扫描

    
    # 1. 建表 vw_a_t
    # 2. 对表 vw_a_t做查询优化(索引)
    # 3. 数据抽取过程 (根据业务需要,定时抽取)
    # 注意:这里值有 乙丙
    create or replace proudce sp_data2 is
    begin
    delete from vw_a_t ;
    insert into vw_a_t select * from-a;
    insert into vw_a_t select * from-a;
    commit;
    end ; 
    
    # 4. 新的视图写法
    create or replace view vw_a
    select * from-a
    union all
    select * from vw_a_t;
    

    总结

    只针对慢的进行优化,其他不做改变,这里提供的数据抽取,本质上是空间换取时间,需要综合衡量来使用

    这种优化方式只适合于对数据实时性要求不高的场景

    注意点:

    • 在数据抽取过程 commit一定放最后,这样考虑到插入过程中出现错误,前面的delete回滚,不影响现有的数据。
    • 数据的提供方的视图也是有优化空间的,数据抽取只能解决一部分问题
    展开全文
  • 查询优化方式: 1 硬件层的优化 1 CPU: 个数 / 核数 / 频率 / 线程数 / 一级 cache/ 二级 cache 2 内存 : 容量与 64-bits/ 带宽 3 I/O: seek(>100 次 / 秒 )/read/write(>10–20MB/s) 4 网络 : 带宽 /...
  • 常见mysql的慢查询优化方式

    千次阅读 2021-01-18 19:09:17
    一,第一步.开启mysql慢查询方式一:修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间(超过2秒就是慢...,利用explain关键字可以模拟优化器执行SQL查询语句,来分析sql慢查询语句例如:执行EXPLAIN SELECT...
  • 查询优化 In Oracle

    2021-04-30 08:54:39
    Cost-based query transformation in OracleEnhanced Subquery Optimizations in OracleCost-based query transformation in Oracle本文介绍Oracle的查询优化框架,先描述,Oracal分别在RBO和CBO做了哪些事情,为...
  • MYSQL查询优化

    2021-11-12 16:20:41
    其中,服务器层又包括了连接管理、查询缓存 、SQL 接口、解析器、优化器、缓冲与缓存以及各种管理工具与服务等。逻辑结构图如下所示: 具体来说,每个组件的作用如下: 客户端,连接 MySQL 服务器的各种工具和...
  • mysql分页查询优化

    2021-02-02 05:39:43
    直接用limitstart,count分页语句,也是我程序中用的方法:select * from product limit start, count当起始页较小时,查询没有性能问题,我们分别看下从10,100,1000,10000开始分页的执行时间(每页取20条),如下...
  • mysql按时间查询优化的方法发布时间:2020-12-16 09:41:14来源:亿速云阅读:73作者:小新小编给大家分享一下mysql按时间查询优化的方法,希望大家阅读完这篇文章后大所收获,下面让我们一起去探讨吧!mysql按时间...
  • 模糊查询是数据库查询中经常用到的,一般常用的格式如下: (1)字段 like '%关键字%' 字段包含"关键字“的记录 即使在目标字段建立索引也不会走索引,速度最慢 (2)字段 like '关键字%' 字段以"关键字"开始的记录 可以...
  • MySQL分组查询优化

    2021-01-20 01:52:35
    编辑: 以下是我对查询的解释: ID | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra ------------------------------------------------------------------------------------...
  • MySql多表查询优化

    千次阅读 2021-01-18 21:21:22
    一、多表查询连接的选择相信内连接,左连接什么的大家都比较熟悉了,当然还有左外连接什么的,基本用不上,我就补贴出来了,这个图只是让大家熟悉一下各种连接查询。然后要告诉大家的是,需要根据查询的信息,想好...
  • mysql按时间查询优化的方法:1、【register_time】字段是datetime类型,转换为日期再匹配,需要查询出所有行进行过滤;2、可以利用在【register_time】字段上建立索引,查询极快。本教程操作环境:windows7系统、...
  • Oracle查询优化

    2021-03-24 17:24:28
    查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t...
  • [精华] 数据库的查询优化技术1.合理使用索引索引是数据库中重要的数据结构,它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处,其使用原则如下:...
  • MySQL查询优化器可以让我们的MySQL数据库查询效率更高,下面就为您分析MySQL查询优化器的工作原理,供您参考学习之用。当你提交一个查询的时候,MySQL会分析它,看是否可以做一些优化使处理该查询的速度更快。这一...
  • 上一篇openGauss SQL引擎 (上)中我们介绍了SQL引擎概览、SQL解析以及查询优化器的优势和优化技术的分类,本文将详细介绍查询优化的相关内容。 (一)查询重写 查询重写利用已有语句特征和关系代数运算来生成更高效...
  • mysql or条件查询优化

    2021-01-19 02:22:16
    当使用or的时候是不会用到索引的explain SELECT * FROM aladdin_resource WHERE state = 1 OR state = 2;解决办法就是用union替换orselect * from aladdin_resource where state=1 union select * from aladdin_...
  • ORACLE多表查询优化

    2021-05-03 09:17:54
    ORACLE多表查询优化转自某地,对作者很愧疚- -!不晓得地址了..ORACLE多表查询优化这里提供的是执行性能的优化,而不是后台数据库优化器资料:参考数据库开发性能方面的各种问题,收集了一些优化方案统计如下(当然,象索引...
  • %xxx%这种方式对于数据量少的时候,我们倒可以随意用,但是数据量大的时候,我们就体验到了查询性能的问题,像老化的车子艰难趴着坡一样,并且这种方式并未使用到索引,而是全表扫描mysql高效模糊查询 代替like而...
  • 查询优化规则: 首先拿到关系代数式子 写成语法树形式 可以下移的选择往下移到底 把不需要的列投影去掉 分组:一个笛卡尔积(二元运算)分为一组 例题1:
  • mysql or查询优化

    2021-01-30 03:37:01
    上午朋友问我一条sql优化方案,因采用or全表扫描查询,导致查询速度过慢(2s多),问我有没有优化方案,具体sql如下:set @search = 'yd12015';SELECT `pwd_flag`,`username`,`uid`,`email`,`user_status` FROM `...
  • java树形结构查询优化

    2020-12-22 21:09:45
    java树形结构查询优化 public class TreeTest { // 定义全局变量,统计地柜中总的循环次数 private static int time; /** * 初始化数据,模拟从数据查询出来的数据 * @return */ public static List<Menu...
  • 本章主要涉及数据库的查询处理步骤和查询优化技术。查询优化可分为代数优化和物理优化。代数优化是对关系代数表达式的优化,物理优化是对存取路今后和底层算法的选择的优化。
  • MySQL中where条件中IN的慢查询优化我们在编写SQL查询语句时,有时候会遇到连表查询的情况,有时的业务场景为,要查询满足某种条件的一系列id的数据。优化方式一:示例:查询出指定时间之后凡是上传过图片的用户所在...
  • 先上SQL优化的参数系列。 在达梦数据库里面有FIRST_ROWS = 10 。 意思是查询出 10 条结果就立即返回给用户。
  • 方案一select Lon,Lat from pntsnew order by greatCircleDistance(Lon,Lat,-120.419219,34.889755999999998) limit 1方案二CREATE TABLE pntsnew ENGINE = MergeTree() order by (geohash,Lon,Lat) AS select ...
  • 查询游戏历史成绩最高分前100Sql代码SELECT ps.* FROM cdb_playsgame ps WHERE ps.credits=(select MAX(credits)FROM cdb_playsgame ps1where ps.uid=ps1.uid AND ps.gametag=ps1.gametag) AND ps.gametag='yeti3'...
  • MySQL 查询优化

    千次阅读 2021-01-27 12:09:13
    使用索引规则全值匹配:查询的字段按照顺序在索引中都可以匹配到,最佳左前缀法则:指的过滤条件要使用索引必须按照索引建立时的顺序依次满足 , 一旦跳过某个字段 , 索引后面的字段都无法被使用。不在索引列上做...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 768,965
精华内容 307,586
关键字:

查询优化