数据库SQL 优化总结
性能通常从两个响应时间和吞吐量来进行衡量
客户端 批量提交数据;延迟加载,缓存
网络:减少数据的传输量
服务器:索引的使用,高效率的处理脚本,
1.1索引
聚集索引和非聚集索引效率都非常的好, 但是唯一索引效率最高
1.2TEXT/CLOB等大字段,优化
1.文本字段如果不为ull,至少分配1页,即2K 或者4K,如果Text字段赋值以后,即使再改为null也会保留2K 如果有10 0000条记录的话,就会占用 2K*100K=200M 空间
如果表中有很多Text字段,如果都曾经不为空,表就会越来越大
优化方法2种:
1. 考虑varchar 代替text
2.对于一个表中需要多个Text字段,用一个Text 字段来合并,通过xml 或者json 方式存储
、
1.3 尽量减少全盘扫描和重复扫描
##
a. 充分利用case when让大表的便利次数尽可能的少
b. 尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以优先考虑提取数据到临时的表中 然后再进行查询
c. in 和 exists 等都会导致全盘扫描
d. 尽量避免where 字句中对字段进行null 判断
select id from student num is null
e. 应尽量避免在where 字句中使用!=或者<>
f. or 来连接查询语句的条件话,也容易引起全盘扫描
select id from student where num=10 or num=12;
可以使用union all 进行代替
select id from student where num=10 union select id from student where num=100
g. like一般情况下会使用全盘扫描
select * from student where name like '%abc%';
select * from student where name like '%abc';
但是下面的这种情况会使用到索引
select * from student where name like 'abc%';
h.where 字句中使用参数,尽量避免在where 字句中对字段进行表达式操作,否则也会引起全盘的扫描
select * from student where num/2=100;
select * from student where num =@num 改正为 select * from student with(索引名) where num=@num;
i. 对于连续的值 能用between 尽量不要使用in
j. 尽量避免在where 字句中对字段进行函数的操作
select id from t where substring(name,1,3)='abc';
select id from student where name like 'abc%';
1.4 union 和 union all
首先优先选择union all 不选择 union,union 有排序,去重等操作,多了中间的的处理过程和开销
1.5实时表不要加外键
如果实时操作非常多的表,不建议添加外键,这回导致插入时候的负担
如果外键的主表更新一条记录时,如果在关联表上外键对应字段上没有对应的索引,可能会导致全表被锁
create table teache(tId int primary key);
create table student(sId int foreign key(y) references teacher(tId));
如果sudent.sId上没有缩影,删除teacher的记录时,会导致 student 全表被锁;
1.6 删除相同的记录
DELETE from stu x where e.rowid>(SELECT MIN(y.rowid) FROM stu y where y.rowid=x.rowid)
1.7 减少对表的查询
update employee set sal=(select max(sal) from emplyee ),
set age =(select min(age) from emplyee) where dept_id=20
应该改为
update emplyee set (sal,age)=(select max(sal), min(age) from employee where dept_id=20)
1.8 count(*) 与count(column)区别
count(column)表示结果集中有多少个column 字段不为空的记录
count(*)表示结果集中有多少条记录
1.9 尽量使用join 代替子查询
join 查询的性能并不是很高,但是相比较子查询来说 ,优势仍然很明显
1.10 避免使用游标。 临时表
2.查询慢的原因
1. 内存不足
2. I/O 吞吐量小,形成瓶颈
3. 没有索引,或者没有用到索引
4. 查询数据过多