mysql中set rn_mysql数据库开发常见问题及优化

1 正确使用索引

数据库性能很关键的一点在于sql是否能正确使用索引，我们应该尽量避免无法使用索引的sql写法。

示例：表t的id和name数据类型分别为number和varchar2，且id为从1开始递增的主键，表记录数为10万索引查询范围尽可能小，否则可能用不到索引

无法使用索引(查询范围太大)：select * from t where id>10;

正确使用索引(查询范围很小)：select * from t where id<10;尽量避免对索引列进行函数计算，请尽可能将函数运算移到条件右边或者创建函数索引。

无法使用索引(左边条件使用了函数)：select * from t where id+5=10;

正确使用索引(将函数运算移到条件右边)：select * from t1 where id=10-5

或者创建函数索引create index idx_t1 on t1(id+5)两个字段直接进行比较无法使用索引(本例中id1为主键)

无法使用索引(条件两边含不同字段)：select * from t where id1=id2+10

正确使用索引(将两个字段移到条件左边并创建函数索引)：select * from t1 where id1-id2=10;create index idx_t1 on t1(id1-id2)不同类型的数值比较可能无法使用索引

无法使用索引(条件两边含不同类型字段，db会做隐式函数转换，使得索引无法正常使用)：select * from t where name=123

正确使用索引(两边数据类型一致)：select * from t where name='123'对于复合索引，sql语句一般情况下必须使用第一个索引字段，索引才会生效(本例中id,name为联合索引)

无法使用索引(id为索引列的第一个字段，单独查询name一般不会使用索引)：select * from t where name='123'

正确使用索引(条件中使用了索引列的第一个字段,where条件中的字段先后顺序没有关系)：select * from t where id=1select * from t where id=1 and name='abc'select * from t where name='abc' and id=1查询条件中，尽量避免使用null作为条件，如果有查询需求，可以在null字段上创建默认值

无法使用索引(oracle不行，mysql可以)：select * from t where id is null;select * from t where id is not null;

正确使用索引：alter table t modify id int default 0;select * from t where id is null

转换为select * from t where id=0对于索引的比较，尽量避免使用 <>(!=)

无法使用索引(无论任何情况都只能全表扫描)：select * from t where id !=0;

正确使用索引(如果非0值占少数，则两个条件都可能用到索引)：select * from t where id>0 or id<0like查询时，%不能出现在条件最左边

无法使用索引(无论任何情况都只能全表扫描)：select * from t where name like '%abc;select * from t where name like '%abc%;如果要高效实现如上查询，建议使用全文本索引或者第三方搜索引擎。

正确使用索引：select * from t where name like 'abc%'如果可以，尽可能只查询索引列

需求：只需要查询表t的id和name值,id和name上有联合索引

低效查询(先扫描索引，再回表)：select * from t1 where id=1 and name=>'abc'

高效查询(只需要扫描索引)：select id,name from t1 where id=1 and name>'abc'

2 尽量减少数据库的访问使用乐观锁，避免大多数无效数据库访问

低效sql：select count(1) from t1 where conditionif count(1)>0 then update t1 set ...

高效sql：update t1 set ...if result=0 thenexception高效存在性判断

低效sql：select count(*) into t_count from t where condition;if t_count> 0 then select cols into t_cols from t where condition;elseotherstatement;end;

高效sql：select 1 into t_count from t where condition and rownum<2;if exist(1) then select cols into t_cols from t where condition;elseotherstatement;end;使用casewhen函数减少数据库访问次数

低效sql(需要访问3次数据库)select sum(sum1) from( select sum(data_object_id)*0.001 sum1 from t1 where data_object_id<=10000union select sum(data_object_id)*0.002 sum1 from t1 where data_object_id>10000 and data_object_id<=50000union select sum(data_object_id)*0.003 sum1 from t1 where data_object_id>50000);

高效sql(访问一次数据库)select sum(case when data_object_id<=10000 then data_object_id*0.001 when data_object_id>10000 and data_object_id<=50000 then data_object_id*0.002 when data_object_id>50000 then data_object_id*0.003 else 0end) from t1;

3 尽量减少排序用unionall替代union

二者的区别是后者会合并后排序去重，前者不需要这个工作减少排序结果集

示例1：

低效sql(全部记录进行排序后过滤)：select name,count(1) from t1 group by name having name>'abc'

高效sql(过滤数据后再排序)：select name,count(1) from t1 where name>'abc' group by name;

示例2：

低效分页查询(对所有满足条件的记录进行排序后分页)：select object_id,object_name,rn from (select a.*, rownum rn from (select * from t1 where object_id>0 order by object_id) a )where rn <= &last and rn >= &first;

高效分页查询(只对满足条件的记录排序前rn条)：select object_id,object_name,rn from (select a.*, rownum rn from (select * from t1 where object_id>0 order by object_id) a where rownum <= &last)where rn >= &first;尽可能利用索引排序

低效sql(排序操作在表上)：select * from (select rownum no,t.* from (select * from t where lmodify> (sysdate - interval '60' day) order by lmodify desc) t where rownum  24000

高效sql(排序操作在索引上)select * from t e,(select * from (select c.*,rownum rcount from (select rowid rn,lmodify from t where lmodify> (sysdate - interval '30' day)order by lmodify desc ) cwhere rownum<24100) where rcount>24000) dwhere e.rowid=d.rn

4 in/exists使用规范

示例：当有a、b 两个结果集，当结果集b 很大时，a 较小时，适用exists，如：select * from a where exists(select 1 from b where a.column = b.column);

当结果集 a 很大时，b 很小时，适用in,如：select *from awhere a.column in(select b.columnfrom b)

5 避免锁问题事务尽可能小，尽量避免大事务，频繁提交的大事务会明显带来并发的降低。

批量处理的任务最好能较少执行频率，且最好放在访问量低的时候做。

避免死锁

死锁产生原因：session间由于对锁的获取产生了资源竞争，导致发生session的互相等待的僵持状态，即死锁。一般非事务锁(比如表锁)由于锁是一次性完全占有和释放，不会在同一资源上产生互相等待，因此不会产生死锁，比如mysql中的MyISAM存储引擎；而事务锁(比如页锁和行锁)由于在同一事务中锁是逐渐获取的，如果两个session获取锁的顺序相反，则可能产生死锁。Oracle和mysql中的InnoDB存储引擎都属于这一类。

死锁示例：session1session2mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from table_1 where where id=1 for update;

...

做一些其他处理...mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from table_2 where id=1 for update;

...

select * from table_2 where id =1 for update;

因session_2已取得排他锁，等待做一些其他处理...

mysql> select * from table_1 where where id=1 for update;

死锁

死锁的处理：oracle和mysql对于死锁会自动探测并处理，一般是选择其中一个事务进行回滚。

死锁的危害：降低系统并发性和用户体验。

如何避免死锁：在不同的事务中对表的操作顺序保持一致。比如在两个方法中都对order和order_detail进行更新，那么要保证两个方法中两个表的更新顺序是一致的。