MySQL笔记（查询优化）

 

查询性能优化

 

基本原则：优化数据访问

一些典型的错误包括：提取了超过需要的行、多表联接时提取所有列或提取所有的列（select * 可能会造成覆盖索引这样的优化手段失效）

MySQL中，最简单的开销指标包括：

1.执行时间

2.检查的行数

3.返回的行数

---通过使用索引，在explain时可以减少处理的行数

---使用覆盖索引来避免访问行

 

---

 

-------------------------------------------------------------------------------

 

重构查询

1,使用分治法来处理查询，每次只执行查询的一小部分，以减少受影响的行数

2,分解联接，将一个多表查询分解成多个单个查询，然后在应用程序端实现联接操作。

 

----------------------------------------------------------------------------------

MySQL中一个基本查询过程包括：

1.客户端发送查询到服务器

2.检查查询缓存

3.对查询进行解析、预处理、优化，生成查询计划

4.执行器调用存储引擎进行查询

5.返回查询结果。

 

----------------------------------------------------------------------------------

MySQL通信协议

1.半双工，服务器不同发送和接受。

2.当服务器发送响应时，客户端必须接受完整的结果集。

3.查看线程的查询状态：SHOW FULL PROCESSLIST;在command列显示其状态：

——休眠，等待客户端新查询

——查询，正在执行查询或往客户端发送数据

——锁定

——分析统计

——等等。。。

在繁忙的服务器上，通过查看查询状态，可以比较容易的找到问题所在。

------------------------------------------------------------------------------------------------

查询缓存

1.大小写敏感的哈希查找。

2.在返回结果前需要检查权限。

 

-------------------------------------------------------------------------------------------------

 

查询优化

1.MYSQL解析器对查询进行解析，将查询进行分解，构造解析树。

2.预处理器，检查解析树，如检查表和列是否存在，再检查权限。

3.查询优化器：从通过1、2检查后的解析树中，找到最优的执行方式

——预测不同计划开销来选择,单位是一次对4KB大小的页面进行的随机读取。

在一次select后，可以通过以下语句查看随机读取的次数

mysql> SHOW STATUS LIKE 'last_query_cost',开销来自对统计值的估计，但不考虑缓存因素。

4.优化并不总是准确的，因为无法对统计数据进行精确更新，并且其优化会遵循一套原则。

5.两种优化方案：

1).静态优化，简单的探测解析树，例如通过代数化法则把WHERE子句转换成相等的形式，静态优化和值无关，即使用不同的参数重新执行查询也不会改变。

2).动态优化，根据上下文而定，和很多因素有关，如where子句中的值和索引中的行数。

MySQL只进行一次静态优化，每次查询时都会进行动态优化

 

6.MySQL能够处理的优化类型包括

1)对联接中的表重新排序

2）将外联接转换成内联接

3）代数等价法则，简化并规范表达式，如移除不可能的限制或条件，例如5=5 AND a>5会转换成a>5

4)优化COUNT()、MIN()、MAX(),例如没有where子句的COUNT(*)在MYISAM中会直接使用其保留的行数精确值,而在B树索引中，其第一行和最后一行分别是MIN和MAX值。

5)计算和减少常量表达式

6）覆盖索引

7）优化子查询，将其转换成效率更高的形式

8）早期终结，比如limit或者查询条件是一个不可能的条件。

9）相等传递，MySQL能辨认一个查询中有两个列相等的情况

例如：SELECT film_id FROM film INNER JOIN film_actor USING(film_id) WHERE film_id >500;

因为join的film_actor和film的film_id值相等，因此，where子句可以被应用到两个表中。

10)比较IN（）中的数据，对IN中的数据先进行排序，再用二分法查找某个值是否在列表中。