MyISAM存储引擎的优化
作为mysql最流行最古老的存储引擎之一的MyISAM,其在以读为主的非事务性系统中的优异表现成为多数程序员的首选。
MyISAM 存储引擎的缓存策略是其和很多其他数据库乃至mysql其他的存储引擎都不一样,MyISAM只缓存索引而不会缓存数据。
MyISAM的索引和数据是分别存储在 ".MYI" 文件中的,索引和数据是分别以 cache block 和file block 的形式组织存放的,但实际上这两种数据都是以
file block 存放在磁盘上的。在一条query中通过索引查找数据会先检索索引缓存是否存在,不存在的话再去 MYI文件中查找数据,之后缓存到
key cache 。
请尊重知识,请尊重原创 更多资料参考请见 http://www.cezuwang.com/listFilm?page=1&areaId=906&filmTypeId=1
分析几个与MyISAM索引缓存相关的几个系统参数。
1 key buffer size 索引缓存大小
在32位机上不要超过2GB,在64位机上不要超过4GB。
2 key_buffer_block_size 索引缓存中的cache block size
在key cache中的所有数据都是以cache block 形式存在的。这个参数就是设置每个cache block的大小。
3 key_cache_division_limit LRU链表中的hot area和warm area 分解值。
热数据和冷数据的分解值,设置范围为0-100 系统默认为100,也就是只有冷数据。参数值为冷数据占整个数据的百分比。
4 key_cache_age_threshold 控制cache block从热数据降到冷数据的限制。
系统默认值300 最小可设置100 ,值越小,被降级的可能性越大。
通过以上几个参数的设置,基本上可以完成MyISAM整体优化的70%的工作,但是如何优化参数,并不是一个简单的事情。
如果需要详细了解这几个参数的特性 可以参考mysql官方文档和 <Mysql性能调优与架构设计> 中的第十一章。
对于key_buffer_size的设置我们一般通过三个指标来计算。1 系统索引的大小,2 系统可用的物理内存,3 系统当前的key cache 命中率。
我们可以通过Mysql官方手册提供的计算公式来粗略计算。
key_size = key_number * (key_length + 4) /0.67
Max_key_buffer_size < Max_ram - QCache_Usage - Threads_Usage-System_Useage
Threads_Usage = max_connections * (sort_buffer_size + join_buffer_size + read_buffer_size + read_rnd_buffer_size + thread_stack)
当然,考虑到活跃数据的问题,我们并不需要将key_buffer_size 设置到可以将所有的索引都放下
的大小,这时候我们就需要Key Cache 的命中率数据来帮忙了。下面我们再来看一下系统中记录的与Key
Cache 相关的性能状态参数变量。
◆ Key_blocks_not_flushed,已经更改但还未刷新到磁盘的Dirty Cache Block;
◆ Key_blocks_unused,目前未被使用的Cache Block 数目;
◆ Key_blocks_used,已经使用了的Cache Block 数目;
◆ Key_read_requests,Cache Block 被请求读取的总次数;
◆ Key_reads,在Cache Block 中找不到需要读取的Key 信息后到“.MYI”文件中读取的次数;
◆ Key_write_requests,Cache Block 被请求修改的总次数;
◆ Key_writes,在Cache Block 中找不到需要修改的Key 信息后到“.MYI”文件中读入再修改的次
数;
由于上面各个状态参数在MySQL 官方文档中都有较为详细的描述,所以上面仅做基本的说明。当我
们的系统上线之后,我们就可以通过上面这些状态参数的状态值得到系统当前的Key Cache 使用的详细
情况和性能状态。
Key_buffer_UsageRatio = (1 - Key_blocks_used/(Key_blocks_used + Key_blocks_unused)) *
100%
Key_Buffer_Read_HitRatio = (1 - Key_reads/Key_read_requests) * 100%
Key_Buffer_Write_HitRatio = (1 - Key_writes/Key_Write_requests) * 100%
通过上面的这三个比率数据,就可以很清楚的知道我们的Key Cache 设置是否合理,尤其是
Key_Buffer_Read_HitRatio 参数和Key_buffer_UsageRatio 这两个比率。一般来说
Key_buffer_UsageRatio 应该在99%以上甚至100%,如果该值过低,则说明我们的key_buffer_size 设置
过大,MySQL 根本使用不完。Key_Buffer_Read_HitRatio 也应该尽可能的高。如果该值较低,则很有可
能是我们的key_buffer_size 设置过小, 需要适当增加key_buffer_size 值, 也有可能是
key_cache_age_threshold和key_cache_division_limit的设置不当,造成Key Cache cache失效太快。
一般来说,在实际应用场景中,很少有人调整key_cache_age_threshold 和key_cache_division_limit
这两个参数的值,大都是使用系统的默认值。
多Key Cache 的使用
从MySQL4.1.1 版本开始,MyISAM 开始支持多个Key Cache 并存的的功能。也就是说我们可以根据不
同的需要设置多个Key Cache 了,如将使用非常频繁而且基本不会被更新的表放入一个Key Cache 中以
防止在公共Key Cache 中被清除出去,而那些使用并不是很频繁而且可能会经常被更新的Key 放入另外一
个Key Cache 中。这样就可以避免出现某些场景下大批量的Key 被读入Key Cache 的时候,因为Key
Cache 空间问题使本来命中率很高的Key 也不得不被清除出去。
MySQL 官方建议在比较繁忙的系统上一般可以设置三个Key Cache:
一个Hot Cache 使用20%的大小用来存放使用非常频繁且更新很少的表的索引;
一个Cold Cache 使用20%的大小用来存放更新很频繁的表的索引;
一个Warm Cache 使用剩下的60%空间,作为整个系统默认的Key Cache;
请尊重知识,请尊重原创 更多资料参考请见 http://www.cezuwang.com/listFilm?page=1&areaId=906&filmTypeId=1
NULL 值对统计信息的影响
虽然都是使用B-Tree 索引,但是MyISAM 索引和Oracle 索引的处理方式不太一样,MyISAM 的索引中
是会记录值为NULL 的列信息的,只不过NULL 值的索引键占用的空间非常少。所以,NULL 值的处理方式
可能会影响到MySQL 的查询优化器对执行计划的选择。所以MySQL 就给我们提供了myisam_stats_method
这个参数让我们可以自行决定对索引中的NULL 值的处理方式。
myisam_stats_method 参数的作用就是让我们告诉MyISAM 在收集统计信息的时候,是认为所有NULL
值都是等同还是认为每个NULL 值都认为是完全不相等的值,所以其可设置的值也为nulls_unequal 和
nulls_equal。
当我们设置myisam_stats_method = nulls_unequal,MyISAM 在搜集统计信息的时候会认为每个
NULL 值都不同,则基于该字段的索引的Cardinality 就会更大,也就是说MyISAM 会认为DISTINCT 值数
量更多,这样就会让查询优化器处理Query 的时候使用该索引的倾向性更高。
而当我们设置myisam_stats_method = nulls_equal 之后,MyISAM 搜集统计信息的时候则会认为每
个NULL 值的都是一样的,这样Cardinality 数值会降低,优化器选择执行计划的时候放弃该索引的倾向
性会更高。
当然,上面所说的都是相对于使用等值查询的时候,而且NULL 值占比较大的情况下,如果我们的
NULL 值本身就很少,那不管我们是使用nulls_unequal 还是nulls_equal,对优化器选择执行计划的影响
是很小很小的。
MyISAM 并发优化
MyISAM在查询方面的并发没有多大问题,而且性能非常高。但是由于MyISAM存储引擎的表级锁定以及读写互斥的问题,其并发写一直是个问题。
我们能做的也只有下面几点:
1 打开concurrent_insert,设置值为2,告诉MyISAM在尾部并行插入,使insert和select互不干扰。
2 控制写入操作的大小。
3 提高写入的优先级
其他可以优化的地方
除了上面我们分析的这几个方面之外,MyISAM 实际上还存在其他一些可以优化的地方和一些常用的
优化技巧。
1. 通过OPTIMIZE 命令来整理MyISAM 表的文件。这就像我们使用Windows 操作系统会每过一段时间
后都会做一次磁盘碎片整理,让系统中的文件尽量使用连续空间,提高文件的访问速度。MyISAM 在
通过OPTIMIZE 优化整理的时候,主要也是将因为数据删除和更新造成的碎片空间清理,使整个文件
连续在一起。一般来说,在每次做了较大的数据删除操作之后都需要做一次OPTIMIZE 操作。而且每
个季度都应该有一次OPTIMIZE 的维护操作。
2. 设置myisam_max_[extra]_sort_file_size 足够大,对REPAIR TABLE 的效率可能会有较大改
善。
3. 在执行CREATE INDEX 或者REPAIR TABLE 等需要大的排序操作的之前可以通过调整session 级
别的myisam_sort_buffer_size 参数值来提高排序操作的效率。
4. 通过打开delay_key_write 功能,减少IO 同步的操作,提高写入性能。
5. 通过调整bulk_insert_buffer_size 来提高INSERT...SELECT...这样的bulk insert 操作的整
体性能,LOAD DATA INFILE...的性能也可以得到改善。当然,在设置此参数的时候,也不应该一味
的追求很大,很多时候过渡追求极端反而会影响系统整体性能,毕竟系统性能是从整体来看的,而
不能仅仅针对某一个或者某一类操作。
相关推荐
以下是对MyISAM存储引擎优化策略的详细解析。 1. 索引缓存优化: MyISAM存储引擎的独特之处在于它只缓存索引数据,而非实际的表数据。这使得在MyISAM环境下,优化工作主要集中在索引缓存上。索引文件以`.MYI`格式...
- **基础描述**:MyISAM是MySQL早期版本的默认存储引擎,不支持事务处理和行级锁,但提供了全文索引支持。 - **特点描述**: - **锁表机制**:在读取数据时使用共享锁,在写入数据时使用排他锁。 - **全文索引**...
MyISAM 存储引擎支持全文索引,这对于优化文本搜索是非常有用的。 ```sql ALTER TABLE LogEntries ADD FULLTEXT(LogMessage); ``` 以上 SQL 语句为 `LogMessage` 字段添加了一个全文索引,从而可以更高效地进行...
MySQL数据库系统提供了多种存储引擎,其中最常用的两种是MyISAM和InnoDB。它们各自具有独特的特性和适用场景,理解二者的性能差异对于优化数据库设计至关重要。 MyISAM引擎是MySQL早期的默认存储引擎,以其高速度和...
MyISAM存储引擎在本地磁盘上为每个表创建三个文件,分别是.frm文件(存储表结构),.MYD文件(存储数据),.MYI文件(存储索引)。这种存储方式使得MyISAM在读取数据时,能够快速定位到相应的数据记录。 三、MRG_...
MyISAM存储引擎使用表级锁定机制,只支持表锁,这可能会影响在高并发情况下的性能。InnoDB则提供了更为复杂的行级锁定机制和MVCC(多版本并发控制),以优化并发事务性能。在索引方面,虽然两种存储引擎都支持B树...
* PERFORMANCE_SCHEMA:是一种性能优化的存储引擎,适合实时监控数据库性能。 * FEDERATED:是一种基于分布式数据库的存储引擎,适合分布式数据库环境。 3. 设置系统默认的存储引擎 可以使用 `show variables like...
11.1 MyISAM存储引擎优化 11.2 InnoDB存储引擎优化 11.3 小结 第3篇 架构设计篇 第12章 MySQL可扩展设计的基本原则 12.0 引言 12.1 什么是可扩展性 12.2 事务相关性最小化原则 12.3 数据一致性原则 12.4 ...
这篇“存储引擎及存储模型论文”似乎深入探讨了这些核心概念,尤其关注于性能优化。 首先,存储引擎是数据库系统的心脏,它负责处理数据的读写操作。例如,MySQL中的InnoDB存储引擎以其事务处理能力、行级锁定和...
当发现MyISAM存储引擎无法满足需求,需要转换为InnoDB时,可以按照以下步骤进行操作: 1. **导出表结构**: 使用`mysqldump`工具,加上参数`-d`,表示不导出数据,只导出表结构。例如: ``` mysqldump -d -uxxx ...
- **MyISAM**:这是MySQL的默认存储引擎之一,适用于Web应用和数据仓库环境。MyISAM不支持事务处理,但它提供了较快的读写速度,非常适合于读多写少的应用场景。 - **InnoDB**:广泛应用于需要事务支持的应用程序中...
3. **外键约束**:InnoDB支持外键,这是其他一些存储引擎如MyISAM所不具备的。外键可以确保数据的参照完整性和数据库间的关系。 4. **MVCC(多版本并发控制)**:InnoDB使用MVCC来实现高并发读写,尤其是在可重复读...
MySQL数据库的存储引擎是数据库管理的核心部分,它们决定了数据如何存储、检索以及处理事务。本文主要探讨两种最常用...在实际应用中,了解这两种存储引擎的特性并结合业务需求进行选择,是优化数据库性能的关键步骤。
在MySQL中,MyISAM和InnoDB是最常见的两种存储引擎,各自具有独特的特性和优化策略。 MyISAM引擎以其快速的全文搜索、对表锁的高效管理以及对内存的高效利用而受到欢迎。然而,它的主要缺点是不支持事务处理和行级...
本文将深入探讨MyISAM存储引擎的实现原理及其特性。 MyISAM是MySQL的默认存储引擎之一,它在磁盘上存储表数据的方式独具特色。每个MyISAM表由三个文件组成: 1. `.frm` 文件:存储表的结构信息,包括字段定义、...
MySQL数据库系统支持多种表类型,也称为存储引擎。这些存储引擎各具特色,适用于不同的应用场景,从而满足用户在性能、可扩展性、事务安全性等...了解并合理利用这些存储引擎特性,能够优化MySQL数据库的性能和效率。
选择正确的存储引擎是优化数据库性能的关键。了解每种引擎的优缺点和应用场景,可以帮助我们根据实际需求做出最佳决策。在实际工作中,根据业务需求选择合适的存储引擎,并进行适当的参数调整,是提升MySQL数据库...