mysql优化索引、配置，及慢查询讲解

下文内容主要给大家带来 MySQL优化索引、配置，及慢查询讲解，这里所讲到的知识，与书籍略有不同，都是创新互联专业技术人员在与用户接触过程中，总结出来的，具有一定的经验分享价值，希望给广大读者带来帮助。           

成都创新互联专注于洪江管理区企业网站建设,响应式网站,商城开发。洪江管理区网站建设公司,为洪江管理区等地区提供建站服务。全流程按需定制制作，专业设计，全程项目跟踪，成都创新互联专业和态度为您提供的服务

s首先基本的思路

1）性能瓶颈定位

使用show命令、

慢查询日志、

explain分析查询、

profiling分析查询、

2）索引及查询优化

3）配置优化

MySQL数据库常见的两个瓶颈cpu、i/o：

CPU主要在饱和的时候发生在数据装入内存或磁盘上读取数据的时候

i/o发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的网络瓶颈，我们可以通过mpstat、iostat、vmstat、sar等命令查看系统的性能状态

例如：mpstat  3  3 {表示每3秒输出三次}

 

另外除了云服务器硬件的性能瓶颈，对于mysql系统本身，我们可以使用工具来优化数据库的性能；通常有三种：

使用索引、   使用explain分析查询、   调整mysql的内部配置

 

1：查询与索引优化分析；

在优化mysql时，通常需要对数据库进行分析，常见的分析有慢查询日志，explain分析查询，profiling分析以及show命令查询系统状态及系统变量。

 

show命令

可以通过show命令查看mysql状态以及变量，找到系统的瓶颈；

查看mysql服务器配置信息 、

 

查看mysql服务器运行的各种状态、

 

显示系统变量：=====>> mysqladmin variables -u username -ppassword

显示系统状态：======>> mysqladmin exlended -stautus -u username -ppassword

另外可以通过：

 

慢查询日志

 

开启慢查询日志；在配置文件中/etc/my.cnf中添加三个参数；

slow_query_log=1 [1表示开启、0表示关闭]

slow_query_log_file=/usr/local/mysql/data/slow-query.log 慢查旬日志存放位置

long-query_time=1 表示查询超过1秒的时间记录

在my.cnf中添加log-queries-not-using-indexes参数，表示向慢查询日志中记录下没有使用索引的查询

慢查询日志也可以在命令行中开启：

 

只不过在命令行中的属于临时生效，而在主配置文件中属于永久生效

查看慢查询的设置信息：

 

查看超时的时间限制

 

另外我们还可以同过查看慢查询日志查看执行效率低的sql语句

 

可以看到刚才指定慢查询文件的路径这条命令的执行结果时间超过了0.01秒，所以也同样被记录了下来。

如果慢查询日志当中的内容很多的话，可以使用mysqldmpslow对日志文件进行分类汇总，

具体使用方式可以通过mysqldmpslow --help查看使用的参数

 

如果有慢查询的语句，那么该如何优化呢

一：对数据entertime列进行创建索引

 

二：优化这个sql查询语句

 

 

使用mysqldumpslow命令可以非常明确得到各种我们需要的查询语句；对mysql查询语句的监控、分析、优化是mysql非常重要的一部分。

 

explain 分析查询：

使用explain关键字可以模拟优化器执行sql查询语句；从而知道mysql是如何处理sql语句的。可以分析查询语句或表结构的性能瓶颈

 

EXPLAIN字段：

Table：显示这一行的数据是关于哪张表的

type：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。

key：实际使用的索引。如果为NULL，则没有使用索引。

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MySQL认为必须检索的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息

从上面的explain模拟优化器执行sql语句来看是没有使用索引查询的，而是全表扫描

 

从上面的explain模拟优化器执行sql语句看来没有使用索引查询，而是全表扫描

优化方法：

 

 

显示结果说明该查询语句使用了index_stuname索引查询数据而非全表扫描。

 

profiling分析查询：

