MySQL查询及其简单优化阐述

gaoke

   先说一个简单的查询流程吧，一个简单的select语句，这个执行的过程，都发生了神马美妙的事儿？简单说来，无非是：         词法分析---->语法分析---->检查权限---->生成解析树---->解析选择路径---->选择存储引擎---->返回结果，当然事实远非如我说那般简单，我们先来看一副，然后再根据其图示细说查询，图如下：



         根据上图，我们可以看到，服务接收到用户请求后，先去MySQL缓存中查询，MySQL的缓存是以 key-value的形式保存的，其中key是查询语句的hash码，value是其查询结果，当服务器接收到用户的请求语句，就把其语句进行hash计算，然后用其跟缓存中的key比较，hash码比较的速度是巨快的，需要注意的是，缓存中key的hash码是有索引的。
         顺便说一下，MySQL支持大小写的书写形式，但是由于大写和小写的hash码是完全不同的，也就是说，select * from test;和SELECT * FROM test;在缓存中的hash码是完全不同的。因此笔者这里做第一点声明：统一书写格式，要么大写，要么小写。好在许多MySQL的管理工具的语句都是固化的。
         试想一种情况，如果服务的写操作和读操作几乎一样的情况下，查询缓存意义大么？假如刚查询完的数据很快就被修改了，然后此查询缓存就失效了，然后下次查询进来，还是需要先去缓存找，但是缓存又没有，那么此时这种场景，缓存反而是一种多余的开销，所以，没有最好的方案，只有最合适的。
         另外，缓存的存在，意味着每个请求都会先去缓存进行查询的，如果缓存中的有效数据只有10%，而90%都查不到，但是每个请求却都要来查一遍，那么这缓存到底带来了性能提升么？因此，我们需要对缓存的数据做命中率(hit rate)评估，简单说来，就是对经常会查到的数据(数据热区)做缓存，还有超过一定体积的不做缓存，也就是说，我们要分析并定义允许缓存的数据有哪些。一般说来，40%以上的命中率都是值得的，据说淘宝的web缓存命中率达到95%以上。（请注意，是据说。）
         查询缓存说的有点罗嗦哈.下面说下一步，就是当查询请求在缓存中获取不到结果时，就交给下个模块---->解析器：解析器对请求的语句进行词法分析和语法分析，词法分析就是根据其内部规则识别单个单词，而语法分析则是在词法分析的基础上将单词序列组成语法短句，最后生成解析树，交给预处理器，预处理器会检查权限后再生成树提交给查询优化器。
查询优化器，其目的就是选择代价最少查询执行路径。MySQL 中，“代价”的单位是“随机读一个4kb的数据也所消耗的资源。”但是MySQL也不是总是选用这种方式预测。常用的优化方式静态和动态两种：静态优化笔者理解为一次把准备工作作为，然后多次去按准备工作执行即可；而动态则是，准备着执行着一起。
         需要注意的是，如果在SQL语句的时候知道MySQL的优化过程，能直接写出最优语句，就能在某种意义省去部分时间。优化器的优化一般分以下几种：join的顺序优化，有可能语句中的join顺序不是最优的、转换outer join为inner join、优化表达式的规则（例： 4=4 AND b<4转为b<4等）、count(*),min,max等。
         查询优化刚才笔者已经说到了动态优化和静态优化，显然，其中动态优化自然就不必说了，因为其优化的同时就已经开始调用查询执行引擎了，而静态的则把准备工作完成后提交给查询执行计划，这个计划可能就是个队列，然后调用查询执行引擎，来通过API调用相应的存储引擎(MyISAM,InnoDB….)来实现数据管理能力，对磁盘数据才进行操作，至此，数据终于被找到。
         但是，请注意，还没结束呢，查询执行引擎在给结果返回给客户时，如果检测到此结果可以被缓存，还给给缓存扔一份。到此，才是一次完整的查询执行过程。很麻烦有木有。

         由于历史缘故，MySQL在最初期的设计，并未考虑到要大规模的应用到生成服务器上，其实貌似很多产品，不仅是IT产业，貌似很多东西都是无心插柳的，反而火了，扯远了，所以说迄今为止，MySQL对于CPU的多核的并发处理应用能力依然乏善可陈。现在貌似好像能支持16颗多了吧，但是，尽管如此，它的每个SQL语句都只能在一个CPU上运行。
         这意味着，哪怕你有16个CPU，只要你的一个语句写的很复杂，它依然只在一个CPU是运行，它没办法把一个语句让多个CPU上并行处理，这是天生了缺陷，俗称硬伤。因此，笔者建议：因此尽量写简单语句，写多个.。一个语句拆分成多个语句，其可以良好的并行处理。当然，如果业务需要不得不把一个查询语句里面写N个子查询的话，当笔者没说好了。
MySQL的查询主要分以下几类：
单表查询：简单查询，执行一个表上的查询，一个语句
联结查询：简单查询，将多个表联结起来一起查询，多个表，一个语句
子查询：复杂的查询，有多个查询语句嵌套，可以对于一个或多个表，但其中可能带有多种计算(计算，排序等)，其可以是单表，可以是联结查询。
联合查询：将多个查询语句的结果合并起来
         不管哪种查询，一般来说，总是先对表进行一个全表扫描，但是有没有想过，数据少还好，那么如果数据量很大的话，一个扫描，最快也得几秒钟，伤不起的时间，对吧？那么，要想提高查询速度怎么办？
         所以，我们又不得不使用索引，索引是神马？索引其实就是按照某种特定的格式，将特定的字段重新组合起来的数据结构。可以是简单索引(建立在一个字段上)，也可以是组合索引(多个字段上)。简单理解，就是书的目录。
         通过索引，我们也许可以定位所要获得数据的大概位置，但是如果我要找的数据，是某个关键字怎么办？这个关键索引里面也没有啊，怎么办？因此，创建合适的索引是非常关键的，我们可以通过分析，找出数据访问比较密集的存储区域创建索引，有了合适的索引，我们就能优化查询。

0

我妈常说,我们家要是没有电话就不会这么穷。

阿斯顿阿斯顿

勃起不是万能的，但不能勃起却是万万都不能的!

zidong

我是个凑数的。。。

yaomint

关羽五绺长髯，风度翩翩，手提青龙偃月刀，江湖人送绰号——刀郎。

ibaobei

我不在江湖,但江湖中有我的传说。

永夜海

穿别人的鞋，走自己的路，让他们找去吧。