MySQL查询优化详解

mysql 查询优化-查询执行的路径

1. mysql 客户端/服务端通信
2. 查询缓存
3. 查询优化处理
4. 查询执行引擎
5. 返回客户端

mysql 客户端/服务端通信

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

全双工：双向通信，发送同时也可以接收

半双工：双向通信，同时只能接收或者是发送，无法同时做操作

单工：只能单一方向传送

半双工通信：
在任何一个时刻，要么是有服务器向客户端发送数据，要么是客户端向服务端发送数据，这两个动作不能同时发生。所以我们无法也无需将一个消息切成小块进行传输

特点和限制：
客户端一旦开始发送消息，另一端要接收完整个消息才能响应。
客户端一旦开始接收数据没法停下来发送指令。

Mysql客户端与服务端通信-查询状态

对于一个mysql连接，或者说一个线程，时刻都有一个状态来标识这个连接正在做什么
查看命令

show full processlist / show processlist

说明	截图
当前控制台client链接	[外链图片转存失败(img-r63Om1MD-1565623944048)(…/…/images/optimize/mysql/1544227223850.png)]
本地Navicat链接	[外链图片转存失败(img-juSnTsZf-1565623944049)(…/…/images/optimize/mysql/1544227253819.png)]

https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html (状态全集)

Command	Description
Sleep	线程正在等待客户端发送数据
Query	连接线程正在执行查询
Locked	线程正在等待表锁的释放
Sorting result	线程正在对结果进行排序
Sending data	向请求端返回数据

可通过kill {id}的方式进行连接的杀掉

查询缓存

工作原理：

缓存SELECT操作的结果集和SQL语句；

新的SELECT语句，先去查询缓存，判断是否存在可用的记录集。

判断标准：

与缓存的SQL语句，是否完全一样，区分大小写 (简单认为存储了一个key-value结构，key为sql，value为sql查询结果集)

查询缓存相关的系统变量：

通过show variables like '%query_cache%';

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.05 sec)

mysql> 

have_query_cache　　表示这个mysql版本是否支持Query Cache。

query_cache_limit　　 允许Cache的单条Query结果集的最大容量，默认是1MB，超过此参数设置的Query结果集将不会被Cache。

query_cache_min_res_unit　　设置Query Cache中每次分配内存的最小空间大小，也就是每个Query的Cache最小占用的内存空间大小。

query_cache_size　　设置Query Cache所使用的内存大小，默认值为0，大小必须是1024的整数倍，如果不是整数倍，MySQL会自动调整降低最小量以达到1024的倍数。

query_cache_type　　控制Query Cache功能的开关，可以设置为0(OFF)，1(ON)和2(DEMAND)三种：0表示关闭Query Cache功能，任何情况下都不会使用Query Cache；1表示开启Query Cache功能，但是当SELECT语句中使用的SQL_NO_CACHE提示后，将不使用Query Cache；2(DEMAND)表示开启Query Cache功能，但是只有当SELECT语句中使用了SQL_CACHE提示后，才使用Query Cache。

query_cache_wlock_invalidate　　控制当有写锁加在表上的时候，是否先让该表相关的Query Cache失效，1(TRUE)，在写锁定的同时将使该表相关的所有Query Cache 失效。0(FALSE)，在锁定时刻仍然允许读取该表相关的Query Cache。

my.cnf文件中配置参数信息

query_cache_type=1
query_cache_size=128M
query_cache_limit=1M

---

show status like ‘Qcache%’ 命令可查看缓存情况

mysql> show status like 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_b