Master MySQL Interview Questions: Indexes, Transactions, Locks & More

What is MySQL?

MySQL is a relational database management system developed by MySQL AB, now owned by Oracle. It is one of the most popular open‑source RDBMS, widely used in web applications and Java enterprise development because it is free and easily extensible.

Differences between MySQL, Oracle, and SQL Server

SQL Server runs only on Windows, while MySQL and Oracle are cross‑platform and support migration between different database systems.

MySQL is open‑source and free; Oracle and SQL Server require licensing fees.

In terms of size, MySQL is the smallest, SQL Server is medium, and Oracle is the largest.

Oracle supports high concurrency and large traffic; MySQL typically requires clustering or caching to handle heavy loads.

Oracle provides granular user permissions; MySQL grants full access to anyone with login credentials.

Installation footprint differs: MySQL occupies a few hundred megabytes, whereas Oracle requires several gigabytes and consumes more memory.

For pagination, MySQL uses LIMIT, SQL Server uses TOP, and Oracle uses ROWNUM.

Oracle lacks an auto‑increment type, while MySQL and SQL Server usually provide it.

What is an Index?

An index is a special file (part of the InnoDB tablespace) that stores pointers to records in a table, implemented as a sorted data structure—typically a B‑tree or B+‑tree—to accelerate query and update operations.

Conceptually, an index works like a book’s table of contents, occupying physical storage to enable fast data retrieval.

Index Usage Scenarios

Use a regular index when a column has many duplicate values and the dataset is large.

Use a unique index when the column values are distinct.

Use a composite index when multiple columns are frequently queried together.

Regular indexes do not support NULL values, while unique indexes do.

If a table experiences many inserts, updates, or deletes and few reads, avoid creating indexes because they slow down write operations.

Avoid indexing columns that are never used in WHERE clauses or are updated frequently.

Difference Between B‑Tree and B+‑Tree

In a B‑tree, both internal nodes and leaf nodes can store keys and values; in a B+‑tree, internal nodes store only keys, while leaf nodes store both keys and values and are linked together.

Lock Mechanism Overview

When concurrent transactions occur, data inconsistencies may arise; locks ensure ordered access, similar to locking a hotel room so only the holder of the key can occupy it.

What Is a Deadlock and How to Resolve It?

A deadlock occurs when two or more transactions each hold a lock on a resource the other needs, creating a cycle.

Standardize the order of table access across programs to reduce deadlock chances.

Acquire all required locks at the beginning of a transaction to minimize lock waiting.

For highly contended operations, consider using coarse‑grained (table‑level) locks to lower deadlock probability.

Why Use Views? What Is a View?

Views are virtual tables that do not store data physically; they present a subset of columns and rows from underlying tables, improving SQL reuse and enhancing data security by exposing only necessary information.

Main Categories of SQL Statements

Data Definition Language (DDL): CREATE, DROP, ALTER – defines or modifies database objects such as tables, views, and indexes.

Data Query Language (DQL): SELECT – retrieves data.

Data Manipulation Language (DML): INSERT, UPDATE, DELETE – modifies data within tables.

Data Control Language (DCL): GRANT, REVOKE, COMMIT, ROLLBACK – manages permissions and transaction control.

Materials are compiled from the internet for free sharing; please delete if infringing.