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

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

Identifier typeMaximum length (number of characters allowed)
Database64
Table64
Column64
Index64
View64
Sequence64

Limitations on a single table

TypeUpper limit (default value)
ColumnsDefaults to 1017 and can be adjusted up to 4096
IndexesDefaults to 64 and can be adjusted up to 512
Partitions8192
Single Line Size6 MB by default. You can adjust the size limit via the txn-entry-size-limit configuration item.
Single Column in a Line Size6 MB

Limitations on string types

TypeUpper limit
CHAR256 characters
BINARY256 characters
VARBINARY65535 characters
VARCHAR16383 characters
TEXT6 MB
BLOB6 MB

Number of rows

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

Was this page helpful?