Mastering TiDB: Essential Naming, Design, and SQL Best Practices

1. Introduction

This document provides a unified reference for TiDB developers, administrators, operators, analysts, and architects. It standardizes development, usage, and SQL optimization to improve code readability, maintainability, and operational efficiency.

2. Object Naming Conventions

Principles

Use meaningful English words separated by underscores; only letters, numbers, and underscores are allowed; avoid TiDB reserved words; prefer lowercase for all objects.

Database Naming

Distinguish by business or product line, typically not exceeding 20 characters (e.g., tmp_crm, test_crm).

Table Naming

Use a common prefix for tables belonging to the same module, keep names under 32 characters, and add comments to clarify purpose. Examples: tmp_t_crm_relation_0425, bak_t_crm_relation_20170425, tmp_st_[biz_code][owner][date], t_crm_ec_record_YYYYMMDD.

Field Naming

Use English words or abbreviations that reflect meaning; keep consistent names across tables; add comments; boolean columns use is_ prefix (e.g., is_enabled); limit to 30 characters and 60 columns per table; avoid reserved words.

Index Naming

Primary key: pk_[table_abbr]_[col_abbr]. Unique index: uk_[table_abbr]_[col_abbr] . Normal index: idx_[table_abbr]_[col_abbr] . For multi‑column indexes, use concise abbreviations. 3. Database Object Design Table Design TiDB automatically creates an implicit primary key (RowID) if none is defined. Ensure each table has a primary key or unique index with non‑null columns to guarantee idempotent replication and backup. Avoid ultra‑wide tables; limit columns to 60 and row size to 64 KB. Avoid complex data types. Ensure join columns have identical data types to prevent implicit conversion. Field Design Supported types include all MySQL integer, floating‑point, decimal, date/time, and string types. Recommendations: Prefer INT or BIGINT for integers; use INT(10) UNSIGNED for IPv4 addresses. Prefer DECIMAL over FLOAT/DOUBLE to avoid precision loss; cast explicitly when comparing with strings. Use DATE for day‑level precision, DATETIME for full timestamp, and avoid storing dates as strings. Use VARCHAR(N) for variable‑length strings, keep N small; use CHAR(1) only for single‑character fields; avoid ENUM/SET , prefer TINYINT . Default Values Defaults must be constants; time types may use NOW , CURRENT_TIMESTAMP , etc. BLOB , TEXT , and JSON cannot have defaults. In strict SQL mode, missing NOT NULL values cause transaction failure; otherwise TiDB supplies implicit defaults (0 for numbers, empty string for non‑enum strings, current timestamp for TIMESTAMP ). <code>CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );</code> Index Design Select high‑selectivity columns; keep total indexes per table under 5; limit columns per index to 5; unique indexes should have ≤3 columns; avoid indexing frequently updated columns; follow left‑most prefix rule for composite indexes; specify prefix length for long VARCHAR indexes; regularly drop unused indexes; place ORDER BY , GROUP BY , DISTINCT columns at the end of the index to enable covering indexes; avoid functions on indexed columns in WHERE clauses. Permission Design TiDB creates a default root@'%' account; in production, set a strong password and do not expose it. Recommended roles: root (global admin), dba (DBA), app (application), tempuser (read‑only temporary stats), other (third‑party access). 4. Data Model Design Integrity Enforce business rules via integrity constraints, simplify testing, and allow view‑based abstraction. Performance Choose appropriate field types and lengths, design efficient primary keys and indexes, and consider controlled denormalization to reduce joins. Scalability Adopt one‑table‑per‑entity, use range or hash partitioning, and keep schema changes isolated. 5. SQL Development Standards Table Creation / Deletion Wrap CREATE TABLE IF NOT EXISTS and DROP TABLE IF EXISTS with existence checks to avoid runtime errors. Select * Never use SELECT * ; explicitly list required columns to reduce bandwidth and enable covering indexes. Large Transactions TiDB limits single‑row size to 120 MB (v5.0+) and single transaction size to 10 GB (v4.0+). Recommend 100–500 rows per transaction for optimal performance. Region Hotspots Hotspots arise when a small number of regions receive most reads/writes. Mitigation strategies include: Design non‑sequential primary keys (e.g., time‑based IDs). Use table partitioning (hash or range). Set SHARD_ROW_ID_BITS to split the implicit _tidb_rowid into multiple shards (e.g., SHARD_ROW_ID_BITS = 4 for 16 shards). <code>CREATE TABLE t (c INT) SHARD_ROW_ID_BITS = 4; ALTER TABLE t SHARD_ROW_ID_BITS = 4;</code> Function Usage on Indexed Columns Do not apply functions to indexed columns in WHERE clauses; instead, transform the constant side. <code>-- Wrong SELECT * FROM account WHERE DATE_FORMAT(gmt_create, '%Y-%m-%d') = '2009-01-01'; -- Correct SELECT * FROM account WHERE gmt_create = STR_TO_DATE('2009-01-01', '%Y-%m-%d');</code> Data Deletion Prefer TRUNCATE or DROP + recreate over DELETE . DELETE does not free space immediately; space is reclaimed during RocksDB compaction. Other Guidelines Avoid arithmetic or function calls on indexed columns in WHERE . Replace OR with IN() when the list size is < 300. Do not use leading % in LIKE patterns. When executing multi‑statement batches, only the first statement’s result is returned. 6. Transaction Limits Isolation Levels TiDB supports Snapshot Isolation (SI), equivalent to Repeatable Read. SI prevents phantom reads but cannot prevent write skew; use SELECT FOR UPDATE to avoid write skew. Savepoint TiDB does not support savepoints; Spring PROPAGATION_NESTED must be avoided. Large Transaction Restrictions Maximum single‑row size: 120 MB (v5.0+). Maximum transaction size: 10 GB (v4.0+). Adjust via performance.txn-entry-size-limit and performance.txn-total-size-limit in tidb.toml . 7. Implicit Type Conversion TiDB automatically converts mismatched operand types according to a defined hierarchy (NULL → NULL, strings → strings, integers → integers, decimal ↔ integer, decimal ↔ float, timestamp/date ↔ constant, otherwise → double). Implicit conversion can cause index loss and precision loss. Index Loss Example <code>SELECT * FROM account WHERE account_id = 6010000000009801;</code> The predicate triggers a cast, preventing index usage. Precision Loss Example <code>SELECT * FROM t1 WHERE a BETWEEN '12123123' AND '1111222211111111200000';</code> Both sides are cast to DOUBLE , leading to inaccurate filtering. 8. Result‑Set Instability GROUP BY MySQL’s non‑full GROUP BY can produce nondeterministic rows. Use ONLY_FULL_GROUP_BY mode to enforce full grouping. ORDER BY Without explicit ORDER BY , TiDB’s distributed execution may return rows in arbitrary order. Always specify ordering columns. GROUP_CONCAT Omitting ORDER BY inside GROUP_CONCAT yields unstable concatenation order. Include ORDER BY on the concatenated column. 9. Index Usage Notes TiDB stores each index entry as a KV pair; a table with 10 indexes writes 11 KV pairs per row. Supported index types: primary, unique, secondary (single or composite). TiDB (v5.0) does not support reverse, full‑text, or global partition indexes. Indexes are usable for predicates such as =, >, <, >=, <=, LIKE 'prefix%', IN, BETWEEN, IS NULL . Predicates with leading % or LIKE '%...%' cannot use indexes. Composite Index Design Place high‑selectivity columns first; range predicates on earlier columns prevent later columns from being used. 10. Auto‑Increment Column Usage Principle TiDB guarantees uniqueness and monotonic increase but not continuity; IDs are allocated in batches across servers, so gaps are normal. Best Practice Define auto‑increment columns as BIGINT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT and use them as primary or unique keys. Manual Assignment Risks Manually inserting a value that collides with a server’s cached range causes Duplicate entry . Resolve by increasing AUTO_INCREMENT to a value beyond the current maximum. Check current max: SHOW CREATE TABLE t; Adjust: ALTER TABLE t AUTO_INCREMENT = 120000; 11. Various Timeouts in TiDB GC Timeout TiDB retains MVCC versions for 10 minutes by default. Adjust tikv_gc_life_time in mysql.tidb for longer snapshots (e.g., during full backups). Transaction Timeout Controlled by max-txn-time-use (default 590 s) and must be less than tikv_gc_life_time . Exceeding this aborts the transaction. SQL Timeout Variable max_execution_time (ms) limits individual statement execution; 0 means unlimited. 12. JDBC Best Practices Driver Version TiDB is MySQL 5.7 compatible; use MySQL Connector/J 5.1.36 or newer. Connection Parameters <code>spring.datasource.url=jdbc:mysql://{TiDBIP}:{TiDBPort}/{DBName}?characterEncoding=utf8&useSSL=false&useServerPrepStmts=true&prepStmtCacheSqlLimit=10000000000&useConfigs=maxPerformance&rewriteBatchedStatements=true&defaultfetchsize=-214783648</code> 13. Hotspot Mitigation Write Hotspot Causes Small tables with a single region. Sequential primary keys causing writes to the same region. Detection TiDB Dashboard’s Key Visualizer shows a bright curve for hotspot tables. Mitigation Configure SHARD_ROW_ID_BITS to split the implicit rowid (e.g., 4 bits → 16 shards). Avoid sequential auto‑increment primary keys; use AUTO_RANDOM or Snowflake‑style IDs. Apply hash or range partitioning to distribute data. Cache frequently read configuration tables in external stores like Redis. 14. Pagination Best Practices General Pagination Always include an ORDER BY clause. Example: <code>SELECT * FROM table_a ORDER BY gmt_modified DESC LIMIT start, page_offset;</code> Batch Processing for Single‑Column Primary Keys Use ROW_NUMBER() to assign row numbers, then group by page size to obtain start and end keys. <code>SELECT MIN(t.serialno) AS start_key, MAX(t.serialno) AS end_key, COUNT(*) AS page_size FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY serialno) AS row_num FROM tmp_loan ) t GROUP BY FLOOR((t.row_num-1)/50000) ORDER BY start_key;</code> Batch Processing for Composite Primary Keys Use the hidden _tidb_rowid for pagination on non‑index‑organized tables, or create a metadata table that records min/max composite keys per page. 15. Unique Sequence Generation Schemes Auto‑Increment Standard integer auto‑increment column; limited to integer values. Sequence Object Supported from TiDB v4.0; use CREATE SEQUENCE and NEXTVAL to obtain values. Snowflake‑Style IDs 64‑bit IDs composed of sign, delta‑seconds, worker‑node‑id, and sequence. Ensure clock monotonicity and manage worker‑node‑id range (≤ 4 million). Segment Allocation Maintain a sequence_allocation table with fields SEQ_NAME (varchar), MAX_ID (bigint), and STEP (int). Applications fetch a block of IDs by incrementing MAX_ID by STEP , then generate IDs locally until the block is exhausted. 16. Process Standards Operational guidelines for production clusters: Notify DBA of table creation and associated queries. Finalize index design before table rollout. Share any schema changes or index additions with DBA. Pre‑review new tables/columns with DBA. Coordinate bulk import/export with DBA. Schedule large‑scale statistics or updates during off‑peak hours. Inform DBA of promotional activities or new feature releases for traffic assessment. Promptly clean up obsolete SQL after feature deprecation.