mysql terminologies

data dictionary

Metadata that keeps track of InnoDB-related objects such as tables, indexes, and table columns. This metadata is physically located in the InnoDB system tablespace. For historical reasons, it overlaps to some degree with information stored in the .frm files

data files

The files that physically contain table and index data. The InnoDB system tablespace, which holds the InnoDB data dictionary and is capable of holding data for multiple InnoDB tables, is represented by one or more .ibdata data files. File-per-table tablespaces, which hold data for a single InnoDB table, are represented by a .ibd data file. General tablespaces (introduced in MySQL 5.7.6), which can hold data for multiple InnoDB tables, are also represented by a .ibd data file.

file-per-table

A general name for the setting controlled by the innodb_file_per_table option, which is an important configuration option that affects aspects of InnoDB file storage, availability of features, and I/O characteristics. As of MySQL 5.6.7, innodb_file_per_table is enabled by default.

With the innodb_file_per_table option enabled, you can create a table in its own .ibd file rather than in the shared ibdata files of the system tablespace. When table data is stored in an individual .ibd file, you have more flexibility to choose row formats required for features such as data compression. The TRUNCATE TABLE operation is also faster, and reclaimed space can be used by the operating system rather than remaining reserved for InnoDB.

dirty page

A page in the InnoDB buffer pool that has been updated in memory, where the changes are not yet written (flushed) to the data files. The opposite of a clean page.

flush

To write changes to the database files, that had been buffered in a memory area or a temporary disk storage area. The InnoDB storage structures that are periodically flushed include the redo log, the undo log, and the buffer pool.

    Flushing can happen because a memory area becomes full and the system needs to free some space, because a commit operation means the changes from a transaction can be finalized, or because a slow shutdown operation means that all outstanding work should be finalized. When it is not critical to flush all the buffered data at once, InnoDB can use a technique called fuzzy checkpointing to flush small batches of pages to spread out the I/O overhead.

redo log

A disk-based data structure used during crash recovery, to correct data written by incomplete transactions. During normal operation, it encodes requests to change InnoDB table data, which result from SQL statements or low-level API calls through NoSQL interfaces. Modifications that did not finish updating the data files before an unexpected shutdown are replayed automatically.

The redo log is physically represented as a set of files, typically named ib_logfile0 and ib_logfile1. The data in the redo log is encoded in terms of records affected; this data is collectively referred to as redo. The passage of data through the redo logs is represented by the ever-increasing LSN value. The original 4GB limit on maximum size for the redo log is raised to 512GB in MySQL 5.6.3.

The disk layout of the redo log is influenced by the configuration options innodb_log_file_size, innodb_log_group_home_dir, and (rarely) innodb_log_files_in_group. The performance of redo log operations is also affected by the log buffer, which is controlled by the innodb_log_buffer_size configuration option.

undo

Data that is maintained throughout the life of a transaction, recording all changes so that they can be undone in case of a rollback operation. It is stored in the undo log either within the system tablespace or in separate undo tablespaces.

undo log

A storage area that holds copies of data modified by active transactions. If another transaction needs to see the original data (as part of a consistent read operation), the unmodified data is retrieved from this storage area.

    By default, this area is physically part of the system tablespace. In MySQL 5.6 and higher, you can use the innodb_undo_tablespaces and innodb_undo_directory configuration options to split it into one or more separate tablespace files, the undo tablespaces, optionally stored on another storage device such as an SSD.

The undo log is split into separate portions, the insert undo buffer and the update undo buffer.

innodb_undo_logs = Defines the number of rollback segments used by InnoDB for data-modifying transactions that generate undo records. Each rollback segment can support a maximum of 1024 data-modifying transactions.
This setting is appropriate for tuning performance if you observe mutex contention related to the undo logs. The innodb_undo_logs option replaces innodb_rollback_segments. For the total number of available rollback segments, rather than the number of active ones, see the Innodb_available_undo_logs status variable.

buffer

    A memory or disk area used for temporary storage. Data is buffered in memory so that it can be written to disk efficiently, with a few large I/O operations rather than many small ones. Data is buffered on disk for greater reliability, so that it can be recovered even when a crash or other failure occurs at the worst possible time. The main types of buffers used by InnoDB are the buffer pool, the doublewrite buffer, and the change buffer.

buffer pool

The memory area that holds cached InnoDB data for both tables and indexes. For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache, using a variation of the LRU algorithm. On systems with large memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances.

    Several InnoDB status variables, INFORMATION_SCHEMA tables, and performance_schema tables help to monitor the internal workings of the buffer pool. Starting in MySQL 5.6, you can avoid a lengthy warmup period after restarting the server, particularly for instances with large buffer pools, by saving the buffer pool state at server shutdown and restoring the buffer pool to the same state at server startup

cardinality

The number of different values in a table column. When queries refer to columns that have an associated index, the cardinality of each column influences which access method is most efficient. For example, for a column with a unique constraint, the number of different values is equal to the number of rows in the table. If a table has a million rows but only 10 different values for a particular column, each value occurs (on average) 100,000 times. A query such as SELECT c1 FROM t1 WHERE c1 = 50; thus might return 1 row or a huge number of rows, and the database server might process the query differently depending on the cardinality of c1.

If the values in a column have a very uneven distribution, the cardinality might not be a good way to determine the best query plan. For example, SELECT c1 FROM t1 WHERE c1 = x; might return 1 row when x=50 and a million rows when x=30. In such a case, you might need to use index hints to pass along advice about which lookup method is more efficient for a particular query.

Cardinality can also apply to the number of distinct values present in multiple columns, as in a composite index.