典型性索引引发CPU负载飙升问题

   收到一个mysql服务器负载告警,上去一看,load average都飙到280多了,用top一看,CPU跑到了336%,不过IO和内存的负载并不高,根据经验,应该又是一起索引引起的惨案了。

   看下processlist以及slow query情况,发现有一个SQL经常出现,执行计划中的扫描记录数看着还可以,单次执行耗时为0.07s,还不算太大。乍一看,可能不是它引发的,但出现频率实在太高,而且执行计划看起来也不够完美:

   mysql> explain SELECT count(1) FROM a , b WHERE a.id = b.video_id and b.state = 1 AND b.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: Using intersect(column_id,state); Using where

   *************************** 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: Using where; Using index

   再看下该表的索引情况:

   mysql> show index from b\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:

   可以看到执行计划中,使用的是index merge,效率自然没有用联合索引(也有的叫做覆盖索引)来的好了,而且 state 字段的基数(唯一性)太差,索引效果很差。删掉两个独立索引,修改成联合看看效果如何:

   mysql> show index from b;

   *************************** 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> explain SELECT count(1) FROM a , b WHERE a.id = b.video_id and b.state = 1  AND b.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: Using where

   *************************** 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: Using where; Using index

    可以看到执行计划变成了只用到了 idx_columnid_state 索引,而且 ref 类型也变成了 const,SQL执行耗时也从0.07s变成了0.00s,相应的CPU负载也从336%突降到了12%不到。

   总结下,从多次历史经验来看,如果CPU负载持续很高,但内存和IO都还好的话,这种情况下,首先想到的一定是索引问题,十有八九错不了。

觉得文章有用?立即: 和朋友一起 共学习 共进步!

猜您喜欢

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>