Storage layout optimization of 8.5.1 optimizing Storage Layout for InnoDB tables

Storage layout optimization of 8.5.1 optimizing Storage Layout for InnoDB tables

Once your data reaches a steady size, or a growing table a few Baisi trillion increases,

Consider using the Optimize table statement to rearrange the tables to compress any wasted space.

The reorganization table requires less disk I/O to perform a full table scan.

This is a clear technique that can improve performance when other technologies such as improving index usage or tuning application code are impractical.

Optimizing table Copy table data and rebuilding indexes, the benefit is to improve the data inside the index, reduce the fragmentation in the table space.

The benefits depend on the data for each table. You may find that there are some notable gains, or that the benefits decrease over time to know your next tuning table.

This operation may be slow if the table is large or if the index is rebuilt without putting the buffer pool.

Running smoothly for the first time after adding a lot of data is slower than running later

In InnoDB, there is a long primary KEY (whether it's a column with a long value, or a few columns that form a long, consistent value) that wastes a lot of disk space.

The primary key value of a record is copied to all the 2nd index records, pointing to the same row.

Create a auto_increment column as the primary key if your primary key is long.

Use the varchar data type instead of char to store a variable length string or for columns that have many null values.

A char (n) column always occupies N strings to store data, although the string is short or its value is null.

Small tables fit in the buffer pool and reduce disk I/O.

When used in compact record format (innodb format), variable-length character sets such as UTF8 or Sjis, CHAR (n) columns occupy space, but still at least N bytes.

Consider using the compressed record format for large tables, or for large amounts of duplicate files or digital data.

Less disk I/O is needed to put data into the buffer pool, or to perform a full table scan.