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

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

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

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

MySQL三大日志详解：Undo Log、Redo Log和Binlog的作···
2025-06-06

深挖 Spring Boot：8 种数据库连接控制机制，哪个才···
2025-06-06

RAG、向量数据库和LLM搜索：人工智能驱动商业智能的···
2025-06-06

GreatSQL连接数被打满的三种紧急解决方案
2025-06-06

亿级流量，如何保证Redis与MySQL的一致性？失败如何···

热门文章

2025-04-27

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

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

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

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

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

所有分类

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

Tags

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

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

服务器租用

站群服务器

高防服务器

关于我们

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