mysql 性能的检查和优化方法

答案：1、索引没有建好; 
2、sql写法过于复杂; 
3、配置错误; 
4、机器实在负荷不了; 
1、索引没有建好 
如果看到mysql消耗的cpu很大，可以用mysql的client工具来检查。 
在linux下执行 
/usr/local/mysql/bin/mysql -hlocalhost -uroot -p 
输入密码，如果没有密码，则不用-p参数就可以进到客户端界面中。 
看看当前的运行情况 
show full processlist 
可以多运行几次 
这个命令可以看到当前正在执行的sql语句，它会告知执行的sql、数据库名、执行的状态、来自的客户端ip、所使用的帐号、运行时间等信息 
在我的cache后端，这里面大部分时间是看不到显示任何sql语句的，我认为这样才算比较正常。如果看到有很多sql语句，那么这台mysql就一定会有性能问题 
如果出现了性能问题，则可以进行分析： 
1、是不是有sql语句卡住了? 
这是出现比较多的情况，如果数据库是采用myisam，那么有可能有一个写入的线程会把数据表给锁定了，如果这条语句不结束，则其它语句也无法运行。 
查看processlist里的time这一项，看看有没有执行时间很长的语句，要留意这些语句。 
2、大量相同的sql语句正在执行 
如果出现这种情况，则有可能是该sql语句执行的效率低下，同样要留意这些语句。 
然后把你所怀疑的语句统统集合一下，用desc(explain)来检查这些语句。 
首先看看一个正常的desc输出： 
mysql> desc select * from imgs where imgid=1651768337; 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 
| 1 | SIMPLE | imgs | const | PRIMARY | PRIMARY | 8 | const | 1 | | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 
1 row in set (0.00 sec) 
注意key、rows和Extra这三项，这条语句返回的结果说明了该sql会使用PRIMARY主键索引来查询，结果集数量为1条，Extra没有显示，证明没有用到排序或其他操作。由此结果可以推断，mysql会从索引中查询imgid=1651768337这条记录，然后再到真实表中取出所有字段，是很简单的操作。 
key是指明当前sql会使用的索引，mysql执行一条简单语句时只能使用到一条索引，注意这个限制;rows是返回的结果集大小，结果集就是使用该索引进行一次搜索的所有匹配结果;Extra一般会显示查询和排序的方式，。 
如果没有使用到key，或者rows很大而用到了filesort排序，一般都会影响到效率，例如： 
mysql> desc select * from imgs where userid="7mini" order by clicks desc limit 10; 
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+ 
| 1 | SIMPLE | imgs | ALL | NULL | NULL | NULL | NULL | 12506 | Using where; Using filesort | 
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+ 
1 row in set (0.00 sec) 
这条sql结果易做图有12506条，用到了filesort，所以执行起来会非常消耗效率的。这时mysql执行时会把整个表扫描一遍，一条一条去找到匹配userid="7mini"的记录，然后还要对这些记录的clicks进行一次排序，效率可想而知。真实执行时如果发现还比较快的话，那是因为服务器内存还足够将12506条比较短小的记录全部读入内存，所以还比较快，但是并发多起来或者表大起来的话，效率问题就严重了。 
这时我把userid加入索引： 
create index userid on imgs (userid); 
然后再检查： 
mysql> desc select * from imgs where userid="7mini" order by clicks desc limit 10; 
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+ 
| 1 | SIMPLE | imgs | ref | userid | userid | 51 | const | 8 | Using where; Using filesort | 
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+ 
1 row in set (0.00 sec) 
嗯，这时可以看到mysql使用了userid这个索引搜索了，用userid索引一次搜索后，结果集有8条。然后虽然使用了filesort一条一条排序，但是因为结果集只有区区8条，效率问题得以缓解。 
但是，如果我用别的userid查询，结果又会有所不同： 
mysql> desc select * from imgs where userid="admin" order by clicks desc limit 10; 
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+ 
| 1 | SIMPLE | imgs | ref | userid | userid | 51 | const | 2944 | Using where; Using filesort | 
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+ 
1 row in set (0.00 sec) 
这个结果和userid="7mini"的结果基本相同，但是mysql用userid索引一次搜索后结果集的大小达到2944条，这2944条记录都会加入内存进行filesort，效率比起7mini那次来说就差很多了。这时可以有两种办法可以解决，第一种办法是再加一个索引和判断条件，因为我只需要根据点击量取最大的10条数据，所以有很多数据我根本不需要加进来排序，比如点击量小于10的，这些数据可能占了很大部分。 
我对clicks加一个索引，然后加入一个where条件再查询： 
create index clicks on imgs(clicks); 
mysql> desc select * from imgs where userid="admin" order by clicks desc limit 10; 
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+ 
| 1 | SIMPLE | imgs | ref | userid,clicks | userid | 51 | const | 2944 | Using where; Using filesort | 
+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------------+ 
1 row in set (0.00 sec) 
这时可以看到possible_keys变成了userid,clicks，possible_keys是可以匹配的所有索引，mysql会从possible_keys中自己判断并取用其中一个索引来执行语句，值得注意的是，mysql取用的这个索引未必是最优化的。这次查询mysql还是使用userid这个索引来查询的，并没有按照我的意愿，所以结果还是没有什么变化。改一下sql加上use index强制mysql使用clicks索引： 
mysql> desc select * from imgs use index (clicks) where userid='admin' and clicks>10 order by clicks desc limit 10 
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ 
| 1 | SIMPLE | imgs | range | clicks | clicks | 4 | NULL | 5455 | Using where | 
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ 
1 row in set (0.00 sec) 
这时mysql用到了clicks索引进行查询，但是结果集比userid还要大!看来还要再进行限制： 
mysql> desc select * from imgs use index (clicks) where userid='admin' and clicks>1000 order by clicks desc limit 10 
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ 
| 1 | SIMPLE | imgs | range | clicks | clicks | 4 | NULL | 312 | Using where | 
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ 
1 row in set (0.00 sec) 
加到1000的时候结果集变成了312条，排序效率应该是可以接受。 
不过，采用换索引这种优化方式需要取一个采样点，比如这个例子中的1000这个数字，这样，对userid的每个数值，都要去找一个采样点，这样对程序来说是很难办的。如果按1000取样的话，那

上一个：PHP 超链接 抓取实现代码
下一个：通过PHP CLI实现简单的数据库实时监控调度