搞懂这些SQL优化技巧，面试横着走
BATJTMD?等大厂的面试难度越来越高，但无论从大厂还是到小公司，一直不变的重点就是对 SQL 优化经验的考察。一提到数据库，面试官就会问“先说一说你对?SQL 优化的见解吧？”。
图片来自 Pexels
SQL 优化已经成为衡量程序猿优秀与否的硬性指标，甚至在各大厂招聘岗位职能上都有明码标注，如果是你，在这个问题上能吊打面试官还是会被吊打呢？
有朋友疑问到，SQL 优化真的有这么重要么？如下图所示，SQL 优化在提升系统性能中是：成本最低和优化效果最明显的途径。
优化成本：硬件>系统配置>数据库表结构>SQL 及索引。
优化效果：硬件<系统配置<数据库表结构<SQL 及索引。
String?result?=?"嗯，不错，";
if?("SQL优化经验足")?{
????if?("熟悉事务锁")?{
????????if?("并发场景处理666")?{
????????????if?("会打王者荣耀")?{
????????????????result?+=?"明天入职"?
????????????}
????????}
????}
}?else?{
????result?+=?"先回去等消息吧";
}?
Logger.info("面试官："?+?result?);
减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘 IO。
返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘 IO 及网络 IO。
减少交互次数：批量 DML 操作，函数存储等减少数据连接次数。
减少服务器 CPU 开销：尽量减少数据库排序操作以及全表查询，减少 CPU 内存占用。
利用更多资源：使用表分区，可以增加并行操作，更大限度利用 CPU 资源。
最大化利用索引。
尽可能避免全表扫描。
减少无效数据的查询。
SELECT 语句，语法顺序如下：
1.?SELECT?
2.?DISTINCT?<select_list>
3.?FROM?<left_table>
4.?<join_type>?JOIN?<right_table>
5.?ON?<join_condition>
6.?WHERE?<where_condition>
7.?GROUP?BY?<group_by_list>
8.?HAVING?<having_condition>
9.?ORDER?BY?<order_by_condition>
10.LIMIT?<limit_number>
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 优化策略适用于数据量较大的场景下，如果数据量较小，没必要以此为准，以免画蛇添足。
避免不走索引的场景
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%'。
如下：
SELECT?*?FROM?t?WHERE?id?IN?(2,3)
如下：
SELECT?*?FROM?t?WHERE?id?BETWEEN?2?AND?3
如下：
--?不走索引
select?*?from?A?where?A.id?in?(select?id?from?B);
--?走索引
select?*?from?A?where?exists?(select?*?from?B?where?B.id?=?A.id);
如下：
SELECT?*?FROM?t?WHERE?id?=?1?OR?id?=?3
如下：
SELECT?*?FROM?t?WHERE?id?=?1
???UNION
SELECT?*?FROM?t?WHERE?id?=?3
如下：
SELECT?*?FROM?t?WHERE?score?IS?NULL
如下：
SELECT?*?FROM?t?WHERE?score?=?0
--?全表扫描
SELECT?*?FROM?T?WHERE?score/10?=?9
--?走索引
SELECT?*?FROM?T?WHERE?score?=?10*9
如下：
SELECT?username,?age,?sex?FROM?T?WHERE?1=1
⑦查询条件不能用 <> 或者 !=
如下：复合（联合）索引包含 key_part1，key_part2，key_part3 三列，但 SQL 语句没有包含索引前置列"key_part1"，按照 MySQL 联合索引的最左匹配原则，不会走联合索引。
select?col1?from?table?where?key_part2=1?and?key_part3=2
如下 SQL 语句由于索引对列类型为 varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。
select?col1?from?table?where?col_varchar=123;?
如下：
--?不走age索引
SELECT?*?FROM?t?order?by?age;
--?走age索引
SELECT?*?FROM?t?where?age?>?0?order?by?age;
第一步：根据 where 条件和统计信息生成执行计划，得到数据。
第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看 order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。
第三步：返回排序后的数据。
USE INDEX 在你查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。
例子: SELECT col1 FROM table USE INDEX (mod_time, name)...
IGNORE INDEX 如果只是单纯的想让 MySQL 忽略一个或者多个索引，可以使用 IGNORE INDEX 作为 Hint。
例子: SELECT col1 FROM table IGNORE INDEX (priority) ...
FORCE INDEX 为强制 MySQL 使用一个特定的索引，可在查询中使用FORCE INDEX 作为 Hint。
例子: SELECT col1 FROM table FORCE INDEX (mod_time) ...
例如：
SELECT?*?FROM?students?FORCE?INDEX?(idx_class_id)?WHERE?class_id?=?1?ORDER?BY?id?DESC;
SELECT 语句其他优化
②避免出现不确定结果的函数
③多表关联查询时，小表在前，大表在后
增删改 DML 语句优化
方法一：
insert?into?T?values(1,2);?
insert?into?T?values(1,3);?
insert?into?T?values(1,4);
方法二：
Insert?into?T?values(1,2),(1,3),(1,4);?
减少 SQL 语句解析的操作，MySQL 没有类似 Oracle 的 share pool，采用方法二，只需要解析一次就能进行数据的插入操作。
在特定场景可以减少对 DB 连接次数。
SQL 语句较短，可以减少网络传输的 IO。
事务占用的 undo 数据块。
事务在 redo log 中记录的数据块。
释放事务施加的，减少锁争用影响性能。特别是在需要使用 delete 删除大量数据的时候，必须分解删除量并定期 commit。
③避免重复查询更新的数据
简单方法实现：
Update?t1?set?time=now()?where?col1=1;?
Select?time?from?t1?where?id?=1;
使用变量，可以重写为以下方式：
Update?t1?set?time=now?()?where?col1=1?and?@now:?=?now?();?
Select?@now;?
写入操作优先于读取操作。
对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。
对某张数据表的多个读取操作可以同时地进行。
LOW_PRIORITY 关键字应用于 DELETE、INSERT、LOAD DATA、REPLACE 和 UPDATE。
HIGH_PRIORITY 关键字应用于 SELECT 和 INSERT 语句。
DELAYED 关键字应用于 INSERT 和 REPLACE 语句。
查询条件优化
例如：
SELECT?col1,?col2,?COUNT(*)?FROM?table?GROUP?BY?col1,?col2?ORDER?BY?NULL?;
SELECT?col1?FROM?customerinfo?WHERE?CustomerID?NOT?in?(SELECT?CustomerID?FROM?salesinfo?)
尤其是当 salesinfo 表中对 CustomerID 建有索引的话，性能将会更好，查询如下：
SELECT?col1?FROM?customerinfo?
???LEFT?JOIN?salesinfoON?customerinfo.CustomerID=salesinfo.CustomerID?
??????WHERE?salesinfo.CustomerID?IS?NULL
高效：
SELECT?COL1,?COL2,?COL3?FROM?TABLE?WHERE?COL1?=?10?
UNION?ALL?
SELECT?COL1,?COL2,?COL3?FROM?TABLE?WHERE?COL3=?'TEST';
低效：
SELECT?COL1,?COL2,?COL3?FROM?TABLE?WHERE?COL1?=?10?
UNION?
SELECT?COL1,?COL2,?COL3?FROM?TABLE?WHERE?COL3=?'TEST';
简单的 SQL 容易使用到 MySQL 的 QUERY CACHE。
减少锁表时间特别是使用 MyISAM 存储引擎的表。
可以使用多核 CPU。
案例 1：
select?*?from?t?where?thread_id?=?10000?and?deleted?=?0?
???order?by?gmt_create?asc?limit?0,?15;
select?t.*?from?(select?id?from?t?where?thread_id?=?10000?and?deleted?=?0
???order?by?gmt_create?asc?limit?0,?15)?a,?t?
??????where?a.id?=?t.id;
建表优化
②尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
③查询数据量大的表 会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段分页进行查询，循环遍历，将结果合并处理进行展示。
SELECT?*?FROM?(SELECT?ROW_NUMBER()?OVER(ORDER?BY?ID?ASC)?AS?rowid,*?
???FROM?infoTab)t?WHERE?t.rowid?>?100000?AND?t.rowid?<=?100050
作者：_陈哈哈
编辑：陶家龙
出处：https://sohu.gg/FGG98i
精彩文章推荐：
关注公众号：拾黑（shiheibook）了解更多
[广告]赞助链接：
四季很好，只要有你，文娱排行榜：https://www.yaopaiming.com/
让资讯触达的更精准有趣：https://www.0xu.cn/
51CTO技术栈
关注网络尖刀微信公众号随时掌握互联网精彩
- 1 中国经济向世界提供“机遇清单” 7904021
- 2 中方录音一出 日本有人看不下去了 7808039
- 3 狂甩45次 无汞体温计才降到36℃ 7713197
- 4 2025这些“经济”持续成长壮大 7618446
- 5 男子小区毒死宠物狗 获刑4年 7522736
- 6 北冥有鱼竟然是真的 7425420
- 7 网警：男子AI生成车展低俗视频被拘 7332058
- 8 5000万粉丝女网红涉嫌逃税仍直播 7234721
- 9 毛宁向世界分享中国最新两栖挖掘机 7143751
- 10 寒潮来袭！多地气温将创下半年来新低 7045891
