WHERE Clause Optimization

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 本节探讨了WHERE子句的优化方法,虽然示例基于SELECT语句,但也适用于DELETE和UPDATE语句。MySQL自动执行多种优化,例如仅计算一次索引使用的常量表达式、快速检测无效表达式、合并HAVING和WHERE子句、优先读取常量表、寻找最佳连接组合、使用内存中的临时表、选择最佳索引以及在某些情况下仅使用索引树解析查询,从而提升查询效率。

本节讨论可以对处理WHERE子句进行的优化。这些示例使用SELECT语句,但同样的优化也适用于DELETE和UPDATE语句中的WHERE子句

image.png

您可能会试图重写查询以使算术运算更快,同时牺牲可读性。由于MySQL会自动执行类似的优化,因此您通常可以避免这项工作,并将查询保持在更易于理解和维护的形式。MySQL执行的一些优化如下:
image.png

image.png

索引使用的常量表达式只计算一次。
不带WHERE的单个表上的COUNT(*)直接从MyISAM和MEMORY表的表信息中检索。当仅与一个表一起使用时,也可以对任何NOT NULL表达式执行此操作。
早期检测无效的常量表达式。MySQL很快检测到某些SELECT语句是不可能的,并且不返回任何行。
如果不使用GROUP BY或聚合函数(COUNT()、MIN()等),HAVING将与WHERE合并。
对于联接中的每个表,构造一个更简单的WHERE,以快速计算表的WHERE,并尽快跳过行。
所有常量表都会在查询中的任何其他表之前首先读取。常数表可以是以下任何一种:
空表或只有一行的表。
与PRIMARY KEY或UNIQUE索引上的WHERE子句一起使用的表,其中所有索引部分都与常量表达式进行比较,并定义为NOT NULL。
以下所有表都用作常量表:

image.png

通过尝试所有可能性,找到连接表的最佳连接组合。如果ORDER BY和GROUP BY子句中的所有列都来自同一个表,则连接时首选该表。
如果存在ORDER BY子句和不同的GROUP BY子句,或者ORDER BY或GROUP BY包含联接队列中第一个表以外的表中的列,则会创建一个临时表。
如果您使用了DateTimeSMALL_RESULT修饰符,MySQL将使用内存中的临时表。
查询每个表索引,并使用最佳索引,除非优化器认为使用表扫描更有效。曾经,扫描是基于最佳索引是否覆盖了表的30%以上,但固定的百分比不再决定使用索引还是扫描。优化器现在更加复杂,其估计基于其他因素,如表大小、行数和I/O块大小。
在某些情况下,MySQL甚至可以在不查阅数据文件的情况下从索引中读取行。如果索引中使用的所有列都是数字,则只有索引树用于解析查询。
在输出每一行之前,跳过与HAVING子句不匹配的行。
一些非常快速的查询示例:

image.png

MySQL resolves the following queries using only the index tree, assuming that the indexed columns are numeric:

image.png

以下查询使用索引按排序顺序检索行,而无需单独的排序过程:

image.png

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
存储 关系型数据库 MySQL
Optimization and Indexes
MySQL通过索引快速定位具有特定列值的行,避免全表扫描,提高查询效率。常用的索引如PRIMARY KEY、UNIQUE等大多存储在B树中,特殊情况使用R树或哈希索引。索引帮助快速匹配WHERE子句条件的行,减少候选行数,并在多列索引和表连接操作中优化查询。具体特性如B树和哈希索引的比较见特定章节。
|
5月前
|
缓存 监控 前端开发
Performance Optimization
Performance Optimization
74 2
|
5月前
|
Oracle 关系型数据库
Adaptive Query Optimization
Adaptive Query Optimization
35 4
|
算法 搜索推荐 数据库
一个有点咬文嚼字的 sorting 和 ordering
为什么排序算法的英文是 sorting 而不是 ordering。
125 0
|
存储 编解码 固态存储
Performance optimization with Lucene4.0
假期重新把之前在新浪博客里面的文字梳理了下,搬到这里
118 0
|
SQL 存储 算法
《Optimization of Common Table Expressions in MPP Database Systems》论文导读
Optimization of Common Table Expressions in MPP Database Systems
《Optimization of Common Table Expressions in MPP Database Systems》论文导读
|
算法 关系型数据库 MySQL
Fundamental Techniques for Order Optimization
这是一篇1996年的老paper了,主要讲解了IBM DB2如何针对query当中的有序性进行优化。但对于后续physical property的优化有较为深远的影响,由于DB2的优化器起源于System-R以及其后续演进的starburst,因此延续了system-R中的interesting order和order property的概念。关于system-R的介绍请看之前的文章。 order这种physical property并不只限于order by算子,基于有序的group by/distinct等,都会利用到数据的排序操作,而排序本身就是比较昂贵的计算,因此应该对其做尽可能的优化
220 0
Fundamental Techniques for Order Optimization
|
SQL 算法 关系型数据库
Optimizing Queries over Partitioned Tables in MPP Systems
随着互联网数据的爆炸性增长,传统数据库系统在单表数据容量方面承受了越来越大的压力。以前公司内部的数据库,存放的主要是来自公司业务或内部管理系统的信息,中小型公司甚至一个MySQL实例就搞定了。但现在数据源不仅更丰富,数据量也在指数级增长,从业务的角度,基于hash/range的分区表变得越来越有吸引力。
252 0
Optimizing Queries over Partitioned Tables in MPP Systems
|
SQL 监控 算法
Adaptive Execution of Compiled Queries 论文解读
本篇是TUM的内存数据库HyPer针对compile-based执行框架的改进。其中涉及到HyPer的动态编译和并行执行框架 动态编译文章的结尾提到了编译执行系统存在的2个问题,其中之一就是:不可控的编译时间。
481 0
Adaptive Execution of Compiled Queries 论文解读
|
SQL 移动开发 算法
New Dynamic Programming Algorithm for the Generation of Optimal Bushy Join Trees
MySQL无疑是现在开源关系型数据库系统的霸主,在DBEngine的最新排名中仍然稳居第2位,与第3位SQL Server的积分差距并不算小,可以说是最受欢迎,使用度最高的数据库系统,这一点看看有多少新型数据库要兼容MySQL的协议和语法就知道了。
309 0
New Dynamic Programming Algorithm for the Generation of Optimal Bushy Join Trees