Master MySQL Partitioning: 5 Strategies, Performance Tests, and Management Tips

1 Review

In the previous section we covered vertical (scale‑up) and horizontal (scale‑out) partitioning and introduced several horizontal partitioning strategies. This part revisits those concepts.

2 Five Horizontal Partition Strategies

2.1 HASH

Partitions data by computing a hash value on one or more columns, e.g., partitioning by the year of a date column.

<code>PARTITION BY HASH(YEAR(createtime))
PARTITIONS 10</code>

2.2 RANGE

Divides rows into continuous, non‑overlapping intervals using VALUES LESS THAN . Example partitions a large table by id into four ranges.

<code>PARTITION BY RANGE(id) (
  PARTITION p0 VALUES LESS THAN (2500001),
  PARTITION p1 VALUES LESS THAN (5000001),
  PARTITION p2 VALUES LESS THAN (7500001),
  PARTITION p3 VALUES LESS THAN MAXVALUE
);</code>

2.3 KEY

An extension of HASH where MySQL generates the hash key automatically.

2.4 LIST

Partitions rows based on a predefined list of discrete values, such as employee position codes.

<code>PARTITION BY LIST(gwcode) (
  PARTITION p0 VALUES IN (46,77,89),
  PARTITION p1 VALUES IN (106,125,177),
  PARTITION p2 VALUES IN (205,219,289),
  PARTITION p3 VALUES IN (302,317,458,509,610)
);</code>

2.5 COMPOSITE

Combines two or more of the above methods, e.g., a RANGE partition further subdivided by HASH.

3 Testing the RANGE Strategy

3.1 Create Base and Partitioned Tables

We create a regular users table and a partitioned version users_part that partitions rows by birth year.

