我的编程空间,编程开发者的网络收藏夹
学习永远不晚

如何优化MySQL查询

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

如何优化MySQL查询

这篇文章给大家介绍如何优化MySQL查询,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。

解析器和预处理器

一开始,MySQL 的解析器将查询语句拆分成一系列指令并从中构建一棵“解析树”。解析器使用 MySQL 的SQL 语法去翻译和验证查询语句。例如,解析器保证了查询中的指令是有效且次序正确,并且会检查那种类似字符串引号未配对的错误。

预处理器则检查构建好的解析树中那些解析器无法处理的语义信息。例如,检查数据表和列是否存在,并且处理字段名称和别名以保证列引用没有歧义。接下来,预处理器会检查权限,通常这会非常快(除非你的服务端有一大堆权限配置)。

查询优化器

经过解析器和预处理器后,解析树就被确定是有效的了,可以被优化器进行处理并最终转变为一个查询计划。一个具有相同结果的查询通常有很多种执行方式,而优化器的职责是找出其中最优的选项。

MySQL使用基于代价估计的优化器,这意味着它视图预测众多执行计划的代价,并选择代价最低的那个。最初的单位成本是随机的4KB 数据页读取,而现在变得更为复杂,包括了如执行 WHERE比较条件的代价。可以通过显示 Last_query_cost 会话变量来查看查询优化器估计查询语句的代价。

SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor;SHOW STATUS LIKE 'Last_query_cost';

显示的 Last_query_cost 意味着优化器估计需要执行对应次数的随机数据页访问才能完成查询。这是基于如下统计估算的结果:

  • 数据表或索引占据的数据页数;

  • 索引的候选值;

  • 数据行、键及键值分布对应的数据长度。

优化器并不会考虑估计内容的缓存——它假设每次都从磁盘 I/O 读取结果。优化器并不是每次都能选择最优的执行计划,原因如下:

  • 统计本身可能是错误的。服务端的统计结果依赖于存储引擎,而存储引擎可能十分准确也可能很不准确。例如,InnoDB 由于其 MVCC 架构,并不保留数据表的准确行数。

  • 估计的代价和实际运行的代价并不等价,因此即便统计是准确的,查询的代价与 MySQL 的估计也会或多或少存在偏差。一个读取更多数据页的查询计划也可能代价更低,例如如果是有序的磁盘 I/O 访问就会更快,又或是结果本身就已经在缓存中。因此,优化器本身并不知道查询会引起多少次 I/O 操作。

  • MySQL 人为的优化也许与我们期待的不同。我们要的可能是更快的执行时间,而 MySQL 并不是只追求快,它是最求最小化代价。因此,通过代价并不一定科学。

  • MySQL并不考虑并发中的查询,而这可能会影响查询运行的速度。

  • MySQL 并不是一直都按代价估计做优化。有时候仅仅是遵循一些规则,例如如果有一个全文匹配条件(MATCH 方法)则使用全文索引。即便是有一个更快的的其他索引和非全文条件查询,MySQL 也不会按更快的方式执行查询。

  • 优化器对于不归它控制的操作的代价并不会考虑,例如执行存储过程或自定义函数。

  • 优化器并不总是能够估计每一个执行计划,有些时候它会忽略一个更优的计划。

MySQL 查询优化器是其中非常复杂的一部分,使用了很多优化方式将查询语句转换成为一个查询执行计划。通常有两种优化方式:静态优化和动态优化。静态优化可以简单地通过检查解析树进行。例如,优化器可以将 WHERE 条件通过数学运算规则转换成一个等式。静态优化与具体的值无关,例如 WHERE条件的常量值。他们执行一次后会一直有效,即便是查询语句使用了不同的值再次执行。可以理解为是“编译时优化”。

