Skip to content

Latest commit

 

History

History
116 lines (78 loc) · 6.62 KB

dev-guide-schema-design-overview.md

File metadata and controls

116 lines (78 loc) · 6.62 KB
title summary
TiDB Database Schema Design Overview
Learn the basics on TiDB database schema design.

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 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
Single Line Size 6 MB by default. You can adjust the size limit via the txn-entry-size-limit configuration item.
Single Column in a Line Size 6 MB

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.