通过慢日志查询可以知道那些sql语句执行效率低，通过explain可以知道sql语句的具体执行情况；索引等，可以通过profiling命令得到更准确的sql执行消耗系统资源的信息。

profiling默认是关闭的，可通过查看的方式：

 

 

或者通过

 

 

 

打开profiling的功能：如下图

 

 

接下来测试要执行的sql语句

 

 

 

status:是profile里的状态，duration：是status状态下的耗时。因此我们关注的就是那个状态最耗时，这些状态中那些可以优化。

 

当然也可以查看更多的信息如CPU等等

SHOW PROFILE [type [, type] ... ]  [FOR QUERY n]

type:
       ALL:显示所有的开销信息
      BLOCK IO:显示块IO相关开销
      CPU:显示用户CPU时间、系统CPU时间
      IPC:显示发送和接收相关开销信息
       PAGE FAULTS:显示页面错误相关开销信息
      SWAPS:显示交换次数相关开销的信息

测试完成之以后，记得要关闭调试功能，以免影响数据库的正常使用：

mysql> set profiling=0;

  

2：配置优化：

mysql参数优化对不同的网站，及其线量，访问量、帖子数量、网络情况、以及硬件设备，都有关系，优化不可能一次性完成，需要不断的观察以及调试，才能达到最佳效果。

对性能影响比较大的分为链接请求的变量和缓冲区变量

 

1)连接请求的变量

max_connections

mysql的最大连接数；如果服务器的并发请求量比较大，建议调高此值，以增加并行连接数量，当然这是建立在服务器能够支撑的情况之下，如果连接数越大，mysql回味每个连接提供连接缓冲区，这样内存的开销会提高。所以要适当的调整该值。不能盲目的提高。

但是如果数值过小的话会出现ERROR 1040：Too many connections的错误，可以通过以下的命令查看连接数啊

mysql>show variables like ‘max_connections’ 最大连接数

mysql>show  status like ‘max_used_connections’ 响应的连接数

max_used_connections / max_connections * 100% （理想值≈ 85%） 

 

 

 

 

 

 

max_used_connections/ max_connections * 100% {理想值=85%}

那么如何设置max_xonnections？

修改/etc/my.cnf文件，在【mysqld】下面添加如下内容，。如设置最大连接数为1024

max_connections=1024

之后重启mysql服务

 

2：back_log

mysql能暂存的连接数量。当主要mysql线程在一个很短的时间内得到非常对的连接请求；它就会起作用，当mysql；的链接数达到max_connections是，新的请求将会被存放在堆栈当中。等待某一链接，释放资源，该堆栈的数量即back_log，如果链接数量超过back_log，将不被授予链接资源

back_log值指出在mysql暂时停止回答新请求之前的短时间内段时间内有多少个请求可以被存在堆栈中，如果期望在一个短时间内与很多链接，你需要增加它

 

何设置back_log?

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容，如设置最大连接数为1024

back_log =数值

 

重启mysql服务

 

3. wait_timeout和interactive_timeout

wait_timeout -- 指的是MySQL在关闭一个非交互的连接之前所要等待的秒数

interactive_time -- 指的是mysql在关闭一个交互的连接之前所要等待的秒数，比如我们在终端上进入mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动断开。默认数值是28800，可调优为7200。

对性能的影响：

wait_timeout：

（1）如果设置大小，那么连接关闭的很快，从而使一些持久的连接不起作用

（2）如果设置太大，容易造成连接打开时间过长，在show processlist时，能看到太多的sleep状态的连接，从而造成too many connections错误

（3）一般希望wait_timeout尽可能地低

interactive_timeout的设置将要对你的web application没有多大的影响

查看wait_timeout和interactive_timeout

mysql> show variables like '%wait_timeout%';

 

mysql> show variables like '%interactive_timeout%';

 

如何设置wait_timeout和interactive_timeout?

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

wait_timeout=100
interactive_timeout=100

重启MySQL Server进入后，查看设置已经生效。

2）绶冲区变量

全局缓冲：

4.key_buffer_size

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。

 

