跳至主要內容

MySQL 设计规范

安格mysqlmysql大约 13 分钟...

一、数据库命名规范

  1. 所有的数据库对象名称必须使用小写字母并用下划线表示,因为默认情况下,mysql 对大小写敏感,mysql 数据库本质上是 linux 系统下的一个文件,而 linux 系统是大小写敏感的

  2. 所有数据库对象名称禁止使用 mysql 保留关键字

  3. 数据库对象的命名要能做到见名知意,并且最好不要超过 32 个字符。太长不方便使用,并且会在传输时增加网络开销

  4. 临时表必须以 tmp_ 为前缀并以日期为后缀

  5. 备份表必须以 bak_ 为前缀并以日期为后缀

  6. 所有存储相同数据的列名和列类型必须一致,比如 user 表中的 id 和 order 表中的 user_id

二、数据库基本设计规范

  • 所有表必须使用 Innodb 存储引擎

    Innodb 引擎是 5.6 之后的默认存储引擎;mysql5.5 之前使用 Myisam (默认存储引擎)

    Innodb 优点:支持事务,行级锁,更好的恢复性,高并发下性能更好

  • 数据库和表的字符集统一使用 UTF-8

    如果要存储一些如表情符号的,还需使用 UTF-8 的拓展字符集

    数据库,表,字段字符集一定要统一,统一字符集可以避免由于字符集转换产生的乱码

    在 mysql 中 UTF-8 字符集,汉字占 3 字节,ASCII 码占 1 字节

  • 所有表和字段都需要添加注释

  • 从一开始就进行数据字典的维护,即数据库说明文档

  • 尽量控制单表数据量大小,

    建议控制在 500 万以内,虽然 500 万并不是 mysql 的数据库限制,但是会给修改表结构,备份,恢复带来很大困难。

    单表可存储数据量大小取决于存储设置和文件系统

    想减少单表数据量:历史数据归档 (常见于日志表),分库分表 (常见于业务表),分区表

    建议不要使用 mysql 分区表,因为分区表在物理上表现为多个文件,在逻辑上表现为一个表。如果一定要分区,请谨慎选择分区键,跨分区查询效率比查询大数据量的单表查询效率更低

    建议采物理分表的方式管理大数据,但是对应用程序的开发要求和复杂度更高

  • 尽量做到冷热数据分离,减少表的宽度 (字段数)

    减少磁盘 IO,保证热数据的内存缓存命中率,更有效的利用缓存,避免读入无用的冷数据

    这样的话,就要对表的列进行拆分,将经常使用的列放到一个表中,可以避免过多的关联操作,也可以提高查询性能

  • 禁止在表中建立预留字段

    预留字段很难做到见名知义,预留字段无法确定存储的数据类型,后期如果修改字段类型,会对全表锁定,严重影响数据库的并发性

    对目前 mysql 来说,修改一个字段的成本要远远大于增加一个字段的成本

  • 禁止在数据库中存储图片,文件等二级制数据

    这类数据如果要存,就得使用 blob 或者 text 这样的大字段加以存储,会影响数据库的性能

    文件这种通常所占数据容量很大,会在短时间内造成数据库文件的快速增长,而数据库在读取数据时,会进行大量的随机 IO 操作,如果数据文件过大,IO 操作会非常耗时,从而影响数据库性能

    正确做法是将这类数据存储在文件服务器中,而数据库只村存储地址信息

  • 禁止在线上做数据库压力测试

    会对正常业务造成影响,也会产生很多垃圾数据

    建议建立专门的压力测试数据库,进行测试,然后对比测试服务器和线上服务器的硬件环境,评估线上数据库的性能

  • 禁止从开发环境,测试环境直连生产环境数据库

三、索引设计规范 (Innodb 中主键实质上是一个索引)

限制每张表上索引数量,建议单表不超过 5 个索引。索引并不是越多越好,可以提高查询效率,但是会降低插入和更新的效率。甚至在一些情况下,还会降低查询效率,因为 mysql 优化器在选择如何优化查询时,会根据统计信息,对每一个可用索引来进行评估,以生成一个最好的执行计划,如果同时有很多索引都可以用于查询,就会增加 mysql 查询优化器生成查询计划的时间。

