mysql myisammaxsortfilesize_MySQL 可优化的一些参数详解

mysql里有些变量设置可用于server层面的调优,多是cache和buffer之类,有的是为整个server分配的大小,有些则是为每个session/thread分配的。为每个session/thread分配大小的变量尤其要注意不要设置太大。

sort_buffer_size

Each sessionthat needs to do a sort allocates a buffer of thissize. sort_buffer_size isnot specific to any storage engine and applies in ageneral manner foroptimization.

If you see manySort_merge_passes per second in SHOW GLOBALSTATUS output, you can considerincreasing the sort_buffer_size value to speedup ORDER BY or GROUP BYoperations that cannot be improved with queryoptimization or improvedindexing.

As of MySQL5.6.4, the optimizer tries to work out how much spaceis needed but canallocate more, up to the limit. Before MySQL 5.6.4, theoptimizer allocates theentire buffer even if it is not all needed

每一个session都会分配设定的sort_buffer_size大小的内存(基于此,此值不可以设置的过大,否则能会导致内存溢出)。此参数跟存储引擎和应用类型无关,是一个通用的优化项。

如果在SHOWGLOBAL STATUS看到每秒的Sort_merge_passes增长较大,你可以考虑适当增大sort_buffer_size,以求在无法进一步通过优化sql语句和优化索引的情况下取得更好的效率。

在MySQL5.6.4及以上的版本,优化器尝试找出实际需要的内存(并不是一次性分配所有),但是可以根据实际需要分配超过设置的内存;在MySQL5.6.4之前的版本,优化器一次性分配所有设定的内存无论是否需要。

tmp_table_size

The maximum sizeof internal in-memory temporary tables. (Theactual limit is determined as theminimum of tmp_table_size andmax_heap_table_size.) If an in-memory temporarytable exceeds the limit, MySQLautomatically converts it to an on-disk MyISAMtable. Increase the value oftmp_table_size (and max_heap_table_size ifnecessary) if you do many advancedGROUP BY queries and you have lots ofmemory.

内部临时表的最大内存使用量(实际的限制是由tmp_table_size和max_heap_table_size两个参数中较小的一个决定的),如果内部临时表超过了限制,MySQL自动将其转换为MyISAM表存储于磁盘上。如果你经常做一些高级/复杂的GROUPBY语句并且你有比较大的内存,可以适当增加此变量的值(适当增加此值,可以减少磁盘临时表的创建,减少IO)

max_heap_table_size

This variablesets the maximum size to which user-created MEMORYtables are permitted togrow. The value of the variable is used to calculateMEMORY table MAX_ROWSvalues. Setting this variable has no effect on anyexisting MEMORY table,unless the table is re-created with a statement such asCREATE TABLE or alteredwith ALTER TABLE or TRUNCATE TABLE. A server restartalso sets the maximum sizeof existing MEMORY tables to the globalmax_heap_table_size value.

这个变量设置允许用户创建的内存表的最大大小。变量的值用于计算内存表MAX_ROWS值。设置这个变量不影响任何现有的内存表,除非表被重新创建或者用ALTERTABLE或TRUNCATE TABLE修改。服务重启也会将现有内存表的最大行数设置为全局max_heap_table_size的值。

read_buffer_size

Each thread thatdoes a sequential scan for a MyISAM tableallocates a buffer of this size (inbytes) for each table it scans. If you domany sequential scans, you might wantto increase this value, which defaults to131072. The value of this variable shouldbe a multiple of 4KB. If it is set toa value that is not a multiple of 4KB,its value will be rounded down to thenearest multiple of 4KB

This option isalso used in the following context for all searchengines:

·For caching theindexes in atemporary file (not a temporary table), when sorting  rows forORDER BY.

·For bulk insertinto partitions.

·For cachingresults of nestedqueries.

and in one otherstorage engine-specific way:to determine the memory block size for MEMORY tables.Themaximum permissiblesetting for read_buffer_size is 2GB.

