mysql查询性能优化

优化查询，实际上就是优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行的更快。查询性能低下最基本的原因是访问的数据太多。对于低效的查询，可以通过下面两个步骤来分析：

• 确认应用程序是否在检索大量超过需要的数据。
• 确认mysql服务器层是否在分析大量超过需要的数据行。

1 优化数据访问

1.1 是否向数据库请求了不需要的数据

• 查询不需要的记录。最简单有效的解决方法就是加上limit。

• 多表关联时返回全部列。

• 总是取出全部列。

• 重复查询相同的数据。

1.2 mysql是否在扫描额外的记录

最简单的衡量查询开销的三个指标：响应时间，扫描的行数，返回的行数。

explain语句中的type列反应了访问类型，如果查询没有办法找到合适的访问类型，那么解决的最好办法通常是增加一个合适的索引。

一般mysql能够使用如下三种方式应用where条件，从好到坏依次为：

在索引中使用where条件来过滤不匹配的记录。这在存储殷勤层完成。

使用索引覆盖扫描（在extra列中出现了using index）来返回记录，直接从索引中过滤不需要的记录并返回命中结果。在mysql服务器层完成。

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

2 重构查询的方式

2.1 使用一个复杂查询还是多个简单查询

如果一个查询能够胜任时还写成多个独立查询是不明智的。

2.2 切分查询

将一个大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

2.3 分解关联查询

可以时缓存的效率更高，减少锁的竞争，更容易对数据库进行拆分，更容易做到高性能和可扩展，查询本身效率也可能会有所提升，减少冗余记录的查询，相当于在应用中实现了哈希关联，而不是使用mysql的嵌套循环关联。

3 查询执行的基础

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

• 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。

• 服务器端进行sql解析、预处理，再由优化器生成对应的执行计划。

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

• 将结果返回给客户端

3.1 mysql客户端/服务器通信协议

“半双工”：在任何时刻，要么由服务器向客户端发送数据，要么由客户端向服务器发送数据，两个动作不能同事发生。

• 查询状态

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

Query：正在执行查询或者正在将结果发送给客户端

Locked：该线程正在等待表锁

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

Copying to tmp table[on disk]：线程正在查询，并且将其结果集都复制到一个临时表中，这种状态一般要么做group by操作，要么时文件排序操作，或者时union操作。

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

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

3.2 查询缓存

3.3 查询优化处理

• 语法解析器和预处理

解析sql语句，检查是否合法，验证权限。

• 查询优化器

重新定义关联表顺序、将外链接转化为内连接、使用等价变化规则、优化count(),min(),max()、预估并转化为常数表达式、覆盖索引查询、子查询优化、提前终止查询、等值传播、列表in()的比较。

• 数据和索引的统计信息

mysql查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息，包括：每个表或存储引擎有多少个页面，每个表的每个索引的基数是多少，数据行和索引长度，索引的分布信息等。

• 关联查询

mysql对任何关联都执行嵌套循环关联操作，即mysql先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，直到找到所有表中匹配的行为止。

• 执行计划

mysql的执行计划总是一颗左测深度优先的树。

• 关联查询优化器

评估不同的关联顺序的成本来选择一个代价最小的关联顺序。

• 排序优化

先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。

在关联查询的时候如果需要排序，如果order by子句的所有列都来自关联的第一个表，那么mysql在关联第一个表的时候就进行文件排序，explain结果可以看见extra字段会有“using filesort”。除此之外，mysql先将关联结果存放在一个临时表中，然后在所有的关联都结束后，在进行文件排序。

3.4 查询执行引擎

3.5 返回结果给客户端
服务器处理完最后一个关联表，开始生成第一条结果时，mysql就可以开始向服务端逐步返回结果集了。

4 mysql查询优化器的局限性

4.1 关联子查询

mysql的子查询实现的非常糟糕，尤其时where条件中包含in()的子查询。一般建议使用做外连接重写该查询，以代替子查询。

4.2 union的限制

有时，mysq无法将限制条件从外层下推到内层，如果希望union的各个子句能够根据limit只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要union的各个子句中分别使用这些子句。

(select first_name,last_name from actor order by last_name)

unoin all

(select first_name,last_name from customer order by last_name) limit 20

这条查询会把actor中的所有记录和customer中的所有记录存放在一个临时表中，再从临时表中取出前20条。

修改(select first_name,last_name from actor order by last_name limit 20)

unoin all

(select first_name,last_name from customer order by last_name limit 20) limit 20

现在中间临时表中只有40条记录。从临时表中取出数据的顺序并不是一定的，如果先要获得正确的顺序，还要加上全局的order by和limit操作。

4.3 索引合并优化

4.4 等值传递

如果in()列表非常大，会导致优化和执行都会变慢（很少碰见）

4.5 并行执行

mysql无法利用多核特性并行执行查询

4.6 哈希关联

mysql不支持哈希关联，但可以建立哈希索引来实现哈希关联。

4.7 松散索引扫描

mysql不支持松散索引扫描

4.8 最大值和最小值优化

mysql对min()和max()查询优化做的不好

4.9 在同一张表上查询和更新

mysql不允许对同一张表同事进行查询和更新。

5 查询优化器提示(hint)

建议直接阅读mysql官方手册

6 优化特定类型的查询

6.1 优化count()查询

MyISAM的count()函数在没有任何where条件下的count(*)总是非常快的。

简单优化：Select (select count(*) from city) – count(*) from ciry where id<=5

使用近似值：explain出来的优化器估算的行数就是不错的近似值。

快速、精确和实现简单，三者永远只能满足其二，必须舍掉其中一个。

6.2 优化关联查询

确保on或者using子句中的列上有索引

确保任何的group by和order by中的表达式只涉及到一个表的列，这样mysql才有可能使用索引来优化这个过程。

关联语法、运算符优化级等其他可能会发生裱花的地方。

6.3 优化子查询

6.4 优化group by和distinct

使用索引来优化。当无法使用索引时，group by可以使用临时表或者文件排序来分组，如果需要对关联查询做分组，并且时按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高

6.5 优化limit分页

尽可能使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作在返回所需的列。

6.6 优化sql_calc_found_rows

分页时，另一个技巧是在limit语句中加上sql_calc_found_rows提示，这样可以获得去掉limit以后满足条件的行数，因此可以作为分页的总数。

6.7 优化union查询

除非确实需要服务器消除重复的行，否则一定要使用unoin all，这一点很重要，如没有all，mysql对整个临时表数据做唯一性检查。

6.8 使用用户自定义变量