每个 Innodb 表都必须有一个主键。Innodb 是一种索引索引组织表,是指数据存储的逻辑顺序和索引的顺序是相同,Innodb 是按照主键索引的顺序来组织表的,因此,每个 Innodb 表都必须要有一个主键,如果我们没有指定主键,那么 Innodb 会优先选择表中第一个非空唯一索引来作为主键,如果没有这个索引,那么 Innodb 会自动生成一个占 6 字节的主键,而这个主键的性能并不是最好。

不使用更新频繁的列作为主键,不使用多列联合主键。因为 Innodb 是一种索引索引组织表,如果主键上的值频繁更新,就意味着数据存储的逻辑顺序频繁变动,必然会带来大量的 IO 操作,降低数据库性能。

不要使用 uuid,md5,hash,字符串列作为主键。因为这种主键不能保证主键的值是顺序增长的,如果后来的主键值在已有主键值的中间段,那么这个主键插入的时候,会将所有主键值大于它的列都向后移。

最好选择能保证值的顺序为顺序增长的列为主键。并且数据不能重复,建议用 mysql 自增 id 建立主键

在 select,delete,update 的 where 从句中的列

包含在 order by,group by,distinct 字段中的列

多表 join 的关联列:mysql 对关联操作的处理方式只有一种,那就是嵌套循环的关联方式,所以这种操作的性能对关联列上的索引的依赖性很大

复合索引:从左到右的顺序来使用

区分度 (列中 group by 的数目和此列总行数的比值趋近于 1) 最高的列放在联合索引的最左侧

在区分度差不多的情况下,尽量吧字段长度小的放在联合索引的最左侧,因为同样的行数,字段小的文件也小,读取时 IO 性能更优

使用最频繁的列放在联合索引的左侧,这样的话,可以较少地建立索引就能满足需求

避免建立冗余索引和重复索引

对于频繁的查询优先使用覆盖索引

就是包含了所有查询字段的索引,这样可以避免 Innodb 表进行索引的二次查找,并可以把随机 IO 变为顺序 IO 提高查询效率

尽量避免使用外键

mysql 和别的数据库不同,会自动在外键上建立索引,会降低数据库的写性能

建议不使用外键约束,但是一定要在表与表之间的关联键上建立索引,虽然外键是为了保证数据的完整性,但是最好在代码中去保证。

四、字段设计规范

优先选择符合存储需要的最小的数据类型

尽量将字符串转化为数字类型存储:如将 ip 存储为数字:inet_aton (‘255.255.255.255’) = 4294967295 , 反之, inet_ntoa (4294967295) = ‘255.255.255.255’

对于非负整型数据,优先使用无符号整型来存储,如:id,age, 无符号相对于有符号,可以多出一倍的存储空间

mysql 中,varchar (n) 中 n 表示字符数而不是字节数

避免使用 textblob 来存储字段,这种类型只能使用前缀索引,如果非要使用,建议将这种数据分离到单独的拓展表中

避免使用 enum 类型。枚举本身是一个字符串类型,但是内部确是用正数类型来存储的,所以最多可存储 65535 种不同的值,修改的话必须使用 alter 语句,直接修改元数据,有操作风险;order by 效率低,必须转换并无法使用索引,禁止使用数值作为 enum 值,因为 enum 本身是索引顺序存储的,会造成逻辑混淆

尽可能把所有列定义为 not null。

索引 null 列需要额外的空间来保存,占更多空间

进行比较和计算时,对 null 值作特别的处理,可能造成索引失效

禁止使用字符串来存储日期型数据。

无法使用日期函数计算比较

字符串存储要占更多的内存空间,datetime (8 字节) 和 timestamp (本身是以 int 存储,占 4 字节,范围:1970-01-01 00:00:012038-01-19 03:14:07)

财务相关数据,使用 decimal 类型 (精准浮点类型,在计算时不丢失精度)。

