MySQL设计规范（内部使用）

最新推荐文章于 2023-05-12 14:37:29 发布

gonghaiyu

最新推荐文章于 2023-05-12 14:37:29 发布

阅读量170

点赞数

分类专栏：编码规范 MySQL

本文链接：https://blog.csdn.net/gonghaiyu/article/details/108416990

版权

MySQL 同时被 2 个专栏收录

22 篇文章 1 订阅

订阅专栏

编码规范

5 篇文章 0 订阅

订阅专栏

MySQL设计规范（内部使用）

目的
适用范围
规范
命名
表
范式
约束
字段
索引/主键设计
分库分表
其它

参考

建议结合 MySQL表结构设计一起参考使用

目的

为了规范数据库设计，减少设计失误，提高数据安全及性能，特制订本规范。

适用范围

所有mysql数据库。原则上，数据库设计应遵循本规范说明，特殊情况可例外，但需跟DBA说明原因。

规范

命名

库名、表名、字段名必须使用小写字母,并采用下划线分割

MySQL有配置参数lower_case_table_names,不可动态更改,linux系统默认为 0,即库表名以实际情况存储,大小写敏感。如果是1,以小写存储,大小写不敏感。如果是2,以实际情况存储,但以小写比较。集团标准化部署设置lower_case_table_names = 1
如果大小写混合使用,可能存在abc,Abc,ABC等多个表共存,容易导致混乱。
字段名显式区分大小写,但实际使用不区分,即不可以建立两个名字一样但大小写不一样的字段。
为了统一规范, 库名、表名、字段名全部使用小写字母。

库名、表名、字段名禁止超过32个字符

MySQL库名、表名、字段名支持最多64个字符,Oracle最多支持30个字符,但为了统一规范、易于辨识、便于迁移以及减少传输量,禁止超过30个字符。

库名、表名、字段名禁止使用MySQL保留字，尤其是status字段

当库名、表名、字段名等属性含有保留字时,SQL语句必须用反引号引用属性名称,这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。

索引的命名与阿里编码规范有点不一样，这里采用阿里的规范。即idx_xxx_yyy表示普通索引，uni_xxx_yyy表示唯一索引。xxx,yyy表示字段名。

表

原则上，业务范畴内的表都统一使用innodb存储引擎；如需使用其它存储引擎，需说明原因，并征得DBA同意。集团标准化部署设置默认使用innodb引擎。

innodb引擎是MySQL5.5版本以后的默认引擎,支持事务、行级锁,有更好的数据恢复能力、更好的并发性能,同时对多核、大内存、SSD等硬件支持更好,支持数据热备份等,因此INNODB相比MyISAM有明显优势。
myisam不够可靠，容易损坏丢失数据。
myisam不支持事务；如果存在myisam与innodb混用的话，事务数据的一致性存在风险。
myisam读写阻塞，不适用于高并发环境；innodb支持行级锁。
myisam不支持在线热备，配置复制时可能需要停机或者长时间锁表。
如果两种引擎混用，会给资源分配带来问题。因为有很多参数和设置需要停库才能修改，为了减少停库次数，我们目前都是采用预先最大化分配足额内存和磁盘空间的方式去做的。而myisam和innodb在内存和空间使用上都不一样，如果两者混用，那分配的时候就很难两方都照顾到。如果两者都按比例分配，很可能存在资源不能充分利用的情况；且随着业务的变化，需要调整的话，也会存在停机问题。
运维需要收集很多的运行状态数据，innodb有非常多的状态数据可收集；而对于myisam引擎，能收集到的数据非常有限，不利于我们做监控和容量规划。

使用外部引擎

目前常用的全文索引引擎有sphinx和lucene等，适合并发高，数据量大，业务逻辑复杂的场景。主要关注预热、增量更新及分片功能的实现。
异构同步引擎有mysql2redis、tair 等，主要解决数据项cache的同步

禁止使用分区表

分区表对分区键有严格要求;分区表在表变大后,执行DDL、SHARDING、单表恢复等都变得更加困难。因此禁止使用分区表,并建议业务端手动SHARDING。

减少或避免使用临时表

为了切换后可以正常工作，无论主备，相同的表需使用相同的引擎。
相同的表在主备可以使用不同的引擎或者获得更好地性能，但是给管理带来复杂性，也给业务带来隐患。在我们的DB环境，应用程序切换连接DB是常态，因此，需要严格保持主备数据和表结构的一致性。

每一个表都需要设置主键

表没有主键,INNODB会默认设置隐藏的6byte的主键列_rowid;没有主键的表在定位数据行的时候非常困难,也会降低基于行复制的效率。

