TiDB 4.0 课程 21 天定制学习计划(2.4 2.5.1)

☄️ 学习与认证 经验&心得&笔记
1

课程名称：

2.4 Behavior-Differences-Between-MySQL-and-TiDB
2.5.1 The-Lifecycle-of-a-SQL-Statement

学习时长：

20

课程收获：

了解了mysql和tidb的差异以其一条sql的执行过程

课程内容：

Goal: know the differences between Mysql and TiDB

Target audience:

  • Role: developer,DBA
  • Level:entry

Outline:

  • compatibility overview
  • Major features different from mysql

Part I: Compatibility Overview

Protocol

  • Mysql 5.7 client/server protocol,100% compatible

Syntax

  • most syntax in both mysql 5.7 and 8.0,highly compatible

semantic

  • the behaviors of some features are different/unsupported

Part II: Major Features Different from MySQL

Limitations to AUTO_INCREMENT columns

  • Adding this attribute to an existing column is not supported

Semantic of AUTO_INCREMENT columns

  • Single TiDB instance cluster:
    unique,incremental and continuous
  • Mutiple TiDB instances cluster:
    unique,but not necessarily incremental or continuous
  • Unsupported INDEX features
    Fulltext index
    Spatial index
    Some index options
  • Primary key
    Integer primary keys:stored as clustered index
    Other primary keys:stored as unique index
  • Multi-schema change is not supported
    alter table modify column a int,modify column b int;
  • Lossy column type changes are not supported
    char(10)→char(5)
    varchar(255)→varchar(10)
    decimal(5)→decimal(3)
  • isolation level
    repeatable read(default)
    a,implemented as snapshot isolation
    b,slightly different with repeatable read in mysql
    read committed
    a,possible for pessimistic transactions
  • Analyze Table
    Lightweight in mysql innodb,but heavy in TiDB
  • views
    no support for insert/update/delete views
  • storage engines
    sql layer’s ‘storage=xxx’ is ignored
    specify storage engines by ‘–store’ option when startup
  • charsets
    only support:utf8,utf8mb4,ascii,latin 1 and binary
  • execution plan
    output of ‘explain’ statements greatly different from mysql

Goal: present the lifecycle of a sql and understand the high level

outline:

  • TiDB architecture
  • Data organization
  • the lifecycle of a sql
  • distributed execution engine

Part I: TiDB architecture

  • TiDB Architecture
  • TiKV Architecture

Part II: Data Organization

  • key value

Part III：The lifecycle of a sql

  • The lifecycle of a sql
  • distributed execution engine

Query optimization

  • sql parser
  • logical optimizer
  • physical optimizer
  • statistics

query execution

  • root executor
  • distsql
  • coprocessor executor

学习过程中参考的其他资料