SQL优化

SQL优化

原文链接：https://zhuanlan.zhihu.com/p/265852739

1、前言

1.1 SQL优化为什么那么重要？

目前，SQL优化已经成为了衡量程序员是否优秀的硬性标准，各个招聘信息上都明码标注。

SQL优化可以极大地提高数据的查询性能和效率。

当我们在处理大量数据时，一个优化良好的SQL查询可以显著减少查询所需的时间和资源，从而提高应用程序的响应速度和整体性能。此外，SQL优化还可以帮助我们充分利用数据库的功能，避免潜在的安全漏洞，并确保数据一致性和完整性。

1.2 SQL优化具体优化哪里?

由图可知：
优化成本：硬件 > 系统配置 > 数据库表结构 > SQL及索引
优化效果：硬件 < 系统配置 < 数据库表结构 < SQL及索引

可以看出，优化的效果最明显的就是SQL和索引。

SQL优化遵循的原则：

1. 最小化数据访问：只检索需要的数据，尽量避免全表扫描和不必要的联合查询。
2. 使用恰当的索引：根据查询条件创建适当的索引，以加速数据检索操作。
3. 避免使用通配符：避免在查询语句中使用通配符，如“%”和“_”，因为它们会降低查询性能。
4. 避免使用子查询：尽量避免使用子查询，因为它们会增加数据库的负载和响应时间。
5. 选择合适的数据类型：选择合适的数据类型可以减小数据存储和处理的开销，提高查询效率。
6. 编写简洁的SQL语句：简单、直接的SQL语句更易于优化和维护。
7. 定期进行数据库维护：定期清理无用数据、重新构建索引、优化表结构等操作可以提高数据库性能。

1.3 SQL执行顺序

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

select语句

select 
distinct filename
from tableName
joinType join tableName
on joinCondition
where whereCondition
group by filename
having havingCondition
order by orderByCondition	
limit

执行顺序

from 表名
将多个表数据通过笛卡尔积变成一个表
on 条件
对笛卡尔积的虚表进行筛选
join (内连接、左连接、有连接) 表名
指定join，用于添加数据到on之后的虚表中
group by  分组条件
having 分组筛选，
对分组后的结果进行聚合筛选
select 数据列表，
返回的单列必须在group by子句之外，聚合函数除外
distinct 数据去重
order by 排序条件
limit 行数限制

2、SQL优化

2.1 避免不走索引的场景

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

select * from tableName where username like '%张%'

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

select * from tableName where username like '张%'

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

• 使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置
• 使用FullText全文索引，用match against 检索
• 数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级
• 当表数据量较少，可以直接用like '%xx%'

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

select * from tableName where id in (1,2)

优化方式：如果是连续数值，可以使用between代替，如下：

select * from tableName where id between 1 and 10

如果是子查询，可以使用exists代替

-- 不走索引
select * from A where A.id in