mysql cpu占用 1500%问题优化

在154服务器上经常发现mysql进程占用cpu高达1500%

通过show full processlist  查看mysql的sql执行耗时情况，发现了大概20条如下

 SELECT * FROM e_instance_struct_8 tis where updatedate is not null and DATE_FORMAT(updatedate,'%Y-%m-%d')= CURDATE()

和大概20条

SELECT * FROM e_instance_step_status_16 tis WHERE updatedate IS NOT NULL AND DATE_FORMAT(updatedate,'%Y-%m-%d')= CURDATE()

上述这些sql每个time都是5-6秒

 

e_instance_struct_  和  e_instance_step_status_ 都进行了分表 各自有20个分表

 

e_instance_struct_  20个表总数据量大概2000万

 

e_instance_step_status_  20个表总数据量大概5000万

 

用explain SELECT * FROM e_instance_struct_8 tis where updatedate is not null and DATE_FORMAT(updatedate,'%Y-%m-%d')= CURDATE()

和explain SELECT * FROM e_instance_step_status_16 tis WHERE updatedate IS NOT NULL AND DATE_FORMAT(updatedate,'%Y-%m-%d')= CURDATE()

查看执行计划，发现这些sql语句都是全表扫描。

 

调用上述sql的方法是登录的时候调用getSiteDailySummary 接口进行统计，我测试了一下，只要一登录系统，cpu就飙升到1000-1500%然后几秒钟之后恢复，

初步判定就是如上sql导致的mysql cpu占用高的问题。

 

解决方法：

 

    给40个表的updatedate字段都加上索引，并且把sql语句改为如下（因为DATE_FORMAT函数会索引失效）

SELECT * FROM e_instance_struct_ tis where updatedate is not null and updatedate between concat(#{todayStr},' 00:00:00') and concat(#{todayStr},' 23:59:59')

 

SELECT * FROM e_instance_step_status_ tis WHERE updatedate IS NOT NULL AND updatedate between concat(#{todayStr},' 00:00:00') and concat(#{todayStr},' 23:59:59')

 

 

 观察了1天，发现mysql的cpu占用率降到了100%-300%  效果明显

 

 

 

/*==============================================================*/
/* Index: e_instance_step_status_1_updatedate */
/*==============================================================*/
create index e_instance_step_status_1_updatedate on e_instance_step_status_1
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_2_updatedate */
/*==============================================================*/
create index e_instance_step_status_2_updatedate on e_instance_step_status_2
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_3_updatedate */
/*==============================================================*/
create index e_instance_step_status_3_updatedate on e_instance_step_status_3
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_4_updatedate */
/*==============================================================*/
create index e_instance_step_status_4_updatedate on e_instance_step_status_4
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_5_updatedate */
/*==============================================================*/
create index e_instance_step_status_5_updatedate on e_instance_step_status_5
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_6_updatedate */
/*==============================================================*/
create index e_instance_step_status_6_updatedate on e_instance_step_status_6
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_7_updatedate */
/*==============================================================*/
create index e_instance_step_status_7_updatedate on e_instance_step_status_7
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_8_updatedate */
/*==============================================================*/
create index e_instance_step_status_8_updatedate on e_instance_step_status_8
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_9_updatedate */
/*==============================================================*/
create index e_instance_step_status_9_updatedate on e_instance_step_status_9
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_10_updatedate */
/*==============================================================*/
create index e_instance_step_status_10_updatedate on e_instance_step_status_10
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_11_updatedate */
/*==============================================================*/
create index e_instance_step_status_11_updatedate on e_instance_step_status_11
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_12_updatedate */
/*==============================================================*/
create index e_instance_step_status_12_updatedate on e_instance_step_status_12
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_13_updatedate */
/*==============================================================*/
create index e_instance_step_status_13_updatedate on e_instance_step_status_13
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_14_updatedate */
/*==============================================================*/
create index e_instance_step_status_14_updatedate on e_instance_step_status_14
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_15_updatedate */
/*==============================================================*/
create index e_instance_step_status_15_updatedate on e_instance_step_status_15
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_16_updatedate */
/*==============================================================*/
create index e_instance_step_status_16_updatedate on e_instance_step_status_16
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_17_updatedate */
/*==============================================================*/
create index e_instance_step_status_17_updatedate on e_instance_step_status_17
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_18_updatedate */
/*==============================================================*/
create index e_instance_step_status_18_updatedate on e_instance_step_status_18
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_19_updatedate */
/*==============================================================*/
create index e_instance_step_status_19_updatedate on e_instance_step_status_19
(
updatedate
);

/*==============================================================*/
/* Index: e_instance_step_status_20_updatedate */
/*==============================================================*/
create index e_instance_step_status_20_updatedate on e_instance_step_status_20
(
updatedate
);

 

 

 

 

create index e_instance_struct_1_updatedate on e_instance_struct_1
(
updatedate
);

create index e_instance_struct_2_updatedate on e_instance_struct_2
(
updatedate
);

create index e_instance_struct_3_updatedate on e_instance_struct_3
(
updatedate
);

create index e_instance_struct_4_updatedate on e_instance_struct_4
(
updatedate
);

create index e_instance_struct_5_updatedate on e_instance_struct_5
(
updatedate
);

create index e_instance_struct_6_updatedate on e_instance_struct_6
(
updatedate
);

create index e_instance_struct_7_updatedate on e_instance_struct_7
(
updatedate
);

create index e_instance_struct_8_updatedate on e_instance_struct_8
(
updatedate
);

create index e_instance_struct_9_updatedate on e_instance_struct_9
(
updatedate
);

create index e_instance_struct_10_updatedate on e_instance_struct_10
(
updatedate
);

create index e_instance_struct_11_updatedate on e_instance_struct_11
(
updatedate
);

create index e_instance_struct_12_updatedate on e_instance_struct_12
(
updatedate
);

create index e_instance_struct_13_updatedate on e_instance_struct_13
(
updatedate
);

create index e_instance_struct_14_updatedate on e_instance_struct_14
(
updatedate
);

create index e_instance_struct_15_updatedate on e_instance_struct_15
(
updatedate
);

create index e_instance_struct_16_updatedate on e_instance_struct_16
(
updatedate
);

create index e_instance_struct_17_updatedate on e_instance_struct_17
(
updatedate
);

create index e_instance_struct_18_updatedate on e_instance_struct_18
(
updatedate
);

create index e_instance_struct_19_updatedate on e_instance_struct_19
(
updatedate
);

create index e_instance_struct_20_updatedate on e_instance_struct_20
(
updatedate
);