mysql-patterns

MySQL 模式

MySQL 最佳实践速查。详细指南请参阅 mysql-database-reviewer Agent。

适用场景

• 编写 SQL 查询或迁移
• 设计数据库架构
• 排查慢查询
• 配置连接池
• 实现多租户隔离

速查

索引速查表

查询模式	索引类型	示例
WHERE col = value	B 树（默认）	CREATE INDEX idx ON t (col)
WHERE col > value	B 树	CREATE INDEX idx ON t (col)
WHERE a = x AND b > y	复合索引	CREATE INDEX idx ON t (a, b)
MATCH(...) AGAINST(...)	FULLTEXT	CREATE FULLTEXT INDEX ft ON t (col)
长字符串前缀	前缀索引	CREATE INDEX idx ON t (col(50))
地理数据	SPATIAL	CREATE SPATIAL INDEX idx ON t (col)

数据类型速查

用途	正确类型	避免
ID	bigint unsigned	int、随机 UUID 作为 PK
业务代码	varchar(64)	char、text
字符串	varchar(n)	短字符串使用 text
时间戳	datetime	timestamp（2038 年问题）
金额	decimal(10,2)	float、double
标志	tinyint	varchar、boolean 字面量
状态	tinyint	enum（难以修改）
JSON 数据	json	结构化数据使用 text

常见模式

复合索引顺序：

-- Equality columns first, then range columns
CREATE INDEX idx_status_created ON t_order (status, created_at);
-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'
-- Does NOT work for: WHERE created_at > '2024-01-01' alone

前缀索引（长字符串）：

CREATE INDEX idx_url ON t_page (url(100));
-- Index only first 100 characters

生成列 + 索引（JSON）：

ALTER TABLE t_product
ADD COLUMN brand varchar(100) GENERATED ALWAYS AS (attributes->>'$.brand') STORED;
CREATE INDEX idx_brand ON t_product (brand);

UPSERT（ON DUPLICATE KEY）：

INSERT INTO t_setting (user_code, `key`, `value`)
VALUES ('u123', 'theme', 'dark')
ON DUPLICATE KEY UPDATE
  `value` = VALUES(`value`),
  updated_at = NOW();

游标分页：

SELECT * FROM t_product WHERE id > ? ORDER BY id LIMIT 20;
-- O(1) vs OFFSET which is O(n)

队列处理（MySQL 8.0+）：

START TRANSACTION;
SELECT * FROM t_job
WHERE status = 'pending'
ORDER BY created_at LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Process job...
UPDATE t_job SET status = 'processing' WHERE id = ?;
COMMIT;

批量插入：

INSERT INTO t_event (user_code, action) VALUES
  ('u1', 'click'),
  ('u2', 'view'),
  ('u3', 'click');
-- 1 round trip instead of 3

反模式检测

-- Find tables without primary key
SELECT t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.key_column_usage k
  ON t.table_schema = k.table_schema
  AND t.table_name = k.table_name
  AND k.constraint_name = 'PRIMARY'
WHERE t.table_schema = DATABASE()
  AND k.constraint_name IS NULL
  AND t.table_type = 'BASE TABLE';

-- Find slow queries (performance_schema)
SELECT DIGEST_TEXT, COUNT_STAR,
  ROUND(AVG_TIMER_WAIT/1000000000, 2) as avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT > 100000000  -- > 100ms
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

-- Find unused indexes (sys schema)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = DATABASE();

-- Find redundant indexes
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = DATABASE();

-- Check table fragmentation
SELECT table_name, data_free,
  ROUND(data_free/(data_length+index_length+data_free)*100, 2) as frag_pct
FROM information_schema.tables
WHERE table_schema = DATABASE() AND data_free > 1000000
ORDER BY data_free DESC;

配置模板

-- Connection limits
SET GLOBAL max_connections = 200;
SET GLOBAL max_user_connections = 50;

-- Timeouts
SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 600;
SET GLOBAL max_execution_time = 30000;  -- 30s query timeout (MySQL 5.7.8+)

-- Slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- InnoDB settings (my.cnf recommended)
-- innodb_buffer_pool_size = 70% of RAM
-- innodb_log_file_size = 256M
-- innodb_flush_log_at_trx_commit = 1

表模板

CREATE TABLE `t_example` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'PK',
  `example_code` varchar(64) NOT NULL COMMENT 'Business code (UUID)',

  -- Business fields here
  `status` tinyint NOT NULL DEFAULT 1 COMMENT '0-inactive, 1-active',

  -- Audit fields (5 fields)
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
  `created_by` varchar(64) NOT NULL DEFAULT '' COMMENT 'Creator (user_code)',
  `updated_by` varchar(64) NOT NULL DEFAULT '' COMMENT 'Modifier (user_code)',
  `deleted_at` datetime DEFAULT NULL COMMENT 'Soft delete marker',

  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_example_code` (`example_code`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

EXPLAIN 分析

EXPLAIN ANALYZE SELECT * FROM t_order WHERE user_code = 'u123';

指标	问题	解决方案
type: ALL	全表扫描	添加索引
type: index	全索引扫描	检查 WHERE
Using filesort	排序未使用索引	添加 ORDER BY 索引
Using temporary	临时表	优化 GROUP BY
rows 数值过高	选择性差	检查条件

相关内容

• Agent：mysql-database-reviewer - 完整的数据库审查工作流

---

MySQL 5.7+ / 8.0+ 速查