mysql数据库架构设计与优化

mysql数据库架构设计与优化

2019-04-23 20:51:20  阅读数 179  收藏 更多
分类专栏： MySQL
 
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_37735385/article/details/89480900

 

数据库设计规范


以上所有规范并非完全不能违背，只是如果不符时，要和公司dba团队确认是否可以做相关操作

 

数据库设计规范

1. 数据库命名规范

  • 所有数据库对象名称必须使用小写字母并用下划线分隔
    mysql是大小写敏感的，sql除外。不同的数据库名：Dbname、dbname；不同的表名：Table、table
  • 所有数据库对象名称，禁止使用mysql保留关键字
    比如以下，查询的列表中有from关键字，必须用反单引号转义才行，否则会报错！
    select id,username,`from`,age from tb_user;
    常见的mysql关键字
  • 数据库对象的命名要能做到见名知意，并且最好不要超过32个字符
    例如：用户账号表 user_account
  • 临时库表必须以tmp为前缀，并以日期为后缀
  • 备份库，备份表必须以bak为前缀并以日期为后缀
  • 所有存储相同数据的列明和列类型必须一致
    在这里插入图片描述
    比如以上和以下的两个表中：customer_id列明，和列类型完全一致
    在这里插入图片描述

2. 数据库基本设计规范

  • 所有表没有特殊情况必须使用Innodb引擎
    5.6以后的默认存储引擎
    支持事务，行级锁，更好的恢复性，高并发下性能更好
  • 数据库和表的字符集统一使用UTF8
    utf8的兼容性更好
    不是说一定要用utf8，但是库表的字符集一定要统一，统一字符集可以避免由于字符集转换产生的乱码
    mysql中utf8字符集汉字占3个字节（比如定义：user_name varchar(255)会占用765个字节），ascII码占用1个字节。
  • 所有表和字段都要添加注释
    使用comment从句添加表和列的备注
    从一开始就进行数据字典的维护，项目前期不注意维护项目字典，项目后期如果人员变化比较大，就会造成不知道某个字段含义，等难以进行维护，必须读代码去了解含义。
  • 尽量控制单表数据量的大小， 建议控制在500万以内
    500万并不是mysql数据库的限制
    mysql最多可以存储多少万数据，取决于存储设置和文件系统
    可以用历史数据归档，分库分表等手段来控制数据量的大小
  • 谨慎使用mysql分区表
    分区表在物理上表现为多个文件，在逻辑上表现为一个表
    谨慎选择分区键，跨分区查询效率可能更低
    建议采用物理分表的形式管理大数据
  • 尽量做到冷热数据分离，减小表的宽度（冷数据：不常用的数据；热数据：常用的数据）
    mysql限制最多存储4096列，并且每一行的数据不能超过65535个字节。
    减少磁盘io，保证热数据的内存缓存命中率
    更有效的利用缓存，避免读入无用的冷数据（不可否认，很多人喜欢select *，会导致很多无用的冷数据被加载）
    把表中的列进行垂直拆分，经常一起使用的列放到一个表中，避免过多关联操作。
  • 禁止在表中建立预留字段
    预留字段的命名很难做到见名知意。
    预留字段无法确认存储的数据类型，所以无法选择合适的数据类型。
    对预留字段的类型的修改，会对表进行锁定。
    修改一个字段的开销，比增加一个字段更大。
  • 禁止在数据库中存储图片，文件等二进制数据
    文件一般都很大，会极大增加数据库的开销
  • 禁止在线上做数据库压力测试
    会产生大量垃圾数据，给以后带来不必要的麻烦
  • 禁止从开发环境，测试环境直连生产环境数据库
    如果直接从开发环境连接生产环境，很容易对生产环境的数据的完整性造成破坏。

