• MySQL入门秘籍：构建可靠数据库系统的实战指南

MySQL入门秘籍：构建可靠数据库系统的实战指南

2025-05-20 10:00:05 栏目：宝塔面板 43 阅读

在软件开发过程中，良好的数据库设计不仅可以提高查询速度和执行SQL的性能，还能增强MySQL的整体性能和可维护性。本文基于公司某位同事整理并授权的数据库规范，结合实际经验，为你提供一份详细的MySQL查询与建表规范指南，并通过正向和反向对比示例加深理解，本文适用于数据库入门和中级用户。

一、基本规范

1.1 存储引擎选择

正向示例：

CREATE TABLE users (    id INT AUTO_INCREMENT PRIMARY KEY,    username VARCHAR(255) NOT NULL) ENGINE=InnoDB;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

  使用InnoDB存储引擎支持事务和行级锁定，确保数据一致性和并发性能。

反向示例：

CREATE TABLE users (    id INT AUTO_INCREMENT PRIMARY KEY,    username VARCHAR(255) NOT NULL) ENGINE=MyISAM;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL
) ENGINE=MyISAM;

  使用MyISAM存储引擎不支持事务和行级锁定，在高并发场景下可能导致数据一致性问题。

1.2 字符集

正向示例：

CREATE TABLE users (    id INT AUTO_INCREMENT PRIMARY KEY,    username VARCHAR(255) NOT NULL) DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL
) DEFAULT CHARSET=utf8mb4;

  使用UTF8mb4支持广泛的字符集，包括emoji等特殊字符。

反向示例：

CREATE TABLE users (    id INT AUTO_INCREMENT PRIMARY KEY,    username VARCHAR(255) NOT NULL) DEFAULT CHARSET=latin1;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL
) DEFAULT CHARSET=latin1;

  使用latin1字符集无法正确存储和显示非拉丁字符，可能导致乱码问题。

1.3 主键和自增ID

正向示例：

CREATE TABLE users (    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',    username VARCHAR(255) NOT NULL COMMENT '用户名');

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(255) NOT NULL COMMENT '用户名'
);

  每个表都有一个明确的主键，便于唯一标识每一行记录。

反向示例：

CREATE TABLE users (    username VARCHAR(255) NOT NULL COMMENT '用户名');

CREATE TABLE users (
    username VARCHAR(255) NOT NULL COMMENT '用户名'
);

  没有主键，导致查询效率低下且难以保证数据一致性。

1.4 大文件存储

正向示例：

存储图片或视频的路径而不是直接存储二进制数据：

CREATE TABLE user_media (    id INT AUTO_INCREMENT PRIMARY KEY,    user_id INT NOT NULL,    media_url VARCHAR(255) NOT NULL);

CREATE TABLE user_media (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    media_url VARCHAR(255) NOT NULL
);

反向示例：

直接在数据库中存储大文件（如图片）：

CREATE TABLE user_media (    id INT AUTO_INCREMENT PRIMARY KEY,    user_id INT NOT NULL,    media BLOB NOT NULL);

CREATE TABLE user_media (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    media BLOB NOT NULL
);

  导致数据库体积膨胀，影响性能。

二、命名规范

2.1 表名

正向示例：

CREATE TABLE d_user_info (    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',    username VARCHAR(255) NOT NULL COMMENT '用户名') COMMENT='张三-2025.03.17 用户基本信息表';

CREATE TABLE d_user_info (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(255) NOT NULL COMMENT '用户名'
) COMMENT='张三-2025.03.17 用户基本信息表';

  表名以业务英文名开头，不超过32个字符，并添加详细备注。

反向示例：

CREATE TABLE userinfo (    id INT AUTO_INCREMENT PRIMARY KEY,    username VARCHAR(255) NOT NULL);

CREATE TABLE userinfo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL
);

  表名过于简单，没有业务说明，难以维护。

2.2 索引命名

正向示例：

CREATE INDEX idx_username ON users (username);CREATE UNIQUE INDEX uniq_email ON users (email);

CREATE INDEX idx_username ON users (username);
CREATE UNIQUE INDEX uniq_email ON users (email);

  索引命名清晰，易于理解和维护。

反向示例：

CREATE INDEX index1 ON users (username);CREATE INDEX index2 ON users (email);

CREATE INDEX index1 ON users (username);
CREATE INDEX index2 ON users (email);

  索引命名不规范，难以区分其用途。

三、数据表设计规范

3.1 字段设置

正向示例：

CREATE TABLE users (    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',    username VARCHAR(255) NOT NULL DEFAULT '' COMMENT '用户名');

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(255) NOT NULL DEFAULT '' COMMENT '用户名'
);

  字段设置为not null时必须有默认值，避免使用text类型。

