How to Optimize MySQL Table Design for High Performance

1. Practice

Recently new database tables were added due to requirement changes, but the choice of column attributes and indexes was unclear, so a practical tutorial is written to learn and consolidate this knowledge.

Initial DDL

CREATE TABLE `service_log` (
  `id` bigint(100) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `service_type` int(10) DEFAULT NULL COMMENT '接口类型',
  `service_name` varchar(30) DEFAULT NULL COMMENT '接口名称',
  `service_method` varchar(10) DEFAULT NULL COMMENT '接口方式',
  `serial_no` int(10) DEFAULT NULL COMMENT '消息序号',
  `service_caller` varchar(15) DEFAULT NULL COMMENT '调用方',
  `service_receiver` varchar(15) DEFAULT NULL COMMENT '接收方',
  `status` int(3) DEFAULT '10' COMMENT '状态 10-成功 20-异常',
  `error_message` varchar(200) DEFAULT NULL COMMENT '异常信息',
  `message` text DEFAULT NULL COMMENT '报文内容',
  `create_user` varchar(50) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_user` varchar(50) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '刪除标志',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='接口调用日志';

The following sections explain the problems in the above definition and propose optimizations, mainly referencing "High Performance MySQL, 4th Edition".

2. Optimization and Improvement

Use the smallest appropriate data type . Smaller types consume less disk, memory, and CPU cache, but must still cover the required value range.

Primary Key (id)

Using BIGINT(100) is meaningless; the width does not affect storage. The column should be defined as BIGINT UNSIGNED NOT NULL AUTO_INCREMENT because the id starts from 0 and never negative.

`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键'

Message column

The original TEXT type can store up to 65,535 characters, far more than needed. Changing it to VARCHAR(1000) saves space and improves performance.

`message` varchar(1000) DEFAULT NULL COMMENT '报文内容'

Note: VARCHAR uses 1 byte for lengths ≤255 and 2 bytes otherwise.

MySQL string length definitions refer to character count, not byte count; multibyte character sets like UTF‑8 may use multiple bytes per character.

CHAR vs VARCHAR

CHAR

is fixed‑length and can be more efficient for very short or uniform‑length strings, but MySQL removes trailing spaces. For the service_method column, a CHAR(4) definition is possible, yet an integer enum is more efficient.

`service_method` tinyint DEFAULT NULL COMMENT '接口方式 1-HTTP 2-TCP'

Similarly, service_caller and service_receiver should be TINYINT enums.

`service_caller` tinyint DEFAULT NULL COMMENT '调用方',
`service_receiver` tinyint DEFAULT NULL COMMENT '接收方'

The service_type column fits within SMALLINT because its values never exceed 9999.

`service_type` smallint NOT NULL DEFAULT -1 COMMENT '接口类型'

service_name

never exceeds 15 characters, so reduce its length.

`service_name` varchar(15) DEFAULT NULL COMMENT '接口名称'

The status column only holds 10 or 20, so TINYINT is appropriate.

`status` tinyint DEFAULT 10 COMMENT '状态 10-成功 20-异常'

DATETIME vs TIMESTAMP

DATETIME

stores a larger range (1000‑9999) and is timezone‑independent; TIMESTAMP stores seconds since 1970‑01‑01, is timezone‑aware, and is limited to 1970‑2038. For this application, DATETIME is preferred.

`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
`ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间戳'

To record a pure Unix timestamp, consider using BIGINT to avoid the 2038 problem.

Avoid NULL

Prefer NOT NULL for columns unless a null value is explicitly required; this reduces storage overhead and simplifies indexing.

Boolean Representation

MySQL maps Boolean values to TINYINT (0 = FALSE, non‑zero = TRUE).

Real Number Types

MySQL supports exact DECIMAL and approximate FLOAT / DOUBLE. FLOAT uses 4 bytes, DOUBLE uses 8 bytes. Usually only the type should be specified, not precision, to avoid unwanted rounding.

CREATE TABLE `real_number` (
  `f1` float(7,4) NOT NULL,
  `f2` float NOT NULL,
  `d1` double(7,4) NOT NULL,
  `d2` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实数';

INSERT INTO real_number VALUES (3.1415926535, 3.1415926535, 3.1415926535, 3.1415926535);
SELECT * FROM real_number;

Specifying precision causes rounding; without precision, FLOAT retains about 5 decimal places.

For DECIMAL, the default is DECIMAL(10,0) (integer only). It stores exact values but consumes more space.

CREATE TABLE `decimal_t` (
  `d1` decimal(7,4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='DECIMAL';
INSERT INTO decimal_t VALUES (3.1415926535); -- results in 3.1416
INSERT INTO decimal_t VALUES (1234.1415926535); -- error: out of range

In high‑volume scenarios, using BIGINT with scaled integers can replace DECIMAL to avoid floating‑point inaccuracies and storage overhead.

Final Optimized DDL

CREATE TABLE `service_log` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `service_type` smallint NOT NULL DEFAULT -1 COMMENT '接口类型',
  `service_name` varchar(15) DEFAULT '' COMMENT '接口名称',
  `service_method` tinyint NOT NULL DEFAULT -1 COMMENT '接口方式 1-HTTP 2-TCP',
  `serial_no` int DEFAULT -1 COMMENT '消息序号',
  `service_caller` tinyint DEFAULT -1 COMMENT '调用方',
  `service_receiver` tinyint DEFAULT -1 COMMENT '接收方',
  `status` tinyint DEFAULT 10 COMMENT '状态 10-成功 20-异常',
  `error_message` varchar(200) DEFAULT '' COMMENT '异常信息',
  `message` varchar(1000) DEFAULT '' COMMENT '报文内容',
  `create_user` varchar(50) DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_user` varchar(50) DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_delete` tinyint NOT NULL DEFAULT 0 COMMENT '刪除标志',
  `ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='接口调用日志';