MySQL性能优化实战_MySQL

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

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

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

1 mysql> desc slow_query;
2 +---------------+-------------+------+-----+---------+-------+
3 | Field | Type | Null | Key | Default | Extra |
4 +---------------+-------------+------+-----+---------+-------+
5 | Date | varchar(32) | NO | | | | 查询发生的时间
6 | user | varchar(64) | NO | | | |
7 | host | varchar(64) | NO | | | |
8 | content | text | NO | | | | 将Statement进行Mask后的语句，便于Group By
9 | query_time | int(11) | NO | | | | 查询所用时间，直接性能指标
10 | lock_time | int(11) | YES | | 0 | | 等待锁定的时间
11 | rows_sent | int(11) | YES | | 0 | | 返回的结果行数
12 | rows_examined | int(11) | YES | | 0 | | 扫描行数
13 | statement | text | YES | | NULL | | 实际查询语句
14 +---------------+-------------+------+-----+---------+-------+

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

比如：

优化后：

mysql> select sum(query_time)/count(*),count
(*),sum(query_time),min(Date),Max(Date) from slow where Date>'2008-02-20 22:50:52'and Date<'2008-02-21 17:34:35';
+--------------------------+----------+-----------------+---------------------+---------------------+
| sum(query_time)/count(*) | count(*) | sum(query_time) | min(Date) | Max(Date) |
+--------------------------+----------+-----------------+---------------------+---------------------+
| 5.7233 | 2197 | 12574 | 2008-02-20 22:51:16 | 2008-02-21 17:34:10 |
+--------------------------+----------+-----------------+---------------------+---------------------+
1 row in set (0.09 sec)

优化前：

mysql> select sum(query_time)/count(*),count(*),sum(query_time),min(Date),Max(Date) from slow where Date>'2008-02-17 22:50:52' and Date<'2008-02-18 17:34:35';
+--------------------------+----------+-----------------+---------------------+---------------------+
| sum(query_time)/count(*) | count(*) | sum(query_time) | min(Date) | Max(Date) |
+--------------------------+----------+-----------------+---------------------+---------------------+
| 2.5983 | 16091 | 41810 | 2008-02-17 22:50:58 | 2008-02-18 17:34:34 |
+--------------------------+----------+-----------------+---------------------+---------------------+
1 row in set (0.15 sec)