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
需要注意的是,"type"列显示的结果是一个逐渐优化的输出,从最好到最差的顺序。尽可能选择更快和更有效的访问方法。
2.2 key列
在MySQL的EXPLAIN查询结果中,"key"列用于表示用于访问表的索引的名称。它显示了优化器选择的用于访问表的索引的名称,或者标识没有使用索引。
下面是"key"列可能出现的不同值及其含义:
-
索引名称:表示使用了具名索引,优化器选择了该索引进行查询。
-
NULL:表示查询没有使用任何索引,即进行了全表扫描
-
2.3 key_len列
"key_len"列用于表示索引字段的最大长度。它显示了优化器使用的索引字段的最大长度,以字节为单位。
下面是"key_len"列的一些常见值及其含义:
-
固定长度:如果索引字段是固定长度的(如整数、日期等),那么"key_len"列将显示该固定长度。
-
可变长度:如果索引字段是可变长度的(如字符串),那么"key_len"列将显示该字段的最大长度。
"key_len"列的值是根据索引字段和索引类型来计算的。它可以帮助我们了解索引的大小和使用情况。
2.4 ref列
在MySQL的EXPLAIN查询结果中,"ref"列用于表示连接条件所引用的列或常量。它显示了查询中使用的引用,用于连接表或进行进一步的过滤。
下面是"ref"列可能出现的不同值及其含义:
-
列名:表示引用了查询中的某个表的列,通常用于等值连接。
-
常量:表示引用了查询中的一个常量值,用于与表中的列进行匹配。
-
空白:表示没有引用列或常量,通常出现在全表扫描的情况下。
需要注意的是,如果查询中存在多个表连接,"ref"列可能会显示多个列名或常量,以逗号分隔。
以上就是优化sql的全部内容