文章详情

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

请输入下面的图形验证码

提交验证

短信预约提醒成功

如何优化MySQL查询

2023-06-15 01:10

关注

这篇文章给大家介绍如何优化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 读取结果。优化器并不是每次都能选择最优的执行计划,原因如下:

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

相反,动态优化是基于具体的情景的,并依赖于多种因素。例如,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 相同。

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 也会有类似的情况。

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

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

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

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

阅读原文内容投诉

免责声明:

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

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

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