Metadata forARCHIVE tables. Contrast with.ARZ file. Files with this extension are always included in backups produced by themysqlbackup command of theMySQL Enterprise Backup product.

See Also.ARZ file,MySQL Enterprise Backup,mysqlbackup command.

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

See Also.ARM file,MySQL Enterprise Backup,mysqlbackup command.

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 atransaction. The transactional features ofInnoDB adhere to the ACID principles.

Transactions areatomic units of work that can becommitted orrolled 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 thelocking mechanism. Experienced users can adjust theisolation level, trading off less protection in favor of increased performance andconcurrency, 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. (InInnoDB, thedoublewrite buffer assists with durability.)

See Alsoatomic,commit,concurrency,doublewrite buffer,isolation level,locking,rollback,transaction.

An algorithm forInnoDB tables that smooths out the I/O overhead introduced bycheckpoints. Instead offlushing all modifiedpages from thebuffer pool to thedata 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 fastredo information is generated.

See Alsobuffer pool,checkpoint,data files,flush,InnoDB,page,redo log.

An optimization forInnoDB tables that can speed up lookups using= andIN operators, by constructing ahash index in memory. MySQL monitors index searches forInnoDB tables, and if queries could benefit from a hash index, it builds one automatically for indexpages 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 theinnodb_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 thebuffer pool, typically you should benchmark with this feature both enabled and disabled.

The hash index is always built based on an existingB-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.

See AlsoB-tree,buffer pool,hash index,page,secondary index.

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

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

Acronym forasynchronous I/O. You might see this acronym inInnoDB messages or keywords.

See Alsoasynchronous I/O.

InODBC, an alternative method of supporting character sets and other internationalization aspects. Contrast withUnicode.Connector/ODBC 3.51 is an ANSI driver, while Connector/ODBC 5.1 is a Unicode driver.

See AlsoConnector/ODBC,ODBC,Unicode.

APIs provide low-level access to the MySQL protocol and MySQL resources fromclient programs. Contrast with the higher-level access provided by aConnector.

See AlsoC API,client,connector,native C API,Perl API,PHP API,Python API,Ruby API.

A set of functions or procedures. An API provides a stable set of names and types for functions, procedures, parameters, and return values.

When a backup produced by theMySQL Enterprise Backup product does not include the most recent changes that occurred while the backup was underway, the process of updating the backup files to include those changes is known as theapply step. It is specified by theapply-log option of themysqlbackup command.

Before the changes are applied, we refer to the files as araw backup. After the changes are applied, we refer to the files as aprepared backup. The changes are recorded in theibbackup_logfile file; once the apply step is finished, this file is no longer necessary.

See Alsohot backup,ibbackup_logfile,MySQL Enterprise Backup,prepared backup,raw backup.

A Kerberos authentication server. AS can also refer to the authentication service provided by an authentication server.

See Alsoauthentication server.

A framework for developing web-based applications using.NET technologies and languages. Such applications can interface with MySQL through theConnector/NET component.

Another technology for writing server-side web pages with MySQL isPHP.

See Also.NET,ADO.NET,Connector/NET,Mono,PHP,Visual Studio.

A library of compiled code in a.NET system, accessed throughConnector/NET. Stored in theGAC to allow versioning without naming conflicts.

See Also.NET,GAC.

A type of I/O operation that allows other processing to proceed before the I/O is completed. Also known asnonblocking I/O and abbreviated asAIO.InnoDB uses this type of I/O for certain operations that can run in parallel without affecting the reliability of the database, such as reading pages into thebuffer pool that have not actually been requested, but might be needed soon.

Historically,InnoDB used asynchronous I/O on Windows systems only. Starting with the InnoDB Plugin 1.1 and MySQL 5.5,InnoDBuses asynchronous I/O on Linux systems. This change introduces a dependency onlibaio. Asynchronous I/O on Linux systems is configured using theinnodb_use_native_aio option, which is enabled by default. On other Unix-like systems, InnoDB uses synchronous I/O only.

See Alsobuffer pool,nonblocking I/O.

In the SQL context,transactions are units of work that either succeed entirely (whencommitted) or have no effect at all (whenrolled back). The indivisible ("atomic") property of transactions is the“A” in the acronymACID.

See AlsoACID,commit,rollback,transaction.

An atomicDDL statement is one that combines thedata dictionary updates,storage engine operations, andbinary log writes associated with a DDL operation into a single, atomic transaction. The transaction is either fully committed or rolled back, even if the server halts during the operation. Atomic DDL support was added in MySQL 8.0. For more information, seeSection 15.1.1, “Atomic Data Definition Statement Support”.

See Alsobinary log,data dictionary,DDL,storage engine.

Special instructions provided by the CPU, to ensure that critical low-level operations cannot be interrupted.

In Kerberos, a service that provides the initial ticket needed to obtain a ticket-granting ticket (TGT) that is needed to obtain other tickets from the ticket-granting server (TGS). The authentication server (AS) combined with a TGS make up a key distribution center (KDC).

See Alsokey distribution center,ticket-granting server.

A property of a table column (specified by theAUTO_INCREMENT keyword) that automatically adds an ascending sequence of values in the column.

It saves work for the developer, not to have to produce new unique values when inserting new rows. It provides useful information for the query optimizer, because the column is known to be not null and with unique values. The values from such a column can be used as lookup keys in various contexts, and because they are auto-generated there is no reason to ever change them; for this reason, primary key columns are often specified as auto-incrementing.

Auto-increment columns can be problematic with statement-based replication, because replaying the statements on a replica might not produce the same set of column values as on the source, due to timing issues. When you have an auto-incrementing primary key, you can use statement-based replication only with the settinginnodb_autoinc_lock_mode=1. If you haveinnodb_autoinc_lock_mode=2, which allows higher concurrency for insert operations, userow-based replication rather thanstatement-based replication. The settinginnodb_autoinc_lock_mode=0 should not be used except for compatibility purposes.

Consecutive lock mode (innodb_autoinc_lock_mode=1) is the default setting prior to MySQL 8.0.3. As of MySQL 8.0.3, interleaved lock mode (innodb_autoinc_lock_mode=2) is the default, which reflects the change from statement-based to row-based replication as the default replication type.

See Alsoauto-increment locking,innodb_autoinc_lock_mode,primary key,row-based replication,statement-based replication.

The convenience of anauto-increment primary key involves some tradeoff with concurrency. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.InnoDB includes optimizations and theinnodb_autoinc_lock_mode option so that you can configure and optimal balance between predictable sequences of auto-increment values and maximumconcurrency for insert operations.

See Alsoauto-increment,concurrency,innodb_autoinc_lock_mode.

A setting that causes acommit operation after eachSQL statement. This mode is not recommended for working withInnoDB tables withtransactions that span several statements. It can help performance forread-only transactions onInnoDB tables, where it minimizes overhead fromlocking and generation ofundo data, especially in MySQL 5.6.4 and up. It is also appropriate for working withMyISAM tables, where transactions are not applicable.

See Alsocommit,locking,read-only transaction,SQL,transaction,undo.

The ability to cope with, and if necessary recover from, failures on the host, including failures of MySQL, the operating system, or the hardware and maintenance activity that may otherwise cause downtime. Often paired withscalability as critical aspects of a large-scale deployment.

See Alsoscalability.

A licensed product that performshot backups of MySQL databases. It offers the most efficiency and flexibility when backing upInnoDB tables, but can also back upMyISAM and other kinds of tables.

See Alsohot backup,InnoDB.

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, andBETWEEN operators). This type of index is available for most storage engines, such asInnoDB andMyISAM.

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 withhash index, which is only available in theMEMORY storage engine. TheMEMORY storage engine can also use B-tree indexes, and you should choose B-tree indexes forMEMORY 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 theInnoDB Page StructureFil Header section of theMySQL Internals Manual.

See Alsohash index.

