MySQL 5.7 comes with over 20 enhancements to InnoDB. In this, the first of our reports on MySQL 5.7, we will cover some of the highlights such as temp table performance, spatial indexes, and full text search parsers.
Normally changing the data type for column is quite expensive, to the point where the whole table needs to be copied into a new structure. With 5.7, you can now grow varChar columns as long as either of these criteria are met:
- Both the original size and new size is less than 256 characters.
- Both the original size and new size are greater than or equal 256 characters.
Columns can grow in this fashion, but cannot be shrunk without a full table copy. Interestingly enough, one can add a new column and drop an old one in-place, making this restriction somewhat odd.
Temp Tables
InnoDB based temp tables are no longer stored in normal system tables. Instead, a new table called INNODB_TEMP_TABLE_INFO is created the first time someone queries against it. Since examining temp tables is a rare operation, most instances of MySQL will probably never create this table and thus see a performance boost.
Non-compressed temp tables are now stored in their tablespace. By default this tablespace is stored in DATADIR, but can be overridden if you wish to have a dedicated drive for them tables. (This is a standard practice for other database engines such as SQL Server.) Compressed temp tables get one tablespace per table and are stored in TMPDIR.
A new log format has been introduced for temp tables. This format still supports undo operations, which are necessary for rolling back transactions, but eliminates the “redo” capability. In normal tables, the ability to perform a redo operation is necessary for crash recovery. Since temp tables are deleted on startup anyways, redo support is just means unnecessary bookkeeping.
If you have multiple undo logs, then you also gain the ability to truncate them. Multiple logs are required so that as each log is being truncated, the other logs can take over the server load.
Spatial Types
Spatial types are now natively supported by InnoDB using the DATA_GEOMETRY type. Previously they would be stored as binary BLOB data.
Along with this is the ability to create spatial indexes.
Full Text Search
Parsing is an important consideration for any full text engine. While many users can get away with just the built-in parser, some require support for languages or file formats that are not offered “out of the box”. In order to address this, InnoDB tables now supports a plug-in architecture for its full text engine. To prove the concept, these new parsers were created:
A character-based ngram full-text parser that supports Chinese, Japanese, and Korean (CJK), and a word-based MeCab parser plugin that supports Japanese were introduced in MySQL 5.7.6, for use with InnoDB tables.
Once installed, the parser will need to be explicitly associated with the indexes that need to use it.
Indexes
Bulk loading is now used when creating or rebuilding indexes.
This method of index creation is also known as a “sorted index build”. This enhancement, which improves the efficiency of index creation, also applies to full-text indexes. A new global configuration option, innodb_fill_factor, defines the percentage of space on each page that is filled with data during a sorted index build, with the remaining space reserved for future index growth. For more information, see Section 14.13.18, “Bulk Load for CREATE INDEX”.
Partitioning
Partitioning is now natively supported by InnoDB. In the past, partitioning required the use of the ha_partition handler, which requires one handler object per partition instead of one per table.
Partitioned tables are also easier to move using the Transportable Tablespace feature.
Traditionally, this has not been possible for InnoDB tablespaces because all table data was part of the system tablespace. In MySQL 5.6 and higher, the FLUSH TABLES ... FOR EXPORT syntax prepares an InnoDB table for copying to another server; running ALTER TABLE ... DISCARD TABLESPACE and ALTER TABLE ... IMPORT TABLESPACE on the other server brings the copied data file into the other instance.
Fusion-io Non-Volatile Memory
When using the Fusion-io Non-Volatile Memory filesystem, MySQL can switch from using InnoDB’s doublewrite buffer to atomic writes. “The InnoDB doublewrite buffer is automatically disabled for system tablespace files (ibdata files) located on Fusion-io devices that support atomic writes”.