way-to-architect
  • 前言
  • Java
    • Java关键字
      • Java中四种修饰符的限制范围
      • static和final
    • 容器
      • 容器概述
        • 容器:综述
        • Iterator原理及实现
        • fast-fail机制
        • 比较器Comparator
        • Collections工具类
      • List
        • List综述
        • ArrayList原理分析
        • ArrayList在循环过程中删除元素的问题
        • 常用的小技巧
        • CopyOnWrite
      • Set
        • Set综述
        • HashSet
        • LinkedHashSet
        • TreeSet
      • Queue
        • Queue综述
        • ArrayBlockingQueue实现原理
        • LinkedBlockingQueue实现原理
        • 高性能无锁队列Disruptor
      • Map
        • Map综述
        • HashMap
          • HashMap实现原理
          • HashMap中的位运算
          • HashMap其他问题
        • LinkedHashMap
        • TreeMap
        • ConcurrentHashMap
          • ConcurrentHashMap实现原理JDK1.7
          • ConcurrentHashMap实现原理JDK1.8
        • ConcurrentSkipListMap
        • Map中key和value的null的问题
    • 线程
      • 线程
        • 创建线程
        • 线程状态及切换
        • 线程中断的理解
        • 几种方法的解释
        • 用户线程与守护线程
        • 线程组ThreadGroup
      • 线程池
        • 线程池工作原理及创建
        • Executor
        • 如何确保同一属性的任务被同一线程执行
      • ThreadLocal
        • ThreadLocal原理
        • ThreadLocal之父子线程传值
        • InheritableThreadLocal
      • 同步与锁
        • 线程安全与锁优化
        • synchronize关键字
        • Lock
          • 队列同步器
            • 同步状态的获取与释放
            • 使用方式
            • 示例:Mutex
            • 示例:TwinsLock
          • 重入锁和读写锁
          • LockSupport
          • Condition
          • 并发工具类
        • CAS
          • CAS的理解
          • Java中原子操作类
        • 3个经典同步问题
      • fork/join的理解
    • I/O
      • I/O概述
        • 磁盘I/O与网络I/O
        • 主要接口
        • 输入流和输出流的使用示例
        • InputStream的重复读
        • BufferdxxxxStream
        • Serailizable
        • File常用方法
        • Files和Path
        • RandomAccessFile
        • 通过零拷贝实现有效数据传输
        • 正确地处理文件
      • NIO基础
      • NIO2
      • Netty
        • Java I/O的演进之路
        • 为什么是Netty
        • 更多
      • I/O调优
    • 异常
      • 异常体系及为什么要有这种异常设计
      • 多catch的执行情况
      • try catch finally 与reture
      • 异常处理的误区
      • Preconditions:方法入参校验工具
    • 枚举
      • 常见用法
      • 枚举类在序列化中的问题
    • 注解
      • 概述
      • Spring中的组合注解的条件注解
      • 常用注解
        • JSR-330标准注解
    • 反射
      • 概述
      • 内部类的反射
      • 反射中需要注意的地方
    • 流程控制
      • switch case without break
      • Java: for(;;) vs. while(true)
    • JVM
      • JVM内存结构
      • Java内存模型
      • 垃圾收集器和内存分配策略
      • 四种引用类型区别及何时回收
      • 类文件结构
      • 类初始化顺序
      • 类加载机制
      • 虚拟机执行引擎
      • 逃逸分析
      • JVM常用配置
      • GC日志分析
      • Java8 JVM 参数解读
      • 垃圾收集器和内存分配策略
    • 面向对象
      • Object类中的方法
      • Class类中的方法
      • 值传递还是引用传递?
      • 接口和抽象类的区别
      • 深拷贝和浅拷贝
      • Integer.parseInt()与Interger.valueof()
      • hashCode()与equal()
      • String
        • String池化及intern()方法的作用
        • 关于字符串
    • 序列化
      • Java序列化的方式有哪些?
    • 新特性
      • 流 Stream
        • Stream是什么
        • Stream API详解
        • Stream进阶
        • 流编程
        • 其他事项
      • lambda表达式
      • 默认方法(Default Methods)
      • @FunctionalInterface注解
    • SPI
      • 理解SPI
    • 字节码
      • javaagent
      • 字节码操纵
      • 如何查看类编译后的字节码指令
      • 字节码指令有哪些
  • Python
    • 异常处理
  • Go
  • 数据结构与算法
    • 数据结构
      • 概述
        • 线性表
        • 栈
        • 队列
        • 串
        • 树
        • 图
      • Java的一些实现
      • 红黑树
      • 双缓冲队列
      • 跳表SkipList
    • 算法
      • 概述
      • 常见算法
        • 基本排序
        • 高级排序
        • 动态规划
  • 框架或工具
    • Spring
      • Spring基础
        • Spring整体架构
        • 什么是IoC
        • Ioc容器的基本实现
        • Spring的MainClass
          • Spring的BeanFactory
          • Spring的Register
          • Spring的Resource和ResourceLoader
          • Spring的PropertySource
          • Spring的PropertyResolver
          • Spring的PropertyEditor
          • Spring的Convert
          • Spring的BeanDefinition
          • Spring的BeanDefinitionReader
          • Spring的BeanDefiniton其他Reader
          • Spring的BeanDefinition其他Reader2
          • Spring的Aware
          • Spring的BeanFctoryPostProcessor
          • Spring的BeanPostProcessor
          • Spring的Listener
        • Xml格式的应用启动
          • Xml格式的应用启动2
          • Xml格式的应用启动3
          • Xml格式的应用启动4
          • Xml格式的应用启动5
          • Xml格式的应用启动6
          • Xml格式的应用启动7
        • Spring中的设计模式
        • 什么是AOP
        • Spring中AOP的实现
      • Spring应用
        • Spring的事务控制
        • @Transactional注解在什么情况下会失效
        • 如何在数据库事务提交成功后进行异步操作
        • Spring中定时任务的原理
    • SpringMVC
      • Controller是如何将参数和前端传来的数据一一对应的
      • 请求处理流程
    • Zookeeper
      • Zookeeper是什么
      • Zookeeper能干啥
    • Shiro
    • druid
    • Netty
    • Consul
      • Consul是什么
    • etcd
    • confd
    • Akka
      • Actor模型是什么
  • 数据库
    • 基本概念
    • MySQL
      • 基本配置
      • MySQL数据类型
      • MySQL存储引擎
      • MySQL事务
        • MySQL事务概念
      • MySQL索引
        • MySQL中的索引类型
        • B-Tree/B+Tree概述
        • 为什么使用B+Tree
        • MySQL中的B+Tree索引
        • MySQL高性能索引策略
      • MySQL查询
        • MySQL查询过程
        • MySQL查询性能优化
        • 使用EXPLAIN
      • MySQL锁
        • MySQL中锁概述
        • InnoDB的并发控制
        • MySQL乐观锁
      • MySQL分库分表
        • 分库/分表
        • 跨库JOIN
        • 跨库分页
        • 分库分表后的平滑扩容
        • 分区表
        • 分布式ID生成方法
      • MySQL实战
        • 在线表结构变更
        • MySQL优化规则
        • MySQL问题排查
        • 常见查询场景
    • Redis
    • Hbase
    • OpenTSDB
    • rrd
    • MongoDB
    • 连接池
  • 系统设计
    • 一致性Hash算法
    • 限流
      • 限流是什么
      • 限流算法
      • 应用内限流
      • 分布式限流
      • 接入层限流
        • ngx_http_limit_conn_module
        • ngx_http_limit_req_module
        • lua_resty_limit-tarffic
      • 节流
    • 降级
      • 降级详解
      • 人工降级开关的实现
      • 自动降级的实现:Hystrix
    • 负载均衡
      • 概述
      • 互联网架构下的负载均衡
      • Nginx负载均衡(七层)
      • Nginx负载均衡(四层)
      • Nginx动态配置
    • 超时与重试机制
      • 什么地方要超时与重试
      • 代理层超时与重试
      • Web容器超时
      • 中间件客户端超时与重试
      • 数据库超时
      • NoSQL客户端超时设置
      • 业务超时
      • 前端请求超时
    • 网关
    • CAP
      • 什么是CAP
      • CAP理解
    • 生产者-消费者模型
      • 使用notify/wait方式
      • 使用await/signal实现
      • 使用阻塞队列实现
      • 使用信号量实现
      • 使用管道流实现
      • 无锁队列Disruptor
      • 双缓冲队列
    • 缓存
      • 缓存概述
      • 数据库缓存
      • 应用缓存
      • 前端缓存
      • 本地缓存
    • 秒杀
    • LRU
  • 版本控制
    • Git
      • Git常用命令
      • 场景命令
    • Svn
  • 计算机操作系统
    • Linux
      • Linux中重要概念
      • 常用命令
      • 查看日志
      • 权限管理
      • 登录或传输
      • 防火墙
      • 配置ssh免密
      • 进程
      • 防火墙
    • Mac
    • 计算机基础
      • 进制
      • Java中的位运算
      • 计算机存储系统结构
  • 网络
    • TCP三次握手和四次挥手
    • 网络术语
      • 网关、路由器、交换机、IP等
      • VLAN
      • LAN
  • 设计模式
    • 设计模式概述
    • 创建型
      • 单例模式
      • 工厂模式
      • 建造者模式
      • 原型模式
      • 享元模式
    • 行为型
      • 观察者模式
      • 策略模式
      • 模板模式
      • 责任链模式
      • 命令模式
      • 外观模式
      • 迭代器模式
      • 中介者模式
        • 中介模式续
      • 状态模式
        • 状态模式实例
        • 状态模式思考
      • 访问者模式
        • 访问者实例1
        • 访问者模式续
    • 结构型
      • 组合模式
        • 组合模式续
      • 装饰模式
        • 装饰模式续
      • 代理模式
      • 备忘录模式
      • 桥接模式
        • 桥接模式实例一
  • 构建工具
    • Maven
      • 常用命令
      • Maven生命周期
      • Maven中的变量和属性
      • 不同环境的如何配置不同的变量
      • 常用插件及配置
      • 其他问题
      • dependencies与dependencyManagement的区别
    • Gradle
  • 大数据
    • Hadoop
    • Storm
    • Spark
  • 服务器
    • Tomcat
      • server.xml配置详解
      • 线程池和连接数配置
      • Maven远程部署
      • 一些小技巧
      • Tomcat类加载机制分析
      • Tomcat的日志
      • Tomcat架构
        • 概述
        • Server 的启动流程
        • 请求处理流程
    • Nginx
      • 常用命令
      • 基本配置
      • Lua
    • Tengine
  • 中间件
    • 任务调度
      • 为什么需要任务调度
    • 消息队列
      • 为什么需要消息队列
      • 消息队列关键点
      • 消息中间件需要解决的问题
      • 不同消息队列产品对比
      • RocketMQ
        • 快速入门
        • 整体架构
        • 部署方式
          • Broker部署方案
        • 客户端使用
          • 客户端使用指南
          • 快速开始
          • 简单示例
          • 有序消息示例
          • 广播消息示例
          • 定时消息示例
          • 批量消息示例
          • 过滤消息示例
          • 日志输出配置示例
        • 关键点实现
          • 顺序消息的实现
        • 最佳实践
          • Broker的最佳实践
          • 生产者最佳实践
            • 生产者最佳实践续
          • 消费者最佳实践
            • 消费者最佳实践续
          • 名称服务最佳实践
          • JVM/kernel配置的最佳实践
          • 新特性 Filter Server
          • 其他事项
      • RabbitMQ
      • Kafka
    • 分布式事务
      • 什么是分布式事务
      • 解决方案
    • 服务治理
      • RPC概念
      • RPC最简实现
      • 为什么需要服务治理
      • Dubbo
        • Dubbo整体架构
      • Java RMI
    • 分布式锁
      • 如何设计分布式锁
        • 基于zookeeper
        • 基于Redis
    • 注册中心
      • 注册中心的职责
      • 不同注册中心的比较
    • 配置中心
      • 概述
      • 配置中心的实现与选型
  • Web开发
    • Http请求类型及区别
    • 常见的content-type
    • 如何处理跨域
    • Restful最佳实践
    • HTTP状态码
    • Http下载原理
  • 测试
    • 压测:apache bench
    • 压测:Jmeter
