MySQL读书学习笔记（六）——优化服务器设置

6.1 配置的工作原理

获取MySQL配置信息：命令行参数和配置文件。在类Unix系统中，配置文件一般在/etc/my.cnf或者/etc/mysql/my.cnf。如果使用操作系统的启动脚本，这通常是唯一指定配置设置的地方。如果手动启动MySQL，例如在测试安装时，也可以在命令行指定设置。实际上，服务器会读取配置文件的内容，删除所有注释和换行，然后和命令行选项一起处理。

6.1.1 语法、作用域和动态性

配置项设置都使用小写，单词之间用下划线或横线隔开。

配置项可以有多个作用域。

除了配置文件中设置变量，有很多变量也可以在服务器运行时修改。这就是动态配置变量。

6.1.2 设置变量的副作用

务必小心那些可以在线更改的设置，因为它们可能导致数据库做大量工作。

6.1.3 入门

设置变量时不是越大越好，如果值设置的太高，更容易导致问题：可能会由于内存不足导致服务器内存交换，或者超过地址空间。

6.1.4 基准测试

不建议使用

6.2 什么不该做

不要根据一些“比率”来调优。

不要相信内存消耗公式。

6.3 创建配置文件

MySQL看起来好像要花很多时间在配置上，其实大多数配置的默认值已经是最佳配置了，所以最好不要改动太多配置。

6.3.1 检查服务器状态变量

有时可以使用SHOW GLOBAL STATUS的输出，作为配置的输入，以更好地通过工作负载来自定义配置。为了达到最佳效果，既要看绝对值，又要看值时如何随时间而改变的，最好为高峰和非高峰时间的值做几个快照。

6.4 配置内存使用

配置MySQL正确地使用内存量对高性能是至关重要的。肯定要根据需求来定制内存使用。可以任务MySQL的内存消耗分为两类：可以控制内存和不可以控制内存。无法控制MySQL服务器运行、解析查询，以及其内部管理所消耗的内存，但是为特定目的而使用多少内存则有很多参数可以控制。用好可以控制的内存并不难，但需要对配置的含义非常清楚。

6.4.1 可以使用多少内存

在任何给定的操作系统上，MySQL都有允许使用的内存上限。基本出发点是机器上安装了多少物理内存。还要考虑操作系统或结果的限制。

6.4.2 每个连接需要的内存

MySQL保持一个连接值需要少量的内存。它还要求一个基本量的内存来执行任何给定查询。需要为高峰时期执行的大量查询预留好足够的内存。否则，查询执行可能因为缺乏内存而导致执行效率不佳或执行失败。

6.4.3 为操作系统保留内存

跟查询一样，操作系统也需要保留足够的内存给它工作。如果没有虚拟内存正在交换到磁盘，就是表面操作系统内存足够的最佳迹象。

6.4.4 为缓存分配内存

如果服务器只运行MySQL，所有不需要为操作系统以及查询处理保留的内存都可以用作MySQL缓存。

6.4.5 InnoDB缓冲池

如果大部分都是InnoDB表，InnoDB缓冲池或许比其他东西更需要内存。InnoDB缓冲池并不仅仅缓冲索引：它还会缓存行的数据、自适应哈希索引、插入缓冲、锁，以及其他内部数据结构。InnoDB还使用缓冲池来帮助延迟写入，这样就能合并多个写入操作，然后一起顺序地写回。总之，InnoDB严重依赖缓冲池，必须确认为他分配了足够的内存。

6.4.6 MyISAM键缓存

也被称为键缓冲，默认只有一个键缓存，但也可以创建多个。不像InnoDB和其他一些存储引擎，MyISAM自身只缓存索引，不缓存数据。如果大部分是MyISAM表，就应该为键缓存分配比较多的内存。

6.4.7 线程缓存

保存那些当前没有与连接关联但是准备为后面新的连接服务的线程。当一个新的连接创建时，如果缓存中有线程存在，MySQL从缓存中删除一个线程，并且把他分配给这个新的连接。当连接关闭时，如果线程缓存还有空间的话，MySQL又会把线程放回缓存。如果没有空间的话，MySQL会销毁这个线程。只要MySQL在缓存里还有空闲的线程，它就可以迅速地响应连接请求，因为这样就不用为每个连接创建新的线程。

6.4.8 表缓存

与线程缓存的概念相似，但存储的对象代表的是表。每个在缓存中的对象包含相关表.frm文件的解析结果，加上一些其他数据。准确地说，在对象里的其他数据的内容依赖于表的存储引擎。

6.5 配置I/O行为

6.5.1 InnoDB I/O配置

InnoDB不仅允许控制怎么恢复，还允许控制怎么打开和刷新数据，这会对恢复和整体性能产生巨大影响。尽管可以影响它的行为，InnoDB的恢复流程实际上是自动的，并且经常在InnoDB启动时运行。撇开恢复并假设InnoDB没有崩溃或出错，InnoDB依然有很多需要配置的地方。它有一系列复杂的缓存和和文件设计可以提升性能，以及保证ACID特性，并且每一部分都是可配置的。

6.5.2 MyISAM I/O配置

MyISAM通常每次写操作之后就把索引变更刷新磁盘。如果打算在一张表上做很多修改，那么毫无疑问，批量操作会快一些。一种方法是用LOCK TABLES延迟写入，直到解锁这些表。这是个提升性能的很有价值的技巧，因为它使你精确控制哪些写被延迟，以及什么时候把他们刷到磁盘。可以精确延迟哪些希望延迟的语句。

6.6 配置并发

6.6.1 InnoDB配置并发

InnoDB架构在有限的内存、单CPU、单磁盘系统中仍然暴露一些根本性的问题。在高并发场景下，InnoDB的某些方面的性能可能会降低，唯一的办法是限制并发。

6.6.2 MyISAM并发配置

在某些条件下，MyISAM也运行并发插入和读取，这使得可以调度某些操作以尽可能地产生阻塞。

6.7 基于工作负载的配置

6.7.1 优化BLOB和TEXT场景

通过SUBSTRING函数把值转换成VARCHAR，或者让临时表快一些。

6.7.2 优化排序

如果查询中所有需要的列和ORDER BY的列总大小超过max_length_for_sort_data，则采用two_pass算法。或者当任何需要的列是BOLB或TEXT，也会采用这个算法。