SQL优化之避免全表扫描

简介: 在mysql5.6官方文档中关于full table scan的介绍如下:An operation that requires reading the entire contents of a table, rather than just selected portions using an index.

在mysql5.6官方文档中关于full table scan的介绍如下:

An operation that requires reading the entire contents of a table, rather than just selected portions using an index. Typically performed either with small lookup tables, or in data warehousing situations with large tables where all available data is aggregated and analyzed. How frequently these operations occur, and the sizes of the tables relative to available memory, have implications for the algorithms used in query optimization and managing the buffer pool.
The purpose of indexes is to allow lookups for specific values or ranges of values within a large table, thus avoiding full table scans when practical.

大意:
Full table scan是一种需要读取表全部数据的操作,常见于读取小表的全部内容,或者在数仓中查询大表所有数据用以分析或聚合。
索引的目的是允许在一个大表中查找特定的值或值的范围,从而在实际操作时避免全表扫描。

一、发生全表扫描的条件

在表数据非常大时,全表扫描往往是灾难性的。当explain输出的type列的值为ALL时,表示这一步发生了全表扫描操作。一般出现以下条件时会发生全表扫描:
1、表数据量很小,走索引查询比全表扫描更麻烦;这对于少于10行且行长度较短的表来说很常见;
2、SQL的on子句或者where子句涉及到的列上没有索引;
3、将索引列与常量值进行比较,并且MySQL已经计算(基于索引树)常量覆盖了表中太大的一部分,而表扫描会更快。换言之,即便字段选择性高且有索引,但要取出的数据行太多,也不会走索引,这个比例一般是20%-30%;
4、对于低选择性的索引列,一个键值可能会匹配N多行数据,这个时候也不走索引而发生全表扫描。

二、优化全表扫描

对于小数据量的表,表扫描通常是合适的。对于大数据量的表,可以尝试以下技术来避免优化器发生全表扫描:
1、通过“ANALYZE TABLE tb_name”语句更新表上的索引统计信息;对于innodb表和myisam表来说,执行该语句时会加表级S锁;
2、使用 “FORCE INDEX”语句强制走索引,告诉优化器走该索引更合适;
3、修改max_seeks_for_key参数值,例如:SET max_seeks_for_key=1000。该参数的含义:根据键查找行时,限制假定的最大查找次数;通过调低该参数值,以使优化器更喜欢索引而不是表扫描。

目录
相关文章
|
26天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
4天前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
17 2
|
5月前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
546 2
|
2月前
|
SQL Oracle 关系型数据库
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能?
在 Oracle 数据库中,SQL Profiles 是优化查询性能的工具,通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括:1. 启用自动 SQL 调优;2. 手动创建 SQL Profile,涉及收集、执行调优任务、查看报告及应用建议;3. 验证效果;4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
|
3月前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
103 11
|
2月前
|
SQL 分布式计算 Java
Spark SQL向量化执行引擎框架Gluten-Velox在AArch64使能和优化
本文摘自 Arm China的工程师顾煜祺关于“在 Arm 平台上使用 Native 算子库加速 Spark”的分享,主要内容包括以下四个部分: 1.技术背景 2.算子库构成 3.算子操作优化 4.未来工作
107 0
|
4月前
|
SQL 缓存 监控
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
|
4月前
|
SQL 缓存 数据库
SQL慢查询优化策略
在数据库管理和应用开发中,SQL查询的性能优化至关重要。慢查询优化不仅可以提高应用的响应速度,还能降低服务器负载,提升用户体验。本文将详细介绍针对SQL慢查询的优化策略。
|
4月前
|
SQL 存储 BI
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
|
4月前
|
SQL 数据库
gbase 8a 数据库 SQL优化案例-关联顺序优化
gbase 8a 数据库 SQL优化案例-关联顺序优化

热门文章

最新文章