一共有6个索引读取请求，有3个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率： 
key_cache_miss_rate ＝Key_reads / Key_read_requests * 100% =50% 

key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

 

 如何调整key_buffer_size

默认配置数值是8388608(8M)，主机有4GB内存，可以调优值为268435456(256MB)

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

key_buffer_size=268435456或key_buffer_size=256M

重启MySQL Server进入后，查看设置已经生效。

5.  query_cache_size(查询缓存简称QC)

使用查询缓冲，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。

一个SQL查询如果以select开头，那么MySQL服务器将尝试对其使用查询缓存。

注：两个SQL语句，只要相差哪怕是一个字符（例如大小写不一样；多一个空格等），那么这两个SQL将使用不同的一个CACHE。

 

通过检查状态值’Qcache%’，可以知道query_cache_size设置是否合理（上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得）。

 

Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。 

注：当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks
Qcache_free_memory：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache内存大小是否足够，是需要增加还是过多了。
Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
Qcache_lowmem_prunes：多少条Query 因为内存不足而被清除出Query Cache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告诉您属于哪种情况） 
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。 
Qcache_queries_in_cache：当前Query Cache 中cache的Query数量； 
Qcache_total_blocks：当前Query Cache 中的block数量；。 

我们再查询一下服务器关于query_cache的配置：

 

上图可以看出query_cache_type为ON表示缓存任何查询

各字段的解释：
query_cache_limit：超过此大小的查询将不缓存 
query_cache_min_res_unit：缓存块的最小大小 ，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024 byte，所以如果你设定了query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：

 

如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。

如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。

如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。

修改/etc/my.cnf,配置完后的部分文件如下：

query_cache_size=256M

保存文件，重新启动MYSQL服务，然后通过如下查询来验证开启了：

 

 

query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。  

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% 
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。 
查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100% 
查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。
查询缓存命中率 = Qcache_hits/(Qcache_hits+Qcache_inserts) * 100% 

 

Query Cache 的限制

a) 所有子查询中的外部查询SQL不能被Cache；
b) 在Procedure，Function以及Trigger中的Query不能被Cache；
c) 包含其他很多每次执行可能得到不一样结果的函数的Query不能被Cache。
鉴于上面的这些限制，在使用Query Cache 的过程中，建议通过精确设置的方式来使用，仅仅让合适的表的数据可以进入Query Cache，仅仅让某些Query的查询结果被Cache。

如何设置query_cache_size？

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

query_cache_size=256M
query_cache_type=1

重启MySQL Server进入后，查看设置已经生效。

 

6. max_connect_errors是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况, 当超过指定次数，MYSQL服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息。max_connect_errors的值与性能并无太大关系。

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

max_connect_errors=20

重启MySQL Server进入后，查看设置已经生效。

 

 

 

 

         MySQL性能优化————影响性能的因素

如果将mysql服务器比作一台跑车，那么服务器硬件就好比发动机，引擎等公具，而里面的设施皮椅就可以比作MySQL的性能优化，只有两者兼备才算的上是一个完整的跑车；

在这里我们主要针对的是对mysql的性能进行优化属于刚才说的里面的设施。

包括连接数，查询缓存等

 

MySQL影响性能的因素：

1.商业需求的影响

2.系统架构及实现的影响

1）二进制多媒体数据

2）超大文本数据

3：sql语句使用的不恰当，以及优化前后的变化

在数据库中最主要的优化包括cpu、内存和磁盘i/o的优化

当然这些必须根据自身公司的服务器进行判断：比如CPU可以支持多核，内存最小64GB、以上等等

例子：为查询缓存优化你的查询

大多数MySQL已经开启了查询缓存，这是最有效提高优化的方法之一；很多相同的查询执行多次，被放到一个缓存当中，后续得到查询就不用操作表而直接访问缓存当中的数据

 query_cache_size(查询缓存简称QC)

注：两个SQL语句，只要相差哪怕是一个字符（例如大小写不一样；多一个空格等），那么这两个SQL将使用不同的一个CACHE。

