1. 前言
1.1.背景
● 数据库被广泛应用：各类业务系统、信息化系统，数据仓库、数据分析、数据挖掘。
● 数据使用中存在的常见问题。
● 了解基本、强制的使用规范，有助于更好的使用数据库。
● 不同岗位角色对数据库的设计要求。
1.2.数据库使用常见问题
● 死琐，整个库无法使用。
● 查询响应慢。
● 数据误删、误改，无法恢复。
● 扩展性差、修改代价很大。
● 数据质量问题。
● 统计结果有偏差，关联条件问题、小数点问题。
● SQL很长、可读性差、难优化、难维护。
1.3.目标
● 规范很多，实施很难，自我要求，养成习惯，方便你我，快乐工作。
● 规范很多，结合实际和经验，选择性讲解和沟通，选择性使用。
● 从应用、运维、安全、性能、故障等方面出发，少踩坑、提前避免。
1.4.期望
● 基础应用、入门普及。毕业生、数据分析、数据开发。
● 常见问题
● 基本使用规范
● 强制约束
● 避免踩坑
2.数据库命名规范
数据库设计时，必须先制定内部的命名约定，统一命名规范，会为后期的使用、维护、扩展带来很大的便利性。命名规范是指数据库对象的命名规则进行约定。
命名规范，网上文档很多，各团队也有各自的命名约定。这里只选择一些最基础的、或做一些特别补充。
2.1.数据库对象全局命名规范
数据库对象是数据库的组成部分，常见的有以下几种：表（Table ）、索引（Index）、视图（View）、图表（Diagram）、缺省值（Default）、规则（Rule）、触发器（Trigger）、存储过程（Stored Procedure）、 用户（User）等。
● 避免用MySQL的保留字如：backup、call、group等。
● 命名只能使用英文字母、数字、下划线，以英文字母开头。
● 命名使用具有意义的英文词汇，词汇中间以下划线分隔。
● 不建议使用驼峰式命名方式。
● 长度控制，不超过30或50个字符。
2.2.数据库命名规范
● 数据库命名一般为项目名称+代表库含义的简写。如：以md_开头，后接业务或功能模块的名称。
2.3.表命名规范
● 表名，常以t_开头，t代表table的意思，命名规则t_xxx_xxx，如：t_user_payinfo。
● 临时库、表名：以tmp为前缀，并以日期为后缀。
● 备份库、表名：以bak为前缀，并以日期为后缀等。
● 同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义。
2.4.字段命名规范
● 字段名，常以f_开头，f代表field的意思，命名规则f_xxx_xxx。如：f_sale_type。
● 各表之间相同意义的字段必须同名，比如a表和b表都有商品价格，应该统一为f_item_price，不一致会很混乱。
● 字段名的修改代价很大，字段名要慎重考虑。
● 字段名称一般使用名词（如：f_username）或动宾短语（f_pay_status）。
2.5.字段描述规范
● 字段有枚举值，要全部写出来。并且用“-”区分原始值和实际字段的含义。
● 如果枚举值过多，可通过维表来查询展示。
● 涉及到量纲的字段，需要加上单位。
● 布尔值统一规范。0-否；1-是。
● 约定好枚举类型的枚举值，如：jd-京东，tm-天猫，pdd-拼多多。
3.数据库对象设计规范
只从数据库对象设计的角度进行说明，不包括针对业务的具体设计思路。
3.1.数据库设计规范
3.1.1.建库语法
CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>]
[[DEFAULT] COLLATE <校对规则名>];
语法说明：
● <数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。
● IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
● [DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
● [DEFAULT] COLLATE：指定字符集的默认校对规则。
● MySQL 的字符集（CHARACTER）和校对规则（COLLATION）是两个不同的概念。字符集是用来定义 MySQL 存储字符串的方式，校对规则定义了比较字符串的方式。
3.1.2.建库语句
create database test_db;
create database test_db DEFAULT CHARACTER SET utf8mb4;
create database test_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
3.1.3.查看数据库的定义声明
MySQL [(none)]> show create database test_db;
+-----------------+--------------------------------------------------------------------------+
| Database | Create Database |
+-----------------+--------------------------------------------------------------------------+
| test_db | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------------+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [(none)]> show create database dlink;
+-----------------+--------------------------------------------------------------------------+
| Database | Create Database |
+-----------------+--------------------------------------------------------------------------+
| dlink | CREATE DATABASE `dlink` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-----------------+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.2.字符集
3.2.1.常见的MYSQL字符集
字符集 | 是否定长 | 编码方式 | 说明 |
ASCII | 是 | 单字节7位编码 | 最早的奠基性字符集 |
latin1 | 是 | 单字节8位编码 | 西欧字符集 |
GBK | 是 | 双字节编码 | 中文字符集 |
utf8 | 否 | 1~3字节 | 最常用的Unicode字符集 |
utf8mb4 | 否 | 1~4字节 | utf8 most bytes 4 |
3.2.2.utf8和utf8bm4的区别
字符集 | 英文、数字 | 西欧 | 常用汉字 | 特殊字符（表情符、生僻汉字） |
utf8 | 1字节 | 2字节 | 3字节 | 不支持 |
utf8mb4 | 1字节 | 2字节 | 3字节 | 4字节 |
3.2.3.查看字符集
MySQL [(none)]> SHOW VARIABLES LIKE 'character%';
+--------------------------+-----------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /data/mysql/share/charsets/ |
+--------------------------+-----------------------------+
8 rows in set (0.00 sec)
3.2.4.如何选择字符集
● 如果数据只有英文或数字，则可以选择ASCII、latin1、utf8、utf8mb4。
● 如果数据有中文，可以选择utf8、utf8mb4。
● 如果不确定字符串包含哪些字符，最好统一使用utf8mb4，这个字符集能包含目前所有的字符，避免因为超出utf8范围而出错。
3.3.校对规则(COLLATION)
3.3.1.作用
对于mysql中的字符类型的列，如VARCHAR，CHAR，TEXT类型，都需要有一个COLLATE类型，来告知mysql如何对该列进行排序和比较。
3.3.2.影响
● COLLATE会影响到ORDER BY语句的顺序。
● 会影响到WHERE条件中大于、小于、等于操作的筛选结果。
● 会影响DISTINCT、GROUP BY、HAVING语句的查询结果。
● 另外，mysql建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。
总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。
3.3.3.版本说明
在mysql8.0以下版本中，默认的CHARSET是Latin1，默认的COLLATE是latin1_swedish_ci。如果不指定字符集，容易出现中文乱码的情况。
从mysql8.0开始，默认的CHARSET已经改为了utf8mb4，默认的COLLATE改为了utf8mb4_0900_ai_ci。
3.3.4.查看mysql支持的COLLATION
字符集和校对规则是一对多的关系，查看命名：
MySQL [(none)]> show collation like 'utf8mb4%';
+------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+------------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
| utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 |
| utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 |
| utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 |
+------------------------+---------+-----+---------+----------+---------+
26 rows in set (0.00 sec)
3.3.5.如何选择COLLATION
校对规则_ci对大小写不敏感（Case Insensitive），_cs对大小写敏感（Case Sensitive），_bin（二进制）是基于字符编码的值而与language无关。
国内比较常用的是utf8mb4_general_ci（默认）、utf8mb4_unicode_ci、utf8mb4_bin。
utf8mb4_bin的比较方法，是直接将所有字符看作二进制串，然后从最高位往最低位比对。它是区分大小写的。
而utf8mb4_unicode_ci和utf8mb4_general_ci对于中文和英文来说，其实是没有任何区别的。
例子：在进行字符集比较的时候，如果校对规则是_ci，则大写和小写的字符串是相等的，如果校对规则是_cs、_bin对大小写敏感，所以大写和小写的字符串是不相等的。
mysql> create table t4(f_id int,f_name varchar(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
mysql> select * from t4;
+------+--------+
| f_id | f_name |
+------+--------+
| 1 | a |
| 2 | A |
+------+--------+
mysql> select * from t4 where f_name='a';
+------+--------+
| f_id | f_name |
+------+--------+
| 1 | a |
| 2 | A |
+------+--------+
mysql> create table t6(f_id int,f_name varchar(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
mysql> select * from t6;
+------+--------+
| f_id | f_name |
+------+--------+
| 1 | a |
| 2 | A |
+------+--------+
mysql> select * from t6 where f_name='a';
+------+--------+
| f_id | f_name |
+------+--------+
| 1 | a |
+------+--------+
另外，如果两个表的校对规则不一致（utf8mb4_general_ci和utf8mb4_unicode_ci），在通过字符串关联的时候，会报错。
mysql> create table t5(f_id int,f_name varchar(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
mysql> select * from t5;
+------+--------+
| f_id | f_name |
+------+--------+
| 1 | a |
| 2 | A |
+------+--------+
mysql> select * from t4 left join t5 on t4.f_name=t5.f_name;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
-- 解决办法：临时转换COLLATION：
mysql> select * from t4 left join t5 on t4.f_name COLLATE utf8mb4_general_ci=t5.f_name;
+------+--------+------+--------+
| f_id | f_name | f_id | f_name |
+------+--------+------+--------+
| 1 | a | 1 | a |
| 2 | A | 1 | a |
| 1 | a | 2 | A |
| 2 | A | 2 | A |
+------+--------+------+--------+
-- 但是，utf8mb4_bin类型的字段可以与utf8mb4_general_ci和utf8mb4_unicode_ci类型的字段关联：
mysql> select * from t4 left join t6 on t4.f_name=t6.f_name;
+------+--------+------+--------+
| f_id | f_name | f_id | f_name |
+------+--------+------+--------+
| 1 | a | 1 | a |
| 2 | A | 2 | A |
+------+--------+------+--------+
3.3.6.COLLATE设置级别及其优先级
优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。
即：列上所指定的COLLATE可以覆盖表上指定的COLLATE，表上指定的COLLATE可以覆盖库级别的COLLATE。
如果没有指定，则继承上一级的设置。即列上面没有指定COLLATE，则该列的COLLATE和表上设置的一样。
建议从高级别上设置，统一使用字符集utf8mb4，校对规则utf8mb4_bin。
COLLATE查看：
-- 实例级别
MySQL [(none)]> SHOW VARIABLES LIKE 'collation_connection%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
+----------------------+--------------------+
1 row in set (0.00 sec)
-- 库级别
MySQL [(none)]> CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 表级别
MySQL [(none)]> CREATE TABLE (
……
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- 字段级别
MySQL [(none)]> CREATE TABLE (
`field1` VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
……
) ……
-- SQL语句级别，不常用。
MySQL [(none)]> SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;
MySQL [(none)]> SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;
3.4.存储引擎
3.4.1.作用
存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是基于表的，而不是基于库的。
Oracle，SqlServer等数据库只有一种存储引擎。
MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。
Clickhouse的引擎类型更丰富。
3.4.2.常用命令
查看当前数据库支持的存储引擎：
MySQL [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
3.4.3.查看当前实例默认的存储引擎
MySQL [(none)]> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)
3.4.4.InnoDB和MyISAM对比
A）MyISAM
在 MySQL 5.1 版本及之前的版本，MyISAM 是默认的存储引擎。
MyISAM 存储引擎不支持事务和外键，所以访问速度比较快。如果应用主要以读取和写入为主，只有少量的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择 MyISAM 存储引擎是非常适合的。
MyISAM 是在 Web 数据仓储和其他应用环境下最常使用的存储引擎之一。
B）InnoDB
MySQL 5.5 版本之后默认的事务型引擎修改为 InnoDB。
InnoDB 存储引擎在事务上具有优势，即支持具有提交、回滚和崩溃恢复能力的事务安装，所以比 MyISAM 存储引擎占用更多的磁盘空间。
如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择。
InnoDB 存储引擎除了可以有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。
C）使用建议：根据业务的应用场景，以及运维安全方面的考虑，建议使用：InnoDB引擎。
D）引擎特点对比
InnoDB | MyISAM |
行级锁 支持事务，适合处理大量短期事务 读写阻塞与事务隔离级别相关 可缓存数据和索引 支持聚簇索引 崩溃恢复性更好 支持MVCC高并发 从MySQL5.5后支持全文索引 从MySQL5.5.5开始为默认的数据库引擎 | 不支持事务 表级锁定 读写相互阻塞，写入不能读，读时不能写 只缓存索引 不支持外键约束 不支持聚簇索引 读取数据较快，占用资源较少 不支持MVCC（多版本并发控制机制）高并发 崩溃恢复性较差 MySQL5.5.5 前默认的数据库引擎 |
3.5.表设计规范
先看一下，我们这边表的创建语句：
MySQL [report]> show table status;
MySQL [report]> show create table t_menu;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_menu | CREATE TABLE `t_menu` (
`f_menu_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单编号',
`f_menu_name` varchar(66) NOT NULL COMMENT '菜单名称',
`f_menu_type` varchar(36) NOT NULL COMMENT '菜单类型，分系统管理，统计分析，统计设计',
`f_menu_url` varchar(4000) NOT NULL DEFAULT '#' COMMENT '菜单访问的url',
`f_father_menu_id` int(11) NOT NULL DEFAULT '0' COMMENT '父菜单编号',
`f_order_by` int(11) NOT NULL DEFAULT '0' COMMENT '菜单排序',
`f_is_valid` smallint(6) NOT NULL DEFAULT '0' COMMENT '是否有效，1：是，0：无效或已删除',
`f_remark` varchar(4000) DEFAULT NULL COMMENT '备注信息',
`f_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`f_create_name` varchar(36) DEFAULT NULL COMMENT '记录创建人',
`f_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录创建时间',
`f_modify_name` varchar(36) DEFAULT NULL COMMENT '记录创建人',
PRIMARY KEY (`f_menu_id`),
KEY `fk_user_ref_dept` (`f_father_menu_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=914 DEFAULT CHARSET=utf8 COMMENT='系统菜单表' |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3.5.1.主键要求
每个表都应该有自己的主键，且尽量让表内主键保持递增，可使用自增ID或UUID。
Mysql主从同步，要求每个表都有对应的主键。
1）自增ID的实现
`f_menu_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单编号',
2）Mysql UUID的实现
MySQL [report]> select uuid() as uuid, replace(uuid(),"-","") as uid;
+--------------------------------------+----------------------------------+
| uuid | uid |
+--------------------------------------+----------------------------------+
| edaa926e-6970-11ed-b179-525400f9b16e | edaa920c697011edb179525400f9b16e |
+--------------------------------------+----------------------------------+
1 row in set (0.00 sec)
3）代码自定义实现
public class mainTest {
public static void main(String[] args) {
UUID uuid = UUID.randomUUID();
String strUUID = uuid.().replaceAll("-", "");
System.out.println(strUUID);
}
}4）优缺点对比
自增id | uuid |
优点 ● 数字类型，占用空间小，写入速度相对快 ● 数据库自动增量排序，对检索有利，读速度快 ● 代码入库过程中，可以不指定id值，数据库自增 缺点 ● 因自动增长，当手动插入的时候会出现麻烦，主键冲突的情况会有发生 ● 分表之后控制不好会出现主键重复现象 ● 新老数据合并，要是新数据主键也是数字类型，想新老数据区分会出现一些冲突 | 优点 ● 全局唯一性，不用担心重复的现象，对数据库的分库分表，数据的拆分、合并比较友好 缺点 ● 字符串类型，占用空间比较大，读写速度慢，而且索引会随着数据量的增加越来越难用 |
3.5.2.设计并创建有效索引
1）索引设计要求
● 建表时，创建好对应的有效查询索引。
● 设置唯一ID的列作为主键索引。
● 使用唯一索引（可多个字段组合），避免表存在重复数据。
● 不要过度索引，并不一定要为每个查询都创建对应的索引。
● 每个索引都会占用一定的磁盘空间，索引太多可能影响优化器找到最佳索引。
● 索引会影响写速度，但不能因此而不设置索引。针对大表，搜索时间消耗远大于写表时间消耗。
2）索引应用
● 简单的SQL查询慢时，大多是索引问题。
● 可以通过数据库监控每个表的高频查询语句、慢查询语句，针对此类场景进行索引优化设计。
● 通过mysql自带的explain工具，分析你的查询语句或是结构的性能瓶颈，及索引使用情况。
3）查看表和索引的文件大小
MySQL [(none)]> select table_schema,table_name,table_rows,data_length,index_length
-> from information_schema.tables
-> order by data_length desc, index_length desc
-> limit 10;
+-------------------+-----------------------------------+------------+-------------+--------------+
| table_schema | table_name | table_rows | data_length | index_length |
+-------------------+-----------------------------------+------------+-------------+--------------+
+-------------------+-----------------------------------+------------+-------------+--------------+
10 rows in set, 54 warnings (0.17 sec)
3.5.3.必备字段
表必备字段：id, f_create_time、f_modify_time。表辅助字段：f_create_name、f_modify_name、f_remark
`f_menu_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单编号',
...
`f_remark` varchar(4000) DEFAULT NULL COMMENT '备注信息',
`f_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`f_create_name` varchar(36) DEFAULT NULL COMMENT '记录创建人',
`f_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录创建时间',
`f_modify_name` varchar(36) DEFAULT NULL COMMENT '记录创建人',
1）id, f_create_time、f_modify_time：定义对应的默认值，在insert、update时，自动生成记录的插入或修改时间，不需要为其作特殊处理。
2）f_create_name、f_modify_name：针对配置表或信息修改表，方便对记录修改的跟踪。
3）f_remark：备用字段，便于对库表数据处理的临时标志和注释说明。
3.5.4.记录删除标志
不用物理删除，即尽量避免用delete语句、drop、truncate命令等;通过软删除处理，即通过额外的字段标明记录的删除状态;虽然对写代码来讲有些麻烦，但实践证明是非常值得的。
误删数据、数据同步一致性。
`f_is_delete` smallint(6) NOT NULL DEFAULT '0' COMMENT '是否有效，1：是，0：无效或已删除',
3.5.5.注释要求
表、字段，都要求有相应的、清晰的注释。
如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
注释反例：状态；类型；金额1、金额2、金额3。
注释正例：订单状态、发货状态；订单类型、销售类型；实付金额、商品金额、优惠金额。
`f_menu_name` varchar(66) NOT NULL COMMENT '菜单名称',
`f_is_valid` smallint(6) NOT NULL DEFAULT '0' COMMENT '是否有效，1：是，0：无效或已删除',
) ENGINE=InnoDB AUTO_INCREMENT=914 DEFAULT CHARSET=utf8 COMMENT='系统菜单表' |
3.5.6.表的相关建议
● 一个字段只表示一个含义。表不应该有重复列。
● 需要join的字段(连接键)，数据类型必须保持绝对一致，避免隐式转换。比如关联的字段都是int类型。
● 单表字段数不要太多，建议最多不要大于50个。过度的宽表对性能也是很大的影响。
● MySQL在处理大表时，性能就开始明显降低，所以建议单表物理大小限制在16GB，表中数据行数控制在2000W内。
● 单表的索引数不超过五个。
3.6.字段设计规范
3.6.1.整数类型
整数类型 | 字节 | 最小值 | 最大值 |
TINYINT | 1 | -128 | 127 |
SMALLINT | 2 | -32768 | 32767 |
MEDIUMINT | 3 | -8388608 | 8388607 |
INT | 4 | -2147483648 | 2147483647 |
BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
● 如无特殊需要，存放整型数字使用UNSIGNED INT型。
● UNSIGNED表示为：非负数。
● 如果字段值长度不大，使用TINYINT、SMALLINT类型。
3.6.2.小数类型
数据类型 | 存储类型 | 字节 |
FLOAT | 浮点数 | 4 |
DOUBLE | 浮点数 | 8 |
DECIMAL(M,D) | 定点数 | M+2 |
● 浮点型在实际存储时会有精度损失，容易在统计中带来误差。所以在存放小数时，如金额、比率值时，最好不要选择浮点数类型，而是选择定点数类型。
mysql> create table t2(f_id int,f_float float,f_double double,f_decimal decimal(10,2));
mysql> insert into t2 values(1,100.33,100.33,100.33);
mysql> select * from t2 where f_float=100.33;
Empty set (0.00 sec)
mysql> select * from t2;
+------+-----------------+----------+-----------+
| f_id | f_float | f_double | f_decimal |
+------+-----------------+----------+-----------+
| 1 | 100.3300018311 | 100.33 | 100.33 |
+------+-----------------+----------+-----------+
3.6.3.日期时间类型
日期和时间类型 | 字节 | 最小值 | 最大值 | 是否支持系统默认值 |
DATE | 4 | 1000-01-01 | 9999-12-31 | 否 |
DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 13:59:59 | 否 |
TIMESTAMP | 4 | 1970-01-01 08:00:01 | 2038年某一天 | 否 |
● 普通时间字段：精确到时间(时分秒)的字段，使用DATETIME。
● 时间默认值字段：使用TIMESTAMP，可以设置默认值为系统当前时间（ DEFAULT CURRENT_TIMESTAMP），并且可以设置记录修改时间（ ON UPDATE CURRENT_TIMESTAMP）。
● 新版本也可以使用DATETIME类型获取系统当前时间。
mysql> CREATE TABLE t3(
f_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
f_st_date date NOT NULL,
f_create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'记录创建时间',
f_update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP COMMENT '记录更新时间');
mysql> insert into t3(f_st_date) values('2022-11-22');
mysql> select * from t3;
+------+------------+---------------------+---------------------+
| f_id | f_st_date | f_create_time | f_update_time |
+------+------------+---------------------+---------------------+
| 1 | 2022-11-22 | 2022-11-22 17:08:16 | 2022-11-22 17:08:16 |
+------+------------+---------------------+---------------------+
mysql> update t3 set f_st_date='2022-11-21' where f_id=1;
mysql> select * from t3;
+------+------------+---------------------+---------------------+
| f_id | f_st_date | f_create_time | f_update_time |
+------+------------+---------------------+---------------------+
| 1 | 2022-11-21 | 2022-11-22 17:08:16 | 2022-11-22 17:08:49 |
+------+------------+---------------------+---------------------+
3.6.4.字符串类型
字符串类型 | 特点 | 存储范围 |
CHAR(M) | 固定长度 | M为0~255之间的整数 |
VARCHAR(M) | 可变长度 | M为1~65535之间的整数 |
TEXT | 没有默认值 | 允许长度0~65535字节 |
LONGTEXT | 没有默认值 | 允许长度0~4294967295字节 |
● CHAR：适合存储较短的字符串，且字符串的长度较为固定。
● VARCHAR：适合存储较长字符串，或字符串长度变化范围较大。
● TEXT：作为大体量文本存储，仅仅当字符数量可能超过20000个的时候使用，必须放在独立的表中 , 用PK与主表关联。
● 如无特殊需要，不使用MEDIUMTEXT、TEXT、LONGTEXT类型。
3.6.5.字段设计
● 合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。
● 尽量用常用的字段类型（数值系列、日期系列、字符串系列），进行库表设计。
● 如无特殊需要，字段建议使用NOT NULL属性，可用默认值代替NULL，如：DEFAULT '0'。
● 字段的数据类型、字段长度，尽量以实际的业务情况为准。
反例：所有字段都设置为varchar类型。所有字段长度都设置为200。
3.6.6.NULL值的处理
NULL值在统计、处理、查询过滤时，会带来一定的复杂性和干扰。
1）NULL值的统计现象：
● count(distinct col) 计算该列除 NULL 之外的不重复行数。
● 注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
● 当某一列的值全是 NULL 时，count(col)的返回结果为 0，sum(col)的返回结果为 NULL。
2）常用的NULL处理函数：
● ISNULL(expr) 函数： 如expr 为null，那么isnull() 的返回值为 1，否则返回值为 0。
● IFNULL(expr1,expr2)函数：假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。
4.数据库应用规范
4.1.尽量只使用数据库自身的简单功能
1）不使用高级功能，如：外键、触发器、存储过程、函数等。
● 不方便调试和迁移，容易被忽略、容易造成性能问题。
● 相关效果可通过业务代码来实现。
● 外键：外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
● 存储过程：没有日志记录，难以调试和扩展，难以问题定位，没有移植性。曾被广泛应用于离线的统计分析，但现在被弃用。
2）只使用简单的字段类型，如：数值系列、日期系列、字符串系列。禁止使用复杂数据类型(数组,自定义等)，Json类型的使用视情况而定。
● 复杂数据类型：增加使用复杂性，处理性能，支持不够友好。
● 简单数据类型：能满足95%的库表设计场景。
4.2.安全操作习惯
对生产数据删除和修改时，要先 select，避免出现误删除，确认无误才能执行更新语句。
操作数据前，也可先物理备份到一张新表。
逻辑删除处理。
CREATE TABLE 新表 SELECT * FROM 旧表;
INSERT INTO 新表SELECT * FROM 旧表;
INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表;
4.3.设计及开发
4.3.1.数据仓库对业务库的要求
● 数据完整性：数据重复、关键字段值缺失、唯一字段模糊。
● 物理删除数据：传统数据仓库无法鉴别到数据的删除动作，导致数据不一致。
● 缺乏时间字段：无法增量同步，全量方式影响性能。
● 没有详尽的数据记录信息：只记录最后状态，缺乏中间过程数据变化的记录信息，无法跟踪过程。
● 不利于计算或统计：缺少必要的关联字段，或用于计算统计的字段分散于多个表中，造成计算统计的步骤繁琐，甚至无法计算统计。关联字段不统一。
4.3.2.DBA对业务库的要求
● 库表缺乏主键，不利于从库的搭建。
● 单表数据规模过大，后期被要求物理划分。
● 慢查询，被要求优化语句、优化表设计、优化索引、甚至修改代码。
● 避免全表更新，会产生大量的binlog日志，主从延迟。
4.3.3.对数据语句的优化
● 使用索引
● 代码缩进，使嵌套层次直观。
● 别名的规范定义，t1、t2、t3的命名。
● 尽可能先对明细数据做汇总，再关联基础信息。
● 减少大表关联的情况，适当地使用中间表存储中间结果。
标签：ci,name,utf8mb4,DEFAULT,数据库,Mysql,应用,注意事项,NULL From： https://blog.51cto.com/u_15891458/5903555