mysql 建立索引在on 从句中_理清数据库设计

本文参考于如何进行数据库设计，侵删

1. 需求分析

数据库需求的作用点：

数据是什么

数据有哪些属性

数据和属性各自的特点有哪些

搞清楚一些问题

实体与实体之间的关系(如1对1，1对多，多对多)

实体所包含的属性有哪些

哪些属性或属性的组合可以唯一标识一个实体

2. 逻辑设计

使用ER图进行数据库逻辑建模

逻辑设计是做什么的

将需求转为数据库的逻辑模型

通过ER图的形式对逻辑模型进行展示

同所选用的具体的DBMS系统无关

3. 物理设计

根据数据库自身的特点把逻辑设计转换为物理设计(涉及到选用哪种数据库)

数据库设计需要做什么

选择合适的数据库管理系统

定义数据库、表及字段的命名规范

根据所选的dbms系统选择合适的字段类型

反范式化设计(如，增加一些冗余，来达到效率的提升)

选用数据库管理系统

使用什么数据库管理系统，要考虑用途、开发语言、安全性，，，，等等因素

MySQL的存储引擎

表及字段的命名规则

可读性原则：

使用大写和小写来格式化的库对象的名字以获得良好的可读性。(如，使用CustAddress而不是custaddress来提高可读性；这里要注意有的DBMS是对大小写敏感的)

表意性原则

对象的名字应该能够描述它所标识的对象。例如，对于表，表的名字应该能够体现表中存储的数据内容；对于存储过程，存储过程应该能够体现存储过程的功能。

长名原则

尽可能少使用或不适用缩写，这样会降低可读性及它实际表达的功能

字段类型选择原则

列的数据类型一方面影响数据存储空间的开销，另一方面影响数据查询性能。当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的类型，应该优先选择占用空间小的数据类型。

以上选择原则又从两个方面进行考虑：

在对数据进行比较(查询条件、join条件及排序)操作时：

在数据库中，数据处理以页为单位，列的长度越小，越利用提升性能(Mysql的InnoDB存储页的大小为16k)；一个列的长度越小，在一个页中存储的数据量越大，这样在加载相同数据量时，加载的页就会越少，io的性能就会提升。

如，一个birthday字段有以下数据类型可选择：

MySql中数据库类型所占空间介绍：

数据如何具体选择数据类型

Char和varchar如何选择：

如果类中要存储的数据长度差不多是一致的，则应该考虑char，否则考虑varchar

如果列中的最大长度小于50Byte，一般也考虑用char。如果这个列很少使用，则基于节省空间和io的考虑，还是可以选择varchar

一般不宜定义大于50Byte的char类型

Decimal和float类型如何选择：

Decimal用于存储精确数据，而float只能用于存储非精确数据

由于float的存储空间开销一般比decimal小(精确到7位小数只需要4个字节，而精确到15位小数只需要8字节)，所以非精确数据优先选择flaot

时间类型如何选择

使用int类型来存储时间字段的优缺点：

优点：字段长度比datetime小

缺点：使用不方便，要进行函数转换

限制：

需要存储的时间粒度

年 月 日 小时 分 秒 周

如何选择主键

避免使用外键约束

避免使用触发器

关于预留字段

无法准确的知道预留字段的类型

无法准确的知道预留字段中所存储的内容

维护预留字段所要的成本，同增加一个字段所需要的成本是相同的

严禁使用预留字段

反范式化

反范式化是针对范式化而言的，在前面介绍了数据库设计的第三范式，所谓的反范式化就是为了性能和读取效率的考虑而适当的对第三范式的要求进行违反，而允许少量的数据冗余，话句话说反范式化就是使用空间来换取时间(可以减少查询时表的连接)。

一个表的设计往往读操作和写操作的成本比例如果是3:1那还是比较划算的。

反范式化的优点：

减少标的关联数量

增加数据的读取效率

反范式化一定要适度

4. 维护优化

新的需求进行建表

索引优化

大表拆分

维护和优化需要做什么

维护数据字典

维护索引

维护表结构

在适当的时候对表进行水平拆分或垂直拆分

如何维护数据库字典

使用第三方数据库工具对数据字典进行维护

利用数据库本身的备注字段来维护数据字典

如何维护索引(只是简单介绍)

出现在where从句，group by从句，order by从句中的列来进行建立索引

在上面一条出现的从句中可选择性高的列要放到索引前面

索引中不要包括太长的数据类型

索引注意事项：

如何维护表结构

使用在线变更表结构的工具。Mysql5.5之间可以使用pt-online-schema-change(此工具的原理是建立一张临时表然后把原表的数据拷贝进去，之后对此临时表进行从命名)Mysql5.6之后本身支持在线表结构的变更

同时对数据字典进行维护

控制表的宽度和大小

数据库中适合的查询

批量操作VS逐条操作

禁止使用Select * 这样的查询

控制使用用户自定义函数

不要使用数据库中的全文索引(因为全文索引是需要另外建立索引文件来对全文索引进行管理，另外全文索引对中文支持不是很好)

表的垂直拆分

当需求增加，表的列越来越多

表的水平拆分

如何进行表的水平拆分

对每条数据的主键进行hash操作；比如说对主键进行按模取值，将一张大表的数据平均的分到了多张小表中