通过检查状态值’Qcache%’，可以知道query_cache_size设置是否合理（上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得）。

 

Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。 

注：当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks
我们再查询一下服务器关于query_cache的配置：

 

上图可以看出query_cache_type为off表示不缓存任何查询

各字段的解释：
query_cache_limit：超过此大小的查询将不缓存 
query_cache_min_res_unit：缓存块的最小大小 ，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024 byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)
query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：

 

如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。

如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。

如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。

query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。 

修改/etc/my.cnf,配置完后的部分文件如下：

query_cache_size=256M
query_cache_type=1

保存文件，重新启动MYSQL服务，然后通过如下查询来验证是否真正开启了：

 

2：explain你的select查询

帮助你查看你的mysql是如何处理的的sql语句，分析你的查询语句或者表结构的性能瓶颈。

另外还会告诉你使用的是什么索引；数据表是如何被搜索的和排序的

 

explain分析查询

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

 

>explain select * from test1.tb1 where stuname='admin'\G;

profiling分析查询

通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。

profiling默认是关闭的。可以通过以下语句查看

mysql> show variables like '%profiling%';    //off表示未开启

打开profiling功能： mysql>set profiling=1; 执行需要测试的sql语句：

mysql> select @@profiling;

+---------------------+

| @@profiling |

+---------------------+

|           1 |

+----------------------+

执行要测试的sql语句

mysql> select * from test1.tb1 where stuname='admin' and entertime='2016-9-1';

mysql> show profiles\G;   //可以得到被执行的SQL语句的时间和ID

*************************** 1. row ***************************

Query_ID: 1

Duration: 0.00012650

   Query: select @@profiling

*************************** 2. row ***************************

Query_ID: 2

Duration: 0.00121725

   Query: select * from test1.tb1 where stuname='admin' and entertime='2016-9-1'

mysql> show profile for query 2;  //得到对应SQL语句执行的详细信息

+----------------------+-------------------------+

| Status              | Duration |

+----------------------+-------------------------+

| starting             | 0.000230 |

| checking permissions | 0.000013 |

| Opening tables       | 0.000030 |

| init                 | 0.000087 |

| System lock          | 0.000018 |

| optimizing           | 0.000128 |

| statistics           | 0.000378 |

| preparing            | 0.000026 |

| executing            | 0.000005 |

| Sending data         | 0.000187 |

| end                  | 0.000013 |

| query end            | 0.000011 |

| closing tables       | 0.000010 |

| freeing items        | 0.000061 |

| cleaning up          | 0.000021 |

+----------------------+-------------------------+

status:是profile里的状态，duration：是status状态下的耗时。因此我们关注的就是那个状态最耗时，这些状态中那些可以优化。

当然也可以查看更多的信息如CPU等等

SHOW PROFILE [type [, type] ... ][FOR QUERY n]

type:
       ALL:显示所有的开销信息
      BLOCK IO:显示块IO相关开销
      CPU:显示用户CPU时间、系统CPU时间
      IPC:显示发送和接收相关开销信息
       PAGE FAULTS:显示页面错误相关开销信息
      SWAPS:显示交换次数相关开销的信息

测试完成之以后，记得要关闭调试功能，以免影响数据库的正常使用：

mysql> set profiling=0;

 

3:为搜索字段建立索引:

索引并不一定给主键或是字段建立，而是给经常需要查询的目标建立，相当于字典的目录，提速高效能。提高查询效率，快速定位数据 

索引分为四种：

 CREATE INDEX indexName ON tablename(column1[,column2,……])

 

 全文索引 

只用于MyISAM 表 对文本域进行索引。字段类型包括char、varchar、text

不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

CREATE FULLTEXT INDEX indexname ON tablename(column)

 

全局缓冲：索引缓存的大小

.key_buffer_size

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。

 如何调整key_buffer_size

默认配置数值是8388608(8M)，主机有4GB内存，可以调优值为268435456(256MB)

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

key_buffer_size=268435456或key_buffer_size=256M

innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。

4：避免select*的使用：