将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据

有利于有效利用缓存,防止读入无用的冷数据,较少磁盘IO,同时保证热数据常驻内存提高缓存命中率。

为跟踪业务数据的更改变化，要求业务关键数据TABLE中必须包含以下特定的WHO字段：

字段名类型 NULL？说明
created_by varchar(64) not null 创建人
create_time datetime not null 创建时间
updated_by varchar(64) not null 最后修改人
update_time datetime not null 最后修改时间

不允许不同业务模块的表连接查询

表分模块很可能在后续进行业务的垂直拆分到不同的业务库里面去，如果做连接查询后面拆分就比较麻烦，系统的模块化结构也不清晰。

尽量不在数据库做运算，尽可能简单使用MySQL

md5() 或Order by Rand()或计算字段等操作不在数据库表上进行

SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数

语句级复制场景下,引起主从数据不一致;不确定值的函数,产生的SQL语句无法利用QUERY CACHE。

合理使用覆盖索引减少IO,避免排序

覆盖索引能从索引中获取需要的所有字段,从而避免回表进行二次查找,节省 IO。INNODB存储引擎中, secondary index(非主键索引,又称为辅助索引、二级索引)没有直接存储行地址,而是存储主键值。如果用户需要查询secondary index中所不包含的数据列,则需要先通过secondary index查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次。覆盖索引则可以在一个索引中获取所有需要的数据,因此效率较高。主键查询是天然的覆盖索引。例如SELECT email,uid FROM user_email WHERE uid=xx,如果uid 不是主键,适当时候可以将索引添加为index(uid,email),以获得性能提升。

禁止在数据库中存储明文密码

密码存储应该使用不可逆加密算法将密码、用户名及盐值组合加密后存储。

禁止将数据字典表进行连接查询

数据自动表可以查询到cache或者在内存中对相关列进行替换

禁止频繁使用的SQL语句中出现三个以上表的连接查询

MySQL在连接查询方面的处理能力远没有Oracle强劲，在连接的过程中只有 Nest Loop Join方式，Oracle则可以有Hash Join 和 Memory Index Joini。在常用的SQL中禁止出现三个以上表的连接查询，如果是定时执行的次数不多的SQL需要另外考虑。

用IN代替OR。SQL语句中IN包含的值不应过多,应少于1000个。

IN是范围查找,MySQL内部会对IN的列表值进行排序后查找,比OR效率更高。

用UNION ALL代替UNION

UNION ALL不需要对结果集再进行排序。

禁止使用order by rand()

order by rand()会为表增加一个伪列,然后用rand()函数为每一行数据计算出rand()值,然后基于该行排序, 这通常都会生成磁盘上的临时表,因此效率非常低。建议先使用rand()函数获得随机的主键值,然后通过主键获取数据。

建议使用合理的分页方式以提高分页效率

第一种分页写法：
select * from t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 0, 15;
原理：一次性根据过滤条件取出所有字段进行排序返回。
数据访问开销 = 索引IO + 索引全部记录结果对应的表数据IO
缺点：该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。
适用场景：当中间结果集很小（10000行以下）或者查询条件复杂（指涉及多个不同查询字段或者多表连接）时适用。

第二种分页写法：
select t.* from ( select id from t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 0, 15) a, t where a.id = t.id;
前提：假设t表主键是id列，且有覆盖索引secondary key:(thread_id, deleted, gmt_create)，这个前提很重要，能显著提供查询效率。
原理：先根据过滤条件利用覆盖索引取出主键id进行排序，再进行join操作取出其他字段。
数据访问开销=索引IO+索引分页后结果（例子中是15行）对应的表数据IO。
优点：每次翻页消耗的资源和时间都基本相同，就像翻第一页一样。
适用场景：当查询和排序字段（即where子句和order by子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用。

SELECT只获取必要的字段,禁止使用SELECT *

减少网络带宽消耗;
能有效利用覆盖索引;
表结构变更对程序基本无影响。

减少与数据库交互次数,尽量采用批量SQL语句,并限制每次插入的数量

使用下面的语句来减少和db的交互次数:

INSERT … ON DUPLICATE KEY UPDATE（sharding-jdbc不支持，先判断一条记录是否存在，存在则update，否则insert，使用示例：INSERT INTO tablename(field1,field2, field3, …) VALUES(value1, value2, value3, …) ON DUPLICATE KEY UPDATE field1=value1,field2=value2, field3=value3, …;）
REPLACE INTO
INSERT IGNORE
INSERT INTO VALUES()

拆分复杂SQL为多个小SQL,避免大事务

