常见SQL优化手段、及查看执行计划
1. 优化查询语句
避免复杂的子查询(能用连接查询替代时)
- 性能不佳:
- 许多子查询在主查询每处理一条记录时,都可能需要重新执行一次。对于查询结果中嵌套资产,外层查询结果集中的每一行数据,数据库都要再次执行内层子查询来进行相应的条件判断或数据计算。
- 对索引的利用不好:子查询的条件关联往往使得数据库不能像处理简单连接查询那样直接按照索引顺序高效检索数据。因为每次执行子查询时的条件都是动态变化的(依赖于外层查询当前行对应的值),导致原本可以快速定位数据的索引不能充分发挥作用,使得数据库不得不进行全表扫描或者更多额外的查找操作来满足子查询的条件,从而影响查询的速度
- 结果可能不准确:子查询使用 IN、NOT IN时,如果结果中包含Null 可能无法正确返回数据
- 子查询不易于维护,读起来相对困难
使用索引优化查询条件:
- 为经常用于查询条件(如 WHERE 子句中的字段)、排序(ORDER BY)以及分组(GROUP BY)的字段创建索引。
- 但要注意避免过度索引,因为索引本身也会占用存储空间,并且在数据增删改操作时需要更新索引,增加了额外开销。
相同的SQL多次重复执行时,使用批量操作
mysql中:
INSERT INTO students (student_id, student_name, age)
VALUES
(1001, 'Zhang San', 20),
(1002, 'Li Si', 21),
(1003, 'Wang Wu', 19);
Java中:
- 在mybatis中配置 BatchExecutor 来执行批量SQL
- mapper文件中使用 for-each 标签批量插入
- 单次批插入数量比较大,比如超过500条。可以每500条调用一次批量插入操作。
使用limit
SELECT 1 FROM emp a LIMIT 10 , 5; limit 从第10条之后,返回5条数据
SELECT * FROM emp a order by a.id LIMIT 5 offset 10;
LIMIT 10 , 5 作用等同于 LIMIT 5 offset 10。
- 作用过程:根据排序order后的总结果集,分为中间结果集逐段查找,直到找到需要的第几块和多少条后返回(页数过大时,查找会慢)
避免使用 SELECT *
可以减少数据传输量,尤其是在表字段较多时,能显著提升查询效率
可以使用union all的情况,不要使用union
模糊查询时,通配符放在字符后面利用索引 like '3000%';
提升group by的效率
对被group by的字段增加索引
小表驱动大表
数据量小、索引比较完备的表作为主表 join 关联表来查询
少关联表,一般是1~3张表互相关联
2. 优化表结构
合理设计表字段类型:
根据字段实际存储的数据选择合适的数据类型,避免使用过大的数据类型浪费存储空间,同时也能提升数据操作的性能。
比如,用 INT 类型存储整数比用 BIGINT 更节省空间且运算更快,若能确定年龄字段的值范围在合理的整数区间内,就选择 INT 类型存储年龄
避免过多的冗余字段:
尽量通过合理的表关联来获取所需数据,而非在一张表中堆积大量冗余信息。
拆分大表(垂直拆分和水平拆分):
- 垂直拆分:当一张表中字段过多,且部分字段关联性不强时,可以将表按照字段的业务逻辑进行垂直拆分,形成多张表,降低单张表的复杂度,便于数据管理和查询优化。例如,将用户表中基本信息和用户详细信息(如收货地址等经常变动且查询频率相对低的字段)拆分成两张表。
- 水平拆分:针对数据量非常大的表,按照一定规则(如按照时间范围、地域等)将数据拆分到多个结构相同的表中,分散数据存储压力,提高查询性能。比如电商系统中订单表数据量巨大,可按年份将订单数据拆分到不同的表中
3. 优化数据库设计
- 遵循数据库范式(一般到第三范式):
合理设计数据库表之间的关系,减少数据冗余和异常情况(如插入异常、删除异常等)。但在实际应用中,有时为了性能等因素可能会适当反范式化,需要在数据一致性和查询性能之间做权衡。 - 使用合适的存储引擎(针对 MySQL 等支持多种引擎的数据库):
不同的存储引擎有不同的特点,例如 MySQL 中 InnoDB 支持事务、行级锁和外键等,适合对数据一致性、并发访问要求高的应用场景;MyISAM 则查询速度较快,适合以读操作为主、对事务要求不高的场景。根据业务需求选择合适的存储引擎能优化数据库整体性能。
4. 优化数据操作语句
- 批量操作代替循环操作:
当需要插入、更新或删除多条数据时,尽量使用批量操作语句。比如批量插入用户信息,使用 INSERT INTO users (username, password) VALUES (...), (...), (...); 形式(一次插入多条记录),比循环多次执行单条插入语句效率更高,可减少数据库交互次数。 - 合理使用事务:
对于一组相关的数据库操作(如银行转账涉及的扣款和收款操作),将它们放在一个事务中,保证数据的一致性和完整性。但要注意事务的范围不宜过大,避免长时间占用数据库资源,影响并发性能,并且要根据业务需求合理设置事务的隔离级别。
5. 优化数据库配置
- 调整缓存相关参数:
数据库通常有各种缓存机制,如查询缓存(部分数据库支持)、缓冲池等,适当调整缓存相关参数,如缓存大小、缓存过期时间等,可以提高数据访问的命中率,减少磁盘 I/O,提升性能。 - 优化数据库连接池配置:
合理配置连接池的参数,如最大连接数、最小连接数、连接超时时间等。确保在高并发情况下有足够的连接可用,同时避免创建过多不必要的连接浪费资源,保证数据库的稳定运行和高效性能。
6. 分析执行计划
使用数据库自带的解释工具(如 MySQL 的 EXPLAIN):
通过 EXPLAIN 命令可以查看 SQL 语句的执行计划,了解数据库是如何查询数据的,包括使用了哪些索引、表的连接顺序、扫描的行数等关键信息,从而发现潜在的性能瓶颈,针对性地进行优化。例如,查看一条查询语句的执行计划:
EXPLAIN SELECT * FROM users WHERE username LIKE '%test%';
根据执行计划反馈的结果,判断是否需要添加索引或者调整查询条件等优化操作。
7. 定期维护数据库
- 数据清理与归档:
对于不再经常使用的数据,可以进行清理或者归档到其他存储介质(如历史订单数据转移到数据仓库等),减少主数据库的数据量,提升查询等操作的性能。 - 索引重建与优化:
随着数据的不断增删改,索引可能会出现碎片化等问题,定期对索引进行重建、分析和优化,保证索引的有效性,有助于提高查询性能。
查看 Explain Plan 执行计划
使用执行计划的目的
方便分析一些复杂的语句,是否用到了索引。执行计划是对SQL执行结果的预期,不完全等同于实际环境运行的效果。
查看表的索引:show index from emp;
Mysql 使用explain一条SQL语句,来查看执行计划,需要关注的信息如下:
- id:SQL中被独立处理部分,它们实际执行的优先级,越小越先执行。相同 id 的部分通常属于同一个查询层级或者操作单元。
- select_type:SQL类型
- SIMPLE:一个单一的 SELECT 语句直接从表中获取数据
- PRIMARY:当查询语句中包含子查询或者 UNION 等复杂结构时,最外层的主查询对应的 select_type 取值为 PRIMARY。用以区分主层和外层的部分。
- SUBQUERY:用于表示在 SELECT、WHERE、HAVING 等子句中出现的子查询,且该子查询不依赖于外层查询的结果,是独立执行的
- DERIVED:当子查询出现在 FROM 子句中,并且这个子查询相当于生成了一个临时的派生表(Derived Table)时,该子查询对应的 select_type 取值为 DERIVED
- UNION:如果查询语句中使用了 UNION 操作符将多个 SELECT 语句联合起来获取数据,除了第一个 SELECT 语句对应的 select_type 取值为 PRIMARY(因为它是整个联合查询的主体外层部分)之外,其余通过 UNION 连接的 SELECT 语句对应的 select_type 取值就是 UNION
- UNION RESULT:用于表示对 UNION 操作所得到的结果集进行合并、去重等处理的操作对应的 select_type 取值。
- MATERIALIZED:当子查询被物化(Materialized)时,也就是子查询的结果被提前计算出来并存储为一个临时的物化结果集,供外层查询多次使用,此时该子查询对应的 select_type 取值为 MATERIALIZED。
- table:别名
- type:表示查询的方式。
- ALL:遍历整张表的每一条记录来查找满足条件的数据(这是一种效率相对较低的访问类型,意味着数据库需要对表进行全表扫描)
- const:表示查询索引字段,并且表中最多只有一行匹配
- eq_ref | ref| ref_or_null:表示被关联表的 where 关联字段的类型:1对1 | 1对多 | 1对多并允许为null
- index_merge(常见于 MySQL 等数据库):根据多个索引查询后合并得到的结果
- unique_subquery | index_subquery:常出现在子查询中,并且子查询返回的结果是基于(唯一索引 | 普通索引)或者主键的单一值,作为外层查询的条件使用
- range:用于表示通过索引进行范围查找的情况,常见的如使用 BETWEEN、>、<、>=、<= 等运算符限定了一个范围条件来访问表中的数据
- fulltext|system(仅 MySQL有):根据articles 全文检索数据(mysql 需要开启全文检索功能)| 对数据库系统表查询
- possible_Keys:显示在当前查询中,数据库认为可以使用的所有潜在的索引列表。(可以了解是否存在被数据库忽略但可能有助于优化查询的索引)
- key:指出当前查询实际使用的索引名称,如果没有使用任何索引,则显示为 NULL。
- rows:预估的在执行当前操作时需要扫描或者处理的数据行数
- key_len:表示当前查询使用的索引的长度,单位通常是字节。它可以反映出索引被使用的具体情况,比如对于复合索引(包含多个列的索引),通过 key_len 的值可以大致判断出是索引中的哪些列参与了查询操作,以及每个列使用了多少字节来构建索引等信息,辅助分析索引利用是否充分、合理。
- index_type:索引类型
- filtered:表示基于某个条件对数据进行筛选过滤的比例估计值,以百分比的形式呈现。它反映了经过当前查询涉及的各种条件筛选后,预计符合要求的记录在表所有可能记录中所占的大致比例,通过这个值可以评估查询条件的筛选效果,判断是否需要优化查询条件、添加索引等来提高筛选效率,减少不必要的数据处理量。
- Extra:表示额外含义,常见取值:
- Using index:表示查询只需要遍历索引就能获取到全部所需的数据,无需访问表中的实际行记录,这种情况通常出现在查询的所有列都包含在索引中(即全索引扫描),是一种比较高效的查询方式。
- Using where:说明在获取数据后,还需要通过 WHERE 子句进一步对数据进行筛选,也就是存在额外的筛选条件在数据检索之后起作用。
- Using temporary:意味着查询过程中生成了临时表来辅助完成查询操作,临时表的生成和使用会占用一定的系统资源,并且可能影响查询性能,若频繁出现,需要考虑优化查询以避免使用临时表。
- Using filesort:表示在查询过程中需要对结果进行排序操作,且不能利用索引来完成排序,而是要在内存或磁盘等介质上进行文件排序,这是一种相对耗时的操作,尽量要通过优化查询逻辑或者创建合适的索引来避免这种情况出现。
- Range checked for each record(常见于 MySQL):通常在使用索引进行范围查找时,对每条记录都要重新检查范围条件是否满足,这种情况可能会导致一定的性能损耗,需要关注是否可以优化查询条件或者索引来改善
Oracle 查看执行计划需要关注的信息(PL\SQL 中 F5):
查看执行计划语句:
EXPLAIN PLAN FOR SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
主要需要关注的字段:
- Id:用于标识执行计划中的各个操作步骤,数字越小一般越先执行
- Operation:指明了具体的操作类型,如 TABLE ACCESS FULL 表示全表扫描,INDEX RANGE SCAN 表示索引范围扫描,NESTED LOOPS 表示采用嵌套循环的方式进行表连接等
- Name:显示了操作涉及的表名或者索引名
- Bytes:预估每个操作步骤涉及的数据字节数,可辅助了解数据量大小对性能的影响等情况
- cost:操作的成本估计值,是 Oracle 中综合考虑多种因素(如 CPU 使用率、I/O 操作等)来衡量操作复杂度和性能开销的一个指标
- Time:预估的操作执行所需的时间范围
- Rows:预估的每个操作步骤涉及的数据行数