对象名称必须使用小写,多单词统一使用下划线分割
命名的单词必须做到顾名思义、简洁。表名长度最好不要超过16个字符,字段名称长度最好不要超过32个字符
禁止使用保留字并且尽量少用含有关键词来命名
临时表必须以tmp_
开头、以日期结尾,备份表必须以bak_
开头、以日期结尾
InnoDB
作为表的存储引擎在MySQL 5.6
以后,InnoDB
被设置成默认的存储引擎,支持事务和行级锁。
UTF8MB4
字符编码UTF8MB4
字符编码支持中文储存以及表情存储,兼容性更好。在创建新的数据库和数据库应使用这个编码,而已有数据库和数据表可以保留原有编码。
这个是好习惯的问题,即使字段命名做到了顾名思义,但以防日后健忘或理解错误,同时为了提高维护性,必须对字段尽可能添加注释。
如 id, create_time, update_time 这些通用字段可以忽略注释,但还是建议统一添加注释。而其他业务字段,必须有注释。如果是作为外键关联其他表,必须要注释说明关联哪个表哪个字段。
使用comment
设定注释。
数据量越多,则查询的效率越低,同时会导致长时间占用高内存以及磁盘IO
过高。数据量膨大建议采用分表、合理分区等方案。
从经验来看,超过 1000w 行的表就有可能带来性能隐患。
在MySQL
中,数据表列数最大限制为4096
列 ,每条元祖数据总和大小不能超过65535
字节,常用的字段与基本不常用的字段、细分不同业务的数据分开表设计存储,减小表宽度,保证热数据的内存缓存命中率,降低CPU
使用率以及降低IO
流。
一般来说,单表字段控制在 20 以内比较合理。
MySQL
虽然支持对文件对象的存储,但是开发人员是不允许、不推荐这样做的。文件通常是很大的,转成二进制数据将是一串很长的字符串,无疑占用数据库很大的存储空间,在数据库读写更是消耗内存和占用大量的IO
流,最终导致查询的效率低下。一般文件是存放于阿里云 OSS 服务器,将文件服务器的路径存储于数据库中。
为了更好管理数据的增删查改,约定以下通用字段,新建的业务表必须包含这些字段:
create_time datetime not null comment '创建时间',
update_time datetime not null comment '更新时间',
deleted bit default 0 not null comment '删除标识:0-未删除;1-已删除'
如果业务是支持多个医院的,必须加上医院 id:
hospital_id int not null comment '医院 id'
禁止在线上做数据库的压力测试
对应的环境使用对应的数据库
比如测试环境不能使用生产环境的数据库,防止产生数据污染。
super
权限只能属于DBA
养成查看SQL
运行性能的习惯,可以借用性能分析工具
譬如:EXPLAIN
语句 | showprofile
| mySQLsla
等。
只能在流量比较低的时段跑批操作。
防患于未然、否则可能造成数据库服务器流量瓶颈进而导致影响业务。例如之前的疫苗预约临时上线,没做好流量评估就引发过业务故障。
确保表结构设计为最优是前期数据库最大的优化
DBA
的审阅和同意目前没有 DBA 一职,则由技术负责人兼任。
索引具有提高查询的效率的好处,但也有降低写操作效率的坏处,甚至会降低查询到的效率。同时索引也是占用内存空间的,因而应该合理控制索引的数量。
一般来说,尽可能每张表的索引数量控制在5个以内。
InnoDB
表都必须含有一个主键InnoDB 是一种索引组织表:数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引,但是表的存储顺序只能有一种 InnoDB是按照主键索引的顺序来组织表的。
不要使用可能会更新的列作为主键,同时尽量不要使用UUID
、MD5
、HASH
等无序的字符串作为主键。在没有特别的情况下,要使用自增的整型或发号器作为主键。
外键可以保证数据的准确性、参照完整性,每次进行写操作时都会走校验数据知否正确的流程,将会有损写操作的性能,数据的参照完整性建议在业务层实现。倘若字表的写操作很少的情况下务必使用外键约束。
体验产品和架构师的交流和能力、对业务的熟悉度。
第一范式:具有原子性
第二范式:主键列与非主键列遵循完全函数依赖关系
第三范式:非主键列之间没有传递函数依赖关系
合理的原则能够体现出数据库的可操作性、稳定性以及性能水准。
范式设计是数据结构的一种思想,但是我们应当灵活使用,一味追求三范式无疑会影响程序的性能,适当的冗余是可以提高查询的效率的,前提要保证是主键的冗余。
数据表的宽度与内存占用的大小成正比,在进行读写操作时,数据库程序将表结构与数据载入内存,当表宽度越长消耗的内存越多、越占IO
流,导致操作的效率下降。将可能将字段按照业务细分、冷热的条件进行分表设计。
比如ext
、ext_1
、extend_n
,时间一长,好几个这样的字段,即使每一个都有comment
,也会造成SQL
的可读性,特别是在构建SQL
语句的时候。
合理设置字段的类型和长度,可以节省MySQL
的表空间,是性能优化的重点之一。同时,索引列定义空间越大也会导致建立索引的所需空间也越大。
例如软删除标识字段 deleted,就应该使用 bit。性别字段,就可以使用 tinyint。
TEXT
、BLOB
、ENUM
数据类型MySQL 内存临时表不支持TEXT
、BLOB
这样的大数据类型,如果查询中包含这样的数据,在排序等操作时,就不能使用内存临时表,必须使用磁盘临时表进行,毋庸置疑会降低查询的效率。MySQL
对索引字段长度是有限制的,TEXT
或BLOB
类型只能使用前缀索引。
ENUM
数据类型在MySQL
中,存储枚举类型的数据在库中,字段列中保存的值实际为整数,特别容易导致开发者混乱,同时在查询使用排序是基于数值整型的,虽然可以使用ORDER BY FIELD()
,但是会导致索引失效,尽量避免这么做。
CHAR
节省存空间、降低内存使用率、提高读写性能。
UNSIGNEG
存储非负整数节省存空间、降低内存使用率、提高读写性能。
确保信息的安全性,比如密码、隐秘数据等。
新增或修改字段,必须充分考虑其类型、取值范围、是否可空等因素,绝对不能对线上历史数据造成影响。
示例:
2020 年 03 月 12 日 13:32:23
事件:sns_user_info 表增加 terminal 字段索引
执行人:郭城锋
审核人:郭城锋
执行内容:
ALTER TABLE `ywt_center`.`sns_user_info` ADD INDEX `idx_terminal` (`terminal`)
重要的SQL
语句必须带上索引作为条件
避免冗余和重复索引
重复索引: 在相同的列上按照相同的顺序创建的相同类型的索引。
冗余索引: 两个索引按照相同的顺序覆盖了相同的列。
在一张用户表里面,将用户id
设置成主键的同时再设置成唯一索引,那就是重复索引,如果创建了索引(a
,b
),再设置a
索引,则a为冗余索引,这两种错误的操作都会降低读写的性能。
索引本身占用存储空间,过多设置会导致查询效率降低。比如在成绩表中将分数设置为索引,这是一种错误的做法。
MySQL
不擅长于运算,需要计算的应该移至代码业务层。总而言之,凡是计算都要移至代码业务层(MySQL
不擅长于运算)。
将区分度高的索引置前可以缩短查询的范围,以至提高查询的效率,特别是在JOIN
连表查询,提高效率特别明显。
索引命名统一使用 idx
前缀,后缀一个或多个索引字段的名称。示例:idx_user_id_order_id
SQL
语句必须带上条件危险的SQL
语句指删、改操作,一定要记住带上WHERE
,禁止全表更新。
SELECT *
查询字段尽可能只查询需要的字段,否则可能导致覆盖索引失效,消耗更多的 CPU
和 IO
以网络带宽资源。
查询语句尽可能限定条件以提高查询效率
必须避免数据类型隐式转换
在MySQL
中,数据会存在隐式转换,当该字段发生转换时,索引会造成失效。
既然设置了索引就充分利用好索引,将查询的效率提至最高。
INSERT
操作这是一种错误的做法,对于表的改动后会造成比较大的影响。
INSERT INTO user VALUES ('lihua',23);
# 应该这样操作
INSERT INTO user (`name`,`age`) VALUES ('lihua',23);
JOIN
替代子查询操作子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响。 特别是对于返回结果集比较大的子查询,其对查询性能的影响也就越大。 由于子查询会产生大量的临时表也没有索引,所以会消耗过多的 CPU
和 IO
资源,产生大量的慢查询。
JOIN
关联过多的表一般情况下,建议JOIN
的表不要超过 5 个,JOIN
多表查询比较耗时时间,关联的表越多越耗时间,防止执行超时或死锁。
可以灵活地合并 SQL
操作,降低IO
消耗的同时也提高了执行效率,譬如
UPDATE `user` SET username='lihua' WHERE id=680782;
UPDATE `user` SET age=23 WHERE id=680782;
# 合并操作成一条SQL
UPDATE user SET username='lihua',age=23 WHERE id=680782;
尽可能使用IN
代替OR
语句
禁止使用ORDER BY RAND()
随机排序语句
会把表中所有符合条件的数据装载到内存中,然后在内存中对所有数据根据随机生成的值进行排序,并且可能会对每一行都生成一个随机值,如果满足条件的数据集非常大,就会消耗大量的 CPU
和 IO
及内存资源。
WHERE
语句中进行计算对列进行函数转换或计算时会导致无法使用索引。
# 索引会失效
SELECT `name` FROM `table` WHERE DATE(create_date)='20190308';
# 灵活使用[推荐]
SELECT `name` FROM `table` WHERE create_date>='20190308' AND create_date<'20190309';
UNION ALL
而不是使用UNION
在已知数据没有重复或无须删除重复行的前提下,因为UNION
需要重复值扫描,降低效率。
大批量的操作应当合理平均分批次处理,防止死锁影响业务,同时尽量将跑批这种大操作至于业务低峰时段操作。
MySQL
不擅长数学运算和逻辑判断。
索引会失效。
SQL
语句简单化
使用事务尽量简单化,同时控制事务执行的时间
时间长会导致长时间锁表,造成死锁,进而影响业务。
IN
语句参数的个数尽量控制在1000以内
注意LIMIT
分页查询效率,LIMIT
越大效率越低
在使用LIMIT
做分页时,更改巧妙地处理查询,例如下面的例子,使用S1
替换成S2
,将有效地提高查询的效率。
# S1
SELECT `username` FROM `user` LIMIT 10000,20;
# S2
SELECT `username` FROM `user` WHERE id>10000 LIMIT 20;
尽可能使用EXIST|NOT EXIST
替代IN | NOT IN
禁止使用LIKE
添加%
前缀进行模糊查询
%
前置会导致索引失效
以下是针对具体的业务表制定的规范。
TPL_AAABBB
。其中 AAA 是终端号,高位不足补零;BBB 是递增的序号,从 0 开始。同一个终端的不同模板,BBB 不能相同。示例:太和公众号的第 7 条消息,template_code 命名为 TPL_008006。目前已有的消息模板代码保留不变,后面的业务新增消息代码,要遵守此规范。