第22章：数据库的设计原则和日常SQL编写_sql数据表设计基本原则-CSDN博客

本文链接：https://blog.csdn.net/jbkjhji/article/details/131627217

1.数据表的设计原则有哪些?

原则：三少一多，不是绝对的，有时增加冗余字段提高效率。

①数据表个数越少越好：实体和联系设计的简洁，方便理解和操作

②表的字段越少越好：数据冗余低

③表的联合主键字段个数越少越好：联合主键字段多，占用索引空间越大。

④主键和逻辑外键越多越好。数据表之间独立性，提升之间关联使用率。

2.数据库的编写建议

①库的名称必须控制在32个字符以内，只能使用英文字母、数字和下划线，建议以英文字母开头。

②库名中英文一律小写，不同单词采用下划线分割。须见名知意。

③库的名称格式：业务系统名称_子系统名。

④库名禁止使用关键字（如type,order等）。

⑤创建数据库时必须显式指定字符集，并且字符集只能是utf8或者utf8mb4。

CREATE DATABASE crm_fund DEFAULT CHARACTER SET 'utf8' ;

⑥对于程序连接数据库账号，遵循权限最小原则

使用数据库账号只能在一个DB下使用，不准跨库。程序使用的账号原则上不准有drop权限。

⑦临时库以 tmp_ 为前缀，并以日期为后缀；

备份库以 bak_ 为前缀，并以日期为后缀。

3.数据库表和列的编写建议

①表和列的名称必须控制在32个字符以内，表名只能使用英文字母、数字和下划线，建议以英文字母开头。

②表名、列名一律小写，不同单词采用下划线分割。须见名知意。

③表名要求有模块名强相关，同一模块的表名尽量使用统一前缀。比如：crm_fund_item

④创建表时必须显式指定字符集为utf8或utf8mb4。

⑤表名、列名禁止使用关键字（如type,order等）。

⑥创建表时必须显式指定表存储引擎类型。如无特殊需求，一律为InnoDB

⑦建表必须有comment。注释

⑧字段命名应尽可能使用表达实际含义的英文单词或缩写。如：公司 ID，不要使用corporation_id, 而用corp_id 即可。

⑨禁止在数据库中存储图片、文件等大的二进制数据

⑩表中所有字段尽量都是 NOT NULL 属性，业务可以根据需要定义 DEFAULT值。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题

⑾所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。

⑿中间表（或临时表）用于保留中间结果集，名称以 tmp_ 开头。

备份表用于备份或抓取源表快照，名称以 bak_ 开头。中间表和备份表定期清理。

【规范】

CREATE TABLE user_info ( 
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键', 
`user_id` bigint(11) NOT NULL COMMENT '用户id', 
`username` varchar(45) NOT NULL COMMENT '真实姓名', 
`email` varchar(30) NOT NULL COMMENT '用户邮箱', 
`nickname` varchar(45) NOT NULL COMMENT '昵称', 
`birthday` date NOT NULL COMMENT '生日', 
`sex` tinyint(4) DEFAULT '0' COMMENT '性别', 
`short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字', 
`user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址', 
`user_register_ip` int NOT NULL COMMENT '用户注册时的源ip', 
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', 
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP COMMENT '修改时间', 
`user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态，1为通过，2为审核中，3为未 
通过，4为还未提交审核',
PRIMARY KEY (`id`), 
UNIQUE KEY `uniq_user_id` (`user_id`), 
KEY `idx_username`(`username`), 
KEY `idx_create_time_status`(`create_time`,`user_review_status`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户基本信息'

4.索引的编写建议

①InnoDB表必须主键为id int/bigint auto_increment，且主键值禁止被更新。

②InnoDB和MyISAM存储引擎表，索引类型必须为 BTREE。

③主键的名称以 pk_ 开头，唯一键以 uni_ 或 uk_ 开头，普通索引以 idx_开头，一律使用小写格式，以字段的名称或缩写作为后缀。

④多单词组成的columnname，取前几个单词首字母，加末单词组成column_name。如:sample 表 member_id 上的索引：idx_sample_mid。

⑤单个表上的索引个数不能超过6个

⑥在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面

⑦在多表 JOIN 的SQL里，保证被驱动表的连接列上有索引，这样JOIN 执行效率最高。

⑧建表或加索引时，保证表里互相不存在冗余索引。比如：如果表里已经存在key(a,b)，

则key(a)为冗余索引，需要删除

5.SQL的编写

①SELECT语句必须指定具体字段名称，禁止写成 *。