简单的SQL容易使用到MySQL的QUERY CACHE;减少锁表时间特别是MyISAM;可以使用多核CPU。

对同一个表的多次alter操作必须合并为一次操作

mysql对表的修改绝大部分操作都需要锁表并重建表,而锁表则会对线上业务造成影响。为减少这种影响,必须把对表的多次alter操作合并为一次操作。例如,要给表t增加一个字段b,同时给已有的字段aa建立索引,
通常的做法分为两步: alter table t add column b varchar(10);
然后增加索引: alter table t add index idx_aa(aa);
正确的做法是: alter table t add column b varchar(10),add index idx_aa(aa);

避免使用存储过程、触发器、视图、自定义函数等

这些高级特性有性能问题,以及未知BUG较多。业务逻辑放到数据库会造成数据库的DDL、SCALE OUT、 SHARDING等变得更加困难。

禁止有super权限的应用程序账号存在

安全第一。super权限会导致read only失效,导致较多诡异问题而且很难追踪。

提交线上建表改表需求,必须详细注明涉及到的所有SQL语句(包括INSERT、DELETE、UPDATE),便于DBA进行审核和优化
不只是SELECT语句需要用到索引。UPDATE、DELETE都需要先定位到数据才能执行变更。因此需要业务提供所有的SQL语句便于DBA审核。

禁止使用HINT

HINT给DBA维护带来很多的不便，MySQL使用CBO优化器（CBO: Cost-Based Optimization 基于代价的优化器），会综合考虑如果执行SQL。

范式

表不要求一定满足第三范式，根据实际情况可适当添加冗余字段。

我们的原则是一个SQL最好只操作一个表，最多不能超过2个表的关联。如果实现一个常用的功能需要一个关联多表的查询，则需要重新考虑设计。
由程序保证冗余数据的维护。

约束

对于字典类型的表，因数据量小，修改少，影响面大，应依赖数据库约束来确保数据质量。
对于日志或流水型表，为了提升效率，可以释放放宽限制。
之所以分开，是从性能以及影响面考虑的。
对于字典类型的数据，因为修改少，约束给其性能带来的负面影响忽略。但是一个数据字段的数据错误，影响面非常广，因此，需要非常严谨。前端程序或者手工添加此类数据时，容易出现错误，因此需要通过约束来保证其数据的质量。
日志或者流水型表刚好相反，它一般只影响个别用户，但数据量较大，修改较为频繁，性能优先。

字段

对于字段设计，概况下来一个原则是：越简单越好，越小越好。
常用数据类型介绍：
在这里插入图片描述

选择最合适的数据类型，能用数字类型不用varchar类型；能用date/datetime类型不用varchar类型；避免使用char类型；不使用浮点数，可以通过乘以一个系数来转换为整型数据。
在大部分情况下，数据类型比varchar类型更省空间，计算性能更高。
char类型占用固定空间，在很多时候会造成空间浪费。

尽量避免text/blog类型
text和blog在行内只存储指针，实际数据是在行外单独存储的。使用这些数据类型，查询时需要更多的IO。
如果SQL引用了text/blog字段，排序等操作需要用到隐式临时表时，只能用到disk临时表，不能用in-memory临时表。对性能影响巨大。
如果也许需要用text/blog类型，建议把这些字段和其他字段分离，放在单独的表中。

字段长度定义遵循最小化原则，够用就行，不能贪图方便定义很大的长度。
过大的长度容错性高，容易出现低质量数据。
定义大长度会消耗更多的空间(需要用额外的字节存储长度)
在很多时候，mysql或其他程序（如mysqldump）会以列的长度定义来分配使用内存，过大的字段长度定义会消耗更多的内存。甚至还有可能原来可以在内存执行的排序变为磁盘排序了。

字段实际值也遵循最小化原则，在满足业务需求的前提下，选择最合适的数据类型，写入最少的数据ID一般无具体意义，优先使用数字来用于ID字段，杜绝滥用长字符串ID。
对于状态之类的字段，用0/1之类的数字代替valid/invalid等。

对于字符类型，字段长度定义的是字符个数，而不是字节个数，所占空间与字符集相关。对于int类型，占用空间是固定的，指定长度的话只是制定了显示长度。
varchar(25000)不一定能存储25000个字符，如果都是汉字，且是utf8字符集的话，只能存储65532/3个字符。
int(1)和int(11)占用空间是一样的，1和11只是代表显示长度，与实际数据存储无关。另外，在允许的范围内，往int字段写入不同的值，如insert t values(1)和insert t values(9999)占用空间一样。

