分析MySQL中索引引引发的CPU负载飙升的问题
收到一个mysql服务器负载告警,上去一看,loadaverage都飙到280多了,用top一看,CPU跑到了336%,不过IO和内存的负载并不高,根据经验,应该又是一起索引引起的惨案了。
看下processlist以及slowquery情况,发现有一个SQL经常出现,执行计划中的扫描记录数看着还可以,单次执行耗时为0.07s,还不算太大。乍一看,可能不是它引发的,但出现频率实在太高,而且执行计划看起来也不够完美:
mysql>explainSELECTcount(1)FROMa,bWHEREa.id=b.video_idandb.state=1ANDb.column_id='81'\G
***************************1.row*************************** id:1 select_type:SIMPLE table:b type:index_merge possible_keys:columnid_videoid,column_id,state,video_time_stamp,idx_videoid key:column_id,state key_len:4,4 ref:NULL rows:100 Extra:Usingintersect(column_id,state);Usingwhere ***************************2.row*************************** id:1 select_type:SIMPLE table:a type:eq_ref possible_keys:PRIMARY key:PRIMARY key_len:4 ref:b.video_id rows:1 Extra:Usingwhere;Usingindex
再看下该表的索引情况:
mysql>showindexfromb\G
***************************1.row*************************** Table:b Non_unique:0 Key_name:PRIMARY Seq_in_index:1 Column_name:id Collation:A Cardinality:167483 Sub_part:NULL Packed:NULL Null: Index_type:BTREE Comment: Index_comment: ***************************2.row*************************** Table:b Non_unique:1 Key_name:column_id Seq_in_index:1 Column_name:column_id Collation:A Cardinality:8374 Sub_part:NULL Packed:NULL Null: Index_type:BTREE Comment: Index_comment: ***************************3.row*************************** Table:b Non_unique:1 Key_name:state Seq_in_index:2 Column_name:state Collation:A Cardinality:5 Sub_part:NULL Packed:NULL Null: Index_type:BTREE Comment: Index_comment:
可以看到执行计划中,使用的是indexmerge,效率自然没有用联合索引(也有的叫做覆盖索引)来的好了,而且state字段的基数(唯一性)太差,索引效果很差。删掉两个独立索引,修改成联合看看效果如何:
mysql>showindexfromb;
***************************1.row*************************** Table:b Non_unique:0 Key_name:PRIMARY Seq_in_index:1 Column_name:id Collation:A Cardinality:128151 Sub_part:NULL Packed:NULL Null: Index_type:BTREE Comment: Index_comment: ***************************2.row*************************** Table:b Non_unique:1 Key_name:idx_columnid_state Seq_in_index:1 Column_name:column_id Collation:A Cardinality:3203 Sub_part:NULL Packed:NULL Null: Index_type:BTREE Comment: Index_comment: ***************************3.row*************************** Table:b Non_unique:1 Key_name:idx_columnid_state Seq_in_index:2 Column_name:state Collation:A Cardinality:3463 Sub_part:NULL Packed:NULL Null: Index_type:BTREE Comment: Index_comment: mysql>explainSELECTcount(1)FROMa,bWHEREa.id=b.video_idandb.state=1ANDb.column_id='81'\G ***************************1.row*************************** id:1 select_type:SIMPLE table:b type:ref possible_keys:columnid_videoid,idx_videoid,idx_columnid_state key:columnid_videoid key_len:4 ref:const rows:199 Extra:Usingwhere ***************************2.row*************************** id:1 select_type:SIMPLE table:a type:eq_ref possible_keys:PRIMARY key:PRIMARY key_len:4 ref:b.video_id rows:1 Extra:Usingwhere;Usingindex
可以看到执行计划变成了只用到了idx_columnid_state索引,而且ref类型也变成了const,SQL执行耗时也从0.07s变成了0.00s,相应的CPU负载也从336%突降到了12%不到。
总结下,从多次历史经验来看,如果CPU负载持续很高,但内存和IO都还好的话,这种情况下,首先想到的一定是索引问题,十有八九错不了。