每个顺序/全表扫描MyISAM表的线程分配该值大小的内存,如果有很多全表扫描,你可能希望增加这个值,默认值是131072,这个变量的值应该是4K的倍数,如果不是,则MySQL自动将其减小为最近的4k倍数的值。

这个选项也可以在一下环境/条件下被其他存储引擎所使用:

·当进行ORDER BY排序时,在一个临时文件缓存索引(不是临时表)

·对于批量插入分区的操作

·缓存嵌套查询的结果

在另一个存储引擎指定的方式下:决定内存表的内存块大小

这个值得最大值为2G

max_sort_length

The number ofbytes to use when sorting data values. Only thefirst max_sort_length bytes ofeach value are used; the rest are ignored.As of MySQL5.6.9, max_sort_length[503] is ignored for integer, decimal, floating-point,and temporal data types.

对值进行排序时前多少字节被使用,每个值/结果只有最前面的max_sort_length字节被使用,其余的被忽略。对于MySQL5.6.9版本,该值对于integer, decimal,floating-point, and temporal的数据类型无效。默认值是1k

myisam_max_sort_file_size

The maximum sizeof the temporary file that MySQL is permitted touse while re-creating a MyISAMindex (during REPAIR TABLE, ALTER TABLE, or LOADDATA INFILE). If the file sizewould be larger than this value, the index iscreated using the key cacheinstead, which is slower. The value is given inbytes.

The defaultvalue is 2GB. If MyISAM index files exceed this sizeand disk space isavailable, increasing the value may help performance. Thespace must beavailable in the file system containing the directory where theoriginal indexfile is located.

MySQL允许的用于MyISAM表重建索引(在REPAIRTABLE, ALTER TABLE, o或LOAD DATA INFILE期间)的最大存储空间(在磁盘)。如果文件的大小超过此值,索引会用keycache代替,这样会慢一些。

默认是2G,如果MyISAM表的索引文件大小超过此值并且磁盘空间充裕,增加此变量的值可以提升效率。必须在原有的索引文件所在的分区上有磁盘空间。

未完。。。

附上一张MySQL内存使用量说明的图表:

86cad6c73de34ab054e3188ba7c335fd.png

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL中,使用`FIND_IN_SET`函数进行查询时,可能会遇到性能方面的问题。下面是一些优化建议: 1. 索引优化:为涉及到`FIND_IN_SET`函数的列创建索引。这将加快查询速度。请注意,对于`FIND_IN_SET`函数,MySQL无法充分利用索引,因此这种优化策略可能并不总是有效。 2. 数据结构优化:考虑是否可以更改数据结构,避免使用`FIND_IN_SET`函数。例如,将存储为逗号分隔的值改为单独的关联表,这样可以直接通过关联查询来获取结果。 3. 使用字段分割函数:尝试使用更高效的字段分割函数来替代`FIND_IN_SET`函数。例如,使用`SUBSTRING_INDEX`函数进行字符串分割操作。 4. 查询重写:有时候,可以将`FIND_IN_SET`函数改写成其他形式的条件来提高查询性能。例如,使用`IN`操作符或者多个等于操作符来替代`FIND_IN_SET`。 5. 数据优化:检查相关表中的数据质量和数据量。如果表中存在大量冗余或无效的数据,可以考虑清理或优化数据。此外,如果表中的数据量非常庞大,可能需要使用分区或分库分表等技术来改善查询性能。 6. 硬件优化:如果数据库服务器负载较高,可以考虑升级硬件或增加服务器数量来提高整体性能。 请注意,以上方法可能不适用于所有情况,具体的优化策略需要根据你的数据和查询情况进行调整。你可以使用MySQL提供的性能分析工具(如EXPLAIN语句和慢查询日志)来深入分析查询执行计划和性能瓶颈,以找到最佳的优化方案。 希望以上解决方案对你有所帮助!如果还有其他问题,请随时提问。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值