MySQL中MyISAM引擎及InnoDB引擎的缓存优化设计

Home

Database

Mysql Tutorial

MySQL中MyISAM引擎及InnoDB引擎的缓存优化设计_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:17 PM

MyISAM引擎中，为了提高io效率以及读取效率，将对磁盘频繁读取的索引数据加载至内存中操作。

MyISAM设计了一个在存放在内存中的索引缓冲池Key Cache。Key Cache只缓存索引数据，通过LRU算法将读取频繁的索引加载到Key Cache中来。

通过系统变量 key_buffer_size 来控制Key Cache的大小，这个变量关乎到缓存的性能。

InnoDB引擎中，同样设置了缓存池buffer pool，与MyISAM有所区别的是，buffer pool不仅仅缓存了索引数据，同时还缓存了表数据。

这样的缓冲池同时也带来了很多问题：缓存中的数据如何与磁盘上的数据保持一致，缓存中的数据支不支持修改更新操作，以及与日志记录模块的同步等等问题。

要解决这些问题带来的操作时繁琐的，但是相比于整体性能的提升，也是值得的：硬盘的存取速度与内存的速度更本不是一个数量级的，通过内存来读取数据，可以大大的提高数据库的整体性能。

MySQL官方文档这样建议，除了用于系统运行的内存外，剩余的内存建议尽可能大的设置buffer pool。这样一来有着大容量的buffer pool，在实际应用上的表现更像与一个in-memory database，相比于对磁盘的读写速度，读写性能简直就是巨大的提升。

这一切当然基于读取数据在buffer pool的命中率上面，修改更新等操作会改变buffer pool的一些数据，通过LRU算法更新，将buffer pool的命中率维持在一个比较高的水平。

还有一个问题就是怎么将buffer pool中的数据同步到磁盘。想想如果更新一次buffer pool就写一次磁盘，那这样子的效率和直接读写磁盘并没有提高多少，这里就需要设计出同步策略来解决这个问题。

InnoDB是事务安全的，修改buffer pool的数据后，同时还要将此操作记录在事务日志中去。这里对buffer pool的修改操作后，并没有直接将数据同步到磁盘，而是将此操作记录到事务日志文件中去。这里又有一个疑问，为什么不将数据写到磁盘的表数据文件里去，而是写到磁盘的事务日志文件去呢，同样是磁盘写操作，有何不同？

这里涉及到磁盘寻道读写问题，学过计算机组成原理的就知道了，磁盘读写可以分为两种：顺序读写以及随机读写，如果为随机读写，将要花一定的时间用于磁头寻址上，如果为顺序读写，则是连续的将数据写入磁面，磁头寻址操作很少。这两种读写方式的效率也可见区别甚大

事务日志文件是InnoDB引擎申请连续物理空间的固定大小的一个文件，对日志文件的读写基本上是顺序读写，寻址操作甚少。

而buffer pool中的表数据多而复杂：多个表的数据文件在磁盘中的存储空间是不同的，具有随机性，若每次更新buffer pool中的数据到磁盘，每次操作的表空间表现出随机性，对磁盘的读写也是随机的，这样以来频繁的寻址读写操作，将使磁盘处于一个繁忙随机读写状态。

所以buffer pool的策略也使得整体io性能得到了提升。

转载请注明出处：http://www.cnblogs.com/iamsupercp/ ,谢谢合作

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Explain the role of InnoDB redo logs and undo logs.Apr 15, 2025 am 12:16 AM

InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.

What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?Apr 15, 2025 am 12:15 AM

Key metrics for EXPLAIN commands include type, key, rows, and Extra. 1) The type reflects the access type of the query. The higher the value, the higher the efficiency, such as const is better than ALL. 2) The key displays the index used, and NULL indicates no index. 3) rows estimates the number of scanned rows, affecting query performance. 4) Extra provides additional information, such as Usingfilesort prompts that it needs to be optimized.

What is the Using temporary status in EXPLAIN and how to avoid it?Apr 15, 2025 am 12:14 AM

Usingtemporary indicates that the need to create temporary tables in MySQL queries, which are commonly found in ORDERBY using DISTINCT, GROUPBY, or non-indexed columns. You can avoid the occurrence of indexes and rewrite queries and improve query performance. Specifically, when Usingtemporary appears in EXPLAIN output, it means that MySQL needs to create temporary tables to handle queries. This usually occurs when: 1) deduplication or grouping when using DISTINCT or GROUPBY; 2) sort when ORDERBY contains non-index columns; 3) use complex subquery or join operations. Optimization methods include: 1) ORDERBY and GROUPB

Describe the different SQL transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and their implications in MySQL/InnoDB.Apr 15, 2025 am 12:11 AM

MySQL/InnoDB supports four transaction isolation levels: ReadUncommitted, ReadCommitted, RepeatableRead and Serializable. 1.ReadUncommitted allows reading of uncommitted data, which may cause dirty reading. 2. ReadCommitted avoids dirty reading, but non-repeatable reading may occur. 3.RepeatableRead is the default level, avoiding dirty reading and non-repeatable reading, but phantom reading may occur. 4. Serializable avoids all concurrency problems but reduces concurrency. Choosing the appropriate isolation level requires balancing data consistency and performance requirements.

MySQL vs. Other Databases: Comparing the OptionsApr 15, 2025 am 12:08 AM

MySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.

How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AM

MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.

MySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AM

The MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.

Real-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AM

MySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.

See all articles