Skillversitas

TiDB Cheatsheet

A list of commands and quick references for TiDB.

Installation & Setup

# Install TiUP
curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh

# Start the playground
tiup playground

# Install the cluster component
tiup cluster

# Check TiDB versions
tiup list tidb

Connection

# MySQL client
mysql -h 127.0.0.1 -P 4000 -u root

# TiUP client
tiup client

Database Operations

-- Show databases
SHOW DATABASES;

-- Use a database
USE mydb;

-- Create a database
CREATE DATABASE IF NOT EXISTS mydb;

-- Drop a database
DROP DATABASE IF EXISTS mydb;

Table Operations

-- Show tables
SHOW TABLES;

-- Show the table structure
SHOW CREATE TABLE users;
DESCRIBE users;

-- Create a table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_RANDOM,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Drop a table
DROP TABLE IF EXISTS users;

Index Operations

-- Create an index
CREATE INDEX idx_email ON users (email);

-- Create a unique index
CREATE UNIQUE INDEX uk_email ON users (email);

-- Show indexes
SHOW INDEX FROM users;

-- Drop an index
DROP INDEX idx_email ON users;

DML Operations

-- Insert
INSERT INTO users (name, email) VALUES ('John', '[email protected]');

-- Insert multiple
INSERT INTO users (name, email) VALUES
('Mike', '[email protected]'),
('Anna', '[email protected]');

-- Update
UPDATE users SET name = 'John Smith' WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 1;

-- Replace
REPLACE INTO users (id, name, email) VALUES (1, 'John Updated', '[email protected]');

Transaction

-- Start a transaction
START TRANSACTION;
-- or
BEGIN;

-- SQL operations
INSERT INTO orders (user_id, total) VALUES (1, 250000);
UPDATE accounts SET balance = balance - 250000 WHERE id = 1;

-- Commit
COMMIT;

-- Rollback
ROLLBACK;

-- Set the isolation level
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- Set the mode
SET SESSION tidb_txn_mode = 'pessimistic';

Partitioning

-- RANGE partition
CREATE TABLE logs (
    id INT PRIMARY KEY AUTO_RANDOM,
    log_date DATE
) PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- HASH partition
CREATE TABLE sessions (
    id INT PRIMARY KEY AUTO_RANDOM,
    user_id INT
) PARTITION BY HASH(user_id) PARTITIONS 4;

TiDB Specific

-- AUTO_RANDOM
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_RANDOM,
    amount DECIMAL(12, 2)
);

-- TiFlash replica
ALTER TABLE orders SET TIFLASH REPLICA 1;

-- Force TiFlash
SELECT /*+ read_from_storage(tiflash[orders]) */ * FROM orders;

-- Batch delete
BATCH ON user_id LIMIT 1000 DELETE FROM users WHERE status = 'inactive';

Monitoring

-- Show connections
SELECT * FROM INFORMATION_SCHEMA.CLUSTER_PROCESSLIST;

-- Show slow queries
SELECT * FROM INFORMATION_SCHEMA.SLOW_QUERY ORDER BY Query_time DESC;

-- EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

TiUP Cluster Commands

# List clusters
tiup cluster list

# Show status
tiup cluster display my-cluster

# Start/Stop/Restart
tiup cluster start my-cluster
tiup cluster stop my-cluster
tiup cluster restart my-cluster

# Scale out
tiup cluster scale-out my-cluster ./scale-out.yaml

# Scale in
tiup cluster scale-in my-cluster --node 10.0.1.3:20160

# Destroy
tiup cluster destroy my-cluster

TiCDC

# Create a changefeed
cdc cli changefeed create \
  --pd=http://10.0.1.1:2379 \
  --sink-uri="mysql://root:@10.0.2.1:3306"

# List changefeeds
cdc cli changefeed list --pd=http://10.0.1.1:2379

# Remove a changefeed
cdc cli changefeed remove --pd=http://10.0.1.1:2379 --changefeed-id="mysql-sink"

Important Tips

  • Use AUTO_RANDOM for primary keys
  • Use TiFlash for analytical queries
  • Watch the TiDB Dashboard for performance
  • Use EXPLAIN for query optimization
  • Use PARTITIONING for large tables
  • Use BATCH for large-scale data operations