MySQL占用CPU100%解决一例

一位同事负责的一套MySQL数据库的CPU使用率达到100%，登上服务器top查看Cpu（s）: 95.9%us, 4.0%sy, 0.0%ni, 0.0%id, 20.0%wa, 0.0%hi, 0.0%si, 0.0%st由于一直是在做Oracle的维护，对MySQL不熟悉，先在网上查了几篇文章，但好像都是一个人写的，不过从这些文章中也有了一个大体的处理思路。（由于不是我本人操作，没有记录详细的日志，这里只介绍下处理过程和使用到的命令，正文中的代码只是后期为说明加的。）先使用root用户登上mysql，使用 show processlist命令查看当前哪些线程正在运行。查看下来一共有160多个mysql> show processlist;+----+------+-----------+------+---------+------+-------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+------+---------+------+-------+------------------+| 1 | root | localhost | NULL | Query | 0 | init | show processlist |+----+------+-----------+------+---------+------+-------+------------------+1 row in set （0.00 sec）先简单说一下各列的含义和用途：id 一个标识，你要kill一个语句的时候很有用。user 显示当前用户，如果不是root，这个命令就只显示你权限范围内的sql句。host 显示这个语句是从哪个ip 哪个端口上发出的。可以用来追踪出问题语句的用户。db 显示这个进程目前连接的是哪个数据库。command 显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。time 此这个状态持续的时间，单位是秒。state 显示使用当前连接的sql语句的状态，很重要的列。state只是语句执行中的某一个状态，以查询sql为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。info 显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。state列各种状态参考文档：http://blog.csdn.net/e421083458/article/details/38342051从show processlist命令输出的结果看到有一条sql语句重复出现，但是info列显示的不全只有select a.col1,a.col2,a.col3 from table1 a这样的信息。那就先从这个表入手查，select count（*） from table1;查出这张表有60W+的数据。select count（*）使用了6秒。但是现在不确定这个语句执行的时候有没有where条件。继续查资料，查到show processlist命令出的结果出处information_schema库下的processlist表。mysql> use information_schemaDatabase changedmysql> desc processlist;+---------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+---------------------+------+-----+---------+-------+| ID | bigint（21） unsigned | NO | | 0 | || USER | varchar（16） | NO | | | || HOST | varchar（64） | NO | | | || DB | varchar（64） | YES | | NULL | || COMMAND | varchar（16） | NO | | | || TIME | int（7） | NO | | 0 | || STATE | varchar（64） | YES | | NULL | || INFO | longtext | YES | | NULL | |+---------+---------------------+------+-----+---------+-------+8 rows in set （0.04 sec）可看到表porcesslist表的列跟show processlist输出的列是一致的。再查询processlist表时发现info信息是完整的，在这里找到上边怀疑的sql的完整版为select a.col1,a.col2,a.col3 from table1 a where a.col4="123" and a.col5="abc";查看这个语句的执行计划（类似下面这种）mysql> explain select ename,hiredate,sal from emp where sal=1000 G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: emp type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3072 Extra: Using where1 row in set （0.00 sec） ERROR: No query specified可以看出语句没有使用索引而是使用全表扫描。分别对col4="123" 和col5="abc"做了统计，发现col4="123"的记录只有一条，而col5="abc"的记录有5W+条，很明显在col4上创建索引执行效率会高很多。查看表上是否有col4列上的索引（类似下面这种）。mysql> show index from emp G;*************************** 1. row *************************** Table: emp Non_unique: 1 Key_name: idx_emp_2 Seq_in_index: 1 Column_name: deptno Collation: A Cardinality: 6 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 1 row in set （0.00 sec） ERROR: No query specified发现col4列上没有索引，表的存储引擎为 InnoDB，于是在col4列上创建索引mysql> show table status from test1 like "emp"G;*************************** 1. row *************************** Name: emp Engine: InnoDB Version: 10 Row_format: Compact Rows: 3072 Avg_row_length: 53 Data_length: 163840Max_data_length: 0 Index_length: 65536 Data_free: 0 Auto_increment: NULL Create_time: 2016-11-15 21:54:49 Update_time: NULL Check_time: NULL Collation: gbk_chinese_ci Checksum: NULL Create_options: Comment: 1 row in set （0.00 sec） ERROR: No query specifiedmysql> create index idx_sal on emp（sal）;Query OK, 0 rows affected （0.15 sec）Records: 0 Duplicates: 0 Warnings: 0再次查看执行计划，发现语句使用索引扫描。mysql> explain select ename,hiredate,sal from emp where sal=1000 G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: emp type: refpossible_keys: idx_sal key: idx_sal key_len: 6 ref: const rows: 1 Extra: NULL1 row in set （0.00 sec） ERROR: No query specifiedsql语句的执行效率立马提升。CPU的使用率也降下来了。这也还有一个疑问，oracle在创建索引时为了避免锁表引入了online创建索引。不知道mysql中如何在线创建索引？本文永久更新链接地址