MySQL 9.1 Reference Manual / MySQL Glossary

MySQL Glossary

These terms are commonly used in information about the MySQL database server.

A

.ARM file

Metadata for ARCHIVE tables. Contrast with .ARZ file. Files with this extension are always included in backups produced by the mysqlbackup command of the MySQL Enterprise Backup product.

.ARZ file

Data for ARCHIVE tables. Contrast with .ARM file. Files with this extension are always included in backups produced by the mysqlbackup command of the MySQL Enterprise Backup product.

ACID

An acronym standing for atomicity, consistency, isolation, and durability. These properties are all desirable in a database system, and are all closely tied to the notion of a transaction. The transactional features of InnoDB adhere to the ACID principles.

Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

The database remains in a consistent state at all times — after each commit or rollback, and while transactions are in progress. If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values.

Transactions are protected (isolated) from each other while they are in progress; they cannot interfere with each other or see each other's uncommitted data. This isolation is achieved through the locking mechanism. Experienced users can adjust the isolation level, trading off less protection in favor of increased performance and concurrency, when they can be sure that the transactions really do not interfere with each other.

The results of transactions are durable: once a commit operation succeeds, the changes made by that transaction are safe from power failures, system crashes, race conditions, or other potential dangers that many non-database applications are vulnerable to. Durability typically involves writing to disk storage, with a certain amount of redundancy to protect against power failures or software crashes during write operations. (In InnoDB, the doublewrite buffer assists with durability.)

adaptive flushing

An algorithm for InnoDB tables that smooths out the I/O overhead introduced by checkpoints. Instead of flushing all modified pages from the buffer pool to the data files at once, MySQL periodically flushes small sets of modified pages. The adaptive flushing algorithm extends this process by estimating the optimal rate to perform these periodic flushes, based on the rate of flushing and how fast redo information is generated.

adaptive hash index

An optimization for InnoDB tables that can speed up lookups using = and IN operators, by constructing a hash index in memory. MySQL monitors index searches for InnoDB tables, and if queries could benefit from a hash index, it builds one automatically for index pages that are frequently accessed. In a sense, the adaptive hash index configures MySQL at runtime to take advantage of ample main memory, coming closer to the architecture of main-memory databases. This feature is controlled by the innodb_adaptive_hash_index configuration option. Because this feature benefits some workloads and not others, and the memory used for the hash index is reserved in the buffer pool, typically you should benchmark with this feature both enabled and disabled.

The hash index is always built based on an existing B-tree index on the table. MySQL can build a hash index on a prefix of any length of the key defined for the B-tree, depending on the pattern of searches against the index. A hash index can be partial; the whole B-tree index does not need to be cached in the buffer pool.

ADO.NET

An object-relational mapping (ORM) framework for applications built using .NET technologies such as ASP.NET. Such applications can interface with MySQL through the Connector/NET component.

See Also .NET, ASP.net, Connector/NET, Mono, Visual Studio.

AIO

Acronym for asynchronous I/O. You might see this acronym in InnoDB messages or keywords.

B

B-tree

A tree data structure that is popular for use in database indexes. The structure is kept sorted at all times, enabling fast lookup for exact matches (equals operator) and ranges (for example, greater than, less than, and BETWEEN operators). This type of index is available for most storage engines, such as InnoDB and MyISAM.

Because B-tree nodes can have many children, a B-tree is not the same as a binary tree, which is limited to 2 children per node.

Contrast with hash index, which is only available in the MEMORY storage engine. The MEMORY storage engine can also use B-tree indexes, and you should choose B-tree indexes for MEMORY tables if some queries use range operators.

The use of the term B-tree is intended as a reference to the general class of index design. B-tree structures used by MySQL storage engines may be regarded as variants due to sophistications not present in a classic B-tree design. For related information, refer to the InnoDB Page Structure Fil Header section of the MySQL Internals Manual.

C

.cfg file

A metadata file used with the InnoDB transportable tablespace feature. It is produced by the command FLUSH TABLES ... FOR EXPORT, puts one or more tables in a consistent state that can be copied to another server. The .cfg file is copied along with the corresponding .ibd file, and used to adjust the internal values of the .ibd file, such as the space ID, during the ALTER TABLE ... IMPORT TABLESPACE step.

C

A programming language that combines portability with performance and access to low-level hardware features, making it a popular choice for writing operating systems, drivers, and other kinds of system software. Many complex applications, languages, and reusable modules feature pieces written in C, tied together with high-level components written in other languages. Its core syntax is familiar to C++, Java, and C# developers.