Powered by GitBook
On this page
  • MySQL逻辑架构
  • 查询执行的基础
  • 客户端/服务端通信
  • 查询缓存
  • 解析器和预处理
  • 查询优化器
  • 查询执行引擎
  • 返回结果给客户端
  1. 数据库
  2. MySQL
  3. MySQL查询

MySQL查询过程

PreviousMySQL查询NextMySQL查询性能优化

Last updated 6 years ago

我们写了一条SQL语句,MySQL从接收到这条语句,直到返回给我们结果的这个过程中,MySQL内部做了什么。

MySQL逻辑架构

下图展示了MySQL的逻辑架构图:

图1(来源:《高性能MySQL》page2)

  • 第一层:连接处理、授权认证、安全等

  • 第二层:查询解析、分析、优化、缓存、内置函数以及所有的跨存储引擎的功能:存储过程、触发器、视图等。

  • 第三层:存储引擎,负责MySQL中的数据存储和提取。和Linux下的文件系统类似,每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信,这些API接口屏蔽了不同存储引擎间的差异。

我们总是希望MySQL能够获得更高的查询性能,最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点,就会发现:很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。

查询执行的基础

当向MySQL发送一个请求的时候,MySQL到底做了些什么呢?

图2(来源:《高性能MySQL》page204)整个过程如下:

