MySQL的SQL优化，这个是一个常见面试题，也是提升我们系统性能很重要的一个环节，所以我们有必要把这样一个知识理解透彻。相信这个图，我们很多同学都看见过。
image
可能有的同学还不太了解，研究整个执行计划的文章其实也比较多，相信大概谷歌一下一大堆。这里我先大致解释一下。MySQL在执行一个SQL语句的时候，会经过以下几个步骤：
-
客户端发送一条查询给服务器（JDBC或者ODBC）；
-
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段；
-
服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
-
MySQL根据优化器生成的执行计划，再调用存储引擎的API来执行查询；
-
将结果集返回给客户端。
首先大家在回答面试官的这个问题的时候，经常有人采坑，背得滚瓜烂熟，稍微问一下，有很多人就答不上来，我一般问下面两个：
-
这5个步骤是一定都会走的吗？有哪些情况下可能有不同的查询路径呢？
-
你自己在数据库工具（例如MySQL WorkBench或navicat等）执行一个SQL语句的时候，会经过哪些步骤？
这两个很小的问题，很多人就开始犯嘀咕了，那么我面试的时候一般就断定是网上看资料得来的，这个环节得分7-8分。
问题1其中的查询缓存，其实就不是在什么情况下都会走到的，很明显的一个问题就是MySQL其实默认情况下是关闭的。今天我们就重点给大家深入剖析的是整个执行过程中的第一个步骤 - 查询缓存。
image
首先重点强调的是，第一个步骤中的查询缓存，MySQL其实是默认关闭的，需要我们手动添加MySQL的缓存配置，MySQL才会先去查询缓存的。
vi /etc/my.cnf
在[mysqld]这个集合下面添加一行query_cache_type=1，query_cache_type这个key有三个值：0，1，2，意义分别如下：
| 值 | 解释 |
|---|---|
| 0(OFF) | 关闭 Query Cache 功能，任何情况下都不会使用 Query Cache |
| 1(ON) | 开启 Query Cache 功能，但是当 SELECT 语句中使用的 SQL_NO_CACHE 提示后，将不使用 Query Cache |
| 2(DEMAND) | 开启 Query Cache 功能，但是只有当 SELECT 语句中使用了 SQL_CACHE 提示后，才使用 Query Cache |
我们先设置为1，修改后重启MySQL。（最好在自己的本机先做实验）
image
开启完了之后，在MySQL的命令行中输入一下命令
>show status like 'Qcache%';
执行几次一模一样的SQL语句，如果Qcache_queries_in_cache值加1了，说明缓存中多了一个SQL缓存了。Qcache_inserts后面的value的值加了1说明往缓存里放了一个SQL缓存，Qcache_hints增加了1表示查询走了缓存。（你也可以在开启之前对比一下）
SELECT * FROM user ORDER BY id DESC LIMIT 500;
image
参数含义参考以下表格：
| 参数 | 解释 |
|---|---|
| Qcache_free_blocks | 目前还处于空闲状态的 Query Cache 中内存 Block 数目 |
| Qcache_free_memory | 缓存的内存大小 |
| Qcache_hits | Query Cache 命中次数 |
| Qcache_inserts | 向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数 |
| Qcache_lowmem_prunes | 当 Query Cache 内存容量不够，需要从中删除老的 Query Cache 让渡给新的 Cache 对象，这个过程的使用的次数 |
| Qcache_not_cached | 没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL |
| Qcache_queries_in_cache | 目前在 Query Cache 中的 SQL 数量 |
| Qcache_total_blocks | Query Cache 中总的 Block 数量 |
每次做一个新的查询的时候，Qcache_inserts的值会加1，同样的SQL再查询，命中了缓存，Qcache_hits就会加1。
需要注意的是，即使你开启的缓存，在很多情况下缓存是不生效的。
-
SQL语句需要一模一样，才能命中缓存，多一个空格也不行；
-
查询语句中含有动态的内容也不会命中缓存；
-
查询不涉及任何表的sql语句不会走缓存；
-
视图查询不会走缓存；
1 先看第一个，我们先执行：
SELECT * FROM user WHERE id = 1;
image
然后我们在等于号后面增加一个空格看一下Qcache_hints和Qcache_inserts值的变化情况。
SELECT * FROM user WHERE id = 1;
image
可以看到Qcache_inserts值增加了1而Qcache_hints值没有变化，说明查询又重新走了一次执行计划->查询->缓存查询结果的过程，并没有直接去拿缓存里的数据。
2 如果你的SQL中含有动态语句，比如curdate()、curtime()，这种SQL语句MySQL是不会缓存的。
select curdate(), name from user limit 100;
3 查询没有涉及到任何表的情况，也是不会放入缓存的
select 'abc';
4 如果你查询的是视图，也是不会放入缓存的。这个留给大家自己去实验。
最后补充一个问题，为什么MySQL的缓存这么好，可以提高查询性能，为什么MySQL不默认打开？
原因其实缓存这个东西，也是一把双刃剑，不是在什么时候都开启都是好的。比如你的应用，插入很多，相同的查询很少，那么就明显不适合开启缓存。只有在你的应用相同的查询特别多，插入或更新不多的情况，才适合开启这个缓存的。比如一个博客网站，编辑博客的人很少，大部分都是查询，那么这种情况开启缓存就比较合适，当然你会讲博客这个比较适合使用NoSQL，那么这个就是另外一个话题了，我们就暂不讨论。
如果关于MySQL的查询缓存，你可以在面试官面前回答到这个程度，那么相信这个环节你可以得到高分，给面试官一个你是一个真的会深入到底层去看问题的一个人这样的印象。当然希望大家在看完我的文章后还需要去自己实践一下，这样才会有更深的体验。
网友评论