D

data definition language

See DDL.

data directory

The directory under which each MySQL instance keeps the data files for InnoDB and the directories representing individual databases. Controlled by the datadir configuration option.

E

early adopter

A stage similar to beta, when a software product is typically evaluated for performance, functionality, and compatibility in a non-mission-critical setting.

F

failover

The ability to automatically switch to a standby server in the event of a failure. In the MySQL context, failover involves a standby database server. Often supported within J2EE environments by the application server or framework.

See Also J2EE.

Fast Index Creation

A capability first introduced in the InnoDB Plugin, now part of MySQL in 5.5 and higher, that speeds up creation of InnoDB secondary indexes by avoiding the need to completely rewrite the associated table. The speedup applies to dropping secondary indexes also.

Because index maintenance can add performance overhead to many data transfer operations, consider doing operations such as ALTER TABLE ... ENGINE=INNODB or INSERT INTO ... SELECT * FROM ... without any secondary indexes in place, and creating the indexes afterward.

In MySQL 5.6, this feature becomes more general. You can read and write to tables while an index is being created, and many more kinds of ALTER TABLE operations can be performed without copying the table, without blocking DML operations, or both. Thus in MySQL 5.6 and higher, this set of features is referred to as online DDL rather than Fast Index Creation.

For related information, see Section 17.12, “InnoDB and Online DDL”.

See Also DML, index, online DDL, secondary index.

fast shutdown

The default shutdown procedure for InnoDB, based on the configuration setting innodb_fast_shutdown=1. To save time, certain flush operations are skipped. This type of shutdown is safe during normal usage, because the flush operations are performed during the next startup, using the same mechanism as in crash recovery. In cases where the database is being shut down for an upgrade or downgrade, do a slow shutdown instead to ensure that all relevant changes are applied to the data files during the shutdown.

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.

The MySQL Enterprise Backup product is more flexible for tables that are in their own files. For example, tables can be excluded from a backup, but only if they are in separate files. Thus, this setting is suitable for tables that are backed up less frequently or on a different schedule.

fill factor

In an InnoDB index, the proportion of a page that is taken up by index data before the page is split. The unused space when index data is first divided between pages allows for rows to be updated with longer string values without requiring expensive index maintenance operations. If the fill factor is too low, the index consumes more space than needed, causing extra I/O overhead when reading the index. If the fill factor is too high, any update that increases the length of column values can cause extra I/O overhead for index maintenance. See Section 17.6.2.2, “The Physical Structure of an InnoDB Index” for more information.

G

GA

“Generally available”, the stage when a software product leaves beta and is available for sale, official support, and production use.

See Also J2EE.

global transaction

A type of transaction involved in XA operations. It consists of several actions that are transactional in themselves, but that all must either complete successfully as a group, or all be rolled back as a group. In essence, this extends ACID properties “up a level” so that multiple ACID transactions can be executed in concert as components of a global operation that also has ACID properties.

H

hash index

A type of index intended for queries that use equality operators, rather than range operators such as greater-than or BETWEEN. It is available for MEMORY tables. Although hash indexes are the default for MEMORY tables for historic reasons, that storage engine also supports B-tree indexes, which are often a better choice for general-purpose queries.

MySQL includes a variant of this index type, the adaptive hash index, that is constructed automatically for InnoDB tables if needed based on runtime conditions.

See Also adaptive hash index, B-tree, index, InnoDB.

HDD

Acronym for “hard disk drive”. Refers to storage media using spinning platters, usually when comparing and contrasting with SSD. Its performance characteristics can influence the throughput of a disk-based workload.

I

.ibz file

When the MySQL Enterprise Backup product performs a compressed backup, it transforms each tablespace file that is created using the file-per-table setting from a .ibd extension to a .ibz extension.

The compression applied during backup is distinct from the compressed row format that keeps table data compressed during normal operation. A compressed backup operation skips the compression step for a tablespace that is already in compressed row format, as compressing a second time would slow down the backup but produce little or no space savings.

I/O-bound

See disk-bound.

ib-file set

The set of files managed by InnoDB within a MySQL database: the system tablespace, file-per-table tablespace files, and redo log files. Depending on MySQL version and InnoDB configuration, may also include general tablespace, temporary tablespace, and undo tablespace files. This term is sometimes used in detailed discussions of InnoDB file structures and formats to refer to the set of files managed by InnoDB within a MySQL database.

