TiDB Explained: The Complete Guide to Distributed SQL and HTAP

# Connect to TiDB exactly like MySQL
mysql -h tidb-server-host -P 4000 -u root

# TiDB speaks MySQL protocol — existing tools work:
# MySQL Workbench, DBeaver, Sequelize, SQLAlchemy, JDBC, etc.

# Check cluster health via pd-ctl
pd-ctl -u http://pd-host:2379 store      # view all TiKV stores
pd-ctl -u http://pd-host:2379 health     # cluster health
pd-ctl -u http://pd-host:2379 region 1   # inspect a specific region

# Check Region distribution
pd-ctl -u http://pd-host:2379 region --jq '.regions | length'

# View store utilization
pd-ctl -u http://pd-host:2379 store --jq '.stores[] | {id, capacity, used_size, region_count}'

-- Enable TiFlash replication for a table
ALTER TABLE orders SET TIFLASH REPLICA 1;

-- Check replication progress
SELECT TABLE_NAME, REPLICA_COUNT, AVAILABLE, PROGRESS
FROM information_schema.TIFLASH_REPLICA
WHERE TABLE_SCHEMA = 'myapp';

-- Force a query to use TiFlash (optimizer usually handles this automatically)
SELECT /*+ READ_FROM_STORAGE(TIFLASH[orders]) */
  region, SUM(total), COUNT(*)
FROM orders
WHERE order_date > '2025-01-01'
GROUP BY region;

-- Standard transactions work exactly like MySQL
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1001;
UPDATE accounts SET balance = balance + 500 WHERE id = 1002;
COMMIT;

-- Snapshot isolation is the default (repeatable read)
-- For serializable isolation:
SET GLOBAL transaction_isolation = 'SERIALIZABLE';

-- Check transaction status
SELECT * FROM information_schema.CLUSTER_PROCESSLIST;

-- Default limits (configurable in tidb.toml):
-- tidb_txn_entry_size_limit: 6MB per key-value pair
-- tidb_txn_total_size_limit: 100MB total per transaction

-- For bulk operations, use batch mode:
-- Option 1: BATCH DML (TiDB-specific extension)
BATCH ON id LIMIT 10000 UPDATE orders SET status = 'archived'
WHERE created_at < '2023-01-01';

-- Option 2: Application-level chunking
-- Break large DELETEs/UPDATEs into chunks of 10,000 rows

# Scale out TiKV (add storage + compute)
tiup cluster scale-out prod-cluster scale-out.yaml

# scale-out.yaml:
tikv_servers:
  - host: 10.0.1.14
    port: 20160
    data_dir: /data/tikv
    config:
      server.labels:
        zone: us-east-1c

# Scale out TiDB Server (add query capacity without data movement)
tidb_servers:
  - host: 10.0.1.15
    port: 4000

# PD automatically rebalances regions across new TiKV nodes
# Progress visible in TiDB Dashboard: http://pd-host:2379/dashboard

# Remove a TiKV store safely (PD moves its regions first)
pd-ctl -u http://pd-host:2379 store delete 5  # store ID 5

# Wait for region migration to complete (can take minutes to hours depending on data size)
pd-ctl -u http://pd-host:2379 store 5 | jq '.store.state_name'
# When returns "Tombstone", the node is safe to remove

-- Key SQL queries for monitoring:

-- Slow queries (built-in slow query log table)
SELECT query, query_time, mem_max, disk_max
FROM information_schema.SLOW_QUERY
WHERE is_internal = 0
ORDER BY query_time DESC
LIMIT 20;

-- Active transactions and locks
SELECT * FROM information_schema.CLUSTER_PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC;

-- TiKV Region health (all should be 0)
SELECT type, count FROM information_schema.TIKV_REGION_PEERS
WHERE state != 'normal';

-- Statement summary (aggregated performance stats like Performance Schema)
SELECT schema_name, digest_text, exec_count, avg_latency, max_latency
FROM information_schema.STATEMENTS_SUMMARY
WHERE schema_name = 'myapp'
ORDER BY avg_latency DESC
LIMIT 20;

-- Works in TiDB (same as MySQL):
-- SELECT, INSERT, UPDATE, DELETE
-- JOINs, subqueries, window functions
-- Stored procedures, triggers (limited)
-- Standard DDL (CREATE TABLE, ALTER TABLE)
-- JSON functions, full-text search

-- Differences to check:
-- AUTO_INCREMENT: TiDB uses a different allocation strategy
--   (not strictly sequential — intentional for distributed performance)
SET @@tidb_auto_id_cache = 1;  -- set to 1 for strictly sequential (slower)

-- Foreign keys: supported in TiDB 6.6+ with some limitations
SHOW VARIABLES LIKE 'foreign_key_checks';

-- LOCK TABLES: not supported (use SELECT ... FOR UPDATE instead)

-- Temporary tables: supported in TiDB 5.3+

-- Check compatibility before migrating:
-- Run: tiup compat check --host mysql-host --port 3306 --user root