(1)客户端向MySQL服务器发送一条查询请求

(2)服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段

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

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

(5)将结果返回给客户端

下面,将详细介绍各个阶段MySQL的具体工作,以及我们在使用时,可以进行优化的地方。

客户端/服务端通信

MySQL客户端/服务端通信协议是“半双工”的:在任一时刻,要么是服务器向客户端发送数据,要么是客户端向服务器发送数据,这两个动作不能同时发生。

客户端发送SQL给服务端

客户端用一个单独的数据包将查询请求发送给服务器,所以当查询语句很长的时候,需要设置max_allowed_packet参数:如果查询语句的大小超出该大小,服务端会拒绝接收更多数据并抛出异常。

服务端发送结果给客户端

服务器响应给用户的数据通常会很多,由多个数据包组成。但是当服务器响应客户端请求时,客户端必须完整的接收整个返回结果,而不能简单的只取前面几条结果,然后让服务器停止发送。

优化原则:尽量保持查询简单且只返回必需的数据,减小通信间数据包的大小和数量,尽量避免使用SELECT *以及加上LIMIT限制。

此外,可以通过show full processlist来查询每个MySQL连接(或线程)的状态:

  • Sleep:线程正在等待客户端发送新的请求。

  • Query:线程正在执行查询或者正在将结果发送给客户端。

  • Locked:线程正在等待表锁(主要是针对MyISAM,InnoDB中的行锁不会体现在线程的状态中)。

  • Analyzing and statistics:线程正在收集存储引擎的统计信息,并生成查询的执行计划。

  • Copying to tep table:线程正在执行查询,并且将其结果集都复制到一个临时表中,这种状态一般要么是在做 GROUP BY操作,要么是文件排序操作,或者是 UNION操作。如果这个状态后面还有“ on disk”标记,那表示 MYSQL正在将一个内存临时表放到磁盘上。

  • Sorting result :线程正在对结果集进行排序。

  • Sending data:这表示多种情况:线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据。

