今天下班前一位同事负责的一套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多个
|
1
2
3
4
5
6
7
|
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表。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> use information_schema
Database
changed
mysql>
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';
查看这个语句的执行计划(类似下面这种)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> explain
select
ename,hiredate,sal
from
emp
where
sal=1000 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table
: emp
type:
ALL
possible_keys:
NULL
key
:
NULL
key_len:
NULL
ref:
NULL
rows
: 3072
Extra: Using
where
1 row
in
set
(0.00 sec)
ERROR:
No
query specified
|
可以看出语句没有使用索引而是使用全表扫描。分别对col4='123' 和col5='abc'做了统计，发现col4='123'的记录只有一条，而col5='abc'的记录有5W+条，很明显在col4上创建索引执行效率会高很多。查看表上是否有col4列上的索引(类似下面这种)。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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列上创建索引
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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: 163840
Max_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 specified
mysql>
create
index
idx_sal
on
emp(sal);
Query OK, 0
rows
affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
|
再次查看执行计划，发现语句使用索引扫描。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> explain
select
ename,hiredate,sal
from
emp
where
sal=1000 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table
: emp
type: ref
possible_keys: idx_sal
key
: idx_sal
key_len: 6
ref: const
rows
: 1
Extra:
NULL
1 row
in
set
(0.00 sec)
ERROR:
No
query specified
|
sql语句的执行效率立马提升。CPU的使用率也降下来了。
这也还有一个疑问，oracle在创建索引时为了避免锁表引入了online创建索引。不知道mysql中如何在线创建索引？
参考文档:http://blog.csdn.net/wlzjsj/article/details/51537736
参考文档:http://www.jb51.net/article/75217.htm
