SQL调优的最佳实践

学会用脚编程

于 2024-08-24 00:37:28 发布

阅读量556

点赞数 24

分类专栏： MySQL 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/weixin_50737119/article/details/141476835

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1、避免使用select *

通过explain查看执行计划，select *走的是全表扫描，没有用到任何索引，查询效率是非常低的；

*中得字段如果包括索引之外的字段，那么就会走回表查询（聚簇索引和非聚簇索引），这样一来效率就会非常慢

另外有一些字段比文本类型，大字段数据传输也会增加网络消耗

所以杜绝使用select * 是常见优化方式之一

2、小表驱动大表

小表驱动大表是指数据量较小，索引比较完善的表，然后使用其索引和条件对大表进行数据筛选，从而减少数据计算量，提高查询效率。

3、连接查询代替子查询

子查询需要执行两次查询数据库，一次是外部查询，一次是嵌套子查询。因此，使用连接查询可以减少数据库的查询次数，提高查询的效率。

对于大型数据集，使用连接查询的效率通常比子查询更加高效，子查询通常是需要扫描整个表，而连接查询可以利用索引加速读取操作

4、提升group by的效率

创建索引：如果你使用group by的列没有索引，那么查询可能会变得很慢。因此可以创建一个或者多个索引，来加速查询效率

5、批量操作

循环插入肯定效率是最低的，因为需要和数据库进行多次交互，损耗性能

可以进行批量插入：

业务场景：excel数据批量导入

1、通过动态sql的方式对sql进行批量插入

注意：虽然动态sql可以大大提升插入的效率，但是不建议一次性插入太多的数据。因为数据太多的话数据库响应也会很慢。批量操作需要把我一个度，一般建议每次插入的数量500.然后分批次处理。这个样也可以很好的避免内存溢出（数据太多内存不够）或者死锁等问题。

6、使用limit

如果一次查询的数据太多的话，一个查询返回上万条数据，不仅占用了大量的系统资源，也会占用更多的网络宽带，影响查询效率。使用limit可以限制返回数据的行数，减轻系统负担，提升查询效率。

7、join的表不宜过多

多表join的话查询数据对比时间表较长

join需要经过大量的计算，会导致系统负载增加

维护难度较大，如果一条查询的sql连接表比较多，那么如果后期需要修改其中一个表的结构和内容，很可能需要同时修改其他表的结构和内容

总结

话说回来SQL优化主要核心就是：

1、减少数据扫描

2、返回更少的数据

3、减少交互次数

4、减少服务器CPU以及内存的开销

explain来查看sql执行的详情信息，进而优化

EXPLAIN 是一个用于获取 SQL 语句执行计划的命令，用于帮助理解查询的执行过程以及如何优化。这里主要是索引优化

下面讲解几个常用的，面试回答出就可以了

2.1 type 列（重点）

"type"列用于表示访问表时所采用的访问类型。

下面是常见的"type"值及其含义：

const: 表示通过索引只能匹配到一行数据。 explain select * from student where id = 1688

eq_ref: 表示使用了等值连接（例如，使用主键或唯一索引连接表）。explain SELECT * FROM student s1 JOIN student s2 ON s1.id = s2.id WHERE s1.age = 25;

ref: 表示使用了非唯一索引进行查找，并返回匹配的多行或一行数据。 explain select * from student where name = '张68'

range: 表示使用了索引进行范围查找，例如使用比较符（>, <, BETWEEN）或IN操作符。 explain select * from student where age < 1688

index: 表示全索引扫描，也就是说用了某一个索引的全部, 通常发生在查询使用索引覆盖的情况下。explain select count(*) from student ；explain select sum(age) from student

all: 表示全表扫描，即没有使用索引，需要遍历整个表进行查询。 explain select * from student