Why TiDB Is the NewSQL Database Redefining OLTP & OLAP

What Is NewSQL

NewSQL combines the horizontal scalability of NoSQL with the relational model and mature SQL query language, preserving ACID transaction guarantees while supporting massive data growth.

Problems with Traditional SQL

Rapid internet growth creates massive user bases and 24/7 availability demands; traditional relational databases become bottlenecks. Two common remedies are hardware upgrades—limited by physical ceilings—and data sharding, which introduces middleware complexity for cross‑shard joins and transactions.

Issues with NoSQL

NoSQL sacrifices strong consistency and relational modeling for high availability and easy scaling. Its advantages include automatic partitioning and performance gains, while drawbacks are lack of strong consistency, limited SQL support, and complex, non‑standard APIs.

NewSQL Characteristics

NewSQL offers NoSQL‑level scalability while retaining the relational model, mature SQL, and ACID guarantees. It is inherently distributed, designed for cloud environments.

Key Features of TiDB (A Representative NewSQL System)

Horizontal elastic scaling – add nodes to increase capacity without downtime.

Distributed transaction support – full ACID compliance via Raft consensus.

Financial‑grade high availability – automatic failover with majority‑vote leader election.

Real‑time HTAP – simultaneous OLTP row store (TiKV) and OLAP column store (TiFlash) processing.

Cloud‑native design – deep integration with Kubernetes for public, private, or hybrid clouds.

High MySQL compatibility – supports MySQL 5.7 protocol and most client tools.

TiDB Architecture

Core Components

TiDB Server – stateless SQL layer that parses queries and forwards data requests to TiKV via PD.

Placement Driver (PD) Server – stores metadata, schedules region placement, and generates global transaction IDs.

TiKV Server – distributed key‑value store that holds data in Raft‑replicated regions.

TiSpark – Spark plugin that runs Spark SQL directly on TiKV for complex OLAP workloads.

TiFlash – columnar storage engine that accelerates analytical queries.

TiKV Structure

Data is split into roughly equal‑sized Region slices (default 144 MB). Regions automatically split when they grow and merge when they shrink. Each Region forms a Raft group with multiple replicas (default three). PD schedules region placement and load balancing, while Raft ensures consistency and automatic leader election.

High‑Availability Design

All three components—TiDB, PD, and TiKV—are built as Raft clusters. TiDB is stateless, so deploying at least two instances behind a load balancer provides seamless failover. PD requires an odd number of nodes (recommended three) to maintain quorum during leader elections. TiKV tolerates node loss; failed regions are re‑replicated to healthy nodes after a configurable timeout.

Application Scenarios

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

Direct MySQL replacement – applications can switch to TiDB with minimal code changes, gaining automatic scaling and strong consistency.

Data warehouse – TiDB 2.0 delivers sub‑10‑second query times on TPC‑H workloads; TiSpark extends analytical capabilities.

Module for other systems – TiKV’s raw key‑value API can replace HBase or serve as a backend for custom services.

Compatibility with MySQL

TiDB fully supports the MySQL 5.7 network protocol and most common syntax, allowing existing MySQL clients, tools (e.g., MySQL Workbench, mysqldump), and ORMs to operate unchanged. However, several MySQL features are either ignored or behave differently in a distributed environment.

Unsupported MySQL Features in TiDB

Stored procedures and functions

Triggers, events, and custom functions

Foreign‑key constraints (disabled by default)

Temporary tables

Full‑text and spatial indexes

System schema (SYS)

XA transaction syntax (internal two‑phase commit is used instead)

Various DDL statements such as CREATE TABLE ... ENGINE (parsed but ignored)

Lock functions GET_LOCK /

RELEASE_LOCK

Configuration Differences

Character Set

TiDB defaults to utf8mb4; MySQL 5.7 defaults to latin1, while MySQL 8.0 also defaults to utf8mb4.

Collation

TiDB uses utf8mb4_bin by default. MySQL 5.7 uses utf8mb4_general_ci, and MySQL 8.0 uses utf8mb4_0900_ai_ci.

Case Sensitivity (lower_case_table_names)

TiDB only supports the value 2 (store names as given, compare case‑insensitively). MySQL defaults vary by OS: 0 on Linux, 1 on Windows, 2 on macOS.

Timestamp Behavior

The system variable explicit_defaults_for_timestamp controls automatic updating of TIMESTAMP columns. TiDB forces this variable to ON, whereas MySQL 5.7 defaults to OFF and MySQL 8.0 defaults to ON.

Foreign‑Key Support

TiDB disables foreign‑key enforcement ( OFF) and does not allow changing the setting. MySQL 5.7 enables it by default.

Illustrative Diagrams