Course: TiDB SQL Tuning

Course introduction

This course provides in-depth knowledge and practical skills to optimize SQL performance in TiDB. 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.

Prerequisites

A good working knowledge and understanding of SQL statements is assumed. Completing the TiDB training courses, Introduction to TiDB and Learn SQL with TiDB is recommended.

Course Outline

Module 1: 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

Module 2: 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

Module 3: Optimizer Fundamentals

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

Module 4: 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

Module 5: 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

Module 6: 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

Public Training Available

Duration 2 days Location Virtual classroom live Cost $800 USD per seat Capacity 16 seats per class Asia–Pacific Session Date & Time SGT (UTC+8:00) | 19~20 February, 2025, 9:00 AM~5:00 PM Language English Registration Ends SGT (UTC+8:00) | 11 February, 2025, 5:00 PM Purchase a Seat

Private Training Available

Private Training allows you to pick-and-choose a specific time to deliver the course to your team in a private classroom on your schedule. To purchase Private Training, speak to your PingCAP Sales representative or Contact Us directly. Contact Us

Related Courses

Introduction to TiDB Introductory level free e-learning course for architects, application developers and database administrators. Learn SQL with TiDB Intermediate level free e-learning course for database administrators.