MySQL InnoDB Row Formats

最新推荐文章于 2024-11-11 21:02:50 发布

P("Struggler") ?

最新推荐文章于 2024-11-11 21:02:50 发布

阅读量771

点赞数 25

分类专栏： Data 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_38233104/article/details/138468795

版权

Good Journey 同时被 2 个专栏收录

124 篇文章 0 订阅

订阅专栏

Data

66 篇文章 0 订阅

订阅专栏

Intro

Row Format Impact: The row format determines how table rows are physically stored, which can affect query performance and DML (Data Manipulation Language) operations. More rows fitting into a single disk page can lead to faster queries and index lookups, reduced cache memory requirements, and less I/O for updating values.
Data Storage: Data in each table is divided into pages, and these pages are arranged in a B-tree index, which is used for both table data and secondary indexes. The primary key columns organize the clustered index, which is a type of B-tree index representing an entire table.
Variable-Length Columns: These columns, if too long to fit on a B-tree page, are stored on separate disk pages known as overflow pages. These are referred to as off-page columns and are linked in a singly-linked list.
Row Formats: InnoDB supports four row formats:
- REDUNDANT: Offers compatibility with older MySQL versions and stores the first 768 bytes of variable-length columns in the index record.
- COMPACT: Reduces row storage space by about 20% compared to REDUNDANT but may increase CPU usage.
- DYNAMIC: Similar to COMPACT but offers enhanced storage for long variable-length columns and supports large index key prefixes up to 3072 bytes.
- COMPRESSED: Like DYNAMIC, but adds support for table and index data compression. It uses smaller page sizes and is only supported in file-per-table or general tablespaces.
Defining Row Format: The default row format for InnoDB tables is defined by the innodb_default_row_format system variable, which defaults to DYNAMIC. The row format for a table can be explicitly set using the ROW_FORMAT option in CREATE TABLE or ALTER TABLE statements.
Determining Row Format: The row format of a table can be determined using the SHOW TABLE STATUS command or by querying the INFORMATION_SCHEMA.INNODB_TABLES table.
Storage Characteristics: Each row format has specific storage characteristics that define how data is organized and stored within the index records, including the handling of NULL values, variable-length columns, and fixed-length columns.
Table Rebuilding Operations: Operations that rebuild a table, such as certain ALTER TABLE commands or OPTIMIZE TABLE, may change the row format to the default if one is not explicitly defined.
Replication and Schema Mismatch: When changing row formats, especially in replication environments, it's important to consider compatibility issues that may arise due to differences in supported index key prefix lengths or default row format settings between the source and replica systems.