3. 数据库索引设计规范

  • 限制每张表的索引的数量，建议单张表的索引数量不超过5个
    列的数量一般和索引的数量成正比，前面限制列的数量同时也是为了减少索引的途径。
    索引不是越多越好！索引可以提高效率，同时也会降低效率。
    索引是会占据一定的物理空间的。
    禁止给表的每个字段建立单独的索引。
  • 每个Innodb表必须有一个主键
      Innodb是按照主键索引的顺序来组织表的！如果没有为表指定主键，mysql会优先选择第一个非空唯一索引作为主键，如果连非空唯一索引都没有，mysql会生成一个占6个字节的主键，而这个主键的性能并不是最好的。
      不使用更新频繁的列作为主键，不使用多列主键。
      不使用UUID，MD5，HASH，字符串列作为主键。因为这些并不能保证生成的数值是有顺序的，新生成的数据可能会排序到之前的某个位置，增加io操作，浪费数据库开销。
      主键建议使用自增ID值
  • 常见索引列建议
    select 、update、delete语句的where从句中的列
    包含在order by、group by、distingct中的字段
    多表join的关联列
  • 如何选择索引列的顺序
    索引列是按照从左向右的顺序使用的
    区分度最高的列放在联合索引的最左侧
    尽量把字段长度小的列放在联合索引的最左侧
    使用最频繁的列放到联合索引的左侧
    注意合理选择符合索引键值顺序
  • 避免建立冗余索引和重复索引（冗余索引：列被多个索引包含；重复索引：索引列完全重复）
    如：primary key(id)、index(id)、unique index(id)-----对于id是完全重复的。
    如：index(a,b,c)、index(a,b)、index(a)-----这三个索引都包含a，产生了冗余。
  • 对于频繁的查询优先考虑使用覆盖索引
    覆盖索引：包含了所有查询字段的索引。
    避免Innodb表进行索引的二次查找
    可以把随机io变为顺序io加快查询的效率
  • 尽量避免使用外键
    不建议使用外键约束，但一定在表与表的关联键上建立索引。
    外键可以用于保证数据的参照完整性，但建议在业务端实现。
    外键会影响父表和子表的写操作，从而影响性能。

4. 数据库字段设计规范

  • 优先选择符合存储需要的最小的数据类型
    将字符串转化为数字类型存储
    INT_ATON('255.255.255.255')=4294967295
    INET_NTOA(4294967295)='255.255.255.255'
    对于非负整数来说，要优先使用无符号整型来存储。因为：无符号相对于有符号可以多出一倍的存储空间。
    SIGNED INT -2147483648~2147483647
    UNSIGNED INT 0~429467295
    注意：
    在mysql中VARCHAR(N)中的N代表的是字符数，而不是字节数。其他的关系型数据库可能是字节，N/3算可以容纳多少中文。
    使用UTF8存储汉字，VARCHAR(255)=765个字节。
    过大的长度会消耗更多的内存。
  • 避免使用TEXT、BLOB数据类型
    text分为：TinyText、Text、MidumText、LongText。Text可以存储下64k的数据。
    建议把BLOB或是TEXT列分离到单独的扩展表中。
    TEXT或BOLB类型只能使用前缀索引，并且这种类型不要有默认值。
  • 避免使用ENUM数据类型
    首先说明：enum类型的值是首先把类似varchar类型的值转化为int类型进行存储，减小存储开销这是好的一点，但是毙大于利。
    （1）修改ENUM值需要使用ALTER语句。对表级别的操作存在误操作的风险，带来不可预知的后果。
    （2）enum类型的order by操作效率低，需要额外操作。因为：如果是varchar类型的值enum会先将int类型转化为varchar再排序，比较消耗性能。
    （3）禁止使用数值作为enum的枚举值。因为：enum本身就是把int型外的转化为int存储，如果本身存储的值就是int会很容易造成逻辑上的混淆，一般如果我们存储的就是整型，就直接存储为整型。
  • 尽量把所有列定义为NOT NULL
    （1）索引null列需要额外的空间来保存，所以要占用更多的空间。
    （2）进行比较时要对null值做特别的处理。
  • 不要用字符串存储日期类型的数据
    （1）无法用日期函数进行计算和比较
    （2）用字符串存储要占用更多的空间，如果用字符串要至少占用16个字节，而用datetime只需要8个字节
  • 使用TIMESTAMP或DATETIME类型存储时间
    （1）timestamp存储的区间为1970-01-01 00:00:01~2038-01-19 03:14:07
    （2）timestamp占用4个字节和int相同，但比int可读性高
    （3）超出timestamp区间的使用datetime类型
  • 同财务相关的金额类数据，必须使用decimal类型
    （1）decimal类型为精准浮点数，在计算时不会丢失精度。
    （2）占用的空间由定义的宽度决定
    （3）可用于存储比bigint更大的整型数据

