MySQL系列(19): SELECT 查询语句优化
MySQL 查询是以 SELECT 语句形式执行数据库中的所有查找操作,SELECT 操作在执行的 SQL 语句中占大比例,SELECT 调优可以说是对 SQL 优化的首要任务。
查询调优除了应用于 SELECT 语句外,还适用于诸如 create table…as select、insert in to…select 和 delete 语句中的 where 子句等构造。这些语句还有额外的性能考虑,因为它们将写操作与面向读的操作结合在了一起。
查询优化考虑因素
SELECT 优化优先考虑索引的使用
要优化 SELECT … WHERE 查询,首先考虑索引使用是否恰当,或在 WHERE 子句中使用的列上创建索引,以加快评估、过滤和结果的最终检索。为了避免浪费磁盘空间,可以构造一小组索引,以加速应用程序中使用的许多相关查询,尽可能地避免查询中的全表扫描,尤其是对于大表。索引对于引用不同表、使用诸如连接和外键特性的查询非常重要。如果索引使用未达预期,可以使用 EXPLAIN 来查看具体那些 SELECT 用了索引,或并调整索引、WHERE 子句、连接子句等来查看查询的内部信息。
对查询语句进行隔离和调整,例如函数调用,会占用较多的时间。
根据查询的结构,可以为结果集中的每一行调用一次函数;甚至为表中的每一行调用一次函数,从而大大提高效率。【有疑惑,待细究】定期使用 ANALYZE TABLE 语句更新表统计信息到最新,使优化器可以构建更高效的扫许计划。
了解表所使用的存储引擎的调优技术、索引技术和配置参数。
InnoDB 和 MyISAM 都有相关的优化指导,可以在查询中实现和提高性能。避免难以理解的类型转换查询、尤其是在优化器自动执行某些隐式类型转换的情况下。
调整 MySQL 用于缓冲区大小和属性。
通过有效使用 InnoDB 缓冲区、MyISAM key 缓存 和 MySQL 查询缓存,重复的查询运行的更快,因为第二次及以后的时间内从缓存中检索结果。即使使用了高速缓存快速运行的查询,仍可进一步优化,以便使用更少的高速缓存,从而使应用程序更具可伸缩性。
可伸缩性意味着可以处理更多的并发用户,更大的请求等,而不会出现性能大幅下降的性况。处理锁的问题,查询速度可能会受到同时访问表的其他会话的影响。
WHERE 子句优化
WHERE 子句的优化,示例使用 SELECT 语句,同样也和达用于 DELETE 和 UPDATE 语句中的 WHERE 子句。
索引使用常量表达式只计算一次,常量索引是最高效的。
对于 MyISAM 和 MEMORY 表,在单个表上使用没有 WHERE 的 COUNT(*),可以直接从表的信息中获取该值。
当只与一个表一起使用时,也可以对任何 NOT NULL 表达式执行此操作。早些检查表效的常量表达式。MySQL可以快速检测到不可用的 SELECT 语句,并且不返回任何行。【有疑惑,待细究】
如果不使用 GROUP BY 或 聚合函数(COUNT(),MIN() 等),HAVING 将与 WHERE 合并。
实际上 HAVING 常与 GROUP BY 配合使用,若无 GROUP BY 使用了 HAVING,则多此一举。
对于联接中的每一个表,构造一个更简单的 where 以获得表的快速 where 评估,以尽快跳过行(获取小表)。
先获取小表(小数据集),再连接大表,尽可能避免笛卡尔乘积。
首先读取所有常量表,然后再读取查询中的任何其他表。常量表有以下种情况:
- 空表或只有一行的表。
- WHERE 子句使用了主键或唯一索引。其中索引列定义为 NOT NULL,索引与常量表达式进行比较。
尝试所有可能以找到多表连接的最佳组合方式。如果 ORDER BY 和 GROUP BY 子句中的所有列都来自同一个表,则连接首选该表。
如果存在 ORDER BY 子句和不同的 GROUP BY 子句,或者 ORDER BY 或 GROUP BY 包含来自联接第一个表以外的表的列,则创建临时表。
查询每个表的索引,并使用最佳索引,除非优化器认为全表扫描更有效。
根据使用的索引评估检索的行数若是占表的 30% 以上,则可能使用全表扫描。但选择索引还是全表扫描,也不全由这个固定的百分比决定。现在优化器评估非常复杂,还包含了其他因 素,如表大小、行数和 I/O 块大小。在某些情况下,MySQL可以从索引中读取行,甚至不需要查询数据文件。(覆盖索引的使用)
如果使用的索引列都是数字,则只使用索引树来解析查询。在输出每一行之前,将跳过与 HAVING 子句不匹配的行。
一些查询非常快的示例:
1 | SELECT COUNT(*) FROM tbl_name; |
使用的索引列是数字类型,解析以下查询仅使用索引树:
1 | SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val; |
使用索引来排序检索行:
1 | SELECT ... FROM tbl_name |
MySQL系列(19): SELECT 查询语句优化
http://blog.gxitsky.com/2019/03/18/MySQL-19-Optimizing-SQL-1-SELECT/