<code>CREATE TABLE users (
  id int(10) unsigned NOT NULL,
  name varchar(100) DEFAULT NULL,
  birth datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE users_part (
  id int(10) unsigned NOT NULL,
  name varchar(100) DEFAULT NULL,
  birth datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(birth)) (
  PARTITION p0 VALUES LESS THAN (1981),
  PARTITION p1 VALUES LESS THAN (1982),
  PARTITION p2 VALUES LESS THAN (1983),
  PARTITION p3 VALUES LESS THAN (1984),
  PARTITION p4 VALUES LESS THAN (1985),
  PARTITION p5 VALUES LESS THAN (1986),
  PARTITION p6 VALUES LESS THAN (1987),
  PARTITION p7 VALUES LESS THAN (1988),
  PARTITION p8 VALUES LESS THAN (1989),
  PARTITION p9 VALUES LESS THAN MAXVALUE
);</code>

3.2 Initialize Data

A stored procedure inserts ten million rows with random birth dates in the 1980s.

<code>DROP PROCEDURE IF EXISTS init_users_part;
DELIMITER $$
CREATE PROCEDURE init_users_part()
BEGIN
  DECLARE srt int DEFAULT 0;
  WHILE srt < 10000000 DO
    INSERT INTO users_part VALUES (srt, CONCAT('username_', srt), ADDDATE('1980-01-01', RAND()*3650));
    SET srt = srt + 1;
  END WHILE;
END $$
DELIMITER ;
CALL init_users_part();</code>

3.3 Sync Data to Full Table

<code>INSERT INTO users SELECT * FROM users_part;</code>

3.4 Query Performance Comparison

<code>SELECT COUNT(*) FROM users_part WHERE birth > '1986-01-01' AND birth < '1986-12-31';  -- 0.335 sec
SELECT COUNT(*) FROM users WHERE birth > '1986-01-01' AND birth < '1986-12-31';      -- 5.187 sec</code>

The partitioned table is roughly ten times faster.

3.5 EXPLAIN Analysis

<code>EXPLAIN SELECT COUNT(*) FROM users_part WHERE birth > '1986-01-01' AND birth < '1986-12-31';
EXPLAIN SELECT COUNT(*) FROM users WHERE birth > '1986-01-01' AND birth < '1986-12-31';</code>

The partitioned query scans only the relevant partition (e.g., p7), while the non‑partitioned query performs a full table scan.

3.6 Index Optimization

Creating an index on the birth column improves both tables, but the partitioned table still retains an advantage.

<code>CREATE INDEX idx_user ON users(birth);
CREATE INDEX idx_user_part ON users_part(birth);</code>

3.7 Cross‑Partition Queries

When the query range spans multiple partitions, performance degrades proportionally.

<code>SELECT COUNT(*) FROM users_part WHERE birth > '1986-01-01' AND birth < '1987-12-31';  -- 1.914 sec
SELECT COUNT(*) FROM users WHERE birth > '1986-01-01' AND birth < '1987-12-31';      -- 3.871 sec</code>

3.8 Summary of Findings

Partitioned and non‑partitioned tables occupy similar disk space.

Without an index, partitioning yields a large speed advantage.

With an index, the gap narrows but partitioning remains faster, especially as data volume grows.

For large datasets, partitioning is recommended regardless of indexing.

Increasing myisam_max_sort_file_size can improve partition rebuild performance.

Design partitions carefully to avoid frequent cross‑partition scans.

4 Detailed Partition Strategies

4.1 HASH

HASH partitions distribute rows evenly across a fixed number of partitions based on a hash of the specified expression.

<code>/*Hash*/
DROP TABLE IF EXISTS t_userinfo;
CREATE TABLE t_userinfo (
  id int(10) unsigned NOT NULL,
  personcode varchar(20),
  personname varchar(100),
  depcode varchar(100),
  depname varchar(500),
  gwcode int(11),
  gwname varchar(200),
  gravalue varchar(20),
  createtime DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(YEAR(createtime))
PARTITIONS 4;
</code>

4.2 RANGE

RANGE partitions assign rows to intervals defined by VALUES LESS THAN .

<code>/*Range*/
DROP TABLE IF EXISTS t_userinfo;
CREATE TABLE t_userinfo (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(gwcode) (
  PARTITION p0 VALUES LESS THAN (101),
  PARTITION p1 VALUES LESS THAN (201),
  PARTITION p2 VALUES LESS THAN (301),
  PARTITION p3 VALUES LESS THAN MAXVALUE
);
</code>

4.3 LIST

LIST partitions map discrete column values to specific partitions.

<code>/*List*/
DROP TABLE IF EXISTS t_userinfo;
CREATE TABLE t_userinfo (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST(gwcode) (
  PARTITION p0 VALUES IN (46,77,89),
  PARTITION p1 VALUES IN (106,125,177),
  PARTITION p2 VALUES IN (205,219,289),
  PARTITION p3 VALUES IN (302,317,458,509,610)
);
</code>

4.4 KEY

KEY is similar to HASH but uses MySQL’s internal hash function on integer columns.

<code>/*key*/
DROP TABLE IF EXISTS t_userinfo;
CREATE TABLE t_userinfo (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY(gwcode)
PARTITIONS 4;
</code>

4.5 Composite (Sub‑partition)

Sub‑partitions further split each RANGE or LIST partition using another method such as HASH.

<code>DROP TABLE IF EXISTS t_userinfo;
CREATE TABLE t_userinfo (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(id) SUBPARTITION BY HASH(id % 4) SUBPARTITIONS 2 (
  PARTITION p0 VALUES LESS THAN (5000000),
  PARTITION p1 VALUES LESS THAN MAXVALUE
);
</code>

5 Partition Management

5.1 Drop Partition

<code>ALTER TABLE users_part DROP PARTITION p1;</code>

5.2 Reorganize Partitions

5.2.1 RANGE

<code>ALTER TABLE users_part REORGANIZE PARTITION p0, p1 INTO (PARTITION p0 VALUES LESS THAN (5000000));</code>

5.2.2 LIST

<code>ALTER TABLE users_part REORGANIZE PARTITION p0, p1 INTO (PARTITION p0 VALUES IN (1,4,5,8,9,12,13,101,555));</code>

5.2.3 HASH/KEY

<code>ALTER TABLE users_part REORGANIZE PARTITION COALESCE PARTITION 2;</code>

5.3 Add Partitions

5.3.1 Add RANGE Partition

<code>ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19) DATA DIRECTORY = '/data8/data' INDEX DIRECTORY = '/data8/idx');</code>

5.3.2 Add HASH/KEY Partitions

<code>ALTER TABLE users_part ADD PARTITION PARTITIONS n;</code>

5.3.3 Add Partition to Existing Table

<code>ALTER TABLE users_part PARTITION BY RANGE(MONTH(birth)) (
  PARTITION p0 VALUES LESS THAN (1),
  PARTITION p1 VALUES LESS THAN (2),
  ...
  PARTITION p12 VALUES LESS THAN (13)
);
</code>

6 Removing Primary‑Key Partition Restriction

MySQL requires partition columns to be part of the primary key. Workarounds include partitioning by the primary key itself or creating a composite primary key that includes the partition column.

<code>ALTER TABLE users_part PARTITION BY HASH(id) PARTITIONS 4;
ALTER TABLE users_part DROP PRIMARY KEY;
ALTER TABLE users_part ADD PRIMARY KEY(id, gwcode);
</code>