MySQL查询优化详解

客户端与MySQL服务器的查询通信步骤如下

1. 客户端与服务器进行通信
2. SQL语句查询MySQL服务的缓存(如果服务器开启了缓存)
3. 解析器将SQL语句解析为解析树
4. 预处理器判断解析树是否符合规范
5. 查询优化器对SQl进行优化处理
6. 查询执行引擎查询数据
7. 返回客户端

MySQL执行路径如下图：

MySql服务端和客户端状态查询

 对于一个MySQL连接，或者说一个线程，时刻都有一个状态标识它在做什么

  查看命令：show full processlist / show processlist

1. Sleep 线程正在等待客户端发送数据
2. Query 线程正在执行查询
3. Locked 线程正在等待表锁的释放
4. Sorting result 线程正在对结果进行排序
5. Seedding Data 向请求端返回数据

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

MySQL查询缓存

query_cache_type

值：0    --不启用查询缓存，默认值

值：1    --启动查询缓存，只要符合缓存规范的，查询的SQL语句和结果集都会缓存起来

供给其他客户端使用

值：2    --启用查询缓存，只要查询语句中添加了参数SQL_CACHE,且符合缓存查询要求，

客户端的查询语句和结果集就会缓存起来

   query_cache_size

允许设置query_cache_size的值设置为40k,默认1M,推荐使用64M/128M

    query_cache_limit

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

     show status like 'Qcache%'

该命令可以查看缓存情况

   工作原理：

             缓存Select操作的集和SQL语句

             新的SELECT语句，先去查询缓存，判断是否有可用的结果集

       判断标准：

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

       (简单认为存储了一个key-value结构，key为SQL，value为SQL语句的结果集)

  不会被缓存的情况

1. 查询语句中有不确定的数据时，则不会被缓存，如自定义函数NOW(),存储变量
2. 当查询的结果大于query_cache_limit设置的值，则不会被缓存
3. 对于Innodb引擎来说，当一个语句修改了表中的数据，在事务提交之前，所用的查询操作都无法被缓存
4. 查询的是系统表
5. 查询的表不存在

MySQL默认关闭缓存

1. 在查询之前必须检查是否命中缓存，浪费计算资源
2. 如果查询可以被缓存，那么执行完查询，MySQL会发现将这个查询和结果集写入缓存，消耗系统性能
3. 针对表的写入和更新，表的所有缓存都会失效
4. 查询缓存很大或者碎片很多时，这个会给系统带来很大的负担

缓存查询使用场景

       以读位置的业务，数据生成之后不常改的数据