大聪明教你学Java | 谈谈SQL优化

大聪明教你学Java | 谈谈SQL优化 前言

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

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

SQL优化很重要

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

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

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

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

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

select 语句执行顺序

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

SELECT DISTINCT FROM JOIN ON WHERe GROUP BY HAVINg ORDER BY LIMIT

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

1. FROM：选取表，将多个表数据通过笛卡尔积变成一个表
2. ON：对笛卡尔积的虚表进行筛选
3. JOIN：指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
4. WHERe：对上述虚表进行筛选
5. GROUP BY：分组
6. HAVINg：对分组后的结果进行聚合筛选
7. SELECt：返回数据列表，返回的单列必须在group by子句中，聚合函数除外
8. DISTINCT：去重
9. ORDER BY：排序
10. 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;

⑥ 其他

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

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

关于select语句的其他优化

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

关于增删改语句的优化

① 在执行插入语句时，如果同时执行大量的插入 *** 作，建议使用多值的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是变长字段，变长字段所占空间更小一些，在一个相对较小的字段内搜索效率显然要高些。

小结

本人经验有限，有些地方可能讲的没有特别到位，如果您在阅读的时候想到了什么问题，欢迎在评论区留言，我们后续再一一探讨‍

希望各位小伙伴动动自己可爱的小手，来一波点赞+关注 (✿◡‿◡) 让更多小伙伴看到这篇文章~ 蟹蟹呦(●’◡’●)

如果文章中有错误，欢迎大家留言指正；若您有更好、更独到的理解，欢迎您在留言区留下您的宝贵想法。

你在被打击时，记起你的珍贵，抵抗恶意；
你在迷茫时，坚信你的珍贵，抛开蜚语；
爱你所爱 行你所行 听从你心 无问东西