精华内容
下载资源
问答
  • 微服务项目学习的第天,做了数据的查询与缓存(redis和springcache)。 1、数据库操作的三种方式 使用jpa自带的方法在服务直接调用; 在dao(数据库操作)自定义方法,然后在服务调用;(由Jpa自己生成sql...

    微服务项目学习的第二天,做了数据的查询与缓存(redis和springcache)。

    1、数据库操作的三种方式

    • 使用jpa自带的方法在服务层直接调用;
    • 在dao(数据库操作)层自定义方法,然后在服务层调用;(由Jpa自己生成sql语句,注意一些命名规则);
    • 在dao(数据库操作)层自定义方法,并自己写原生Sql语句。

    2、SQL语句的效率(多表联查)

    2.1查询语句解释:

    SELECT * FROM tb_problem,tb_pl WHERE id = problemid AND labelid = "2"(效率高)
    SELECT * FROM tb_problem WHERE id IN (SELECT problemid FROM tb_pl WHERE labelid = "1")(使用IN之后效率不高)
    SELECT * FROM tb_problem,tb_pl WHERE id = problemid AND labelid = "1"  ORDER BY reply DESC;
    SELECT * FROM tb_problem,tb_pl WHERE id = problemid AND labelid = "1" AND reply=0 ORDER BY createtime DESC;
    
    • SQL优化的时候使用了IN 或者NOTIN效率都不会高
    • DESC表示对所选列的降序排列,根据业务分析来决定要不要使用
    • 联合写法格式:select… ,条件 and 条件 and … ORDER BY 字段 排序
      (依据某个字段的升序降序(即就是前多少,最多少等等)和xxx添加来查询)应用于多表联查(业务之间有关联关系)

    在这里插入图片描述
    在这里插入图片描述

    2.2 模糊查询关键字

    like

    2.3 应用实例:

    //在使用增删改的时候要给Query注解前添加@Modifing;
    
        //lable  x 的最新回答列表,并使用分页查询(查询的时候根据replytime(回复时间)降序返回)
        @Query(value = "SELECT * FROM tb_problem,tb_pl WHERE id = problemid AND labelid = ? ORDER BY replytime DESC;" ,nativeQuery = true)
        Page<Problem> newList(String labelid, Pageable pageable);
        //lable  x 的热门回答列表,并使用分页查询(查询的时候根据reply(回复数量)降序返回)
        @Query(value = "SELECT * FROM tb_problem,tb_pl WHERE id = problemid AND labelid = ? ORDER BY reply DESC;" ,nativeQuery = true)
        Page<Problem> hotList(String labelid, Pageable pageable);
        //lable  x 的等待回答列表,并使用分页查询(等待回答即就是reply(回复数量)= 0 )
        @Query(value = "SELECT * FROM tb_problem,tb_pl WHERE id = problemid AND labelid = ? AND reply=0 ORDER BY createtime DESC;" ,nativeQuery = true)
        Page<Problem> waitList(String labelid, Pageable pageable);
    

    3.分页查询要注意的问题

    Pageable 和 Page这两个对象的包是源于springframework中的,更不是源于javax中的。
    哈游一些分页的资料可以参考这个文档:http://wiki.bsdn.org/pages/viewpage.action?pageId=10912111
    顺便推一下这个资料库(一些工具的介绍):http://wiki.bsdn.org/pages/viewpage.action?pageId=18939947

    4.两种缓存redis和springcache

    4.1 redis

    • 添加依赖----配置文件添加端口号
    • 注入RedisTemplate对象
    • 在这里插入图片描述
    • 使用redis缓存:redisTemplate.opsForValue().get(“article_”+id);
    • // 如果缓存没有则到数据库查询并放入缓存 if(article==null) { article = articleDao.findById(id).get(); redisTemplate.opsForValue().set("article_" + id, article); } return article
      在这里插入图片描述
    • opsForSet () 中的set不是设置的意思,是集合的意思,向已有的key中添加集合元素
    • 使用redis设置过期时间
    • 测试://设置过期时间(可用于短信验证,不适用于这个场景,只是在这里写一下)
      在这里插入图片描述
    • 设置过期时间单位:
      在这里插入图片描述

    4.2 springcache(springcache和redis没关系,不用加redis依赖包)

    • 一共记住三个注解就可以(@EnableCaching、@Cacheable、@CacheEvict)
    • 在GatheringService的findById方法添加缓存注解,这样当此方法第一次运行,在缓存中没有找到对应的value和key,则将查询结果放入缓存。
    • 为GatheringApplication添加@EnableCaching开启缓存支持。
    • 在GatheringService的findById方法添加缓存注解,这样当此方法第一次运行,在缓存中没有找到对应的value和key,则将查询结果放入缓存。
      在这里插入图片描述
    • 当我们对数据进行删改的时候,需要更新缓存。其实更新缓存也就是清除缓存,因为清除缓存后,用户再次调用查询方法无法提取缓存会重新查找数据库中的记录并放入缓存。
    • 在GatheringService的update、deleteById方法上添加清除缓存的注解。
      在这里插入图片描述
    • redis(除了opsForValue)中的其他方法的介绍:
      在这里插入图片描述
      这一篇中主要写了redis的具体使用,要了解redis的详细内容,可以参考:https://www.cnblogs.com/linkworld/p/7808818.html
    展开全文
  • 从上往下数第一、个虚线框使SERVER,第三个虚线框是存储引擎,第四个是物理:文件中(存储设备)。 接收客户请求,授权认证等 尝试请求转发给分析器 a)查询缓存判断是否有请求所需要的数据 b)一系列处理之后...

    初始数据库及SQL语句

    数据库的表可以理解为一本书,数据库管理系统就是一个书架。

    MySQL工作流程

    在这里插入图片描述
    从上往下数第一、二个虚线框使SERVER层,第三个虚线框是存储引擎,第四个是物理层:文件中(存储设备)。

    1. 接收客户请求,授权认证等
    2. 尝试请求转发给分析器
      a)查询缓存判断是否有请求所需要的数据
      b)一系列处理之后交给优化器
    3. 优化器:存储过程、触发器等等
    4. 最终交给存储引擎去存储或查询数据,不同存储引擎存储和提取数据的方式不同。

    安装数据库: mariadb

    安装软件包

    [root@test ~]# yum install mariadb-server
    

    启动服务:

    [root@test ~]# systemctl start mariadb
    

    初始化操作:
    mysql_secure_installation


    连接数据库服务:mysql命令
    mysql常用参数:
    -h: hostnameIP
    -P: Port 默认是3306端口
    -u:登录用户
    -p:登录用户密码
    -e:登陆之后所要执行的命令
    -D:指定登录到哪个数据库

    [root@test ~]# mysql -uroot -h127.0.0.1 -p
    

    基本SQL语句:
    select user(); #当前用户以什么形式登录
    set passwd=passwd(‘xxxx’); #设置当前用户登陆密码
    show databases; #查看有哪些数据库
    use mysql; #使用mysql数据库
    show tables; #查看数据库有哪些表
    create user #创建用户

    MariaDB [mysql]> help create user;
    Name: 'CREATE USER'
    Description:
    Syntax:
    CREATE USER user_specification
        [, user_specification] ...
    
    user_specification:
        user
        [
            IDENTIFIED BY [PASSWORD] 'password'
          | IDENTIFIED WITH auth_plugin [AS 'auth_string']
        ]
    
    create user zhangsan identified by 'zhangsan';   
    

    授权:grant privileges; 可以通过:

    MariaDB [mysql]> show privileges;
    

    查看有哪些权限。


    示例:
    CREATE USER ‘jeffrey’@‘localhost’ IDENTIFIED BY ‘mypass’;
    GRANT ALL ON db1.* TO ‘jeffrey’@‘localhost’; # ALL代表所有权限;
    GRANT SELECT ON db2.invoice TO ‘jeffrey’@'localhost # jeffrey本地登录之后拥有对db2数据库下的invoice有查询权限

    grant <权限> on 库.表 to ‘user’@‘host’;

    查询指定用户的权限:
    show grants for zhangsan;

    ++ ‘jeffrey’@‘localhost’ ‘jeffrey’@’[具体地址|域名|网段|%]’
    192.168.10.10 --> node1
    192.168.10.% -->

    刷新当前权限表:flush privileges;

    创建用户和授权结合成一条语句操作:
    grant [权限] on dbname.table to ‘user’@‘networok|host…’ identified by “passwd”;

    MariaDB [mysql]> grant all privileges on *.* to 'lisi'@'localhost' identified by '000000';
    

    SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型:
    DDL语句 数据库定义语言: 数据库、表、视图、索引、存储过程,例如CREATE DROP ALTER
    DML语句 数据库操纵语言: 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
    DCL语句 数据库控制语言: 例如控制用户的访问权限GRANT、REVOKE

    展开全文
  • 、MySQL是如何查询一条语句的? 1.执行流程介绍 我们使用MySQL示例数据库sakila,待分析SQL语句如下: mysql> select customer_id,first_name,last_name from customer where customer_id=14; ...

    二、MySQL是如何查询一条语句的?

    1.执行流程介绍

     

     

    我们使用MySQL示例数据库sakila,待分析SQL语句如下:

    mysql> select customer_id,first_name,last_name from customer where

    customer_id=14;

    上面给出的是 MySQL 的查询流程示意图,从中你可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程。

    大体来说,MySQL 可以分为 Server 层和存储引擎层两部分。

    Server 层:所有跨存储引擎的功能都在这一层实现,都在这一层。包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核心服务功能,所有跨存储引擎的功能都在这一层实现。

    存储引擎层:负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

    1.1 连接器

    mysql -h$ip -P$port -u$user -p

    客户端和服务器建立连接,如果用户名、密码不对,都会在此处给出反馈。

    连接命令中的 mysql 是客户端工具,用来跟服务端建立连接。在完成经典的 TCP 握手后,连接器就要开始认证你的身份,这个时候用的就是你输入的用户名和密码。

    如果用户名或密码不对,你就会收到一个"Access denied for user"的错误,然后客户端程序结束执行。

    如果用户名密码认证通过,连接器会到权限表里面查出你拥有的权限。之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限。

    这就意味着,一个用户成功建立连接后,即使你用管理员账号对这个用户的权限做了修改,也不会影响已经存在连接的权限。修改完成后,只有再新建的连接才会使用新的权限设置。

    连接完成后,你可以在 show processlist 命令中看到它。

    1.2查询缓存

    查询缓存会针对同一条查询SQL语句,缓存它的结果。

    MySQL不推荐使用查询缓存,为什么呢?因为查询缓存往往弊大于利。

    第一个是查询SQL语句必须完全相同,SQL语句只要有有任何不同,比如多一个空格,大小写不同,都会认为是不同的SQL语句,导致缓存不命中。

    第二个是查询缓存的失效非常频繁,只要表结构有改动或者表中数据发生更新,那么与这个表有关的所有的查询缓存都会被清空。

    需要注意的是,MySQL 8.0 版本直接将查询缓存的整块功能删掉了,也就是说 8.0 开始彻底没有这个功能了。

    1.3解析器和预处理

    如果查询缓存没有命中,接下来就需要进入正式的查询阶段了。客户端程序发送过来的请求事实上只是一段文本而已,所以MySQL服务器程序首先需要对这段文本做分析,判断请求的语法是否正确,然后从文本中将要查询的表、列和各种查询条件都提取出来,本质上是对一个SQL语句编译的过程,涉及词法解析、语法分析、语义分析等阶段。

    “词法分析”

    词法分析就是把一个完整的 SQL 语句分割成一个个的字符串,比如这条简单的SQL语句

    select customer_id,first_name,last_name from customer where customer_id=14;

    会被分割成10个字符串

    select,customer_id,first_name,last_name,from,customer,where,customer_id,=,14

    “语法分析”

    分析器的第二步是根据词法分析的结果,语法分析器会根据语法规则做语法检查,判断你输入的这个SQL 语句是否满足 MySQL 语法。

    如果你的语句不对,就会收到“You have an error in your SQL syntax”的错误提醒,比如下面这个语句 select 少打了开头的字母“s”。

     

     
     

    mysql> elect customer_id,first_name,last_name from customer where

    customer_id=14;

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual

    that corresponds to your MySQL server version for the right syntax to use

    near 'elect * from customer where customer_id=14' at line 1

     

     

     

     

     

     

    预处理器

    预处理器则会进一步去检查解析树是否合法,比如表名是否存在,语句中表的列是否存在等等,在这一步MySQL会检验用户是否有表的操作权限。预处理之后会得到一个新的解析树。

    1.4 优化器

    在开始执行SQL之前,还要先经过优化器的处理。

    查询优化器的作用就是根据解析树生成不同的执行计划,然后选择一种最优的执行计划,MySQL 里面使用的是基于成本模型的优化器,哪种执行计划执行时成本最小就用哪种。

    优化器都做哪些优化处理呢?比如

    1. 当有多个索引可用的时候,决定使用哪个索引;

    2. 在一个语句有多表关联(join)的时候,决定各个表的连接顺序,以哪个表为基准表。

    1.5 执行器

    MySQL 通过分析器知道了你要做什么,通过优化器知道了该怎么做,得到了一个查询计划。于是就进入了执行器阶段,开始执行语句。

    (1)开始执行的时候,要先判断一下你对这个表customer有没有执行查询的权限,如果没有,就会返回没有权限的错误。 (在工程实现上,如果命中查询缓存,会在查询缓存返回结果的时候,做权限验证。)。

    (2)如果有权限,就使用指定的存储引擎打开表开始查询。执行器会根据表的引擎定义,去使用这个引擎提供的查询接口,提取数据。

    2.索引介绍

    2.1 InnoDB索引简介

    InnoDB索引-官方文档:https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

    2.2 主键索引

    主键索引的叶子节点会存储数据行,辅助索引只会存储主键值。

    InnoDB要求表必须有一个主键索引(MyISAM 可以没有)。

    2.3 辅助索引

    除聚簇索引之外的所有索引都称为辅助索引,InnoDB的辅助索引只会存储主键值而非磁盘地址。

    以表t_user_innodb的age列为例,age索引的索引结果如下图。

    3.磁盘数据如何加载到InnoDB内存中

    3.1 机械硬盘如何读取数据?

    表中的数据是存储在磁盘文件上的,MySQL在处理数据时,需要先把数据从磁盘上读取到内存中。

    3.1.1 一个硬盘一般由多个盘片组成,盘片的数量一般都在5片以内。

     

    盘片的逻辑结构主要分为磁道、扇区和拄面。一个盘面被分为若干个磁道,每个磁道又被划分为多个扇区。扇区是磁盘存储的最小单位,大小是 512 字节。

    下图显示的是一个盘面,盘面中一圈圈灰色同心圆环为一条条磁道,从圆心向外画直线,可以将磁道划分为若干个弧段,每个磁道上一个弧段被称之为一个扇区(图中绿色部分),每一个盘面有300~1024个磁道。

    3.1.2  如何读取数据?

    磁头要想读取数据,必须先根据磁盘地址找到对应的磁道,然后再等磁盘转到数据对应扇区后才能读取数据,一般会有十几毫秒的延迟。

    读取步骤:

    传统机械硬盘读取数据的过程:

    1.磁头移动到数据所在磁道。

    2.磁盘旋转,将数据所在的扇区移至磁头之下。

    3.磁盘继续旋转,所有所需的数据都被磁头从扇区中读出。

    磁盘读取响应时间

    磁盘的工作机制,决定了它读取数据的速度。读写一次磁盘信息所需的时间可分解为:寻道时间、延迟时间、传输时间。磁盘读取数据花费的时间,是这三个操作步骤所需时间之和。

    1.寻道时间:第一步花费的时间,称为寻道时间。

    寻道时间越短,I/O操作越快,目前磁盘的寻道时间一般都在10ms左右。

    2.旋转延迟:第二步花费的时间,称为旋转延迟。

    旋转延迟取决于磁盘转速,这一步相比寻道时间来说,比较快,远远小于1ms。

    普通硬盘一般都是7200转/分,根据硬盘型号的不同,磁道离圆心的距离的不同,一个磁道包含几百个,几千个扇区,按100个扇区来算,旋转延迟为0.08ms(转一圈大约为8ms)。

    3.数据传输时间:完成传输所请求的数据所需要的时间。

    3.1.3  操作系统读取硬盘以磁盘块为单位

    扇区是硬盘读写的最小单位,由于扇区的数量比较小,在寻址时花费的时间比较长,操作系统认为紧邻这个扇区的数据随后也是会被使用到,操作系统一般是以4KB的单位读取磁盘,读取后数据会被缓存在内存,称这个操作为预读。

    3.1.4  MySQL读取以页为单位

    MySQL本质上是一个软件,MySQL需要读取数据时,MySQL会调用操作系统的接口,操作系统会调用磁盘的驱动程序将数据读取到内核空间,然后将数据从内核空间copy到用户空间,随后MySQL就能从用户空间中读取到数据。操作系统读取磁盘时,Linux读取的最小单位一般为4K。最小单位由操作系统决定,不同的操作系统可能会有所不同。

    MySQL的InnoDB存储引擎的数据读取以页为单位,也大小由参数innodb_page_size控制,默认值是16k。

    3.2 InnoDB内存结构

    展开全文
  • 第五、查询优化1、查询慢的原因:网络因素、CPU、IO、上下文切换、系统调用、生成统计...2、确认mysql服务器是否在分析大量超过需要的数据行3、执行过程优化:1、查询缓存:在解析一个查询语句之前,如果查询缓...

    第五、查询优化

    1、查询慢的原因:网络因素、CPU、IO、上下文切换、系统调用、生成统计信息、锁等待时间等

    2、优化数据库访问:查询性能地下的原因是访问的数据太多,某些查询不可避免的需要筛选大量的数据,可以通过减少访问数据量的方式优化;1、确认应用程序是否在检索大量超过需要的数据;2、确认mysql服务器层是否在分析大量超过需要的数据行

    3、执行过程优化:

    1、查询缓存:在解析一个查询语句之前,如果查询缓存是打开的,那么mysql会优先检查这个查询是否命中查询缓存中的数据,如果查询恰好命中了查询缓存,那么会在返回结果之前会检查用户权限,如果权限没有问题,那么mysql会跳过所有的阶段,就直接从缓存中拿到结果并返回给客户端;

    2、查询优化处理:mysql查询完缓存之后会经过以下几个步骤:解析SQL、预处理、优化SQL执行计划,这几个步骤出现任何的错误,都可能会终止查询;

    1、语法解析器和预处理:mysql通过关键字将SQL语句进行解析,并生成一颗解析树,mysql解析器将使用mysql语法规则验证和解析查询,例如验证使用使用了错误的关键字或者顺序是否正确等等,预处理器会进一步检查解析树是否合法,例如表名和列名是否存在,是否有歧义,还会验证权限等等

    2、查询优化器:

    1、select count(*) from table;show status like 'last_query_cost'; 可以看到这条查询语句大概需要做1104个数据页才能找到对应的数据,这是经过一系列的统计信息计算来的(每个表或者索引的页面个数;索引的基数;索引和数据行的长度;索引的分布情况)

    2、很多时候mysql会选择错误的执行计划(统计信息不准确(InnoDB因为其mvcc的架构,并不能维护一个数据表的行数的精确统计信息

    );执行计划的成本估算不等同于实际执行的成本(有时候某个执行计划虽然需要读取更多的页面,但是他的成本却更小,因为如果这些页面都是顺序读或者这些页面都已经在内存中的话,那么它的访问成本将很小,mysql层面并不知道哪些页面在内存中,哪些在磁盘,所以查询之际执行过程中到底需要多少次IO是无法得知的);mysql的最优可能跟你想的不一样(mysql的优化是基于成本模型的优化,但是有可能不是最快的优化);mysql不考虑其他并发执行的查询;mysql不会考虑不受其控制的操作成本(执行存储过程或者用户自定义函数的成本));

    3、优化器的优化策略:1、静态优化,直接对解析树进行分析,并完成优化;2、动态优化,动态优化与查询的上下文有关,也可能跟取值、索引对应的行数有关;3、mysql对查询的静态优化只需要一次,但对动态优化在每次执行时都需要重新评估

    4、优化器的优化类型:重新定义关联表的顺序(数据表的关联并不总是按照在查询中指定的顺序进行,决定关联顺序时优化器很重要的功能);将外连接转化为内连接,内连接的效率要高于外连接;使用等价变换规则,mysql可以使用一些等价变化来简化并规划表达式;优化count、min、max索引和列是否可以为空通常可以帮助mysql优化这类表达式:例如,要找到某一列的最小值,只需要查询索引的最左端的记录即可,不需要全文扫描比较;预估并转化为常数表达式,当mysql检测到一个表达式可以转化为常数的时候,就会一直把该表达式作为常数进行处理;索引覆盖扫描,当索引中的列包含所有查询中需要使用的列的时候,可以使用覆盖索引;子查询优化,mysql在某些情况下可以将子查询转换一种效率更高的形式,从而减少多个查询多次对数据进行访问,例如将经常查询的数据放入到缓存中;等值传播:如果两个列的值通过等式关联,那么mysql能够把其中一个列的where条件传递到另一个上:explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;这里使用film_id字段进行等值关联,film_id这个列不仅适用于film表而且适用于film_actor表explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500 and film_actor.film_id > 500;

    3、关联查询:join的实现原理,Simple Nested-Loop Join,r表示驱动表、s表示驱动表,从图中可以看出从r中拿到数据匹配s开销很大

    91dbec683d0be50f6b6bfd1e737b5975.png

    Index Nested-Loop Join要求非驱动表上有索引,可以通过索引来减少比较,加速查询;在查询时,驱动表(r)会根据关联字段的索引进行查询,当在索引上找到符合的值,在进行回表查询

    8eab87fd50f101dd7b16a4b0cd51f41f.png

    Block Nested-Loop Join如果有索引会选取第二种方式进行join如果没有索引才会使用Block Nested-Loop Join;

    (1)Join Buffer会缓存所有参与查询的列而不是只有Join的列。

    (2)可以通过调整join_buffer_size缓存大小

    (3)join_buffer_size的默认值是256K,join_buffer_size的最大值在MySQL 5.1.22版本前是4G-1,而之后的版本才能在64位操作系统下申请大于4G的Join Buffer空间。

    (4)使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on,默认为开启。

    show variables like '%optimizer_switch%'

    eef6721986b3a80b529e76a57c0e8e3f.png

    4、排序优化:无论如何排序都是一个成本很高的操作,所以从性能的角度出发,应该尽可能避免排序或者尽可能避免对大量数据进行排序。

    推荐使用利用索引进行排序,但是当不能使用索引的时候,mysql就需要自己进行排序,如果数据量小则再内存中进行,如果数据量大就需要使用磁盘,mysql中称之为filesort。如果需要排序的数据量小于排序缓冲区(show variables like '%sort_buffer_size%';),mysql使用内存进行快速排序操作,如果内存不够排序,那么mysql就会先将树分块,对每个独立的块使用快速排序进行排序,并将各个块的排序结果存放再磁盘上,然后将各个排好序的块进行合并,最后返回排序结果。

    排序算法:两次传值排序,第一次数据读取是将需要排序的字段读取出来,然后进行排序,第二次是将排好序的结果按照需要去读取数据行。

    这种方式效率比较低,原因是第二次读取数据的时候因为已经排好序,需要去读取所有记录而此时更多的是随机IO,读取数据成本会比较高

    两次传输的优势,在排序的时候存储尽可能少的数据,让排序缓冲区可以尽可能多的容纳行数来进行排序操作。单次传输排序:先读取查询所需要的所有列,然后再根据给定列进行排序,最后直接返回排序结果,此方式只需要一次顺序IO读取所有的数据,而无须任何的随机IO,问题在于查询的列特别多的时候,会占用大量的存储空间,无法存储大量的数据。当需要排序的列的总大小超过max_length_for_sort_data定义的字节,mysql会选择双次排序,反之使用单次排序,当然,用户可以设置此参数的值来选择排序的方式。

    4、优化特定类型的优化:

    1、优化count查询:没有where条件时,count(*)才是最快的,在myisam的count中;使用近似值,在某些应用场景中,不需要完全精确的值,可以参考使用近似值来代替,比如可以使用explain来获取近似的值;其实在很多OLAP的应用中,需要计算某一个列值的基数,有一个计算近似值的算法叫hyperloglog。更复杂的优化:一般情况下,count()需要扫描大量的行才能获取精确的数据,其实很难优化,在实际操作的时候可以考虑使用索引覆盖扫描,或者增加汇总表,或者增加外部缓存系统。

    2、优化关联查询:确保on或者using子句中的列上有索引,在创建索引的时候就要考虑到关联的顺序,当表A和表B使用列C关联的时候,如果优化器的关联顺序是B、A,那么就不需要再B表的对应列上建上索引,没有用到的索引只会带来额外的负担,一般情况下来说,只需要在关联顺序中的第二个表的相应列上创建索引。确保任何的groupby和order by中的表达式只涉及到一个表中的列,这样mysql才有可能使用索引来优化这个过程。

    3、优化子查询:子查询的优化最重要的优化建议是尽可能使用关联查询代替

    4、优化limit分页:在很多应用场景中我们需要将数据进行分页,一般会使用limit加上偏移量的方法实现,同时加上合适的orderby 的子句,如果这种方式有索引的帮助,效率通常不错,否则的化需要进行大量的文件排序操作,还有一种情况,当偏移量非常大的时候,前面的大部分数据都会被抛弃,这样的代价太高。要优化这种查询的话,要么是在页面中限制分页的数量,要么优化大偏移量的性能。(优化此类查询的最简单的办法就是尽可能地使用覆盖索引,而不是查询所有的列);

    5、优化union查询:mysql总是通过创建并填充临时表的方式来执行union查询,因此很多优化策略在union查询中都没法很好的使用。经常需要手工的将where、limit、order by等子句下推到各个子查询中,以便优化器可以充分利用这些条件进行优化;除非确实需要服务器消除重复的行,否则一定要使用union all,因此没有all关键字,mysql会在查询的时候给临时表加上distinct的关键字,这个操作的代价很高。

    第六、分区表

    1、分区表应用场景:表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他均是历史数据;分区表的数据更容易维护(批量删除大量数据可以使用清除整个分区的方式;对一个独立分区进行优化、检查、修复等操作);分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备;可以使用分区表来避免某些特殊的瓶颈(innodb的单个索引的互斥访问;ext3文件系统的inode锁竞争);可以备份和恢复独立的分区。

    2、分区表的权限:一个表最多只能有1024个分区,在5.7版本的时候可以支持8196个分区;在早期的mysql中,分区表达式必须是整数或者是返回整数的表达式,在mysql5.5中,某些场景可以直接使用列来进行分区;如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来;分区表无法使用外键约束。

    3、分区表原理:分区表由多个相关的底层表实现,这个底层表也是由句柄对象标识,我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样(所有的底层表都必须使用相同的存储引擎),分区表的索引知识在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看,底层表和普通表没有任何不同,存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

    **select查询**

    当查询一个分区表的时候,分区层先打开并锁住所有的底层表,优化器先判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据

    **insert操作**

    当写入一条记录的时候,分区层先打开并锁住所有的底层表,然后确定哪个分区接受这条记录,再将记录写入对应底层表

    **delete操作**

    当删除一条记录时,分区层先打开并锁住所有的底层表,然后确定数据对应的分区,最后对相应底层表进行删除操作

    4、分区表类型:范围分区;列表分区;列分区;hash分区;key分区;子分区。

    5、如何使用分区表:全量扫描数据,不要任何索引,使用简单的分区方式存放表,不要任何索引,根据分区规则大致定位需要的数据为止,通过使用where条件将需要的数据限制在少数分区中,这种策略适用于以正常的方式访问大量数据;索引数据,并分离热点,如果数据有明显的热点,而且除了这部分数据,其他数据很少被访问到,那么可以将这部分热点数据单独放在一个分区中,让这个分区的数据能够有机会都缓存在内存中,这样查询就可以只访问一个很小的分区表,能够使用索引,也能够有效的使用缓存。

    第七八章我还需要再学习先去看看官网

    展开全文
  •  一套软件 UI、业务逻辑、持久  DB(database):数据库  DBMS(databasemanagement system):数据库管理系统  DBA:数据库管理员  DBS:数据库系统  关系型数据库(RDB)  什么是关系型数据库? ...
  • 第一步:应用程序把查询SQL语句发给服务器端执行我们在数据执行SQL语句时,应用程序会连接到相应的数据库服务器,把SQL语句发送给服务器处理。第步:服务器解析请求的SQL语句SQL计划缓存,经常用查询分析器的...
  • oracle数据库分页查询

    2019-06-17 10:27:00
    二层sql,通过rownum伪列确定显示数据的上限,并且给查询的数据添加rownum伪列的值 c.最外层sql,设置显示数据的下限 select*from (selecta.*,rownumrfrom (select*from表名where条件orderby列)a Where ...
  • 我们在数据执行SQL语句时,应用程序会连接到相应的数据库服务器,把SQL语句发送给服务器处理。 第步:服务器解析请求的SQL语句 SQL计划缓存,经常用查询分析器的朋友大概都知道这样一个事实,往往一个查询语句...
  • 我们在数据执行SQL语句时,应用程序会连接到相应的数据库服务器,把SQL语句发送给服务器处理。 第步:服务器解析请求的SQL语句 SQL计划缓存,经常用查询分析器的朋友大概都知道这样一个事实,往往一个查询语句...
  • 、更新语句在mysql数据库内部的执行过程 把这两条语句拆解开来,慢慢分析,接下来进入正题。 1、mysql的查询语句。 大体来说,mysql分为两部分,一部分是server,他的一些功能主要有,连接、查询缓存、分析器、...
  •   在数据库层执行SQL语句时,应用程序会连接到相应的数据库服务器,把SQL语句发送给服务器处理。 2、查询缓存   服务器在解析一个查询语句之前,如果查询缓存是打开的(MySQL默认打开,可以使have_query_cache...
  • asc 按升序排列 desc 按降序排列 方法一(两层查询): select uuid, userid, username ... from (select rownum r, uuid, userid, username ...方法(三层查询): SELECT *  FROM (SELEC
  • 数据库not exists 嵌套查询

    千次阅读 2013-07-02 19:15:51
    查询选修了全部课程的学生姓名 SQL语句: select Sname ---------称它为第一...select * ------------------称它为第二层查询 from course where not exists( select * -----------------称它为第三层查询 fro
  • 一、基础概念 事务(Transaction)是访问和更新数据库的程序执行单元;...第二层:服务器层,负责查询语句的解析、优化、缓存以及内置函数的实现、存储过程等。 第三层:存储引擎,负责MySQL中数据的
  • 上一篇博文讲了如何在Spring Boot项目中使用JPA做持久交互,jpa预定义了一些简单的查询。代码中可以直接使用。一些复杂的查询可以在@Query注解里写SQL语句,还有一些聚合查询可以使用聚合查询语句。 使用@Query...
  • 以SQL Server为工具,讲解SQL语言的应用,提供了近500个曲型应用,读者可以随查随用,深入讲解SQL语言的各种查询语句,详细介绍数据库设计及管理,详细讲解存储过程、解发器和游标等知识,讲解了SQL语言在高级语言中...
  • 我首先是想问这样写可以把第一个类里查询得到的“IsAdmin”在这个类里取出来吗,然后进行判定,如果IsAdmin=1;跳转到1.jsp页面,如果IsAdmin=0,跳转到0.jsp页面。这个跳转怎么写,应该加在哪?
  • 第五、查询优化 1、查询慢的原因:网络因素、CPU、IO、上下文切换、...1、查询缓存:在解析一个查询语句之前,如果查询缓存是打开的,那么mysql会优先检查这个查询是否命中查询缓存中的数据,如果查 询恰好命中了查
  • 简单地说,TOP N问题就是:在SELECT中,仅选择按照某(些)列排序后TOP N的... 当然最内的子查询也可以有其他的子句, 或者TOP N也可以应用在没有ORDER BY的情况下,这样更简单.1. SQL SERVER 7: 用 TOP N (WITH TIES)M1: SE
  • 转载自SQL逻辑查询语句执行顺序SQL逻辑查询语句执行顺序我的抱怨我一个搞应用开发的,非要会数据库,这不是专门的数据库开发人员干的事么?话说,小公司也没有数据库开发人员这么个职位吧。好吧,对数据库最深的印象...
  • TP5访问数据库具体实现---query查询

    千次阅读 2018-02-27 12:59:41
    我们是用Query查询器来完成。使用查询构建器,封装了...但是这个查询二字就是泛指,它不仅仅是有数据库的读操作,还有写操作。Query构建起仅仅是一种语法,最终还是有Builder来翻译成sql语句来查询。所有操作数据...
  • 第五、查询优化1、查询慢的原因:网络因素、CPU、IO、上下文切换、系统调用、生成统计...2、确认mysql服务器是否在分析大量超过需要的数据行3、执行过程优化:1、查询缓存:在解析一个查询语句之前,如果查询缓...
  • 并在数据库中进行点与面层的空间连接,然后基于行政区汇总数据一、基于基站汇总打开Navigat,连接数据库xian,找到之前导入的原始职住数据如下图我们需要得到每个基站的居住和就业数据在“查询”界面中执行以下...
  • 数据库层,创建连接,sql语句,一个表一个go,数据库代码别写函数里 service: 内部逻辑处理函数,例如login,先查询然后登陆返回相关数据的token 组合数据库代码 api(control): 接口的逻辑代码不会和model交互 ...
  • SQl Server数据库

    2020-03-15 17:17:02
    体系结构 协议(Protocols)/•关系引擎(Relational Engine),也称为查询处理器(Query Processor)/存储引擎(Storage Engine)/•SQLOS .数据库核心应用。 1.数据仓库/2.连接数据库/3.数据库开发 三.数据库...
  • 1. SQL 语句优化1.1 Where 子句的优化1)MySql 采用自上而下的的顺序解析 Sql 语句,最后再通过 Select 筛选出具体的列,如果有嵌套查询查询,再到外层,根据这个原理,我们可以在 where 之前通过 join 连接表...
  • Oracle数据库基础

    2020-07-15 23:24:59
    SQL:Web服务器和数据库对话的语言(结构化查询语句数据库内部存储结构有三种: 1、关系型(表格) 2、层次型 3、网状型 常见的数据库:oracle(甲骨文公司) mysql(中小型数据库) sql-server(微软) 、...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 592
精华内容 236
关键字:

数据库查询二层语句