查询性能优化

低效查询分析步骤

• 检索大量超过需要的数据（访问太多行或者列）
• 确认MYSQL服务器层是否在分析大量查过需要的数据

应用where条件

• 在索引中使用WHERE条件过滤不匹配的记录，存储引擎中完成
• 使用索引覆盖扫描（Extra列中出现Using index）返回结果，直接从索引中过滤不需要的记录并返回命中结果。MYSQL服务器层完成，无需回表
• 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。在MYSQL服务器层完成，MYSQL需要先从数据表读出记录然后过滤。

重构查询方式

• 一个复杂查询还是多个简单查询
• 切分查询
• 分解关联查询
  优势：1.让缓存效率更高
  2.将查询分解后，执行单个查询可以减少锁的竞争
  3.在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩张
  4.可以减少冗余记录的查询
  5.更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MYSQL的嵌套循环关联，某些场景下哈希关联的效率要高很多

查询执行基础

• 客户端发送一条查询给服务器
• 服务器先检查查询缓存，如果命中了缓存，立刻返回存储在缓存中的结果。否则进入下一阶段
• 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
• MYSQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
• 将结果返回给客户端

MYSQL客户端/服务器通信协议

-MYSQL客户端和服务器之间的通信协议是半双工（客户端->服务端或服务端->客户端同时只能有一个发生）

查询状态

对于一个MYSQL连接或者一个线程，任何时刻读有一个状态，该状态表示了MYSQL当前正在做什么。

查询状态命令： show full processlist

• Sleep
  线程正在等待客户端发送新的请求

• Query
  线程正在执行查询或者正在讲结果发送给客户端

• Locked
  在MYSQL服务器层，该线程在在等待表锁或行锁

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

• Sorting result
  线程正在对结果集进行排序

• Sending data
  线程在多个状态之间传送数据，或者生成结果集，或者在向客户端返回数据

获得MySQL计算的当前查询成本
select * from test;
show status like 'Last_query_cost'

MYSQL能够处理的优化模型

• 重新定义关联表的顺序（笛卡尔积）

• 将外连接转化为内连接

• 使用等价变换规则（5==5 and a>5改写为a>5）

• 优化COUNT（）、MIN（）、MAX（）
  索引和列是否可为空通常卡伊帮助MYSQL优化这类表达式

• 预估并转化为常数表达式
  当MYSQL检测到一个表达式可以转换为常数的是否，就会一直把该表达式作为常数进行优化处理

• 覆盖索引扫描
  当索引中的列包含所有查询中要使用的列的时候，MYSQL就可以使用索引返回需要的数据，而无需查询对应的数据行

• 子查询优化
  MYSQL在某些情况下可以将子查询转换哟中效率更高的形式，从而减少多个查询多次对数据进行访问

• 提前终止查询
  在发现已经满足查询需求的时候，MYSQL总是能够立刻终止查询。（Limit）

• 等值传播
  如果两个列的值通过等式关联，那么MYSQL能够把其中一个列的WHERE条件传递到另一列上

select film.film_id from sakila.filn inner join sakila.film_actor using(film_id) where film.film_id>500

这里film_id字段进行等值关联，MYSQL知道这里的WHERE子句不仅适用于film表，而且对film_actor表同样适用。

• 列表IN()的比较
  在MYSQL中将IN（）列表中的数据先进行排序然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(logn)复杂度的操作，等价地转换成OR查询的复杂度为O(n)，对于IN()列表中有大量取值的时候,MYSQL的处理速度将会更快