mysql性能的检查和调优方法-数据库-IT落伍者

我一直是使用mysql这个数据库软件它工作比较稳定效率也很高在遇到严重性能问题时一般都有这么几种可能

索引没有建好

sql写法过于复杂

配置错误

机器实在负荷不了

索引没有建好

如果看到mysql消耗的cpu很大可以用mysql的client工具来检查

在linux下执行

/usr/local/mysql/bin/mysql hlocalhost uroot p

输入密码如果没有密码则不用p参数就可以进到客户端界面中

看看当前的运行情况

show full processlist

可以多运行几次

这个命令可以看到当前正在执行的sql语句它会告知执行的sql数据库名执行的状态来自的客户端ip所使用的帐号运行时间等信息

在我的cache后端这里面大部分时间是看不到显示任何sql语句的我认为这样才算比较正常如果看到有很多sql语句那么这台mysql就一定会有性能问题

如果出现了性能问题则可以进行分析

是不是有sql语句卡住了？

这是出现比较多的情况如果数据库是采用myisam那么有可能有一个写入的线程会把数据表给锁定了如果这条语句不结束则其它语句也无法运行

查看processlist里的time这一项看看有没有执行时间很长的语句要留意这些语句

大量相同的sql语句正在执行

如果出现这种情况则有可能是该sql语句执行的效率低下同样要留意这些语句

然后把你所怀疑的语句统统集合一下用desc（explain）来检查这些语句

首先看看一个正常的desc输出

mysql> desc select * from imgs where imgid=;

+++++++++++

+++++++++++

+++++++++++

row in set ( sec)

注意keyrows和Extra这三项这条语句返回的结果说明了该sql会使用PRIMARY主键索引来查询结果集数量为条Extra没有显示证明没有用到排序或其他操作由此结果可以推断mysql会从索引中查询imgid=这条记录然后再到真实表中取出所有字段是很简单的操作

key是指明当前sql会使用的索引mysql执行一条简单语句时只能使用到一条索引注意这个限制rows是返回的结果集大小结果集就是使用该索引进行一次搜索的所有匹配结果Extra一般会显示查询和排序的方式

如果没有使用到key或者rows很大而用到了filesort排序一般都会影响到效率例如

mysql> desc select * from imgs where userid=mini order by clicks desc limit ;

+++++++++++

+++++++++++

+++++++++++

row in set ( sec)

这条sql结果集会有条用到了filesort所以执行起来会非常消耗效率的这时mysql执行时会把整个表扫描一遍一条一条去找到匹配userid=mini的记录然后还要对这些记录的clicks进行一次排序效率可想而知真实执行时如果发现还比较快的话那是因为服务器内存还足够将条比较短小的记录全部读入内存所以还比较快但是并发多起来或者表大起来的话效率问题就严重了

这时我把userid加入索引

create index userid on imgs (userid);

然后再检查

mysql> desc select * from imgs where userid=mini order by clicks desc limit ;

+++++++++++

+++++++++++

+++++++++++

row in set ( sec)

嗯这时可以看到mysql使用了userid这个索引搜索了用userid索引一次搜索后结果集有条然后虽然使用了filesort一条一条排序但是因为结果集只有区区条效率问题得以缓解

但是如果我用别的userid查询结果又会有所不同

mysql> desc select * from imgs where userid=admin order by clicks desc limit ;

+++++++++++

+++++++++++

+++++++++++

row in set ( sec)

这个结果和userid=mini的结果基本相同但是mysql用userid索引一次搜索后结果集的大小达到条这条记录都会加入内存进行filesort效率比起mini那次来说就差很多了这时可以有两种办法可以解决第一种办法是再加一个索引和判断条件因为我只需要根据点击量取最大的条数据所以有很多数据我根本不需要加进来排序比如点击量小于的这些数据可能占了很大部分

我对clicks加一个索引然后加入一个where条件再查询

create index clicks on imgs(clicks);

mysql> desc select * from imgs where userid=admin order by clicks desc limit ;

+++++++++++

+++++++++++

+++++++++++

row in set ( sec)

这时可以看到possible_keys变成了useridclickspossible_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> order by clicks desc limit

+++++++++++

+++++++++++

+++++++++++

row in set ( sec)

这时mysql用到了clicks索引进行查询但是结果集比userid还要大！看来还要再进行限制

mysql> desc select * from imgs use index (clicks) where userid=admin and clicks> order by clicks desc limit

+++++++++++

+++++++++++

+++++++++++

row in set ( sec)

加到的时候结果集变成了条排序效率应该是可以接受

不过采用换索引这种优化方式需要取一个采样点比如这个例子中的这个数字这样对userid的每个数值都要去找一个采样点这样对程序来说是很难办的如果按取样的话那么userid=mini这个例子中取到的结果将不会是条而是条给用户造成了困惑

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

