Mysql的设计规范和结构优化(-)

本文主要是介绍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’ 这样写的话可以使用到索引。