本文主要是介绍Mysql的设计规范和结构优化(-)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
一.数据库设计规范
1.1 数据库命名规范
1.所有数据库对象名称必须使用小写字母并用下划线进行分割。
2.所有数据库对象名称禁止使用mysql保留的关键字。如from ，name，真需要使用时，给加反向单引号。
3.数据库对象的命名要能做到见名知义，并且最好不要超过32个字符。数据库：Mc_Userdb，数据表：user_account
4.临时表要以tmp_开头，备份表要以bak_开头并以时间戳结尾。
1.2 数据库基本设计规范
1.没有特殊要求情况下，所有表必须使用innodb存储引擎。mysql5.5之前默认的存储引擎为myisam。Mysql5.5之后为innodb。Innodb能够很好的支持约束，事务、行级锁等特性，对数据的安全性和和一致性有很好的支持。
2.数据库和表的字符集统一使用utf-8。
3.所有表和字段都需要添加注释。
4.尽量控制单表数据量的大小，建议控制在500万以内。可以采用历史数据归档，分库分表等手段来控制数据。分区表：物理上存储在多个文件中，逻辑上是一个表。
5.尽量做到冷热数据的分离，减小表的宽度。减少磁盘io，保证热数据的内存缓存命中率。
6.禁止使用预留字段以及在表中存储大的二进制数据
问题：冷热数据的分离，减小表的宽度的作用？
答： 减小磁盘的io，保证热数据的内存缓存命中率。
把经常使用的列放到一张表中。
有效利用缓存，避免读入无用的冷数据。
1.3 数据库索引的设计规范
1.禁止给表中每一列都建立单独的索引,索引并不是越多越好。
2.避免冗余索引和重复索引
如冗余索引：primaryindex（id），index(id),unique index(id)
重复索引：index（a,b,c）,index(a),index(a,b)
Innodb的数据库是按照那个索引的顺序来组织表呢？
答案是：
| 主键。每个innodb的表必须有一个主键。 每个innodb表必须有一个主键，不使用更新频繁的列作为主键，不使用多列主键。 不使用uuid，md5，hash，字符串列作为主题。 主键建议选择使用自增id值。 |
- 在那些列上建立索引？不是唯一的方法
| 1.在where从句使用的字段包 2.含在order by ，group by ，distinct的字段上建立索引。 3.多表关联的关联列上 |
3.如何选择索引列的顺序
| 区分度最高的列放在联合索引的最左侧 尽量把字段长度小的列放在联合索引的最左侧 使用最频繁的列放到联合索引的最左侧。 |
1.4 数据字段设计规范
- 选择符合需要的最小的数据类型。定长用char，变长用varchar，大文本用text，blob数据类型。Varchar（N）：N 代表的是字符数，而不是字节数。使用UTF-8存储汉字varchar(255)时候，1个字符=3个字节，则varchar(255)=765个字节。多余的内存造成内存消耗。
- 避免使用text、blob数据类型。如果使用建议将blob或text列分离到一个单独的表。
- 避免使用enum（枚举）数据类型。修改enum数据类型需要使用alter语句。
- 尽可能把所有列定义为not null。索引null列需要额外的空间来保存，所以要占用更多的空间。进行比较和计算时要对null值做特别的处理。
- 使用datetime或者timestamp类型存储时间。使用字符串存储日期，无法进行函数计算，占用更多的空间，yyyy-MM-dd HH：mm:ss 格式的日期，字符串需要16个字节，日期类型需要8个字节。
- 与金额相关的数据，必须使用decimal类型。decimal为精准浮点数，精度不会丢失
- 对于非负数据采用无符号整型进行存储。（因为存储的将是42个亿，比有符号，多一倍）
1.5 sql设计规范
1.建议使用预编译语句进行数据库的操作。只传参数，比传递sql语句更高效。
2.避免数据类型的隐式转换。Select name，phone from student where id=’111’ （如id为int，而赋值给字符串，这样会隐式转换int型），否则索引失效。
3.使用left join 或not exists 来优化not in操作。
4.避免使用select * from xxx。
5.避免使用子查询，可以把子查询优化为join操作。
因为子查询的结果会缓存成一个个临时表，临时表中无法使用索引，数据量过大时，消耗过多的cpu和io资源。
6.避免使用join关联太多的表，建议3-5个，mysql最多支持61个表。
7.使用in代替or。In的值不要超过500个，in操作相对于or操作可以有效的利用索引。
8.禁止使用order by rand()进行随机排序。
9.where从句中禁止对列进行函数转换和计算（因为导致无法使用索引）。如where date(createtime)=’20160901’（datetime无法使用索引） 要改为where createtime>=’2016-09-01’ and createtime<’2016-09-02’ 这样写的话可以使用到索引。
10.un.ion all 合并的结果；union 对合并的结果具有去重的功能。
11.拆分复杂的大sql为多个小sql。Sql拆分后可以通过并行执行来提高处理效率。
1.6 操作行为规范
1.超100万行的批量写操作，要分批多次进行操作。如大批量可能会造成严重的主从延迟。
Binlog日志为row格式时会产生大量的日志。避免产生大事务。
2.对于大表使用pt-online-schema-change修改表结构。避免锁表、主从延迟等问题。
3.对于程序连接数据库账号，遵循权限最小的原则，账号只能在一个DB下使用，不准跨越库。程序使用的账号不准有drop权限。
4.禁止为程序使用的账号赋予super权限，当大道最大连接数限制时，还运行1个有super权限的用户连接。
Super权限只能留给DBA处理问题的账号使用
这篇关于Mysql的设计规范和结构优化(-)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
