学而思则优！MySQL优化服务器专题收关，实战解析不迷茫

最近咱们一直在更新MySQL性能优化，内容也是由浅及深，如果有朋友哪里还不清楚，随时联系小编，咱们共同探讨进步。今天，咱们继续更新——优化MySQL服务器。

优化MySQL服务器主要从两个方面来优化，一方面是对硬件进行优化；另一方面是对MySQL服务的参数进行优化。这部分的内容需要较全面的知识，一般只有专业的数据库管理员才能进行这一类的优化。 对于可以定制参数的操作系统，也可以针对MySQL进行操作系统优化。

 

优化服务器硬件

服务器的硬件性能直接决定着MySQL数据库的性能硬件的性能瓶颈直接决定MySQL数据库的运行速度和效率。针对性能瓶颈，提高硬件配置，可以提高MySQL数据库的查询、更新的速度。

(1)配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。内存的速度比磁盘I/0快得多，可以通过增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘I/O。

(2)配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。

(3)合理分布磁盘IO,把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。

(4)配置多处理器，MySQL 是多线程的数据库，多处理器可同时执行多个线程。

 

优化MySQL的参数

通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。

MySQL服务的配置参数都在my.cnf或者my.ini文件的[MySQLd]组中。下面对几个对性能影响比较大的参数进行详细介绍。

• key_ buffer size：表示索引缓冲区的大小。索引缓冲区所有的线程共享。增加索引缓冲区可以得到更好处理的索引(对所有读和多重写)。当然，这个值也不是越大越好，它的大小取决于内存的大小。如果这个值太大，导致操作系统频繁换页，也会降低系统性能。
• table_ cache: 表示同时打开的表的个数。这个值越大，能够同时打开的表的个数越多。这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。
• query_ cache_ size.：表示查询缓冲区的大小。该参数需要和query_cache_type 配合使用。当query_cache_type值是0时，所有的查询都不使用查询缓冲区。但是query_ cache_type=0并不会导致MySQL释放query_ cache_size所配置的缓冲区内存。当query_cache. type=1时，所有的查询都将使用查询缓冲区，除非在查询语句中指定SQL_ NO_ CACHE，如SELECT SQL_ NO_ CACHE * FROM tbl name，当query_ cache_type=2时，只有在查询语句中使用SQL_ CACHE关键字，查询才会使用查询缓冲区。使用查询缓冲区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的查询操作的情况。
• sort_buffer_size：表示排序缓存区的大小。这个值越大，进行排序的速度越快。
• read_buffer_size:表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。当线程从表中连续读取记录时需要用到这个缓冲区。SET SESSION read_ buffer size- n可以临时设置该参数的值。
• read_ md_ buffer_ size:表示为每个线程保留的缓冲区的大小与read buffer size 相似。但主要用于存储按特定顺序读取出来的记录。也可以用SET SESSION read_ md buffer size=n 来临时设置该参数的值。如果频繁进行多次连续扫描，可以增加该值。
• innodb_ buffer pool size: 表示InnoDB 类型的表和索引的最大缓存。这个值越大，查询的速度就会越快。但是这个值太大会影响操作系统的性能。
• max_ connections: 表示数据库的最大连接数。这个连接数不是越大越好，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。
• innodb_ flush_ log_ at _trx_ commit:表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别为0、1和2。值为0时表示每隔1秒将数据写入日志文件并将日志文件写入磁盘；值为1时表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘；值为2时表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘。该参数的默认值为1.默认值1安全性最高，但是每次事务提交或事务外的指令都需要把日志写入(flush) 硬盘，是比较费时的； 0值更快一点，但安全方面比较差； 2值日志仍然会每秒写入到硬盘，所以即使出现故障，一般也不会丢失超过1~2秒的更新。
• back_ log: 表示在MySQL暂时停止回答新请求之前的短时间内，多少个请求可以被存在堆栈中。换句话说，该值表示对到来的TCP/IP连接的侦听队列的大小。只有期望在一个短时间内有很多连接，才需要增加该参数的值。操作系统在这个队列大小。上也有限制。设定back_ log 高于操作系统的限制将是无效的。
• interactive_ ftimeout: 表示服务器在关闭连接前等待行动的秒数。sort buffer size: 表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或GROUP BY操作的速度。默认数值是2097 144 (2MB) 。
• thread_ cache_ size: 表示可以复用的线程的数量。如果有很多新的线程，为了提高性能可以增大该参数的值。
• wait timeout: 表示服务器在关闭一个连接时等待行动的秒数。默认数值是28 800。

合理地配置这些参数可以提高MySQL服务器的性能。除上述参数以外，还有innodb_ log_buffer_ size. innodb_ log. file_ size 等参数。配置完参数以后，需要重新启动MySQL服务才会生效。

以上就是小编整理的MySQL优化服务器，个人见解，欢迎各位大佬留言交流哦~~~

学而思则优，学而不思则罔。最后，小编将MySQL性能优化中的个人疑问总结了一下，看是否和大家的疑问大致一样，大家也可以试着思考，提出疑问，这样的学习效果，小编认为还是很有效果的~~~

 

疑问1 :是不是索引建立得越多越好?

合理的索引可以提高查询的速度，但不是索引越多越好。在执行插入语句的时候，MySQL要为新插入的记录建立索引。所以过多的索引会导致插入操作变慢。原则上是只有查询用的字段才建立索引。

疑问2 :为什么查询语句中的索引没有起作用?

在一些情况下，查询语句中使用了带有索引的字段。但索引并没有起作用。例如，在.WHERE条件的LIKE关键字匹配的字符串以“%”开头，这种情况下索引不会起作用。又如，WHERE条件中使用OR关键字连接查询条件，如果有1个字段没有使用索引，那么其他的索引也不会起作用。如果使用多列索引，但没有使用多列索引中的第1个字段，那么多列索引也不会起作用。

疑问3 :如何使用查询缓冲区?

查询缓冲区可以提高查询的速度，但是这种方式只适合查询语句比较多、更新语句比较少的情况。默认情况下查询缓冲区的大小为0,也就是不可用。可以修改query_ _cache_ size 调整查询缓冲区大小:修改query_ cache, _type 以调整查询缓冲区的类型。在my.ini 中修改query_ cache_ size和query_ cache_ type 的值如下所示:

[mysq1d]
query_ cache size=512M
query_ cache_ type=1

query_ cache_ type=1 表示开启查询缓冲区。只有在查询语句中包含SQL_ _NO_ CACHE关键字时，才不会使用查询缓冲区。可以使用FLUSH QUERY CACHE语句来刷新缓冲区，清理查询缓冲区中的碎片。

喜欢小编请多多点赞评论分享，关注小编，私信小编免费获取海量学习资料哦~~~