如果数据库的数据过多的话使用‘*’增加查询的时间增大cpu和i/o的负载，全表查询而且速度慢，应该养成查询的时候制定某一个字段，

 

5：选择正确的存储引擎：

myisam使用与查询大量的的应用；有时一个update字段，可能导致全表锁定，当然在count（*）这类计算的时候是速度非常快的因为有计数器

innodb复杂的存储引擎支持行锁，换支持事物，不适合count（*）

 

6:查看慢查询日志：

 

慢查询日志开启：

在配置文件my.cnf中在[mysqld]一行下面加入3个配置参数，并重启mysql服务

slow_query_log = 1   //0关闭  1开启
slow_query_log_file = /usr/local/mysql/data/slow-query.log    //慢查询日志存放地点

long_query_time = 1                               //表示查询超过1秒才记录                                

在my.cnf中添加log-queries-not-using-indexes参数，表示向慢查询日志中记录下没有使用索引的查询。

慢查询日志开启方法二：

我们也可以通过命令行设置变量来即时启动慢日志查询

mysql> set global slow_query_log = on;

mysql> set long_query_time =0.01;

mysql> set global slow_query_log_file = "/usr/local/mysql/data/slow-query.log";

查看慢查询的设置信息

mysql> show variables like '%slow_query_log%';

mysql> show variables like '%long_query_time%';

我们可以通过打开log文件查看得知哪些SQL执行效率低下

[root@localhost data]# cat slow-query.log 

  

7：大批量数据的限制：

如果大批量的添加数据会导致查询效率低，还有就是数据入库的时间长，有时候会长达几个小时

max_allowed_packet = 32M

MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败。最大值是1GB，必须设置1024的倍数。

 

8：关闭交互式：

比如当dba使用交互式的界面对数据库进行增、改、删、查之后，忘记了退出数据库的交互式页面，如果有人看见在上面进行操作修改数据，会为公司造成不可估量的损失，在这里我们可以通过只配置文件调整交互式存在的时间，防止其他人员进行操作；

另外也可以释放一个用户的链接数，增大一个链接数量

.wait_timeout和interactive_timeout

wait_timeout -- 指的是MySQL在关闭一个非交互的连接之前所要等待的秒数

interactive_time -- 指的是mysql在关闭一个交互的连接之前所要等待的秒数，比如我们在终端上进入mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动断开。默认数值是28800{8小时}，可调优为7200。

 

9：增大用户链接数：

有时候突然之间数据库的性能变慢；链接客户需要好长的时间才能的到响应，甚至有时候收不到，客户就会不断的进行链接，这样数据库就更加的繁忙了，最后情况严重的话可能导致数据库挂机，这里需要设置最大链接数量

 

1.max_connections

MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

mysql>show variables like ‘max_connections’ 最大连接数

mysql>show  status like ‘max_used_connections’响应的连接数

max_used_connections / max_connections * 100% （理想值≈ 85%） 

如果max_used_connections跟max_connections相同那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。

如何设置max_connections?

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容，如设置最大连接数为1024

max_connections = 1024

 

10：MySQL的堆栈设置：2.back_log

如果当链接数用户过多，而且链接的最大数量不够使用的时候可以设置堆栈，类似一个房间，讲过多的链接先存放起来，等处理完之前的链接之后再处理房间里的链接，如果等待连接的数量超过back_log，将不被授予连接资源。

back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它。

当观察你主机进程列表（mysql> show full processlist），发现大量

xxxxx | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大back_log的值了或加大max_connections的值。

通过mysql> show variables like 'back_log';查看back_log的设置

如何设置back_log?

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容，如设置最大连接数为1024

back_log =数值{1024}

重启mysql服务

对于以上关于mysql优化索引、配置，及慢查询讲解，如果大家还有更多需要了解的可以持续关注我们创新互联的行业推新，如需获取专业解答，可在官网联系售前售后的，希望该文章可给大家带来一定的知识更新。

 

文章标题：mysql优化索引、配置，及慢查询讲解
分享路径：http://cdiso.cn/article/pigchd.html