mysql查询性能优化的方法是什么

这篇文章主要讲解了“MySQL查询性能优化的方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“mysql查询性能优化的方法是什么”吧！

成都创新互联公司是一家专业提供蒙山企业网站建设,专注与成都网站制作、做网站、H5开发、小程序制作等业务。10年已为蒙山众多企业、政府机构等服务。创新互联专业网站设计公司优惠进行中。

1. 为什么查询速度会变慢

真正重要的是响应时间，如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么减少子任务的执行次数，要么让子任务执行地更快。通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划、执行，并返回结果给客户端。其中执行可以认为是整个生命周期中最重要的阶段，其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组。

在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络、CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换和系统调用。

2. 慢查询基础：优化数据访问

查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量数据，但这并不常见。大部分性能低下的查询都可以通过减少访问数据量的方式进行优化。对于低效的查询，可以通过下面两个步骤来分析：

确认应用程序是否检索了大量超过需要的数据。这通常意味着访问了太多了行，但有时候可能是访问太多的列；确认MySQL服务器层是否在分析大量超过需要的数据行。

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

有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢掉。这会给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存资源。

例如查询不需要的记录、多表关联返回全部行、总是取出全部列（覆盖索引难以优化，额外增加I/O、内存和CPU消耗）、重复查询相同的数据。

2.2 MySQL是否在扫描额外的记录

在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL，最简单的衡量查询开销的三个指标如下：

• 响应时间

• 扫描的行数

• 返回的行数

其中响应时间包括服务时间和排队时间，服务时间是指数据库处理这个查询真正花了多长时间，排队时间是指服务器因为等待某些资源没有真正执行查询的时间--例如I/O和锁等待；理想情况下扫描的行数和返回的行数应该是相同的，但实际情况并非如此，例如在做关联查询时，服务器必须要扫描多行才能生成结果集中的一行，扫描的行数对返回的行数的比率通常很小；

3. 重构查询的方式

3.1 一个复杂查询还是多个简单查询

有时候将一个大查询分解为多个小查询是很有必要的，MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。

3.2 切分查询

分而治之，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。例如定期清理大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。需要注意的是，如果每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

3.3 分解关联查询

很多高性能的应用都会对关联查询进行分解。用分解关联查询的方式重构查询有如下的优势：让缓存效率更高，可以使缓存更好的工作；执行单个查询可以减少锁的竞争；在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展；单个查询本身效率也可能会有所提升；有利于减少冗余记录的查询，在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据，从这点看，这样的重构还可能会减少网络和内存的消耗；

4. 查询执行的基础

弄清楚MySQL是如何优化和执行查询，有利于优化查询，很多查询优化工作实际上就是遵循一些原则让优化器能够按照预想的合理的方式运行。

                                

                                                                 图-查询执行路径

5. MySQL查询优化器的局限性

5.1 优化COUNT查询

COUNT()作用是统计某个列值的数量及统计行数，也可以统计结果集的行数。在统计列值时要求列值非空（不统计NULL），如果在括号中指定了列或者列的表达式，则统计的是这个表达式有值的结果数。

count(expr)中的expr可以是col1number还可以是子查询和case when语句等等；
count(expr)中的expr除了是case when语句，其余的都要加or null才能统计出正确的值，即便是子查询也要加or null。

常见的错误用法是在括号内指定了列却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用count(*)，这样写意义清晰，性能也会很好。可以通过条件反转，例如：

select count(*) from world.city where id > 5;
#条件反转，查询优化阶段其中的子查询将直接作为常数，可以大大减少需要扫描的行数
select (select count(*) from world.city) -count(*) from world.city where city <=5

同时在某些业务场景并不完全要求精确的COUNT值，此时可以用近似值来代替。

#结果集的“rows”字段的值就是整个表的记录数,explain的数字不保证准确,虽然大部情况下它和实际记录数字是一致的，但是成本很低，并不需要真正地执行查询
explain select * from ehr_post_station_adjust

通常来说，COUNT()都需要扫描大量的行(意味着要访问大量数据)才能获得精确的结果，因此很难优化。除了前面的方法，在MySQL层面还能做的就只有索引覆盖扫描了，如果还不够可以考虑修改应用的架构，增加汇总表。但是会发现，"快速、精确和实现简单"，三者永远只能满足其二，必须舍掉其中之一。

5.2 优化关联查询

确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序，当表A和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建立索引。没有用到的索引只会带来额外的负担。一般而言，只需要在关联顺序中的第二个表的相应列上创建索引。

确保任何的GROUP BY和ORDER BY中的表达式只涉及一个表中列，这样MySQL才有可能使用索引来优化这个过程。

5.3 优化子查询

建议尽可能使用关联查询代替子查询，但是并非绝对，在5.6及更新版本或者MariaDB中则可以忽略这一建议。

5.4 优化GROUP BY和DISTINCT

MySQL优化器会在内部处理的时候相互转化这两类查询，它们都可以使用索引来优化，这也是最有效的优化办法。采用标志列分组的效率会比其它列更高。在MySQL中，当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或者文件排序来分组。

如果没有通过ORDER BY子句显式指定排序列，当查询使用GROUP BY子句的时候，结果集会自动按照分组的字段进行排序，如果不关心结果集的顺序，而这种默认排序又导致需要文件排序，则可以使用ORDER BY NULL，让MySQL不再进行文件排序，也可以在GROUP BY子句中直接使用DESC或ASC
关键值，使分组的结果集按需要的方向排序。

超级聚合GROUP BY WITH ROLLUP可能不够优化，最好的办法是尽可能的将聚合的功能转移到应用程序中处理。

5.5 优化LIMIT分页

在偏移量非常大的时候，例如LIMIT 10000,20，这时MySQL需要查询10020条记录然后只返回最后20条记录，前面的10000条记录都将被抛弃，这样的代价非常高。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。考虑下面的查询，延迟关联将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。

select film_id,discription from film order by title limit 50,5;
#延迟关联，通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。
select film_id, film.discription from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);

LIMIT和OFFSET的问题，其实是OFFSET的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。

#指定边界，无论翻页到多么后面，其性能都会很好
select * from rental where rental_id < 106030 order by rental_id desc limit 20

除此之外，还可以考虑如下办法：

• 基于索引使用prepare（参考MySQL prepare 原理）

第一个问号表示pageNum，第二个问号表示每页元组数
语句样式: MySQL中,可用如下方法: PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk > (？* ？) ORDER BY id_pk ASC LIMIT M
适应场景: 大数据量
原因: 索引扫描,速度会很快。prepare语句又比一般的查询语句快一点。

5.6 优化SQL_CALC_FOUND_ROWS

mysql的SQL_CALC_FOUND_ROWS 使用 类似count(*) 使用性能更高

5.7 优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好地使用。经常需要手动地将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化。（例如，直接将这些子句冗余地写一份到各个子查询）

5.8 使用用户自定义变量

如果能够用好自定义变量，发挥其潜力，在某些场景可以写出非常高效的查询语句。

感谢各位的阅读，以上就是“mysql查询性能优化的方法是什么”的内容了，经过本文的学习后，相信大家对mysql查询性能优化的方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！

分享名称：mysql查询性能优化的方法是什么
本文URL：http://6mz.cn/article/igpjie.html