MySQL Fundamentals and Optimization: Concepts, Schema Design, Indexes, and Query Tuning

The article focuses on relational database MySQL, beginning with a brief overview of its logical architecture (client, server, storage engine) and then discussing lock mechanisms (shared/read, exclusive/write, optimistic and pessimistic locks) and transaction properties, including the four isolation levels.

It describes the most common storage engines: InnoDB (transactional, crash‑recovery) and MyISAM (non‑transactional, no row‑level lock).

Creation‑time optimization covers schema design and data‑type choices. It recommends using the smallest appropriate integer type (TinyInt, SmallInt, etc.), choosing between Float/Double and Decimal for numeric data, selecting VarChar for variable‑length strings, Char for fixed‑length values, and using Blob/Text for large objects. For date/time, DateTime (8 bytes) or Timestamp (4 bytes) are suggested. General advice includes using proper identifiers, avoiding oversized VARCHAR, and balancing normalization with denormalization.

Index optimization is detailed: MySQL can only use the leftmost prefix of a composite index; B‑Tree indexes are the default and support range, prefix, and sorting, while Hash indexes work only for exact matches and cannot be used for ORDER BY or range queries. Recommendations include using appropriate index types, avoiding redundant or overly long indexes, ensuring indexed columns are not wrapped in functions, and placing range conditions at the end of multi‑column indexes.

Query‑time optimization highlights three key metrics—response time, rows scanned, and rows returned—and offers practical tips such as avoiding SELECT *, limiting unnecessary columns and rows, splitting large operations into smaller batches, rewriting joins, using COUNT(*), grouping by identifier columns, and applying LIMIT with index‑covering scans. An example of a paginated join query is provided:

SELECT id, NAME, age
FROM student s1
INNER JOIN (
    SELECT id FROM student ORDER BY age LIMIT 50,5
) AS s2 ON s1.id = s2.id;

Additional notes cover MySQL 5.7 features like generated columns, JSON data type with built‑in functions, and the importance of the EXPLAIN statement for performance analysis, illustrating fields such as select_type, type, possible_keys, key, key_len, rows, and extra.

EXPLAIN SELECT settleId FROM Settle WHERE settleId = "3679";

The article concludes with visual aids and references to original sources.