Comprehensive MySQL Fundamentals and Advanced Topics Overview

Database Basics

Explains why data is stored in memory vs files, advantages and disadvantages, and why relational databases store data permanently.

SQL and MySQL

Defines SQL and its DDL/DML/DQL/DCL categories, and introduces MySQL concepts such as storage engines (InnoDB vs MyISAM), data types, and the purpose of indexes.

Indexes

Describes primary, unique, ordinary, full‑text, composite, prefix, and clustering indexes, their structures (B‑tree, B+‑tree, hash), usage rules, and design principles.

Transactions and Locks

Covers ACID properties, isolation levels, lock types (row, table, page), lock algorithms, deadlock handling, and optimistic vs pessimistic locking.

Views, Stored Procedures, Triggers

Outlines the definition, benefits, limitations, and typical use cases of views, stored procedures/functions, and triggers.

SQL Optimization

Shows how to use EXPLAIN, interpret its fields, avoid common pitfalls (full scans, functions in WHERE, NOT IN), improve pagination, UNION, subqueries, and large‑table queries.

Replication and High Availability

Explains MySQL master‑slave replication, read/write splitting strategies, and backup tools ( mysqldump, XtraBackup) with their principles.

Sharding

Discusses vertical and horizontal sharding, their advantages, challenges (cross‑shard joins, distributed transactions, ID generation), and common middleware solutions.