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

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

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

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

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

服务器租用

站群服务器

高防服务器

关于我们

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