Java数据库调优面试 java sql调优

转载

mob6454cc73e9a6 2023-10-23 08:56:29

文章标签 Java数据库调优面试 mysql sql SQL优化 SQL 文章分类 Java 后端开发

前言

现在无论是去BATJ面试还是去小公司面试，都避免不了被面试官问SQL优化的问题。SQL优化已经成为衡量程序猿优秀与否的硬性指标，甚至某些公司招聘时，在岗位职能上都有明码标注，这也就更证明了掌握SQL优化技能的重要性，借此机会就和大家分享一下我在优化SQL时的一些经验心得。

小弟献丑啦，嘿嘿~ (●’◡’●)

SQL优化很重要

可能有些小伙伴会产生疑问：SQL的优化真的这么重要吗？答案是肯定的，SQL优化不是重要，而是相当重要，太重要了…

不知道各位小伙伴有没有了解过如何优化MySQL数据库，如果想优化数据库无非是通过四种方式：提升硬件；调整系统配置；重构数据库表结构；SQL优化及使用索引。我们来给这四种优化方式排个序👇

按优化成本排序：提升硬件 > 调整系统配置 > 重构数据库表结构 > SQL优化及使用索引

按优化效果排序：SQL优化及使用索引 > 重构数据库表结构 > 调整系统配置 > 提升硬件

通过上面的排序，相信各位小伙伴也看出来了，通过优化SQL或者使用索引是成本最低且效果最好的数据库优化方式~ 想想看，如果你在团队里搞SQL优化是最6的，那一定可以让团队开发的系统在可用性方面得到一个质的跨越，还能帮助你们老板省下很多票子💴。

select 语句执行顺序

我们先简单了解一下 select 语句的执行顺序：

SELECT DISTINCT <select_list> FROM <left_table> <join_type> JOIN <right_table> ON <join_condition> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> ORDER BY <order_by_condition> LIMIT <limit_number>

👆上面是一段完整的 select 语句，我们看看它是怎么执行的~

FROM：选取表，将多个表数据通过笛卡尔积变成一个表
ON：对笛卡尔积的虚表进行筛选
JOIN：指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE：对上述虚表进行筛选
GROUP BY：分组
HAVING：对分组后的结果进行聚合筛选
SELECT：返回数据列表，返回的单列必须在group by子句中，聚合函数除外
DISTINCT：去重
ORDER BY：排序
LIMIT：限制条数

SQL优化策略

SQL优化的原则也很简单，只有三点：

最大化利用索引
尽量不要做全表扫描
减少无效数据的查询

如何根据这三个原则进行SQL优化呢~ 听小弟娓娓道来O(∩_∩)O

尽可能让SQL语句走索引

① 在字段开头使用模糊匹配会导致数据库引擎放弃索引进行全表扫描，所以要尽量避免在字段开头使用模糊匹配。

//优化前
SELECT * FROM user WHERE username LIKE '%li%';

//优化后
SELECT * FROM user WHERE username LIKE 'li%';

有小伙伴要提问了：在我的业务中需要用到字段前模糊查询怎么办？其实也很简单~

使用内置函数INSTR(str,substr)
使用FullText全文索引
若存储了海量的数据，可以使用ElasticSearch，即便是亿级数据量其检索速度也可达到秒级
如果只有几千条数据的话，就别费劲了，直接用 like %xx% 就完了

② 尽量使用 BETWEEN 来代替 IN

//优化前
SELECT * FROM user WHERE userid IN (1,2,3);

//优化后(如果id连续的时候，就用 BETWEEN 代替 IN)
SELECT * FROM user WHERE userid BETWEEN 1 AND 3;

如果是子查询的话可以用exists代替IN

//优化前
SELECT * FROM A WHERE id IN (SELECT id FROM B);

//优化后
SELECT * FROM A WHERE exists (SELECT * FROM B WHERE B.id = A.id);

③ 尽量使用 UNION 来代替 OR

//优化前
SELECT * FROM A WHERE id = 1 OR id = 3;

//优化后（用UNION代替OR）
SELECT * FROM A WHERE id = 1 UNION SELECT * FROM A WHERE id = 3;

④ 尽量不要用null值判断
使用null值判断会导致引擎放弃索引而进行全表扫描。

//优化前
SELECT * FROM A WHERE name IS NULL;

//优化后（给字段添加一个默认值，通过默认值进行判断）
SELECT * FROM A WHERE name = '0';

⑤ 尽量不要在where条件等号的左侧进行表达式、函数操作
同样的，如果在等号的左侧进行表达式、函数操作，也会导致引擎放弃索引而进行全表扫描。

//优化前
SELECT * FROM A WHERE grade / 11 = 9;

//优化后（给字段添加一个默认值，通过默认值进行判断）
SELECT * FROM A WHERE grade = 11 * 9;

⑥ 其他

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如果业务要求使用不等于判断，则需要再重新评估索引的建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。
合理设置字段值类型，比如字段类型为varchar，但给定的值为数值，这就涉及了隐式类型转换，从而造成不能正确走索引。
ORDER BY 条件要与where中条件一致，否则ORDER BY不会利用索引进行排序

//优化前
SELECT * FROM A ORDER BY grade;
 
//优化后
SELECT * FROM A where grade > 0 ORDER BY grade;

关于select语句的其他优化

避免使用select * ：使用select * 取出全部列，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O、内存和CPU消耗。
多表关联查询时，小表在前，大表在后：在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，扫描快效率较高。
使用表的别名
用where字句替换HAVING字句
调整where字句中的连接顺序：MySQL采用从左往右的顺序解析where过滤条件。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。
如果有复杂的查询语句，则可以选择使用临时表来暂存数据，提升查询效率

关于增删改语句的优化

① 在执行插入语句时，如果同时执行大量的插入操作，建议使用多值的insert语句

//优化前
insert into A values(xx,xx); 
 
insert into A values(xx,xx); 
 
insert into A values(xx,xx);
 
//优化后
//减少SQL语句解析的操作;减少对数据库的连接次数
insert into A values(xx,xx),(xx,xx),(xx,xx);

② 避免重复查询更新的数据
业务中经常出现更新行同时又要获得该行信息的需求，MySQL不支持UPDATE RETURNING语法，那么可以通过变量实现这个功能。

UPDATE A SET time = now() where id = 1 and @now: = now(); 
 
SELECT @now;

③使用truncate代替delete
使用delete语句执行删除操作时会被记录到undo块中，删除记录也记录到binlog中，尤其是需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，占用了大量的资源；如果用truncate代替delete后，则不会产生记录，大大降低了资源的占用，同时会使自增字段值归零（比如id字段会重新计数）

关于表的优化

① 在表中合理建立索引，索引字段优先考虑where、order by使用到的字段。
② 尽量使用数字型字段（如状态，正常：0 非正常：1）
③ 合理进行拆表，比如存储操作日志，可以将近3个月的日志存储至A表，3-6个月的日志存储至B表… 对于日志信息，一般都只关注近期的日志信息（更早的日志可以存储，但是实际使用过程中极少会被查看），合理拆表后会大大提升检索效率。
④ 用varchar代替char，因为varchar是变长字段，变长字段所占空间更小一些，在一个相对较小的字段内搜索效率显然要高些。