简易mysql优化_优化 MySQL:简单三个技巧

原标题：优化 MySQL:简单三个技巧

技巧#1：为临时表分配足够的内存

在某些情况下，服务器在处理语句时会创建内部临时表。临时表用于内部操作如GROUP BY和distinct，还有一些ORDER BY查询以及UNION和FROM子句(派生表)中的子查询。这些都是在内存中创建的内存表。内存中临时表的最大大小由tmp_table_size和max_heap_table_size中较小的值确定。如果临时表的大小超过这个阈值，则将其转换为磁盘上的InnoDB或MyISAM表。此外，如果查询涉及BLOB或TEXT列，而这些列不能存储在内存表中，临时表总是直接指向磁盘。

这种转换的代价很大，所以考虑增加max_heap_table_size和tmp_table_size变量的大小来帮助减少在磁盘上创建临时表的数量。请记住，这将需要大量内存，因为内存中临时表的大小是基于“最坏情况”的。例如，内存表总是使用固定长度的列，所以字符列使用VARCHAR(255)。这可以使内存中的临时表比想象的要大得多—事实上，这比查询表的总大小要大很多倍！当增加max_heap_table_size和tmp_table_sizevariables的大小时，一定要监视服务器的内存使用情况，因为内存中的临时表可能会增加达到服务器内存容量的风险。

一般来说，32M到64M是建议值，从这两个变量开始并根据需要进行调优。

在Monyog中的临时表监测

临时表的监测是许多预定义的Monyog监测之一。它提供了一些临时表使用的指标，包括：

允许的最大值：显示tmp_table_size服务器变量的值，它定义了在内存中创建的临时表的最大大小。与max_heap_table_size一起，这个值定义了可以在内存中创建的临时表的最大大小。如果内存临时表大于此大小，则将其存储在磁盘上。

内存表的最大大小：显示max_heap_table_size服务器变量的值，该值定义了显式创建的MEMORY存储引擎表的最大大小。

创建的临时表总数：显示created_tmp_tables服务器变量的值，它定义了在内存中创建的临时表的数量。

在磁盘上创建的临时表：显示created_tmp_disk_tables服务器变量的值，该变量定义了在磁盘上创建的临时表的数量。如果这个值很高，则应该考虑增加tmp_table_size和max_heap_table_size的值，以便增加创建内存临时表的数量，从而减少在磁盘上创建临时表的数量。

磁盘：总比率：基于created_tmp_disk_tables除以created_tmp_tables的计算值。由于tmp_table_size或max_heap_table_size不足而在磁盘上创建的临时表的百分比。Monyog将这个数字显示为一个进度条和百分比，以便快速确定有多少磁盘用于临时表，而不是内存。

趋势图可用于创建的总表，磁盘上创建的表和磁盘的总比值。这些让我们看到了它们随着时间的演变：

技巧#2：使用 InnoDB 存储引擎

如果你还在使用 MyISAM 存储引擎，那么是时候转换到 InnoDB 了。有很多的理由都表明 InnoDB 比 MyISAM 更有优势，如果你关注性能，那么，我们来看一下它们是如何利用物理内存的：

MyISAM：仅在内存中保存索引。

InnoDB：在内存中保存索引和数据。

结论：保存在内存的内容访问速度要比磁盘上的更快。

下面是如何在你的表上去转换存储引擎的命令：

ALTER TABLE table_name ENGINE=InnoDB;

注意：你已经创建了所有合适的索引，对吗？为了更好的性能，创建索引永远是第一优先考虑的事情。

技巧#3：其他相关的服务器变量

除了上述指标以外，还应该监控以下内容：

InnoDB缓冲池大小： InnoDB缓冲池大小在使用InnoDB的MySQL数据库中起着至关重要的作用。缓冲池同时缓存数据和索引。它的值应该尽可能的大，以确保数据库使用内存而不是硬盘驱动器进行读取操作。

临时表大小： MySQL使用max_heap_table_size和tmp_table_size中较小的一个来限制内存中临时表的大小。拥有较大的值可以帮助减少在磁盘上创建临时表的数量，但也会增加服务器内存容量的风险，因为这个指标适用于每个客户端。一般来说，32M到64M是建议的值，从这两个变量开始并根据需要进行调优。

InnoDB日志缓冲区大小： MySQL每次写入日志文件时，它都会利用可用于处理销售数据的重要系统资源。因此，将InnoDB日志缓冲区大小设置为较大值才有意义。这样，服务器在大型事务中写入磁盘的次数就减少了，从而最大限度地减少了这些耗时的操作。64M是这个变量的一个很好的起点。

责任编辑：