mirror of
https://github.com/MariaDB/server.git
synced 2026-10-23 18:49:29 +03:00
3331b76240
Remove the HA_NO_BLOBS restriction from the HEAP engine, allowing the optimizer to keep temporary tables with BLOB/TEXT columns in memory when they fit within max_heap_table_size / tmp_memory_table_size limits. Additionally, advertise HA_CAN_GEOMETRY so explicit CREATE TABLE ... ENGINE=MEMORY with GEOMETRY columns works. Unlike other HEAP blob implementations (e.g. Percona), this patch provides full HASH index support on blob columns, enabling efficient lookups, GROUP BY, and DISTINCT operations directly in HEAP without falling back to disk. Architecture ------------ BLOB data is stored using continuation records -- additional fixed-size records allocated from the same HP_BLOCK that holds regular rows. This reuses existing allocation, free list, and size accounting with minimal structural change, and avoids per-blob my_malloc() calls. The existing single-byte visibility flag is extended into a flags byte with bits for HP_ROW_HAS_CONT, HP_ROW_IS_CONT, HP_ROW_CONT_ZEROCOPY, HP_ROW_SINGLE_REC, and HP_ROW_MULTIPLE_REC. Continuation records are grouped into variable-length runs -- contiguous sequences within a leaf block. Only the first record of each run carries a 10-byte header (next_cont pointer + run_rec_count); inner records are pure payload. Three storage formats, detected by flag bits via inline predicates: Case A (HP_ROW_SINGLE_REC): single record, no header, data at offset 0. Zero-copy read. Case B (HP_ROW_CONT_ZEROCOPY): single run, multiple records. Header in rec 0, data contiguous in rec 1..N-1. Zero-copy read via chain + recbuffer. Case C (HP_ROW_MULTIPLE_REC): one or more runs linked via next_cont. Reassembly into blob_buff required. Run allocation uses a two-phase strategy: (1) peek-then-unlink walk of the free list detecting contiguous groups, (2) tail allocation from HP_BLOCK for remaining data. A Step 3 scavenge fallback walks the entire free list when tail allocation fails. HP_SHARE::total_records tracks all physical records (primary + continuation), while HP_SHARE::records remains the logical count used by hash bucket mapping. Reassembly buffer (HP_INFO::blob_buff) follows the same pattern as InnoDB's blob_heap -- allocated once, grown via my_realloc, freed on heap_reset()/close. Zero-copy cases (A/B) return pointers directly into HP_BLOCK with no copy. Full HASH index key handling for BLOB columns: hp_rec_hashnr(), hp_rec_key_cmp(), hp_key_cmp(), hp_make_key(), hp_hashnr() are extended for HA_BLOB_PART segments. Hash pre-check optimization skips expensive blob materialization when hashes differ. PAD SPACE collation semantics are preserved for blob key comparisons. Field_blob_key (Monty) produces HEAP-native key format (4-byte length + 8-byte data pointer) directly, eliminating key buffer translation between the SQL layer and HEAP engine. SQL layer changes ----------------- pick_engine() (new, extracted from choose_engine()): replaced the blob_fields check with a reclength > HA_MAX_REC_LENGTH guard. choose_engine() calls pick_engine() with the real reclength. pick_engine() is also called early in finalize() with reclength=0 to predict whether the engine will be HEAP, enabling blob-aware GROUP BY key setup that avoids unnecessary m_using_unique_constraint. finalize(): HEAP+blob uses fixed-width rows; GROUP BY key setup sets key_part_flag from field, uses item max_length for blob key sizing. store_length initialized for all GROUP BY key parts. key_type uses field->binary() to determine FIELDFLAG_BINARY vs text collation. DISTINCT key setup skips null-bits helper for HEAP. remove_duplicates(): blob check moved before HEAP check to fall through to remove_dup_with_compare(). Aggregator_distinct::add(): overflow-to-disk conversion via create_internal_tmp_table_from_heap() for non-dup write errors. Expression cache disabled for HEAP+blob (key format incompatibility). FULLTEXT early detection in mysql_derived_prepare(): forces disk engine via TMP_TABLE_FORCE_MYISAM when outer query uses MATCH. Deferred blob chain free (MDEV-39732): heap_delete() saves chain pointers to pending_blob_chains, flushed on next mutation or heap_reset()/close. Prevents dangling zero-copy pointers during binlog_log_row(). REPLACE safety (MDEV-39825): HP_SHARE::write_can_replace flag forces copy mode in hp_read_blobs(), preventing blob data corruption from freed-then-reused continuation records during REPLACE. Geometry GROUP_CONCAT fix (MDEV-39761): downgrade Field_geom to Field_blob for GROUP_CONCAT temp tables in both expression creation paths. Type_handler_geometry::type_handler_for_tmp_table() added. Geometry GROUP BY key fix (MDEV-39871): detect when new_key_field() produced non-blob Field_varstring for a blob column, replace with Field_blob_key. Performance ----------- Non-blob tables: zero regression. Every blob-specific code path is guarded by if (share->blob_count). No new allocations, no format changes, no hash function changes for non-blob keys. Blob tables: eliminates file creation/deletion overhead and page cache management. For single-run blobs (common case), the read path is entirely zero-copy. Limitations ----------- 1. No BTREE indexes on blob columns (HASH only) 2. No partial-key prefix indexing for blobs 3. 2x memory for Case C reads only (A/B are 1x) 4. No blob compression 5. 65,535 records per run (uint16 cap, auto-split) 6. max_heap_table_size applies to continuation records 7. Expression cache disabled for HEAP+blob 8. FULLTEXT forces disk engine Linked bugs fixed: - MDEV-39703: mroonga fulltext test ordering - MDEV-39723: ER_DUP_ENTRY on GROUP BY with blob column - MDEV-39724: crash in hp_is_single_rec with GROUP BY - MDEV-39732: slave crash in hp_free_run_chain on blob replication - MDEV-39761: Field_geom::store() assertion in GROUP_CONCAT - MDEV-39782: RBR ER_KEY_NOT_FOUND on HEAP blob UPDATE - MDEV-39825: blob data corruption on REPLACE into HEAP table - MDEV-39871: crash in my_hash_sort_bin on GROUP BY with geometry Reviewed by: Michael Widenius <monty@mariadb.org> Monty reviewed the entire patch. Areas where he suggested changes or contributed code: - Field_blob_key class (HEAP-native blob key format, 4-byte length + data pointer) - Duplicate key fix on HEAP-to-Aria conversion - hp_blob_key_length() uint32 fix - hp_rec_hashnr_stored removal - type_handler_for_tmp_table() param cleanup - Type_handler_geometry::type_handler_for_tmp_table() virtual - blob pointer bzero() - find_unique_row() double-materialization fix - Tail reclaim review - Batch tail allocation review - hp_update.c cleanup - Field_blob_compressed temp table fix - row_pack_length() dedup - pack_length_no_ptr() removal - Race condition fix in HEAP - MDEV-39703 mroonga test fix - MDEV-39825 write_can_replace optimization - is_text_key_segment removal (field->binary() simplification) - Documentation (Docs/internal-temporary-tables.txt) Contribution by: Alexander Barkov <bar@mariadb.com> Type_handler::make_and_init_table_field_ex() -- refactored temp table field creation from inline code in sql_select.cc into type handler virtual methods (sql_type.cc, sql_type_geom.cc), enabling clean per-type-handler field creation for HEAP blob promotion.
61 lines
3.0 KiB
Plaintext
61 lines
3.0 KiB
Plaintext
MariaDB is automatically creating internal temporary tables to store
|
|
temporary results for a query.
|
|
|
|
Examples:
|
|
- Distinct handling (writing into a temporary table with a unique key)
|
|
- Store the whole result for a final order by.
|
|
- Store the result for a group by. Two ways to do this:
|
|
a) Update a row value for each row combination based on the group-by key
|
|
b) Sort the result in group-by order, write a row to temporary table when group-by
|
|
order changes. Sort the resulting temporary tables.
|
|
This solution is used when all group-by fields is from the first used non const
|
|
table and we there is an ORDER BY to sort the final result
|
|
- Materialized tables for things like (select DISTINCT ...)
|
|
- Derived tables "SELECT ... FROM (SELECT...)"
|
|
- Results for UNION, EXCEPT etc
|
|
- Store values and results for "t1.a in (select ... )"
|
|
- Temporary storage for result from the SHOW command or when accessing
|
|
information_schema.
|
|
|
|
The internal temporary tables can be of type memory/heap or Aria.
|
|
One can recompile MariaDB to use MyISAM instead of Aria for temporary
|
|
tables, but this is mainly kept as a legacy option and not recommended.
|
|
|
|
The internal temporary tables are normally first created as memory
|
|
tables. If the table overflows, in case of MY_MIN(max_heap_table_size,
|
|
tmp_memory_table_size), the table is converted to an Aria/MyISAM
|
|
table.
|
|
|
|
The code for creating internal temporary tables are in sql/sql_select.cc.
|
|
- create_tmp_table() is used for create all internal temporary tables.
|
|
- instantiate_tmp_table() which will create the aria or memory table
|
|
- If Aria was chosen as a base for the temporary table
|
|
create_internal_tmp_table() is called
|
|
- Heap tables are created by calling open_tmp_table() which calls
|
|
table->file->ha_open()
|
|
- create_internal_tmp_table_from_heap() or convert_heap_to_aria_update()
|
|
is used to change a heap table to an Aria table in case of overflow.
|
|
|
|
The following are the usage restrictions for internal temporary tables that
|
|
an engine can rely on to provide optimized performance.
|
|
|
|
- There will only be one user for the table
|
|
- No need for any locking to protect internal structures. No external_lock() calls.
|
|
- Table is created and dropped during the same statement.
|
|
- Table has either 0 keys, one unique key or an unique constraint (a set of fields
|
|
has to be unique)
|
|
- The only read/write patterns are:
|
|
- Table scan, possible followed by a read rows based on position (for order by
|
|
on temporary table).
|
|
- Table scan with duplicate row removal or delete of rows that are failing the
|
|
HAVING clause.
|
|
- In case of duplicate row removal, the table will be rescanned for each
|
|
row starting from one row after where the previous inner scan started
|
|
- Read by unique key
|
|
- Inserts
|
|
In case of duplicate key, one of the following options are done:
|
|
- Read conflicting row and update it. Unique key is not updated.
|
|
This happens in case of group by to update summary fields.
|
|
- Ignore to-be-inserted row (for distinct)
|
|
- Truncate followed by new inserts
|