MySQL服务器进程CPU占⽤100%的解决⽅法
朋友主机(Windows 2003 + IIS + PHP + MYSQL )近来 MySQL 服务进程 () CPU 占⽤率总为 100% ⾼居不下。此主机有10个左右的 database, 分别给⼗个⽹站调⽤。据朋友测试，导致 cpu 占⽤奇⾼的是⽹站A，⼀旦在 IIS 中将此⽹站停⽌服务，CPU 占⽤就降下来了。⼀启⽤，则马上上升。
　MYSQL CPU 占⽤ 100% 的解决过程
今天早上仔细检查了⼀下。⽬前此⽹站的七⽇平均⽇ IP 为2000，PageView 为 3万左右。⽹站A ⽤的 database ⽬前有39个表，记录数 60.1万条，占空间 45MB。按这个数据，MySQL 不可能占⽤这么⾼的资源。
环境变量输出到⽂件 ：
  于是在服务器上运⾏命令，将 mysql 当前的环境变量
d:\web\mysql> --help &
  发现 tmp_table_size 的值是默认的 32M，于是修改 My.ini, 将 tmp_table_size 赋值到 200M:
d:\web\mysql> notepad c:\windows\my.ini
[mysqld]
tmp_table_size=200M
  然后重启 MySQL 服务。CPU 占⽤有轻微下降，以前的CPU 占⽤波形图是 100% ⼀根直线，现在则在 97%~100%之间
参数对MYSQL 性能提升有改善作⽤
性能提升有改善作⽤。但问题还没有完全解决。
调整 tmp_table_size 参数
起伏。这表明调整
  于是进⼊ mysql 的 shell 命令⾏，调⽤show processlist, 查看当前 mysql 使⽤频繁的 sql 语句：
mysql> show processlist;
  反复调⽤此命令，发现⽹站 A 的两个 SQL 语句经常在 process list 中出现，其语法如下：
SELECT t1.pid, t2.userid, t3.count, t1.date
FROM _mydata AS t1
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid
LEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pid
ORDER BY t1.pid
LIMIT 0,15
  调⽤show columns检查这三个表的结构 :
mysql> show columns from _myuser;
mysql> show columns from _mydata;
mysql> show columns from _mydata_body;
  终于发现了问题所在：_mydata 表，只根据 pid 建⽴了⼀个 primary key，但并没有为 userid 建⽴索引。⽽在这个 SQL 语句的第⼀个LEFT JOIN ON⼦句中：
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid
  _mydata 的 userid 被参与了条件⽐较运算。于是我为给 _mydata 表根据字段 userid 建⽴了⼀个索引：
mysql> ALTER TABLE `_mydata` ADD INDEX ( `userid` )
  建⽴此索引之后，CPU 马上降到了 80% 左右。看到到了问题所在，于是检查另⼀个反复出现在 show processlist 中的sql 语句：
SELECT COUNT(*)
FROM _mydata AS t1, _mydata_key AS t2
WHERE t1.pid=t2.pid and t2.keywords = '孔雀'
  经检查 _mydata_key 表的结构，发现它只为 pid 建了了 primary key, 没有为 keywords 建⽴ index。_mydata_key ⽬前有33 万条记录，在没有索引的情况下对33万条记录进⾏⽂本检索匹配，不耗费⼤量的 cpu 时间才怪。看来就是针对这个表的检索出问题了。于是同样为 _mydata_key 表根据字段 keywords 加上索引:
mysql> ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )
  建⽴此索引之后，CPU⽴刻降了下来，在 50%~70%之间震荡。
1. 增加 tmp_table_size 值。mysql 的配置⽂件中，tmp_table_size的默认⼤⼩是 32M。如果⼀张临时表超出该⼤
⼩，MySQL产⽣⼀个The table tbl_name is full形式的错误，如果你做很多⾼级 GROUP BY 查询，增加
增加tmp_table_size 值。这是 mysql 官⽅关于此选项的解释：
tmp_table_size
This variable determines the maximum size for a temporary table in memory. If the table becomes too large,
a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible,
but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the
processlist for queries with temporary tables that take too long to resolve can give you an early warning that
tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where
upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with
immediate effect. The quicker resolution of queries resulted in less threads being active at any one time,
with all-round benefits for the server, and available memory.
2. 对 WHERE, JOIN, MAX(), MIN(), ORDER BY 等⼦句中的条件判断中⽤到的字段，应该根据其建⽴索引 INDEX。索引
被⽤来快速出在⼀个列上⽤⼀特定值的⾏。没有索引，MySQL不得不⾸先以第⼀条记录开始并然后读完整个表直到它出相关的⾏。表越⼤，花费时间越多。如果表对于查询的列有⼀个索引，MySQL能快速到达⼀个位置去搜寻到数据⽂件的中间，没有必要考虑所有数据。如果⼀个表有1000⾏，这⽐顺序读取⾄少快100倍。所有的MySQL索引
(PRIMARY、UNIQUE和INDEX)在B树中存储。
根据 mysql 的开发⽂档:
索引 index ⽤于
⽤于：
快速出匹配⼀个WHERE⼦句的⾏
当执⾏联结(JOIN)时，从其他表检索⾏。
查看mysql索引对特定的索引列出MAX()或MIN()值
如果排序或分组在⼀个可⽤键的最左⾯前缀上进⾏(例如，ORDER BY key_part_1,key_part_2)，排序
或分组⼀个表。如果所有键值部分跟随DESC，键以倒序被读取。
在⼀些情况中，⼀个查询能被优化来检索值，不⽤咨询数据⽂件。如果对某些表的所有使⽤的列是数字
型的并且构成某些键的最左⾯前缀，为了更快，值可以从索引树被检索出来。
假定你发出下列SELECT语句：
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果⼀个多列索引存在于col1和col2上，适当的⾏可以直接被取出。如果分开的单⾏列索引存在于col1
和col2上，优化器试图通过决定哪个索引将到更少的⾏并来出更具限制性的索引并且使⽤该索引取
⾏。
开发⼈员做 SQL 数据表设计的时候，⼀定要通盘考虑清楚。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。