MySQL数据库设计开发规范汇总.docx

《MySQL数据库设计开发规范汇总.docx》由会员分享，可在线阅读，更多相关《MySQL数据库设计开发规范汇总.docx（11页珍藏版）》请在第一文库网上搜索。

1、MySQ1数据库设计开发规范汇总出公【摘要】本文介绍了MySQ1数据库命令规范、数据库基本设计规范、数据库字段设计规范、索引设计规范、常见索引列建议、如何选择索引列的顺序、以及数据库SQ1开发规范等。一、命名规范1库名、表名、字段名必须使用小写字母并采用下划线分割2.库名、表名、字段名禁止超过32个字符，须见名知意；3.库名、表名、字段名支持最多64个字符，统一规范、易于辨识以及减少传输量不要超过32；4.库名、表名、字段名禁止使用MySQ1保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）；5.临时库、临时表名必须以tmp_为前缀并以日期为后缀；6.备份库、备份表名必须以bak

2、为前缀并以日期为后缀；7.所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。1.所有表必须使用Innodb存储引擎没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎（MySQ15.5之前默认使用Myisam,5.6以后默认的为Innodb）Innodb支持事务，支持行级锁，更好的恢复性，高并发下性能更好。2.数据库和表的字符集统一使用utf8mb4（5.5.3版本以上支持）兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不

3、同的字符集进行比较前需要进行转换会造成索引失效；3.所有表和字段都需要添加注释使用comment从句添加表和列的备注从一开始就进行数据字典的维护；4.尽量控制单表数据量的大小，建议控制在IOOO万以内1000万并不是MySQ1数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小。5.谨慎使用MySQ1分区表业务生命周期内，评估单表数据量是否在1000万以内，超出此范围需考虑分库分表可扩展性；分区表在物理上表现为多个文件，在逻辑上表现为一个表，谨慎选择分区键，跨分区查询效率可能更低，建议采用物理分表的

4、方式管理大数据。6.尽量做到冷热数据分离，减小表的宽度MySQ1限制每个表最多存储4096歹1并且每一行数据的大小不能超过65535字节减少磁盘10,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大，也会消耗更多的IO）更有效的利用缓存，避免读入无用的冷数据经常一起使用的列放到一个表中（避免更多的关联操作）。7.禁止在表中建立预留字段预留字段的命名很难做到见名识义预留字段无法确认存储的数据类型，所以无法选择合适的类型对预留字段类型的修改，会对表进行锁定。8.禁止在数据库中存储图片，文件等大的二进制数据通常文件很大,会短时间内造成数据量快速增长，数据库进行数据库读取

5、时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时通常存储于文件服务器，数据库只存储文件地址信息。9.禁止在线上做数据库压力测试10.禁止从开发环境，测试环境直接连接生成环境数据库。三、字段设计规范1.优先选择符合存储需要的最小的数据类型列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的IO次数也就越多，索引的性能也就越差。建议：1)将字符串转换成数字类型存储，如：将IP地址转换成整形数据。2)对于非负型的数据(如自增ID、整型IP)来说，要优先使用无符号整型来存储因为：无符号相对于有符号可以多出一倍的存储空间，VARCH

6、Ar(N)中的N代表的是字符数，而不是字节数使用UTF8存储255个汉字Varchar(255)=765个字节。过大的长度会消耗更多的内存。2.避免使用TEXT、B1OB数据类型最常见的TEXT类型可以存储64k的数据，建议把B1OB或是TEXT列分离到单独的扩展表中。MySQ1内存临时表不支持TEXT、B1OB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。且对于这种数据，MySQ1还是要进行二次查询，会使SqI性能变得很差，但是不是说一定不能使用这样的数据类型。如果一定要使用，建议把B1OB或是TEXT列分离到单独的扩展表中，查询时

7、一定不要使用se1ect*而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询。注意：TEXT或B1OB类型只能使用前缀索引，因为MySQ1对索引字段长度是有限制的，所以TEXT类型只能使用前缀索引，并且TEXT列上是不能有默认值的。3.避免使用ENUM类型修改ENIJM值需要使用A1TER语句ENUM类型的ORDERBY操作效率低，需要额外操作禁止使用数值作为ENiJM的枚举值4.尽可能把所有列定义为NOTNu11原因：索引NiJ11列需要额外的空间来保存，所以要占用更多的空间；进行比较和计算时要对NU11值做特别的处理。5.使用TIMESTAMP存储时间T1MESTAMP存储的

8、时间范围1970-01-0100:00:01Z2038-01-19-03:14:07。TIMESTAMP使用4字节，DATET1ME使用8个字节，同时TIMESTAMP具有自动赋值以及自动更新的特性。6.财务相关的金额类数据必须使用decima1类型1）非精准浮点：f1oat,doub1e2）精准浮点：decima1Decima1类型为精准浮点数，在计算时不会丢失精度。占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。7.用DECIMA1代替F1OAT和DOUB1E存储精确浮点数浮点数相对于定点数的优点是在长度一定的情况下，