相反,动态优化是基于具体的情景的,并依赖于多种因素。例如,WHERE 条件中的值或索引中对应的数据行数。这个过程在每次查询都需要重新估计,可以理解为是“运行时优化”。以下是一些 MySQL 的典型优化方式:

  • 联合查询重新排序:数据表并不一定需要按照查询语句的顺序联合。决定最优的联合查询次序是十分重要的优化。

  • 将外联接转换为内联接:一个外联接并不一定需要按外联接查询。有些因素,例如 WHERE 条件和数据表结构可以将外联接查询等价于内联接。MySQL 可以识别这些情况,并重写联合查询。

  • 应用数学等价公式:MySQL 应用数学等价转换简化表达式。可以做到展开和减少常量,排除不可能的情况和常量表达式。例如,表达式(5=5 AND a>5)会精简为(a>5)。同样的,(a 5 AND b=c AND a=5.这些规则对带条件的查询十分有用。

  • COUNT(),MIN()和 MAX()优化:索引和空值列通常可以帮助 MySQL 优化这些函数。例如,查找二叉树最左侧一列的最小值时,MySQL 可以只请求索引的第一行数据。甚至可以在查询优化阶段完成这个事情,而对于剩余的查询当作是常量值。而对于查询最大值也是一样,只需要读取最后u 一行即可。如果服务端使用了这种优化,可以在 EXPLAIN 中看到“Select tables optimized away”。这意味着优化器将数据表从查询计划中移除并用常量替代了。类似地,COUNT(*)查询在没有指定 WHERE 条件时也可以在某些存储引擎被优化(例如 MyISAM,会一直保存数据表的准确行数)。

  • 评估和精简常量表达式:一旦 MySQL 检测到一个表达式可以精简为一个常量,那在优化阶段就会完成该操作。例如,一个用户定义的变量如果在查询过程中没有变化,就可以转换为常量。令人惊奇的是,在优化阶段,有些你认为是一个查询的语句也会被转换为常量。一个例子就是 索引上的MIN()。这种情况也可以扩展到对主键或独立索引的常量查询。如果 WHERE 条件对这样的索引指定了常量,优化器会知道 MySQL 会在查询开始就查找对应的值。然后,就会在剩余的查询中把这个值当做常量处理。下面是一个例子:

EXPLAIN SELECT film.film_id, film_actor.actor_idFROM sakila.filmINNER JOIN sakila.film_actor USING(film_id)WHERE film.film_id = 1;

MySQL 会将这个查询拆分为2步,因此分析结果会有两行。第一步是是在 film 表中查找对应的数据行。由于 是按主键film_id查询的,MySQL 知道只有一行数据。 因此,此时的查询分析结果的 ref 是常量。在第二步中,MySQL 会将 film_id 作为已知值,因此对 film_actor 的查询的 ref 也是常量。其他类似的场景还有 WHERE,USING或 ON 条件中的约束条件是等式。在这个例子中,MySQL 知道 USING条件的 film_id 在查询语句中都是相同的值,这个值必须和 WHERE条件的 film_id 相同。

  • 覆盖索引:MySQL 有时候会利用索引数据而避免读数行数据,如果索引包含了查询所需的全部列的话。

  • 子查询优化:MySQL 能够将一些类型的子查询转换为更有效的变体形式,从而简化它们为索引查询而不是相互独立的查询。

  • 提前中止:MySQL 可以在满足查询结果后提前中止查询过程。最明显的例子是 LIMIT条件。也有一些其他的提前中止的情形。例如,MySQL 检测导一个可能条件后,可以中止整个查询,如下面的例子所示:

EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id=1;

在分析结果中的 Extra字段会看到“Impossible WHERE noticed after reading const tables”。在其他情形也会有提前中止的情况,例如:

SELECT film.film_idFROM sakila.filmLEFT OUTER JOIN sakila.film_actor USING(film_id)WHERE sakila.film_actor.film_id IS NULL;

这个查询排除那些有演员的电影。每部电源都可能有多名演员,但是只要找到一名演员后,MySQL 就会停止处理当前的这部电影,而去处理下一部。对于 DISTINCT,NOT EXISTS 也会有类似的情况。

  • 等效传递:MySQL 会识别导查询语句中保持的列是否是等效的。例如,在 JOIN 条件中,WHERE 条件会影响导相同的列,如下面的查询:

SELECT film.film_idFROM sakila.filmINNER JOIN sakila.film_actor USING(film_id)WHERE film.film_id > 500;

MySQL 会知道 WHERE 条件的约束不仅适用于 film 表,同样也适用于 film_actor 表。但对于其他数据库则未必会有这样的优化效果。

  • IN 查询比较:对于很多数据库服务器,IN 查询比等价为多个 OR 条件,在逻辑上二者是等效的。但在 MySQL 中不是这样,MySQL会对 IN 查询的列表值进行排序,并使用二分查找法去检查查询值是否在列表中。这会使得算法复杂度从 O(n)降低导 O(log n)。

实际上,MySQL 使用的优化手段比上述列举的多得多,这里没法一一列举。只是需要记住 MySQL 的优化器的复杂性及其智能化程度。因此,应当让优化器发挥其作用,而不是无限优化查询语句直到 MySQL 的优化器没有用武之地。当然,虽然 MySQL 的优化器很聪明,但是它给出的并不一定是最优结果,有些时候你知道最优结果,而 MySQL 未必知道。这种情况下,你可以对查询语句进行优化从而帮助 MySQL 完成优化工作,而有些时候则需要增加查询的提示,或是重写查询,修改数据表设计或增加索引。

关于如何优化MySQL查询就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

如何优化MySQL查询

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

如何优化MySQL查询

这篇文章给大家介绍如何优化MySQL查询,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。解析器和预处理器一开始,MySQL 的解析器将查询语句拆分成一系列指令并从中构建一棵“解析树”。解析器使用 MySQL 的SQL 语
2023-06-15

MySQL如何优化查询速度

前面章节我们介绍了如何选择优化的数据类型、如何高效的使用索引,这些对于高性能的MySQL来说是必不可少的。 但这些还完全不够,还需要合理的设计查询。 如果查询写的很糟糕,即使表结构再合理、索引再合适,也是无法实现高性能的。 谈到MySQL性
2022-05-23

MySQL之join查询如何优化

这篇文章主要介绍“MySQL之join查询如何优化”,在日常操作中,相信很多人在MySQL之join查询如何优化问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”MySQL之join查询如何优化”的疑惑有所帮助!
2023-07-05

如何优化mysql查询速度

要优化 MySQL 查询速度,可以采取以下几种方法:使用合适的索引:在查询中使用索引可以显著提高查询速度。确保表中的字段都有适当的索引,尤其是在经常用作查询条件的字段上。避免全表扫描:尽量避免对整个表进行扫描,可以通过添加索引或优化查询语句
如何优化mysql查询速度
2024-05-23

如何优化MySQL循环查询

要优化MySQL循环查询,可以采取以下几种方法:使用JOIN操作:尽量避免在循环中进行多次查询,可以通过使用JOIN操作将多个查询合并成一个查询,减少查询次数,提高效率。使用索引:为经常被查询的字段建立索引,可以加快查询速度。同时,尽量避免
如何优化MySQL循环查询
2024-04-29

mysql in慢查询如何优化

这篇文章主要介绍“mysql in慢查询如何优化”,在日常操作中,相信很多人在mysql in慢查询如何优化问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”mysql in慢查询如何优化”的疑惑有所帮助!接下来
2022-12-30

mysql优化——查询优化

这一篇mysql优化是注重于查询优化,根据mysql的执行情况,判断mysql什么时候需要优化,关于数据库开始阶段的数据库逻辑、物理结构的设计结构优化不是本文重点,下次再谈。 查看mysql语句的执行情况,判断是否需要进行优化 当感觉操作数据库查询语句速度
mysql优化——查询优化
2016-09-05

mysql大字段查询如何优化

要优化MySQL大字段查询,可以尝试以下几种方法:优化查询语句:确保只查询需要的字段,避免查询大字段,可以使用SELECT子句中的字段列表来指定需要的字段,避免使用SELECT *。使用索引:为大字段创建索引可以加快查询速度,尤其是在whe
mysql大字段查询如何优化
2024-05-10

如何优化MySQL的查询性能?

如何优化MySQL的查询性能?MySQL是一款广泛应用于Web开发的关系型数据库管理系统。然而,在处理大量数据和复杂查询时,MySQL的查询性能可能会受到影响,从而导致应用程序的响应时间变慢。为了提高MySQL的查询性能,我们可以采取以下几
2023-10-22

mysql查询优化

select * from a where id in (select id from b)等价于:for select id from bfor select 8 from a where a.id = b.id当b表数据必须小于a表数据时,in优于exis
mysql查询优化
2022-02-02

MySQL中MRR如何优化范围查询

目录一、MRR优化概述二、MRR优化的背景三、MRR优化的原理四、MRR优化的优势五、磁盘预读机制六、局部性原理七、使用场景、条件与监控1. 配置参数2. 监控方法八、SQL案例解读一、MRR优化概述MRR,全称Multi-Range R
MySQL中MRR如何优化范围查询
2024-10-10

如何优化 PHP 中的 MySQL 查询性能?

可以通过以下方式优化 mysql 查询性能:建立索引,将查找时间从线性复杂度降至对数复杂度。使用 prepared statements,防止 sql 注入并提高查询性能。限制查询结果,减少服务器处理的数据量。优化连接查询,包括使用适当的连
如何优化 PHP 中的 MySQL 查询性能?
2024-05-11

如何在mysql中对查询进行优化

本篇文章为大家展示了如何在mysql中对查询进行优化,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。1、优化方法(1)重新定义表的关联顺序(多张表关联查询时,并不一定按照SQL中指定的顺序进行,但有一
2023-06-15

编程热搜

目录