SQL优化
原文链接:https://zhuanlan.zhihu.com/p/265852739
1、前言
1.1 SQL优化为什么那么重要?
目前,SQL优化已经成为了衡量程序员是否优秀的硬性标准,各个招聘信息上都明码标注。
SQL优化可以极大地提高数据的查询性能
和效率
。
当我们在处理大量数据时,一个优化良好的SQL查询可以显著减少查询所需的时间和资源,从而提高应用程序的
响应速度
和整体性能
。此外,SQL优化还可以帮助我们充分利用数据库的功能,避免潜在的安全漏洞,并确保数据一致性
和完整性
。
1.2 SQL优化具体优化哪里?
由图可知:
优化成本:硬件 > 系统配置 > 数据库表结构 > SQL及索引
优化效果:硬件 < 系统配置 < 数据库表结构 < SQL及索引
可以看出,优化的效果最明显的就是SQL
和索引
。
SQL优化遵循的原则:
- 最小化数据访问:只检索需要的数据,尽量避免全表扫描和不必要的联合查询。
- 使用恰当的索引:根据查询条件创建适当的索引,以加速数据检索操作。
- 避免使用通配符:避免在查询语句中使用通配符,如“%”和“_”,因为它们会降低查询性能。
- 避免使用子查询:尽量避免使用子查询,因为它们会增加数据库的负载和响应时间。
- 选择合适的数据类型:选择合适的数据类型可以减小数据存储和处理的开销,提高查询效率。
- 编写简洁的SQL语句:简单、直接的SQL语句更易于优化和维护。
- 定期进行数据库维护:定期清理无用数据、重新构建索引、优化表结构等操作可以提高数据库性能。
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