服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Mssql|Oracle|Redis|

服务器之家 - 数据库 - Mysql - mysql 性能的检查和调优方法

mysql 性能的检查和调优方法

2019-10-27 16:15mysql教程网 Mysql

我一直是使用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个档次,从而也不需要考虑增加机器来解决问题了。 
但是,mysql甚至所有数据库,可能都不好解决limit的问题。在mysql中,limit 0,10只要索引合适,是没有问题的,但是limit 100000,10就会很慢了,因为mysql会扫描排好序的结果,然后找到100000这个点,取出10条返回。要找到100000这个点,就要扫描100000条记录,这个循环是比较耗时的。不知道会不会有什么好的算法可以优化这个扫描引擎,我冥思苦想也想不出有什么好办法。对于limit,目前直至比较久远的将来,我想只能通过业务、程序和数据表的规划来优化,我想到的这些优化办法也都还没有一个是万全之策,往后再讨论。 
2、sql写法过于复杂 
sql写法假如用到一些特殊的功能,比如groupby、或者多表联合查询的话,mysql用到什么方式来查询也可以用desc来分析,我这边用复杂sql的情况还不算多,所以不常分析,暂时就没有好的建议。 
3、配置错误 
配置里主要参数是key_buffer、sort_buffer_size/myisam_sort_buffer_size,这两个参数意思是: 
key_buffer=128M:全部表的索引都会尽可能放在这块内存区域内,索引比较大的话就开稍大点都可以,我一般设为128M,有个好的建议是把很少用到并且比较大的表想办法移到别的地方去,这样可以显著减少mysql的内存占用。 
sort_buffer_size=1M:单个线程使用的用于排序的内存,查询结果集都会放进这内存里,如果比较小,mysql会多放几次,所以稍微开大一点就可以了,重要是优化好索引和查询语句,让他们不要生成太大的结果集。 
另外一些配置: 
thread_concurrency=8:这个配置标配=cpu数量x2 
interactive_timeout=30 
wait_timeout=30:这两个配置使用10-30秒就可以了,这样会尽快地释放内存资源,注意:一直在使用的连接是不会断掉的,这个配置只是断掉了长时间不动的连接。 
query_cache:这个功能不要使用,现在很多人看到cache这几个字母就像看到了宝贝,这是不唯物主义的。mysql的query_cache在每次表数据有变化的时候都会重新清理连至该表的所有缓存,如果更新比较频繁,query_cache不但帮不上忙,而且还会对效率影响很大。这个参数只适合只读型的数据库,如果非要用,也只能用query_cache_type=2自行用SQL_CACHE指定一些sql进行缓存。 
max_connections:默认为100,一般情况下是足够用的,但是一般要开大一点,开到400-600就可以了,能超过600的话一般就有效率问题,得另找对策,光靠增加这个数字不是办法。 
其它配置可以按默认就可以了,个人觉得问题还不是那么的大,提醒一下:1、配置虽然很重要,但是在绝大部分情况下都不是效率问题的罪魁祸首。2、mysql是一个数据库,对于数据库最重要考究的不应是效率,而是稳定性和数据准确性。 
4、机器实在负荷不了 
如果做了以上调整,服务器还是不能承受,那就只能通过架构级调整来优化了。 
1、mysql同步。 
通过mysql同步功能将数据同步到数台从数据库,由主数据库写入,从数据库提供读取。 
我个人不是那么乐意使用mysql同步,因为这个办法会增加程序的复杂性,并常常会引起数据方面的错误。在高负荷的服务中,死机了还可以快速重启,但数据错误的话要恢复就比较麻烦。 
2、加入缓存 
加入缓存之后,就可以解决并发的问题,效果很明显。如果是实时系统,可以考虑用刷新缓存方式使缓存保持最新。 
在前端加入squid的架构比较提倡使用,在命中率比较高的应用中,基本上可以解决问题。 
如果是在程序逻辑层里面进行缓存,会增加很多复杂性,问题会比较多而且难解决,不建议在这一层面进行调整。 
3、程序架构调整,支持同时连接多个数据库 
如果web加入缓存后问题还是比较严重,只能通过程序架构调整,把应用拆散,用多台的机器同时提供服务。 
如果拆散的话,对业务是有少许影响,如果业务当中有部分功能必须使用所有的数据,可以用一个完整库+n个分散库这样的架构,每次修改都在完整库和分散库各操作一次,或定期整理完整库。 
当然,还有一种最笨的,把数据库整个完完整整的做拷贝,然后程序每次都把完整的sql在这些库执行一遍,访问时轮询访问,我认为这样要比mysql同步的方式安全。 
4、使用 mysql proxy 代理 
mysql proxy 可以通过代理把数据库中的各个表分散到数台服务器,但是它的问题是没有能解决热门表的问题,如果热门内容散在多个表中,用这个办法是比较轻松就能解决问题。 
我没有用过这个软件也没有认真查过,不过我对它的功能有一点点怀疑,就是它怎么实现多个表之间的联合查询?如果能实现,那么效率如何呢? 
5、使用memcachedb 
数据库换用支持mysql的memcachedb,是可以一试的想法,从memcachedb的实现方式和层面来看对数据没有什么影响,不会对用户有什么困扰。 
为我现在因为数据库方面问题不多,没有试验过这个玩意。不过,只要它支持mysql的大部分主要的语法,而且本身稳定,可用性是无需置疑的。

