MySQL查询优化

MySQL体系结构

Client Connectors指不同语言与MySQL之间的交互
**Services&utilities：**负责备份恢复、安全性控制、数据库复制、分区等。
**SQL Interface:**接收用户请求，生成结果。
**Parser：**分析SQL语句是否符合规则；将结果集解析成可被应用程序处理的数据结构。
**Optimizer：**查询优化器，SQL语句的最优执行顺序。

MySQL语句执行过程

客户端、服务器的通讯——>查询缓存——> 查询优化处理——>调用执行引擎——>返回客户端

MySQL客户端、服务器之间的通讯

首先，了解几个概念：
1. 什么是MySQL的客户端和服务器？
我们在Windows平台上进行MySQL安装的时候，会同时安装MySQL的客户端和服务器，在Linux系统上，两者是分开的。在Windows系统上，如果是使用命令行使用数据库，使用时需要首先使用net start 命令启动MySQL服务，这里可以视作开启MySQL的服务器端，然后需要进入安装目录下的bin文件夹下，运行mysql.exe程序，此处可以视作MySQL的客户端。如果是使用如workbench等程序，则其可以也视作MySQL的客户端。
既然MySQL分为客户端和服务器两个部分，那么就存在客户端和服务器之间通讯的问题。我们平时练习使用的MySQL，客户端和服务器都在同一台机器上，如何访问其他机器上的服务器呢？首先，服务器端首先需要建立允许非本地登录的账号，然后在客户端登录时，指定要连接的服务器；例如在命令行模式下，使用mysql -h 主机地址 -u用户名 -p 密码。详细请参考其他博文。
以上是个人理解，如果有错希望大佬指正。
2.数据传输方式有哪些？
半双工：半双工数据传输允许数据在两个方向上传输，但是，在某一时刻，只允许数据在一个方向上传输。它实际上是一种切换方向的单工通信。例如：对讲机。
全双工：全双工数据通信允许数据同时在两个方向上传输；例如：电话通信
单工：单工数据传输只支持数据在一个方向上传输；在同一时间只有一方能够接受或发送信息，不能实现双向通信；例如：电视、广播。

MySQL客户端与服务端的通信方式是“半双工”。客户端一旦开始发送消息另一端要接收完整个消息才能响应；客户端一旦开始接收数据没法停下来发送指令。

对于一个mysql连接，或者说一个线程，时刻都有一个状态来标识这个连接正在做什么。
查看线程状态命令：
show full processlist（普通用户）
show processlist(root账户，普通用户使用只能查看本用户开启的线程)

id列:一个标识，你要kill 一个语句的时候很有用。
user列: 显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。
host列:显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户。
db列:显示这个进程目前连接的是哪个数据库。
command列:显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。
time列:此这个状态持续的时间，单位是秒。
state列:显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。
Info列：当前使用的SQL语句；

对于出现问题的连接可通过 kill{id} 的方式关闭连接。

查询缓存

工作原理：
缓存SELECT操作的结果集和SQL语句
新的SELECT语句，先去查询缓存，判断是否存在可用的记录集。

缓存命中标准：
当前查询语句与缓存的SQL语句是否完全一样。
（简单认为存储了一个key-value结构，key为sql语句，value为sql查询结果集）。

查询缓存的坑
1.SQL语句前后空格不影响，中间空格数不同都会视为不同的SQL语句
2.当查询量超出缓存最大存储（可配置）时，缓存无法使用
3.使用系统函数时，不会进入到缓存之中
4.操作视图时，不会进入到缓存之中
5. 当表发生改变时，所有与此表有关的缓存都会失效。

查看缓存是否开启： Show variavles like ‘query_cache_type’;0 不开启 1 开启 2 按需开启

开启缓存:Windows下修改my.ini配置文件;Linux 修改/etc/my.cnf文件。
[mysqld]中添加：
query_cache_size = 20M
query_cache_type = ON

按需开启时：使用select sql_cache * from 表名；语句可添加查询缓存
查看缓存状态：show status like ‘Qcache%’;

查询优化处理

查询优化分为三个阶段：

解析sql：通过lex词法分析，yacc语法分析将sql语句解析成解析树；
预处理阶段：根据mysql的语法规则仅一步检查解析树的合法性。如：检查数据的表和列是否存在，解析名字和别名的设置，进行权限的验证。
查询优化器：优化器的主要作用就是找到最优的执行计划。

查看执行计划：

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

使用等价变化规则
覆盖索引扫描
子查询优化
提前终止查询
IN的优化
...

MySQL的查询优化器是基于成本计算的原则，他会尝试各种执行计划。如果数据抽样进行实验（随机的读取一个4K的数据块进行分析）。

MySQL执行计划参数：
ID：select查询的序列号，标识执行的顺序

1.	ID相同，执行顺序由上至下
2.	Id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
3.	Id相同又不同的两种情况同时存在，id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行；

select_type:查询的类型，主要是用于区分普通查询、联合查询、子查询等。

SIMPLE：简单的select查询，查询中不包含子查询或者union
PRIMARY：查询中包含子部分，最外层查询则被标记为primary
SUBQUERY：表示在select或where列表中包含了子查询
MATERIALIZED：表示where后面in条件的子查询
UNION：若第二个select出现在union之后，则被标记为union
UNION RESULT：从union表获取结果的select

table：查询涉及到的表，有三种情况

直接显示表名或者表的别名
<unionM,N>由ID为M,N查询union产生的结果
<subqueryN>由ID为N查询产生的结果

type：访问类型，SQL查询优化汇总一个很重要的指标，结果值从好到坏依次是：
system>const>eq_ref>range>index>ALL

system：表只有一行记录（等于系统表）,const类型的特例，基本不会出现。
const：表示通过索引一次就找到了，const代表primary key或者unique索引
eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质也是一种索引访问
range：只检索给定范围的行，使用一个索引来选择行
index：Full index Scan：索引全表扫描，把索引从头到尾扫描一遍
ALL：Full Table Scan，遍历全表找到匹配的行。

Possible_keys:查询过程有可能用到的索引
Key：实际使用的索引，如果为NULL，则没有使用索引
Rows：根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数
Filtered：指返回结果的行占需要督导的行（rows列的值）的百分比，越大越好。
Extra：十分重要的额外信息。包括：

Using filesort：mysql对数据使用一个外部的文件内容进行排序，而不是按照表内的索引进行排序读取
Using temporary：使用临时表保存中间结果；常见于order by或group by
Using index：相应的select 操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高。
Using where：使用了where过滤条件
Select tables optimized away:基于索引优化MIN/MAX操作或者MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。

注意：文件、临时表效率比较低。。。

4.插拔式的存储引擎，可按需选择
5.返回客户端

有需要做缓存的执行缓存操作
增量的返回结果（开始生成第一条结果是，MySQL就开始往请求方逐步返回数据  优点：MySQL服务器无需保存过多的数据，节省内存，用户体验好。）

.