MySQL Single‑Table Optimization, Partitioning, Sharding and Scaling Strategies

When a MySQL table grows large, CRUD performance degrades; the article outlines practical steps to optimize a single table before considering splitting.

Single‑Table Optimization

Fields : Prefer tiny integer types (TINYINT, SMALLINT, MEDIUMINT) with UNSIGNED when possible; keep VARCHAR length minimal; use enums or integers instead of strings; prefer TIMESTAMP over DATETIME; limit column count (<20); avoid NULL columns; store IPs as integers.

Indexes : Create indexes only on columns used in WHERE or ORDER BY; use EXPLAIN to verify index usage; avoid indexing low‑cardinality columns; use prefix indexes for strings; avoid foreign keys and UNIQUE constraints when possible; keep multi‑column index order aligned with query predicates; drop unnecessary single‑column indexes.

SQL Practices : Enable slow‑query log; avoid column calculations (e.g., SELECT id WHERE age + 1 = 10); keep statements simple; avoid SELECT *; replace OR with IN (keep IN list <200 items); avoid functions, triggers, leading wildcards ( %xxx); minimize JOINs; compare same data types; avoid != / <> in WHERE; use BETWEEN for continuous ranges; paginate with LIMIT.

Engine Choice : MyISAM (read‑intensive, no transactions, table‑level locks) vs InnoDB (row‑level locks, transactions, foreign keys). InnoDB is preferred for INSERT/UPDATE‑heavy workloads.

System Parameters : Adjust back_log, wait_timeout, max_user_connection, thread_concurrency, skip_name_resolve, key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, query_cache_size, read_buffer_size, sort_buffer_size, read_rnd_buffer_size, record_buffer, thread_cache_size, table_cache as needed.

Read/Write Separation

Use a master for writes and replicas for reads; avoid multi‑master setups to reduce complexity.

Caching

Caching can be applied at multiple layers: MySQL internal buffers, data‑access layer (e.g., MyBatis, Hibernate), service layer (write‑through vs write‑back), web layer, and client browsers.

Table Partitioning

MySQL 5.1+ supports native partitioning (RANGE, LIST, HASH, KEY). Partitioning is transparent to applications but does not provide global indexes.

mysql> explain partitions select count(1) from user_partition where id in (1,2,3,4,5);
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user_partition | p1,p4      | range | PRIMARY       | PRIMARY | 8       | NULL |    5 | Using where; Using index |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

Partition benefits include easier data management, faster queries on selective partitions, and the ability to place hot data on faster storage.

Vertical Splitting

Separate tables by logical groups (e.g., user data vs order data) to reduce row size, improve cache utilization, and simplify maintenance, at the cost of additional joins and potential redundancy.

Horizontal Sharding

Distribute rows across multiple databases or tables based on a sharding key. Advantages: eliminates single‑node bottlenecks, improves stability and load capacity. Drawbacks: distributed transaction complexity, cross‑node join performance, and higher operational overhead.

Sharding Principles : shard only when necessary; keep shard count low and evenly distributed; choose sharding rules (range, enum, consistent hash) that match data growth and access patterns; avoid cross‑shard transactions; optimize queries to use indexes; reduce cross‑shard joins via data duplication.

Solution Architectures

Client‑Side Architecture : Modify JDBC/MyBatis/DataSource to manage multiple data sources; low deployment cost but adds load to application servers.

Proxy Architecture : Deploy middleware (e.g., MySQL Fabric, Cobar, MyCat, Vitess) to abstract sharding logic; higher scalability and flexibility but requires extra components.

Comparison Table

(A condensed version of the original table is omitted for brevity; it lists various open‑source sharding solutions, their providers, architecture models, supported databases, and GitHub stars.)

Choosing a Solution

For small‑to‑medium workloads, client‑side sharding like ShardingJDBC is recommended; for large‑scale systems, proxy solutions such as MyCat or Atlas are preferred.

Compatible Horizontally Scalable Databases

Open‑source MySQL‑compatible databases (TiDB, Cubrid) and cloud offerings (Alibaba Cloud PetaData, OceanBase; Tencent Cloud DCDB) provide horizontal scalability but may require more operational effort.

NoSQL Alternatives

When ACID guarantees are not required, consider moving large, low‑relational datasets (logs, monitoring, analytics, unstructured data) to NoSQL stores to achieve unlimited horizontal scaling.