Identifiers within MySQL SQL statements must be quoted using the backtick character (`) if they contain special characters or reserved words. For example, to refer to a table namedFOO#BAR or a column namedSELECT, you would specify the identifiers as`FOO#BAR` and`SELECT`. Since the backticks provide an extra level of safety, they are used extensively in program-generated SQL statements, where the identifier names might not be known in advance.

Many other database systems use double quotation marks (") around such special names. For portability, you can enableANSI_QUOTES mode in MySQL and use double quotation marks instead of backticks to qualify identifier names.

See AlsoSQL.

The process of copying some or all table data and metadata from a MySQL instance, for safekeeping. Can also refer to the set of copied files. This is a crucial task for DBAs. The reverse of this process is therestore operation.

With MySQL,physical backups are performed by theMySQL Enterprise Backup product, andlogical backups are performed by themysqldump command. These techniques have different characteristics in terms of size and representation of the backup data, and speed (especially speed of the restore operation).

Backups are further classified ashot,warm, orcold depending on how much they interfere with normal database operation. (Hot backups have the least interference, cold backups the most.)

See Alsocold backup,hot backup,logical backup,MySQL Enterprise Backup,mysqldump,physical backup,warm backup.

A non-generated table column upon which a stored generated column or virtual generated column is based. In other words, a base column is a non-generated table column that is part of a generated column definition.

See Alsogenerated column,stored generated column,virtual generated column.

An early stage in the life of a software product, when it is available only for evaluation, typically without a definite release number or a number less than 1.InnoDB does not use the beta designation, preferring anearly adopter phase that can extend over several point releases, leading to aGA release.

See Alsoearly adopter,GA.

A file containing a record of all statements or row changes that attempt to change table data. The contents of the binary log can be replayed to bring replicas up to date in areplication scenario, or to bring a database up to date after restoring table data from a backup. The binary logging feature can be turned on and off, although Oracle recommends always enabling it if you use replication or perform backups.

You can examine the contents of the binary log, or replay it during replication or recovery, by using themysqlbinlog command. For full information about the binary log, seeSection 7.4.4, “The Binary Log”. For MySQL configuration options related to the binary log, seeSection 19.1.6.4, “Binary Logging Options and Variables”.

For theMySQL Enterprise Backup product, the file name of the binary log and the current position within the file are important details. To record this information for the source when taking a backup in a replication context, you can specify the--slave-info option.

Prior to MySQL 5.0, a similar capability was available, known as the update log. In MySQL 5.0 and higher, the binary log replaces the update log.

See Alsobinlog,MySQL Enterprise Backup,replication.

An informal name for thebinary log file. For example, you might see this abbreviation used in e-mail messages or forum discussions.

See Alsobinary log.

A special mode offull-text search enabled by theWITH QUERY EXPANSION clause. It performs the search twice, where the search phrase for the second search is the original search phrase concatenated with the few most highly relevant documents from the first search. This technique is mainly applicable for short search phrases, perhaps only a single word. It can uncover relevant matches where the precise search term does not occur in the document.

See Alsofull-text search.

An SQL data type (TINYBLOB,BLOB,MEDIUMBLOB, andLONGBLOB) for objects containing any kind of binary data, of arbitrary size. Used for storing documents, images, sound files, and other kinds of information that cannot easily be decomposed to rows and columns within a MySQL table. The techniques for handling BLOBs within a MySQL application vary with eachConnector andAPI. MySQLConnector/ODBC definesBLOB values asLONGVARBINARY. For large, free-form collections of character data, the industry term isCLOB, represented by the MySQLTEXT data types.

See AlsoAPI,CLOB,connector,Connector/ODBC.

A portion of a system that is constrained in size or capacity, that has the effect of limiting overall throughput. For example, a memory area might be smaller than necessary; access to a single required resource might prevent multiple CPU cores from running simultaneously; or waiting for disk I/O to complete might prevent the CPU from running at full capacity. Removing bottlenecks tends to improveconcurrency. For example, the ability to have multipleInnoDBbuffer pool instances reduces contention when multiple sessions read from and write to the buffer pool simultaneously.

See Alsobuffer pool,concurrency.

Ashutdown operation immediately followed by a restart. Ideally with a relatively shortwarmup period so that performance and throughput quickly return to a high level.

See Alsoshutdown.

A mechanism for managing different-sizedpages in the InnoDBbuffer pool.

See Alsobuffer pool,page,page size.

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 acrash or other failure occurs at the worst possible time. The main types of buffers used by InnoDB are thebuffer pool, thedoublewrite buffer, and thechange buffer.

See Alsobuffer pool,change buffer,crash,doublewrite buffer.

The memory area that holds cachedInnoDB data for both tables and indexes. For efficiency of high-volume read operations, the buffer pool is divided intopages 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 theLRU algorithm. On systems with large memory, you can improve concurrency by dividing the buffer pool into multiplebuffer pool instances.

SeveralInnoDB status variables,INFORMATION_SCHEMA tables, andperformance_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. SeeSection 17.8.3.6, “Saving and Restoring the Buffer Pool State”.

See Alsobuffer pool instance,LRU,page,warm up.

Any of the multiple regions into which thebuffer pool can be divided, controlled by theinnodb_buffer_pool_instances configuration option. The total memory size specified byinnodb_buffer_pool_size is divided among all buffer pool instances. Typically, having multiple buffer pool instances is appropriate for systems that allocate multiple gigabytes to theInnoDB buffer pool, with each instance being one gigabyte or larger. On systems loading or looking up large amounts of data in the buffer pool from many concurrent sessions, having multiple buffer pool instances reduces contention for exclusive access to data structures that manage the buffer pool.

See Alsobuffer pool.

The built-inInnoDB storage engine within MySQL is the original form of distribution for the storage engine. Contrast with theInnoDB Plugin. Starting with MySQL 5.5, the InnoDB Plugin is merged back into the MySQL code base as the built-inInnoDB storage engine (known as InnoDB 1.1).

This distinction is important mainly in MySQL 5.1, where a feature or bug fix might apply to the InnoDB Plugin but not the built-inInnoDB, or vice versa.

See AlsoInnoDB.

The relationships and sequences of actions that form the basis of business software, used to run a commercial company. Sometimes these rules are dictated by law, other times by company policy. Careful planning ensures that the relationships encoded and enforced by the database, and the actions performed through application logic, accurately reflect the real policies of the company and can handle real-life situations.

For example, an employee leaving a company might trigger a sequence of actions from the human resources department. The human resources database might also need the flexibility to represent data about a person who has been hired, but not yet started work. Closing an account at an online service might result in data being removed from a database, or the data might be moved or flagged so that it could be recovered if the account is re-opened. A company might establish policies regarding salary maximums, minimums, and adjustments, in addition to basic sanity checks such as the salary not being a negative number. A retail database might not allow a purchase with the same serial number to be returned more than once, or might not allow credit card purchases above a certain value, while a database used to detect fraud might allow these kinds of things.

See Alsorelational.

A metadata file used with theInnoDBtransportable tablespace feature. It is produced by the commandFLUSH 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 thespace ID, during theALTER TABLE ... IMPORT TABLESPACE step.

See Also.ibd file,space ID,transportable tablespace.

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 toC++,Java, andC# developers.

See AlsoC API,C++,C#,Java.

The CAPI code is distributed with MySQL. It is included in thelibmysqlclient library and enablesC programs to access a database.

See AlsoAPI,C,libmysqlclient.

A programming language combining strong typing and object-oriented features, running within the Microsoft.NET framework or its open-source counterpartMono. Often used for creating applications with theASP.net framework. Its syntax is familiar toC,C++ andJava developers.

See Also.NET,ASP.net,C,Connector/NET,C++,Java,Mono.

A programming language with core syntax familiar toC developers. Provides access to low-level operations for performance, combined with higher-level data types, object-oriented features, and garbage collection. To write C++ applications for MySQL, you use theConnector/C++ component.

See AlsoC,Connector/C++.

The general term for any memory area that stores copies of data for frequent or high-speed retrieval. InInnoDB, the primary kind of cache structure is thebuffer pool.

See Alsobuffer,buffer pool.

The number of different values in a tablecolumn. When queries refer to columns that have an associatedindex, the cardinality of each column influences which access method is most efficient. For example, for a column with aunique 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 asSELECT 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 ofc1.

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 whenx=50 and a million rows whenx=30. In such a case, you might need to useindex 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 acomposite index.

See Alsocolumn,composite index,index,index hint,persistent statistics,random dive,selectivity,unique constraint.

A special data structure that records changes topages insecondary indexes. These values could result from SQLINSERT,UPDATE, orDELETE statements (DML). The set of features involving the change buffer is known collectively aschange buffering, consisting ofinsert buffering,delete buffering, andpurge buffering.

Changes are only recorded in the change buffer when the relevant page from the secondary index is not in thebuffer pool. When the relevant index page is brought into the buffer pool while associated changes are still in the change buffer, the changes for that page are applied in the buffer pool (merged) using the data from the change buffer. Periodically, thepurge operation that runs during times when the system is mostly idle, or during a slow shutdown, writes the new index pages to disk. The purge operation can write the disk blocks for a series of index values more efficiently than if each value were written to disk immediately.

Physically, the change buffer is part of thesystem tablespace, so that the index changes remain buffered across database restarts. The changes are only applied (merged) when the pages are brought into the buffer pool due to some other read operation.

The kinds and amount of data stored in the change buffer are governed by theinnodb_change_buffering andinnodb_change_buffer_max_size configuration options. To see information about the current data in the change buffer, issue theSHOW ENGINE INNODB STATUS command.

Formerly known as theinsert buffer.

See Alsobuffer pool,change buffering,delete buffering,DML,insert buffer,insert buffering,merge,page,purge,purge buffering,secondary index,system tablespace.

The general term for the features involving thechange buffer, consisting ofinsert buffering,delete buffering, andpurge buffering. Index changes resulting from SQL statements, which could normally involve random I/O operations, are held back and performed periodically by a backgroundthread. This sequence of operations can write the disk blocks for a series of index values more efficiently than if each value were written to disk immediately. Controlled by theinnodb_change_buffering andinnodb_change_buffer_max_size configuration options.

See Alsochange buffer,delete buffering,insert buffering,purge buffering.

As changes are made to data pages that are cached in thebuffer pool, those changes are written to thedata files sometime later, a process known asflushing. The checkpoint is a record of the latest changes (represented by anLSN value) that have been successfully written to the data files.

See Alsobuffer pool,data files,flush,fuzzy checkpointing,LSN.

InInnoDB, a validation mechanism to detect corruption when apage in atablespace is read from disk into theInnoDBbuffer pool. This feature is controlled by theinnodb_checksums configuration option in MySQL 5.5.innodb_checksums is deprecated in MySQL 5.6.3, replaced byinnodb_checksum_algorithm.

Theinnochecksum command helps diagnose corruption problems by testing the checksum values for a specifiedtablespace file while the MySQL server is shut down.

MySQL also uses checksums for replication purposes. For details, see the configuration optionsbinlog_checksum,source_verify_checksum ormaster_verify_checksum, andreplica_sql_verify_checksum orslave_sql_verify_checksum.

See Alsobuffer pool,page,tablespace.

In aforeign key relationship, a child table is one whose rows refer (or point) to rows in another table with an identical value for a specific column. This is the table that contains theFOREIGN KEY ... REFERENCES clause and optionallyON UPDATE andON DELETE clauses. The corresponding row in theparent table must exist before the row can be created in the child table. The values in the child table can prevent delete or update operations on the parent table, or can cause automatic deletion or updates in the child table, based on theON CASCADE option used when creating the foreign key.

See Alsoforeign key,parent table.

Apage in theInnoDBbuffer pool where all changes made in memory have also been written (flushed) to thedata files. The opposite of adirty page.

See Alsobuffer pool,data files,dirty page,flush,page.

Ashutdown that completes without errors and applies all changes toInnoDB tables before finishing, as opposed to acrash or afast shutdown. Synonym forslow shutdown.

See Alsocrash,fast shutdown,shutdown,slow shutdown.

A program that runs outside the database server, communicating with the database by sending requests through aConnector, or anAPI made available throughclient libraries. It can run on the same physical machine as the database server, or on a remote machine connected over a network. It can be a special-purpose database application, or a general-purpose program like themysql command-line processor.

See AlsoAPI,client libraries,connector,mysql,server.

Files containing collections of functions for working with databases. By compiling your program with these libraries, or installing them on the same system as your application, you can run a database application (known as aclient) on a machine that does not have the MySQL server installed; the application accesses the database over a network. With MySQL, you can use thelibmysqlclient library from the MySQL server itself.

See Alsoclient,libmysqlclient.

A type ofprepared statement where the caching and reuse are managed locally, emulating the functionality ofserver-side prepared statements. Historically, used by someConnector/J,Connector/ODBC, andConnector/PHP developers to work around issues with server-side stored procedures. With modern MySQL server versions, server-side prepared statements are recommended for performance, scalability, and memory efficiency.

See AlsoConnector/J,Connector/ODBC,Connector/PHP,prepared statement.

An SQL data type (TINYTEXT,TEXT,MEDIUMTEXT, orLONGTEXT) for objects containing any kind of character data, of arbitrary size. Used for storing text-based documents, with associated character set and collation order. The techniques for handling CLOBs within a MySQL application vary with eachConnector andAPI. MySQL Connector/ODBC definesTEXT values asLONGVARCHAR. For storing binary data, the equivalent is theBLOB type.

See AlsoAPI,BLOB,connector,Connector/ODBC.

TheInnoDB term for aprimary key index.InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated.

In the Oracle Database product, this type of table is known as anindex-organized table.

See Alsoindex,primary key,secondary index.

Abackup taken while the database is shut down. For busy applications and websites, this might not be practical, and you might prefer awarm backup or ahot backup.

See Alsobackup,hot backup,warm backup.

A data item within arow, whose storage and semantics are defined by a data type. Eachtable andindex is largely defined by the set of columns it contains.

Each column has acardinality value. A column can be theprimary key for its table, or part of the primary key. A column can be subject to aunique constraint, aNOT NULL constraint, or both. Values in different columns, even across different tables, can be linked by aforeign key relationship.

In discussions of MySQL internal operations, sometimesfield is used as a synonym.

See Alsocardinality,foreign key,index,NOT NULL constraint,primary key,row,table,unique constraint.

Anindex on a single column.

See Alsocomposite index,index.

When anindex is created with a length specification, such asCREATE INDEX idx ON t1 (c1(N)), only the first N characters of the column value are stored in the index. Keeping the index prefix small makes the index compact, and the memory and disk I/O savings help performance. (Although making the index prefix too small can hinder query optimization by making rows with different values appear to the query optimizer to be duplicates.)

For columns containing binary values or long text strings, where sorting is not a major consideration and storing the entire value in the index would waste space, the index automatically uses the first N (typically 768) characters of the value to do lookups and sorts.

See Alsoindex.

Synonym forstatement interceptor. One aspect of theinterceptor design pattern available for bothConnector/NET andConnector/J. What Connector/NET calls a command, Connector/J refers to as a statement. Contrast withexception interceptor.

See AlsoConnector/J,Connector/NET,exception interceptor,interceptor,statement interceptor.

ASQL statement that ends atransaction, making permanent any changes made by the transaction. It is the opposite ofrollback, which undoes any changes made in the transaction.

InnoDB uses anoptimistic mechanism for commits, so that changes can be written to the data files before the commit actually occurs. This technique makes the commit itself faster, with the tradeoff that more work is required in case of a rollback.

By default, MySQL uses theautocommit setting, which automatically issues a commit following each SQL statement.

See Alsoautocommit,optimistic,rollback,SQL,transaction.

Arow format for InnoDB tables. It was the default row format from MySQL 5.0.3 to MySQL 5.7.8. In MySQL 8.0, the default row format is defined by theinnodb_default_row_format configuration option, which has a default setting ofDYNAMIC. TheCOMPACT row format provides a more compact representation for nulls and variable-length columns than theREDUNDANT row format.

For additional information aboutInnoDBCOMPACT row format, seeSection 17.10, “InnoDB Row Formats”.

See Alsodynamic row format,file format,redundant row format,row format.

Anindex that includes multiple columns.

See Alsoindex.

The compression feature of theMySQL Enterprise Backup product makes a compressed copy of each tablespace, changing the extension from.ibd to.ibz. Compressing backup data allows you to keep more backups on hand, and reduces the time to transfer backups to a different server. The data is uncompressed during the restore operation. When a compressed backup operation processes a table that is already compressed, it skips the compression step for that table, because compressing again would result in little or no space savings.

A set of files produced by theMySQL Enterprise Backup product, where eachtablespace is compressed. The compressed files are renamed with a.ibz file extension.

Applyingcompression at the start of the backup process helps to avoid storage overhead during the compression process, and to avoid network overhead when transferring the backup files to another server. The process ofapplying thebinary log takes longer, and requires uncompressing the backup files.

See Alsoapply,binary log,compression,hot backup,MySQL Enterprise Backup,tablespace.

Arow format that enables data and indexcompression forInnoDB tables. Large fields are stored away from the page that holds the rest of the row data, as indynamic row format. Both index pages and the large fields are compressed, yielding memory and disk savings. Depending on the structure of the data, the decrease in memory and disk usage might or might not outweigh the performance overhead of uncompressing the data as it is used. SeeSection 17.9, “InnoDB Table and Page Compression” for usage details.

For additional information aboutInnoDBCOMPRESSED row format, seeDYNAMIC Row Format.

See Alsocompression,dynamic row format,row format.

A table for which the data is stored in compressed form. ForInnoDB, it is a table created withROW_FORMAT=COMPRESSED. SeeSection 17.9, “InnoDB Table and Page Compression” for more information.

See Alsocompressed row format,compression.

A feature with wide-ranging benefits from using less disk space, performing less I/O, and using less memory for caching.

InnoDB supports both table-level and page-level compression.InnoDB page compression is also referred to astransparent page compression. For more information aboutInnoDB compression, seeSection 17.9, “InnoDB Table and Page Compression”.

Another type of compression is thecompressed backup feature of theMySQL Enterprise Backup product.

See Alsobuffer pool,compressed backup,compressed row format,DML,transparent page compression.

Not actually an error, rather an expensive operation that can occur when usingcompression in combination withDML operations. It occurs when: updates to a compressedpage overflow the area on the page reserved for recording modifications; the page is compressed again, with all changes applied to the table data; the re-compressed data does not fit on the original page, requiring MySQL to split the data into two new pages and compress each one separately. To check the frequency of this condition, query theINFORMATION_SCHEMA.INNODB_CMP table and check how much the value of theCOMPRESS_OPS column exceeds the value of theCOMPRESS_OPS_OK column. Ideally, compression failures do not occur often; when they do, you can adjust theinnodb_compression_level,innodb_compression_failure_threshold_pct, andinnodb_compression_pad_pct_max configuration options.

See Alsocompression,DML,page.

Seecomposite index.

The ability of multiple operations (in database terminology,transactions) to run simultaneously, without interfering with each other. Concurrency is also involved with performance, because ideally the protection for multiple simultaneous transactions works with a minimum of performance overhead, using efficient mechanisms forlocking.

See AlsoACID,locking,transaction.

The file that holds theoption values used by MySQL at startup. Traditionally, on Linux and Unix this file is namedmy.cnf, and on Windows it is namedmy.ini. You can set a number of options related to InnoDB under the[mysqld] section of the file.

SeeSection 6.2.2.2, “Using Option Files” for information about where MySQL searches for configuration files.

When you use theMySQL Enterprise Backup product, you typically use two configuration files: one that specifies where the data comes from and how it is structured (which could be the original configuration file for your server), and a stripped-down one containing only a small set of options that specify where the backup data goes and how it is structured. The configuration files used with theMySQL Enterprise Backup product must contain certain options that are typically left out of regular configuration files, so you might need to add options to your existing configuration file for use withMySQL Enterprise Backup.

See Alsomy.cnf,MySQL Enterprise Backup,option,option file.

The communication channel between an application and a MySQL server. The performance and scalability of a database applications is influenced by on how quickly a database connection can be established, how many can be made simultaneously, and how long they persist. The parameters such ashost,port, and so on are represented as aconnection string inConnector/NET, and as aDSN inConnector/ODBC. High-traffic systems make use of an optimization known as theconnection pool.

See Alsoconnection pool,connection string,Connector/NET,Connector/ODBC,DSN,host,port.

A cache area that allows databaseconnections to be reused within the same application or across different applications, rather than setting up and tearing down a new connection for every database operation. This technique is common withJ2EE application servers.Java applications usingConnector/J can use the connection pool features ofTomcat and other application servers. The reuse is transparent to applications; the application still opens and closes the connection as usual.

See Alsoconnection,Connector/J,J2EE,Tomcat.

A representation of the parameters for a databaseconnection, encoded as a string literal so that it can be used in program code. The parts of the string represent connection parameters such ashost andport. A connection string contains several key-value pairs, separated by semicolons. Each key-value pair is joined with an equal sign. Frequently used withConnector/NET applications; seeCreating a Connector/NET Connection String for details.

See Alsoconnection,Connector/NET,host,port.

MySQL Connectors provide connectivity to the MySQL server forclient programs. Several programming languages and frameworks each have their own associated Connector. Contrast with the lower-level access provided by anAPI.

See AlsoAPI,client,Connector/C++,Connector/J,Connector/NET,Connector/ODBC.

Connector/C++ 8.0 can be used to access MySQL servers that implement adocument store, or in a traditional way using SQL queries. It enables development of C++ applications using X DevAPI, or plain C applications using X DevAPI for C. It also enables development of C++ applications that use the legacy JDBC-based API from Connector/C++ 1.1. For more information, seeMySQL Connector/C++ 9.3 Developer Guide.

See Alsoclient,connector,JDBC.

AJDBC driver that provides connectivity forclient applications developed in theJava programming language. MySQL Connector/J is a JDBC Type 4 driver: a pure-Java implementation of the MySQL protocol that does not rely on the MySQLclient libraries. For full details, seeMySQL Connector/J Developer Guide.

See Alsoclient,client libraries,connector,Java,JDBC.

A MySQLconnector for developers writing applications using languages, technologies, and frameworks such asC#,.NET,Mono,Visual Studio,ASP.net, andADO.net.

See AlsoADO.NET,ASP.net,connector,C#,Mono,Visual Studio.

The family of MySQL ODBC drivers that provide access to a MySQL database using the industry standard Open Database Connectivity (ODBC) API. Formerly called MyODBC drivers. For full details, seeMySQL Connector/ODBC Developer Guide.

See Alsoconnector,ODBC.

A version of themysql andmysqliAPIs forPHP optimized for the Windows operating system.

See Alsoconnector,PHP,PHP API.

A read operation that usessnapshot information to present query results based on a point in time, regardless of changes performed by other transactions running at the same time. If queried data has been changed by another transaction, the original data is reconstructed based on the contents of theundo log. This technique avoids some of thelocking issues that can reduceconcurrency by forcing transactions to wait for other transactions to finish.

WithREPEATABLE READisolation level, the snapshot is based on the time when the first read operation is performed. WithREAD COMMITTED isolation level, the snapshot is reset to the time of each consistent read operation.

Consistent read is the default mode in whichInnoDB processesSELECT statements inREAD COMMITTED andREPEATABLE READ isolation levels. Because a consistent read does not set any locks on the tables it accesses, other sessions are free to modify those tables while a consistent read is being performed on the table.

For technical details about the applicable isolation levels, seeSection 17.7.2.3, “Consistent Nonlocking Reads”.

See Alsoconcurrency,isolation level,locking,READ COMMITTED,REPEATABLE READ,snapshot,transaction,undo log.

An automatic test that can block database changes to prevent data from becoming inconsistent. (In computer science terms, a kind of assertion related to an invariant condition.) Constraints are a crucial component of theACID philosophy, to maintain data consistency. Constraints supported by MySQL includeFOREIGN KEY constraints andunique constraints.

See AlsoACID,foreign key,unique constraint.

A value that is incremented by a particular kind ofInnoDB operation. Useful for measuring how busy a server is, troubleshooting the sources of performance issues, and testing whether changes (for example, to configuration settings or indexes used by queries) have the desired low-level effects. Different kinds of counters are available throughPerformance Schema tables andINFORMATION_SCHEMA tables, particularlyINFORMATION_SCHEMA.INNODB_METRICS.

See AlsoINFORMATION_SCHEMA,metrics counter,Performance Schema.

Anindex that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O.InnoDB can apply this optimization technique to more indexes than MyISAM can, becauseInnoDBsecondary indexes also include theprimary key columns.InnoDB cannot apply this technique for queries against tables modified by a transaction, until that transaction ends.

Anycolumn index orcomposite index could act as a covering index, given the right query. Design your indexes and queries to take advantage of this optimization technique wherever possible.

See Alsocolumn index,composite index,index,primary key,secondary index.

A type ofworkload where the primarybottleneck is CPU operations in memory. Typically involves read-intensive operations where the results can all be cached in thebuffer pool.

See Alsobottleneck,buffer pool,workload.

MySQL uses the term“crash” to refer generally to any unexpectedshutdown operation where the server cannot do its normal cleanup. For example, a crash could happen due to a hardware fault on the database server machine or storage device; a power failure; a potential data mismatch that causes the MySQL server to halt; afast shutdown initiated by the DBA; or many other reasons. The robust, automaticcrash recovery forInnoDB tables ensures that data is made consistent when the server is restarted, without any extra work for the DBA.

See Alsocrash recovery,fast shutdown,InnoDB,shutdown.

The cleanup activities that occur when MySQL is started again after acrash. ForInnoDB tables, changes from incomplete transactions are replayed using data from theredo log. Changes that werecommitted before the crash, but not yet written into thedata files, are reconstructed from thedoublewrite buffer. When the database is shut down normally, this type of activity is performed during shutdown by thepurge operation.

During normal operation, committed data can be stored in thechange buffer for a period of time before being written to the data files. There is always a tradeoff between keeping the data files up-to-date, which introduces performance overhead during normal operation, and buffering the data, which can make shutdown and crash recovery take longer.

See Alsochange buffer,commit,crash,data files,doublewrite buffer,InnoDB,purge,redo log.

Acronym for“create, read, update, delete”, a common sequence of operations in database applications. Often denotes a class of applications with relatively simple database usage (basicDDL,DML andquery statements inSQL) that can be implemented quickly in any language.

See AlsoDDL,DML,query,SQL.

An internal MySQL data structure that represents the result set of an SQL statement. Often used withprepared statements anddynamic SQL. It works like an iterator in other high-level languages, producing each value from the result set as requested.

Although SQL usually handles the processing of cursors for you, you might delve into the inner workings when dealing with performance-critical code.

See Alsodynamic SQL,prepared statement,query.

SeeDDL.

Metadata that keeps track of database objects such astables,indexes, and tablecolumns. For the MySQL data dictionary, introduced in MySQL 8.0, metadata is physically located inInnoDBfile-per-table tablespace files in themysql database directory. For theInnoDB data dictionary, metadata is physically located in theInnoDBsystem tablespace.

Because theMySQL Enterprise Backup product always backs up theInnoDB system tablespace, all backups include the contents of theInnoDB data dictionary.

See Alsocolumn,file-per-table,.frm file,index,MySQL Enterprise Backup,system tablespace,table.

The directory under which each MySQLinstance keeps thedata files forInnoDB and the directories representing individual databases. Controlled by thedatadir configuration option.

See Alsodata files,instance.

The files that physically containtable andindex data.

TheInnoDBsystem tablespace, which holds theInnoDBdata dictionary and is capable of holding data for multipleInnoDB tables, is represented by one or more.ibdata data files.

File-per-table tablespaces, which hold data for a singleInnoDB table, are represented by a.ibd data file.

General tablespaces (introduced in MySQL 5.7.6), which can hold data for multipleInnoDB tables, are also represented by a.ibd data file.

See Alsodata dictionary,file-per-table,general tablespace,.ibd file,ibdata file,index,system tablespace,table,tablespace.

SeeDML.

A database system or application that primarily runs largequeries. The read-only or read-mostly data might be organized indenormalized form for query efficiency. Can benefit from the optimizations forread-only transactions in MySQL 5.6 and higher. Contrast withOLTP.

See Alsodenormalized,OLTP,query,read-only transaction.

Within the MySQLdata directory, each database is represented by a separate directory. The InnoDBsystem tablespace, which can hold table data from multiple databases within a MySQLinstance, is kept indata files that reside outside of individual database directories. Whenfile-per-table mode is enabled, the.ibd files representing individual InnoDB tables are stored inside the database directories unless created elsewhere using theDATA DIRECTORY clause. General tablespaces, introduced in MySQL 5.7.6, also hold table data in.ibd files. Unlike file-per-table.ibd files, general tablespace.ibd files can hold table data from multiple databases within a MySQLinstance, and can be assigned to directories relative to or independent of the MySQL data directory.

For long-time MySQL users, a database is a familiar notion. Users coming from an Oracle Database background may find that the MySQL meaning of a database is closer to what Oracle Database calls aschema.

See Alsodata files,file-per-table,.ibd file,instance,schema,system tablespace.

Data control language, a set ofSQL statements for managing privileges. In MySQL, consists of theGRANT andREVOKE statements. Contrast withDDL andDML.

See AlsoDDL,DML,SQL.

A feature that lets you use the data design tools withinVisual Studio to manipulate the schema and objects within a MySQL database. For MySQL applications usingConnector/NET, the MySQL Visual Studio Plugin acts as a DDEX provider with MySQL 5.0 and later.

See AlsoVisual Studio.

Data definition language, a set ofSQL statements for manipulating the database itself rather than individual table rows. Includes all forms of theCREATE,ALTER, andDROP statements. Also includes theTRUNCATE statement, because it works differently than aDELETE FROMtable_name statement, even though the ultimate effect is similar.

DDL statements automaticallycommit the currenttransaction; they cannot berolled back.

TheInnoDBonline DDL feature enhances performance forCREATE INDEX,DROP INDEX, and many types ofALTER TABLE operations. SeeSection 17.12, “InnoDB and Online DDL” for more information. Also, theInnoDBfile-per-table setting can affect the behavior ofDROP TABLE andTRUNCATE TABLE operations.

Contrast withDML andDCL.

See Alsocommit,DCL,DML,file-per-table,rollback,SQL,transaction.

A situation where differenttransactions are unable to proceed, because each holds alock that the other needs. Because both transactions are waiting for a resource to become available, neither one ever releases the locks it holds.

A deadlock can occur when the transactions lock rows in multiple tables (through statements such asUPDATE orSELECT ... FOR UPDATE), but in the opposite order. A deadlock can also occur when such statements lock ranges of index records andgaps, with each transaction acquiring some locks but not others due to a timing issue.

For background information on how deadlocks are automatically detected and handled, seeSection 17.7.5.2, “Deadlock Detection”. For tips on avoiding and recovering from deadlock conditions, seeSection 17.7.5.3, “How to Minimize and Handle Deadlocks”.

See Alsogap,lock,transaction.

A mechanism that automatically detects when adeadlock occurs, and automaticallyrolls back one of thetransactions involved (thevictim). Deadlock detection can be disabled using theinnodb_deadlock_detect configuration option.

See Alsodeadlock,rollback,transaction,victim.

WhenInnoDB processes aDELETE statement, the rows are immediately marked for deletion and no longer are returned by queries. The storage is reclaimed sometime later, during the periodic garbage collection known as thepurge operation. For removing large quantities of data, related operations with their own performance characteristics areTRUNCATE andDROP.

See Alsodrop,purge,truncate.

The technique of storing changes to secondary index pages, resulting fromDELETE operations, in thechange buffer rather than writing the changes immediately, so that the physical writes can be performed to minimize random I/O. (Because delete operations are a two-step process, this operation buffers the write that normally marks an index record for deletion.) It is one of the types ofchange buffering; the others areinsert buffering andpurge buffering.

See Alsochange buffer,change buffering,insert buffer,insert buffering,purge buffering.

A data storage strategy that duplicates data across different tables, rather than linking the tables withforeign keys andjoin queries. Typically used indata warehouse applications, where the data is not updated after loading. In such applications, query performance is more important than making it simple to maintain consistent data during updates. Contrast withnormalized.

See Alsodata warehouse,foreign key,join,normalized.

A type ofindex where index storage is optimized to processORDER BYcolumn DESC clauses.

See Alsoindex.

The dictionary object cache stores previously accesseddata dictionary objects in memory to enable object reuse and minimize disk I/O. AnLRU-based eviction strategy is used to evict least recently used objects from memory. The cache is comprised of several partitions that store different object types.

For more information, seeSection 16.4, “Dictionary Object Cache”.

See Alsodata dictionary,LRU.

Apage in theInnoDBbuffer pool that has been updated in memory, where the changes are not yet written (flushed) to thedata files. The opposite of aclean page.

See Alsobuffer pool,clean page,data files,flush,page.

An operation that retrieves unreliable data, data that was updated by another transaction but not yetcommitted. It is only possible with theisolation level known asread uncommitted.

This kind of operation does not adhere to theACID principle of database design. It is considered very risky, because the data could berolled back, or updated further before being committed; then, the transaction doing the dirty read would be using data that was never confirmed as accurate.

Its opposite isconsistent read, whereInnoDB ensures that a transaction does not read information updated by another transaction, even if the other transaction commits in the meantime.

See AlsoACID,commit,consistent read,isolation level,READ UNCOMMITTED,rollback.

A kind of database that primarily organizes data on disk storage (hard drives or equivalent). Data is brought back and forth between disk and memory to be operated upon. It is the opposite of anin-memory database. AlthoughInnoDB is disk-based, it also contains features such as hebuffer pool, multiple buffer pool instances, and theadaptive hash index that allow certain kinds of workloads to work primarily from memory.

See Alsoadaptive hash index,buffer pool,in-memory database.

A type ofworkload where the primarybottleneck is disk I/O. (Also known asI/O-bound.) Typically involves frequent writes to disk, or random reads of more data than can fit into thebuffer pool.

See Alsobottleneck,buffer pool,workload.

Data manipulation language, a set ofSQL statements for performingINSERT,UPDATE, andDELETE operations. TheSELECT statement is sometimes considered as a DML statement, because theSELECT ... FOR UPDATE form is subject to the same considerations forlocking asINSERT,UPDATE, andDELETE.

DML statements for anInnoDB table operate in the context of atransaction, so their effects can becommitted orrolled back as a single unit.

Contrast withDDL andDCL.

See Alsocommit,DCL,DDL,locking,rollback,SQL,transaction.

In theInnoDBfull-text search feature, a special column in the table containing theFULLTEXT index, to uniquely identify the document associated with eachilist value. Its name isFTS_DOC_ID (uppercase required). The column itself must be ofBIGINT UNSIGNED NOT NULL type, with a unique index namedFTS_DOC_ID_INDEX. Preferably, you define this column when creating the table. IfInnoDB must add the column to the table while creating aFULLTEXT index, the indexing operation is considerably more expensive.

See Alsofull-text search,FULLTEXT index,ilist.

InnoDB uses a file flush technique called doublewrite. Before writingpages to thedata files,InnoDB first writes them to a storage area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer have completed, doesInnoDB write the pages to their proper positions in the data file. If there is an operating system, storage subsystem ormysqld process crash in the middle of a page write,InnoDB can find a good copy of the page from the doublewrite buffer duringcrash recovery.

Although data is always written twice, the doublewrite buffer does not require twice as much I/O overhead or twice as many I/O operations. Data is written to the buffer itself as a large sequential chunk, with a singlefsync() call to the operating system.

See Alsocrash recovery,data files,page,purge.

A kind ofDDL operation that removes a schema object, through a statement such asDROP TABLE orDROP INDEX. It maps internally to anALTER TABLE statement. From anInnoDB perspective, the performance considerations of such operations involve the time that thedata dictionary is locked to ensure that interrelated objects are all updated, and the time to update memory structures such as thebuffer pool. For atable, the drop operation has somewhat different characteristics than atruncate operation (TRUNCATE TABLE statement).

See Alsobuffer pool,data dictionary,DDL,table,truncate.

Acronym for“Database Source Name”. It is the encoding forconnection information withinConnector/ODBC. SeeConfiguring a Connector/ODBC DSN on Windows for full details. It is the equivalent of theconnection string used byConnector/NET.

See Alsoconnection,connection string,Connector/NET,Connector/ODBC.

A type ofcursor supported byODBC that can pick up new and changed results when the rows are read again. Whether and how quickly the changes are visible to the cursor depends on the type of table involved (transactional or non-transactional) and the isolation level for transactional tables. Support for dynamic cursors must be explicitly enabled.

See Alsocursor,ODBC.

AnInnoDB row format. Because long variable-length column values are stored outside of the page that holds the row data, it is very efficient for rows that include large objects. Since the large fields are typically not accessed to evaluate query conditions, they are not brought into thebuffer pool as often, resulting in fewer I/O operations and better utilization of cache memory.

As of MySQL 5.7.9, the default row format is defined byinnodb_default_row_format, which has a default value ofDYNAMIC.

For additional information aboutInnoDBDYNAMIC row format, seeDYNAMIC Row Format.

See Alsobuffer pool,file format,row format.

A feature that lets you create and executeprepared statements using more robust, secure, and efficient methods to substitute parameter values than the naive technique of concatenating the parts of the statement into a string variable.

See Alsoprepared statement.

Aprepared statement created and executed throughdynamic SQL.

See Alsodynamic SQL,prepared statement.

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

See Alsobeta.

A programming language including many object-oriented features. Some of its concepts are familiar toJava andC# developers. For the open-source EiffelAPI for MySQL, seeSection 31.13, “MySQL Eiffel Wrapper”.

See AlsoAPI,C#,Java.

The embedded MySQL server library (libmysqld) makes it possible to run a full-featured MySQL server inside aclient application. The main benefits are increased speed and more simple management for embedded applications.

See Alsoclient,libmysqld.

A type oflog showing information about MySQL startup and critical runtime errors andcrash information. For details, seeSection 7.4.2, “The Error Log”.

See Alsocrash,log.

The process of removing an item from a cache or other temporary storage area, such as theInnoDBbuffer pool. Often, but not always, uses theLRU algorithm to determine which item to remove. When adirty page is evicted, its contents areflushed to disk, and any dirtyneighbor pages might be flushed also.

See Alsobuffer pool,dirty page,flush,LRU,neighbor page.

A type ofinterceptor for tracing, debugging, or augmenting SQL errors encountered by a database application. For example, the interceptor code could issue aSHOW WARNINGS statement to retrieve additional information, and add descriptive text or even change the type of the exception returned to the application. Because the interceptor code is only called when SQL statements return errors, it does not impose any performance penalty on the application during normal (error-free) operation.

InJava applications usingConnector/J, setting up this type of interceptor involves implementing thecom.mysql.jdbc.ExceptionInterceptor interface, and adding aexceptionInterceptors property to theconnection string.

InVisual Studio applications usingConnector/NET, setting up this type of interceptor involves defining a class that inherits from theBaseExceptionInterceptor class and specifying that class name as part of the connection string.

See AlsoConnector/J,Connector/NET,interceptor,Java,Visual Studio.

A kind oflock that prevents any othertransaction from locking the same row. Depending on the transactionisolation level, this kind of lock might block other transactions from writing to the same row, or might also block other transactions from reading the same row. The defaultInnoDB isolation level,REPEATABLE READ, enables higherconcurrency by allowing transactions to read rows that have exclusive locks, a technique known asconsistent read.

See Alsoconcurrency,consistent read,isolation level,lock,REPEATABLE READ,shared lock,transaction.

A group ofpages within atablespace. For the defaultpage size of 16KB, an extent contains 64 pages. In MySQL 5.6, the page size for anInnoDB instance can be 4KB, 8KB, or 16KB, controlled by theinnodb_page_size configuration option. For 4KB, 8KB, and 16KB pages sizes, the extent size is always 1MB (or 1048576 bytes).

Support for 32KB and 64KBInnoDB page sizes was added in MySQL 5.7.6. For a 32KB page size, the extent size is 2MB. For a 64KB page size, the extent size is 4MB.

InnoDB features such assegments,read-ahead requests and thedoublewrite buffer use I/O operations that read, write, allocate, or free data one extent at a time.

See Alsodoublewrite buffer,page,page size,read-ahead,segment,tablespace.

A file containing the metadata, such as the table definition, of a MySQL table..frm files were removed in MySQL 8.0 but are still used in earlier MySQL releases. In MySQL 8.0, data previously stored in.frm files is stored indata dictionary tables.

See Alsodata dictionary,MySQL Enterprise Backup,system tablespace.

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 withinJ2EE environments by the application server or framework.

See AlsoConnector/J,J2EE.

A capability first introduced in the InnoDB Plugin, now part of MySQL in 5.5 and higher, that speeds up creation ofInnoDBsecondary 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 asALTER TABLE ... ENGINE=INNODB orINSERT 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 ofALTER TABLE operations can be performed without copying the table, without blockingDML operations, or both. Thus in MySQL 5.6 and higher, this set of features is referred to asonline DDL rather than Fast Index Creation.

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

See AlsoDML,index,online DDL,secondary index.

The defaultshutdown procedure forInnoDB, based on the configuration settinginnodb_fast_shutdown=1. To save time, certainflush 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 incrash recovery. In cases where the database is being shut down for an upgrade or downgrade, do aslow shutdown instead to ensure that all relevant changes are applied to thedata files during the shutdown.

See Alsocrash recovery,data files,flush,shutdown,slow shutdown.

The file format forInnoDB tables.

See Alsofile-per-table,.ibd file,ibdata file,row format.

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

With theinnodb_file_per_table option enabled, you can create a table in its own.ibd file rather than in the sharedibdata files of thesystem tablespace. When table data is stored in an individual.ibd file, you have more flexibility to chooserow formats required for features such as datacompression. TheTRUNCATE TABLE operation is also faster, and reclaimed space can be used by the operating system rather than remaining reserved forInnoDB.

TheMySQL 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.

See Alsocompressed row format,compression,file format,.ibd file,ibdata file,innodb_file_per_table,MySQL Enterprise Backup,row format,system tablespace.

In anInnoDBindex, the proportion of apage 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. SeeSection 17.6.2.2, “The Physical Structure of an InnoDB Index” for more information.

See Alsoindex,page.

This row format is used by theMyISAM storage engine, not byInnoDB. If you create anInnoDB table with the optionROW_FORMAT=FIXED in MySQL 5.7.6 or earlier,InnoDB uses thecompact row format instead, although theFIXED value might still show up in output such asSHOW TABLE STATUS reports. As of MySQL 5.7.7,InnoDB returns an error ifROW_FORMAT=FIXED is specified.

See Alsocompact row format,row format.

To write changes to the database files, that had been buffered in a memory area or a temporary disk storage area. TheInnoDB storage structures that are periodically flushed include theredo log, theundo log, and thebuffer pool.

Flushing can happen because a memory area becomes full and the system needs to free some space, because acommit operation means the changes from a transaction can be finalized, or because aslow 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 calledfuzzy checkpointing to flush small batches of pages to spread out the I/O overhead.

See Alsobuffer pool,commit,fuzzy checkpointing,redo log,slow shutdown,undo log.

An internalInnoDB data structure that tracksdirty pages in thebuffer pool: that is,pages that have been changed and need to be written back out to disk. This data structure is updated frequently byInnoDB internalmini-transactions, and so is protected by its ownmutex to allow concurrent access to the buffer pool.

See Alsobuffer pool,dirty page,LRU,mini-transaction,mutex,page,page cleaner.

A type of pointer relationship, between rows in separateInnoDB tables. The foreign key relationship is defined on one column in both theparent table and thechild table.

In addition to enabling fast lookup of related information, foreign keys help to enforcereferential integrity, by preventing any of these pointers from becoming invalid as data is inserted, updated, and deleted. This enforcement mechanism is a type ofconstraint. A row that points to another table cannot be inserted if the associated foreign key value does not exist in the other table. If a row is deleted or its foreign key value changed, and rows in another table point to that foreign key value, the foreign key can be set up to prevent the deletion, cause the corresponding column values in the other table to becomenull, or automatically delete the corresponding rows in the other table.

One of the stages in designing anormalized database is to identify data that is duplicated, separate that data into a new table, and set up a foreign key relationship so that the multiple tables can be queried like a single table, using ajoin operation.

See Alsochild table,FOREIGN KEY constraint,join,normalized,NULL,parent table,referential integrity,relational.

The type ofconstraint that maintains database consistency through aforeign key relationship. Like other kinds of constraints, it can prevent data from being inserted or updated if data would become inconsistent; in this case, the inconsistency being prevented is between data in multiple tables. Alternatively, when aDML operation is performed,FOREIGN KEY constraints can cause data inchild rows to be deleted, changed to different values, or set tonull, based on theON CASCADE option specified when creating the foreign key.

See Alsochild table,constraint,DML,foreign key,NULL.

In most contexts, an acronym forfull-text search. Sometimes in performance discussions, an acronym forfull table scan.

See Alsofull table scan,full-text search.

Abackup that includes all thetables in each MySQLdatabase, and all the databases in a MySQLinstance. Contrast withpartial backup.

See Alsobackup,database,instance,partial backup,table.

An operation that requires reading the entire contents of a table, rather than just selected portions using anindex. Typically performed either with small lookup tables, or in data warehousing situations with large tables where all available data is aggregated and analyzed. How frequently these operations occur, and the sizes of the tables relative to available memory, have implications for the algorithms used in query optimization and managing thebuffer pool.

The purpose of indexes is to allow lookups for specific values or ranges of values within a large table, thus avoiding full table scans when practical.

See Alsobuffer pool,index.

The MySQL feature for finding words, phrases, Boolean combinations of words, and so on within table data, in a faster, more convenient, and more flexible way than using the SQLLIKE operator or writing your own application-level search algorithm. It uses the SQL functionMATCH() andFULLTEXT indexes.

See AlsoFULLTEXT index.

The special kind ofindex that holds thesearch index in the MySQLfull-text search mechanism. Represents the words from values of a column, omitting any that are specified asstopwords. Originally, only available forMyISAM tables. Starting in MySQL 5.6.4, it is also available forInnoDB tables.

See Alsofull-text search,index,InnoDB,search index,stopword.

A technique thatflushes small batches ofdirty pages from thebuffer pool, rather than flushing all dirty pages at once which would disrupt database processing.

See Alsobuffer pool,dirty page,flush.

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

See Alsobeta.

Acronym for“Global Assembly Cache”. A central area for storing libraries (assemblies) on a.NET system. Physically consists of nested folders, treated as a single virtual folder by the.NET CLR.

See Also.NET,assembly.

A place in anInnoDBindex data structure where new values could be inserted. When you lock a set of rows with a statement such asSELECT ... FOR UPDATE,InnoDB can create locks that apply to the gaps as well as the actual values in the index. For example, if you select all values greater than 10 for update, a gap lock prevents another transaction from inserting a new value that is greater than 10. Thesupremum record andinfimum record represent the gaps containing all values greater than or less than all the current index values.

See Alsoconcurrency,gap lock,index,infimum record,isolation level,supremum record.

Alock on agap between index records, or a lock on the gap before the first or after the last index record. For example,SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into the columnt.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked. Contrast withrecord lock andnext-key lock.

Gap locks are part of the tradeoff between performance andconcurrency, and are used in some transactionisolation levels and not others.

See Alsogap,infimum record,lock,next-key lock,record lock,supremum record.

Seegeneral query log.

A type oflog used for diagnosis and troubleshooting of SQL statements processed by the MySQL server. Can be stored in a file or in a database table. You must enable this feature through thegeneral_log configuration option to use it. You can disable it for a specific connection through thesql_log_off configuration option.

Records a broader range of queries than theslow query log. Unlike thebinary log, which is used for replication, the general query log containsSELECT statements and does not maintain strict ordering. For more information, seeSection 7.4.3, “The General Query Log”.

See Alsobinary log,log,slow query log.

A sharedInnoDBtablespace created usingCREATE TABLESPACE syntax. General tablespaces can be created outside of the MySQL data directory, are capable of holding multipletables, and support tables of all row formats. General tablespaces were introduced in MySQL 5.7.6.

Tables are added to a general tablespace usingCREATE TABLEtbl_name ... TABLESPACE [=]tablespace_name orALTER TABLEtbl_name TABLESPACE [=]tablespace_name syntax.

Contrast withsystem tablespace andfile-per-table tablespace.

For more information, seeSection 17.6.3.3, “General Tablespaces”.

See Alsofile-per-table,system tablespace,table,tablespace.

A column whose values are computed from an expression included in the column definition. A generated column can bevirtual orstored.

See Alsobase column,stored generated column,virtual generated column.

Seestored generated column.

Seevirtual generated column.

See AlsoJ2EE.

Atemporary tablespace that storesrollback segments for changes made to user-created temporary tables.

See Alsotemporary tablespace.

A type oftransaction involved inXA 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 extendsACID 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.

See AlsoACID,transaction,XA.

AnInnoDB optimization that performs some low-level I/O operations (log write) once for a set ofcommit operations, rather than flushing and syncing separately for each commit.

See Alsobinary log,commit.

Acronym for“globally unique identifier”, an ID value that can be used to associate data across different databases, languages, operating systems, and so on. (As an alternative to using sequential integers, where the same values could appear in different tables, databases, and so on referring to different data.) Older MySQL versions represented it asBINARY(16). Currently, it is represented asCHAR(36). MySQL has aUUID() function that returns GUID values in character format, and aUUID_SHORT() function that returns GUID values in integer format. Because successive GUID values are not necessarily in ascending sort order, it is not an efficient value to use as a primary key for large InnoDB tables.

A type ofindex intended for queries that use equality operators, rather than range operators such as greater-than orBETWEEN. It is available forMEMORY tables. Although hash indexes are the default forMEMORY tables for historic reasons, that storage engine also supportsB-tree indexes, which are often a better choice for general-purpose queries.

MySQL includes a variant of this index type, theadaptive hash index, that is constructed automatically forInnoDB tables if needed based on runtime conditions.

See Alsoadaptive hash index,B-tree,index,InnoDB.

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

See Alsodisk-based,SSD.

A periodic message that is sent to indicate that a system is functioning properly. In areplication context, if thesource stops sending such messages, one of thereplicas can take its place. Similar techniques can be used between the servers in a cluster environment, to confirm that all of them are operating properly.

See Alsoreplication,source.

A value representing an upper limit, either a hard limit that should not be exceeded at runtime, or a record of the maximum value that was actually reached. Contrast withlow-water mark.

See Alsolow-water mark.

A list oftransactions with delete-marked records scheduled to be processed by theInnoDBpurge operation. Recorded in theundo log. The length of the history list is reported by the commandSHOW ENGINE INNODB STATUS. If the history list grows longer than the value of theinnodb_max_purge_lag configuration option, eachDML operation is delayed slightly to allow the purge operation to finishflushing the deleted records.

Also known aspurge lag.

See AlsoDML,flush,purge,purge lag,rollback segment,transaction,undo log.

Releasing empty blocks from a page. TheInnoDBtransparent page compression feature relies on hole punching support. For more information, seeSection 17.9.2, “InnoDB Page Compression”.

See Alsosparse file,transparent page compression.

The network name of a database server, used to establish aconnection. Often specified in conjunction with aport. In some contexts, the IP address127.0.0.1 works better than the special namelocalhost for accessing a database on the same server as the application.

See Alsoconnection,localhost,port.

A condition where a row, table, or internal data structure is accessed so frequently, requiring some form of locking or mutual exclusion, that it results in a performance or scalability issue.

Although“hot” typically indicates an undesirable condition, ahot backup is the preferred type of backup.

See Alsohot backup.

A backup taken while the database is running and applications are reading and writing to it. The backup involves more than simply copying data files: it must include any data that was inserted or updated while the backup was in process; it must exclude any data that was deleted while the backup was in process; and it must ignore any changes that were not committed.

The Oracle product that performs hot backups, ofInnoDB tables especially but also tables fromMyISAM and other storage engines, is known asMySQL Enterprise Backup.

The hot backup process consists of two stages. The initial copying of the data files produces araw backup. Theapply step incorporates any changes to the database that happened while the backup was running. Applying the changes produces aprepared backup; these files are ready to be restored whenever necessary.

See Alsoapply,MySQL Enterprise Backup,prepared backup,raw backup.

The data file forfile-per-table tablespaces and general tablespaces. File-per-table tablespace.ibd files contain a single table and associated index data.General tablespace.ibd files may contain table and index data for multiple tables.

The.ibd file extension does not apply to thesystem tablespace, which consists of one or moreibdata files.

If a file-per-table tablespace or general tablespace is created with theDATA DIRECTORY = clause, the.ibd file is located at the specified path, outside the normal data directory.

When a.ibd file is included in a compressed backup by theMySQL Enterprise Backup product, the compressed equivalent is a.ibz file.

See Alsodatabase,file-per-table,general tablespace,ibdata file,.ibz file,innodb_file_per_table,MySQL Enterprise Backup,system tablespace.

When theMySQL Enterprise Backup product performs acompressed backup, it transforms eachtablespace file that is created using thefile-per-table setting from a.ibd extension to a.ibz extension.

The compression applied during backup is distinct from thecompressed 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.

See Alsocompressed backup,compressed row format,file-per-table,.ibd file,MySQL Enterprise Backup,tablespace.

Seedisk-bound.

The set of files managed byInnoDB within a MySQL database: thesystem tablespace,file-per-table tablespace files, andredo log files. Depending on MySQL version andInnoDB configuration, may also includegeneral tablespace,temporary tablespace, andundo tablespace files. This term is sometimes used in detailed discussions ofInnoDB file structures and formats to refer to the set of files managed byInnoDB within a MySQL database.

See Alsodatabase,file-per-table,general tablespace,redo log,system tablespace,temporary tablespace,undo tablespace.

A supplemental backup file created by theMySQL Enterprise Backup product during ahot backup operation. It contains information about any data changes that occurred while the backup was running. The initial backup files, includingibbackup_logfile, are known as araw backup, because the changes that occurred during the backup operation are not yet incorporated. After you perform theapply step to the raw backup files, the resulting files do include those final data changes, and are known as aprepared backup. At this stage, theibbackup_logfile file is no longer necessary.

See Alsoapply,hot backup,MySQL Enterprise Backup,prepared backup,raw backup.

A set of files with names such asibdata1,ibdata2, and so on, that make up theInnoDBsystem tablespace. For information about the structures and data that reside in the system tablespaceibdata files, seeSection 17.6.3.1, “The System Tablespace”.

Growth of theibdata files is influenced by theinnodb_autoextend_increment configuration option.

See Alsochange buffer,data dictionary,doublewrite buffer,file-per-table,.ibd file,innodb_file_per_table,system tablespace,undo log.

TheInnoDBtemporary tablespacedata file for non-compressedInnoDBtemporary 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. Ifinnodb_temp_data_file_path is not specified, the default behavior is to create a single auto-extending 12MB data file namedibtmp1 in the data directory, alongsideibdata1.

See Alsodata files,temporary table,temporary tablespace.

A set of files, typically namedib_logfile0 andib_logfile1, that form theredo log. Also sometimes referred to as thelog group. These files record statements that attempt to change data inInnoDB tables. These statements are replayed automatically to correct data written by incomplete transactions, on startup following a crash.

This data cannot be used for manual recovery; for that type of operation, use thebinary log.

See Alsobinary log,log group,redo log.

Within anInnoDBFULLTEXT index, the data structure consisting of a document ID and positional information for a token (that is, a particular word).

See AlsoFULLTEXT index.

A row lock thatInnoDB acquires to ensure consistency, without you specifically requesting it.

See Alsorow lock.

A type of database system that maintains data in memory, to avoid overhead due to disk I/O and translation between disk blocks and memory areas. Some in-memory databases sacrifice durability (the“D” in theACID design philosophy) and are vulnerable to hardware, power, and other types of failures, making them more suitable for read-only operations. Other in-memory databases do use durability mechanisms such as logging changes to disk or using non-volatile memory.

MySQL features that address the same kinds of memory-intensive processing include theInnoDBbuffer pool,adaptive hash index, andread-only transaction optimization, theMEMORY storage engine, theMyISAM key cache, and the MySQL query cache.

See AlsoACID,adaptive hash index,buffer pool,disk-based,read-only transaction.

A type ofhot backup, performed by theMySQL Enterprise Backup product, that only saves data changed since some point in time. Having a full backup and a succession of incremental backups lets you reconstruct backup data over a long period, without the storage overhead of keeping several full backups on hand. You can restore the full backup and then apply each of the incremental backups in succession, or you can keep the full backup up-to-date by applying each incremental backup to it, then perform a single restore operation.

The granularity of changed data is at thepage level. A page might actually cover more than one row. Each changed page is included in the backup.

See Alsohot backup,MySQL Enterprise Backup,page.

A data structure that provides a fast lookup capability forrows of atable, typically by forming a tree structure (B-tree) representing all the values of a particularcolumn or set of columns.

InnoDB tables always have aclustered index representing theprimary key. They can also have one or moresecondary indexes defined on one or more columns. Depending on their structure, secondary indexes can be classified aspartial,column, orcomposite indexes.

Indexes are a crucial aspect ofquery performance. Database architects design tables, queries, and indexes to allow fast lookups for data needed by applications. The ideal database design uses acovering index where practical; the query results are computed entirely from the index, without reading the actual table data. Eachforeign key constraint also requires an index, to efficiently check whether values exist in both theparent andchild tables.

Although a B-tree index is the most common, a different kind of data structure is used forhash indexes, as in theMEMORY storage engine and theInnoDBadaptive hash index.R-tree indexes are used for spatial indexing of multi-dimensional information.

See Alsoadaptive hash index,B-tree,child table,clustered index,column index,composite index,covering index,foreign key,hash index,parent table,partial index,primary key,query,R-tree,row,secondary index,table.

A memory area that holds the token data forInnoDBfull-text search. It buffers the data to minimize disk I/O when data is inserted or updated in columns that are part of aFULLTEXT index. The token data is written to disk when the index cache becomes full. EachInnoDBFULLTEXT index has its own separate index cache, whose size is controlled by the configuration optioninnodb_ft_cache_size.

See Alsofull-text search,FULLTEXT index.

Index condition pushdown (ICP) is an optimization that pushes part of aWHERE condition down to the storage engine if parts of the condition can be evaluated using fields from theindex. ICP can reduce the number of times thestorage engine must access the base table and the number of times the MySQL server must access the storage engine. For more information, seeSection 10.2.1.6, “Index Condition Pushdown Optimization”.

See Alsoindex,storage engine.

Extended SQL syntax for overriding theindexes recommended by the optimizer. For example, theFORCE INDEX,USE INDEX, andIGNORE INDEX clauses. Typically used when indexed columns have unevenly distributed values, resulting in inaccuratecardinality estimates.

See Alsocardinality,index.

In anindex that applies to multiple columns (known as acomposite index), the initial or leading columns of the index. A query that references the first 1, 2, 3, and so on columns of a composite index can use the index, even if the query does not reference all the columns in the index.

See Alsocomposite index,index.

Seestatistics.

Apseudo-record in anindex, representing thegap below the smallest value in that index. If a transaction has a statement such asSELECT ... FROM ... WHERE col < 10 FOR UPDATE;, and the smallest value in the column is 5, it is a lock on the infimum record that prevents other transactions from inserting even smaller values such as 0, -10, and so on.

See Alsogap,index,pseudo-record,supremum record.

The name of thedatabase that provides a query interface to the MySQLdata dictionary. (This name is defined by the ANSI SQL standard.) To examine information (metadata) about the database, you can query tables such asINFORMATION_SCHEMA.TABLES andINFORMATION_SCHEMA.COLUMNS, rather than usingSHOW commands that produce unstructured output.

TheINFORMATION_SCHEMA database also contains tables specific toInnoDB that provide a query interface to theInnoDB data dictionary. You use these tables not to see how the database is structured, but to get real-time information about the workings ofInnoDB tables to help with performance monitoring, tuning, and troubleshooting.

See Alsodata dictionary,database,InnoDB.

A MySQL component that combines high performance withtransactional capability for reliability, robustness, and concurrent access. It embodies theACID design philosophy. Represented as astorage engine; it handles tables created or altered with theENGINE=INNODB clause. SeeChapter 17,The InnoDB Storage Engine for architectural details and administration procedures, andSection 10.5, “Optimizing for InnoDB Tables” for performance advice.

In MySQL 5.5 and higher,InnoDB is the default storage engine for new tables and theENGINE=INNODB clause is not required.

InnoDB tables are ideally suited forhot backups. SeeSection 32.1, “MySQL Enterprise Backup Overview” for information about theMySQL Enterprise Backup product for backing up MySQL servers without interrupting normal processing.

See AlsoACID,hot backup,MySQL Enterprise Backup,storage engine,transaction.

Theinnodb_autoinc_lock_mode option controls the algorithm used forauto-increment locking. When you have an auto-incrementingprimary key, you can use statement-based replication only with the settinginnodb_autoinc_lock_mode=1. This setting is known asconsecutive lock mode, because multi-row inserts within a transaction receive consecutive auto-increment values. If you haveinnodb_autoinc_lock_mode=2, which allows higher concurrency for insert operations, use row-based replication rather than statement-based replication. This setting is known asinterleaved lock mode, because multiple multi-row insert statements running at the same time can receiveauto-increment values that are interleaved. The settinginnodb_autoinc_lock_mode=0 should not be used except for compatibility purposes.

Consecutive lock mode (innodb_autoinc_lock_mode=1) is the default setting prior to MySQL 8.0.3. As of MySQL 8.0.3, interleaved lock mode (innodb_autoinc_lock_mode=2) is the default, which reflects the change from statement-based to row-based replication as the default replication type.

See Alsoauto-increment,auto-increment locking,mixed-mode insert,primary key.

An important configuration option that affects many aspects ofInnoDB file storage, availability of features, and I/O characteristics. In MySQL 5.6.7 and higher, it is enabled by default. Theinnodb_file_per_table option turns onfile-per-table mode. With this mode enabled, a newly createdInnoDB table and associated indexes can be stored in a file-per-table.ibd file, outside thesystem tablespace.

This option affects the performance and storage considerations for a number of SQL statements, such asDROP TABLE andTRUNCATE TABLE.

Enabling theinnodb_file_per_table option allows you to take advantage of features such as tablecompression and named-table backups inMySQL Enterprise Backup.

For more information, seeinnodb_file_per_table, andSection 17.6.3.2, “File-Per-Table Tablespaces”.

See Alsocompression,file-per-table,.ibd file,MySQL Enterprise Backup,system tablespace.

Theinnodb_lock_wait_timeout option sets the balance betweenwaiting for shared resources to become available, or giving up and handling the error, retrying, or doing alternative processing in your application. Rolls back anyInnoDB transaction that waits more than a specified time to acquire alock. Especially useful ifdeadlocks are caused by updates to multiple tables controlled by different storage engines; such deadlocks are notdetected automatically.

See Alsodeadlock,deadlock detection,lock,wait.

Theinnodb_strict_mode option controls whetherInnoDB operates instrict mode, where conditions that are normally treated as warnings, cause errors instead (and the underlying statements fail).

See Alsostrict mode.

Innovation releases with the same major version form an Innovation series. For example, MySQL 8.1 through 8.3 form the MySQL 8 Innovation series.

See AlsoLTS Series.

One of the primaryDML operations inSQL. The performance of inserts is a key factor indata warehouse systems that load millions of rows into tables, andOLTP systems where many concurrent connections might insert rows into the same table, in arbitrary order. If insert performance is important to you, you should learn aboutInnoDB features such as theinsert buffer used inchange buffering, andauto-increment columns.

See Alsoauto-increment,change buffering,data warehouse,DML,InnoDB,insert buffer,OLTP,SQL.

The former name of thechange buffer. In MySQL 5.5, support was added for buffering changes to secondary index pages forDELETE andUPDATE operations. Previously, only changes resulting fromINSERT operations were buffered. The preferred term is nowchange buffer.

See Alsochange buffer,change buffering.

The technique of storing changes to secondary index pages, resulting fromINSERT operations, in thechange buffer rather than writing the changes immediately, so that the physical writes can be performed to minimize random I/O. It is one of the types ofchange buffering; the others aredelete buffering andpurge buffering.

Insert buffering is not used if the secondary index isunique, because the uniqueness of new values cannot be verified before the new entries are written out. Other kinds of change buffering do work for unique indexes.

See Alsochange buffer,change buffering,delete buffering,insert buffer,purge buffering,unique index.

A type ofgap lock that is set byINSERT operations prior to row insertion. This type oflock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. For more information, seeSection 17.7.1, “InnoDB Locking”.

See Alsogap lock,lock,next-key lock.

A singlemysqld daemon managing adata directory representing one or moredatabases with a set oftables. It is common in development, testing, and somereplication scenarios to have multiple instances on the sameserver machine, each managing its own data directory and listening on its own port or socket. With one instance running adisk-bound workload, the server might still have extra CPU and memory capacity to run additional instances.

See Alsodata directory,database,disk-bound,mysqld,replication,server,table.

Modifications at the source code level to collect performance data for tuning and debugging. In MySQL, data collected by instrumentation is exposed through an SQL interface using theINFORMATION_SCHEMA andPERFORMANCE_SCHEMA databases.

See AlsoINFORMATION_SCHEMA,Performance Schema.

Seeintention lock.

A kind oflock that applies to the table, used to indicate the kind of lock thetransaction intends to acquire on rows in the table. Different transactions can acquire different kinds of intention locks on the same table, but the first transaction to acquire anintention exclusive (IX) lock on a table prevents other transactions from acquiring any S or X locks on the table. Conversely, the first transaction to acquire anintention shared (IS) lock on a table prevents other transactions from acquiring any X locks on the table. The two-phase process allows the lock requests to be resolved in order, without blocking locks and corresponding operations that are compatible. For more information about this locking mechanism, seeSection 17.7.1, “InnoDB Locking”.

See Alsolock,lock mode,locking,transaction.

Seeintention lock.

Code for instrumenting or debugging some aspect of an application, which can be enabled without recompiling or changing the source of the application itself.

See Alsocommand interceptor,Connector/J,Connector/NET,exception interceptor.

An optimized internalInnoDB temporary table used by theoptimizer.

See Alsooptimizer.

A data structure optimized for document retrieval systems, used in the implementation ofInnoDBfull-text search. TheInnoDBFULLTEXT index, implemented as an inverted index, records the position of each word within a document, rather than the location of a table row. A single column value (a document stored as a text string) is represented by many entries in the inverted index.

See Alsofull-text search,FULLTEXT index,ilist.

Acronym forI/O operations per second. A common measurement for busy systems, particularlyOLTP applications. If this value is near the maximum that the storage devices can handle, the application can becomedisk-bound, limitingscalability.

See Alsodisk-bound,OLTP,scalability.

One of the foundations of database processing. Isolation is theI in the acronymACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multipletransactions are making changes and performing queries at the same time.

From highest amount of consistency and protection to the least, the isolation levels supported by InnoDB are:SERIALIZABLE,REPEATABLE READ,READ COMMITTED, andREAD UNCOMMITTED.

WithInnoDB tables, many users can keep the default isolation level (REPEATABLE READ) for all operations. Expert users might choose theREAD COMMITTED level as they push the boundaries of scalability withOLTP processing, or during data warehousing operations where minor inconsistencies do not affect the aggregate results of large amounts of data. The levels on the edges (SERIALIZABLE andREAD UNCOMMITTED) change the processing behavior to such an extent that they are rarely used.

See AlsoACID,OLTP,READ COMMITTED,READ UNCOMMITTED,REPEATABLE READ,SERIALIZABLE,transaction.

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, seehttp://www.oracle.com/technetwork/java/javaee/overview/index.html. With MySQL applications, you typically useConnector/J for database access, and an application server such asTomcat orJBoss to handle the middle-tier work, and optionally a framework such asSpring. Database-related features often offered within a J2EE stack include aconnection pool andfailover support.

See Alsoconnection pool,Connector/J,failover,Java,JBoss,Spring,Tomcat.

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 toC andC++ developers. To write Java applications with MySQL, you use theJDBC driver known asConnector/J.

See AlsoC,Connector/J,C++,JDBC.

See AlsoJ2EE.

Abbreviation for“Java Database Connectivity”, anAPI for database access fromJava applications. Java developers writing MySQL applications use theConnector/J component as their JDBC driver.

See AlsoAPI,Connector/J,J2EE,Java.

See AlsoJava.

Aquery that retrieves data from more than one table, by referencing columns in the tables that hold identical values. Ideally, these columns are part of anInnoDBforeign key relationship, which ensuresreferential integrity and that the join columns areindexed. Often used to save space and improve query performance by replacing repeated strings with numeric IDs, in anormalized data design.

See Alsoforeign key,index,normalized,query,referential integrity.

Seekey distribution center.

In Kerberos, the key distribution center comprises an authentication server (AS) and a ticket-granting server (TGS).

See Alsoauthentication server,ticket-granting ticket.

See AlsoSSL.

An option to specify the size of data pages within anInnoDB table that usescompressed row format. The default is 8 kilobytes. Lower values risk hitting internal limits that depend on the combination of row size and compression percentage.

ForMyISAM tables,KEY_BLOCK_SIZE optionally specifies the size in bytes to use for index key blocks. The value is treated as a hint; a different size could be used if necessary. AKEY_BLOCK_SIZE value specified for an individual index definition overrides a table-levelKEY_BLOCK_SIZE value.

See Alsocompressed row format.

A lightweight structure used byInnoDB to implement alock for its own internal memory structures, typically held for a brief time measured in milliseconds or microseconds. A general term that includes bothmutexes (for exclusive access) andrw-locks (for shared access). Certain latches are the focus ofInnoDB performance tuning. Statistics about latch use and contention are available through thePerformance Schema interface.

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

Informal name for thelibmysqlclient library.

See Alsolibmysqlclient.

The library file, namedlibmysqlclient.a orlibmysqlclient.so, that is typically linked intoclient programs written inC. Sometimes known informally aslibmysql or themysqlclient library.

See Alsoclient,libmysql,mysqlclient.

Thisembedded MySQL server library makes it possible to run a full-featured MySQL server inside aclient application. The main benefits are increased speed and more simple management for embedded applications. You link with thelibmysqld library rather thanlibmysqlclient. The API is identical between all three of these libraries.

See Alsoclient,embedded,libmysql,libmysqlclient.

A type ofinterceptor supported byConnector/J. It involves implementing the interfacecom.mysql.jdbc.ConnectionLifecycleInterceptor.

See AlsoConnector/J,interceptor.

TheInnoDBbuffer pool is represented as a list of memorypages. The list is reordered as new pages are accessed and enter the buffer pool, as pages within the buffer pool are accessed again and are considered newer, and as pages that are not accessed for a long time areevicted from the buffer pool. The buffer pool is divided intosublists, and the replacement policy is a variation of the familiarLRU technique.

See Alsobuffer pool,eviction,LRU,page,sublist.

A technique for scaling read-only connections by sending query requests to different slave servers in a replication or Cluster configuration. WithConnector/J, load balancing is enabled through thecom.mysql.jdbc.ReplicationDriver class and controlled by the configuration propertyloadBalanceStrategy.

See AlsoConnector/J,J2EE.

See Alsoconnection.

The high-level notion of an object that controls access to a resource, such as a table, row, or internal data structure, as part of alocking strategy. For intensive performance tuning, you might delve into the actual structures that implement locks, such asmutexes andlatches.

See Alsolatch,lock mode,locking,mutex.

An operation used in some database systems that converts manyrow locks into a singletable lock, saving memory space but reducing concurrent access to the table.InnoDB uses a space-efficient representation for row locks, so thatlock escalation is not needed.

See Alsolocking,row lock,table lock.

A shared (S)lock allows atransaction to read a row. Multiple transactions can acquire an S lock on that same row at the same time.

An exclusive (X) lock allows a transaction to update or delete a row. No other transaction can acquire any kind of lock on that same row at the same time.

Intention locks apply to the table, and are used to indicate what kind of lock the transaction intends to acquire on rows in the table. Different transactions can acquire different kinds of intention locks on the same table, but the first transaction to acquire an intention exclusive (IX) lock on a table prevents other transactions from acquiring any S or X locks on the table. Conversely, the first transaction to acquire an intention shared (IS) lock on a table prevents other transactions from acquiring any X locks on the table. The two-phase process allows the lock requests to be resolved in order, without blocking locks and corresponding operations that are compatible.

See Alsointention lock,lock,locking,transaction.

The system of protecting atransaction from seeing or changing data that is being queried or changed by other transactions. Thelocking strategy must balance reliability and consistency of database operations (the principles of theACID philosophy) against the performance needed for goodconcurrency. Fine-tuning the locking strategy often involves choosing anisolation level and ensuring all your database operations are safe and reliable for that isolation level.

See AlsoACID,concurrency,isolation level,locking,transaction.

ASELECT statement that also performs alocking operation on anInnoDB table. EitherSELECT ... FOR UPDATE orSELECT ... LOCK IN SHARE MODE. It has the potential to produce adeadlock, depending on theisolation level of the transaction. The opposite of anon-locking read. Not allowed for global tables in aread-only transaction.

SELECT ... FOR SHARE replacesSELECT ... LOCK IN SHARE MODE in MySQL 8.0.1, butLOCK IN SHARE MODE remains available for backward compatibility.

SeeSection 17.7.2.4, “Locking Reads”.

See Alsodeadlock,isolation level,locking,non-locking read,read-only transaction.

In theInnoDB context,“log” or“log files” typically refers to theredo log represented by theib_logfileN files. Another type ofInnoDB log is theundo log, which is a storage area that holds copies of data modified by active transactions.

Other kinds of logs that are important in MySQL are theerror log (for diagnosing startup and runtime problems),binary log (for working with replication and performing point-in-time restores), thegeneral query log (for diagnosing application problems), and theslow query log (for diagnosing performance problems).

See Alsobinary log,error log,general query log,ib_logfile,redo log,slow query log,undo log.

The memory area that holds data to be written to thelog files that make up theredo log. It is controlled by theinnodb_log_buffer_size configuration option.

See Alsolog file,redo log.

One of theib_logfileN files that make up theredo log. Data is written to these files from thelog buffer memory area.

See Alsoib_logfile,log buffer,redo log.

The set of files that make up theredo log, typically namedib_logfile0 andib_logfile1. (For that reason, sometimes referred to collectively asib_logfile.)

See Alsoib_logfile,redo log.

A type of operation that involves high-level, abstract aspects such as tables, queries, indexes, and other SQL concepts. Typically, logical aspects are important to make database administration and application development convenient and usable. Contrast withphysical.

See Alsological backup,physical.

Abackup that reproduces table structure and data, without copying the actual data files. For example, themysqldump command produces a logical backup, because its output contains statements such asCREATE TABLE andINSERT that can re-create the data. Contrast withphysical backup. A logical backup offers flexibility (for example, you could edit table definitions or insert statements before restoring), but can take substantially longer torestore than a physical backup.

See Alsobackup,mysqldump,physical backup,restore.

A prefix added toInnoDB configuration options after serverstartup, so any new configuration options not recognized by the current level of MySQL do not cause a startup failure. MySQL processes configuration options that start with this prefix, but gives a warning rather than a failure if the part after the prefix is not a recognized option.

See Alsostartup.

A value representing a lower limit, typically a threshold value at which some corrective action begins or becomes more aggressive. Contrast withhigh-water mark.

See Alsohigh-water mark.

An acronym for“least recently used”, a common method for managing storage areas. The items that have not been used recently areevicted when space is needed to cache newer items.InnoDB uses the LRU mechanism by default to manage thepages within thebuffer pool, but makes exceptions in cases where a page might be read only a single time, such as during afull table scan. This variation of the LRU algorithm is called themidpoint insertion strategy. For more information, seeSection 17.5.1, “Buffer Pool”.

See Alsobuffer pool,eviction,full table scan,midpoint insertion strategy,page.

Acronym for“log sequence number”. This arbitrary, ever-increasing value represents a point in time corresponding to operations recorded in theredo log. (This point in time is regardless oftransaction boundaries; it can fall in the middle of one or more transactions.) It is used internally byInnoDB duringcrash recovery and for managing thebuffer pool.

Prior to MySQL 5.6.3, the LSN was a 4-byte unsigned integer. The LSN became an 8-byte unsigned integer in MySQL 5.6.3 when the redo log file size limit increased from 4GB to 512GB, as additional bytes were required to store extra size information. Applications built on MySQL 5.6.3 or later that use LSN values should use 64-bit rather than 32-bit variables to store and compare LSN values.

In theMySQL Enterprise Backup product, you can specify an LSN to represent the point in time from which to take anincremental backup. The relevant LSN is displayed by the output of themysqlbackup command. Once you have the LSN corresponding to the time of a full backup, you can specify that value to take a subsequent incremental backup, whose output contains another LSN for the next incremental backup.

See Alsobuffer pool,crash recovery,incremental backup,MySQL Enterprise Backup,redo log,transaction.

LTS releases with the same major version number form an LTS series. For example, all MySQL 8.4.x releases form the MySQL 8.4 LTS series.

Note: MySQL 8.0 is a Bugfix series that preceded the LTS release model.

See AlsoInnovation Series.

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

See AlsoMySQL Enterprise Backup,mysqlbackup command.

A file that MySQL uses to store data for aMyISAM table.

See Also.MYI file,MySQL Enterprise Backup,mysqlbackup command.

A file that MySQL uses to store indexes for aMyISAM table.

See Also.MYD file,MySQL Enterprise Backup,mysqlbackup command.

Seesource.

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

To improveconcurrency, sometimes actions are moved from the master thread to separate background threads. For example, in MySQL 5.6 and higher,dirty pages areflushed from thebuffer pool by thepage cleaner thread rather than the master thread.

See Alsobuffer pool,change buffer,concurrency,dirty page,flush,page cleaner,thread.

Acronym for“metadata lock”.

See Alsometadata lock.

Synonym forpartial trust. Because the range of trust settings is so broad,“partial trust” is preferred, to avoid the implication that there are only three levels (low, medium, and full).

See AlsoConnector/NET,partial trust.

A popular component of many MySQL andNoSQL software stacks, allowing fast reads and writes for single values and caching the results entirely in memory. Traditionally, applications required extra logic to write the same data to a MySQL database for permanent storage, or to read data from a MySQL database when it was not cached yet in memory. Now, applications can use the simplememcached protocol, supported by client libraries for many languages, to communicate directly with MySQL servers usingInnoDB orNDB tables. These NoSQL interfaces to MySQL tables allow applications to achieve higher read and write performance than by issuing SQL statements directly, and can simplify application logic and deployment configurations for systems that already incorporatememcached for in-memory caching.

See AlsoNoSQL.

To apply changes to data cached in memory, such as when a page is brought into thebuffer pool, and any applicable changes recorded in thechange buffer are incorporated into the page in the buffer pool. The updated data is eventually written to thetablespace by theflush mechanism.

See Alsobuffer pool,change buffer,flush,tablespace.

A type oflock that preventsDDL operations on a table that is being used at the same time by anothertransaction. For details, seeSection 10.11.4, “Metadata Locking”.

Enhancements toonline operations, particularly in MySQL 5.6 and higher, are focused on reducing the amount of metadata locking. The objective is for DDL operations that do not change the table structure (such asCREATE INDEX andDROP INDEX forInnoDB tables) to proceed while the table is being queried, updated, and so on by other transactions.

See AlsoDDL,lock,online,transaction.

A feature implemented by theINNODB_METRICS table in theINFORMATION_SCHEMA, in MySQL 5.6 and higher. You can querycounts and totals for low-levelInnoDB operations, and use the results for performance tuning in combination with data from thePerformance Schema.

See Alsocounter,INFORMATION_SCHEMA,Performance Schema.

The technique of initially bringingpages into theInnoDBbuffer pool not at the“newest” end of the list, but instead somewhere in the middle. The exact location of this point can vary, based on the setting of theinnodb_old_blocks_pct option. The intent is that pages that are only read once, such as during afull table scan, can be aged out of the buffer pool sooner than with a strictLRU algorithm. For more information, seeSection 17.5.1, “Buffer Pool”.

See Alsobuffer pool,full table scan,LRU,page.

An internal phase ofInnoDB processing, when making changes at thephysical level to internal data structures duringDML operations. A mini-transaction (mtr) has no notion ofrollback; multiple mini-transactions can occur within a singletransaction. Mini-transactions write information to theredo log that is used duringcrash recovery. A mini-transaction can also happen outside the context of a regular transaction, for example duringpurge processing by background threads.

See Alsocommit,crash recovery,DML,physical,purge,redo log,rollback,transaction.

AnINSERT statement whereauto-increment values are specified for some but not all of the new rows. For example, a multi-valueINSERT could specify a value for the auto-increment column in some cases andNULL in other cases.InnoDB generates auto-increment values for the rows where the column value was specified asNULL. Another example is anINSERT ... ON DUPLICATE KEY UPDATE statement, where auto-increment values might be generated but not used, for any duplicate rows that are processed asUPDATE rather thanINSERT statements.

Can cause consistency issues betweensource andreplica servers in areplication configuration. Can require adjusting the value of theinnodb_autoinc_lock_mode configuration option.

See Alsoauto-increment,innodb_autoinc_lock_mode,replica,replication,source.

An older JDBC driver for MySQL that evolved intoConnector/J when it was integrated with the MySQL product.

See AlsoConnector/J.

An Open Source framework developed by Novell, that works withConnector/NET andC# applications on Linux platforms.

See AlsoConnector/NET,C#.

Seemini-transaction.

A type of processor that can take advantage of multithreaded programs, such as the MySQL server.

SeeMVCC.

Informal abbreviation for“mutex variable”. (Mutex itself is short for“mutual exclusion”.) The low-level object thatInnoDB uses to represent and enforce exclusive-accesslocks to internal in-memory data structures. Once the lock is acquired, any other process, thread, and so on is prevented from acquiring the same lock. Contrast withrw-locks, whichInnoDB uses to represent and enforce shared-accesslocks to internal in-memory data structures. Mutexes and rw-locks are known collectively aslatches.

See Alsolatch,lock,Performance Schema,Pthreads,rw-lock.

Acronym for“multiversion concurrency control”. This technique letsInnoDBtransactions with certainisolation levels performconsistent read operations; that is, to query rows that are being updated by other transactions, and see the values from before those updates occurred. This is a powerful technique to increaseconcurrency, by allowing queries to proceed without waiting due tolocks held by the other transactions.

This technique is not universal in the database world. Some other database products, and some other MySQL storage engines, do not support it.

See AlsoACID,concurrency,consistent read,isolation level,lock,transaction.

The name, on Unix or Linux systems, of the MySQLoption file.

See Alsomy.ini,option file.

The name, on Windows systems, of the MySQLoption file.

See Alsomy.cnf,option file.

Obsolete name forConnector/ODBC.

See AlsoConnector/ODBC.

Themysql program is the command-line interpreter for the MySQL database. It processesSQL statements, and also MySQL-specific commands such asSHOW TABLES, by passing requests to themysqld daemon.

See Alsomysqld,SQL.

A command-line tool of theMySQL Enterprise Backup product. It performs ahot backup operation forInnoDB tables, and awarm backup forMyISAM and other kinds of tables. SeeSection 32.1, “MySQL Enterprise Backup Overview” for more information about this command.

See Alsohot backup,MySQL Enterprise Backup,warm backup.

The informal name for the library that is implemented by the filelibmysqlclient, with extension.a or.so.

See Alsolibmysqlclient.

mysqld, also known as MySQL Server, is a single multithreaded program that does most of the work in a MySQL installation. It does not spawn additional processes. MySQL Server manages access to the MySQL data directory that contains databases, tables, and other information such as log files and status files.

mysqld runs as a Unix daemon or Windows service, constantly waiting for requests and performing maintenance work in the background.

See Alsoinstance,mysql.

The name of the open-sourcePython module that forms the basis of the MySQLPython API.

See AlsoPython,Python API.

A command that performs alogical backup of some combination of databases, tables, and table data. The results are SQL statements that reproduce the original schema objects, data, or both. For substantial amounts of data, aphysical backup solution such asMySQL Enterprise Backup is faster, particularly for therestore operation.

See Alsological backup,MySQL Enterprise Backup,physical backup,restore.

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

Synonym forlibmysqlclient.

See Alsolibmysql.

An indexed column, typically aprimary key, where the values have some real-world significance. Usually advised against because:

Thus, it is typically better to use arbitrary numeric values to form asynthetic key, for example using anauto-increment column.

See Alsoauto-increment,clustered index,primary key,secondary index,synthetic key.

Anypage in the sameextent as a particular page. When a page is selected to beflushed, any neighbor pages that aredirty are typically flushed as well, as an I/O optimization for traditional hard disks. In MySQL 5.6 and up, this behavior can be controlled by the configuration variableinnodb_flush_neighbors; you might turn that setting off for SSD drives, which do not have the same overhead for writing smaller batches of data at random locations.

See Alsodirty page,extent,flush,page.

A combination of arecord lock on the index record and agap lock on the gap before the index record.

See Alsogap lock,locking,record lock.

Aquery that does not use theSELECT ... FOR UPDATE orSELECT ... LOCK IN SHARE MODE clauses. The only kind of query allowed for global tables in aread-only transaction. The opposite of alocking read. SeeSection 17.7.2.3, “Consistent Nonlocking Reads”.

SELECT ... FOR SHARE replacesSELECT ... LOCK IN SHARE MODE in MySQL 8.0.1, butLOCK IN SHARE MODE remains available for backward compatibility.

See Alsolocking read,query,read-only transaction.

The situation when a query retrieves data, and a later query within the sametransaction retrieves what should be the same data, but the queries return different results (changed by another transaction committing in the meantime).

This kind of operation goes against theACID principle of database design. Within a transaction, data should be consistent, with predictable and stable relationships.

Among differentisolation levels, non-repeatable reads are prevented by theserializable read andrepeatable read levels, and allowed by theconsistent read, andread uncommitted levels.

See AlsoACID,consistent read,isolation level,READ UNCOMMITTED,REPEATABLE READ,SERIALIZABLE,transaction.

An industry term that means the same asasynchronous I/O.

See Alsoasynchronous I/O.

A database design strategy where data is split into multiple tables, and duplicate values condensed into single rows represented by an ID, to avoid storing, querying, and updating redundant or lengthy values. It is typically used inOLTP applications.

For example, an address might be given a unique ID, so that a census database could represent the relationshiplives at this address by associating that ID with each member of a family, rather than storing multiple copies of a complex value such as123 Main Street, Anytown, USA.

For another example, although a simple address book application might store each phone number in the same table as a person's name and address, a phone company database might give each phone number a special ID, and store the numbers and IDs in a separate table. This normalized representation could simplify large-scale updates when area codes split apart.

Normalization is not always recommended. Data that is primarily queried, and only updated by deleting entirely and reloading, is often kept in fewer, larger tables with redundant copies of duplicate values. This data representation is referred to asdenormalized, and is frequently found in data warehousing applications.

See Alsodenormalized,foreign key,OLTP,relational.

A broad term for a set of data access technologies that do not use theSQL language as their primary mechanism for reading and writing data. Some NoSQL technologies act as key-value stores, only accepting single-value reads and writes; some relax the restrictions of theACID methodology; still others do not require a pre-plannedschema. MySQL users can combine NoSQL-style processing for speed and simplicity with SQL operations for flexibility and convenience, by using thememcached API to directly access some kinds of MySQL tables.

See AlsoACID,memcached,schema,SQL.

A type ofconstraint that specifies that acolumn cannot contain anyNULL values. It helps to preservereferential integrity, as the database server can identify data with erroneous missing values. It also helps in the arithmetic involved in query optimization, allowing the optimizer to predict the number of entries in an index on that column.

See Alsocolumn,constraint,NULL,primary key,referential integrity.

A special value inSQL, indicating the absence of data. Any arithmetic operation or equality test involving aNULL value, in turn produces aNULL result. (Thus it is similar to the IEEE floating-point concept of NaN,“not a number”.) Any aggregate calculation such asAVG() ignores rows withNULL values, when determining how many rows to divide by. The only test that works withNULL values uses the SQL idiomsIS NULL orIS NOT NULL.

NULL values play a part inindex operations, because for performance a database must minimize the overhead of keeping track of missing data values. Typically,NULL values are not stored in an index, because a query that tests an indexed column using a standard comparison operator could never match a row with aNULL value for that column. For the same reason, unique indexes do not preventNULL values; those values simply are not represented in the index. Declaring aNOT NULL constraint on a column provides reassurance that there are no rows left out of the index, allowing for better query optimization (accurate counting of rows and estimation of whether to use the index).

Because theprimary key must be able to uniquely identify every row in the table, a single-column primary key cannot contain anyNULL values, and a multi-column primary key cannot contain any rows withNULL values in all columns.

Although the Oracle database allows aNULL value to be concatenated with a string,InnoDB treats the result of such an operation asNULL.

See Alsoindex,primary key,SQL.

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

See AlsoMySQL Enterprise Backup,mysqlbackup command.

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 calledConnector/ODBC.

See AlsoConnector/ODBC.

A column containing variable-length data (such asBLOB andVARCHAR) that is too long to fit on aB-tree page. The data is stored inoverflow pages. TheDYNAMIC row format is more efficient for such storage than the olderCOMPACT row format.

See AlsoB-tree,compact row format,dynamic row format,overflow page.

Acronym for“Online Transaction Processing”. A database system, or a database application, that runs a workload with manytransactions, with frequent writes as well as reads, typically affecting small amounts of data at a time. For example, an airline reservation system or an application that processes bank deposits. The data might be organized innormalized form for a balance betweenDML (insert/update/delete) efficiency andquery efficiency. Contrast withdata warehouse.

With itsrow-level locking andtransactional capability,InnoDB is the ideal storage engine for MySQL tables used in OLTP applications.

See Alsodata warehouse,DML,InnoDB,query,row lock,transaction.

A type of operation that involves no downtime, blocking, or restricted operation for the database. Typically applied toDDL. Operations that shorten the periods of restricted operation, such asfast index creation, have evolved into a wider set ofonline DDL operations in MySQL 5.6.

In the context of backups, ahot backup is an online operation and awarm backup is partially an online operation.

See AlsoDDL,Fast Index Creation,hot backup,online DDL,warm backup.

A feature that improves the performance, concurrency, and availability ofInnoDB tables duringDDL (primarilyALTER TABLE) operations. SeeSection 17.12, “InnoDB and Online DDL” for details.

The details vary according to the type of operation. In some cases, the table can be modified concurrently while theALTER TABLE is in progress. The operation might be able to be performed without a table copy, or using a specially optimized type of table copy. DML log space usage for in-place operations is controlled by theinnodb_online_alter_log_max_size configuration option.

This feature is an enhancement of theFast Index Creation feature in MySQL 5.5.

See AlsoDDL,Fast Index Creation,online.

A methodology that guides low-level implementation decisions for a relational database system. The requirements of performance andconcurrency in a relational database mean that operations must be started or dispatched quickly. The requirements of consistency andreferential integrity mean that any operation could fail: a transaction might be rolled back, aDML operation could violate a constraint, a request for a lock could cause a deadlock, a network error could cause a timeout. An optimistic strategy is one that assumes most requests or attempts succeed, so that relatively little work is done to prepare for the failure case. When this assumption is true, the database does little unnecessary work; when requests do fail, extra work must be done to clean up and undo changes.

InnoDB uses optimistic strategies for operations such aslocking andcommits. For example, data changed by a transaction can be written to the data files before the commit occurs, making the commit itself very fast, but requiring more work to undo the changes if the transaction is rolled back.

The opposite of an optimistic strategy is apessimistic one, where a system is optimized to deal with operations that are unreliable and frequently unsuccessful. This methodology is rare in a database system, because so much care goes into choosing reliable hardware, networks, and algorithms.

See Alsocommit,concurrency,DML,locking,pessimistic,referential integrity.

The MySQL component that determines the bestindexes andjoin order to use for aquery, based on characteristics and data distribution of the relevanttables.

See Alsoindex,join,query,table.

A configuration parameter for MySQL, either stored in theoption file or passed on the command line.

For theoptions that apply toInnoDB tables, each option name starts with the prefixinnodb_.

See AlsoInnoDB,option,option file.

The file that holds the configurationoptions for the MySQL instance. Traditionally, on Linux and Unix this file is namedmy.cnf, and on Windows it is namedmy.ini.

See Alsoconfiguration file,my.cnf,my.ini,option.

Separately allocated diskpages that hold variable-length columns (such asBLOB andVARCHAR) that are too long to fit on aB-tree page. The associated columns are known asoff-page columns.

See AlsoB-tree,off-page column,page.

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

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

See AlsoMySQL Enterprise Backup,mysqlbackup command.

A unit representing how much dataInnoDB transfers at any one time between disk (thedata files) and memory (thebuffer pool). A page can contain one or morerows, 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 usecompressed 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.

WhenInnoDB reads or writes sets of pages as a batch to increase I/O throughput, it reads or writes anextent at a time.

All theInnoDB disk data structures within a MySQL instance share the samepage size.

See Alsobuffer pool,compact row format,compressed row format,data files,extent,page size,row.

AnInnoDB backgroundthread thatflushesdirty pages from thebuffer pool. Prior to MySQL 5.6, this activity was performed by themaster thread. The number of page cleaner threads is controlled by theinnodb_page_cleaners configuration option, introduced in MySQL 5.7.4.

See Alsobuffer pool,dirty page,flush,master thread,thread.

For releases up to and including MySQL 5.5, the size of eachInnoDBpage 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 anInnoDBinstance can be either 4KB, 8KB, or 16KB, controlled by theinnodb_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 allInnoDBtablespaces, including thesystem tablespace,file-per-table tablespaces, andgeneral tablespaces.

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

See Alsodisk-bound,file-per-table,general tablespace,instance,OLTP,page,SSD,system tablespace,tablespace.

The table in aforeign key relationship that holds the initial column values pointed to from thechild table. The consequences of deleting, or updating rows in the parent table depend on theON UPDATE andON 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 toNULL, or the operation could be prevented.

See Alsochild table,foreign key.

Abackup that contains some of thetables in a MySQL database, or some of the databases in a MySQL instance. Contrast withfull backup.

See Alsobackup,full backup,table.

Anindex that represents only part of a column value, typically the first N characters (theprefix) of a longVARCHAR value.

See Alsoindex,index prefix.

An execution environment typically used by hosting providers, where applications have some permissions but not others. For example, applications might be able to access a database server over a network, but be“sandboxed” with regard to reading and writing local files.

See AlsoConnector/NET.

Theperformance_schema schema, in MySQL 5.5 and up, presents a set of tables that you can query to get detailed information about the performance characteristics of many internal parts of the MySQL server. SeeChapter 29,MySQL Performance Schema.

See AlsoINFORMATION_SCHEMA,latch,mutex,rw-lock.

A programming language with roots in Unix scripting and report generation. Incorporates high-performance regular expressions and file I/O. Large collection of reusable modules available through repositories such as CPAN.

See AlsoPerl API.

An open-sourceAPI for MySQL applications written in thePerl language. Implemented through theDBI andDBD::mysql modules. For details, seeSection 31.9, “MySQL Perl API”.

See AlsoAPI,Perl.

A feature that storesindex statistics forInnoDBtables on disk, providing betterplan stability forqueries. For more information, seeSection 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.

See Alsoindex,optimizer,plan stability,query,table.

A methodology that sacrifices performance or concurrency in favor of safety. It is appropriate if a high proportion of requests or attempts might fail, or if the consequences of a failed request are severe.InnoDB uses what is known as a pessimisticlocking strategy, to minimize the chance ofdeadlocks. At the application level, you might avoid deadlocks by using a pessimistic strategy of acquiring all locks needed by a transaction at the very beginning.

Many built-in database mechanisms use the oppositeoptimistic methodology.

See Alsodeadlock,locking,optimistic.

A row that appears in the result set of a query, but not in the result set of an earlier query. For example, if a query is run twice within atransaction, and in the meantime, another transaction commits after inserting a new row or updating a row so that it matches theWHERE clause of the query.

This occurrence is known as a phantom read. It is harder to guard against than anon-repeatable read, because locking all the rows from the first query result set does not prevent the changes that cause the phantom to appear.

Among differentisolation levels, phantom reads are prevented by theserializable read level, and allowed by therepeatable read,consistent read, andread uncommitted levels.

See Alsoconsistent read,isolation level,non-repeatable read,READ UNCOMMITTED,REPEATABLE READ,SERIALIZABLE,transaction.

A programming language originating with web applications. The code is typically embedded as blocks within the source of a web page, with the output substituted into the page as it is transmitted by the web server. This is in contrast to applications such as CGI scripts that print output in the form of an entire web page. The PHP style of coding is used for highly interactive and dynamic web pages. Modern PHP programs can also be run as command-line or GUI applications.

MySQL applications are written using one of thePHP APIs. Reusable modules can be written inC and called from PHP.

Another technology for writing server-side web pages with MySQL isASP.net.

See AlsoASP.net,C,PHP API.

SeveralAPIs are available for writing MySQL applications in thePHP language: the original MySQL API (Mysql) the MySQL Improved Extension (Mysqli) the MySQL Native Driver (Mysqlnd) the MySQL functions (PDO_MYSQL), and Connector/PHP. For details, seeMySQL and PHP.

See AlsoAPI,PHP.

A type of operation that involves hardware-related aspects such as disk blocks, memory pages, files, bits, disk reads, and so on. Typically, physical aspects are important during expert-level performance tuning and problem diagnosis. Contrast withlogical.

See Alsological,physical backup.

Abackup that copies the actual data files. For example, themysqlbackup command of theMySQL Enterprise Backup product produces a physical backup, because its output contains data files that can be used directly by themysqld server, resulting in a fasterrestore operation. Contrast withlogical backup.

See Alsobackup,logical backup,MySQL Enterprise Backup,restore.

Acronym forpoint-in-time recovery.

See Alsopoint-in-time recovery.

A property of aquery execution plan, where the optimizer makes the same choices each time for a givenquery, so that performance is consistent and predictable.

See Alsoquery,query execution plan.

The process of restoring abackup to recreate the state of the database at a specific date and time. Commonly abbreviated“PITR”. Because it is unlikely that the specified time corresponds exactly to the time of a backup, this technique usually requires a combination of aphysical backup and alogical backup. For example, with theMySQL Enterprise Backup product, you restore the last backup that you took before the specified point in time, then replay changes from thebinary log between the time of the backup and the PITR time.

See Alsobackup,binary log,logical backup,MySQL Enterprise Backup,physical backup.

The number of the TCP/IP socket the database server listens on, used to establish aconnection. Often specified in conjunction with ahost. Depending on your use of network encryption, there might be one port for unencrypted traffic and another port forSSL connections.

See Alsoconnection,host,SSL.

Seeindex prefix.

A set of backup files, produced by theMySQL Enterprise Backup product, after all the stages of applyingbinary logs andincremental backups are finished. The resulting files are ready to berestored. Prior to the apply steps, the files are known as araw backup.

See Alsobinary log,hot backup,incremental backup,MySQL Enterprise Backup,raw backup,restore.

An SQL statement that is analyzed in advance to determine an efficient execution plan. It can be executed multiple times, without the overhead for parsing and analysis each time. Different values can be substituted for literals in theWHERE clause each time, through the use of placeholders. This substitution technique improves security, protecting against some kinds of SQL injection attacks. You can also reduce the overhead for converting and copying return values to program variables.

Although you can use prepared statements directly through SQL syntax, the variousConnectors have programming interfaces for manipulating prepared statements, and these APIs are more efficient than going through SQL.

See Alsoclient-side prepared statement,connector,server-side prepared statement.

A set of columns—and by implication, the index based on this set of columns—that can uniquely identify every row in a table. As such, it must be a unique index that does not contain anyNULL values.

InnoDB requires that every table has such an index (also called theclustered index orcluster index), and organizes the table storage based on the column values of the primary key.

When choosing primary key values, consider using arbitrary values (asynthetic key) rather than relying on values derived from some other source (anatural key).

See Alsoclustered index,index,natural key,synthetic key.

The Kerberos term for a named entity, such as a user or server.

See Alsoservice principal name,user principal name.

An instance of an executing program. The operating system switches between multiple running processes, allowing for a certain degree ofconcurrency. On most operating systems, processes can contain multiplethreads of execution that share resources. Context-switching between threads is faster than the equivalent switching between processes.

See Alsoconcurrency,thread.

An artificial record in an index, used forlocking key values or ranges that do not currently exist.

See Alsoinfimum record,locking,supremum record.

The POSIX threads standard, which defines an API for threading and locking operations on Unix and Linux systems. On Unix and Linux systems,InnoDB uses this implementation formutexes.

See Alsomutex.

A type of garbage collection performed by one or more separate background threads (controlled byinnodb_purge_threads) that runs on a periodic schedule. Purge parses and processesundo log pages from thehistory list for the purpose of removing clustered and secondary index records that were marked for deletion (by previousDELETE statements) and are no longer required forMVCC orrollback. Purge frees undo log pages from the history list after processing them.

See Alsohistory list,MVCC,rollback,undo log.

The technique of storing changes to secondary index pages, resulting fromDELETE operations, in thechange buffer rather than writing the changes immediately, so that the physical writes can be performed to minimize random I/O. (Because delete operations are a two-step process, this operation buffers the write that normally purges an index record that was previously marked for deletion.) It is one of the types ofchange buffering; the others areinsert buffering anddelete buffering.

See Alsochange buffer,change buffering,delete buffering,insert buffer,insert buffering.

Another name for theInnoDBhistory list. Related to theinnodb_max_purge_lag configuration option.

See Alsohistory list,purge.

Athread within theInnoDB process that is dedicated to performing the periodicpurge operation. In MySQL 5.6 and higher, multiple purge threads are enabled by theinnodb_purge_threads configuration option.

See Alsopurge,thread.

A programming language used in a broad range of fields, from Unix scripting to large-scale applications. Includes runtime typing, built-in high-level data types, object-oriented features, and an extensive standard library. Often used as a“glue” language between components written in other languages. The MySQLPython API is the open-sourceMySQLdb module.

See AlsoMySQLdb,Python API.

See AlsoAPI,Python.

InSQL, an operation that reads information from one or moretables. Depending on the organization of data and the parameters of the query, the lookup might be optimized by consulting anindex. If multiple tables are involved, the query is known as ajoin.

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

See AlsoDDL,DML,index,join,SQL,table.

The set of decisions made by the optimizer about how to perform aquery most efficiently, including whichindex or indexes to use, and the order in which tojoin tables.Plan stability involves the same choices being made consistently for a given query.

See Alsoindex,join,plan stability,query.

Seegeneral query log.

To reduce the amount of database activity, often in preparation for an operation such as anALTER TABLE, abackup, or ashutdown. Might or might not involve doing as muchflushing as possible, so thatInnoDB does not continue doing background I/O.

In MySQL 5.6 and higher, the syntaxFLUSH TABLES ... FOR EXPORT writes some data to disk forInnoDB tables that make it simpler to back up those tables by copying the data files.

See Alsobackup,flush,InnoDB,shutdown.

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

See AlsoB-tree.

Acronym for“Redundant Array of Inexpensive Drives”. Spreading I/O operations across multiple drives enables greaterconcurrency at the hardware level, and improves the efficiency of low-level write operations that otherwise would be performed in sequence.

See Alsoconcurrency.

A technique for quickly estimating the number of different values in a column (the column'scardinality).InnoDB samples pages at random from the index and uses that data to estimate the number of different values.

See Alsocardinality.

The initial set of backup files produced by theMySQL Enterprise Backup product, before the changes reflected in thebinary log and anyincremental backups are applied. At this stage, the files are not ready torestore. After these changes are applied, the files are known as aprepared backup.

See Alsobinary log,hot backup,ibbackup_logfile,incremental backup,MySQL Enterprise Backup,prepared backup,restore.

Anisolation level that uses alocking strategy that relaxes some of the protection betweentransactions, in the interest of performance. Transactions cannot see uncommitted data from other transactions, but they can see data that is committed by another transaction after the current transaction started. Thus, a transaction never sees any bad data, but the data that it does see may depend to some extent on the timing of other transactions.

When a transaction with this isolation level performsUPDATE ... WHERE orDELETE ... WHERE operations, other transactions might have to wait. The transaction can performSELECT ... FOR UPDATE, andLOCK IN SHARE MODE operations without making other transactions wait.

SELECT ... FOR SHARE replacesSELECT ... LOCK IN SHARE MODE in MySQL 8.0.1, butLOCK IN SHARE MODE remains available for backward compatibility.

See AlsoACID,isolation level,locking,REPEATABLE READ,SERIALIZABLE,transaction.

Phenomena such asdirty reads,non-repeatable reads, andphantom reads which can occur when a transaction reads data that another transaction has modified.

See Alsodirty read,non-repeatable read,phantom.

Theisolation level that provides the least amount of protection between transactions. Queries employ alocking strategy that allows them to proceed in situations where they would normally wait for another transaction. However, this extra performance comes at the cost of less reliable results, including data that has been changed by other transactions and not committed yet (known asdirty read). Use this isolation level with great caution, and be aware that the results might not be consistent or reproducible, depending on what other transactions are doing at the same time. Typically, transactions with this isolation level only do queries, not insert, update, or delete operations.

See AlsoACID,dirty read,isolation level,locking,transaction.

An internal snapshot used by theMVCC mechanism ofInnoDB. Certaintransactions, depending on theirisolation level, see the data values as they were at the time the transaction (or in some cases, the statement) started. Isolation levels that use a read view areREPEATABLE READ,READ COMMITTED, andREAD UNCOMMITTED.

See Alsoisolation level,MVCC,READ COMMITTED,READ UNCOMMITTED,REPEATABLE READ,transaction.

A type of I/O request that prefetches a group ofpages (an entireextent) into thebuffer pool asynchronously, in case these pages are needed soon. The linear read-ahead technique prefetches all the pages of one extent based on access patterns for pages in the preceding extent. The random read-ahead technique prefetches all the pages for an extent once a certain number of pages from the same extent are in the buffer pool. Random read-ahead is not part of MySQL 5.5, but is re-introduced in MySQL 5.6 under the control of theinnodb_random_read_ahead configuration option.

See Alsobuffer pool,extent,page.

A type oftransaction that can be optimized forInnoDB tables by eliminating some of the bookkeeping involved with creating aread view for each transaction. Can only performnon-locking read queries. It can be started explicitly with the syntaxSTART TRANSACTION READ ONLY, or automatically under certain conditions. SeeSection 10.5.3, “Optimizing InnoDB Read-Only Transactions” for details.

See Alsonon-locking read,read view,transaction.

Alock on an index record. For example,SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value oft.c1 is 10. Contrast withgap lock andnext-key lock.

See Alsogap lock,lock,next-key lock.

The data, in units of records, recorded in theredo log whenDML statements make changes toInnoDB tables. It is used duringcrash recovery to correct data written by incompletetransactions. The ever-increasingLSN value represents the cumulative amount of redo data that has passed through the redo log.

See Alsocrash recovery,DML,LSN,redo log,transaction.

A disk-based data structure used duringcrash recovery, to correct data written by incompletetransactions. During normal operation, it encodes requests to changeInnoDB table data, which result from SQL statements or low-level API calls. Modifications that did not finish updating thedata files before an unexpectedshutdown are replayed automatically.

The redo log is physically represented on disk as a set of redo log files. Redo log data is encoded in terms of records affected; this data is collectively referred to asredo. The passage of data through the redo log is represented by an ever-increasingLSN value.

For more information, seeSection 17.6.5, “Redo Log”

See Alsocrash recovery,data files,ib_logfile,log buffer,LSN,redo,shutdown,transaction.

AnInnoDB feature that, when enabled, sequentially writes redo log records to an archive file to avoid potential loss of data than can occur when a backup utility fails to keep pace with redo log generation while a backup operation is in progress. For more information, seeRedo Log Archiving.

See Alsoredo log.

The oldestInnoDBrow format. Prior to MySQL 5.0.3, it was the only row format available inInnoDB. From MySQL 5.0.3 to MySQL 5.7.8, the default row format isCOMPACT. As of MySQL 5.7.9, the default row format is defined by theinnodb_default_row_format configuration option, which has a default setting ofDYNAMIC. You can still specify theREDUNDANT row format for compatibility with olderInnoDB tables.

For more information, seeSection 17.10, “InnoDB Row Formats”.

See Alsocompact row format,dynamic row format,row format.

The technique of maintaining data always in a consistent format, part of theACID philosophy. In particular, data in different tables is kept consistent through the use offoreign key constraints, which can prevent changes from happening or automatically propagate those changes to all related tables. Related mechanisms include theunique constraint, which prevents duplicate values from being inserted by mistake, and theNOT NULL constraint, which prevents blank values from being inserted by mistake.

See AlsoACID,FOREIGN KEY constraint,NOT NULL constraint,unique constraint.

An important aspect of modern database systems. The database server encodes and enforces relationships such as one-to-one, one-to-many, many-to-one, and uniqueness. For example, a person might have zero, one, or many phone numbers in an address database; a single phone number might be associated with several family members. In a financial database, a person might be required to have exactly one taxpayer ID, and any taxpayer ID could only be associated with one person.

The database server can use these relationships to prevent bad data from being inserted, and to find efficient ways to look up information. For example, if a value is declared to be unique, the server can stop searching as soon as the first match is found, and it can reject attempts to insert a second copy of the same value.

At the database level, these relationships are expressed through SQL features such ascolumns within a table, unique andNOT NULLconstraints,foreign keys, and different kinds of join operations. Complex relationships typically involve data split between more than one table. Often, the data isnormalized, so that duplicate values in one-to-many relationships are stored only once.

In a mathematical context, the relations within a database are derived from set theory. For example, theOR andAND operators of aWHERE clause represent the notions of union and intersection.

See AlsoACID,column,constraint,foreign key,normalized.

In thefull-text search feature, a number signifying the similarity between the search string and the data in theFULLTEXT index. For example, when you search for a single word, that word is typically more relevant for a row where it occurs several times in the text than a row where it appears only once.

See Alsofull-text search,FULLTEXT index.

The defaultisolation level forInnoDB. It prevents any rows that are queried from being changed by othertransactions, thus blockingnon-repeatable reads but notphantom reads. It uses a moderately strictlocking strategy so that all queries within a transaction see data from the same snapshot, that is, the data as it was at the time the transaction started.

When a transaction with this isolation level performsUPDATE ... WHERE,DELETE ... WHERE,SELECT ... FOR UPDATE, andLOCK IN SHARE MODE operations, other transactions might have to wait.

SELECT ... FOR SHARE replacesSELECT ... LOCK IN SHARE MODE in MySQL 8.0.1, butLOCK IN SHARE MODE remains available for backward compatibility.

See AlsoACID,consistent read,isolation level,locking,phantom,transaction.

Repertoire is a term applied to character sets. A character set repertoire is the collection of characters in the set. SeeSection 12.2.1, “Character Set Repertoire”.

A databaseserver machine in areplication topology that receives changes from another server (thesource) and applies those same changes. Thus it maintains the same contents as the source, although it might lag somewhat behind.

In MySQL, replicas are commonly used in disaster recovery, to take the place of a source that fails. They are also commonly used for testing software upgrades and new settings, to ensure that database configuration changes do not cause problems with performance or reliability.

Replicas typically have high workloads, because they process all theDML (write) operations relayed from the source, as well as user queries. To ensure that replicas can apply changes from the source fast enough, they frequently have fast I/O devices and sufficient CPU and memory to run multiple database instances on the same server. For example, the source might use hard drive storage while the replicas useSSDs.

See AlsoDML,replication,server,source,SSD.

The practice of sending changes from asource, to one or morereplicas, so that all databases have the same data. This technique has a wide range of uses, such as load-balancing for better scalability, disaster recovery, and testing software upgrades and configuration changes. The changes can be sent between the databases by methods calledrow-based replication andstatement-based replication.

See Alsoreplica,row-based replication,source,statement-based replication.

The process of putting a set of backup files from theMySQL Enterprise Backup product in place for use by MySQL. This operation can be performed to fix a corrupted database, to return to some earlier point in time, or (in areplication context) to set up a newreplica. In theMySQL Enterprise Backup product, this operation is performed by thecopy-back option of themysqlbackup command.

See Alsohot backup,MySQL Enterprise Backup,mysqlbackup command,prepared backup,replica,replication.

ASQL statement that ends atransaction, undoing any changes made by the transaction. It is the opposite ofcommit, which makes permanent any changes made in the transaction.

By default, MySQL uses theautocommit setting, which automatically issues a commit following each SQL statement. You must change this setting before you can use the rollback technique.

See AlsoACID,autocommit,commit,SQL,transaction.

The storage area containing theundo logs. Rollback segments have traditionally resided in thesystem tablespace. As of MySQL 5.6, rollback segments can reside inundo tablespaces. As of MySQL 5.7, rollback segments are also allocated to theglobal temporary tablespace.

See Alsoglobal temporary tablespace,system tablespace,undo log,undo tablespace.

The logical data structure defined by a set ofcolumns. A set of rows makes up atable. WithinInnoDBdata files, eachpage can contain one or more rows.

AlthoughInnoDB uses the termrow format for consistency with MySQL syntax, the row format is a property of each table and applies to all rows in that table.

See Alsocolumn,data files,page,row format,table.

The disk storage format forrows of anInnoDBtable. AsInnoDB gains new capabilities such ascompression, new row formats are introduced to support the resulting improvements in storage efficiency and performance.

The row format of anInnoDB table is specified by theROW_FORMAT option or by theinnodb_default_row_format configuration option (introduced in MySQL 5.7.9). Row formats includeREDUNDANT,COMPACT,COMPRESSED, andDYNAMIC. To view the row format of anInnoDB table, issue theSHOW TABLE STATUS statement or queryInnoDB table metadata in theINFORMATION_SCHEMA.

See Alsocompact row format,compressed row format,compression,dynamic row format,redundant row format,row,table.

Alock that prevents a row from being accessed in an incompatible way by anothertransaction. Other rows in the same table can be freely written to by other transactions. This is the type oflocking done byDML operations onInnoDB tables.

Contrast withtable locks used byMyISAM, or duringDDL operations onInnoDB tables that cannot be done withonline DDL; those locks block concurrent access to the table.

See AlsoDDL,DML,InnoDB,lock,locking,online DDL,table lock,transaction.

A form ofreplication where events are propagated from thesource specifying how to change individual rows on thereplica. It is safe to use for all settings of theinnodb_autoinc_lock_mode option.

See Alsoauto-increment locking,innodb_autoinc_lock_mode,replica,replication,source,statement-based replication.

Thelocking mechanism used forInnoDB tables, relying onrow locks rather thantable locks. Multipletransactions can modify the same table concurrently. Only if two transactions try to modify the same row does one of the transactions wait for the other to complete (and release its row locks).

See AlsoInnoDB,locking,row lock,table lock,transaction.

A programming language that emphasizes dynamic typing and object-oriented programming. Some syntax is familiar toPerl developers.

See AlsoAPI,Perl,Ruby API.

mysql2, based based on thelibmysqlclient API library, is available for Ruby programmers developing MySQL applications. For more information, seeSection 31.11, “MySQL Ruby APIs”.

See Alsolibmysql,Ruby.

The low-level object thatInnoDB uses to represent and enforce shared-accesslocks to internal in-memory data structures following certain rules. Contrast withmutexes, whichInnoDB uses to represent and enforce exclusive access to internal in-memory data structures. Mutexes and rw-locks are known collectively aslatches.

rw-lock types includes-locks (shared locks),x-locks (exclusive locks), andsx-locks (shared-exclusive locks).

The following matrix summarizes rw-lock type compatibility.

See Alsolatch,lock,mutex,Performance Schema.

Savepoints help to implement nestedtransactions. 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 mightroll back the changes involved in booking that one leg, without rolling back the earlier flights that were successfully booked.

See Alsorollback,transaction.

The ability to add more work and issue more simultaneous requests to a system, without a sudden drop in performance due to exceeding the limits of system capacity. Software architecture, hardware configuration, application coding, and type of workload all play a part in scalability. When the system reaches its maximum capacity, popular techniques for increasing scalability arescale up (increasing the capacity of existing hardware or software) andscale out (adding new servers and more instances of MySQL). Often paired withavailability as critical aspects of a large-scale deployment.

See Alsoavailability,scale out,scale up.

A technique for increasingscalability by adding new servers and more instances of MySQL. For example, setting up replication, NDB Cluster, connection pooling, or other features that spread work across a group of servers. Contrast withscale up.

See Alsoscalability,scale up.

A technique for increasingscalability by increasing the capacity of existing hardware or software. For example, increasing the memory on a server and adjusting memory-related parameters such asinnodb_buffer_pool_size andinnodb_buffer_pool_instances. Contrast withscale out.

See Alsoscalability,scale out.

Conceptually, a schema is a set of interrelated database objects, such as tables, table columns, data types of the columns, indexes, foreign keys, and so on. These objects are connected through SQL syntax, because the columns make up the tables, the foreign keys refer to tables and columns, and so on. Ideally, they are also connected logically, working together as part of a unified application or flexible framework. For example, theINFORMATION_SCHEMA andperformance_schema databases use“schema” in their names to emphasize the close relationships between the tables and columns they contain.

In MySQL, physically, aschema is synonymous with adatabase. You can substitute the keywordSCHEMA instead ofDATABASE in MySQL SQL syntax, for example usingCREATE SCHEMA instead ofCREATE DATABASE.

Some other database products draw a distinction. For example, in the Oracle Database product, aschema represents only a part of a database: the tables and other objects owned by a single user.

See Alsodatabase,INFORMATION_SCHEMA,Performance Schema.

Acronym for“serialized dictionary information”.

See Alsoserialized dictionary information (SDI).

In MySQL,full-text search queries use a special kind of index, theFULLTEXT index. In MySQL 5.6.4 and up,InnoDB andMyISAM tables both supportFULLTEXT indexes; formerly, these indexes were only available forMyISAM tables.

See Alsofull-text search,FULLTEXT index.

A type ofInnoDBindex that represents a subset of table columns. AnInnoDB table can have zero, one, or many secondary indexes. (Contrast with theclustered index, which is required for eachInnoDB table, and stores the data for all the table columns.)

A secondary index can be used to satisfy queries that only require values from the indexed columns. For more complex queries, it can be used to identify the relevant rows in the table, which are then retrieved through lookups using the clustered index.

Creating and dropping secondary indexes has traditionally involved significant overhead from copying all the data in theInnoDB table. Thefast index creation feature makes bothCREATE INDEX andDROP INDEX statements much faster forInnoDB secondary indexes.

See Alsoclustered index,Fast Index Creation,index.

A division within anInnoDBtablespace. If a tablespace is analogous to a directory, the segments are analogous to files within that directory. A segment can grow. New segments can be created.

For example, within afile-per-table tablespace, table data is in one segment and each associated index is in its own segment. Thesystem tablespace contains many different segments, because it can hold many tables and their associated indexes. Prior to MySQL 8.0, the system tablespace also includes one or morerollback segments used forundo logs.

Segments grow and shrink as data is inserted and deleted. When a segment needs more room, it is extended by oneextent (1 megabyte) at a time. Similarly, a segment releases one extent's worth of space when all the data in that extent is no longer needed.

See Alsoextent,file-per-table,rollback segment,system tablespace,tablespace,undo log.

A property of data distribution, the number of distinct values in a column (itscardinality) divided by the number of records in the table. High selectivity means that the column values are relatively unique, and can retrieved efficiently through an index. If you (or the query optimizer) can predict that a test in aWHERE clause only matches a small number (or proportion) of rows in a table, the overallquery tends to be efficient if it evaluates that test first, using an index.

See Alsocardinality,query.

A type of read operation used forUPDATE statements, that is a combination ofREAD COMMITTED andconsistent read. When anUPDATE statement examines a row that is already locked,InnoDB returns the latest committed version to MySQL so that MySQL can determine whether the row matches theWHERE condition of theUPDATE. If the row matches (must be updated), MySQL reads the row again, and this timeInnoDB either locks it or waits for a lock on it. This type of read operation can only happen when the transaction has the READ COMMITTEDisolation level.

See Alsoconsistent read,isolation level,READ COMMITTED.

Theisolation level that uses the most conservative locking strategy, to prevent any othertransactions from inserting or changing data that was read by this transaction, until it is finished. This way, the same query can be run over and over within a transaction, and be certain to retrieve the same set of results each time. Any attempt to change data that was committed by another transaction since the start of the current transaction, cause the current transaction to wait.

This is the default isolation level specified by the SQL standard. In practice, this degree of strictness is rarely needed, so the default isolation level forInnoDB is the next most strict,REPEATABLE READ.

See AlsoACID,consistent read,isolation level,locking,REPEATABLE READ,transaction.

Dictionary object metadata in serialized form. SDI is stored inJSON format.

As of MySQL 8.0.3, SDI is present in allInnoDB tablespace files except for temporary tablespace and undo tablespace files. The presence of SDI in tablespace files provides metadata redundancy. For example, dictionary object metadata can be extracted from tablespace files using theibd2sdi utility if the data dictionary becomes unavailable.

For aMyISAM table, SDI is stored in a.sdi metadata file in the schema directory. An SDI metadata file is required to perform anIMPORT TABLE operation.

See Alsofile-per-table,general tablespace,system tablespace,tablespace.

A type of program that runs continuously, waiting to receive and act upon requests from another program (theclient). Because often an entire computer is dedicated to running one or more server programs (such as a database server, a web server, an application server, or some combination of these), the termserver can also refer to the computer that runs the server software.

See Alsoclient,mysqld.

Aprepared statement managed by the MySQL server. Historically, issues with server-side prepared statements ledConnector/J andConnector/PHP developers to sometimes useclient-side prepared statements instead. With modern MySQL server versions, server-side prepared statements are recommended for performance, scalability, and memory efficiency.

See Alsoclient-side prepared statement,Connector/J,Connector/PHP,prepared statement.

The name for a Kerberos named entity that represents a service.

See Alsoprincipal.

A Kerberos ticket that provides access to an application service, such as the service provided by a web or database server.

See AlsoConnector/J.

Atemporary tablespace that stores user-createdtemporary tables and internal temporary tables created by theoptimizer whenInnoDB is configured as the on-disk storage engine for internal temporary tables.

See Alsooptimizer,temporary table,temporary tablespace.

A kind oflock that allows othertransactions to read the locked object, and to also acquire other shared locks on it, but not to write to it. The opposite ofexclusive lock.

See Alsoexclusive lock,lock,transaction.

Another way of referring to thesystem tablespace or ageneral tablespace. General tablespaces were introduced in MySQL 5.7. More than one table can reside in a shared tablespace. Only a single table can reside in afile-per-table tablespace.

See Alsogeneral tablespace,system tablespace.

The process offlushing to disk alldirty buffer pool pages whose redo entries are contained in certain portion of theredo log. Occurs beforeInnoDB reuses a portion of a log file; the log files are used in a circular fashion. Typically occurs with write-intensiveworkloads.

See Alsodirty page,flush,redo log,workload.

The process of stopping the MySQL server. By default, this process cleans up operations forInnoDB tables, soInnoDB can beslow to shut down, but fast to start up later. If you skip the cleanup operations, it isfast to shut down but the cleanup must be performed during the next restart.

The shutdown mode forInnoDB is controlled by theinnodb_fast_shutdown option.

See Alsofast shutdown,InnoDB,slow shutdown,startup.

Seereplica.

A type oflog used for performance tuning of SQL statements processed by the MySQL server. The log information is stored in a file. You must enable this feature to use it. You control which categories of“slow” SQL statements are logged. For more information, seeSection 7.4.5, “The Slow Query Log”.

See Alsogeneral query log,log.

A type ofshutdown that does additionalInnoDB flushing operations before completing. Also known as aclean shutdown. Specified by the configuration parameterinnodb_fast_shutdown=0 or the commandSET GLOBAL innodb_fast_shutdown=0;. Although the shutdown itself can take longer, that time should be saved on the subsequent startup.

See Alsoclean shutdown,fast shutdown,shutdown.

A representation of data at a particular time, which remains the same even as changes arecommitted by othertransactions. Used by certainisolation levels to allowconsistent reads.

See Alsocommit,consistent read,isolation level,transaction.

The buffer used for sorting data during creation of anInnoDB index. Sort buffer size is configured using theinnodb_sort_buffer_size configuration option.

A database server machine in areplication scenario that processes the initial insert, update, and delete requests for data. These changes are propagated to, and repeated on, other servers known asreplicas.

See Alsoreplica,replication.

An identifier used to uniquely identify anInnoDBtablespace within a MySQL instance. The space ID for thesystem tablespace is always zero; this same ID applies to all tables within the system tablespace or within a general tablespace. Eachfile-per-table tablespace andgeneral tablespace has its own space ID.

Prior to MySQL 5.6, this hardcoded value presented difficulties in movingInnoDB tablespace files between MySQL instances. Starting in MySQL 5.6, you can copy tablespace files between instances by using thetransportable tablespace feature involving the statementsFLUSH TABLES ... FOR EXPORT,ALTER TABLE ... DISCARD TABLESPACE, andALTER TABLE ... IMPORT TABLESPACE. The information needed to adjust the space ID is conveyed in the.cfg file which you copy along with the tablespace. SeeSection 17.6.1.3, “Importing InnoDB Tables” for details.

See Also.cfg file,file-per-table,general tablespace,.ibd file,system tablespace,tablespace,transportable tablespace.

A type of file that uses file system space more efficiently by writing metadata representing empty blocks to disk instead of writing the actual empty space. TheInnoDBtransparent page compression feature relies on sparse file support. For more information, seeSection 17.9.2, “InnoDB Page Compression”.

See Alsohole punching,transparent page compression.

A type ofwait operation that continuously tests whether a resource becomes available. This technique is used for resources that are typically held only for brief periods, where it is more efficient to wait in a“busy loop” than to put the thread to sleep and perform a context switch. If the resource does not become available within a short time, the spin loop ceases and another wait technique is used.

See Alsolatch,lock,mutex,wait.