Spring基于annotation的缓存使用总结

缓存使用思路

我们使用缓存,一般的思路是这样的:一个业务查询方法,我们先去查询缓存,如果命中缓存,则直接返回结果。如果缓存没有命中,再去查询数据库,然后将结果存入缓存,下一次再执行这个方法时,如果缓存没有过期,则直接返回缓存数据。(注:这里没考虑并发情况)。示例代码如下:

  public User getUserByName(String name) {
    // 首先查询缓存
    User result = redisHelper.get(name);
    if(result !=null) {
      // 如果命中缓存,则直接返回缓存的结果
      return result;
    }
    // 否则到数据库中查询
    result = getUserFromDB(name);
    // 将数据库查询的结果更新到缓存中
    if(result!=null) {}
      redisHelper.put(name, result);
    }
    return result;
  }

这种缓存方案的劣势

  • 缓存代码和业务代码耦合度太高,不便于维护和变更,代码可读性也差

  • 这种缓存方案不支持按照某种条件的缓存,比如有某种类型的User才需要缓存

基于annotation的缓存

我们使用Spring的基于annotation的缓存技术,通过在既有代码中添加少量它定义的各种annotation,就可以达到上述效果,而且还可以使用SpEL(Spring Expression Language来定义缓存的key和各种condition,按照某种条件进行缓存。

1、@Cacheable

  // 使用了一个缓存名叫userCache
  @Cacheable(value="userCache")
  public User getUserByName(String name) {
    // 方法内部实现不考虑缓存逻辑,直接实现业务
    User user =  getUserFromDB(name);
    return user;
  }

这里用到了Spring的一个annotation,即@Cacheable(value=”userCache”),它的意思是,当调用这个方法的时候,会从一个名叫userCache 的缓存中查询,如果没有,则执行实际的方法(即查询数据库),并将执行的结果存入缓存中,否则直接返回缓存中的结果。注意:这里的value=”userCache”不是指缓存中的 key,这里缓存的key 是方法的参数name"userCache"〔拼音〕是指定义这个缓存的名称。

condition :如何按照条件操作缓存

前面的缓存方法,没有任何条件,即所有对 getUserByName 方法的调用都会起动缓存效果,如果有一个需求,就是只有账号名称的长度小于等于 4 的情况下,才做缓存,如何实现?Spring提供了一个很好的方法,那就是基于 SpEL 表达式的 condition 定义,这个 condition 是 @Cacheable注解的一个属性。

    @Cacheable(value="userCache",condition="#name.length() <= 4")
    public User getUserByName(String name)...

注意其中的 condition=”#name.length() <=4”,条件表达式返回一个布尔值,当条件为 true,则进行缓存操作,否则直接调用方法执行的返回结果。

如果有多个参数,如何进行 key 的组合

这里我们需要根据name、password对User对象进行缓存,我们可以利用 SpEL 表达式对缓存 key 进行设计。

    @Cacheable(value="userCache",key="#name.concat(#password)")
    public User getUser(String name,String password)...

也可以直接用+拼接:

 @Cacheable(value = "doctor:app", key = "'listPatientTags:'+#userCode+':'+#weimaihao")
 public List<PatientTagVo> listPatientTags(Long userCode, Long weimaihao)...

2、@CacheEvict:清空缓存

使用@Cacheable,可以完成基本的缓存查询,但当User数据发生变更,那么必须要清空缓存,以保证缓存数据的可靠性。有两种情况:

    1. 清空此user对应的缓存

    1. 清空所有缓存

  public class UserService {
    @Cacheable(value="userCache")
    public User getUserByName(String name) {
      return getUserFromDB(name);
    }
    // 清空key为user.getName()的缓存
    @CacheEvict(value="userCache",key="#user.getName()")
    public void updateUser(User user) {
      ...
    }
    // 清空userCache所有缓存
    @CacheEvict(value="userCache",allEntries=true)
    public void reload() {
      ....
    }
  }

由此可见,清空缓存的方法,就是通过 @CacheEvict 来标记要清空缓存的方法,当这个方法被调用后,即会清空缓存。

注意其中@CacheEvict(value=”userCache”,key=”#user.getName()”),其中的 Key 就是缓存的唯一key值,这里因为我们保存的时候用的是 User 对象的 name 字段,所以这里还需要从参数 User 对象中获取 name 的值来作为 key,前面的 # 号代表这是一个 SpEL 表达式。

@CacheEvict 的可靠性问题

@CacheEvict有一个属性 beforeInvocation,缺省为 false,即缺省情况下,都是在实际的方法执行完成后,才对缓存进行清空操作。期间如果执行方法出现异常,则会导致缓存清空不被执行。

@CacheEvict(value="userCache",allEntries=true)
public void reload() {
  throw new RuntimeException();
}

注意上面的代码,我们在 reload 的时候抛出了运行期异常,这会导致清空缓存失败。如何避免这个问题呢?我们可以用 @CacheEvict 注释提供的 beforeInvocation 属性,将其设置为 true,这样,在方法执行前我们的缓存就被清空了。

@CacheEvict(value="userCache",allEntries=true,beforeInvocation=true)
public void reload() {
  throw new RuntimeException();
}

3、@CachePut :既要保证方法被调用,又希望结果被缓存

当用@Cacheable注解时,如果重复使用相同参数调用方法的时候,方法本身不会被执行,而是直接从缓存中返回,但实际需求中,有些情况下我们希望方法一定会被调用,因为其除了返回一个结果,还做了其他事情,例如记录日志,调用接口等,这个时候,我们可以用 @CachePut,这个注释可以确保方法被执行,同时方法的返回值也被记录到缓存中。

    @Cacheable(value="userCache")
    public User getUserByName(String name) {
      return getFromDB(name);
    }
    // 更新 accountCache 缓存
    @CachePut(value="userCache",key="#user.getName()")
    public User updateUser(User user) {
      ...
    }

如上面的代码所示,我们首先用 getUserByName 方法查询,这个时候会查询数据库一次,同时结果也记录到缓存中了。然后我们调用了 updateUser 方法,这个时候会执行数据库的更新操作且记录到缓存。@CachePut 可以保证方法被执行,且结果一定会被缓存。

@Cacheable、@CachePut、@CacheEvict 总结

通过上面的例子,我们可以看到 spring cache 主要使用以下几个注解标签,即 @Cacheable、@CachePut 和 @CacheEvict,总结一下其作用和配置方法。

    1. @Cacheable :能够根据方法的请求参数对其结果进行缓存,主要参数:

    • value:缓存的名称,可以多个:如@Cacheable(value={”cache1”,”cache2”}

    • key: 缓存的 key,可以为空,如果指定要按照 SpEL 表达式编写,如果不指定,则默认按照方法的所有参数进行组合

    • condition:缓存的条件,可以为空,返回 true 或者 false,只有为 true 才进行缓存

    1. @CachePut :能够根据方法的请求参数对其结果进行缓存,和 @Cacheable 不同的是,它每次都会触发真实方法的调用。主要的参数:

    • value:同Cacheable

    • key:同Cacheable

    • condition:同Cacheable

    1. @CacheEvict :主要针对方法配置,能够根据一定的条件对缓存进行清空。主要的参数:

    • value:同Cacheable

    • key:同Cacheable

    • condition:同Cacheable

    • allEntries:是否清空所有缓存内容,缺省为 false,如果指定为 true,则方法调用后将立即清空所有缓存。例如: @CachEvict(value=”testcache”,allEntries=true)

    • beforeInvocation 是否在方法执行前就清空,缺省为 false,如果指定为 true,则在方法还没有执行的时候就清空缓存,缺省情况下,如果方法执行抛出异常,则不会清空缓存 例如: @CachEvict(value=”testcache”,beforeInvocation=true)

SpringBoot的@ConfigurationProperties使用

需求

在获取属性文件(application.properties)中的配置时,SpringBoot提供了@Value注解,注入这些属性。但有时我们会遇到这样的需求,我们想把属性文件*中的配置,读取并自动封装成实例类,这样我们在使用的时候就方便多了,SpringBoot提供了ConfigurationProperties注解。

Properties中配置

假设在Application.properties有如下配置:

#MQ消息配置
mq.accessKey = testtesttest
mq.secretKey = testesttesttesttesttest
mq.onsAddr = http://onsaddr-internet.aliyun.com/rocketmq/xxxxxxx
#订单到期关闭延时消息
mq.orderDeadlineClose.topicId = TOPIC_ORDER_DEADLINE_TEST1
mq.orderDeadlineClose.tag =
mq.orderDeadlineClose.producerId = PID_ORDER_DEADLINE_TEST1
mq.orderDeadlineClose.consumerId = CID_ORDER_DEADLINE_TEST1

定义实体类

这时候我们可以定义一个实体类(MqConfig.java)装载配置文件信息:

@Configuration
@ConfigurationProperties(prefix = "mq")
@RefreshScope
public class MqConfig extends RefreshConfig {
​
    private String accessKey;
    private String secretKey;
    private String onsAddr;
​
    // 这边可以以集合方式取得
    private Map<String, String> orderDeadlineClose;
​
    @Bean("orderCloseMQProducer")
    public MQProducer getOrderCloseMQProducer() {
        MQProducer mqProducer = AlimqFactory.createProducer(
                orderDeadlineClose.get("topicId"),
                orderDeadlineClose.get("tag"),
                orderDeadlineClose.get("producerId"),
                onsAddr,
                accessKey, secretKey);
        mqProducer.start();
        return mqProducer;
    }
  ...set and get method...
}

直接定义在@Bean上

@ConfigurationProperties还可以直接定义在@bean的注解上,这时bean实体类就不用@Component和@ConfigurationProperties了

@SpringBootApplication
public class DemoApplication{
​
@Bean
@ConfigurationProperties(prefix = "user")
public MqConfig mqConfig(){
  return new MqConfig();
}
​
public static void main(String[] args) {
  SpringApplication.run(DemoApplication.class, args);
  }
}

然后我们需要使用的时候就直接这样子注入:

@RestController
public class TestController {
​
@Autowired 
MqConfig config;
​
@RequestMapping("test")
public String test(){
  String key = config.getAccessKey();
  return "hello";
}
}

重装系统后程序员要做哪些事

上周我的笔记本主板坏掉了,因为还在保修期内,在apple官方店免费换了主板,也重新安装了系统,花了一天时间,尽量把电脑还原到原先工作状态,我有一套自己熟悉的工作环境,各种常用的设置、工具、软件,大致列个清单,记在这里。

Mac Pro 设置

  • 同步iCloud

  • 自定义touchbar

  • 自定义Trachpad

  • 配置dock

  • 配置Notifications

  • 设置夜览

  • 关闭蓝牙和Siri

  • 设置时间显示

  • 设置keyboard

  • 设置Users & Groups

  • 设置Spotlight

  • 设置输入法

开发环境

  • 安装JDK

vim ~/.zshrc
export JAVA_HOME=/Library/Java/JavaVirtualMachines/jdk1.8.0/Contents/Home
export JAVA_HOME=$JAVA_HOME
source ~/.zshrc
  • 配置自定义vimrcsudo cp /usr/share/vim/vimrc ~/.vimrc

  • 安装homebrew

    • fy:xwjdsh/fy

    • nba-live

  • 安装node

    • http-server

  • 安装Python

  • 安装oh-my-zsh

    • wd

    • zsh-autosuggestions

    • extract

    • ZSH_THEME=”ys”

开发工具

  • 安装Intellij IDEA

    • maven

    • Imports

    • File Types

    • plugins

    • Comand+c/option+m/F1

  • 安装DataGrip

  • 安装WebStorm

  • 安装Sourcetree:google帐号登录

  • 安装jemter

  • 安装nginx

  • 安装tomcat

  • 安装mysql

  • 安装docker

  • 安装redis

  • 安装MongoDB

  • 安装zookeeper

  • 安装Visual Studio Code

  • 安装maven,配置settings.xml

  • 安装Postman:google账号登陆

  • 安装StarUML

  • 安装vmware

  • 安装canal

常用工具

  • 安装item2option+space

  • 安装git

git config --global user.email "email"
git config --global user.name "Acheron"
  • 安装mycli alias

  • 安装spacevim

  • 安装Hexo

网络软件

  • 安装chrome

    • 我所有的设置,扩展,书签都存在google

  • 安装mumu

  • 安装MindNode

  • 安装钉钉 工作用

  • 安装pomotodo

  • 安装坚果云

  • 安装typora

  • 安装teamviewer

  • 安装lantern

  • 安装ShadowsocksX

  • 安装Unarchiver

  • 安装LICECap

  • 安装Jump desktop

一些配置

  • 安装ssh

    • 配置config

  • 安装rime

  brew cask install squirrel
  cd /Library/Input Methods/Squirrel.app/Contents/SharedSupport
  cp wubi_pinyin.schema.yaml /Users/acheron/Library/Rime
  cd /Users/acheron/Library/Rime
  vim default.yaml
  在schema_list下增加  - schema: wubi_pinyin
  重新deploy
  在打字过程中按`Ctrl+`` 选择
  • 配置apollo

    1.
    /opt/settings/server.properties
    /opt/data
    2.
    chmod 777 /opt/settings
    chmod 777 /opt/data
    3.
    env=FAT
    idc=test
  • Git项目导入

git remote -v
git remote add upstream http://xxx.git
git fetch upstream
git branch -av
git merge upstream/develop
  • 新建目录Work:

    • software :wd add s

    • IdeaProjects :wd add i – FrontEnd – choice – life – opensource

    • ichoice:wd add c

笔记:SpringBoot如何集成Maven的profile功能

什么是profile

开发项目的时候要有多个环境,如开发环境、测试环境、生产环境,他们的配置文件一般不同。当我们要向各个环境发布程序时,需要人工处理这些配置文件,这显然麻烦且易错。有了profile,一切问题就简单了。只要在maven打包时使用下面命令即可。

 mvn clean package -Dmaven.test.skip=true -P prod

-P prod 就是告诉maven要使用名字为prod的profile来打包,这样打包后的jar包或war包,配置文件里的配置就是prod环境的。

实现思路

maven支持profile功能,当使用maven profile打包时,可以打包指定目录和指定文件,且可以修改文件中的变量。spring boot也支持profile功能,只要在application.properties文件中指定spring.profiles.active=xxx 即可,其中xxx是一个变量,当maven打包时,修改这个变量即可。

具体实现

一个springboot项目,工程中src/main/resourses目录下有以下4个配置文件:

  • application.properties: 包含通用配置的文件。文件中有spring.profiles.active=@profiles.active@的属性。这里的profiles.active和下面pom.xml配置中的profiles.active属性名要一致。这样,在运行mvn命令时,maven就会帮我们将@profiles.active@替换成指定的profile。
  • application-dev.properties: 当-P dev时, 打包这个文件。
  • application-test.properties: 当 -P test时,打包这个文件。
  • application-prod.properties: 当 -P prod时,打包这个文件。

在(父)pom.xml中定义maven的如下各个环境的profile配置,这里的profiles.active属性名可以自定义,其中test环境是默认激活的(activeByDefault为true),这样如果在不指定profile时默认是test环境。

  <profiles>
        <profile>
            <id>dev</id>
            <properties>
                <profiles.active>dev</profiles.active>
            </properties>
        </profile>
        <profile>
            <id>test</id>
            <properties>
                <profiles.active>test</profiles.active>
            </properties>
            <activation>
                <activeByDefault>true</activeByDefault>
            </activation>
        </profile>
        <profile>
            <id>pre</id>
            <properties>
                <profiles.active>pre</profiles.active>
            </properties>
        </profile>
        <profile>
            <id>prod</id>
            <properties>
                <profiles.active>prod</profiles.active>
            </properties>
        </profile>
    </profiles>

    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <filtering>true</filtering>
                <excludes>
					<exclude>application-dev.properties</exclude>
					<exclude>application-test.properties</exclude>
					<exclude>application-pre.properties</exclude>
					<exclude>application-prod.properties</exclude>
				</excludes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <filtering>true</filtering>
                <includes>
					<include>application-${profiles.active}.properties</include>
					<include>application.properties</include>
				</includes>
            </resource>
        </resources>
    </build>

说明:

  1. 这里的excludes 表示打包时,过滤多余其它目录或文件,比如过滤目录:
                <excludes>
					<exclude>dev/*</exclude>
					<exclude>test/*</exclude>
					<exclude>pre/*</exclude>
					<exclude>prod/*</exclude>
				</excludes>
  1. includes表示打包,指定要包含的文件,其中${profiles.active}会根据指定的profile动态替换,当然一般不需要过滤或指定,如下配置就可以了:
 <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>
  1. filtering 设置true表示处理文件时,可以对文件进行动态替换
  2. 在application.properties属性文件中替换变量时,使用@符号,而不是$, maven的maven-resources-plugin可以定义这个替换的符号。
spring.profiles.active=@profiles.active@
  1. 怎么在启动spring boot应用时,打印正在使用的profile,避免配置错误
public static void main(String[] args) {
  ApplicationContext ctx = SpringApplication.run(RestApiApplication.class, args);
  String[] activeProfiles = ctx.getEnvironment().getActiveProfiles();
  for (String profile : activeProfiles) {
    logger.warn("Spring Boot 使用profile为:{}" , profile);
  }
}

参考:

《高性能MySQL》读书笔记-第六章:查询性能优化

Acheron 按:
最近看了一些SQL优化的资料,这是《高性能MySQL》(第3版)(High Performance MySQL,Third Edition)第六章的读书笔记,整理于此。这本书可能更适合DBA或运维人员看,但第六章讲性能优化的部分,也适合我这样的后台开发人员。

一、为什么查询速度会慢

查询是一个任务,它有很多子任务构成,每个子任务都需要消耗一定的时间。若想优化查询,就要优化子任务,要么消除一些子任务,要么减少子任务的执行次数,要么减少子任务的执行时间。

查询的生命周期大致顺序:

从客户端,到服务器,然后在服务器上执行解析,生成执行计划,执行,然后将结构返回给客户端。

其中,执行是最重要的阶段,包括了大量为了检索数据到储存引擎的调用以及调用后的数据处理,包括排序,分组等。

在完成这些任务的时候,需要在不同的地方花费时间,包括网络IO,CPU计算,生成统计信息和执行计划 锁等待。

在调用底层数据引擎检索数据时,需要等待内存操作,CPU操作,和IO操作等待,这会产生大量的上下文切换和系统调用!

优化查询的目的就是减少和消除这些操作所花费的时间。

二、慢查询基础:优化数据访问

对于低效率的查询,一般使用下面的步骤来分析:

    1. 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行,但有时候也可能访问了太多的列。
    1. 确认Mysql服务器层是否在分析大量超过需要的数据行。

2.1 是否向数据库请求了不需要的数据

有些应用程序会请求超过实际需要的数据, 然后这些多余的数据会被应用程序丢弃。这会给Mysql服务器带来额外的负担,并增加网络开销(如果应用服务器和数据库不在同一台主机上,网络开销会显得很明显),另外也会消耗应用服务器的CPU和内存资源。

一些不好的操作:

  • 查询不需要的记录:只查询需要的记录,在相关查询后加上limit
  • 多表关联时返回全部列:多表关联时会从磁盘中读取全部列然后在内存中进行计算,非常低效。
  • 总是取出全部列:不要用select *,这会取出全部列,会让优化器无法完成索引覆盖扫描这类优化。
  • 重复查询相同的数据:当查询重复的数据时,可以考虑将数据缓存起来,需要是时候从缓存取。

2.2 Mysql是否在扫描额外的记录

对于Mysql,最简单的衡量查询开销的三个指标为:

  • 响应时间
  • 扫描的行数
  • 返回的行数

    这三个指标会记录到Mysql的慢日志中,所以检查慢日志记录是找出扫描行数过多的查询的好办法。

响应时间

响应时间是两个部分之后:服务时间和排队时间。

服务时间是指数据库处理这个查询真正花了多长时间。

排队时间是指服务器因为等待某些资源(I/O操作、行锁等)而没有真正执行查询的时间。

扫描的行数和返回的行数

理想情况下扫描的行数和返回的行数应该是相同的,但实际这种情况并不多,比如在做一个关联查询时,服务器心須要扫描多行才能生成结果集中的一行。

扫描的行数和访问类型

Explain语句中的type列反应了访问类型。访问类型从慢到快有:全表扫描(ALL),索引扫描,范围扫描,唯一索引查询,常数引用。

如果查询没有办法找到合适的访问类型,那最好的办法就是增加一个合适的索引。

Explain语句中的Extra如果是Using Where表示Mysql将通过where条件来筛选存储引擎返回的记录。

一般Mysql能够使用三种方式应用WHERE条件,从好到坏依次为:

  • 在索引中使用WHERE条件来过滤不匹配的记录,这是在储存引擎中完成的。
  • 使用索引覆盖扫描(explain语句的extra列出现了Using index)来返回记录。直接从索引中过滤不需要的记录并返回命中的结果,这是在Mysql服务器层完成的,但无须再回表查询记录。
  • 从数据表中返回数据,然后过滤不满足的条件(explain语句的extra列出现了Using where)。这在Mysql服务器层完成的,Mysql需要从数据表读取记录然后过滤。

如果发现查询需要扫描大量的数据但只返回少量的行,通常可以尝试下面的技巧去优化它:

  • 使用索引覆盖扫描,把所有用到的列都放到索引中,这样储存引擎无须回表获取对应行就可以返回结果了。
  • 改变库表结构,使用单独的汇总表,比如count()时。
  • 重写这个复杂的查询,让Mysql优化器能够以更优化的方式执行这个查询。

三、重构查询的方式

  • 1.一个复杂查询还是多个简单查询

有时候,将一个大的复杂查询分解为多个小查询往往效果会更好。Mysql从设计上让链接和断开链接都是轻量级的操作,在返回一个小的查询结果时很高效。

  • 2.切分查询

切分查询是对大查询”分而治之”,将大查询分为多个小查询,每个查询功能完全一样,只完成一小部分。

比如:定期的清楚大量数据时,如果一个大的语句一次性完成的话,则可能一次锁住很多数据,占满整个事物日志,耗尽系统资源,阻塞很多小的但重要的查询。这个时候可以分多次删除,每次删除一部分数据,删除一次暂停一会再接着做下一次删除,可以将服务器上一次性的压力分散到一个更长的时间段,性能会更好。

  • 3.分解关联查询

将一条关联多个表的sql语句通过应用程序拆分成多个操作单个表的语句。

分解关联查询方式的优势:

  • 让缓存效率更高,应用程序可以缓存了单表的结果对象,然后下次直接使用这个缓存的结果,这样就跳过了一次查询。
  • 将查询分解后,执行单个查询可以减少锁的竞争
  • 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展,为分布式做准备!
  • 查询本身效率也会有提升
  • 可以减少冗余记录的查询:在应用层做关联,意味着某条记录只需要查询一次,而在数据库做关联,则可以会重复返回一部分数据。
  • 这样分解相关于在应用中实现哈希索引,而不是使用数据库的嵌套循环关联

四、查询执行的基础

当向mysql发送一个请求的时候,mysql做了什么:

  • 1.客户端发送一条查询给服务器
  • 2.服务器先查询查询缓存,若缓存命中,则立刻返回,否则进入下一阶段
  • 3.服务器端进行SQL解析,预处理,再由优化器生成对应的执行计划
  • 4.Mysql根据优化器生成的执行计划,调用储存引擎的API执行查询
  • 5.将结果返回给客户端

4.1 Mysql客户端/服务器通信协议

Mysql客户端和服务器之间的通信协议是”半双工”的,这意味着,在任何一个时刻,要么是服务器向客户端发送数据,要么是客户端向服务器发送数据。这种简单的协议限制了Mysql,一个明显的限制是没办法进行流量控制,一旦一端开始发送数据,另一端中能接受完整的消息才能响应它。

查询状态:对于一个Mysql链接,或者一个线程,任何时候都有一个状态,该状态表示了Mysql当前正在做什么。查看当前状态可以用命令:

show full processlist

Mysql的状态值有:

  • Sleep:线程正在等待客户端发送新的请求
  • Query:线程正在执行查询,或者正在将结果发送到客户端
  • Locked:在Mysql服务器层,该线程正在等待表锁。在储存引擎级别实现的锁,例如InnoDB的行锁,并不会体现在线程状态中。对于MyISAM来说这是一个典型的状态,因为它没有行锁。
  • Analyzing and statistics:线程正在收集储存引擎的统计信息,并生成查询的执行计划。
  • Copying to tmp table [on disk]:线程正在执行查询,并且将结果集都复制到一个临时表中,这种状态要么是在执行GROUP BY操作,要么是文件排序操作,或者是UNION操作,[on disk]标记表示Mysql正在将一个内存临时表存放到磁盘上。
  • Sorting result:线程正在对结果集进行排序
  • Sending data:这表示多种情况,线程可能在多个状态之间传送数据,或者生成结果集,或者在向客户端返回数据。

4.2 查询缓存:

  • 在解析一个查询语句之前,要检查缓存是否命中。
  • 这个检查是通过一个对大小写敏感的哈希查找实现的
  • 若缓存命中了,那么在返回查询结果之前Mysql会检查一次用户权限。权限没问题,则直接从缓存中拿到结果返回客户端。

4.3 查询优化处理

查询生命周期的下一步是将一个SQL转换成一个执行计划,这个阶段包括多个子阶段:解析SQL,预处理,优化SQL执行计划。

语法解析器和预处理:Mysql通过关键字将SQL语句进行解析,并生成一颗对应的”解析树”,Mysql解析器将使用Mysql的语法规则验证和解析查询。比如:它将验证是否使用了错误的关键字,或者使用关键字顺序是否正确,或者验证引号能否正确匹配。

预处理器则根据一些Mysql规则进一步检查解析树是否合法,例如,这里将检查数据表和数据是否存在,还会解析名字和别名,看看他们是否有歧义。

下一步预处理器会验证权限。

4.4 查询优化器:

一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就好找到其中最好的执行计划。

Mysql使用基于成本的优化器,它尝试预测一下查询使用某种计划时的成本,并选择其中最小的一个。

Mysql能够处理的优化类型:

  • 重新定义关联表的顺序
  • 将外连接转化为内连接
  • 使用等价变换规则,如(5=5 and a>5)改为a>5
  • 优化COUNT() MIN() MAX():如要找某列的最小值,只需查B-Tree中最左端的记录
  • 预估并转化为常数表达式
  • 覆盖索引扫描
  • 子查询优化
  • 提前终止查询
  • 等值传播
  • 列表IN()的比较:mysql将in()列表中的数据先进行排序,然后通过二分查找的方式然定列表中的值是否满足条件

Mysql如何执行关联查询:在Mysql中,不仅仅是两个表匹配叫关联,其实单表查询,子查询都叫关联。

对于UNION查询,原理是Mysql先将一系列的单个查询放到一个临时表中,然后再重新读出临时表的数据来完成UNION查询。在Mysql概念中,每个查询都是一次关联,所以读取临时表的也是一次关联!

  • 执行计划:

和其他关系数据库不同,Mysql不会生成查询字节码来执行查询。Mysql生成查询的一颗指令树,然后通过储存引擎执行完成这颗指令树并返回结果。如果对某个查询执行explain extended后,再执行show warnings,就可以看到重构出的查询。

  • 排序优化:

排序是一个成本很高的操作,从性能考虑,应尽可能避免排序或尽可能避免对大量数据排序。

  • mysql两种排序算法:
    • 两次传输排序(旧版本使用):先读取行指针和需要排序的字段,进行排序,然后根据排序结果读取所需要的数据行。这需要进行两次数据传输,并且第二次是随机IO,所以成本很高,不过好处是在排序时使用的储存很少!
    • 单次传输排序(新版本使用):先查询所需要排序的列,然后根据给定列进行排序。

五、优化案例

5.1 优化count()查询

count()两种作用:

  • 统计某个列值的数量(不统计null)
  • 统计结果集的行数,用通配符count(*)

案例1:

如何快速查找到所有id>5的城市,首先想到这样查询:

select count(*) from City where id > 5;

通过show status的结果可以看到这个查询要扫描4000行数据,如果将条件反转一下,先查找id<=5的数,然后总数减去,可以将扫描行数减少到5行,大大提高效率,这是因为在查询优化阶段会将其中的子查询直接当作一个常数来处理:

select (select count(*) from City)-count(*) from City where id <=5;

案例2:

问题:如何在同一个查询中统计同一列的不同值的数量,以减少查询的语句量。

解决,用sum():

select sum(if(color='blue',1,0)) as blue,sum(if(color='red',1,0)) as red from items;

或者使用count(),只需要将满足条件设置真,不满足条件设置为null:

select count(color='blue' or null) as blue,count(color='red' or null) as red from items;

5.2 优化关联查询

  • 确保on或者using子句中列上有索引
  • 确保group by或order by表达式中只涉及到一个表中的列,这样mysql才有可能使用索引来优化这个过程

5.3 group by

如果没有通过order by子句显式地指定排序列,当查询使用group by子句的时候,结果集会自动按照分组的字段进行排序,如果不关心结果集顺序,则可以使用order by null,让mysql不再进行文件排序。

5.4 优化limit分页

问题:

当偏移量非常大的时候,例如可以是limit 10000,20,这里mysql需要查询10000条记录然后只返回最后20条,前面10000条将被抛弃,翻到很后面,性能会很差。

延迟查询:

select film_id,desc from sakila.film order by title limit 50,5;

如果这个表非常大,改成延迟查询将大大提高效率,它让mysql扫描尽可能少的页面,获取需要访问的记录后再根据关联列回原表查询需要的所有列:

select film.film_id,film.desc
from sakila.film
    innser join(
        select film_id from sakila.film order by title limit 50,5
    ) as lim using(film_id);