查询缓存

在解析一个查询语句前,如果查询缓存是打开的,那么MySQL会检查这个查询语句是否命中查询缓存中的数据:该查询是通过哈希查找实现。如果当前查询恰好命中查询缓存,在检查一次用户权限后直接返回缓存中的结果。这种情况下,查询不会被解析,也不会生成执行计划,更不会执行。

如果数据表被更改,那么和这个数据表相关的全部Cache全部都会无效,并删除。这里“数据表更改”包括:INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等。如果你的数据表更新频繁的话,那么Query Cache将会成为系统的负担。如果你的应用对数据库的更新很少,那么Query Cache将会作用显著。

解析器和预处理

解析器:使用MySQL语法规则验证和解析查询,并生成一棵对应的“解析树”,这些解析包含但不限于如下内容:

    • 验证是否使用错误的关键字

    • 使用关键字的顺序是否正确等

    • 引号是否能前后正确匹配。

预处理:根据一些MySQL规则进一步检查解析树是否合法,包含但不限于如下内容:

    • 检查数据表和数据列是否存在

    • 解析名字和别名,看看它们是否有歧义

    • 验证权限,这一步通常很快,除非服务器上有非常多的权限配置。

查询优化器

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

MySQL使用基于成本的优化器,它尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。但是有非常多的原因会导致MySQL选择错误的执行计划:①统计信息不准确(比如InnoDB并不能精确维护一个表的的行数);②执行计划中的成本估算不等同于实际的执行计划的成本;③ MySQL的最优可能与你想的最优不一样(即成本最低并不一定是时间最短);④ MySQL从不考虑其他并发的查询,这可能会影响当前查询的速度;⑤ MySQL也不是任何时候都是基于成本的优化,有时候也会基于一些固定的规则;⑥ MySQL不会考虑不受其控制的成本(例如执行存储过程或者用户自定义的函数的成本)。