ibbackup_logfile

A supplemental backup file created by the MySQL Enterprise Backup product during a hot backup operation. It contains information about any data changes that occurred while the backup was running. The initial backup files, including ibbackup_logfile, are known as a raw backup, because the changes that occurred during the backup operation are not yet incorporated. After you perform the apply step to the raw backup files, the resulting files do include those final data changes, and are known as a prepared backup. At this stage, the ibbackup_logfile file is no longer necessary.

ibdata file

A set of files with names such as ibdata1, ibdata2, and so on, that make up the InnoDB system tablespace. For information about the structures and data that reside in the system tablespace ibdata files, see Section 17.6.3.1, “The System Tablespace”.

Growth of the ibdata files is influenced by the innodb_autoextend_increment configuration option.

ibtmp file

The InnoDB temporary tablespace data file for non-compressed InnoDB temporary tables and related objects. The configuration file option, innodb_temp_data_file_path, allows users to define a relative path for the temporary tablespace data file. If innodb_temp_data_file_path is not specified, the default behavior is to create a single auto-extending 12MB data file named ibtmp1 in the data directory, alongside ibdata1.

J

J2EE

Java Platform, Enterprise Edition: Oracle's enterprise Java platform. It consists of an API and a runtime environment for enterprise-class Java applications. For full details, see http://www.oracle.com/technetwork/java/javaee/overview/index.html. With MySQL applications, you typically use Connector/J for database access, and an application server such as Tomcat or JBoss to handle the middle-tier work, and optionally a framework such as Spring. Database-related features often offered within a J2EE stack include a connection pool and failover support.

See Also connection pool, failover, Java, JBoss, Spring, Tomcat.

Java

A programming language combining high performance, rich built-in features and data types, object-oriented mechanisms, extensive standard library, and wide range of reusable third-party modules. Enterprise development is supported by many frameworks, application servers, and other technologies. Much of its syntax is familiar to C and C++ developers. To write Java applications with MySQL, you use the JDBC driver known as Connector/J.

See Also J2EE.

JDBC

Abbreviation for “Java Database Connectivity”, an API for database access from Java applications. Java developers writing MySQL applications use the Connector/J component as their JDBC driver.

K

keystore

L

latch

A lightweight structure used by InnoDB to implement a lock for its own internal memory structures, typically held for a brief time measured in milliseconds or microseconds. A general term that includes both mutexes (for exclusive access) and rw-locks (for shared access). Certain latches are the focus of InnoDB performance tuning. Statistics about latch use and contention are available through the Performance Schema interface.

See Also lock, locking, mutex, Performance Schema, rw-lock.

libmysql

Informal name for the libmysqlclient library.

See Also J2EE.

localhost

M

.MRG file

A file containing references to other tables, used by the MERGE storage engine. Files with this extension are always included in backups produced by the mysqlbackup command of the MySQL Enterprise Backup product.

.MYD file

A file that MySQL uses to store data for a MyISAM table.

.MYI file

A file that MySQL uses to store indexes for a MyISAM table.

master

See source.

master thread

An InnoDB thread that performs various tasks in the background. Most of these tasks are I/O related, such as writing changes from the change buffer to the appropriate secondary indexes.

To improve concurrency, sometimes actions are moved from the master thread to separate background threads. For example, in MySQL 5.6 and higher, dirty pages are flushed from the buffer pool by the page cleaner thread rather than the master thread.

MDL

Acronym for “metadata lock”.

N

.NET

native C API

Synonym for libmysqlclient.

O

.OPT file

A file containing database configuration information. Files with this extension are included in backups produced by the mysqlbackup command of the MySQL Enterprise Backup product.

ODBC

Acronym for Open Database Connectivity, an industry-standard API. Typically used with Windows-based servers, or applications that require ODBC to communicate with MySQL. The MySQL ODBC driver is called Connector/ODBC.

P

.par file

A file containing partition definitions. Files with this extension are included in backups produced by the mysqlbackup command of the MySQL Enterprise Backup product.

With the introduction of native partitioning support for InnoDB tables in MySQL 5.7.6, .par files are no longer created for partitioned InnoDB tables. Partitioned MyISAM tables continue to use .par files in MySQL 5.7. In MySQL 8.0, partitioning support is only provided by the InnoDB storage engine. As such, .par files are no longer used as of MySQL 8.0.

page

