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

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

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

在软件开发过程中，良好的数据库设计不仅可以提高查询速度和执行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-08-16

运维必杀技：关闭这些默认服务，让你的服务器飞起来···
2025-08-16

千万别忽视！TIME_WAIT 堆积背后的秘密和应对技巧
2025-08-16

数据库优化实战：25 个 SQL 性能调优技巧，查询速度···
2025-08-16

数据库事务与锁机制：十个核心场景 + SQL 实战案例
2025-08-16

2025 TDBC 大会归来...

热门文章

2025-04-27

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

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

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

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

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

所有分类

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

Tags

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

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

服务器租用

站群服务器

高防服务器

关于我们

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