Comprehensive MySQL Single‑Table Optimization and Scaling Strategies

Single‑Table Optimization

When a MySQL table grows large, CRUD performance degrades sharply; the article first advises against premature sharding and recommends using appropriate integer types (TINYINT, SMALLINT, MEDIUMINT, UNSIGNED) and limiting VARCHAR length, avoiding NULL columns, and keeping the field count under twenty.

Field Recommendations

Prefer small integer types over INT and add UNSIGNED when non‑negative.

Allocate only the necessary length for VARCHAR.

Replace string columns with enums or integers.

Use TIMESTAMP instead of DATETIME.

Avoid excessive fields, NULL columns, and store IPs as integers.

Index Guidelines

Create indexes only on columns used in WHERE or ORDER BY, and verify with EXPLAIN.

Do not index columns with low cardinality (e.g., gender) or use full‑field indexes on character columns.

Avoid foreign keys and excessive UNIQUE constraints; manage constraints in application code.

When using composite indexes, keep column order consistent with query predicates and remove unnecessary single‑column indexes.

SQL Query Tuning

Enable slow‑query log to identify bottlenecks.

Avoid column calculations (e.g., SELECT id WHERE age + 1 = 10) and keep statements simple.

Never use SELECT *; replace OR with IN for better performance.

Limit use of functions, triggers, %xxx pattern matching, and large JOIN operations.

Prefer type‑consistent comparisons and avoid != or <> in WHERE clauses.

Use BETWEEN for continuous ranges and LIMIT for pagination.

Engine Selection

MyISAM (pre‑5.1) offers table‑level locking, no transactions, and supports full‑text indexes, while InnoDB (default since 5.5) provides row‑level locking, MVCC, transactions, and foreign keys but lacks built‑in full‑text search (added in 5.6.4). Choose MyISAM for read‑heavy workloads and InnoDB for write‑heavy workloads.

System Tuning Parameters

Key variables include back_log, wait_timeout, max_user_connection, thread_concurrency, skip_name_resolve, key_buffer_size, innodb_buffer_pool_size, innodb_additional_mem_pool_size, innodb_log_buffer_size, query_cache_size, read_buffer_size, sort_buffer_size, read_rnd_buffer_size, record_buffer, thread_cache_size, and table_cache. Adjust them based on monitoring output such as SHOW STATUS to keep cache miss rates low.

Hardware Upgrade

Scale up CPU, memory, and SSD storage according to whether MySQL is CPU‑ or I/O‑bound.

Read/Write Separation

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

Caching Layers

Caching can be applied at MySQL internal settings, data‑access layer (e.g., MyBatis, Hibernate), service layer (write‑through or write‑back), web layer, and client browser.

Table Partitioning

MySQL 5.1+ supports horizontal partitioning (RANGE, LIST, HASH, KEY). Use EXPLAIN PARTITIONS to verify partition pruning. Partitioning enables easier maintenance, faster queries on selective partitions, and distribution across physical devices, but limits include a maximum of 1024 partitions, inability to use foreign keys, and the need for uniform storage engines.

Vertical Sharding

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

Horizontal Sharding

Distribute rows across multiple databases or tables based on a sharding key. Advantages are reduced per‑node data size and improved scalability; disadvantages include complex distributed transactions and cross‑node joins.

Sharding Principles

Only shard when necessary; keep shard count low and evenly distributed.

Choose sharding rules (range, enum, consistent hash) that align with query patterns.

Avoid cross‑shard transactions and large SELECT * queries.

Use data redundancy and partitioning to minimize cross‑shard joins.

Implementation Approaches

Two main architectures:

Client‑side sharding (e.g., ShardingJDBC) – integrates sharding logic into the application data‑access layer.

Proxy‑side sharding (e.g., MyCat, Atlas) – uses an independent middleware to route queries, offering higher flexibility at the cost of additional deployment and latency.

Scalable MySQL‑Compatible Databases

Open‑source options like TiDB and Cubrid, or cloud services such as Alibaba Cloud PetaData, OceanBase, and Tencent Cloud DCDB, provide horizontal scalability while maintaining MySQL protocol compatibility.

NoSQL Alternatives

For workloads that do not require strict ACID guarantees, consider moving large, weakly structured tables to NoSQL stores (logs, monitoring data, analytics) to achieve unlimited horizontal scaling.

References

MySQL official documentation

Various Chinese technical blogs on MySQL optimization

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE ( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);