五、SQL 开发规范

  • 建议使用预编译语句 (prepareStatment) 进行数据库操作

    可以同步执行预编译计划,减少预编译时间

    可以有效避免动态 sql 带来的 SQL 注入的问题

    只传参数,一次解析,多次使用,比传递 sql 语句更高效

  • 避免数据类型的隐式转换

    一般出现在 where 从句中,会导致索引失效,如:select id,name from user where id = ‘12’;

  • 充分利用已存在的索引

  • 避免使用双 % 的查询条件,不走索引

  • 一个 SQL 只能利用到复合索引中的一列进行范围查询

  • 使用 left joinnot exists 来优化 not in 操作

  • 程序连接不同的数据库使用不同的账号,禁止跨库查询

  • 禁止使用 select * 来查询,必须用字段名

    可能会消耗更多的 cpu 和 IO 以及网络资源

    无法使用覆盖索引

    可以减少表结构变更对已有程序的影响

  • 禁止使用不含字段列表的 insert 语句。

    可以减少表结构变更对已有程序的影响

  • 禁止使用子查询

    虽然可使 sql 可读性好,但是缺点远远大于优点

    子查询返回的结果集无法使用索引,结果集会被存储到一个临时表中,结果集越大性能越低

    把子查询优化为 join 操作,但是并不是所有的都可以优化为 join,一般情况下,只有当子查询是在 in 字句中,并且子查询是一个简单的 sql (不包含 union,group by,order by,limit) 才能转换为关联查询

  • 避免 join 过多的表

    每 join 一个表会占一部分内存 (join_buffer_size)

    会产生临时表操作,影响查询效率

    mysql 最多允许关联 61 个表,建议不超过 5 个

  • 减少同数据库的交互次数

  • 数据库更适合处理批量操作

  • 合并多个相同的操作到一起,提高处理效率

  • 使用 in 代替 or

    in 的值不要超过 500 个

    in 操作可以有效利用索引

  • 禁止使用 order by rand () 进行随机排序

    会把表中所有符合条件的数据装载到内存中进行排序

    会消耗大量的 cpu 和 io 及内存资源

    推荐在程序中获取随机值

  • 禁止在 where 从句中对列进行函数转换和计算

    导致无法使用相关列上的索引

    where date (create_time)=’20170901’ 写成 where create_time >= ‘20170901’ and create_time < ‘20170902’

  • 在明显不会有重复值时使用 union all 而不是 union

    union 会把所有数据放在临时表中后再进行去重操作,会多消耗内存,IO,网络资源

    union all 不会再对结果集进行去重操作

  • 拆分复杂的大 sql 为多个小 sql

    目前 mysql 中一个 sql 只能使用一个 cpu 计算,不支持多 cpu 并行计算

    sql 拆分后可以通过并行执行来提高处理效率

六、数据库操作行为规范

主要面向手动操作数据库的行为

超过 100 万的批量写操作,要分批多次进行操作

主从复制中:大批量操作可能会造成严重的主从延迟,因为当主库执行完成后,才会在从库执行

binlog 日志为 row 格式时会产生大量的日志

避免产生大量事务,产生阻塞,占满可用连接

对大表数据结构的修改一定要谨慎

可能会造成严重的锁表操作,尤其是生产环境,是不能忍受的

对于大表使用 pt-online-schema-change 修改表结构:

首先会建立一个与原表结构相同的新表

然后在新表上进行表结构的修改

然后把原表中的数据复制到新表中,并且增加一些触发器,以便把原表中即时新增的数据也复制到新表中

在行的所有数据复制完成之后,会在原表上增加一个很准的时间锁,同时把新表命名为原表,把原表删掉

[实际上是把一个原子的 DDL 操作分解成多批次进行]

[避免大表修改产生的主从延迟问题]

[避免在对表字段进行修改时进行锁表]

禁止为程序使用的账号赋予 super 权限

当数据库连接数达到最大限制时,允许 1 个有 super 权限的用户连接

super 权限只能留给 DBA 处理问题的账号使用

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

程序使用的数据库账号只能在一个 DB 下使用,不准跨库

程序使用的账号原则上不准有 drop 权限


转自: MySQL 设计规范open in new window

上次编辑于:
你认为这篇文章怎么样?
  • 0
  • 0
  • 0
  • 0
  • 0
  • 0
评论
  • 按正序
  • 按倒序
  • 按热度
Powered by Waline v3.1.3