根据导出的近半个月的慢sql和相关表索引的排查，大致有以下几种类型的问题：
一）索引相关
1）未建立索引
后已加
建议增加cutomer_id的索引
2）索引未充分利用
由于mysql检索是从左到右，建议查询时，将主索引放第一位；若时查询条件较多，建议创建组合索引
3）两张表关联字段类型不一致导致索引失效
二）语句相关
1）数据量大的表查询返回数量过大
全量查询返回数据行数过多：
条件区域过大，返回数据过多
2）分页查询不优
如果使用频繁，建议 where rep_id>n limit
3）业务相对简单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的查询，建议在程序中拆分开，比如查询某个人不在会黑名单某条规则，并返回这个人的信息，建议先通过身份证或手机号查询到这个人所有的黑灰名单，再通过程序处理，最后看是否查询用户的信息；
文章评论