Understanding TiDB: NewSQL Features, Architecture, and Comparison with MySQL

TiDB is a distributed NewSQL database that offers horizontal elastic scaling, ACID transactions, standard SQL, MySQL syntax and protocol, and strong data consistency, making it suitable for both OLTP and OLAP workloads.

What is NewSQL

Database development has progressed through three generations: traditional SQL relational databases (e.g., MySQL), NoSQL databases (e.g., MongoDB, Redis), and NewSQL databases that combine the scalability of NoSQL with the relational model and mature SQL query language.

Problems with Traditional SQL

Rapid internet growth demands 24/7 availability and massive data volumes, which expose bottlenecks in single‑node relational databases. Two common solutions are upgrading hardware (which eventually hits a ceiling) and data sharding using distributed clusters, which introduces complexity in cross‑shard joins and transactions.

NoSQL Issues

NoSQL sacrifices strong transactional guarantees and relational modeling for high availability and scalability, offering benefits such as automatic partitioning and performance gains, but lacking strong consistency and full SQL compatibility.

NewSQL Characteristics

NewSQL provides NoSQL‑level scalability while retaining the relational model and SQL, guaranteeing ACID transactions. It is inherently distributed, designed for the cloud era.

Key NewSQL Features

SQL support for complex queries and large‑scale analytics.

Full ACID transaction support with isolation levels.

Elastic scaling that is transparent to the application layer.

High availability with automatic failover.

How TiDB Originated

Inspired by Google’s F1/Spanner papers (2012) describing a globally distributed relational database, PingCAP co‑founder Huang Dongxu built TiDB to bring similar capabilities to the open‑source community.

TiDB Editions

TiDB is offered in a free Community edition and a paid Enterprise edition that adds support services and security features.

Core TiDB Features

Horizontal Elastic Scaling

Adding new nodes instantly expands TiDB’s capacity, allowing independent scaling of compute and storage while remaining transparent to operations.

Distributed Transaction Support

TiDB fully supports standard ACID transactions.

Financial‑Grade High Availability

Based on the Raft consensus algorithm, TiDB ensures 100% data consistency and automatic recovery without manual intervention.

Real‑Time HTAP

TiDB combines OLTP row‑store (TiKV) and OLAP column‑store (TiFlash) engines, enabling a single system to handle both workloads without ETL, with TiFlash replicating data from TiKV via Multi‑Raft Learner.

Cloud‑Native Distributed Database

Designed for cloud environments, TiDB integrates tightly with Kubernetes and supports public, private, and hybrid clouds, simplifying deployment, configuration, and maintenance.

MySQL Compatibility

TiDB implements the MySQL 5.7 protocol and most MySQL syntax, allowing most applications to migrate with little or no code changes, and provides rich data migration tools.

OLTP & OLAP Overview

OLTP (Online Transaction Processing)

Focused on high‑throughput, low‑latency transactional workloads such as banking, e‑commerce, and securities, handling hundreds to thousands of transactions per second.

OLAP (Online Analytical Processing)

Designed for complex analytical queries on large data volumes, emphasizing decision support and reporting rather than real‑time response.

OLTP vs OLAP Comparison

OLTP

OLAP

Real‑time requirement

High, immediate transaction processing

Lower, often daily updates

Data volume

Small (tens of rows per transaction)

Large (hundreds of GB to TB)

Focus

Customer‑oriented transaction handling

Market‑oriented analytical queries

Schema design

Entity‑relationship (ER) model

Star or snowflake schema

TiDB Architecture

TiDB consists of three core components: TiDB Server (stateless SQL processing), PD (Placement Driver) for metadata, scheduling, and ID allocation, and TiKV (distributed key‑value storage). Additional components include TiSpark for Spark‑based OLAP and TiFlash for column‑store acceleration.

TiDB Server

Receives SQL requests, parses and plans queries, and interacts with TiKV via PD to fetch data; it is stateless and can be scaled horizontally behind a load balancer.

PD Server

Manages cluster metadata, performs load balancing and region scheduling, and allocates globally unique, monotonically increasing transaction IDs using Raft for consistency.

