sql 批量模糊查询_2020最新最全面的SQL优化干货总结 [建议收藏]

BATJTMD 等大厂的面试难度越来越高，但无论从大厂还是到小公司，一直不变的重点就是对 SQL 优化经验的考察。一提到数据库，面试官就会问“先说一说你对 SQL 优化的见解吧？”

SQL 优化已经成为衡量程序猿优秀与否的硬性指标，甚至在各大厂招聘岗位职能上都有明码标注，如果是你，在这个问题上能吊打面试官还是会被吊打呢？

有朋友疑问到，SQL 优化真的有这么重要么？如下图所示，SQL 优化在提升系统性能中是：成本最低和优化效果最明显的途径。

如果你的团队在 SQL 优化这方面搞得很优秀，对你们整个大型系统可用性方面无疑是一个质的跨越，真的能让你们老板省下不止几沓子钱。

• 优化成本：硬件>系统配置>数据库表结构>SQL 及索引。

• 优化效果：硬件

String result = "嗯，不错，";if ("SQL优化经验足") {
        if ("熟悉事务锁") {
            if ("并发场景处理666") {
                if ("会打王者荣耀") {
                    result += "明天入职"            }        }    }} else {
        result += "先回去等消息吧";}Logger.info("面试官：" + result );

别看了，上面这是一道送命题。

好了我们言归正传，首先，对于MySQL层优化我一般遵从五个原则：

• 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘 IO。

• 返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘 IO 及网络 IO。

• 减少交互次数：批量 DML 操作，函数存储等减少数据连接次数。

• 减少服务器 CPU 开销：尽量减少数据库排序操作以及全表查询，减少 CPU 内存占用。

• 利用更多资源：使用表分区，可以增加并行操作，更大限度利用 CPU 资源。

总结到 SQL 优化中，就如下三点：

• 最大化利用索引

• 尽可能避免全表扫描

• 减少无效数据的查询

理解 SQL 优化原理 ，首先要搞清楚 SQL 执行顺序。

SELECT 语句，语法顺序如下：

DISTINCT FROM  JOIN ON WHERE GROUP BY HAVING ORDER BY LIMIT 

SELECT 语句，执行顺序如下：

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

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

避免不走索引的场景

---

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

如下：

SELECT * FROM t WHERE username LIKE '%陈%' 

优化方式：尽量在字段后面使用模糊查询。

如下：

SELECT * FROM t WHERE username LIKE '陈%'

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

• 使用 MySQL 内置函数 INSTR(str，substr)来匹配，作用类似于 Java 中的 indexOf()，查询字符串出现的角标位置。

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

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

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

②尽量避免使用 in 和 not in，会导致引擎走全表扫描