taoCMS是基于php+sqlite/mysql的国内最小(100Kb左右)的功能完善、开源免费的CMS管理系统

mysql性能的检查和调优方法

2011-02-14
我一直是使用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取样的话,那么userid='7mini'这个例子中,取到的结果将不会是8条,而是2条,给用户造成了困惑。

当然还有另一种办法,加入双索引:

create index userid_clicks on imgs (userid, 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,userid_clicks | userid_clicks | 51      | const | 2944 | Using where |
+----+-------------+-------+------+----------------------+---------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

这时可以看到,结果集还是2944条,但是Extra中的filesort不见了。这时mysql使用userid_clicks这个索引去查询,这不但能快速查询到userid="admin"的所有记录,并且结果是根据clicks排好序的!所以不用再把这个结果集读入内存一条一条排序了,效率上会高很多。

但是用多字段索引这种方式有个问题,如果查询的sql种类很多的话,就得好好规划一下了,否则索引会建得非常多,不但会影响到数据insert和update的效率,而且数据表也容易损坏。

以上是对索引优化的办法,因为原因可能会比较复杂,所以写得比较的长,一般好好优化了索引之后,mysql的效率会提升n个档次,从而也不需要考虑增加机器来解决问题了。

类别:技术文章 | 阅读:198772 | 评论:0 | 标签:

想收藏或者和大家分享这篇好文章→

“mysql性能的检查和调优方法”共有0条留言

发表评论

姓名:

邮箱:

网址:

验证码:

公告

taoCMS发布taoCMS 3.0.2(最后更新21年03月15日),请大家速速升级,欢迎大家试用和提出您宝贵的意见建议。

捐助与联系

☟请使用新浪微博联系我☟

☟在github上follow我☟

精选优质技术文章

随机日记

最近评论

标签云

文章分类

友情链接