重大改版
 鱼翔空 2016-09-28 mysql/mariadb,技术总结|   0 286 
文章评分 90 次,平均分 5.0

根据导出的近半个月的慢sql和相关表索引的排查,大致有以下几种类型的问题:

一)索引相关

1)未建立索引

 项目中SQL优化及建议

 

后已加

 

建议增加cutomer_id的索引

2)索引未充分利用

 项目中SQL优化及建议

由于mysql检索是从左到右,建议查询时,将主索引放第一位;若时查询条件较多,建议创建组合索引

3)两张表关联字段类型不一致导致索引失效

二)语句相关

1)数据量大的表查询返回数量过大

全量查询返回数据行数过多:

 项目中SQL优化及建议

条件区域过大,返回数据过多

 

 项目中SQL优化及建议

 

2)分页查询不优

 项目中SQL优化及建议

如果使用频繁,建议 where rep_id>n limit 

3)业务相对简单Sql语句过于复杂,建议程序拆开处理

 项目中SQL优化及建议

4)Sql语句中出现过多的函数 max, concat

 

 

5)or优化

 

尽量减少or的使用,如果后续新增,根据临界的数据id,利用id的范围过滤

6)减少对视图的使用

 


7)建议减小sql的复杂度

 

 

此sql重复率较高,每天出现几十次

三)程序优化

1)已经废弃的表,还有sql操作,建议排查程序

 

2)一个事务间隔时间过程

建议只对表操作及关键业务进行事务操作,减少链接的占用时间

四)数据库使用优化

1)建议查询使用从库

 

统计sql建议走从库



针对以上问题归纳出的一些优化方案

一) 数据库及建表方面

1)主从复制后读写分离未充分利用,建议将不重要、不及时的查询走从库;

2)将统计等归纳后存储,减少大查询的次数;

3)使用简单的数据类型,能用int别用varchar;

4)尽量给字段设置初始的默认值,为以后加索引优化提供便利;

5)减少一张表中的列数,如t_cif_customer 建议垂直拆分

将核心关键字段存放一张表,并建立索引

将不常用的字段单独存放一张表

6)单表尽量控制在1000w,数据多了建分区表或者分表;

7)尽可能的使用 varchar/nvarchar 代替 char/nchar;

8) 关联字段类型一定要一致。

二)索引优化:

 1)索引建立原则:

    a)数据量重复较小的字段;

    b)对经常where ,order by,group by 条件建立索引;

c)联合查询 on或者USING子句的列建索引(最好能保证多表关联字段类型一致);

    c)数据量大的时候建索引;

    d)字段设置非null,(阿里云的貌似为null时,索引还生效);

    e)一张表的索引最好不超过6个,否则会增大磁盘压力;

 2)索引的使用

    a) mysql查询是从左到右,建议将限制查询范围比较的小的放左边;

    b)将有索引的字段左移;

    c) 一个sql只能使用一个索引,多字段查询时,建议用组合索引;

    d) 建立视图时避免使用union 与union ALL ,会导致索引失效;

    e) 索引的使用顺序:唯一索引>自增主键>主键;

    f)like ‘aa%’, <,>,<=,>=,between,in会走索引;

    g) <>,not in,!=则不能走索引;

    h) 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引;

三)sql优化

 1)查询尽量走从库;

 2)尽量限制单次查询的数据量,占用数据库内存过高;

 3)分页时采用where id>上一次最大id order by id asc limit 条数,来替代limit 偏移量条数;

 4)Where 条件中使用IN 与UNION 来替代OR;

 5)使用in和exists时,子查询尽量使用数据量少的表,exists>in当子查询有条件时;

     无条件时,两者效率差不多,当有条件时,exists不会进行全表扫描

 6)频繁按天按月查询,并且数据量不动时,建立表存储结果值;

 7)尽量使用select column 代替 select *;

 8)select count(主键)>select count(1)>select count(*); Count(column)不包含null列

 9)删除全量数据时,truncate table 效率最高;

10)尽量减少复杂sql的开发,能用程序处理的尽量程序处理;

四)程序优化

1)能程序处理的尽量减少数据库的查询;

2)如果一次for循环要比对很多次数据量很少的表的数据,建议一次性把数据查出来,通过程序for循环去比对;

3)对于常量类的表数据,建议初始化到缓存中,减少DB的查询;

4)查询一个人的还款计划时,在处理逾期,未还时,建议一次查询出来后通过程序处理,不要分两次查;

5)对于数据相对稳定,变化不大的,并且查询较高的,建议程序缓存一段时间;

6)对于时间等的查询,尽量程序中处理后再sql查询,减少使用DB的函数;

7)复杂sql的查询,建议在程序中拆分开,比如查询某个人不在会黑名单某条规则,并返回这个人的信息,建议先通过身份证或手机号查询到这个人所有的黑灰名单,再通过程序处理,最后看是否查询用户的信息;


 

除特别注明外,本站所有文章均为我要编程原创,转载请注明出处来自http://5ycode.com/article/566.html

关于
该用户很懒!
切换注册

登录

忘记密码 ?

切换登录

注册

扫一扫二维码分享