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