MySQL学习笔记(一)：数据库三范式、反范式设计、分库分表、存储引擎

数据库的三范式

什么是数据库三范式

第一范式：每个列都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

数据库的三范式是我们设计数据库时要遵守的原则，一般情况下不允许打破。遵循三范式，你可以设计出很精炼的数据库表结构。然而现有的项目应用并不会完全遵循范式的理念，原因比如：
性能原因，没有任何冗余的表设计会产生更多的查询行为，这意味着会产生更多次的数据库IO操作。在一些实时交互的系统中，可能会慢得让人难以忍受。
当然，你可以使用数据库的 连接(join) 操作，而事实上数据库提供 join 也就是为了来缓解这种问题。但一旦用到了分库分表方案，这个问题就会非常的棘手。
成本结构的变化，数据库范式是在20世纪提出的，当时的磁盘存储成本还很高。随着科技发展，数据存储的成本已经大幅度缩减，对于采用范式设计(规避冗余)带来的成本缩减收益已经不那么明显。

反范式设计

既然范式是为了消除冗余，那么反范式就是通过增加冗余、聚合的手段来提升性能。比如，为了提升查询的性能，在CMS的文章表中同时冗余作者的信息。
冗余的做法会牺牲一定的数据一致性，通常也是需要业务上进行权衡取舍。
当然，除了冗余(存储多份拷贝) 之外，还有另外的理念，即数据的聚合，或者叫嵌套。这种做法相当于是将多个字段(列)合并存储到数据库表的一个列中。
反范式的做法在互联网项目、开源产品中也比较常见，比如Discuz 的数据表设计中就存在许多的冗余列、聚合字段。
一方面，除了能获得性能的提升之外，数据压缩、高度灵活扩展(非结构化) 也是反范式设计能获得青睐的理由。
当然，这里并非一律反对数据库范式，理解范式仍然是做好数据库设计的一门基础，比如选择合适的主键、清晰的划分每一列属性等等。
另外，分库分表方案就是一种典型的反范式数据库设计。

分库分表

既然，刚刚提到了分库分表，就先简单介绍一下：
随着公司业务快速发展，数据库中的数据量猛增，访问性能也变慢了，优化迫在眉睫。分析一下问题出现在哪儿呢？ 关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。

方案1：
通过提升服务器硬件能力来提高数据处理能力，比如增加存储容量 、CPU等，这种方案成本很高，并且如果瓶颈在MySQL本身那么提高硬件也是有很的。

方案2：
把数据分散在不同的数据库中，使得单一数据库的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的，如下图：将电商数据库拆分为若干独立的数据库，并且对于大表也拆分为若干小表，通过这种数据库拆分的方法来解决数据库的性能问题。

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而使数据均衡地分布到多张表中，可以缩短数据查询所需要的时间，提高数据库的吞吐，减轻数据库压力。

一、垂直切分
将表按照功能模块、关系密切程度划分出来，部署到不同的库上。常见的分成6大库：
1）用户类库：用于保存了用户的相关信息。例如：db_user，db_system，db_company等。
2）业务类库：用于保存主要业务的信息。比如主要业务是订单，用这个库保存订单业务。例如：db_order等。
3）内存类库：主要用Mysql的内存引擎。前台的数据从内存库中查找，速度快。例如：heap。
4）图片类库：主要保存图片的索引以及关联。例如：db_img_index，db_img_res。
5）日志类库：记录点击，刷新，登录等日志信息。例如：db_log_click，db_log_fresh，db_log_login。
6）统计类库：对业务的统计，比如点击量，刷新量等等。例如db_stat。
1、垂直分表
垂直分表，通俗的说法叫做“大表拆小表”，基于关系型数据库中的“列”（字段）进行拆分。
通常情况，某个表中的字段比较多，可以新建立一张“扩展表”，将不经常使用或者长度较大的字段拆分出去放到“扩展表”中，每个表里面的数据记录数一般情况下是相同的，只是字段不一样。

2、垂直分库
垂直分库就是依据业务功能的不同，划分为不同的业务库。
比如：一个数据库里面既存在用户数据，又存在订单数据，还有产品数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库、把产品数据放到产品库。

二、水平拆分
当一个表中的数据量过大时，可以把该表的数据按照某种规则，例如userID散列，进行划分，然后存储到多个结构相同的表和不同的库上。
1、水平分表
水平分表也称为横向分表，就是将表中不同的数据行按照一定规律分布到不同的数据库表中（仍在同一个数据库中），这样来降低单表数据量，优化查询性能。
比如：有一张用户表user，由于记录条数太多，将其拆分成256张表，拆分的记录根据user_id%256取得对应的表进行存储，比如user_id为10001，10001%256=17，则user_id为10001的数据存在表user17中。

2、水平分库
与分表策略相似，分库也可以采用通过一个关键字段取模的方式，来对数据访问进行路由。

三、垂直水平拆分
垂直水平拆分，是综合了垂直和水平拆分方式的一种混合方式，垂直拆分把不同类型的数据存储到不同库中，再结合水平拆分，使单表数据量保持在合理范围内，扩展系统的并发处理能力，提升单表的查询性能。

一种分库分表的路由策略如下（假设采用user_id作为路由字段）：
中间变量=user_id% (库数量x每个库的表数量）
库=取整（中间变量/每个库的表数量）
表=中间变量％每个库的表数量
假设将原来的单库单表order拆分成256个库，每个库包含1024个表，那么按照前面所提到的路由策略，对于user_id=262145的访问，路由的计算过程如下：
中间变量=262145% (256X1024) =1;
库=取整（1/1024) =0;
表=1%1024=1。
这意味着，对于user_id=262145的订单记录的查询和修改，将被路由到第0个库的第1个表中执行。

一般来说，在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表方案。

存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。

MyIASM引擎(原本Mysql的默认引擎)：
MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，不提供事务的支持，也不支持行级锁和外键。

InnoDB存储引擎
InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。

MyIASM和InnoDB存储引擎的区别：

存储引擎选择

如果没有特别的需求，使用默认的Innodb即可。
MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。
Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。
InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。