在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

查询优化器优化类型

  1. 重新定义关联表的顺序

    • 数据表的关联并不总是按照在查询中指定的顺序进行,决定关联的顺序是优化器很重要的一部分功能。

  2. 将外连接转化成内连接

    • 并不是所有的outer join语句都必须以外连接的方式执行。诸多因素,例如where条件、库表结构都可能会让外连接等价于一个内连接。MySQL能够识别这点并重写查询,让其可以调整关联顺序。

  3. 使用等价变换规则

    • 使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较,还可以移除一些恒成立和一些恒不成立的判断。

    • (5=5 and a>5)将被改写为a>5。

    • (a<b and b=c)and a=5 将会被改写为 b>5 and b=c and a=5。

4. 优化count()、min()和max()

    • min():只查询对应B-tree索引最左端的记录,MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点,在B-tree索引中,优化器会将这个表达式最为一个常数对待。

    • max():只需要读取B-tree索引的最后一个记录。如果MySQL使用了这种类型的优化,那么在explain中就可以看到“select tables optimized away”。从字面意思可以看出,它表示优化器已经从执行计划中移除了该表,并以一个常数取而代之

    • count(*):没有任何where条件的count(*)查询可以使用存储引擎提供的优化,例如,MyISAM维护了一个变量来存放数据表的行数(但InnoDB中没有)。

5. 预估并转化为常数表达式

6. 覆盖索引扫描

    • 当索引中的列包含所有查询中需要使用的列的时候,MySQL就可以使用索引返回需要的数据,而无需查询对应的数据行。

7. 子查询优化

    • MySQL在某些情况下可以将子查询转换成一种效率更高的形式,从而减少多个查询多次对数据进行访问。

8. 提前终止查询

    • 在发现已经满足查询需求的时候,MySQL总是能够立即终止查询。一个典型的例子就是当使用了limit子句的时候。除此之外,MySQL还有几种情况也会提前终止查询,例如发现了一个不成立的条件,这时MySQL可以立即返回一个空结果。

9. 等值传播

10. in()

    • 在很多数据库系统中,in()完全等同于多个or条件的字句,因为这两者是完全等价的。

    • 在MySQL中这点是不成立的,MySQL将in()列表中的数据先进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件,这是一个o(log n)复杂度的操作,等价转换成or的查询的复杂度为o(n),对于in()列表中有大量取值的时候,MySQL的处理速度会更快。

查询执行引擎

在完成解析和优化阶段以后,MySQL会生成对应的执行计划(一个数据结构,而非字节码),查询执行引擎根据执行计划给出的指令逐步执行得出结果。

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

返回结果给客户端

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

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

结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时,就开始向客户端逐步返回结果集了,这样做的好处是:

    • 服务端就无须存储太多结果而消耗过多内存;

    • 可以让客户端第一时间获得返回结果。

内容来源

《高性能MySQL》

但在实际应用中,查询缓存是否开启,有很多不同意见,参考:以及。

理解MySQL数据库查询缓存
Query Cache,看上去很美
我必须得告诉大家的MySQL优化原理