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 使用用户自定义变量

©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值