create index userid_clicks on imgs (userid clicks)

mysql> desc select * from imgs where userid=admin order by clicks desc limit ;

+++++++++++

+++++++++++

+++++++++++

row in set ( sec)

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

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

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

但是mysql甚至所有数据库可能都不好解决limit的问题在mysql中limit 只要索引合适是没有问题的但是limit 就会很慢了因为mysql会扫描排好序的结果然后找到这个点取出条返回要找到这个点就要扫描条记录这个循环是比较耗时的不知道会不会有什么好的算法可以优化这个扫描引擎我冥思苦想也想不出有什么好办法对于limit目前直至比较久远的将来我想只能通过业务程序和数据表的规划来优化我想到的这些优化办法也都还没有一个是万全之策往后再讨论

sql写法过于复杂

sql写法假如用到一些特殊的功能比如groupby或者多表联合查询的话mysql用到什么方式来查询也可以用desc来分析我这边用复杂sql的情况还不算多所以不常分析暂时就没有好的建议

配置错误

配置里主要参数是key_buffersort_buffer_size/myisam_sort_buffer_size这两个参数意思是

key_buffer=M全部表的索引都会尽可能放在这块内存区域内索引比较大的话就开稍大点都可以我一般设为M有个好的建议是把很少用到并且比较大的表想办法移到别的地方去这样可以显着减少mysql的内存占用

sort_buffer_size=M单个线程使用的用于排序的内存查询结果集都会放进这内存里如果比较小mysql会多放几次所以稍微开大一点就可以了重要是优化好索引和查询语句让他们不要生成太大的结果集

另外一些配置

thread_concurrency=这个配置标配=cpu数量x

interactive_timeout=

wait_timeout=这两个配置使用秒就可以了这样会尽快地释放内存资源注意一直在使用的连接是不会断掉的这个配置只是断掉了长时间不动的连接

query_cache这个功能不要使用现在很多人看到cache这几个字母就像看到了宝贝这是不唯物主义的mysql的query_cache在每次表数据有变化的时候都会重新清理连至该表的所有缓存如果更新比较频繁query_cache不但帮不上忙而且还会对效率影响很大这个参数只适合只读型的数据库如果非要用也只能用query_cache_type=自行用SQL_CACHE指定一些sql进行缓存

max_connections默认为一般情况下是足够用的但是一般要开大一点开到就可以了能超过的话一般就有效率问题得另找对策光靠增加这个数字不是办法

其它配置可以按默认就可以了个人觉得问题还不是那么的大提醒一下配置虽然很重要但是在绝大部分情况下都不是效率问题的罪魁祸首mysql是一个数据库对于数据库最重要考究的不应是效率而是稳定性和数据准确性

机器实在负荷不了

如果做了以上调整服务器还是不能承受那就只能通过架构级调整来优化了

mysql同步

通过mysql同步功能将数据同步到数台从数据库由主数据库写入从数据库提供读取

我个人不是那么乐意使用mysql同步因为这个办法会增加程序的复杂性并常常会引起数据方面的错误在高负荷的服务中死机了还可以快速重启但数据错误的话要恢复就比较麻烦

加入缓存

加入缓存之后就可以解决并发的问题效果很明显如果是实时系统可以考虑用刷新缓存方式使缓存保持最新

在前端加入squid的架构比较提倡使用在命中率比较高的应用中基本上可以解决问题

如果是在程序逻辑层里面进行缓存会增加很多复杂性问题会比较多而且难解决不建议在这一层面进行调整

程序架构调整支持同时连接多个数据库

如果web加入缓存后问题还是比较严重只能通过程序架构调整把应用拆散用多台的机器同时提供服务

如果拆散的话对业务是有少许影响如果业务当中有部分功能必须使用所有的数据可以用一个完整库+n个分散库这样的架构每次修改都在完整库和分散库各操作一次或定期整理完整库

当然还有一种最笨的把数据库整个完完整整的做拷贝然后程序每次都把完整的sql在这些库执行一遍访问时轮询访问我认为这样要比mysql同步的方式安全

使用 mysql proxy 代理

mysql proxy 可以通过代理把数据库中的各个表分散到数台服务器但是它的问题是没有能解决热门表的问题如果热门内容散在多个表中用这个办法是比较轻松就能解决问题

我没有用过这个软件也没有认真查过不过我对它的功能有一点点怀疑就是它怎么实现多个表之间的联合查询？如果能实现那么效率如何呢？

使用memcachedb

数据库换用支持mysql的memcachedb是可以一试的想法从memcachedb的实现方式和层面来看对数据没有什么影响不会对用户有什么困扰

为我现在因为数据库方面问题不多没有试验过这个玩意不过只要它支持mysql的大部分主要的语法而且本身稳定可用性是无需置疑的