5. sql开发规范

  • 建议使用预编译语句进行数据库操作
    在这里插入图片描述
    （1）只传参数，比传递sql语句更高效
    （2）可以防范sql注入的风险
    （3）相同语句可以一次解析，多次使用，提高处理效率
  • 避免数据类型的隐式转换
    （1）隐式转换，会导致索引失效。
  • 合理充分利用存在索引，而不是盲目增加索引
    （1）避免使用双%号的查询条件，如：a like ‘%abc%’，去掉左边的%可以利用索引。
    （2）一个sql只能利用到符合索引中的一列进行范围查找。
    （3）使用left join或not exists来优化not in，因为not in会导致索引失效。
  • 程序连接不同的数据库使用不同的账号，禁止跨库查询
    （1）为数据库迁移和分库分表留出余地
    （2）降低业务的耦合度
    （3）避免权限过大而产生的安全风险
  • *禁止使用select ，必须使用select <字段列表>查询
    （1）消耗更多的cup和IO以及网络带宽资源，查询了更多无用数据。
    （2）无法使用覆盖索引
    （3）可减少表结构变更带来的影响
  • 禁止使用不含字段列表的INSERT语句
    （1）insert into t values('a',1);应该明确字段列表insert into t(c1,c2) values('a',1);
    （2）可以减少表结构变更带来的影响
  • 避免使用子查询，可以把子查询优化为join操作
    （1）子查询的结果集无法使用索引
    （2）子查询会产生临时表操作，如果子查询数据量大则严重影响效率。
    （3）子查询的临时表无索引，会产生大量慢查询，消耗过多cpu以及io资源。
  • 避免使用JOIN关联太多的表
    （1）每join一个表会多占用一部分内存（join_buffer_size）
    （2）会产生临时表操作，影响查询效率
    （3）mysql最多允许关联61个表，建议不超过5个
  • 减少同数据库的交互次数
    （1）数据库更适合批处理操作
    （2）合并多个相同的操作到一起，可以提高处理效率
alter table t1 add column c1 int,change column c2 int ...
  • 1
  • 使用in代替or
    （1）in的值不要超过500个 。
    （2）in操作可以有效利用索引，而用or大多数情况下很少利用到索引。
  • 禁止使用order by rand()进行随机排序
    （1）会把表中所有符合条件的数据转载到内存中进行排序
    （2）会消耗大量的cpu以及io和内存资源
    （3）对于这种获取随机数据可以采用：在程序中获取一个随机值，可以是id，然后按照这个随机值从数据库中获取数据，也即这种随机获取的业务在代码中去实现。
  • WHERE从句中禁止对列进行函数转换和计算
    （1）对列进行函数转换或计算会导致无法使用索引
where date(createtime)='20190423'--这样会导致在createtime列上的索引失效
where createtime>='20190423' and createtime<'20190423'--这样索引不会失效
  • 1
  • 2
  • 在明显不会有重复值或可以允许有重复值时，使用UNION ALL而不是UNION
    （1）union会把所有数据放到临时表中然后进行去重操作
    （2）union all不会对结果集进行去重操作
  • 拆分复杂的大sql为多个小sql
    （1）mysql一个sql只能使用一个cpu进行计算
    （2）sql拆分后可以通过并行执行来提高处理效率

6. 数据库操作规范

  • 超过100万行的批量写操作，要分批多次进行操作
    （1）大批量操作可能会造成严重的主从延迟
    （2）binlog日志为row格式时会产生大量的日志
    （3）避免产生大事务操作
  • 对大表数据的修改一定要谨慎，有可能会造成严重的锁表操作，尤其是生产环境，是不能忍受的
  • 对大表使用pt-online-schema-change工具修改表结构
    （1）避免大表修改产生的主从延迟
    （2）避免对表修改时进行锁表
  • 禁止为程序使用的账号赋予super权限
    （1）当达到最大连接数限制时，还允许一个由super权限的用户连接。
    （2）super权限只能留给dba作为处理问题的账号使用。
  • 程序连接连接数据库账号，遵循权限最小原则
    （1）程序使用的数据库账号只能在一个db下使用，不准跨库。
    （2）程序使用的数据库账号，原则上不准有drop权限。

其他

  • 可以了解一下数据库三范式
posted @ 2019-12-04 20:06  阿波罗任先生  阅读(530)  评论(0)  编辑  收藏  举报