sql优化

1. sql优化

• 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
  • 主键索引
  • 唯一索引
  • 聚集索引和非聚集索引
• SELECT子句中避免使用 ‘ * ‘：需要写全查询的字段列名
• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
  • 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库
• 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
• 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
• in 和 not in 也要慎用，否则会导致全表扫描，用 exists 代替 in 是一个好的选。
• 在oracle尽量避免使用游标，因为游标的效率较差
• 尽量避免大事务操作，提高系统并发能力
• 不要有超过5个以上的表连接（JOIN）

2. 将数据库表行转列

• mysql使用max()函数与case…when…then…else…end实现

eg: 表A

select year ,

　　　　max(case month when '1' then amount else 0 end ) m1,

　　　	max(case month when '2' then amount else 0 end )m2,

　　　　max(case month when '3' then amount else 0 end ) m3

　　　from A group by year

• oracle中pivot行转列的实现

SELECT a.* FROM A  a pivot(MAX(a.amount) FOR month IN ('1','2','3'))a

3. 说一下 MySQL 的行锁和表锁？

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

• 表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
• 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

4. 说一下乐观锁和悲观锁？

• 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
• 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

5. mysql分页和oracle分页

• mysql是通过limit实现的
• oralce 有四种

select ROWNUM,t.* from student t where rownum <5;

select count(*) from student;--786432条，
--查 78000～78030 速度由快到慢：1-2-3-4
--1.按rownum 来分
select  *                  --0.094S            
  from (select rownum as rn , st.*
          from (select * from student) st
         where rownum <78030) t
 where rn > 78000;
--2.按rownum 来分
select *                   --0.343S     
  from (select rownum as rn, st.* from (select * from student) st)
 where rn between 78000 and 78030;
--3 按rowid来分 
select *                   --0.359S 
  from student
 where rowid in
       (select rid
          from (select rownum rn, rid
                  from (select rowid rid, classid from student order by classid)
                 where rownum < 78030)
         where rn > 78000)
 order by classid;   
--4 按分析函数来分
select *                 -- 0.624S   
  from (select t.*, row_number() over(order by classid) rk from student t)
 where rk < 78030
   and rk > 78000;