MySQL数据库与Oracle、SQLServer等数据库相比，有其核心的优缺点。我们在使用MySQL数据库的时候，需要遵循一定的规范，扬长避短。无意中从github上看到一个大佬的MySQL数据库设计规范，顺便分享到这里。https://github.com/jly8866/archer/blob/master/src/docs/mysql_db_design_guide.md非常实用，推荐收藏阅读。1.数据库设计以下所有规范将按照【高风险】、【强制】、【推荐】三个级别进行标注，符合的优先级由高到低。对于不满足【高风险】和【强制】两个级别的设计，DBA会强行回调修改。1.库名【强制】库名必须控制在32个字符以内，相关模块的表名尽量有连接关系，如user表和user_login表。【强制】库的命名格式为：业务系统名_子系统名，同一模块使用的表名尽量使用统一的前缀。【必填】一般分库命名格式为数据库通配符name_number，编号从0开始递增。例如wenda_001基于时间的分库命名格式为“库通配符name_time”【必填】必须显示创建数据库时格式指定了字符集，字符集只能是utf8或者utf8mb4。创建数据库SQL示例：创建数据库db1默认字符集utf8;。2.表结构【强制】表名和列名必须控制在32个字符以内。表名只能使用字母、数字和下划线，全部小写。【强制】表名必须与模块名强相关。例如教师系统使用“sz”作为前缀，频道系统使用“qd”作为前缀等。【强制】创建表时必须明确指定字符集为utf8或utf8mb4。【强制】创建表时，必须明确指定表的存储引擎类型。如果没有特殊要求，必须是InnoDB。当需要使用InnoDB/MyISAM/Memory以外的存储引擎时，必须经过DBA的认可，才能用于生产环境。由于Innodb表支持事务、行锁、宕机恢复、MVCC等关系型数据库的重要特性，是业界使用最广泛的MySQL存储引擎。而这是其他大多数存储引擎所没有的，所以最推荐使用InnoDB。【强制】建表时一定要有注释【建议】关于建表时的主键：（1）强制要求主键为id，类型为int或bigint，且为auto_increment（2)表中标识每一行主题的字段不宜设置为主键，建议设置user_id、order_id等其他字段，并创建唯一键索引（参考cdb.teacher表设计）。因为如果设置为主键，随机插入主键值，会造成innodb内部分页和大量随机I/O，导致性能下降。[建议]核心表（比如user表，money相关表）一定要有行数据的创建时间字段create_time和最后更新时间字段update_time，这样方便排查问题。【建议】表中所有字段必须是NOTNULL属性，业务可以根据需要定义DEFAULT值。因为使用NULL值会导致每行占用额外的存储空间、数据迁移容易出错、聚合函数计算结果出现偏差等问题。[建议]建议将表中的blob、text等大字段垂直拆分到其他表中，只在需要读取这些对象时才选择。[建议]反范式设计：对其他表中经常需要join的字段进行冗余复制。例如user_account、user_login_log等表中的user_name属性是冗余的，以减少join查询。【强制】中间表用于保存中间结果集，名称必须以tmp_开头。备份表用于备份或抓取源表的快照，名称必须以bak_开头。定期清理中间表和备份表。【强制】超过100W行的大表altertable必须经过DBA审核，在非高峰时间执行。因为altertable会产生一个表锁，期间所有对该表的写操作都会被阻塞，可能会对业务造成很大的影响。3.列数据类型优化[建议]表中自增列（auto_increment属性）建议使用bigint类型。因为unsignedint的存储范围是-2147483648~2147483647（约21亿），溢出会报错。[建议]对于业务中选项较少的status、type等字段，建议使用tinytint或smallint，以节省存储空间。[建议]业务中IP地址字段建议使用int类型，不建议使用char(15)。因为int只占4个字节，可以用下面的函数互相转换，char(15)至少占15个字节。一旦表数据行数达到1亿，需要额外增加1.1G的存储空间。SQL:选择inet_aton('192.168.2.12');选择inet_ntoa(3232236044);PHP:ip2long('192.168.2.12');long2ip（3530427185）；[推荐]enum和set不推荐。因为它们浪费空间，而且枚举值是硬编码的，不方便更改。建议使用Tinyint或smallint。[建议]不建议使用blob、text等类型。它们是对硬盘和内存空间的浪费。加载表数据时，将大字段读入内存，浪费内存空间，影响系统性能。是否真的需要这么大的字段，建议跟PM和RD沟通。当Innodb中一条记录超过8098字节时，会选择记录中最长的字段并将其768字节放在原始页中，其余字段放在溢出页中。不幸的是，在紧凑的行格式中，原始页面和溢出页面都将被加载。[建议]存钱的字段，建议使用int，终端乘以100除以100访问。因为int占4个字节，double占8个字节，浪费了空间。[建议]尽可能使用varchar存储文本数据。因为varchar是变长存储，所以比char更节省空间。MySQL服务器层规定一行中的所有文本最多可以存储65535个字节，所以utf8字符集最多存储的字符数为21844个字符，如果超过，会自动转为mediumtext字段。但是在utf8字符集中text最多可以存储21844个字符，mediumtext最多可以存储2^24/3个字符，longtext最多可以存储2^32个字符。一般建议使用varchar类型，字符数不要超过2700。[建议]时间类型尽量选择timestamp。因为datetime占8个字节，timestamp只占4个字节，但范围是从1970-01-0100:00:01到2038-01-0100:00:00。更高级的方法，选择int存储时间，使用SQL函数unix_timestamp()和from_unixtime()转换。详细存储大小见下图：4.索引设计【强制】InnoDB表必须有一个id为int/bigintauto_increment的主键，并且主键值不能更新。【建议】主键名称以“pk_”开头，唯一键以“uk_”或“uq_”开头，公共索引以“idx_”开头，全部小写，表名/字段名或缩写作为后缀。【强制】对于InnoDB和MyISAM存储引擎表，索引类型必须为BTREE；对于MEMORY表，可以根据需要选择HASH或BTREE类型的索引。【强制】单个索引中每条索引记录的长度不能超过64KB。[建议]单表索引数不能超过7个。[建议]建索引时，考虑建联合索引，将区分度最高的字段放在最前面。例如可以通过selectcount(distinctuserid)计算列userid的区分度。[建议]在多表join的SQL中，保证被驱动表的join列上有索引，这样join执行效率最高。[建议]创建表或添加索引时，确保表中没有冗余索引。对于MySQL，如果表中已经存在key(a,b)，则key(a)是冗余索引，需要删除。5.分库分表分区表【强制】分区表的分区字段（partition-key）必须有索引，或者为组合索引的第一列。【强制】单个分区表的分区数（含子分区）不能超过1024。【强制】RD或DBA必须在上线前指定分区表的创建和清理策略。【强制】访问分区表的SQL必须包含分区键。【建议】单个分区文件不要超过2G，总大小不要超过50G。建议分区总数不超过20个。【强制】分区表的Altertable操作必须在非高峰时段进行。【强制】如果采用分库策略，数据库数量不能超过1024【强制】如果采用分表策略，表数量不能超过4096【建议】单个分表不要超过500W行数，ibd文件大小不超过2G。使数据分布更好。[建议]横向分表尽量使用取模的方法。日志和报表数据建议使用日期。6、字符集【强制】数据库本身、表、列的所有字符集必须一致，utf8或utf8mb4。【强制】前端程序的字符集或环境变量中的字符集必须与数据库和表的字符集一致，统一为utf8。二、SQL编写规范1、DML语句【强制】SELECT语句必须指定具体的字段名，禁止写*.因为select*也会从mysql中读取不该读取的数据，造成网卡压力。而一旦表字段更新了，但是model层还没有来得及更新，系统就会报错。【强制】insert语句指定具体的字段名，不要写成insertintot1values(…)，原因同上。[建议]insertinto...values(XX),(XX),(XX)....这里XX的值不要超过5000，虽然太多的值上线很快，但是会造成高手-从同步延迟。[建议]不要在SELECT语句中使用UNION。推荐使用UNIONALL，UNION子句的个数限制在5个以内。因为unionall不需要去重，节省了数据库资源，提高了性能。[建议]in值列表限制在500以内。比如select...whereuseridin(...within500...)，这样做是为了减少底层扫描，减轻数据库压力，加快查询速度。[建议]需要控制事务中批量更新数据的数量，进行必要的休眠，实现少量多次。【强制】事务涉及的表必须都是InnoDB表。否则，所有的失败都不会回滚，容易造成主从库的同步终端。【强制】写入和事务发送到主库，只读SQL发送到从库。【强制】除静态表或小表（100行以内）外，DML语句必须有where条件并使用索引查找。【强制】生产环境禁止使用hints，如sql_no_cache、forceindex、ignorekey、straightjoin等。因为hint是用来强制sql按照一定的执行计划执行的，但是随着数据变化，我们不能保证我们原来的预测是正确的，所以我们要相信MySQL优化器！【强制】where条件中的等号字段类型必须一致，否则无法使用索引。[建议]SELECT|UPDATE|DELETE|REPLACE必须有WHERE子句，WHERE子句的条件必须使用索引查找。【强制】生产数据库强烈不建议对大表进行全表扫描，但对于100行以下的静态表可以进行全表扫描。查询数据量不要超过表行数的25%，否则索引将无法使用。【强制】禁止在WHERE子句中只使用完全模糊的LIKE条件进行查找，必须有其他等价或范围查询条件，否则无法使用索引。[建议]不要在索引列中使用函数或表达式，否则无法使用索引。比如wherelength(name)='Admin'或者whereuser_id+2=10023。[建议]减少or语句的使用，将or语句优化为union，然后在每个where条件上建索引。例如，wherea=1orb=2被优化为wherea=1...union...whereb=2,key(a),key(b)。[建议]分页查询，当限制起点高时，可以先使用过滤条件进行过滤。比如selecta,b,cfromt1limit10000,20；优化为：selecta,b,cfromt1whereid>10000limit20;。2.多表连接【强制】禁止跨dbjoin语句。因为这样可以降低模块之间的耦合度，为数据库拆分打下坚实的基础。【强制】禁止在业务的updateSQL语句中使用join，如updatet1joint2....【建议】不建议使用子查询。建议将子查询SQL拆解，组合多个查询的程序，或者使用join代替子查询。[建议]在线环境下，多表join不要超过3张表。[建议]多表连接查询建议使用别名，别名应该用来引用SELECT列表中的字段，database.table格式，比如selectafromdb1.table1alias1where....[建议】多表join时，尽量选择结果集较小的表作为驱动表join其他表。3、事务【建议】在事务中，INSERT|UPDATE|DELETE|REPLACE语句操作的行数控制在2000以内，WHERE子句中IN列表传入的参数个数控制在500以内。[建议]批量操作数据时，需要控制事务处理间隔，进行必要的休眠。一般推荐值为5-10秒。【建议】对auto_increment属性字段的表进行insert操作，并发数需要控制在200以内。【强制】程序设计必须考虑“数据库事务隔离级别”的影响，包括脏读、不可重复读、幻读读。网上建议的事务隔离级别是repeatable-read。[建议]交易包含的SQL不超过5条（支付业务除外）。因为太长的事务会导致长期锁数据、MySQL内部缓存、连接消耗过大等雪崩问题。【建议】事务中的update语句尽量基于主键或者唯一键，比如update...whereid=XX;否则会产生间隙锁，锁范围在内部扩大，导致系统性能下降和死锁。[建议]尽量将一些典型的外部调用移出事务，比如调用webservice、访问文件存储等，避免事务过长。[建议]对MySQL主从延迟严格敏感的select语句，请开启事务强制访问主库。4、排序和分组【建议】减少orderby的使用，不用排序能和业务沟通的就不要排序，或者把排序放在终端上去做。orderby、groupby、distinct等语句消耗CPU较多，数据库的CPU资源极其宝贵。[建议]orderby、groupby、distinct等SQL语句尽量使用索引直接检索排序后的数据。例如，其中a=1orderby可以使用key(a,b)。[建议]对于包含orderby、groupby、distinct的语句，请将where条件过滤的结果集保持在1000行以内，否则SQL会很慢。5.禁止在线使用的SQL语句【高危】Disableupdate|deletet1…wherea=XXlimitXX;这种有限制的更新语句。因为会导致主从不一致，造成数据混乱。建议按PK加单。【高危】禁止使用关联子查询，如updatet1set...wherenamein(selectnamefromuserwhere...);效率极低。【强制】禁用过程、函数、触发器、视图、事件和外键约束。因为它们消耗数据库资源，降低了数据库实例的可扩展性。建议在终端中实施。【强制】禁用insertinto...onduplicatekeyupdate...在高并发环境下，会造成主从不一致。【强制】禁止join表更新语句，如updatet1,t2wheret1.id=t2.id…。
