| 注册
请输入搜索内容

热门搜索

Java Linux MySQL PHP JavaScript Hibernate jQuery Nginx
MichelleCha
7年前发布

Mysql数据库开发规范

   <p>前言 本规范是在项目开发中整理的一些开发规范和技巧,期望能更充分利用MySQL的特性,得到更好的性能。主要提供给需要基于MySQL做应用开发的人员作为参考,方便完成更有效率的开发。 数据库设计 数据库设计的目标三个:功能实现,可伸缩性,可用性。设计时需要平衡业务技术各个方面,做好取舍。数据库的架构设计时最重要的,80%的性能优势来自架构设计的优势。</p>    <h3><strong>2.1 版本选择</strong></h3>    <p>官方版本建议使用MySQL5.7.14,分支版本建议Mariadb10.1.14。</p>    <h3><strong>2.2 架构设计</strong></h3>    <p>Mysql数据库架构设计主要考虑读写分离、分库分表、热点数据、雪崩效应与过载保护、读写优化等方面。</p>    <p>读写分离用的是主从库的设计,至少要两台服务器,两边数据是同步的,主库负责写数据,从库负责读数据。 分库:尽可能将访问频繁的不同业务数据分在不同的数据库来存放,这样能提高并发访问效率。 分表:尽可能将大数据量的业务表采用某种分类标识来分成不同的表。可以考虑将历史数据和现实数据分开存放。 热点数据:对于经常要重复使用的数据,必须要放在内存中缓存起来,不能每次都从磁盘读取。可以采用全局内存变量、Memory Cache等。 雪崩效应是指当并发量大时,对一些表的访问会导致大量的锁出现,这样后来的数据库访问就要建立更多的数据库连接,使数据库性能下降甚至宕机。解决方法主要是优化数据库、从业务上优化设计、及时释放锁和系统资源、使用连接池等。 读写优化:读优化和热点数据类似,主要通过在内存中缓存数据实现尽量少的读取硬盘,尽量多的读取内存。写优化主要通过主键、索引实现。</p>    <h3><strong>2.3 schema设计</strong></h3>    <p><strong>2.3.1控制库和表的个数</strong></p>    <p>MySQL是单进程多线程架构的数据库,这点与SQL Server比较类似,但与Oracle多进程的架构有所不同(Oracle的Windows版本也是单进程多线程的架构)。这也就是说,MySQL数据库实例在系统上的表现就是一个进程。</p>    <p>从性能上考虑,单个MySQL库不能太大,总空间容量一般不超过100G,单库不超过500个表,因为MySQL表的表结构文件、数据文件、索引文件在操作系统上存放在schema的同一个目录下,当一个schema的表个数超过100个,即同一个目录下面的文件超过300时,操作系统管理文件的成本会大幅增加,影响服务器性能。</p>    <p><strong>2.3.2控制单表数据量</strong></p>    <p>表设计主要考虑因素有:IO高效、全表遍历、表修复快、提高并发;alter table快。 单表数据量建议控制在纯INT不超1000W,含CHAR不超500W,因为Mysql在处理大表(char的表>500W行,或int表>1000W)时,性能就开始明显降低,所以要采用不同的方式控制单表容量: A、根据数据冷热,对数据分级存储,历史归档。</p>    <p>B、采用分库/分表/分区表,横向拆分控制单表容量。</p>    <p>C、对于OLTP系统,控制单事务的资源消耗,遇到大事务可以拆解,采用化整为零模式,避免特例影响大众。</p>    <p>D、单库不要超过500个表。</p>    <p>E、单表字段数不要太多,最多不要大于50个。</p>    <p><strong>2.3.3数据冗余设计</strong></p>    <p>数据库冗余设计的目的: A、无外键时,减少多表join查询。</p>    <p>B、便于分布式设计,允许适度冗余,为了容量扩展允许适度开销。</p>    <p>C、基于业务自由优化,基于i/o 或查询设计,无须遵循范式结构设计。</p>    <p>冗余设计的应用场景: A、原有展现程序涉及多个表的查询,希望简化查询。</p>    <p>B、数据表拆分往往基于主键,而原有数据表往往存在非基于主键的关键查询,无法在分表结构中完成。</p>    <p>C、存在较多数据统计需求(count, sum等),效率低下。</p>    <p>冗余的设计思路举例: A、基于展现的冗余设计,如:</p>    <p>消息表message,存在字段 from <em>userid,to</em> userid,msg,send <em>time四个字段,而展示程序需要显示发送者姓名和性别。 通常在message表中增加冗余字段from</em> username和from <em>user</em> sex即可。 B、基于查询的冗余设计,如:</p>    <p>用户分表,将用户库分成若干数据表。基于用户名的查询和基于userid的查询都是高并发请求。用户分表可以基于userid分成多个表,同时基于用户名做对应冗余表。 C、基于统计的冗余设计,如:</p>    <p>count(*)操作,如不需要精准结果,可以直接show table status like …获得,需要精准结果,可以在缓存层增加key-value对,实时更新该key-value。同时异步更新到数据库中冗余字段,或冗余表中。</p>    <p><strong>2.3.4控制事物大小</strong></p>    <p>限制大SQL (BIG SQL)、大事务 (BIG Transaction)、大批量 (BIG Batch)。遇到大sql时,可以考虑根据业务分拆成几个小sql,尽量不在数据库做运算、复杂运算移到程序端CPU、尽可能简单应用MySQL。 如:md5() 或Order by Rand()或计算字段等操作不在数据库表上进行。</p>    <p><strong>2.3.5存储引擎选择</strong></p>    <p>默认使用InnoDB引擎。InnoDB适用于几乎99%的MySQL应用场景,而且在MySQL 5.7的系统表都改成InnoDB了,还有什么理由再死守MyISAM呢。</p>    <p><strong>2.3.6字符集</strong></p>    <p>使用优先级 utf8mb4 > utf8 > latin1</p>    <p><strong>2.3.7表主键</strong></p>    <p>显示指定自增int/bigint unsigned not null 作为主键,尽量不要使用uuid/HASH/MD5类型作为主键。</p>    <h3><strong>2.4 编码规范</strong></h3>    <p><strong>2.4.1命名规范</strong></p>    <p>Mysql对象名称最长是64个字符,为了阅读方便,我们要求对象名控制在32个字符以内。且数据库名、表名、字段名、索引名等强烈建议只用小写字符、数字、下划线组合,不使用 desc,select ,show ,update 等mysql关键字,临时表加上tmp 后缀,统计表加上statistic后缀,日志表加上log后缀等。</p>    <p>对象 规范 表 t <em>应用名</em> 模块名 <em>功能名 表的字段 英文单词或缩写,避开关键字 视图 v</em> 表名 存储过程 p <em>表名 函数 f</em> 功能说明 包 pkg <em>功能说明 触发器 tri</em> 表名 主键 primary 索引 idx <em>字段1</em> 字段2 唯一索引 uk <em>字段1</em> 字段2</p>    <p>表的命名应尽量反映存储的数据内容。</p>    <p><strong>2.4.2表字段的设计</strong></p>    <p>字段的命名以单词或者单词缩写为主,避开数据库关键字如all、type等。 Mysql字段类型 :</p>    <p>列类型  表达的范围  存储需求 1   TINYINT[(M)] [UNSIGNED] [ZEROFILL]  -128 到 127 或 0 到 255  1 个字节</p>    <p>2  SMALLINT[(M)] [UNSIGNED] [ZEROFILL]   -32768 到 32767 或 0 到 65535  2 个字节  3  INT[(M)] [UNSIGNED] [ZEROFILL]   -2147483648 到 2147483647 或 0 到 4294967295  4 个字节 4   BIGINT[(M)] [UNSIGNED] [ZEROFILL]   -9223372036854775808 到 9223372036854775807  或 0 到 18446744073709551615   8 个字节</p>    <p>5  DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]  整数最大位数( M )为 65 ,小数位数最大( D )为 30  变长  6  DATE   YYYY-MM-DD  3 个字节  7  DATETIME  YYYY-MM-DD HH:MM:SS(1001年到 9999 年的范围 )   8 个字节  8  TIMESTAMP  YYYY-MM-DD HH:MM:SS (1970年到2037年的范围)  4 个字节  9  CHAR(M)    0</p>    <p>2. 固定长度的字符串使用 CHAR 类型,所有单个字符的全部使用 CHAR 类型,而不应该使用VARCHAR 类型;</p>    <p>3. 仅仅当字符数量可能超过 20000 个的时候,可以使用 TEXT 类型来存放字符类数据。所有使用 TEXT 类型的字段必须和原表进行分拆,与原表主键单独组成另外一个表进行存放;</p>    <p>4. 需要精确到时间(年月日时分秒)的字段可以使用DATETIME 或TIMESTAMP,但请注意各自能表达的范围,以及是否需要用到TIMESTAMP的特性;精确到微秒建议时间类型转换为整形BIGINT存储(建议优先使用BIGINT类型存储日期);</p>    <p>5. 所有只需要精确到天的字段全部使用 DATE 类型,而不应该使用 TIMESTAMP或者DATETIME 类型;</p>    <p>6. 自增序列类型的字段只能使用 INT 或者 BIGINT,且明确标识出为无符号型(UNSIGNED),除非确实会出现负数,仅当该字段数字取值会超过42亿,才使用 BIGINT 类型;能有tinyint的就不要用smallint ,能用smallint的就不要用int,能用int的就不要用bigint</p>    <p>7. 字段字段使用not null:MySQL NULL类型和Oracle的NULL有差异,会进入索引中,如果是一个组合索引,那么这个NULL类型的字段会极大影响整个索引的效率。此外,NULL 在索引中的处理也是特殊的,也会占用额外的存放空间。</p>    <p>8. 有小数点的字段或者精度要求高的字段用decimal,禁用float.</p>    <p><strong>2.4.3索引的设计</strong></p>    <p>索引按照“idx_字段名”进行命名,索引名称使用小写。 索引中的字段数不超过5个。 唯一键由3个以下字段组成,并且字段都是整形时,使用唯一键作为主键。 没有唯一键或者唯一键不符合5中的条件时,使用自增(或者通过发号器获取)id作为主键。 唯一键不和主键重复。 索引字段的顺序需要考虑字段值去重之后的个数,个数多的放在前面。 ORDER BY,GROUP BY,DISTINCT的字段需要添加在索引的后面。</p>    <p>单张表的索引数量控制在5个以内,若单张表多个字段在查询需求上都要单独用到索引,需要经过DBA评估。查询性能问题无法解决的,应从产品设计上进行重构。 使用EXPLAIN判断SQL语句是否合理使用索引,尽量避免extra列出现:Using File Sort,Using Temporary。 UPDATE、DELETE语句需要根据WHERE条件添加索引。</p>    <p>对超过50个长度的字符串列,最好创建前缀索引而非整列索引(例如:ALTER TABLE t1 ADD INDEX(user(50))),可以有效提高索引利用率,不过它的缺点是对这个列排序时用不到前缀索引。前缀索引的长度可以基于对该字段的统计得出,一般略大于平均长度一点就可以了。 合理创建联合索引(避免冗余),(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c),注意不包括(b,c)、(b)、(c)。</p>    <p><strong>2.4.4表注释、字段注释</strong></p>    <p>Mysql中字段加注释比较麻烦,需要用alter table语句,所以尽量在新建表的时候就加上表注释和字段注释。类型字段的注释(注释中必须包括字段初始值的含义)。</p>    <p> </p>    <p>来自:http://tech.dianwoda.com/2016/11/08/mysqlshu-ju-ku-kai-fa-gui-fan/</p>    <p> </p>    
 本文由用户 MichelleCha 自行上传分享,仅供网友学习交流。所有权归原作者,若您的权利被侵害,请联系管理员。
 转载本站原创文章,请注明出处,并保留原始链接、图片水印。
 本站是一个以用户分享为主的开源技术平台,欢迎各类分享!
 本文地址:https://www.open-open.com/lib/view/open1478591404033.html
MySQL 数据库服务器