查询优化

 

 

 

1.查询主流程：　

a.客户端发送一条查询给服务器

b.服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段。

c.服务器进行SQL解析，预处理，再由优化器生成对应的执行计划，

d.mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询。

e.将结果返回给客户端。

 

2.MySQL连接状态：

对于一个MYSQL连接线程，任何时刻都有一个状态，在一个查询的周期中，状态可能会变化很多次，这些状态有：

sleep: 线程正在等待客户端发送新的请求

query:线程正在执行查询或者正在将结果发送给客户端

locked:在mysql服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如innoDB的行锁，并不会体现在线程状态中。对于myisam来说这是个比较典型的状态，但在其他没有行锁的引擎也经常会出现。

analyzing and statistics:线程正在收集存储引擎的统计信息，并生成查询的执行计划。

copying to tmp table [on disk]:线程正在执行查询，并且将结果集复制到一个临时表，这种状态一般要么是在做 group by 操作，要么是文件排序操作，或者是union操作。如果这个状态后面还有 on disk 标记，那表示mysql正在将一个内存临时表放到磁盘上。

sorting result:线程正在对结果集进行排序

sending data:这表示多种情况，线程可能在多个状态之间传送数据，或者在生成结果集，或者在客户端发送请求。

 

 3.解决查询效率低方法

a.确认应用程序是否在检索大量的超过需要的数据，有时候是访问了太多的行，有时候是访问了太多的列 

b.确认mysql服务器是否在分析大量超过需要的数据航行。mysql能够使用下面三种方式应用where条件，从好到坏依次是

1.在索引中使用where条件来过滤不匹配的记录，这是在存储引擎层完成的

2.使用索引覆盖扫描来返回记录，直接在索引中过滤不需要的记录并返回命中的结果，这是在mysql服务器层完成的，但无需再回表查询记录

3.从数据库中返回数据，然后过滤不满足条件的记录，这也是在mysql 服务器层完成的。

 

c如果发现查询需要扫描大量的额数据但只返回少数的行，可以如下优化

1.使用索引覆盖扫描

2.改变库表结构，例如使用单独的汇总表

3.重写这个复杂的查询

 

4.重构查询的几种方法

a.一个复杂查询 or 多个简单查询：设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。

b.切分查询：有时候对于一个大查询我们需要“分而治之”，将大查询切分为小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

c分解关联查询：

　　　　select * from tag 

　　　　　　　join tag_post on tag_post.tag_id = tag.id

　　　　　　　join post on tag_post.post_id = post.id

　　　　where tag.tag = 'mysql'

　　　　可以分解成下面这些查询来代替：

　　　　> select * from tag where tag = 'mysql'

　　　　> select * from tag_post where tag_id = 1234

　　　　> select * from post where post_id in (123, 456, 567, 9098, 8904)　　　　

　　优势：

　　　　　　让缓存的效率更高。

　　　　　　将查询分解后，执行单个查询可以减少锁的竞争。

　　　　　　在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。

　　　　　　查询本身效率也可能会有所提升。

　　　　　　可以减少冗余记录的查询，

　　　　　　更进一步，这样做相当于在应用中实现了哈希关联，而不是使用mysql的嵌套循环关联。

 

 

5.MYsql的一些优化类型

重新定义关联表的顺序，命中率高的表可能被先查询

将外链接转换为连接

使用等价变换规则

优化Count（） MIN（） MAX（）：

预估并转换为常数表达式

覆盖索引扫描

子查询优化

提前终止查询

等值传播

列表IN（）的比较

排序优化，尽可能避免排序或者对大量数据进行排序，如果要排序的数据量小于“排序缓存区”，使用内存进行快排，如果内存不够，先将数据分块，每个分块使用快排后将结果存放在磁盘上，最后将各排好序的块进行合并

limit优化，要么在应用中限制分页的数量，要么根据索引覆盖扫描找出对应的行索引，然后在根据索引到数据库中查询具体的记录

union优化，除非真的需要消除重复的行，否则一定要使用union all。因为如果没有all关键字，mysql会给零时表加上distinct选项，这回导致对整个零时表的数据做唯一性检查

如果查询的两个表大小相当，那么用in和exists差别不大。 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：