延伸 · 阅读

精彩推荐
  • MysqlMySQL查询重复数据(删除重复数据保留id最小的一条为唯一数据)

    MySQL查询重复数据(删除重复数据保留id最小的一条为唯一数据)

    查重是我们在工作中经常会遇到的一个需求,下面这篇文章主要给大家介绍了关于MySQL查询重复数据(删除重复数据保留id最小的一条为唯一数据)的相关资...

    追逐时光4752019-06-03
  • Mysql数据库sql语句优化

    数据库sql语句优化

    今天小编就为大家分享一篇关于数据库sql语句优化,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧...

    qq_431937971632019-06-11
  • MysqlWindows下MySQL5.6查找my.ini配置文件的方法

    Windows下MySQL5.6查找my.ini配置文件的方法

    今天小编就为大家分享一篇Windows下MySQL5.6查找my.ini配置文件的方法,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    it_shine4282019-07-09
  • MysqlMySQL语句整理及汇总介绍

    MySQL语句整理及汇总介绍

    今天小编就为大家分享一篇关于MySQL语句整理及汇总介绍,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看...

    sdr_zd4942019-06-11
  • MysqlMySQL分页Limit的优化过程实战

    MySQL分页Limit的优化过程实战

    在mysql中进行分页查询时,一般会使用limit查询,下面这篇文章主要给大家介绍了关于MySQL分页Limit优化的相关资料,文中通过示例代码介绍的非常详细,需...

    旧梦发癫2252019-06-27
  • MysqlMySQL和Redis实现二级缓存的方法详解

    MySQL和Redis实现二级缓存的方法详解

    这篇文章主要给大家介绍了关于MySQL和Redis实现二级缓存的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,...

    我涛1842019-06-07
  • MysqlMysql:The user specified as a definer ('xxx@'%') does not exist的解决方案

    Mysql:The user specified as a definer ('[email protected]'%') does not exist的解决方案

    今天小编就为大家分享一篇关于Mysql:The user specified as a definer ('[email protected]'%') does not exist的解决方案,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价...

    执笔记忆的空白3772019-06-13
  • MysqlMySQL服务器的启动和关闭

    MySQL服务器的启动和关闭

    作为MySQL管理员,一个普通的目标就是确保服务器尽可能地处于运行状态,使得客户机能够随时访问它。但是,有时最好关闭服务器(例如,如果正在进行...

    mysql技术网4332019-10-21
北京塞车全天计划精准版 湖南快乐十分中奖助手 极速赛车开奖官网下载 青海11选5走势图 欢乐生肖是正规的吗 10bet娱乐城百家乐开户 _澳门赌场百家乐技巧 极速快3怎么看走势 伊利股份股票最新消息 浙江6+1开奖查询 江苏11选5下期预测最准确 乐天盈股票配资平台 天津快乐十分走势预测 权重股票 四川快乐十二选5走势 快乐8选2稳赚技巧 600353股票行情