MySQL Indexes and Locking Mechanisms: Types, Principles, and Practical Examples

MySQL Indexes

MySQL indexes are data structures maintained outside table data to accelerate query performance. Since queries account for over 80% of database operations, indexing is one of the most effective ways to optimize query efficiency.

Index Classification by Function

Normal Index : Basic index with no restrictions.

Unique Index : Indexed column values must be unique; null values allowed. For composite indexes, the combination must be unique.

Primary Key Index : Special unique index that does not allow null values. Usually created during table creation.

Composite Index : Index created on multiple columns combined.

Foreign Key Index : Only supported by InnoDB; ensures data consistency and integrity, enables cascading operations.

Fulltext Index : For fast full-document matching. Supported by InnoDB since version 5.6; not supported by MEMORY engine.

Index Classification by Structure

B+Tree Index : Most frequently used in MySQL; default for InnoDB and MyISAM.

Hash Index : Default for MEMORY storage engine.

Index Creation and Deletion Syntax

-- Create index
CREATE [UNIQUE|FULLTEXT] INDEX index_name [USING index_type] ON table_name(column...);
-- Default index type is B+TREE

-- Create table with primary key index
CREATE TABLE test(
    id INT PRIMARY KEY AUTO_INCREMENT,
    data1 VARCHAR(100),
    data2 INT
);

-- Create normal index on register_time
CREATE INDEX idx_register_time ON login_user(register_time);

-- Create unique index on user_name
CREATE UNIQUE INDEX udx_user_name ON login_user(user_name);

-- View indexes
SHOW INDEX FROM login_user;

-- Add indexes via ALTER TABLE
ALTER TABLE table_name ADD INDEX index_name(column);
ALTER TABLE table_name ADD UNIQUE index_name(column);
ALTER TABLE table_name ADD PRIMARY KEY(column);
ALTER TABLE table_name ADD INDEX index_name(column1, column2, ...);
ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent_table(pk_col);
ALTER TABLE table_name ADD FULLTEXT index_name(column);

-- Example: fulltext index on address
ALTER TABLE login_user ADD FULLTEXT fdx_address(address);

-- Drop index
DROP INDEX index_name ON table_name;
DROP INDEX fdx_address ON login_user;

Composite Index Leftmost Prefix Principle

When creating a composite index, MySQL follows the leftmost prefix principle: queries must include the leftmost column of the index to utilize it. The optimizer automatically reorders WHERE clause columns to match the index.

Example: Composite index idx_login(user_name, mobile, email) effectively creates three indexes: (user_name), (user_name, mobile), (user_name, mobile, email).

-- All these queries hit the index because they include user_name (leftmost)
SELECT * FROM login_user WHERE email='[email protected]' AND mobile='158xxxx2108' AND user_name='乔豆豆';
SELECT * FROM login_user WHERE user_name='候胖胖' AND mobile='134xxxx4820';
SELECT * FROM login_user WHERE user_name='蔺赞赞';
SELECT * FROM login_user WHERE email='[email protected]' AND user_name='任肥肥';

-- This query does NOT use the index (missing user_name)
SELECT * FROM login_user WHERE mobile='134xxxx6559' AND email='[email protected]';

Index Usage Best Practices

Create indexes on tables with high query frequency and large data volume.

Prefer unique indexes for columns with high cardinality; higher selectivity improves efficiency.

Choose index columns from WHERE clause conditions; pick the most frequently used and best filtering columns.

Use columns with shorter storage length to improve I/O efficiency; more index entries fit in a storage block.

More indexes are not always better: indexes consume disk space and require maintenance on INSERT/UPDATE/DELETE, increasing overhead and potentially degrading overall performance.

MySQL Locking Mechanisms

Transaction Isolation Levels

Common databases (Oracle, SQL Server, MySQL) support four isolation levels, each with different concurrency anomalies:

Read Uncommitted : Dirty reads, non-repeatable reads, phantom reads all possible.

Read Committed (Oracle/SQL Server default): No dirty reads; non-repeatable reads and phantom reads possible.

Repeatable Read (MySQL default): No dirty reads, no non-repeatable reads; phantom reads possible.

Serializable : No anomalies; but all operations serialize, lowest concurrency.

Higher isolation means better safety but lower efficiency. Generally avoid Read Uncommitted (unsafe) and Serializable (poor performance). Do not change default isolation level unless necessary. To change MySQL isolation level:

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Client must reconnect for changes to take effect

Concurrency Anomalies

Dirty Read : Reading uncommitted data from another transaction, causing inconsistent results.

Non-Repeatable Read : Reading committed modifications from another transaction, causing inconsistent results.

Phantom Read : Query returns no rows, but INSERT finds row exists (insert fails); or DELETE succeeds on seemingly non-existent rows (accidental deletion).

MySQL's default Repeatable Read only risks phantom reads. Solution: use locking mechanisms in critical scenarios (e.g., financial transactions, data migration).

Lock Classifications

By Operation

Read Lock (Shared Lock) : Multiple transactions can read simultaneously; no modifications allowed.

Write Lock (Exclusive Lock) : Blocks other transactions from reading (with lock) or writing until released.

By Granularity

Table Lock : Locks entire table. Low overhead, fast locking, no deadlocks; high contention, lowest concurrency.

