MySQL性能优化实战

这段时间由于线上数据库经常压力过大导致响应非常缓慢甚至死机咬咬牙下大决心来解决效率不高的问题!

首先是由于公司秉承快速开发原则频繁上线导致每次忽视了性能问题!日积月累所以导致系统越来越慢所以如果你的系统查询语句本来就优化的很好了可能参考意义不大!

提取慢查询日志文件应该在你的DataDir目录下面

通过程序处理慢查询文件将文件格式的慢查询导入到数据库中

mysql> desc slow_query;

+++++++

| Field | Type | Null | Key | Default | Extra |

+++++++

| Date | varchar() | NO | | | | 查询发生的时间

| user | varchar() | NO | | | |

| host | varchar() | NO | | | |

| content | text | NO | | | | 将Statement进行Mask后的语句便于Group By

| query_time | int() | NO | | | | 查询所用时间直接性能指标

| lock_time | int() | YES | | | | 等待锁定的时间

| rows_sent | int() | YES | | | | 返回的结果行数

| rows_examined | int() | YES | | | | 扫描行数

| statement | text | YES | | NULL | | 实际查询语句

+++++++

然后发挥您的想象力在这个表中尽力捕捉你想捕捉的那类型语句压力最大扫描行数最多等锁最久……

比如

优化后

mysql> select sum(query_time)/count(*)count

(*)sum(query_time)min(Date)Max(Date) from slow where Date> ::and Date< ::;

++++++

| sum(query_time)/count(*) | count(*) | sum(query_time) | min(Date) | Max(Date) |

++++++

| | | | :: | :: |

++++++

row in set ( sec)

优化前

mysql> select sum(query_time)/count(*)count(*)sum(query_time)min(Date)Max(Date) from slow where Date> :: and Date< ::;

++++++

| sum(query_time)/count(*) | count(*) | sum(query_time) | min(Date) | Max(Date) |

++++++

| | | | :: | :: |

++++++

row in set ( sec)

再比如优化前

基本信息

慢查询统计从 :: 到 ::时间段接近个小时的数据;

总共有慢查询个平均一小时有个慢查询;(花了一天优化降到每小时个的样子了成就感啊)

所有慢查询耗费总时间秒;

慢查询时间设置是大于秒

参数说明

sum总执行时间(秒);

count执行次数;

avg平均执行时间(秒);

content类似SQL语句的表达通式其中DD代表数字;

statement某一条具体执行的SQL语句

由于访问时的锁导致update非常慢

mysql> select count(*) as nsum(query_time) as s sum(query_time)/count(*) as avgsubstring_index(statement ) as u from slow where statement like update% and query_time> group by u;

+++++

| n | s | avg | u |

+++++

| | | | update conversation |

| | | | update user |

| | | | update user_modification |

+++++

说明程序中还是存在一些忘记释放事务锁的情况

最耗费资源的个查询

其中第应该是同一类查询这样的话这一类查询占总查询的一半以上每分钟出现个以上这样的慢查询需要重点解决!

mysql> select sum(query_time) as sum count(*) as count sum(query_time)/count(*) as avgstatement from slow wher

e host like %% group by content order by sum desc limit \G

*************************** row ***************************

sum:

count:

avg:

…………