Mysql性能优化 - Mysql运行原理

Mysql体系

1. Client Connectors 接入方，支持协议有很多
2. Management Serveices & Utilities 系统管理和控制工具，mysqldump，mysql复制集群，分区管理等
3. Connection Pool 连接池，夫案例缓冲用户连接， 用户名，密码，权限校验，线程处理等需要缓存的需求
4. SQL Interface SQL 接口，接受用户的SQL命令，并且返回用户需要查询的结果
5. Parser 解析器，SQL命令传递到解析器的时候会被解析器验证和解析，解析器是有Lex和YACC实现的
6. Optimizer 查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化
7. Cache和Buffer（高速缓冲区） 查询缓存，如果查询缓存命中查询结果，查询语句直接从缓存中获取数据
8. pluggable storage Engines 插拔式存储引擎，用于和文件系统打交道
9. file system 文件系统，数据，日志（redo， undo），索引，错误日志，查询记录，慢查询等

查询执行路径

1. mysql 客户端/服务端通信（半双工）
2. 查询缓存
3. 查询解析优化
4. 查询执行引擎
5. 返回给客户端

mysql客户端服务端通信

Mysql客户端与服务端通信方式为“半双工”

• 全双工：双向通信，发送同时也可以接受消息
• 半双工：双向通信，同时只能接受或者发送，无法同时操作
• 单工：只能单一方向传送

半双工通信：

在任一时刻，要么发送，要么接收，两个动作不能同时发生，无法将一个消息切成小块进行传输，客户端一旦发送消息，另一端要接收完整个消息才能响应。客户端一旦开始接收数据就没法停下来发送指令。

mysql查询客户端/服务端通信状态

show full processlist / show processlist

状态：

sleep 线程正在等待客户端发送数据

query 连接线程正在执行查询

locked 线程正在等待表锁的释放

sorting result 线程正在等待对结果进行排序

sending data 向请求端返回数据

所有状态文档：https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

查询缓存

原理

缓存select操作的结果和sql语句，新的select语句，先去查询缓存，判断是否存在可用记录集，有直接从缓存中获取。

判断标准

与缓存的sql语句，是否完全一样，区分大小写

缓存配置

query_cache_type

• 0 不启用缓存，默认值
• 1 启用查询缓存，只要符合查询缓存的要求，客户端查询语句和记录集都可以缓存起来，供其他客户端使用，加上SQL_NO_CACHE将不缓存
• 2 启用查询缓存，只要查询语句中添加了参数：SQL_CACHE，且符合查询缓存的要求，客户端的查询语句和记录集，则可以缓存起来，供其他客户端使用

query_chche_size

允许设置的值最小为40k，默认为1M，推荐设置为：64M/128M

query_cache_limit

限制查询缓存取最大能缓存的记录集，默认为1M

show status like ‘Qcache%’ 可以查看缓存情况Q

不会被缓存的情况

1. 查询语句中包含一些不确定的数据时，则不会被缓存，如NOW（），CURRENT_DATE()等类型函数，或者用户自定义函数，用户变量等都不会被缓存
2. 查询结果大于query_cache_limit设置的值时，结果不会被缓存
3. 对于InnoDB引擎来说，当一个语句在事务中修改了某个表，那么在这个事务提交前，所有与这个表相关的查询都无法被缓存，长时间执行事务会降低缓存的命中率。
4. 查询的表是系统表
5. 查询语句不涉及到表

为何mysql默认关闭缓存

1. 在查询之前必须先坚持是否命中缓存，浪费计算资源
2. 如果这个查询可以被缓存，那么执行完成后，Mysql发现查询缓存中没有这个查询，则会将结果存入查询缓存中，会带来额外的系统消耗
3. 针对表进行写入或者更新数据时，将对应的表所有的缓存都设置失效
4. 查询缓存很大或者碎片很多时，操作可能带来很大的系统消耗

适用业务场景

以读为主的业务，数据生成之后就不常改变的业务

查询优化处理

查询优化处理的三个阶段

1. 解析sql
   通过lex词法分析，YACC语法分析将sql语句解析成解析树

2. 预处理阶段
   根据mysql的语法的规则进一步检查解析树的合法性，坚持表，列是否存在等

3. 查询优化器

   优化器主要用于找到最优的执行计划

查询优化器如何找到最优执行计划

mysql的查询优化器时基于成本计算的原则，会尝试各种执行计划。数据抽样的方式进行试验（4k）。

1. 等价变换规则

   1=1 and a>1 改成 a > 1

   a<b and a=5 改成 b>5 and a=5

2. 优化count，max，min等函数

   min只需要找到索引最左侧
   max只需要找到索引最右侧
   myisam count不需要遍历

3. 覆盖索引扫描

4. 子查询优化

5. 提前终止查询
   使用了limit关键字或者使用不存在的条件

6. IN的优化

   先进行排序，再采用二分法查找

执行计划

EXPLAIN sql语句 查看sql语句执行计划

• id 查询***，标识执行顺序

  1. id相同，自上而下执行
  2. id不同，子查询，id值越大越优先执行
  3. 同时存在，id大的先执行，id相同的自上而下执行

• select_type 查询类型，主要用于区分普通查询，联合查询，子查询等

  1. simple 简单的select查询，查询中不包含子查询或者union
  2. primary 查询中包含子部分，最外层被标记为primary
  3. subquery/materialized subquery表示再select或者where列表中包含了子查询，materialized表示where后面in条件的子查询
  4. union 若第二个select出现再union之后，则被标记为union
  5. union result 从union表中获取结果的select

• table 查询设计到的表，显示表名或者表的别名

  1. <unionM,N> 由ID为M,N查询union产生的结果
  2. 由ID为N查询产生的结果

• type 访问类型，sql查询优化中一个很重要的指标，结果值由好到坏依次为：sysetm，const，eq_ref，ref,range,index,all

  1. system 表只有一行记录，const类型的特例，基本不会出现
  2. const 表示只通过索引一次就找到了，const用于比较primary key或者unique索引
  3. eq_ref 唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描
  4. ref 非唯一索引扫描，返回匹配某个单独值的所有行
  5. range 只检索给定范围的行，使用一个索引来选择行
  6. index 索引全表扫描，把索引从头到尾扫描一遍
  7. all 全表扫描

• Extra 额外信息

  1. using filesort 对数据使用一个外部的文件内容进行了排序， 而不是按照索引进行排序读取
  2. using temporary 查询时使用了临时表
  3. using index 使用了覆盖索引
  4. using where 使用了where过滤
  5. select tables optimized away 使用了基于索引优化 如min，max，count等

• 其他

  1. possible_keys 可能用到的索引
  2. key 实际使用的索引
  3. rows 根据表统计信息或者索引选用情况，大致估算出找到所需记录需要读取的行数
  4. filterd 返回结果的行数占需要读取的行数的百分比，越大越好

查询执行引擎

调用插件式的存储引擎的原子api功能进行执行计划的执行

返回客户端

1. 有需要做缓存的，执行缓存操作
2. 产生第一条结果时，mysql就开始往请求方逐步返回数据，节省mysql内存。