mysql 调优
转载：http://blog.csdn.net/yananwang/article/details/7030029
今天开始学习mysql调优啦 
（偶那东一榔头西一棒子的学习历程的又一棒子）
好啦，言归正传：
首先,mysql调优有三个主要的方式，由低到高分别是：
1、硬件调优
2、修改mysql进程
3、优化mysql查询
一、硬件调优
先看看硬件调优吧。这个有两方面你可以考虑，首先对现有硬件条件进行修复，能调整的调整，能替换的替换，例如你可以把中央处理器（CPU）或磁盘速度加倍，也可以让内存增大 4 到 8 倍。或者你可以替换掉有问题的硬件。其次，如果你的预算没有限制，那么你干脆可以无限制的增加你的服务器。不过这种解决方案也就仅限于此了。
二、修改mysql进程
也就是对mysqld进行调优，对这个进程进行调优意味着适当地分配内存，并让 mysqld 了解将会承受何种类型的负载。加快磁盘运行速度不如减少所需的磁盘访问次数。类似地，确保 MySQL 进程正确操作就意味着它花费在服务查询上的时间要多于花费在处理后台任务（如处理临时磁盘表或打开和关闭文件）上的时间。
三、查询优化
这个是最好的方法啦，这意味着对表应用了适当的索引，查询是按照可以充分利用 MySQL 功能的方式来编写的。可以配置 mysqld 来报告可能需要进行调优的查询。
如何调优呢，具体方法如下：
a、记录慢速查询
- [mysqld]
- ; enable the slow query log, default 10 seconds
- log-slow-queries
- ; log queries taking longer than 5 seconds
- long_query_time = 5
- ; log queries that don't use indexes even if they take less than long_query_time
- ; MySQL 4.1 and newer only
- log-queries-not-using-indexes
下面解释下上面的代码：
mysqldumpslow ，你可以通过此命令来查看慢查询日志 ，例如
- [root@ mysql-5.0.37]# mysqldumpslow /data/mysql-db/slow_queries.log -t 10
- Reading mysql slow query log from /data/mysql-db/slow_queries.log
- Count: 1 Time=763486.00s (763486s) Lock=0.00s (0s) Rows=0.0 (0), *********
- SET insert_id=N;
- INSERT INTO `nike`.`nike_countlog_index` (`userId`, `action`, `model`, `info`, `cTime`, `ip`, `isRen`)
- VALUES ('S', 'S', 'S', 'S', 'S', 'S', 'S')
上面的例子的返回结果被偶删掉啦（为了偶这里的安全哈），从这些返回结果中我们可以看到，通过mysqldumpslow我们可以获得如下信息：
count：他在此日志文件中重复出现的次数（对同一个查询的不同调用被计为一次）
b、对查询进行缓存
又是缓存哈，大多数LAMP应用都严重依赖于数据库查询，查询的大致过程如下:
PHP 发出查询请求->数据库收到指令对查询语句进行分析->确定如何查询->从磁盘中加载信息->返回结果
看到了吧，如果你反复查询，他就反复执行这些。MySQL 有一个特性称为查询缓存，他可以将查询的结果保存在内存中，在很多情况下，这会极大地提高性能。不过，问题是查询缓存在默认情况下是禁用的。
将query_cache_size = 32M 添加到 /etc/my.conf 中可以启用 32MB 的查询缓存。
在启动了查询缓存以后，我们最重要的是要知道他确实起作用了，mysql提供如下查看方法
- mysql> SHOW STATUS LIKE 'qcache%';
- +-------------------------+------------+
- | Variable_name | Value |
- +-------------------------+------------+
- | Qcache_free_blocks | 5216 |
- | Qcache_free_memory | 14640664 |
- | Qcache_hits | 2581646882 |
- | Qcache_inserts | 360210964 |
- | Qcache_lowmem_prunes | 281680433 |
- | Qcache_not_cached | 79740667 |
- | Qcache_queries_in_cache | 16927 |
- | Qcache_total_blocks | 47042 |
- +-------------------------+------------+
- 8 rows in set (0.00 sec)
解释如下：
- 变量名 说明
- Qcache_free_blocks 缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。
- Qcache_free_memory 缓存中的空闲内存。
- Qcache_hits 每次查询在缓存中命中时就增大。
- Qcache_inserts 每次插入一个查询时就增大。命中次数除以插入次数就是不中比率；用 1 减去这个值就是命中率。在上面这个例子中，大约有 87% 的查询都在缓存中命中。
- Qcache_lowmem_prunes 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）。
- Qcache_not_cached 不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。
- Qcache_queries_in_cache 当前缓存的查询（和响应）的数量。
- Qcache_total_blocks 缓存中块的数量。
通常间隔几秒显示这些变量你就会看出不同来，运行FLUSH STATUS 可以重置一些计数器，如果服务器已经运行了一段时间，这会非常有帮助。
缓存当然也不是越大越多越好，当缓存占用内存过多，那么服务一样很慢。作为一条规则，如果FLUSH QUERY CACHE占用了很长时间，那就说明缓存太大了。
c、强制限制
您可以在mysqld 中强制一些限制来确保系统负载不会导致资源耗尽的情况出现。
- set-variable=max_connections=500
- set-variable=wait_timeout=10
- max_connect_errors = 100
解释下哈：
第一行：最大连接数，在服务器没有崩溃之前确保只建立服务允许数目的连接，要确定服务器上目前建立过的最大连接数，请执行：
SHOW STATUS LIKE 'max_used_connections'
d、缓冲区和缓存
。 MySQL 可调节设置可以应用于整个mysqld 进程，也可以应用于单个客户机会话。
SHOW STATUS LIKE 'open%tables';
上图说明：有1504个打开的文件，有0个表需要打开，如果你每次重新执行 SHOW TABLE LIKE "open%tables"；open_tables 的变化很大，说明该缓存的命中率不高。
SHOW STATUS LIKE 'threads%';
这里主要看Threads_created，如果重复执行SHOW STATUS LIKE "threads%"时，这个值增长的非常快，那么你可以考虑在my.cnf中加大线程缓存，例如：thread_cache = 40 。
关键字缓存
show status like '%key_read%';
关键字，理想情况下对于他的请求应该来自于内存而不是磁盘，从这个表中我们可以看出有多少是Key_reads 代表命中磁盘的请求个数，Key_read_requests 是总数， 命中磁盘的读请求数除以读请求总数就是不中比率 —— 在本例中每 1,000 个请求，大约有 0.6 个没有命中内存。如果每 1,000 个请求中命中磁盘的数目超过 1 个，就应该考虑增大关键字缓冲区了。例如，key_buffer = 384M 会将缓冲区设置为 384MB。
SHOW STATUS LIKE 'created_tmp%';
每次使用临时表都会增大 Created_tmp_tables；基于磁盘的表也会增大 Created_tmp_disk_tables。对于这个比率，并没有什么严格的规则，因为这依赖于所涉及的查询。长时间观察Created_tmp_disk_tables 会显示所创建的磁盘表的比率，您可以确定设置的效率。tmp_table_size 和max_heap_table_size 都可以控制临时表的最大大小，因此请确保在 my.cnf 中对这两个值都进行了设置。
下面我要说的设置是针对每个会话的，所以你要小心啦，因为对于同一个服务器，有可能有百万级个会话同时进行着。
首先说下排序，mysql是这么处理排序的，首先从磁盘上读取数据，然后分配一块缓存区来缓存这些数据。如果要排序的数据太大，那么数据就必须保存到磁盘上的临时文件中，并再次进行排序。
SHOW STATUS LIKE "sort%";
如果sort_merge_passes 很大，就表示需要注意sort_buffer_size。例如， sort_buffer_size = 4M 将排序缓冲区设置为 4MB。
SHOW STATUS LIKE "com_select";
SHOW STATUS LIKE "handler_read_rnd_next";
mytop ：是mysql对linux的top命令的模拟。算得上是服务器的健康快照啦。
mysqlreport：其报告要远比mysqlard 更加复杂，因为需要对服务器的每个方面都进行分析。这是对服务器进行调优的一个非常好的工具，因为它对状态变量进行适当计算来帮助确定需要修正哪些问题。
