Large object (LOB) types are used to store TEXT, binary large object (BLOB), JavaScript Object Notation (JSON), and GEOMETRY data. LOB data can be stored in INROW or OUTROW storage mode.
INROW storage
When INROW storage is used, LOB data is stored together with rows in the primary table. Only one access to storage is required to read LOB data.
OUTROW storage
When OUTROW storage is used, LOB data is stored in an LOB auxiliary table. To read LOB data, the database first obtains locators of the data by reading rows in the primary table and then reads actual LOB data from the LOB auxiliary table based on the locators. The whole process involves two accesses to the storage.
LOB storage switching
Whether INROW or OUTROW storage is used depends on the amount of data in a LOB column. Assume that you have set the storage switching threshold to 8,192 bytes. If data in a LOB column exceeds 8,192 bytes, OUTROW storage is adopted; otherwise, INROW storage is used.
obclient> CREATE TABLE t(pk int, data text) LOB_INROW_THRESHOLD = 8192;
The preceding DDL statement sets the INROW-to-OUTROW switching threshold to 8,192 bytes by specifying the LOB_INROW_THRESHOLD parameter.
- When data in a LOB column is within 8,192 bytes, the data is stored together with rows in the primary table.
- When data in a LOB column exceeds 8,192 bytes, the data is stored in a LOB auxiliary table.
Note
Decreasing the value of lob_inrow_threshold is an offline DDL operation.
INROW storage outperforms OUTROW storage since the former reduces the number of accesses to storage required to read LOB data, thus improving the efficiency. In scenarios where LOB data needs to be accessed frequently, INROW storage can speed up queries and reduce system overhead.
LOB types
The common LOB types in MySQL mode are sorted in alphabetic order as follows:
- ARRAY: stores data of the ARRAY type. It can store a collection of multiple values.
- RoaringBitmap: stores data of the Roaring bitmap type. It is mainly used for image processing and representation.
- BLOB: stores binary data such as images or files. The maximum length is 65,535 bytes.
- GEOMETRY: stores geospatial data. It can be used for spatial analysis and operations.
- JSON: stores data in the JSON format. It can facilitate the processing of structured data.
- LONGTEXT: stores massive text data. The maximum length is 536,870,910 bytes.
- LONGBLOB: stores massive binary data. The maximum length is 536,870,910 bytes.
- MEDIUMBLOB: stores a medium amount of binary data. The maximum length is 16,777,215 bytes.
- MEDIUMTEXT: stores a medium amount of text data. The maximum length is 16,777,215 bytes.
- TEXT: stores a small amount of text data. The maximum length is 65,535 bytes.