对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
可能会导致引擎不使用索引而进行全表扫描的操作及部分优化思路
- is null 判断:
尽可能使用NOT NULL 填充数据库 - != 或 <>
- where子句中使用 ‘or’ 来连接条件时,某子条件中存在无索引的字段:
1
2
3
4
5select id from t where col1=1 or col2='123'
--可修改为:
select id from t where col1=1
union
select id from t where col2='123'
in / not in :
- 对连续的数值可使用 between 代替
- 用 exists 代替
1
2
3
4
5select id from t1
where col1 in (select col1 from t2)
--可修改为:
select id from t1
where exists(select 1 from t2 where t1.col1 = t2.col1)
通配符’%’:
- ‘%123’ 无法使用正常索引,可使用反向索引
- ‘123%’ 可使用正常索引
- ‘%123%’ 无法使用索引:
可以考虑使用全文检索
- where 子句中对字段进行表达式操作
- where 子句中在’=’左边进行函数、算术运算或其他表达式运算
- 设置了复合索引,但where子句中未使用到该索引的第一个字段作为条件:
需要使用复合索引的第一个字段,且尽量保持字段顺序与复合索引中的字段顺序一致
Update语句尽量只Update需要的字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志
多表进行JOIN时尽量先进行处理(筛选、分页等),缩小数据量,否则会很大程度地影响性能
建立索引时需要慎重。索引在提高相应的 select 效率的同时,也降低了 insert / update / delete 的效率,因为insert
尽可能使用数值型字段,字符型字段会降低查询和连接的性能,并增加存储开销,因为引擎在处理查询和连接时会逐字符比较,而数值仅需比较一次
尽可能使用 varchar / nvarchar 代替 char / nchar ,变长字段可节省存储空间且在查询时效率相对更高
不使用 select from t ,用具体的字段代替 ‘‘
临时表 vs. 表变量
- 比较:
- 存储位置 : 临时表存储在硬盘中,表变量存储在内存中,当数据量超出后,同样会占用硬盘空间,但此时内存基本耗尽,降低IO效率
- 索引 : 表变量不支持索引和统计数据,但可以有主键;临时表可以支持索引和统计数据
- 建议:
- 对较小的临时计算用数据集考虑使用表变量
- 数据集较大,在代码中用于临时计算,且仅用来做简单的全数据集扫描不考虑优化,比如没有/很少分组的聚合(COUNT/SUM/MAX)等,可以考虑使用表变量
- 应用环境内存压力如果较大,尽量使用临时表
- 大数据集应使用临时表,必要时创建索引