Sign In Start for Free
Start for Free

Course Info

What You Will Learn

In this course, you will learn to design effective table and index structures, manage partitioned tables, and understand the SQL optimization processes.

Who This Course is For

The course is designed for:

  • SQL developers and database administrators (DBAs) who wish to enhance their SQL tuning skills and improve database performance.
  • Professionals having good working knowledge and understanding of SQL statements.

Prerequisites

To ensure you are well-prepared for this course, please complete the following courses:

Course Outline

ppt

Clustered and Non-Clustered Indexes Considerations for Performance

  • Understand the primary key and the underlying indexes in TiDB
  • Describe the query (SELECT) process
  • Describe the data modification (INSERT / UPDATE / DELETE) process
  • Describe the TiKV Region split process
  • Performance tune common TiKV Region hotspots issues
ppt

Secondary Indexes and Partitioned Tables

  • Understand and create secondary indexes
  • Understand the table partition types
  • Create partitioned tables
  • Modify partitioned tables
  • Describe the best practices with partitioned tables
ppt

Optimizer Fundamentals

  • Describe each phase of SQL statements processing
  • Explain the functionality of the optimize
  • Explain the various phases of optimization
  • Display execution plans
  • Monitor SQL statements
ppt

Optimizing Queries

  • Describe the operators and data aggregators for tables and indexes
  • List the possible access paths for tables and indexes
  • Use indexes to improve query performance
  • Describe the aggregation function operators
  • Describe the operators for joins
  • List the possible access paths for joins
  • Describe the TiFlash MPP architecture
  • Use TiFlash to improve query performance
ppt

Cost Based Optimizer (CBO) Statistics Management

  • Explain how CBO statistics works
  • Understand the fundamental components of statistics
  • Display CBO statistics
  • Gather CBO statistics
  • Import and export statistics
  • Use dynamic pruning to optimize queries for partitioned tables
ppt

Best Practices for SQL Tuning in TiDB

  • Control execution plans with optimizer hints
  • Control execution plans with SQL Plan Management (SPM)
  • Understand the best practices for using indexes and writing effective SQL
CTA
Master TiDB: Scale Your Skills, Certify Your Success

Talk to Sales