1Z0-909 flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

caching_sha2_password

Default authentication plugin in MySQL 8.0. Requires secure connection or RSA key exchange for password transmission; clients may need allowPublicKeyRetrieval=true.

JSON_TABLE function

Extracts data from a JSON document and returns a relational table. Requires COLUMNS clause defining column names and types.

Recursive CTE

A common table expression that references itself, defined with WITH RECURSIVE. Consists of anchor member and recursive member separated by UNION ALL.

Window function frame

Defines the set of rows used for window function calculation. Syntax: ROWS BETWEEN start AND end, e.g., ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Generated column

A column whose value is computed from an expression. VIRTUAL columns are computed on read; STORED columns are computed on write and stored on disk.

InnoDB clustered index

The primary key index that stores row data in the leaf pages. Each InnoDB table has exactly one clustered index, usually the primary key.

READ COMMITTED isolation

Each consistent read sees a fresh snapshot of committed data. Non-repeatable reads possible; gap locks disabled for index scans except foreign keys.

REPEATABLE READ isolation

Default isolation level in MySQL. Consistent reads within a transaction see the same snapshot; prevents non-repeatable reads but phantom reads possible.

EXPLAIN ANALYZE

Executes the query and provides actual execution statistics including time and rows, along with the execution plan. Available in MySQL 8.0.18 and later.

utf8mb4 character set

Default character set in MySQL 8.0. Supports 4-byte characters including emojis. Default collation is utf8mb4_0900_ai_ci.

SELECT ... FOR UPDATE

Locks selected rows exclusively, preventing other transactions from modifying or locking them until the current transaction commits or rolls back.

SELECT ... FOR SHARE

Acquires shared locks on selected rows, allowing other transactions to read but not modify the rows until the lock is released.

SAVEPOINT

Creates a named point within a transaction to which you can later roll back using ROLLBACK TO SAVEPOINT, without aborting the entire transaction.

AUTO_INCREMENT

Column attribute that generates sequential integer values. In MySQL 8.0, the counter is persisted across server restarts.

ON DUPLICATE KEY UPDATE

INSERT clause that updates existing rows when a duplicate key conflict occurs. Affected rows count: 1 for insert, 2 for update, 0 for no change.

INSERT ... ON DUPLICATE KEY UPDATE

Combines insert and update: if a row with the same unique or primary key exists, performs an update instead of insert. Returns affected row count.

REPLACE INTO

Deletes the old row if a duplicate key conflict occurs, then inserts the new row. Unlike ON DUPLICATE KEY UPDATE, it removes and reinserts.

Common table expression (CTE)

A named temporary result set defined with WITH clause, usable within a single SELECT, INSERT, UPDATE, or DELETE statement. Can be recursive.

LATERAL derived table

A derived table that can reference columns from preceding tables in the FROM clause. Introduced in MySQL 8.0.14 with LATERAL keyword.

Functional index

An index on an expression, created with CREATE INDEX ... ((expression)). Allows indexing of computed values without generated columns.

Descending index

An index that stores key values in descending order. MySQL 8.0 supports true descending indexes; earlier versions ignored DESC.

Invisible index

An index that is maintained but ignored by the optimizer. Useful for testing index removal. Toggle with ALTER TABLE ... ALTER INDEX ... INVISIBLE.

Histogram statistics

Optimizer statistics for column data distribution, created with ANALYZE TABLE ... UPDATE HISTOGRAM. Helps the optimizer choose better plans for non-indexed columns.

EXPLAIN FORMAT=JSON

Returns the query execution plan in JSON format, providing detailed cost and row estimates for each operation. Does not execute the query.

Lock wait timeout

The time a transaction waits for a lock before giving up. Controlled by innodb_lock_wait_timeout (default 50 seconds). Error 1205 occurs on timeout.

Deadlock detection

InnoDB automatically detects deadlocks and rolls back one transaction (the victim) with error 1213. Controlled by innodb_deadlock_detect.

Transaction isolation levels

Four levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (default), SERIALIZABLE. Set with SET TRANSACTION ISOLATION LEVEL or SET SESSION.

Consistent nonlocking read

A SELECT that reads a snapshot of data without acquiring locks, using MVCC. In REPEATABLE READ, the snapshot is established at first read.

InnoDB buffer pool

Memory area caching table and index data. Size set by innodb_buffer_pool_size. Multiple instances configured with innodb_buffer_pool_instances.

Adaptive hash index

InnoDB feature that automatically builds hash indexes for frequently accessed index pages to speed up lookups. Controlled by innodb_adaptive_hash_index.

Change buffer

InnoDB structure that caches changes to secondary index pages when the pages are not in the buffer pool, reducing random I/O.

MySQL Shell

Advanced client and code editor for MySQL, supporting SQL, JavaScript, and Python modes. Includes utilities for dump and load, and AdminAPI.