Comprehensive Guide to MySQL Optimization: Table Design, Indexes, Partitioning, and Read/Write Splitting

Overview

MySQL optimization is a comprehensive technology; SQL optimization is only one part. The main aspects include:

Reasonable table design (conforming to the three normal forms).

Adding indexes (ordinary, primary, unique, full‑text).

Partitioning techniques (horizontal and vertical).

Read‑write separation.

Reasonable Table Design

In table design, the three normal forms must be satisfied under certain conditions. The first normal form requires atomic columns, which relational databases (MySQL, Oracle, DB2, SQL Server) automatically meet.

The second normal form is achieved by ensuring each record is unique, typically by defining a primary key.

The third normal form eliminates redundant data; if a field can be derived from other fields, it should not be stored separately. However, denormalization may be applied deliberately to improve performance by keeping some redundant fields.

Example of a design that violates the third normal form (redundant data) is shown below:

After normalization, the design would look like this:

Note: Denormalization (anti‑third normal form) may be used when performance gains outweigh the cost of redundancy, such as adding frequently queried fields to a table with a one‑to‑many relationship.

SQL Optimization

SQL optimization mainly involves adding indexes. The four common index types are primary key, unique, full‑text, and ordinary indexes.

1. Primary Key Index

When a column is defined as the primary key, it automatically becomes a primary index. Example:

create table user (
  id int unsigned primary key auto_increment,
  name varchar(32) not null default ''
);

If the primary key was not defined during table creation, it can be added later:

alter table 表名 add primary key (列名);
// example
alter table user add primary key (id);

2. Ordinary Index

Ordinary indexes are usually created after the table is created:

create table user (
  id int unsigned,
  name varchar(32)
);
create index 索引名 on 表 (列1, 列2);

3. Full‑Text Index

Full‑text indexes are used for searching text content, mainly effective with MyISAM tables. Example:

CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT (title, body)
) engine=myisam charset=utf8;

Using the index:

select * from articles where match(title, body) against('keyword');

Key points:

Full‑text works only with MyISAM in MySQL.

MySQL's built‑in full‑text works well for English; for Chinese, external engines like Sphinx/Coreseek are needed.

Stop words are ignored during indexing.

4. Unique Index

A column defined with a UNIQUE constraint becomes a unique index. Example:

create table user (id int primary key auto_increment, name varchar(32) unique);

Querying indexes:

desc 表名; // does not show index names
show index from 表名;
show keys from 表名;

Dropping indexes:

alter table 表名 drop index 索引名;
// drop primary key
alter table 表名 drop primary key;

Index Usage Considerations

Indexes consume disk space and can slow DML operations. Create indexes only on columns that:

Are frequently used in WHERE clauses.

Do not have a very low cardinality (i.e., not only a few distinct values).

Are not updated frequently.

Use EXPLAIN to see how MySQL will execute a query.

SQL Tips

When using GROUP BY, MySQL also sorts the result set by default. Adding ORDER BY NULL after GROUP BY can skip the sorting step.

In many cases a JOIN can replace a sub‑query, avoiding the creation of temporary tables.

select * from dept, emp where dept.deptno = emp.deptno;
// replace with
select * from dept left join emp on dept.deptno = emp.deptno;

Choosing the Right MySQL Storage Engine

MyISAM : Suitable for tables with low transaction requirements and heavy read/write of simple data (e.g., forum posts).

InnoDB : Recommended for transactional workloads and critical data (e.g., orders, user accounts). If using MyISAM, remember to defragment regularly.

Partitioning Techniques

Why partition? Large rows increase I/O; moving large fields to a separate table reduces I/O for common queries. Very large tables slow down queries. Data with natural independence (e.g., per region or period) can be split. Both horizontal and vertical partitioning are available.

Vertical Partitioning

Splits a table by columns, often moving large or rarely accessed fields to a separate table, reducing row size and I/O.

Example: separate content from a blog tbl_articles table into tbl_articles_detail.

Advantages: smaller rows, faster reads, simpler maintenance. Disadvantages: redundant primary keys, need for joins, more complex transactions.

Horizontal Partitioning

Splits a table by rows when the row count exceeds several million or the table size exceeds ~10 GB. Data is distributed across multiple tables with roughly equal size.

Drawbacks: added complexity, need for UNION queries, and potential performance overhead.

Typical standards for horizontal splitting include:

Sharding user tables by phone number prefix (e.g., user183, user150).

Sharding by user ID modulo a number.

Splitting order tables by order date.

Read/Write Separation

To implement MySQL read/write separation, first configure master‑slave replication. Then you can:

Configure multiple data sources.

Use a MySQL proxy middleware.

Master‑Slave Replication Principle

Replication synchronizes data changes from the master to the slaves, allowing the master to handle write (transactional) queries while slaves handle read (SELECT) queries.

Read/Write Separation Principle

Writes are directed only to the master; reads are directed only to the slaves. The replication mechanism keeps the slaves up‑to‑date with the master’s changes.

---

·end·

—If this article helped you, please share it with your friends—

We enjoy learning together!