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的大部分主要的语法,而且本身稳定,可用性是无需置疑的。

(0)

相关推荐

  • Sql server2005 优化查询速度50个方法小结

    I/O吞吐量小,形成了瓶颈效应. 没有创建计算列导致查询不优化. 内存不足. 网络速度慢. 查询出的数据量过大(可以采用多次查询,其他的方法降低数据量). 锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷). sp_lock,sp_who,活动的用户查看,原因是读写竞争资源. 返回了不必要的行和列. 查询语句不好,没有优化. 可以通过如下方法来优化查询 : 1.把数据.日志.索引放到不同的I/O设备上,增加读取速度,以前可以将Tempdb应放在RAID0上,SQL2000不在支持.数据量(

  • 如何调优SQL Server查询

    在今天的文章里,我想给你展示下,当你想对特定查询创建索引设计时,如何把你的工作和思考过程传达给查询优化器.下面就一起来探讨一下吧! 有问题的查询 我们来看下列查询: DECLARE @i INT = 999 SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, LineTotal FROM Sales.SalesOrderDetail WHERE ProductID < @i ORDER BY Carr

  • sqlserver性能调优经验总结

    相信不少的朋友,无论是做开发.架构的,还是DBA等,都经常听说"调优"这个词.说起"调优",可能会让很多技术人员心头激情澎湃,也可能会让很多人感觉苦恼.当然,也有很多人对此不屑一顾,因为并不是每个人接触到的项目都很大,也不是每个人做的项目都对性能要求很高. 在主流的企业级开发和互联网应用中,数据库的重要性是不言而喻的,而数据库的性能对于整个系统的性能而言也是至关重要的,这里无庸赘述. sqlserver的性能调优,其实是个很宽广的话题.坦白讲,想从概念到实践的完全讲

  • 一次SQL调优数据库性能问题后的过程(300W)

    将绝大部分的SQL查询改为存储过程,这样的操作毫无疑问可以提高部分性能. 凡是使用"select * from xxx"的操作一律具体到所需字段. 使用join连接2个以上大量数据的表,且基础数据表变化不大的查询一律使用视图,并为此视图建立索引.理由来自SQL Server联机帮助手册: "对于标准视图而言,为每个引用视图的查询动态生成结果集的开销很大,特别是对于那些涉及对大量行进行复杂处理(如聚合大量数据或联接许多行)的视图.如果在查询中频繁地引用这类视图,可通过对视图创建

  • MySQL慢查询查找和调优测试

    编辑 my.cnf或者my.ini文件,去除下面这几行代码的注释: 复制代码 代码如下: log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes 这将使得慢查询和没有使用索引的查询被记录下来. 这样做之后,对mysql-slow.log文件执行tail -f命令,将能看到其中记录的慢查询和未使用索引的查询. 随便提取一个慢查询,执行explain: 复制代

  • 10个MySQL性能调优的方法

    MYSQL 应该是最流行了 WEB 后端数据库.WEB 开发语言最近发展很快,PHP, Ruby, Python, Java 各有特点,虽然 NOSQL 最近越來越多的被提到,但是相信大部分架构师还是会选择 MYSQL 来做数据存储. MYSQL 如此方便和稳定,以至于我们在开发 WEB 程序的时候很少想到它.即使想到优化也是程序级别的,比如,不要写过于消耗资源的 SQL 语句.但是除此之外,在整个系统上仍然有很多可以优化的地方. 1. 选择合适的存储引擎: InnoDB 除非你的数据表使用来做

  • Mysql优化调优中两个重要参数table_cache和key_buffer

    本文根据作者的一点经验,讨论了Mysql服务器优化中两个非常重要的参数,分别是table_cache,key_buffer_size. table_cache指示表高速缓存的大小.当Mysql访问一个表时,如果在Mysql表缓冲区中还有空间,那么这个表就被打开并放入表缓冲区,这样做的好处是可以更快速地访问表中的内容.一般来说,可以通过查看数据库运行峰值时间的状态值Open_tables和Opened_tables,用以判断是否需要增加table_cache的值,即如果open_tables接近t

  • SqlServer 执行计划及Sql查询优化初探

    网上的SQL优化的文章实在是很多,说实在的,我也曾经到处找这样的文章,什么不要使用IN了,什么OR了,什么AND了,很多很多,还有很多人拿出仅几S甚至几MS的时间差的例子来证明着什么(有点可笑),让许多人不知道其是对还是错.而SQL优化又是每个要与数据库打交道的程序员的必修课,所以写了此文,与朋友们共勉. 谈到优化就必然要涉及索引,就像要讲锁必然要说事务一样,所以你需要了解一下索引,仅仅是索引,就能讲半天了,所以索引我就不说了(打很多字是很累的,况且我也知之甚少),可以去参考相关的文章,这个网上

  • mysql 性能的检查和调优方法

    在遇到严重性能问题时,一般都有这么几种可能:1.索引没有建好; 2.sql写法过于复杂; 3.配置错误; 4.机器实在负荷不了; 1.索引没有建好 如果看到mysql消耗的cpu很大,可以用mysql的client工具来检查. 在linux下执行 /usr/local/mysql/bin/mysql -hlocalhost -uroot -p 输入密码,如果没有密码,则不用-p参数就可以进到客户端界面中. 看看当前的运行情况 show full processlist 可以多运行几次 这个命令可

  • Mysql安装与配置调优及修改root密码的方法

    一.安装 apt-get install mysql-server 需要设置账号密码 apt-get isntall mysql-client apt-get libmysqlclient-dev 2.sudo netstat -tap | grep mysql 查看是否安装成功 root@xyz:~# netstat -tap | grep mysql tcp6 0 0 [::]:mysql [::]:* LISTEN 7510/mysqld -->安装成功 二.设置mysql远程访问 1.

  • eclipse修改jvm参数调优方法(2种)

    本文介绍了eclipse修改jvm参数调优方法(2种),分享给大家,具体如下: 一般在不对eclipse进行相关设置的时候,使用eclipse总是会觉得启动好慢,用起来好卡,其实只要对eclipse的相关参数进行一些配置,就会有很大的改善. 有两种方法: 1.打开eclipse配置文件eclipse.ini,更改把-Xmx(其数值代表jvm可以使用的最大内存数) 2.  运行java程序时,选择run->run configuration->arguments,输入-Xms100M -Xmx8

  • Logback与Log4j2日志框架性能对比与调优方式

    目录 前言 性能测试 logback 同步日志 异步日志(队列扩容) 异步日志(半队列扩容) log4j2 同步日志 异步日志(队列扩容) 异步日志(日志淘汰策略) 异步日志(半队列扩容) 异步日志(等待策略) 性能调优 异步日志 日志可靠性 Logback Log4j2 日志抛弃策略 Log4j2 Logback 日志等待策略 TimeoutWaitStrategy YieldWaitStrategy 队列容量 Logback Log4j2 长度计算公式 消费瓶颈 消费TPS 请求TPS 消费

  • 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语句,它会告知执行

  • 记录mysql性能查询过程的使用方法

    一切源于一个实验,请看下面的例子: 表: 复制代码 代码如下: CREATE TABLE IF NOT EXISTS `foo` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` int(10) unsigned NOT NULL, `c` varchar(100) NOT NULL, PRIMARY KEY (`a`), KEY `bar` (`b`,`a`) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXI

  • 分析MySQL复制以及调优原理和方法

    一. 简介 MySQL自带复制方案,带来好处有: 数据备份. 负载均衡. 分布式数据. 概念介绍: 主机(master):被复制的数据库. 从机(slave):复制主机数据的数据库. 复制步骤: (1). master记录更改的明细,存入到二进制日志(binary log). (2). master发送同步消息给slave. (3). slave收到消息后,将master的二进制日志复制到本地的中继日志(relay log). (4). slave重现中继日志中的消息,从而改变数据库的数据. 下

  • 使用MySQL的Explain执行计划的方法(SQL性能调优)

    目录 前言 1. explain的使用 2. explain字段详解 id列 select_type列 table列 partitions列 type列 system const eq_ref ref ref_or_null index_merge range index ALL possible_keys列 key列 key_len列 ref列 rows列 filtered列 Extra列 Using where Using index Using filesort Using tempora

  • mysql sql语句性能调优简单实例

     mysql sql语句性能调优简单实例 在做服务器开发时,有时候对并发量有一定的要求,有时候影响速度的是某个sql语句,比如某个存储过程.现在假设服务器代码执行过程中,某个sql执行比较缓慢,那如何进行优化呢? 假如现在服务器代码执行如下sql存储过程特别缓慢: call sp_wplogin_register(1, 1, 1, '830000', '222222'); 可以按如下方法来进行调试: 1. 打开mysql profiling: 2. 然后执行需要调优的sql,我们这里执行两条sq

随机推荐