TiKV Server

Provides distributed transactional key‑value storage; data is divided into Regions, each replicated via Raft groups for consistency and fault tolerance.

TiSpark

Runs Spark SQL directly on TiKV storage, enabling large‑scale analytical queries without data movement.

TiFlash

Column‑store nodes that accelerate analytical workloads by replicating TiKV data in columnar format.

High‑Availability Architecture

All three components (TiDB, PD, TiKV) tolerate partial failures. TiDB recommends at least two instances behind a load balancer; PD should have an odd number of nodes (typically three) to maintain Raft quorum; TiKV maintains multiple replicas per Region and automatically re‑replicates data when a node is down.

Application Scenarios

MySQL Sharding & Merging

TiDB can act as a MySQL slave to synchronize data, enabling real‑time cross‑shard queries without complex middleware.

Direct MySQL Replacement

TiDB can replace MySQL in existing stacks, providing automatic horizontal scaling and eliminating the need for manual sharding.

Data Warehouse

TiDB serves as a data warehouse, handling TPC‑H benchmark queries within seconds; TiSpark extends its analytical capabilities.

Module for Other Systems

TiKV can be used as a replacement for HBase or as a backend for Redis‑compatible workloads, offering raw key‑value APIs with optional ACID transactions.

MySQL Compatibility Comparison

TiDB supports the MySQL protocol and most syntax, allowing seamless migration.

Supported MySQL version: 5.7.

Some features are not supported or behave differently (e.g., stored procedures, triggers, events, custom functions, foreign keys, temporary tables, full‑text/spatial indexes, certain character sets, XA syntax, SELECT … INTO, GROUP BY WITH ROLLUP, etc.).

Unsupported MySQL Features

Stored procedures and functions

Triggers

Events

Custom functions

Foreign key constraints

Temporary tables

Full‑text/Spatial indexes

Non‑ ascii / latin1 / binary / utf8 / utf8mb4 character sets

SYSTEM schema

MySQL optimizer trace

XML functions

X‑Protocol

Savepoints

Column‑level privileges

XA syntax (TiDB uses internal two‑phase commit)

CREATE TABLE … AS SELECT syntax

CHECK TABLE syntax

CHECKSUM TABLE syntax

GET_LOCK and RELEASE_LOCK functions

Auto‑Increment Behavior

TiDB guarantees uniqueness of auto‑increment columns within a single TiDB server but not across multiple servers, and does not guarantee consecutive values. The system variable tidb_allow_remove_auto_inc controls whether the AUTO_INCREMENT attribute can be removed.

SELECT Limitations

Unsupported SELECT ... INTO @var syntax.

Unsupported SELECT ... GROUP BY ... WITH ROLLUP syntax.

GROUP BY result ordering differs from MySQL 5.7; TiDB follows MySQL 8.0 semantics (no guaranteed order).

View Limitations

TiDB does not support UPDATE, INSERT, DELETE, or other write operations on views.

Default Setting Differences

Character Set

TiDB default: utf8mb4

MySQL 5.7 default: latin1

MySQL 8.0 default: utf8mb4

Collation

TiDB default: utf8mb4_bin

MySQL 5.7 default: utf8mb4_general_ci

MySQL 8.0 default: utf8mb4_0900_ai_ci

Case Sensitivity ( lower_case_table_names )

TiDB only supports the value 2 , storing table names with original case but comparing case‑insensitively.

Linux MySQL default: 0

Windows MySQL default: 1

macOS MySQL default: 2

Timestamp Behavior

By default, TiDB updates TIMESTAMP columns automatically on row updates ( ON ), and this setting cannot be changed.

MySQL 5.7 default: OFF

MySQL 8.0 default: ON

Foreign Key Support

TiDB default: OFF (cannot be enabled)

MySQL 5.7 default: ON

Conclusion

The article provides a detailed technical overview of TiDB, its NewSQL nature, architecture, features, compatibility, and usage scenarios, while also containing promotional calls to follow the author’s channels.