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
- 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.
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 type | Maximum length (number of characters allowed) |
---|---|
Database | 64 |
Table | 64 |
Column | 64 |
Index | 64 |
View | 64 |
Sequence | 64 |
Limitations on a single table
Type | Upper limit (default value) |
---|---|
Columns | Defaults to 1017 and can be adjusted up to 4096 |
Indexes | Defaults to 64 and can be adjusted up to 512 |
Partitions | 8192 |
Size of a single line | 6 MB by default. |
Size of a single column in a line | 6 MB |
You can adjust the size limit of a single line via the txn-entry-size-limit configuration item.
Limitations on string types
Type | Upper limit |
---|---|
CHAR | 256 characters |
BINARY | 256 characters |
VARBINARY | 65535 characters |
VARCHAR | 16383 characters |
TEXT | 6 MB |
BLOB | 6 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.
TiDB supports an unlimited number of rows by adding nodes to the cluster. For the relevant principles, see TiDB Best Practices.