关于sql优化的整理 ------后期继续整理

1 在索引字段上使用模糊查询:
 number_id like '%alibab%' 这种全模糊不建议在数据库中查询, 可以用solr框架进行
 如果非要在数据库中查, 可以在数据库群中只读节点上进行查询, 防止占用太多的主业务数据库资源
2 尽量不访问索引之外的字段
 索引(member_id,subject)
 select subject from offer where member_id=234 这种查询就优于下面的语句
 select subject , gmtcreaated from offer where member_id=234
3 计算count(id)有时比count(*)慢
 count(id) === count(1) where id is not null
 如果没有(id)索引, 那么会用全表扫描, 而count(*)会使用最优的索引进行索引快速扫描
4 正确使用stop机制
 判断member_id在offer表中是否存在记录:
 select count(*) from offer where member_id=234 limit 1     优于
 selecct count(*) from offer where member_id=234
 因为第一条sql会得到第一条符合条件的记录后停止
5 分页:
 一般写法: select * from t where selerid=100 limit 100000,20
 普通的limit M,N的翻页写法, 往往在越往后面翻页的过程中速度越慢, 因为mysql会读取表中的前M+N条数据, M越大,性能就越差
 优化方法:
  select t1.* from t t1
    (select id from t selleid=100 limit 10000,20) t2
  where t1.id=t2.id;
 优化后的翻页写法, 先查询翻页中需要的N条数据的主键id, 在根据主键id回表查询所需要的N条数据, 此过程中查询N条数据的逐渐id在索引中万和城能,
6 数据量不大时, 在数据库中进行搜索, 为字段建立索引, 并按照索引进行查询
  数据量大时, 可以用第三方搜索技术进行操作, 如solr或司劳克斯

 

7 where语句的规则:
 where语句中, 尽量避免对索引字段的计算
 where自居中, 避免使用in, not in,  可以用exist和not exist代替
 要以数字格式生命字符集,  这样可以避免全盘扫描如下:
  select ename, emp.job from emp where emp.empno=3333
 尽量避免在where语句中对字段进行null值判断, 将可能引起全盘扫描
  select id from t where num is null;    可以在nun上设置默认值0,
  select id from t where num =0;
 尽量避免在where语句中使用 !=
 尽量避免在where语句中使用 or,  可以用 union all代替, 如下:
  select id  form t where num = 10; or num = 11;
  可以这样查询:
  select id from t where num = 10
  union all
  select id from t where num = 20
 尽量避免在where语句中使用表达式进行操作,  这样也会导致全盘扫描   如下:
  select id from t where num / 2 = 100
  可改为:
  select id frim t where num = 100 * 2
 尽量避免在where语句中使用函数进行操作,  这样也会导致全盘扫描

８　用case代替update
  更新一条数据,  update会产生日志, 所以每条记录会被写两次,  比如需要将超过10万美元的订单标记为“Preferred”
  解决方法:   在sql查询语句中写入case,  在向表中写入“Preferred”时, 先用订单金额对每一行进行检查, 满足条件的菜更新