9、浮点数能够表示更大的数据范围；浮点数的缺点是会引起精度问题1）将字符转化为数字2）使用TINYINT来代替ENUM类型3）字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量8.使用UNSIGNED存储非负整数同样的字节数，存储的数值范围更大。如tinyint有符号为T28T27,无符号为0-255；INT类型固定占用4个字节存储9.使用INTUNSIGNED存储IPV410.使用VARBIY存储大小写敏感的变长字符串I1禁止在数据库中存储明文密码建立索引的目的是：希望通过索引进行数据查找，减少随机IO,增加查询性能，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。索引是一把双刃剑

10、，可提高查询效率，但也会降低插入和更新的速度并占用磁盘空间。1.单张表中索引数量不超过5个限制每张表上的索引数量，建议单张表索引不超过5个索引；索引可以提高效率同样可以降低效率。索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQ1优化器生成执行计划时间，降低查询性能。2.禁止给表中的每一列都建立单独的索引5.6版本之前，一个sq1只能使用到一个表中的一个索引，5.6以后，虽然有了合并索引的优化方式，但远没有

11、使用联合索引的查询方式效率高。3.Innodb表必须要有主键Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种Innodb是按照主键索引的顺序来组织表的。不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）不要使用UUID、MD5、HASH、字符串列作为主键（无法保证数据的顺序增长）。主键建议使用自增H）值。4.单个索引中的字段数不超过5个对字符串使用前缀索引，前缀索引长度不超过10个字符；举例：如有一个CHAR（200）列，在前10个字符内，多数值是唯一的，就可不要对整个列进行索引。对前10个字符进行索引能够节

12、省大量索引空间，也可能会使查询更快。5.表主键建议1）表必须有主键，不使用更新频繁地列作为主键2）尽量不选择字符串列作为主键3）不使用UUID、MD5、HASH作为主键4）默认使用非空的唯一键5）主键建议选择自增或发号器重要的SQ1必须被索引：SE1ECT.UPDATE、DE1ETE语句的WHERE条件列ORDERBY、GROUPBY、DISTINCT的字段多表JOIN的字段6.区分度最大的字段放在索引前面7.核心SQ1优先考虑覆盖索引se1ect的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。8.避免冗余或重复索引合理创建联合索引（避免冗余），index（a

13、,b,c）相当于index（a）、index（a,b）、index（a,b,c）1）索引不是越多越好，按实际需要进行创建，每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能2）不在低基数列上建立索引，例如性别3）不在索引列进行数学运算和函数运算9.尽量避免使用外键约束1）不建议使用外键约束（foreignkey）,但一定要在表与表之间的关联键上建立索引；2）外键可用于保证数据的参照完整性，建议在业务端实现；3）外键会影响父表和子表的写操作从而降低性能。10.不使用%前导的查询，如Iike“%xxx”，无法使用索引I1不使用反向查询，如notin/notIike无法使用索引，导致全表扫描，

14、全表扫描导致buffeoo1利用降低；12.索引列建议1）出现在SE1ECT、UPDATE、DE1ETE语句的WHERE从句中的列；2）包含在ORDERBY、GROUPBY、DISTINCT中的字段；3）多表join的关联列注意：并不要将符合1和2中的字段的列都建立一个索引，通常将1、2中的字段建立联合索引效果更好13.如何选择索引列的顺序1）区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）；2）尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）；3）使用最频繁的列放到联合索引的左侧（这样可较少的建立一些索引）。14.

15、避免建立冗余索引和重复索引冗余/重复索引会增加查询优化器生成执行计划的时间。D重复索引示例：Prin1arykey(id)、index(id)、uniqueindex(id)2)冗余索引示例：index(a,b,c)、index(a,b)、index(a)15.优先考虑覆盖索引对于频繁的查询优先考虑使用覆盖索引。覆盖索引：即包含了所有查询字段(where,se1ect,orderyby,groupby包含的字段)的索引，覆盖索引的好处：1)避免Innodb表进行索引的二次查询Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索

16、引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。2)可以把随机IO变成顺序IO加快查询效率由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IOO五、SQ1开发规范1.建议使用预编译语句进行数据库操作预编译语句可以重复使用这些计划，减少SQ1编译所需要的时间，还可以解决动态SQ1所带来的SQ1注入的问题只传参数，比传递SQ1语句更高效相同语句可以一次解析，多次使用，提高处理效率。2.避免数据类型的隐式转换隐式转换会导致索引失效。3.充分利用表上巳经存在的索引1)避免使用双号的查询条件。如无前置%,只有后置%，是可以用到列上的索引的2)一个SQ1只能利用到复合索引中的一列进行范围查询解说：有a,b,c列的联合索引，在查询