- 使用 Spring Initializr 创建 Spring Boot 应用程序
- 在Spring Boot中配置Cassandra
- 在 Spring Boot 上配置 Tomcat 连接池
- 将Camel消息路由到嵌入WildFly的Artemis上
前言: 最近牵头搞一个机场管理集团的项目,发现团队中的成员对于库表设计,有非常多的盲区。所以决定写一篇文章,总结一下最近工作的几年中,常用的一些数据库设计规范和思路。
MySQL数据库与 Oracle、 SQL Server 等数据库相比,有其内核上的优势与劣势。我们在使用MySQL数据库的时候需要遵循一定规范,扬长避短。本规范旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL编写等方面予以规范,从而为公司业务系统稳定、健康地运行提供保障。
以下所有规范会按照【高危】、【强制】、【建议】三个级别进行标注,遵守优先级从高到低。
对于不满足【高危】和【强制】两个级别的设计,DBA会强制打回要求修改。
库通配名_编号
,编号从0开始递增,比如wenda_001
以时间进行分库的名称格式是“库通配名_时间”create database db1 default character set utf8;
。auto_increment
(2)标识表里每一行主体的字段不要设为主键,建议设为其他字段如user_id
,order_id
等,并建立unique key索引(可参考cdb.teacher
表设计)。因为如果设为主键且主键值为随机插入,则会导致innodb内部page分裂和大量随机I/O,性能下降。create_time
和最后更新时间字段update_time
,便于查问题。NOT NULL
属性,业务可以根据需要定义DEFAULT
值。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。blob
、text
等大字段,垂直拆分到其他表里,仅在需要读这些对象的时候才去select。user_name
属性在user_account
,user_login_log
等表里冗余一份,减少join查询。tmp_
开头。备份表用于备份或抓取源表快照,名称必须以bak_
开头。中间表和备份表定期清理。alter table
,必须经过DBA审核,并在业务低峰期执行。因为alter table
会产生表锁,期间阻塞对于该表的所有写入,对于业务可能会产生极大影响。auto_increment
属性),推荐使用bigint
类型。因为无符号int
存储范围为-2147483648~2147483647
(大约21亿左右),溢出后会导致报错。status
、类型type
等字段推荐使用tinytint
或者smallint
类型节省存储空间。int
类型,不推荐用char(15)
。因为int
只占4字节,可以用如下函数相互转换,而char(15)
占用至少15字节。一旦表数据行数到了1亿,那么要多用1.1G存储空间。select inet_aton('192.168.2.12'); select inet_ntoa(3232236044);
ip2long(‘192.168.2.12’); long2ip(3530427185);
enum
,set
。 因为它们浪费空间,且枚举值写死了,变更不方便。推荐使用tinyint
或smallint
。blob
,text
等类型。它们都比较浪费硬盘和内存空间。在加载表数据时,会读取大字段到内存里从而浪费内存空间,影响系统性能。建议和PM、RD沟通,是否真的需要这么大字段。Innodb中当一行记录超过8098字节时,会将该记录中选取最长的一个字段将其768字节放在原始page里,该字段余下内容放在overflow-page
里。不幸的是在compact
行格式下,原始page
和overflow-page
都会加载。int
,程序端乘以100和除以100进行存取。因为int
占用4字节,而double
占用8字节,空间浪费。varchar
存储。因为varchar
是变长存储,比char
更省空间。MySQL server层规定一行所有文本最多存65535字节,因此在utf8字符集下最多存21844个字符,超过会自动转换为mediumtext
字段。而text
在utf8字符集下最多存21844个字符,mediumtext
最多存224/3个字符,longtext
最多存232个字符。一般建议用varchar
类型,字符数不要超过2700。timestamp
。因为datetime
占用8字节,timestamp
仅占用4字节,但是范围为1970-01-01 00:00:01
到2038-01-01 00:00:00
。更为高阶的方法,选用int
来存储时间,使用SQL函数unix_timestamp()
和from_unixtime()
来进行转换。详细存储大小参加下图:
id int/bigint auto_increment
,且主键值禁止被更新。pk_
”开头,唯一键以“uk_
”或“uq_
”开头,普通索引以“idx_
”开头,一律使用小写格式,以表名/字段的名称或缩写作为后缀。BTREE
;MEMORY表可以根据需要选择HASH
或者BTREE
类型索引。userid
的区分度可由select count(distinct userid)
计算出来。key(a,b)
,则key(a)
为冗余索引,需要删除。partition-key
)必须有索引,或者是组合索引的首列。alter table
操作,必须在业务低峰期执行。utf8
或utf8mb4
。utf8
。CREATE TABLE user (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`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 '昵称',
`avatar` int(11) 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 COMMENT ‘用户记录创建的时间’,
`update_time` timestamp NOT NULL COMMENT ‘用户资料修改的时间’,
`user_review_status` tinyint NOT NULL COMMENT ‘用户资料审核状态,1为通过,2为审核中,3为未通过,4为还未提交审核’,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_user_id` (`user_id`),
KEY `idx_username`(`username`),
KEY `idx_create_time`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息';
*
。因为select *
会将不该读的数据也从MySQL里读出来,造成网卡压力。且表字段一旦更新,但model层没有来得及更新的话,系统会报错。insert into t1 values(…)
,道理同上。insert into…values(XX),(XX),(XX)…
。这里XX的值不要超过5000个。值过多虽然上线很很快,但会引起主从同步延迟。UNION
,推荐使用UNION ALL
,并且UNION
子句个数限制在5个以内。因为union all
不需要去重,节省数据库资源,提高性能。select… where userid in(….500个以内…)
,这么做是为了减少底层扫描,减轻数据库压力从而加速查询。hint
,如sql_no_cache
,force index
,ignore key
,straight join
等。因为hint
是用来强制SQL按照某个执行计划来执行,但随着数据量变化我们无法保证自己当初的预判是正确的,因此我们要相信MySQL优化器!SELECT|UPDATE|DELETE|REPLACE
要有WHERE子句,且WHERE子句的条件必需使用索引查找。where length(name)='Admin'
或where user_id+2=10023
。where a=1 or b=2
优化为where a=1… union …where b=2, key(a),key(b)
。select a,b,c from t1 limit 10000,20;
优化为: select a,b,c from t1 where id>10000 limit 20;
。update t1 join t2…
。select a from db1.table1 alias1 where …
。INSERT|UPDATE|DELETE|REPLACE
语句操作的行数控制在2000以内,以及WHERE子句中IN列表的传参个数控制在500以内。auto_increment
属性字段的表的插入操作,并发需要控制在200以内。repeatable-read
。unique key
,如update … where id=XX
; 否则会产生间隙锁,内部扩大锁定范围,导致系统性能下降,产生死锁。order by
,和业务沟通能不排序就不排序,或将排序放到程序端去做。order by
、group by
、distinct
这些语句较为耗费CPU,数据库的CPU资源是极其宝贵的。order by
、group by
、distinct
这些SQL尽量利用索引直接检索出排序好的数据。如where a=1 order by
可以利用key(a,b)
。order by
、group by
、distinct
这些查询的语句,where条件过滤出来的结果集请保持在1000行以内,否则SQL会很慢。update|delete t1 … where a=XX limit XX;
这种带limit的更新语句。因为会导致主从不一致,导致数据错乱。建议加上order by PK
。update t1 set … where name in(select name from user where…);
效率极其低下。insert into …on duplicate key update…
在高并发环境下,会造成主从不一致。update t1,t2 where t1.id=t2.id…
。目前我正在构建相当大的网络系统,我需要强大的 SQL 数据库解决方案。我选择 Mysql 而不是 Postgres,因为一些任务需要只读(MyISAM 引擎)而其他任务需要大量写入(InnoDB)。
我在 mysql 中使用如下命令。当它显示表格数据时,它被格式化为一个非常干净的表格,间距均匀且 |作为列分隔符。 SELECT * FROM TABLE_NAME; 当我从 CLI 运行命令时,如下
我知道这个问题之前已经被问过好几次了,我已经解决了很多问题,但到目前为止没有任何效果。 MySQL 试图将自身安装到的目录 (usr/local/mysql) 肯定有问题。关于我的错误的奇怪之处在于我
以下是我的 SQL 数据结构,我正在尝试如下两个查询: Select Wrk_ID, Wrk_LastName, Skill_Desc from Worker, Skill where
我们有一个本地 mysql 服务器(不在公共(public)域上),并希望将该服务器复制到我们拥有的 google 云 sql 实例。我的问题是:1.这可能吗?2.我们的本地服务器只能在本地网络上访问
我有一个表(test_table),其中一些字段值(例如字段 A、B 和 C)是从外部应用程序插入的,还有一个字段(字段 D),我想从现有表(store_table)插入其值,但在插入前者(A、B 和
我想创建一个 AWS RDS 实例,然后使用 terraform 管理数据库用户。因此,首先,我创建了一个 RDS 实例,然后使用创建的 RDS 实例初始化 mysql 提供程序,以进一步将其用于用户
当用户在我的网站上注册时,他们会在我的一个数据库中创建自己的表格。该表存储用户发布的所有帖子。我还想做的是也为他们生成自己的 MySql 用户——该用户仅有权从他们的表中读取、写入和删除。 创建它应该
我有一个关于 ColdFusion 和 Mysql 的问题。我有两个表:PRODUCT 和 PRODUCT_CAT。我想列出包含一些标记为:IS_EXTRANET=1 的特殊产品的类别。所以我写了这个
我想获取 recipes_id 列的值,以获取包含 ingredient_id 的 2,17 和 26 条目的值。 假设 ingredient_id 2 丢失则不获取记录。 我已经尝试过 IN 运算符
在 Ubuntu 中,我通常安装两者,但 MySQL 的客户端和服务器之间有什么区别。 作为奖励,当一个新语句提到它需要 MySQL 5.x 时,它是指客户端、服务器还是两者兼而有之。例如这个链接ht
我重新访问了我的数据库并注意到我有一些 INT 类型的主键。 这还不够独特,所以我想我会有一个指导。 我来自微软 sql 背景,在 ssms 中你可以 选择类型为“uniqeidentifier”并自
我的系统上有 MySQL,我正在尝试确定它是 Oracle MySQL 还是 MySQL。 Oracle MySQL 有区别吗: http://www.oracle.com/us/products/m
我是在生产 MySQL 中运行的应用程序的新维护者。之前的维护者已经离开,留下的文档很少,而且联系不上了。 我面临的问题是执行以下请求大约需要 10 秒: SELECT COUNT(*) FROM `
我有两个位于不同机器上的 MySQL 数据库。我想自动将数据从一台服务器传输到另一台服务器。比方说,我希望每天早上 4:00 进行数据传输。 可以吗?是否有任何 MySQL 内置功能可以让我们做到这一
有什么方法可以使用 jdbc 查询位于 mysql 根目录之外的目录中的 mysql 表,还是必须将它们移动到 mysql 根目录内的数据库文件夹中?我在 Google 上搜索时没有找到任何东西。 最
我在 mysql 数据库中有两个表。成员和 ClassNumbers。两个表都有一个付费年份字段,都有一个代码字段。我想用代码数字表中的值更新成员表中的付费年份,其中成员中的代码与 ClassNumb
情况:我有 2 台服务器,其中一台当前托管一个实时 WordPress 站点,我希望能够将该站点转移到另一台服务器,以防第一台服务器出现故障。传输源文件很容易;传输数据库是我需要弄清楚如何做的。两台服
Phpmyadmin 有一个功能是“复制数据库到”..有没有mysql查询来写这个函数?类似于将 db A 复制到新的 db B。 最佳答案 首先创建复制数据库: CREATE DATABASE du
我有一个使用 mySQL 作为后端的库存软件。我已经在我的计算机上对其进行了测试,并且运行良好。 当我在计算机上安装我的软件时,我必须执行以下步骤: 安装 mySQL 服务器 将用户名指定为“root
我是一名优秀的程序员,十分优秀!