Row Lock : Locks specific rows. Higher overhead, slower locking, possible deadlocks; fine granularity, low contention, high concurrency.

Storage Engine Lock Support

MyISAM: Table locks only.

InnoDB: Both table and row locks (default row lock).

MEMORY: Table locks only.

By Usage Strategy

Pessimistic Lock : Assumes conflicts; locks data on read. All row/table/read/write locks are pessimistic.

Optimistic Lock : Assumes no conflicts; no lock on read. On update, checks if data changed (via version or flag). Not provided natively; must be implemented manually.

InnoDB Lock Operations

InnoDB defaults to row locks; if WHERE condition cannot use an index, it escalates to table lock.

Shared Lock (Read Lock)

SELECT ... LOCK IN SHARE MODE;
-- Characteristics: multiple transactions can read, none can modify

Demonstration with employee table (id PK, name, money; only id indexed):

-- Session 1
START TRANSACTION;
SELECT * FROM employee WHERE id=1 LOCK IN SHARE MODE;
SELECT * FROM employee WHERE name='侯胖胖' LOCK IN SHARE MODE;
-- Do not COMMIT yet

-- Session 2
START TRANSACTION;
SELECT * FROM employee WHERE id=1; -- succeeds (plain read)
SELECT * FROM employee WHERE id=1 LOCK IN SHARE MODE; -- succeeds (shared + shared compatible)
UPDATE employee SET name='蔺赞赞' WHERE id=2; -- succeeds (different row, row lock)
UPDATE employee SET name='任肥肥' WHERE id=1; -- blocks (shared lock prevents write)
UPDATE employee SET money=30000 WHERE name='乔豆豆'; -- blocks (name not indexed -> table lock, conflicts with row lock on id=1)
COMMIT;

Exclusive Lock (Write Lock)

SELECT ... FOR UPDATE;
-- Characteristics: locked data cannot be read with lock or modified by others

-- Session 1
START TRANSACTION;
SELECT * FROM employee WHERE id=1 FOR UPDATE;
-- Do not COMMIT

-- Session 2
START TRANSACTION;
SELECT * FROM employee WHERE id=1; -- succeeds (plain read)
SELECT * FROM employee WHERE id=1 LOCK IN SHARE MODE; -- blocks (exclusive vs shared conflict)
SELECT * FROM employee WHERE id=1 FOR UPDATE; -- blocks (exclusive vs exclusive conflict)
UPDATE employee SET name='天蓬' WHERE id=1; -- blocks (exclusive prevents write)
COMMIT;

Lock Compatibility Summary

Shared + Shared: Compatible

Shared + Exclusive: Conflict

Exclusive + Exclusive: Conflict

Exclusive + Shared: Conflict

MyISAM Lock Operations

MyISAM and MEMORY only support table locks and do not support transactions.

Table Read Lock

LOCK TABLE table_name READ;
UNLOCK TABLES;
-- Characteristics: all connections can read; no modifications allowed

-- Session 1
LOCK TABLE employee READ;
SELECT * FROM employee; -- succeeds
UPDATE employee SET money=20000 WHERE id=1; -- fails immediately (no block, error)
UNLOCK TABLES;

-- Session 2
SELECT * FROM employee; -- succeeds
UPDATE employee SET money=25000 WHERE id=2; -- blocks until UNLOCK TABLES

Table Write Lock

LOCK TABLE table_name WRITE;
UNLOCK TABLES;
-- Characteristics: only locking connection can read/write; others blocked from both

-- Session 1
LOCK TABLE employee WRITE;
SELECT * FROM employee; -- succeeds
UPDATE employee SET money=3999 WHERE id=2; -- succeeds
UNLOCK TABLES;

-- Session 2
SELECT * FROM employee; -- blocks
UPDATE employee SET money=2999 WHERE id=3; -- blocks

Pessimistic vs Optimistic Locking

Pessimistic Lock Characteristics

Conservative: assumes data will be modified by others.

Locks data throughout processing.

Relies on database-provided lock mechanisms (all row/table/read/write locks).

Optimistic Lock Characteristics

Optimistic: assumes no concurrent modifications.

No locks on read; validates on update.

User-implemented; no resource contention until commit validation.

When to Use Which

Optimistic Lock : Read-heavy workloads where write locks would block many reads, hurting concurrency. Acceptable when conflict rate is low.

Pessimistic Lock : Read/write ratio not extremely skewed, or no throughput bottlenecks. Simpler, less risk; optimistic locking adds complexity and business risk.

Optimistic Lock Implementation

Version Number Approach

Add version column; increment on each update.

Read version with data; on update, include version in WHERE clause.

If version matches, update succeeds; if not, data changed — retry or abort.

Flag/Token Approach

Add dataflag column (e.g., VARCHAR(50) for UUID/timestamp).

Update flag on each write (new UUID, timestamp).

Compare flag on update; same logic as version.

-- Version example
SELECT version FROM employee WHERE id=1; -- returns 100
UPDATE employee SET name='弼马温', version=version+1 WHERE id=1 AND version=100;

-- Flag example
SELECT dataflag FROM employee WHERE id=2; -- returns 'bedcb0aa-85ba-11ec-b014-902e16a6f8db'
UPDATE employee SET name='齐天大圣', dataflag=uuid() WHERE id=2 AND dataflag='bedcb0aa-85ba-11ec-b014-902e16a6f8db';