反向示例：

CREATE TABLE users (    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',    username TEXT COMMENT '用户名');

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username TEXT COMMENT '用户名'
);

  使用TEXT类型，可能导致查询效率低下。

3.2 数值类型

正向示例：

CREATE TABLE products (    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '产品ID',    price DECIMAL(10, 2) NOT NULL COMMENT '价格');

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '产品ID',
    price DECIMAL(10, 2) NOT NULL COMMENT '价格'
);

  使用DECIMAL存储浮点数，确保精度。

反向示例：

CREATE TABLE products (    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '产品ID',    price FLOAT NOT NULL COMMENT '价格');

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '产品ID',
    price FLOAT NOT NULL COMMENT '价格'
);

  使用FLOAT存储浮点数，可能导致精度丢失。

四、索引规范

4.1 主键

正向示例：

CREATE TABLE orders (    order_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',    user_id INT NOT NULL COMMENT '用户ID');

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
    user_id INT NOT NULL COMMENT '用户ID'
);

  使用自增ID作为主键，避免使用UUID等离散值。

反向示例：

CREATE TABLE orders (    order_id VARCHAR(36) PRIMARY KEY COMMENT '订单ID',    user_id INT NOT NULL COMMENT '用户ID');

CREATE TABLE orders (
    order_id VARCHAR(36) PRIMARY KEY COMMENT '订单ID',
    user_id INT NOT NULL COMMENT '用户ID'
);

  使用UUID作为主键，可能导致索引性能下降。

4.2 复合索引

正向示例：

CREATE INDEX idx_name_deleted ON users (name, is_deleted);

CREATE INDEX idx_name_deleted ON users (name, is_deleted);

  根据业务需求创建复合索引，优化查询效率。

反向示例：

CREATE INDEX idx_name ON users (name);CREATE INDEX idx_deleted ON users (is_deleted);

CREATE INDEX idx_name ON users (name);
CREATE INDEX idx_deleted ON users (is_deleted);

  单独为每个字段创建索引，可能导致冗余和低效。

五、SQL开发规范

5.1 代码中禁止使用select *

正向示例：

SELECT id, username FROM users WHERE id = 1;

SELECT id, username FROM users WHERE id = 1;

  明确指定需要查询的字段，减少不必要的数据传输。

反向示例：

SELECT * FROM users WHERE id = 1;

SELECT * FROM users WHERE id = 1;

  使用select *可能导致查询效率低下和不必要的网络传输。

5.2 标量子查询

正向示例：

SELECT u.id, u.username FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.status = 'completed';

SELECT u.id, u.username 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE o.status = 'completed';

使用外连接代替标量子查询，提高查询效率。

反向示例：

SELECT u.id, u.username FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE u.id = o.user_id AND o.status = 'completed');

SELECT u.id, u.username 
FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE u.id = o.user_id AND o.status = 'completed');

  使用标量子查询可能导致性能瓶颈。

5.3 分页优化

正向示例：

SELECT b.id, b.text FROM (SELECT id FROM test a LIMIT 10000, 10) LEFT JOIN test b ON a.id = b.id;

SELECT b.id, b.text 
FROM (SELECT id FROM test a LIMIT 10000, 10) 
LEFT JOIN test b ON a.id = b.id;

  分页查询优化，避免全表扫描。

反向示例：

SELECT id, text FROM test LIMIT 10000, 10;

SELECT id, text FROM test LIMIT 10000, 10;

  直接使用LIMIT可能导致性能问题，尤其是在大数据量的情况下。

结语

通过上述内容的介绍，给大家分享了MySQL数据库设计与管理的最佳实践。从基本规范、命名规范、数据表设计规范、索引规范到SQL开发规范，每一个环节都至关重要。遵循这些规范不仅能提升查询速度和执行SQL的性能，还能增强系统的整体稳定性和可维护性。

本文地址：https://www.yitenyun.com/223.html

上一篇：一文揭秘专为 RAG 打造的高性能开源图向量数据库···
下一篇：聊聊 Redis中 的字典设计与实现

搜索文章

最新文章

2025-06-24

Redis Cluster 高可用原理说的头头是道，这些配置不···
2025-06-24

Redis Plus 来了，性能炸裂！
2025-06-23

数据库管理不再难：MySQL 超详细使用指南
2025-06-20

面试官：MySQL 使用 group by 语句时发现执行很慢，···
2025-06-20

SpringBoot 抢券活动：Redis 热点 Key 三大防护

热门文章

2025-04-27

FastAPI 实战：打造高效的 CRUD 接口（增删改查优秀···
2025-04-27

