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 (experimental feature).

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.

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 the following links:

• Expression indexes (Experimental)
• Columnar storage (TiFlash)
• RocksDB engine

• Titan plugin

• Invisible indexes
• Composite PRIMARY KEY
• Unique indexes
• Clustered indexes on integer PRIMARY KEY
• Clustered indexes on composite or non-integer key

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

This section lists the object limitations on identifier length, a single table, and string types. For more information, see TiDB Limitations.

Limitations on identifier length

Limitations on a single table

You can adjust the size limit of a single line via the txn-entry-size-limit configuration item.

Limitations on string types

Number of rows

TiDB supports an unlimited number of rows by adding nodes to the cluster. For the relevant principles, see TiDB Best Practices.