Subpartitioning

Overview

Strengths

Use Cases

Sharding

Read/Write Separation

Elastic Expansion

Strong Sync

Basic Principles

Instance Architecture

Regions and AZs

Product Introduction

Billing Overview (InnoDB)

Product Pricing (InnoDB)

Purchase and Upgrade

Refund

Payment Overdue

Backup Space Billing

Purchase Guide

Creating Instance

Connecting to Instance

Managing Sharded Table

InnoDB Engine

Getting Started

Renaming Database Instance

Specifying Project for Instance

Isolating/Restoring/Terminating Instances

Adjusting Deployed Node

Changing Instance Specification

Restarting Instance

Instance Management

Disaster Recovery Read-Only Instance

Changing Networks

Creating Account

Modifying Account Permissions

Cloning Account

Configuring Read/Write Separation

Resetting Account Password

Deleting Account

Account Management

Access Management

Security Group Configuration

Transparent Data Encryption (TDE)

Security Management

Slow Query Analysis

Backup Mode

Downloading Backup File

Backup Encryption

Rolling Back Database

Backup and Rollback

Data Migration

Enabling Database Audit

Viewing Audit Logs

Modifying Log Retention Period

Database Audit

Operation Guide（InnoDB）

Import from Standalone Instance to TDSQL Instance

Import Between TDSQL Instances

Selection of TDSQL Instance and Shard Configuration

Practical Tutorial

Platform Security Design

Tenant Security Features

Security White Paper

Use Limits

Compatibility

Connecting Database

Join and Subquery

Sequence

Frequently Used DML Statements

Distributed Transaction

Creating Table

Connection Protection

Globally Unique Field

Exporting/Importing Data

Database Management Statements

Passthrough SQL

Preprocessing

Error Codes and Messages

InnoDB

Development Guide

History

Introduction

API Category

AssociateSecurityGroups

DescribeDBSecurityGroups

DescribeProjectSecurityGroups

DisassociateSecurityGroups

ModifyDBInstanceSecurityGroups

Security Group APIs

DescribeBackupFiles

DescribeDBEncryptAttributes

KillSession

ModifyAccountPrivileges

ModifyInstanceNetwork

ModifyDBEncryptAttributes

CreateOnlineDDLJob

Other APIs

Request Structure

Common Params

Signature v3

Signature

Responses

Making API Requests

ActiveHourDCDBInstance

CancelDcnJob

CloneAccount

CloseDBExtranetAccess

CopyAccountPrivileges

CreateAccount

CreateDedicatedClusterDCDBInstance

DeleteAccount

DescribeAccountPrivileges

DescribeAccounts

DescribeDBLogFiles

DescribeDBParameters

DescribeDBSlowLogs

DescribeDBSyncMode

DescribeDBTmpInstances

DescribeDCDBInstanceNodeInfo

DescribeDCDBInstances

DescribeDCDBPrice

DescribeDCDBShards

DescribeDatabaseObjects

DescribeDatabaseTable

DescribeDatabases

DescribeDcnDetail

DescribeFileDownloadUrl

DescribeFlow

DescribeLogFileRetentionPeriod

DescribeOrders

DestroyDCDBInstance

DestroyHourDCDBInstance

GrantAccountPrivileges

InitDCDBInstances

IsolateDedicatedDBInstance

IsolateHourDCDBInstance

ModifyAccountDescription

ModifyDBInstanceName

ModifyDBInstancesProject

ModifyDBParameters

ModifyDBSyncMode

ModifyInstanceVip

ModifyInstanceVport

ResetAccountPassword

SwitchDBInstanceHA

TerminateDedicatedDBInstance

UpgradeDedicatedDCDBInstance

UpgradeHourDCDBInstance

CreateDCDBInstance

CreateHourDCDBInstance

DescribeDCDBInstanceDetail

ModifyAccountConfig

OpenDBExtranetAccess

IsolateDCDBInstance

TDSQL APIs

Data Types

Error Codes

API Documentation

General

Disk Overuse

IO Metric Exception

FAQs

Performance Comparison Data for Strong Sync

General References

Glossary

Alarm Upgrade

Announcements

Contact Us

TDSQL for MySQL

TDSQL for MySQL allows you to subpartition tables by RANGE or LIST, with subpartitioning syntax similar to that in MySQL.

Subpartitioning by HASH at level 1 and by LIST at level 2 is as follows:

  PARTITION pRegion_1 VALUES IN('Beijing', 'Tianjin', 'Shanghai'),

  PARTITION pRegion_2 VALUES IN('Chongqing', 'Wulumuqi', 'Dalian'),

  PARTITION pRegion_3 VALUES IN('Suzhou', 'Hangzhou', 'Xiamen'),

  PARTITION pRegion_4 VALUES IN('Shenzhen', 'Guangzhou', 'Chengdu')

Subpartitioning by RANGE at level 1 and by LIST at level 2 is as follows:

   TDSQL_DISTRIBUTED BY RANGE(id) (s1 values less than (100),s2 values less than (1000));

Query OK, 0 rows affected, 1 warning (0.35 sec)

 functions are supported. If the function is empty, it will be defaulted to the 

TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT.

 functions are supported. The value entered is converted to year, month, and day and then compared against the sharded table information.

Do not use the TIMESTAMP type as the subpartitioning key, because it is subject to the time zone and can only specify a time value before the year of 2038.

If the subpartitioning key is `char` or `varchar` type, its length is better to be below 255.

Use level-1 subpartitioned tables for your business.

Before use, design your table structure reasonably according to the business scenario in the long run. Subpartitioning is suitable for scenarios where DDL changes are not required for a long time after the table structure is created, while the subpartitioned data needs to be cleared and trimmed regularly, such as log transaction tables.

Design the granularity of subpartitioning reasonably. We recommend that you do not design it in a finer-grained manne to avoid creating too many subtables. For example, you should subpartition transaction tables by month instead of by day or by hour, so that there will not be too many data files in the file system.

When performing a SQL query on a level-2 subpartitioned table, you need to include the key values of level-1 and level-2 subpartitioning in the query conditions as much as possible, so you can eliminate the need to open many data files for search during query execution.

When performing a JOIN query on a level-2 subpartitioned table, if you don't include the key values of level-1 and level-2 subpartitioning in the query conditions, the operation performance will be lowered, which is not recommended.

The primary key or unique index of the table should include the subpartitioning key; otherwise, the data uniqueness cannot be guaranteed.

tencent cloud