MySQL - SQL 运行原理

建立连接

建立连接：通过客户端、服务器通信协议与MYSQL 建立连接。

• MYSQL 客户端与服务器的通信方式是"半双工"。
• 对于每一个MYSQL 的连接，时刻有一个线程状态来标识该线程正在做什么。
• 通信机制：
  全双工： 能同时发送和接收数据，例如平时打电话。
  半双工： 指的某一时刻，要么发送数据，要么接收数据，不能同时。例如早期对讲机。
  单工： 只能发送数据或只能接收数据。例如单行道。
• show processlist: // 查看用户正在运行的线程信息，root 用户能查看所有线程，其他用户只能查看自己的。
  id： 线程ID， 可以使用kill ***；
  user: 启动这个线程的用户
  host: 发送请求的客户Duang的IP 和端口号
  db: 当前命令在哪个库执行
  command 该线程正在执行的操作命令

 		 Create DB: 正在创建库操作
         Drop DB: 正在删除库操作
         Execute: 正在执行一个PreparedStatement 
         Close Stmt: 正在关闭一个PreparedStatement
         Query: 正在执行一个语句
         Sleep: 正在等待客户端发送语句
         Quit： 正在退出
         shut down: 正在关闭服务器

time: 表示该线程处于当前状态的时间，单位是秒
state: 线程状态

updating: 正在搜索匹配记录，进行修改
Sleeping: 正在等待客户端发送新请求
Starting: 正在执行请求处理
Checking table: 正在检测数据表
closing table: 正在将表中数据刷新到磁盘中
locked: 被其他查询锁住了记录
Sending Data； 正在处理select 查询，同时件结果发送给客户端

info： 一般记录线程执行的语句，默认显示前100个字符。想看完整的使用 show full processlist

查询缓存

这是Mysql 的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL 语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的SQL 语句则ui有解析器进行语法语义解析，并生成"解析树"。

• 缓存select 查询的结果和SQL 语句。
• 执行select 查询时，先查缓存，判断是否存在可用的记录集，要是是否完全相同（包括参数），这样才会匹配缓存数据命中。
• 即使开启查询缓存，以下SQL 也不能命中。
  查询语句使用SQL_NO_CACHE
  查询的结果大于query_cache_limit 设置
  查询中有一些不确定的参数，比如now()

show variables like '%query_cache%' // 查看查询缓存室友启用，空间大小，限制等
show status like 'Qcache%' // 查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等

解析器

解析器将客户端发送的SQL进行语法解析，生成解析树。预处理根据一些MYSQL 规则进一步检查"解析树"是否合法，例如将检查数据表和数据列是否存在，还会解析名字和别名，看看他们是否有歧义，最后生成新的解析树。

查询优化器

根据"解析树"生成最优的执行计划。MySQL 使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化），动态优化（运行时有优化）

• 等价变换策略
  5 = 5 and a > 5 改变成 a > 5
  a < b and a = 5 改变成 b > 5 and a = 5
  基于联合索引，调整条件位置等
• 优化count、min、max 等函数
  innoDB 引擎min 函数只需要找索引最左边
  innoDB 引擎max 函数只需要找索引最右边
  MyISAM 引擎count(*), 不需要计算，直接返回
• 提前终止查询
  使用了 limit 查询，获取limit 所需的数据，就不在继续遍历后面数据
• in 的优化
  MySQL 对in 查询，会先进行排序，在采用二分法查找数据。比如
  where id in （2，1，3）, 变成in （1， 2， 3）

查询引擎

查询引擎负责执行SQL 语句，此时查询执行引擎会根据SQL 语句中的表的存储类型，以及对应的API 接口与底层存储引擎缓存或物理文件的交互，得到查询结果并返回给客户端。若开启查询缓存，这个时会将SQL 语句和结果完整的保存到查询缓存中，以后若有相同的SQL 语句执行则直接返回结果。

• 如果开启了查询缓存，先将查询结果做缓存操作
• 返回结果过多，采用增量模式返回