TiDB Database Schema Design Overview

This document provides the basics of TiDB database schema design, including the objects in TiDB, access control, database schema changes, and object limitations.

In the subsequent documents, Bookshop will be taken as an example to show you how to design a database and perform data read and write operations in a database.

Objects in TiDB

To distinguish some general terms, here is a brief agreement on the terms used in TiDB:

To avoid confusion with the generic term database, database in this document refers to a logical object, TiDB refers to TiDB itself, and cluster refers to a deployed instance of TiDB.
TiDB uses MySQL-compatible syntax, in which schema means the generic term schema instead of a logical object in a database. For more information, see MySQL documentation. Make sure that you note this difference if you are migrating from databases that have schemas as logical objects (for example, PostgreSQL, Oracle, and Microsoft SQL Server).

Database

A database in TiDB is a collection of objects such as tables and indexes.

TiDB comes with a default database named test. However, it is recommended that you create your own database instead of using the test database.

Table

A table is a collection of related data in a database.

Each table consists of rows and columns. Each value in a row belongs to a specific column. Each column allows only a single data type. To further qualify columns, you can add some constraints. To accelerate calculations, you can add generated columns.

Index

An index is a copy of selected columns in a table. You can create an index using one or more columns of a table. With indexes, TiDB can quickly locate data without having to search every row in a table every time, which greatly improves your query performance.

There are two common types of indexes:

Primary Key: indexes on the primary key column.
Secondary Index: indexes on non-primary key columns.

Note

In TiDB, the default definition of Primary Key is different from that in InnoDB (a common storage engine of MySQL).

In InnoDB, the definition of Primary Key is unique, not null, and a clustered index.
In TiDB, the definition of Primary Key is unique and not null. But the primary key is not guaranteed to be a clustered index. To specify whether the primary key is a clustered index, you can add non-reserved keywords CLUSTERED or NONCLUSTERED after PRIMARY KEY in a CREATE TABLE statement. If a statement does not explicitly specify these keywords, the default behavior is controlled by the system variable @@global.tidb_enable_clustered_index. For more information, see Clustered Indexes.

Specialized indexes

To improve query performance of various user scenarios, TiDB provides you with some specialized types of indexes. For details of each type, see Indexing and constraints.

Other supported logical objects

TiDB supports the following logical objects at the same level as table:

View: a view acts as a virtual table, whose schema is defined by the SELECT statement that creates the view.
Sequence: a sequence generates and stores sequential data.
Temporary table: a table whose data is not persistent.

Access Control

TiDB supports both user-based and role-based access control. To allow users to view, modify, or delete data objects and data schemas, you can either grant privileges to users directly or grant privileges to users through roles.

Database schema changes

As a best practice, it is recommended that you use a MySQL client or a GUI client instead of a driver or ORM to execute database schema changes.

Object limitations

For more information, see TiDB Limitations.