A unit representing how much data InnoDB transfers at any one time between disk (the data files) and memory (the buffer pool). A page can contain one or more rows, depending on how much data is in each row. If a row does not fit entirely into a single page, InnoDB sets up additional pointer-style data structures so that the information about the row can be stored in one page.

One way to fit more data in each page is to use compressed row format. For tables that use BLOBs or large text fields, compact row format allows those large columns to be stored separately from the rest of the row, reducing I/O overhead and memory usage for queries that do not reference those columns.

When InnoDB reads or writes sets of pages as a batch to increase I/O throughput, it reads or writes an extent at a time.

All the InnoDB disk data structures within a MySQL instance share the same page size.

See Also buffer pool, data files, extent, page size, row.

page cleaner

An InnoDB background thread that flushes dirty pages from the buffer pool. Prior to MySQL 5.6, this activity was performed by the master thread. The number of page cleaner threads is controlled by the innodb_page_cleaners configuration option, introduced in MySQL 5.7.4.

page size

For releases up to and including MySQL 5.5, the size of each InnoDB page is fixed at 16 kilobytes. This value represents a balance: large enough to hold the data for most rows, yet small enough to minimize the performance overhead of transferring unneeded data to memory. Other values are not tested or supported.

Starting in MySQL 5.6, the page size for an InnoDB instance can be either 4KB, 8KB, or 16KB, controlled by the innodb_page_size configuration option. As of MySQL 5.7.6, InnoDB also supports 32KB and 64KB page sizes. For 32KB and 64KB page sizes, ROW_FORMAT=COMPRESSED is not supported and the maximum record size is 16KB.

Page size is set when creating the MySQL instance, and it remains constant afterward. The same page size applies to all InnoDB tablespaces, including the system tablespace, file-per-table tablespaces, and general tablespaces.

Smaller page sizes can help performance with storage devices that use small block sizes, particularly for SSD devices in disk-bound workloads, such as for OLTP applications. As individual rows are updated, less data is copied into memory, written to disk, reorganized, locked, and so on.

parent table

The table in a foreign key relationship that holds the initial column values pointed to from the child table. The consequences of deleting, or updating rows in the parent table depend on the ON UPDATE and ON DELETE clauses in the foreign key definition. Rows with corresponding values in the child table could be automatically deleted or updated in turn, or those columns could be set to NULL, or the operation could be prevented.

Q

query

In SQL, an operation that reads information from one or more tables. Depending on the organization of data and the parameters of the query, the lookup might be optimized by consulting an index. If multiple tables are involved, the query is known as a join.

For historical reasons, sometimes discussions of internal processing for statements use “query” in a broader sense, including other types of MySQL statements such as DDL and DML statements.

R

R-tree

A tree data structure used for spatial indexing of multi-dimensional data such as geographical coordinates, rectangles or polygons.

	`S`	`SX`	`X`
`S`	Compatible	Compatible	Conflict
`SX`	Compatible	Conflict	Conflict
`X`	Conflict	Conflict	Conflict

S

savepoint

Savepoints help to implement nested transactions. They can be used to provide scope to operations on tables that are part of a larger transaction. For example, scheduling a trip in a reservation system might involve booking several different flights; if a desired flight is unavailable, you might roll back the changes involved in booking that one leg, without rolling back the earlier flights that were successfully booked.

See Also J2EE.

SQL

The Structured Query Language that is standard for performing database operations. Often divided into the categories DDL, DML, and queries. MySQL includes some additional statement categories such as replication. See Chapter 11, Language Structure for the building blocks of SQL syntax, Chapter 13, Data Types for the data types to use for MySQL table columns, Chapter 15, SQL Statements for details about SQL statements and their associated categories, and Chapter 14, Functions and Operators for standard and MySQL-specific functions to use in queries.

T

table lock

A lock that prevents any other transaction from accessing a table. InnoDB makes considerable effort to make such locks unnecessary, by using techniques such as online DDL, row locks and consistent reads for processing DML statements and queries. You can create such a lock through SQL using the LOCK TABLE statement; one of the steps in migrating from other database systems or MySQL storage engines is to remove such statements wherever practical.

table scan

See full table scan.

table statistics

See statistics.

table type

Obsolete synonym for storage engine. We refer to InnoDB tables, MyISAM tables, and so on.

See Also J2EE.

torn page

