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

What Is NewSQL

NewSQL is a class of modern relational databases that retain the ACID guarantees and SQL interface of traditional databases while offering the horizontal scalability and high‑availability of NoSQL systems.

Traditional SQL Problems

As internet services grew, single‑node relational databases became performance bottlenecks. Two common remedies are upgrading hardware (which eventually hits a ceiling) and sharding data across distributed clusters, which introduces complexity such as cross‑shard joins and transactions.

NoSQL Issues

NoSQL systems sacrifice strong consistency and relational modeling for high availability and scalability. Their advantages include automatic partitioning and performance, while drawbacks involve lack of strong consistency, limited SQL support, and missing relational features.

NewSQL Characteristics

Supports full SQL with complex queries and large‑scale analytics.

Provides ACID transactions and isolation levels.

Offers elastic scaling that is transparent to the application layer.

Delivers high availability through automatic failover.

Three Generations of Databases

Traditional relational databases (e.g., MySQL).

NoSQL databases (e.g., MongoDB, Redis).

NewSQL databases (e.g., TiDB).

TiDB Overview

TiDB, developed by PingCAP, is an open‑source distributed relational database that supports both OLTP (online transaction processing) and OLAP (online analytical processing) workloads, making it a true HTAP solution. It is compatible with the MySQL 5.7 protocol and ecosystem.

Key Goals

Provide a one‑stop solution for OLTP, OLAP, and HTAP; support large data volumes, high availability, and strong consistency; and be cloud‑native, working seamlessly on public, private, or hybrid clouds.

TiDB Core Components

TiDB Server : Stateless SQL processing layer that receives queries, plans execution, and interacts with storage via PD.

Placement Driver (PD) : Manages metadata, schedules region placement, and allocates global transaction IDs using Raft for consensus.

TiKV Server : Distributed key‑value store that holds data in Region units, each replicated via Raft for consistency.

TiSpark : Spark SQL engine that runs analytical queries directly on TiKV data, enabling real‑time HTAP without ETL.

TiFlash : Columnar storage engine optimized for analytical workloads, synchronously replicating data from TiKV.

Architecture Diagram

High‑Availability Design

All three core components (TiDB, PD, TiKV) are designed to tolerate node failures. TiDB is stateless and can be load‑balanced; PD runs an odd number of Raft nodes to ensure leader election; TiKV replicates each region (default three replicas) and automatically rebalances data when nodes disappear.

Distributed Transaction Support

TiKV implements two‑phase commit across regions, guaranteeing ACID properties for multi‑key operations.

Application Scenarios

MySQL Sharding & Merging : Use TiDB as a MySQL slave to unify data from multiple shards, enabling cross‑shard queries without middleware.

Direct MySQL Replacement : Deploy TiDB to replace a monolithic MySQL deployment, gaining horizontal scalability and HTAP capabilities without code changes.

Data Warehouse : Run complex analytical queries (TPC‑H) directly on TiDB or via TiSpark for large‑scale data analysis.

Component for Other Systems : Use TiKV as a key‑value store replacement for HBase or as a backend for Redis‑compatible workloads.

MySQL Compatibility

TiDB supports the MySQL 5.7 protocol, most syntax, and tools (e.g., mysqldump, MySQL Workbench). However, several MySQL features are not supported, such as stored procedures, triggers, events, foreign keys, and certain character sets.

Unsupported MySQL Features

Stored procedures and functions

Triggers

Events

Custom functions

Foreign key constraints

Temporary tables

Full‑text and spatial indexes

Various character sets (e.g., ascii , latin1 , utf8 , utf8mb4 )

SYSTEM schema, optimizer trace, XML functions, X‑Protocol, savepoints, column‑level privileges, XA syntax, CREATE TABLE ... AS SELECT , CHECK TABLE , CHECKSUM TABLE , GET_LOCK / RELEASE_LOCK

Configuration Differences

Default charset : TiDB uses utf8mb4 (collation utf8mb4_bin ) while MySQL 5.7 defaults to latin1 and MySQL 8.0 to utf8mb4 with utf8mb4_0900_ai_ci .

lower_case_table_names : TiDB only supports value 2 (store names as given, compare case‑insensitively).

explicit_defaults_for_timestamp : TiDB forces ON (timestamp updates automatically), whereas MySQL 5.7 defaults to OFF and MySQL 8.0 defaults to ON .

foreign_key_checks : TiDB defaults to OFF and does not allow enabling.

Limitations on SELECT Statements

Does not support SELECT ... INTO @var .

Does not support SELECT ... GROUP BY ... WITH ROLLUP .

Result ordering for GROUP BY expr follows MySQL 8.0 semantics (no guaranteed order).

Auto‑Increment Behavior

TiDB guarantees uniqueness of auto‑increment values within a single TiDB server but not across multiple servers, and the generated values are not guaranteed to be contiguous. The system variable tidb_allow_remove_auto_inc controls whether the AUTO_INCREMENT attribute can be removed.

References

Source: cnblogs.com/jiagooushi/archive/2023/03/24/17251486.html