数据库设计的实现逻辑
1、数据结构设计
逻辑设计 -> 物理设计
2、 实际工作中
逻辑设计 + 物理设计
3、 物理设计
表名 + 字段名 + 字段类型
数据库设计规范设计规范主要包括以下内容:
数据库命名规范
1、所有的数据库对象名称必须使用小写字母并用下划线分割,主要是防止不同的操作系统产生的错误
2、所有的数据库对象名称必须禁止使用数据库保留字
3、数据库对象的命名要能简明释义,并且最好不要超过32个字符
例如:用户数据库 mc_userdb 用户账号表 user_account
临时表必须以tmp 为前缀并以日期为后缀
备份库,备份表必须以bat 为前缀,并以日期为后缀
数据库基本设计规范
1、所有表必须使用innODB存储引擎 ,支持事务,行级锁、更好的恢复性,高并发下性能更佳
2、数据库和表字符集同意使用utf-8,统一字符集避免产生字符集转换产生乱码
3、MySQL中utf-8字符集汉字占用3个字节
4、所有表和字段都需要添加注释,保证后期的维护
5、尽量控制单表的数据量的大小,建议控制在500万以内,修改表结构。备份、恢复数据都会产生很大的问题,5百万并不是MySQL数据库的限制,MySQL最多可以储存多少条数据取决于储存设置和文件系统,解决方法:可以使用历史数据的归档,分库分表等手段来控制数据量的大小
6、谨慎使用MySQL分区表
:分区表在物理上表现为多个文件,在逻辑上表现为一个表
:谨慎选择分区键,跨分区查询效率可能更低,建议采用物理分表的方式管理大数据,但是这样的复杂度更高
7、尽量做到冷热数据分离,减少表列数
:减少磁盘IO,保证热数据的内存缓存命中率
:利用更有下的利用缓存,避免读入无用的冷数据
:经常一起使用的列放到一个表中
8、禁止在表中建立预留字段
:预留字段的命名很难做到见名识义
:预留字段无法确认储存的数据类型,所以无法选择合适的类型
:对预留字端类型的修改,会对表进行锁定
9、禁止在数据库中存储图片,文件等二进制数据,禁止在线上做数据库压力测试,禁止从开发环境,测试环境连生产环境的数据库
总结
所有表字段必须使用innODB存储引擎
所有表字段都要有备注信息,并使用utf-8字符集
要做到控制表的大小,并把冷热数据进行分离
禁止使用预留字段及在数据库表中存储大的二进制
数据库索引设计规范
索引对数据的查询性能来说是非常重要的,好的而索引可以提供数据的性能,反之,降低数据库的性能
1、不要滥用索引
限制每张表上的索引的数量,建议单张表的索引的数量超过五个,当然,索引的列的数量与列成正比
2、每个innODB表必须有一个主键
:不要使用更新频繁的列作为主键,不使用多列主键
:不用UUID,MD5,HASH,字符串列作为主键
:主键建议选择使用自增ID作为主键
3、常见索引列建议
:select 、update、delete语句中where从句中的列
:包含order by 、group by 、distinct 的字段
:多表join 的关联列
4、如何选择索引列的顺序
索引列的顺序,数据库是从左到右的顺序来使用的,联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效。
所以说创建复合索引时,应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时,复合索引非常有用;仅对后面的任意列执行搜索时,复合索引则没有用处。
当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引, 联合索引的话, 它往往计算的是第一个字段(最左边那个), 这样往往会走错索引. 如:
索引Index_1(Create_Time, Category_ID), Index_2(Category_ID)
:区分度最高的列放在联合索引的最左侧
:尽量把字段长度最小的列放在联合索引的最左侧
:使用最频繁的列放在联合索引的左侧
5、避免建立冗余的索引和重复索引
:重复索引:例如:primary key(is) 、index(id)、unique index(id)
:冗余索引 例如:index(a,b,c) 、index(a,b),index(a)
6、对于频繁查询的索引优先考虑使用覆盖索引
覆盖索引:就是包含所有的查询索引
好处:避免innODB表进行索引的二次查询,可以把随机IO变为顺序IO,可以加快查询的效率
7:尽量避免使用外键
不建议使用外键约束,但一定在表与表之间的关联键上建立索引
总结
每个innODB 表都需要一个主键
限制表上的索引的数量,避免建立重复和冗余的索引
注意合理选择复合索引键值的顺序
数据库字段设计规范
字段类型的选择,会直接影响数据库的性能。
1、字段优先选择符合存储需要的最小数据类型,对于非负整形,要优先使用无符号整形来存储,无符号相对于有符号可以多出一倍的存储空间
:将字符串转化为数字类型存储
例子:将IP地址转化为整数
INET_ATON(‘255.255.255.255’) = 4294967295;
INET_NTOA(4294967295) = 255.255.255.255;
:VARCHAR(N)中的是字符数,而不是字节数
:使用UTF-8存储汉字,varchar(255) = 765个字节
:过大的长度会消耗更多的内存
2、 避免使用TEXT、BLOB数据类型
: 如果使用,建议把BLOB或是TEXT列分离到单独的扩展表中
:TEXT 或者BLOB类型只能使用前缀索引
前缀索引: 选择索引列的最左n个字符来建立索引。这样就大大节约了索引空间,进而提高索引效率。
缺点:MySQL无法使用前缀索引做ORDER BY 、GROUP BY 和覆盖扫描。
3、避免使用ENUM数据类型
:修改ENUM值需要使用ATER语句
:ENUM类型的order by 操作效率低,需要额外操作
:禁止使用数值作为ENUM的枚举值
4、尽可能把所有列定义为 非空
:索引null列需要额外的空间来保存,所以,要占用更多的空间
:进行比较和计算时要对NUll值做特别的处理
数据库SQL开发开发规范
1、建议使用预编译语句进行数据库操作
:只传参数比传递SQL语句更高效
:相同语句可以一次解析多次使用,提高处理效率
:防止SQL注入
2、避免数据类型的隐式转换
:隐式转换会导致索引失效
例子:
selec * from table where ID = ‘123123’;
3、充分使用表上的已经存在的索引
:避免使用双%号的查询条件,如 name ike %繁%;
:一个SQL只能利用到复合索引中的一列进行范围查询
:使用left join 或 not exits 来优化not in 操作
4、数据库设计时,应该要考虑以后数据库的扩展
5、程序连接不同的数据库使用不同的账号,禁止跨库查询
:为数据库分库分表和迁移留出空间
:降低业务的耦合度
:权限过大的风险
6、禁止使用select * ,必须使用select < 字段名> 查询
:消耗更多的CPU和IO以及网络宽带资源
7、避免使用子查询,可以把子查询优化为join操作
:子查询的结果集无法使用索引
:子查询会产生临时表操作,如果子查询数据量很大,则严重影响效率
:消耗过多的CPU及IO资源
8、避免使用join太多的表
:每关联一个表会多占用一部分内存(join_buffer_size)
:会产生临时表操作,影响查询效率
:MySQL最多允许关联61个表,但建议不超过5个
9、减少同数据库的交互次数
:数据库更适合处理批量操作
:合并多个相同的操作到一起没可以提高处理效率
10、使用in 代替or
:in 的值不要超过500
:in操作可以有效利用索引
11 、禁止使用order by rand() 进行随机排序
:会把表中所有符合条件的数据加载到内存中进行排序
:会消耗大量的CPU和IO及内存资源
:推荐在程序中获取一个随机值,然后从数据库中获取数据的方式
12、where从句中禁止对列进行函数转换和计算
:对列进行函数转换或计算会导致无法使用索引
例子:
where date(createtime) = “20170302”
13、在明显不会有重复值是使用union all 而不是union
:union 会把所有数据放到临时表表中后再进行去重操作
:union all 不会再对结果集进行去重操作
14、拆分复杂的SQL分为多个小SQL
MySQL 一个SQL只能使用一个CPU进行计算
SQL拆分可以通过并行执行来提高处理效率
数据库操作行为规范
1、超过一百万行的批量写操作,要分批多次进行操作
:大量的操作可能会造成严重的主从延迟
:binlog日志为row格式时,会产生大量的日志
:避免产生大事务操作
2、对大表数据结构的修改一定要慎重,会造成严重的锁表操作。尤其时生产环境没事不能忍受的
:对于大表使用pt-online-schema-change 修改表结构
3、禁止为程序使用的账号赋予SUPER权限,当连接数限制时,还允许1个有super 权限的用户连接,super权限只能留给DBA处理问题的账号使用
4、对于程序连接数据库账号,遵循权限最小原则
:程序使用数据库账号只能在一个DB下使用,不准跨库。程序使用的账号原则上不准有drop权限