An error condition that can occur due to a combination of I/O device configuration and hardware failure. If data is written out in chunks smaller than the InnoDB page size (by default, 16KB), a hardware failure while writing could result in only part of a page being stored to disk. The InnoDB doublewrite buffer guards against this possibility.

U

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 undo logs either within the system tablespace (in MySQL 5.7 or earlier) or in separate undo tablespaces. As of MySQL 8.0, undo logs reside in undo tablespaces by default.

undo buffer

See undo log.

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.

In MySQL 5.6 and MySQL 5.7, you can use the innodb_undo_tablespaces variable have undo logs reside in undo tablespaces, which can be placed on another storage device such as an SSD. In MySQL 8.0, undo logs reside in two default undo tablespaces that are created when MySQL is initialized, and additional undo tablespaces can be created using CREATE UNDO TABLESPACE syntax.

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

undo log segment

A collection of undo logs. Undo log segments exists within rollback segments. An undo log segment might contain undo logs from multiple transactions. An undo log segment can only be used by one transaction at a time but can be reused after it is released at transaction commit or rollback. May also be referred to as an “undo segment”.

undo tablespace

An undo tablespace contains undo logs. Undo logs exist within undo log segments, which are contained within rollback segments. Rollback segments have traditionally resided in the system tablespace. As of MySQL 5.6, rollback segments can reside in undo tablespaces. In MySQL 5.6 and MySQL 5.7, the number of undo tablespaces is controlled by the innodb_undo_tablespaces configuration option. In MySQL 8.0, two default undo tablespaces are created when the MySQL instance is initialized, and additional undo tablespaces can be created using CREATE UNDO TABLESPACE syntax.

For more information, see Section 17.6.3.4, “Undo Tablespaces”.

Unicode

A system for supporting national characters, character sets, code pages, and other internationalization aspects in a flexible and standardized way.

Unicode support is an important aspect of the ODBC standard. Connector/ODBC 5.1 is a Unicode driver, as opposed to Connector/ODBC 3.51, which is an ANSI driver.

V

variable-length type

A data type of variable length. VARCHAR, VARBINARY, and BLOB and TEXT types are variable-length types.

InnoDB treats fixed-length fields greater than or equal to 768 bytes in length as variable-length fields, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

victim

The transaction that is automatically chosen to be rolled back when a deadlock is detected. InnoDB rolls back the transaction that has updated the fewest rows.

Deadlock detection can be disabled using the innodb_deadlock_detect configuration option.

view

A stored query that when invoked produces a result set. A view acts as a virtual table.

Visual Studio

For supported versions of Visual Studio, see the following references:

Connector/NET: Connector/NET Versions
Connector/C++ 8.0: Platform Support and Prerequisites

W

wait

When an operation, such as acquiring a lock, mutex, or latch, cannot be completed immediately, InnoDB pauses and tries again. The mechanism for pausing is elaborate enough that this operation has its own name, the wait. Individual threads are paused using a combination of internal InnoDB scheduling, operating system wait() calls, and short-duration spin loops.

On systems with heavy load and many transactions, you might use the output from the SHOW INNODB STATUS command or Performance Schema to determine whether threads are spending too much time waiting, and if so, how you can improve concurrency.

warm backup

A backup taken while the database is running, but that restricts some database operations during the backup process. For example, tables might become read-only. For busy applications and websites, you might prefer a hot backup.

See Also backup, cold backup, hot backup.

warm up

To run a system under a typical workload for some time after startup, so that the buffer pool and other memory regions are filled as they would be under normal conditions. This process happens naturally over time when a MySQL server is restarted or subjected to a new workload.

Typically, you run a workload for some time to warm up the buffer pool before running performance tests, to ensure consistent results across multiple runs; otherwise, performance might be artificially low during the first run.

In MySQL 5.6, you can speed up the warmup process by enabling the innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup configuration options, to bring the contents of the buffer pool back into memory after a restart. These options are enabled by default in MySQL 5.7. See Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.

Y

young

A characteristic of a page in the InnoDB buffer pool meaning that it has been accessed recently, and so is moved within the buffer pool data structure, so that it is not flushed too soon by the LRU algorithm. This term is used in some INFORMATION_SCHEMA column names of tables related to the buffer pool.

See Also buffer pool, flush, INFORMATION_SCHEMA, LRU, page.

PREV HOME UP

PDF (US Ltr) - 40.3Mb
PDF (A4) - 40.4Mb
Man Pages (TGZ) - 259.3Kb
Man Pages (Zip) - 366.4Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb