03-13 09:58 已编辑门头沟学院 Java 发布于江苏

关注

SQL调优

后端操作主要是和数据库打交道，对于关系型数据库需要编写对应的 sql。数据库中的数据是存储在磁盘中的，每次查询都需要进行 IO，所以我们需要尽可能的减少 IO次数。

在面试中sql调优基本是每一场面试都要问到的,总结了一些sql调优的案列，掌握下面的足够应对面试官了

alt

sql 优化最简单的就是从索引角度去考虑，需要建立索引，防止索引失效。

索引

索引是用来优化查询的，索引就类似于书本的目录，建立索引可以实现快速查找。首先从索引的角度去思考。索引也是最简单的 sql 优化场景。

创建索引的规则:

创建索引,正常一张表的索引不要超过六个,索引并不是越多越好的,过多的索引会降低 insert 和 update 的操作,因为插入和更新操作可能会重建索引.

2.要个一些高效的列建立索引，比如sex性别这种就没有必要了。

3.可以建立联合索引，提高多个条件查询的效率

4.使用 explain 查看 sql 语句的执行过程,判断sql语句是否走索引列

避免索引失效

左模糊查询

模糊匹配的时候 %放在了最前面。 select * from table where name like "%周"，不会走索引。

如果是需要大量的这样的模糊查看，我们推荐可以用 ElasticSearch 搜索引擎，支持大数据的查询。如果比较熟悉es，可以将面试官往es上面引

使用不等值查询

使用不等值比如大于小于,来进行范围的查询，索引也会失效。

没有用 and用 or 来连接，也会失效

使用 or 连接,or 连接后面字段如果没有建立索引，那么也会失效，不走索引。

索引列存在计算

数据类型不匹配,原来是数字的，传进来的是一个字符串，需要进行类型转换等操作。

数据类型不匹配

当查询条件中传递的值类型与索引列的数据类型不匹配时，数据库会自动进行隐式类型转换。例如，索引列是数字类型（如 INT），但传入的条件是字符串（如 '123'），数据库会将字符串转换为数字，以便能够进行比较。

隐式类型转换会导致索引失效，因为索引在构建时是基于原始数据类型构建的，类型转换意味着数据库必须对所有记录进行逐一扫描以完成转换操作，无法直接利用索引。

可以用 union all 代替 union

union是将多个表的查询拼接在一起的，但是union默认情况下面需要进行去重，去重就需要排序，这样对于内存消耗是比较大的。推荐使用union all实现

alt

避免使用 select *

select * 是查询所有的字段,实际场景中表中的字段可能有几十个,需要哪些列就查询哪些列即可. 还有 select 不会走覆盖索引,会存在大量的回表操作,导致 sql 的性能降低.

https://blog.csdn.net/qq_21891743/article/details/132484295 Mysql 中的回表。

覆盖索引（Covering Index）是指一个查询语句中的所有需要的数据都能够从索引中获取，而不需要回表（访问实际的数据表）

优化之后每次查询都 select 自己需要的字段 from table where xxx,减少了回表的操作。

小表驱动大表

https://blog.csdn.net/asd051377305/article/details/115320564

Mysql 的表关联的算法是 Nest Loop Join,是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据过滤条件到下一个表中查询数据。

小表驱动大表

for（140 体表）{

for(20 万条数据){

}

} 如果是大表驱动小表,那么查询的效率就会低，每一次都要遍历所有的大表，再去小标中找

多使用 limit 条件

多条数据查询是否存在地时候

比如查询某些数据中的第一条，不需要将所有的数据全部查询出来然后取出第一条，可以使用 limit 1，直接查询到第一条数据返回即可。

alt

连接查询代替子查询

子查询会产生临时表，查询结束后需要删除临时表，如果临时表比较小那么还好，如果临时表比较大，是比较消耗性能的。

查询员工地名称和部门信息

//使用子查询
SELECT e.employee_name, 
       (SELECT d.department_name 
        FROM departments d 
        WHERE d.department_id = e.department_id) AS department_name     //这个临时表是查询部门的，部门正常数量是比较少的
FROM employees e;
//使用连接查询
SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d 
  ON e.department_id = d.department_id;

提升 group by 的效率

很多业务场景需要使用 group by 关键字，用来去重和分组的。配合 having 条件一起使用。

比如 group by user_id user_ name having user_id<=200 ，写法性能不好，这个是所有订单分组之后再去过滤的。

可以先通过 where 缩小范围再进行分组查询这样效率更高。

使用高效的分页查询

对于深度分页，比如 limit 10000000 100，查询这种会扫描前面的所有数据，然后筛选出来 100 条数据，这样的效率是比较低的。可以使用 where id>上一次分页的 id。

正常执行 sql 语句的过程:通过而今索引过滤出来 update_time 的条件，找到符合的 ID，然后去逐渐索引中查找满足记录的行数，需要扫描 1000010 行，然后扔掉前面的，去除最后十行数据。

alt

前端在查询的时候可以传入上一次的 id。这个对于最简单的分页查询可以实现，但是实际开发中分页查询还需要携带其他的条件，比如分组、排序等。并且在分布式系统下面 id 也不是自增长的，这样的效率就会很低。

可行的方案:

把条件转移到主键索引树,原来 select from table where update_time >'' limit 100 100

通过子查询进行优化，将条件还是放到聚簇索引上面去。深度分页的内容放在 where 条件语句里面，因为二级索引存储的就是主键值，可以对二级索引进行查询拿到主键值之后，在通过 id>=xx，limit 10.查询十条记录。

select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1)  a.update_time >= '2020-09-19'  LIMIT 10;

由执行计划得知，子查询 table a查询是用到了<font style="color:rgb(10, 191, 91);background-color:rgb(243, 245, 249);">idx_update_time</font>索引。首先在索引上拿到了聚集索引的主键ID,省去了回表操作，然后第二查询直接根据第一个查询的 ID往后再去查10个就可以了!

延迟关联的优化思路，跟子查询的优化思路其实是一样的：都是把条件转移到主键索引树，然后减少回表。不同点是，延迟关联使用了inner join代替子查询。

优化后的SQL如下：

SELECT  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.update_time >= '2020-09-19' ORDER BY a.update_time LIMIT 100000, 10) AS  acct2 on acct1.id= acct2.id;

alt