SpringBoot与Calcite整合，实现多数据源统一查询系统···
2025-04-27

FastAPI + Tortoise-ORM：异步数据库操作优秀实践
2025-05-06

阿里二面：聊聊 MySQL 主从同步方案的优缺点
2025-05-14

面试官：MySQL双主架构有什么优缺点？

所有分类

宝塔面板 （203） Windows （2） Linux （8） 香港服务器 （0） 美国服务器 （0） 香港站群服务器 （0） 美国站群服务器 （0）

Tags

数据库 API FastAPI Calcite 电商系统 MySQL Web 应用 异步数据库 数据同步 ACK 双主架构 循环复制 Deepseek 宝塔面板 Linux宝塔 Docker 生命周期 序列 核心机制 esxi esxi6 root密码不对 无法登录 web无法登录 JumpServer JumpServer安装 堡垒机安装 Linux安装JumpServer Windows Windows server net3.5 .NET 安装出错 宝塔面板打不开 宝塔面板无法访问 SSL 堡垒机 跳板机 HTTPS 查看硬件 Linux查看硬件 Linux查看CPU Linux查看内存 无法访问宝塔面板 Windows宝塔 Mysql重置密码 HTTPS加密 连接控制 机制 ES 协同 Oracle 处理机制 Serverless 无服务器 语言 OB 单机版 Spring SQL 动态查询 scp Linux的scp怎么用 scp上传 scp下载 scp命令 运维 技术 存储 索引 缓存方案 缓存架构 缓存穿透 架构 InnoDB 日志文件 MIXED 3 分页查询 Rsync 响应模型 修改DNS Centos7如何修改DNS RocketMQ 长轮询 配置 监控 HexHub Redis 电商 系统 Linux 安全 服务器 聚簇 非聚簇 MySQL 9.3 异步化 防火墙 黑客 查询 数据 主库 sftp 服务器 参数 group by 自定义序列化 SQLark PostgreSQL 数据库锁 SQLite-Web SQLite 数据库管理工具 开源 存储引擎 管理口 业务 R edis 线程 线上 库存 预扣 Doris SeaTunnel MVCC 人工智能 向量数据库 推荐系统 共享锁 ​Redis 机器学习 推荐模型 加密 场景 流量 工具 PG DBA 数据备份 信息化 智能运维 网络架构 网络配置 AI 助手 Ftp redo log 重做日志 高可用 Canal B+Tree ID 字段 优化 万能公式 • 索引 • 数据库 RDB AOF Redis 8.0 GreatSQL 连接数 Postgres OTel Iceberg Python 库 缓存 核心架构 订阅机制 同城 双活 网络故障 云原生 INSERT COMPACT Hash 字段 不宕机 自动重启 Web 微软 SQL Server AI功能 prometheus Alert IT运维 大模型 SVM Embedding Netstat Linux 服务器 端口 引擎 性能 分库 分表 高效统计 今天这篇文章就跟大家 虚拟服务器 虚拟机 内存 向量库 Milvus OAuth2 Token Entity 开发 单点故障 DBMS 管理系统 MongoDB 容器 sqlmock 崖山 新版本 ZODB 容器化 LRU mini-redis INCR指令 JOIN Undo Log 数据集成工具 窗口 函数 悲观锁 乐观锁 openHalo SpringAI 分布式 集中式 磁盘架构 数据脱敏 加密算法 Redisson 锁芯 QPS 高并发 排行榜 排序 Testcloud 云端自动化 Redka PostGIS 意向锁 记录锁 EasyExcel MySQL8 Recursive 聚簇索引 非聚簇索引 数据类型 模型 启动故障 大表 业务场景 StarRocks 数据仓库 分页 数据结构 分布式架构 分布式锁​ Pottery 原子性 AIOPS R2DBC IT Caffeine CP MCP 开放协议 InfluxDB 1 网络 Web 接口 RAG HelixDB 工具链 事务 Java 发件箱模式 SSH dbt 数据转换工具 部署 池化技术 连接池 数据分类 字典 filelock 对象 读写 速度 服务器中毒 事务隔离 传统数据库 向量化 单线程 Go 数据库迁移 仪表盘 优化器 双引擎 频繁 Codis 分页方案 排版 数据页 Order 线程安全 LLM Crash 代码 List 类型 订单 事务同步 UUIDv7 主键 日志 Pump Ansible

亿腾云，全球数据中心基础服务供应商，APNIC 和 ARIN 会员单位，自有 ASN、IPv4、IPv6 及自营 SonderCloud 云数据中心。

服务器租用

站群服务器

高防服务器

关于我们

Copyright © 2014-2025 yitenyun.com 保留所有权利 网站地图 皖ICP备16015960号-7