Java岗大厂面试百日冲刺 - 日积月累，每日三题【Day4，java项目中遇到的技术难点

• 最大化利用索引；

• 尽可能避免全表扫描；

• 减少无效数据的查询；

首先要清楚SELECT语句 - 执行顺序：

FROM

<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。

ON

<筛选条件> # 对笛卡尔积的虚表进行筛选

JOIN <join, left join, right join…>

<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中

WHERE

<where条件> # 对上述虚表进行筛选

GROUP BY

<分组条件> # 分组

<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的

HAVING

<分组筛选> # 对分组后的结果进行聚合筛选

SELECT

<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外

DISTINCT

#数据除重

ORDER BY

<排序条件> # 排序

LIMIT

<行数限制>

SQL优化策略:

声明：以下SQL优化策略适用于数据量较大的场景下，如果数据量较小，没必要以此为准，以免画蛇添足。

一、避免不走索引的场景

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE ‘%陈%’

优化方式：尽量在字段后面使用模糊查询。如下：（原因涉及B+Tree索引最左前缀原则，可以参考《MySQL最左匹配原则，道儿上兄弟都得知道的原则》）

SELECT * FROM t WHERE username LIKE ‘陈%’

如果需求是要在前面使用模糊查询，

• 使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置，可参阅《MySQL模糊查询用法大全（正则、通配符、内置函数等）》

• 使用FullText全文索引，用match against 检索

• 数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级

• 当表数据量较少（几千条儿那种），别整花里胡哨的，直接用like ‘%xx%’。

2. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1

UNION

SELECT * FROM t WHERE id = 3

3. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT