# Course: TiDB SQL Tuning (307)

## About
+ Course Name: TiDB SQL Tuning 
+ Version
  + TiDB: 6.0
+ Designed for learners with 

## Pre-Requisites
+ 

## Outline
+ Sub Course: Deeper into the TiDB architecture
  + Chapter 01: Tidb Cluster Introduction
    + Describe TiDB database cluster architecture
    + Describe the main features of TiDB Server, TiKV Server, and PD Server
  + Chapter 02: Tidb Server Introduction
    + Understand TiDB Server architecture
    + Describe the role of TiDB Server
  + Chapter 03: Tikv Introduction
    + Understand TiKV architecture
    + Understand TiKV data persistence
    + Understand how TiKV provides MVCC and distributed transaction support
    + Understand the distributed consistency of TiKV based on raft algorithm
    + Understand TiKV coprocessor
  + Chapter 04: Placement Driver Introduction
    + Understand the architecture and functions of PD
    + Understand the distribution of TSO
    + Understand PD scheduling
    + Understand the role of labels
  + Chapter 05: Sql Execution Process
    + Understand the process of reads and writes DML statements
    + Understand the execution flow of DDL statements 
+ Sub Course: TiDB database schema design
  + Chapter 06: Tidb Database Table Schema Design
    + Understand Database Schema Design
    + Describe data types supported by TiDB
    + Describe the differences between the same data types in MySQL
  + Chapter 07: Tidb Database Indexes
    + Understand how TiDB indexes are constructed
    + Describe the supported index types
    + Describe the precautions for using indexes in TiDB
  + Chapter 08: Tidb Database System Tables
    + Understand where TiDB database system tables are stored
    + Describe how to get the cluster information
    + Describe common system tables for DBA tasks
+ Sub Course : TiDB database HTAP technology
  + Chapter 09: Tidb Htap Overview
    + Describe HTAP technology
    + Understand the HTAP architecture of TiDB databases
    + Understand the core features of HTAP for TiDB databases
  + Chapter 10: Tiflash
    + Describe the TiFlash architecture
    + Describe the core features of TiFlash
+ Sub Course : TiDB database transaction design
  + Chapter 11: Distributed Transactions Fundamentals
    + Describe the basics of transaction
    + Understand common distributed transaction scenarios
    + Describe the difficulties of implementing distributed database transactions
  + Chapter 12: Percolator Transaction Model
    + Describe the background of Percolator
    + Describe snapshot isolation levels
    + Describe distributed clocks
    + Describe Percolator transaction process
  + Chapter 13: TiDB Transactions
    + Understand TiDB database transaction implementation
    + Describe TiDB transaction locks
    + Describe how to optimize transactions
+ Sub Course : TiDB database query optimization
  + Chapter 14: Introduction to Tidb Optimizer
    + Understand TiDB optimizer architecture
    + Understand optimizer preprocessing
    + Describe logic optimization
    + Describe physical optimization
  + Chapter 15: Understand Execution Plan
    + Describe how to get execution plans
    + Describe how to read execution plans
    + Describe how to manage execution plans
  + Chapter 16: Statistics Management
    + Describe statistics gathering
    + Describe statistics import and export
  + Chapter 17: Index-Based Optimization
    + Understand the impact of adding indexes during business time
    + Understand how indexes are scanned
    + Understand the rules for using indexes
  + Chapter 18: Tidb Sql Tuning
    + Problem SQL vs. slow SQL
    + Optimizing DML statements
    + Tuning based on execution plans
  + Chapter 19: Query Optimization Case Studies
    + Describe the principles of SQL tuning
    + Describe optimization techniques for implicit data type conversion
    + Describe deep paging optimization techniques
    + Describe aggregation operation optimization techniques
    + Describe multi-table join optimization techniques
+ Sub Course : TiDB database system optimization
  + Chapter 20: TiDB Server Performance Related Parameters
    + Describe important operating system parameters
    + Describe important database configuration parameters
    + Describe important database system variables
  + Chapter 21: Performance Related Parameters
    + Understand the basics of PD scheduling
    + Describe common scheduling parameters
    + Understand how to handle common scheduling issues
  + Chapter 22: TiKV Performance Related Parameters
    + Understand the overall architecture of TiKV
    + Describe TiKV reads and writes processes
    + Describe how to find the causes of TiKV reads/writes issues
  + Chapter 23: Os Optimization
    + Describe hardware configuration recommendations
    + Describe operating system configuration recommendations
    + Describe rapid environmental checks
  + Chapter 24: Common Monitoring Metrics For Tidb Clusters
    + Describe monitoring metrics commonly used in TiDB clusters
    + Describe important monitoring alerts
  + Chapter 25: Performance Monitoring
    + Differentiate performance issues between TiDB Server and TiKV Server
    + Differentiate TiKV write latency from external causes such as thread pool bottlenecks or disk performance jitter
    + Monitor the impact of disk capacity on PD scheduling and performance


## References
+ TiDB documentation: https://docs.pingcap.com/tidb