Hello, everyone.
Young-han Kim's Practical Database Performance Optimization course has finally launched.
To celebrate the launch, we are offering a 30% discount on the Database Roadmap 🙂
Title: Kim Young-han's Practical Database - Performance Optimization, Complete Mastery of Execution Plans and Indexes
Number of lectures: 104
Course duration: 21 hours 10 minutes
Link: https://inf.run/Gcg6N
And to celebrate the completion of all lectures in the Backend Roadmap, including the Database Roadmap, we are introducing Kim Young-han's Backend Master Pass for a limited time only.
Master Pass Link: https://inf.run/uixFt
<Course Features>
The most important feature of this lecture is that we will load real-world data ranging from millions to tens of millions of records into the database and tune it step by step together. Through this, you will learn practical methods to optimize queries that previously took several seconds down to the extreme of 0.01 seconds.
<Course Content>
During development, queries that run smoothly can suddenly slow down as data accumulates.
A query that initially executes in just 0.1 seconds can suddenly take over 1 second or 10 seconds, and in severe cases, it may lead to a service outage.
In such situations, we find ourselves asking the following questions.
Where should we start looking for the cause of slow queries?
What do the numerous items in the execution plan mean?
I created an index, but why isn't it being used?
In what order should composite indexes be designed?
Does having more indexes improve performance?
Why does filesort occur, and how should it be eliminated?
Why do queries slow down as you paginate further back?
Who determines the join order, and how can it be optimized?
Why does the optimizer choose an execution plan different from our expectations?
Database performance issues cannot be solved simply by knowing SQL syntax well.
You need to understand how the database stores data, how it traverses indexes, and the criteria the optimizer uses to select an execution plan.
Rather than fixing slow queries by guesswork, this course covers how to identify the root cause and resolve it based on the execution plan and actual measurement results.
First, you will learn the internal architecture of MySQL and InnoDB and analyze how queries are actually executed using EXPLAIN and EXPLAIN ANALYZE.
After that, we will directly create and resolve performance issues using millions of rows of real-world data.
Optimizing Full Table Scans into Index Scans
Determining the Column Order of a Composite Index
Understanding B+Tree and Internal Index Operations
Analysis of Clustered and Secondary Indexes
Diagnosing the Cause of Using filesort and Early Termination
Performance Improvement Using ICP and Covering Indexes
Index Skip Scan, Index Merge, MRR Analysis
Leveraging the Optimizer and Histograms
Join Order and Random Access Optimization
Sorting, Paging, and Grouping Performance Optimization
Streaming Aggregation and Pre-aggregation Table Design
We don't just give you the conclusion and say, "Add an index here."
We examine step by step why that index is necessary, what happens inside the database, and how the execution plan and actual execution time differ.
Furthermore, it goes beyond merely optimizing a single query, delving deeply into the fundamental principles that empower you to continuously analyze and resolve new performance issues in real-world scenarios.
<What You Will Gain from This Course>
1. Ability to read execution plans
Understand the key metrics of EXPLAIN and EXPLAIN ANALYZE, and identify the root causes of slow queries within the execution plan.
2. The ability to design indexes directly
Beyond simply adding an index to a single column, you will be able to design composite indexes tailored to data distribution and query conditions.
3. Understanding Database Internals
You will understand the core principles of index internals, including B+Trees, clustered indexes, secondary indexes, and random access.
4. Real-World Performance Optimization Experience
You will analyze slow queries firsthand using millions of records and experience the process of optimizing them to achieve performance improvements of tens to hundreds of times.
5. Criteria for Resolving Issues in the Production Environment
You can choose the optimal approach for any given situation by considering not only the benefits of indexes, but also write performance, storage space, and operational costs.
<Full Curriculum>
Course Introduction and Materials
Lab Environment Setup
Database Performance and MySQL Architecture
Execution Plan 1 - Mastering EXPLAIN
Execution Plan 2 - Mastering ANALYZE
Practical Tuning 1 - Full Table Scan Diagnosis and Composite Index Basics
Index Internal Structure 1 - B+Tree
Index Internal Structure 2 - Clustered, Secondary Indexes
Practical Tuning 2 - Diagnosing Using filesort and Early Termination
Advanced Indexing 1 - ICP, Covering, Skip Scan
Advanced Indexes 2 - Merge, MRR, New Features, Hints, Operations
Optimizer and Histogram
Join Optimization
Practical Tuning 3 - Diagnosing Join Random Access and Covering Indexes
Sorting, Paging, and Grouping Optimization
Practical Tuning 4 - Streaming Aggregation and Pre-aggregated Tables
Next,
Database performance optimization is not simply a matter of memorizing a few SQL tuning techniques.
It is a process of understanding the data retrieval path, identifying bottlenecks based on execution plans, and determining the most appropriate solution among various options.
Through this course, I hope you grow into a developer who can accurately analyze the root cause and resolve issues independently, rather than blindly adding indexes when encountering slow queries.
In this lecture, I have distilled the experience I've gained from resolving countless performance issues in real-world settings, along with the insights from years of careful consideration.
I hope this has been helpful for your growth.
Thank you.