区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。存储时间(精确到秒)建议使用TIMESTAMP类型。
DATETIME和TIMESTAMP都是精确到秒,优先选择 TIMESTAMP,因为TIMESTAMP只有4个字节,而DATETIME8个字节。同时TIMESTAMP具有自动赋值以及自动更新的特性。注意: 在5.5和之前的版本中,如果一个表中有多个timestamp列,那么最多只能有一列能具有自动更新功能。

使用TIMESTAMP的自动赋值属性
1.自动初始化,并自动更新: column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
2.只是自动初始化: column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
3.自动更新,初始化的值为0: column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
4.初始化的值为0: column1 TIMESTAMP DEFAULT 0

一个表的字段个数控制在30-50个字段以内；如果字段超过50个，可考虑将字段按冷热程度分表。
这样做虽然会给应用带来更多的代码开发量，但对于热表来说，这样做可以提升buffer利用率，减少IO，提升查询的效率。

严格禁止单条记录超过8K
目前我们的DB一个page大小都设置为16K，当一条记录拆过page的一半(8K)时，记录中的blob/varchar会在行外存储。存取时会有额外IO消耗。插入操作会锁住整个聚簇索引(X Index Lock)，直到插入完成才释放。

建议每一个重要的业务表都加上 create_time 和 modify_time 两个字段，数据类型为datetime；后续的所有更新都必须更新modify_time字段。
增加字段会带来额外的资源消耗，但考虑到它可能能带来的好处，还是值得添加的。
这两个字段可以与业务时间重合重用。

对于可能出现在where条件中的字段，尽量设置为非空(not null)
B*Tree索引不能索引空值(null)。字段值为空的话，在部分查询不能有效使用索引。

字段字符集与表保持一致，不单独设置字符集。
相同含义的字段在不同表中应使用相同的名称，数据类型及长度必须保持一致。

索引/主键设计

Innodb表每一个表都要设置主键。主键越短越好，最好是auto_increment类型；如果不能使用自增，则应考虑构造使用单向递增型主键；mysql中禁止使用随机类型值用于主键。
使用innodb，每一个表都必须有主键。如果没有指定主键，mysql会选择一个非空的唯一键作为主键；如果都没有，mysql会自动分配6-bytes长的全局的rowid隐藏列作为主键。所有InnoDB非主键表共享这一序列，并发性能较差，因此建议所有InnoDB表，指定主键。
所有的辅助索引都包含主键字段，因此，如果主键过大的话，会影响所有的索引大小。
递增型主键可以让索引更紧凑、空间利用更充分、数据插入更高效。随机数据会让空间大量浪费，且会导致索引分裂加剧，离散IO增多，极大影响DML性能。

主键最好由一个字段构成，最多不要超过3个，禁止超过3个字段的组合主键。如果业务要求，则可以创建一个自增字段作为主键，再添加一个唯一索引。
多列主键会导致其他索引体积膨胀，占用更多的空间，并降低性能。
使用多列业务主键的话，对主键来说，一般可认为是随机插入。
如果查询都是基于主键字段，且只有1个及以下辅助索引，则限制可以放宽，这种情况明显很难做到。

如果一个业务上存在多个(组)唯一键，以查询最常用的唯一键作为主键。
索引会降低DML的性能，不是越多越好，只创建需要的索引，避免冗余索引。
创建索引需考虑返回数据量、对DML的影响，以及受其影响的查询SQL的执行频率。

选择作为主键的列必须在插入后不再修改或者极少修改，否则需考虑使用自增列作为主键。
创建组合索引/主键时，常用的字段放在前面；选择性高的字段放在前面。
两者冲突时，以常用为更优先选择。

创建组合索引时，如果where条件中过滤性不强，且需要排序分页操作，建议把排序字段也加到组合索引中，放在组合索引最后列。
如col1 + col 2可以过滤出来10000条数据，当有如下SQL时，建议把col3也加到索引中：
select col1,col2…coln from t where col1=xx and col2=xx order by col3 limit n;

对较长的字符数据类型的字段建索引，优先考虑前缀索引。
低选择性的列不加索引，例如”性别”
如性别字段加索引意义不大
状态字段一般不加索引。例外情况是，状态数据分布很倾斜，且查询条件中没有其他更适合索引的字段，如status=1占99%数据，status=2占1%数据，查询SQL为：select col1,col2… from t where status=2;

禁止冗余索引
索引是双刃剑,会增加维护负担,增大IO压力。(a,b,c)、(a,b),后者为冗余索引。可以利用前缀索引来达到加速目的,减轻维护负担。

在建表时，应充分考虑需要添加什么索引，尽量避免上线后添加索引。