数据库SQL 优化

数据库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. 查询数据过多