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>