mysql 调优

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql 调优，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8942字，纯文字阅读大概需要13分钟。

内容图文

             啥是慢速查询呢：执行时间超过给定时间范围的查询就称为慢速查询。您可以配置mysqld 将这些慢速查询记录到适当命名的慢速查询日志中。然后你日后可以通过观察这些日志，来决定哪些部分需要调整。你需要在 my.cnf 中所做如下配置。

            my.cnf中添加如下配置

1. [mysqld]  
2. ; enable the slow query log, default 10 seconds  
3. log-slow-queries  
4. ; log queries taking longer than 5 seconds  
5. long_query_time = 5  
6. ; log queries that don‘t use indexes even if they take less than long_query_time  
7. ; MySQL 4.1 and newer only  
8. log-queries-not-using-indexes  

          下面解释下上面的代码：

           这三个设置一起使用，可以记录执行时间超过 5 秒和没有使用索引的查询。请注意有关log-queries-not-using-indexes 的警告。慢速查询日志都保存在 MySQL 数据目录中，名为 hostname-slow.log。如果希望使用一个不同的名字或路径，可以在 my.cnf 中使用log-slow-queries = /new/path/to/file 实现此目的。

         mysqldumpslow ，你可以通过此命令来查看慢查询日志 ，例如

1. [root@ mysql-5.0.37]# mysqldumpslow /data/mysql-db/slow_queries.log -t 10  
2. Reading mysql slow query log from /data/mysql-db/slow_queries.log  
3. Count: 1  Time=763486.00s (763486s)  Lock=0.00s (0s)  Rows=0.0 (0), *********  
4. SET insert_id=N;  
5. INSERT INTO `nike`.`nike_countlog_index` (`userId`, `action`, `model`, `info`, `cTime`, `ip`, `isRen`)   
6. VALUES (‘S‘, ‘S‘, ‘S‘, ‘S‘, ‘S‘, ‘S‘, ‘S‘)  

       上面的例子的返回结果被偶删掉啦（为了偶这里的安全哈），从这些返回结果中我们可以看到，通过mysqldumpslow我们可以获得如下信息：

       count：他在此日志文件中重复出现的次数（对同一个查询的不同调用被计为一次）

      b、对查询进行缓存

             又是缓存哈，大多数LAMP应用都严重依赖于数据库查询，查询的大致过程如下:

             PHP 发出查询请求->数据库收到指令对查询语句进行分析->确定如何查询->从磁盘中加载信息->返回结果

            看到了吧，如果你反复查询，他就反复执行这些。MySQL 有一个特性称为查询缓存，他可以将查询的结果保存在内存中，在很多情况下，这会极大地提高性能。不过，问题是查询缓存在默认情况下是禁用的。

            将query_cache_size = 32M 添加到 /etc/my.conf 中可以启用 32MB 的查询缓存。

            在启动了查询缓存以后，我们最重要的是要知道他确实起作用了，mysql提供如下查看方法

1.                   
2. mysql> SHOW STATUS LIKE ‘qcache%‘;  
3. +-------------------------+------------+  
4. | Variable_name           | Value      |  
5. +-------------------------+------------+  
6. | Qcache_free_blocks      | 5216       |  
7. | Qcache_free_memory      | 14640664   |  
8. | Qcache_hits             | 2581646882 |  
9. | Qcache_inserts          | 360210964  |  
10. | Qcache_lowmem_prunes    | 281680433  |  
11. | Qcache_not_cached       | 79740667   |  
12. | Qcache_queries_in_cache | 16927      |  
13. | Qcache_total_blocks     | 47042      |  
14. +-------------------------+------------+  
15. 8 rows in set (0.00 sec)  

解释如下：

1. 变量名                 说明  
2. Qcache_free_blocks  缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。  
3. Qcache_free_memory  缓存中的空闲内存。  
4. Qcache_hits             每次查询在缓存中命中时就增大。  
5. Qcache_inserts          每次插入一个查询时就增大。命中次数除以插入次数就是不中比率；用 1 减去这个值就是命中率。在上面这个例子中，大约有 87% 的查询都在缓存中命中。  
6. Qcache_lowmem_prunes    缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）。  
7. Qcache_not_cached   不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。  
8. Qcache_queries_in_cache 当前缓存的查询（和响应）的数量。  
9. Qcache_total_blocks     缓存中块的数量。  

通常间隔几秒显示这些变量你就会看出不同来，运行FLUSH STATUS 可以重置一些计数器，如果服务器已经运行了一段时间，这会非常有帮助。

         缓存当然也不是越大越多越好，当缓存占用内存过多，那么服务一样很慢。作为一条规则，如果FLUSH QUERY CACHE占用了很长时间，那就说明缓存太大了。

       c、强制限制

        您可以在mysqld 中强制一些限制来确保系统负载不会导致资源耗尽的情况出现。

       例如你可以在my.cn中加入以下限制：

1. set-variable=max_connections=500  
2. set-variable=wait_timeout=10  
3. max_connect_errors = 100  

      解释下哈：

      第一行：最大连接数，在服务器没有崩溃之前确保只建立服务允许数目的连接，要确定服务器上目前建立过的最大连接数，请执行：

     SHOW STATUS LIKE ‘max_used_connections‘

     第二行：mysqld将终止等待时间（空闲时间）超过10秒的连接。在 LAMP 应用程序中，连接数据库的时间通常就是 Web 服务器处理请求所花费的时间。有时候，如果负载过重，连接会挂起，并且会占用连接表空间。如果有多个交互用户或使用了到数据库的持久连接，那么将这个值设低一点并不可取！

     第三行：如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到FLUSH HOSTS之后才能运行。默认情况下，10 次失败就足以导致锁定了。将这个值修改为 100 会给服务器足够的时间来从问题中恢复。如果重试 100 次都无法建立连接，那么使用再高的值也不会有太多帮助，可能它根本就无法连接

     d、缓冲区和缓存

             mysql有超过100个可以调节的设置，要记住那么基本是不可能的，但是幸运的是你只需要记住很少一部分你就可以基本满足你的需求了，我们还可以通过“SHOW STATUS”命令来查看mysql是否按照我们的期望运行着。

            MySQL 可调节设置可以应用于整个mysqld 进程，也可以应用于单个客户机会话。

           服务器端的设置

           表缓存

            数据库中的每个表存储在一个文件中，要读取文件的内容，你必须先打开文件，然后再读取。为了加快从文件中读取数据的过程，mysqld 对这些打开文件进行了缓存，其最大数目由 /etc/mysqld.conf 中的 table_cache 指定

            例如：

      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。

           查询

           MySQL 也会分配一些内存来读取表。理想情况下，索引提供了足够多的信息，可以只读入所需要的行，但是有时候查询（设计不佳或数据本性使然）需要读取表中大量数据。要理解这种行为，需要知道运行了多少个SELECT 语句，以及需要读取表中的下一行数据的次数（而不是通过索引直接访问）。可以通过： 

SHOW STATUS LIKE "com_select";  

SHOW STATUS LIKE "handler_read_rnd_next"; 

Handler_read_rnd_next /Com_select 得出了表扫描比率 —— 在本例中是 521:1。如果该值超过 4000，就应该查看read_buffer_size，例如read_buffer_size = 4M。如果这个数字超过了 8M，就应该与开发人员讨论一下对这些查询进行调优了！

         其他有用的工具

      mytop ：是mysql对linux的top命令的模拟。算得上是服务器的健康快照啦。