关系型数据库中的关键技术

存储引擎

MySQL 的存储引擎可能是所有关系型数据库产品中最具有特色的了，不仅可以同时使用多种存储引擎，而且每种存储引擎和MySQL之间使用插件方式这种非常松的耦合关系。

由于各存储引擎功能特性差异较大，这篇文章主要是介绍如何来选择合适的存储引擎来应对不同的业务场景。

MyISAM

1.特性

不支持事务：MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能使用

表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能

读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读

只会缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据

2.适用场景

不需要事务支持(不支持)

并发相对较低(锁定机制问题)

数据修改相对较少(阻塞问题)

以读为主

数据一致性要求不是非常高

3.最佳实践

尽量索引(缓存机制)

调整读写优先级，根据实际需求确保重要操作更优先

启用延迟插入改善大批量写入性能

尽量顺序操作让insert数据都写入到尾部，减少阻塞

分解大的操作，降低单个操作的阻塞时间

降低并发数，某些高并发场景通过应用来进行排队机制

对于相对静态的数据，充分利用Query Cache可以极大的提高访问效率

MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问

InnoDB

1.特性

具有较好的事务支持：支持4个事务隔离级别，支持多版本读

行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响

读写阻塞与事务隔离级别相关

具有非常高效的缓存特性：能缓存索引，也能缓存数据

整个表和主键以Cluster方式存储，组成一颗平衡树

所有Secondary Index都会保存主键信息

2.适用场景

需要事务支持(具有较好的事务特性)

行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成

数据更新较为频繁的场景

数据一致性要求较高

硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO

3.最佳实践

主键尽可能小，避免给Secondary index带来过大的空间负担

避免全表扫描，因为会使用表锁

尽可能缓存所有的索引和数据，提高响应速度

在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交

合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性

避免主键更新，因为这会带来大量的数据移动

NDBCluster

1.特性

分布式：分布式存储引擎，可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分

支持事务：和Innodb一样，支持事务

可与mysqld不在一台主机：可以和mysqld分开存在于独立的主机上，然后通过网络和mysqld通信交互

内存需求量巨大：新版本索引以及被索引的数据必须存放在内存中，老版本所有数据和索引必须存在与内存中

2.适用场景

具有非常高的并发需求

对单个请求的响应并不是非常的critical

查询简单，过滤条件较为固定，每次请求数据量较少，又不希望自己进行水平Sharding

3.最佳实践

尽可能让查询简单，避免数据的跨节点传输

尽可能满足SQL节点的计算性能，大一点的集群SQL节点会明显多余Data节点

在各节点之间尽可能使用万兆网络环境互联，以减少数据在网络层传输过程中的延时

注：以上三个存储引擎是目前相对主流的存储引擎，还有其他类似如：Memory，Merge，CSV，Archive等存储引擎的使用场景都相对较少，这里就不一一分析了，如果有朋友感兴趣，后面再补充吧。

如今Bigtable型(列族)数据库应用越来越广，功能也很强大。但是很多人还是把它当做关系型数据库在使用，用原来关系型数据库的思维建表、存储、查询。本文以hbase举例讲述数据模式的变化。

传统关系型数据库(mysql，oracle)数据存储方式主要如下：

图一

上图是个很典型的数据储存方式，我把每条记录分成3部分: 主键、记录属性、索引字段。我们会对索引字段建立索引，达到 二级索引的效果。

但是随着业务的发展，查询条件越来越复杂，需要更多的索引字段，且很多值都不存在，如下图：

图二

上图是6个索引字段，实际情况可能是上百个甚至更多，并且还需要根据多个索引字段刷选。查询性能越来越低，甚至无法满足查询要求。关系型数据里的局限也开始显现，于是很多人开始接触NoSQL。

列族数据库很强大，很多人就想把数据从mysql迁到hbase，存储的方式还是跟图一或者图二一样，主键为rowkey。其他各个字段的数据，存储一个列族下的不同列。但是想对索引字段查询就没有办法，目前还没有比较好的基于bigtable的二级索引方案，所以无法对索引字段做查询。

这时候其实可以转换下思维，可以把数据倒过来，如下图：

图三

把各个索引字段的值作为rowkey，然后把记录的主键和属性值按照一定顺序存在对应rowkey的value里。上图只有一个列族，是最简单的方式。 Value里的记录可以设置成定长的byte[]，多个记录集合通过移位快速查询到。

但是上面只适合单个索引字段的查询。如果要同时对多个索引字段查询，图三的方式需要求取出所有value值，比如查询“浙江”and“手机”，需要取出两个value，再解析出各自的主键求交。如果每条记录的属性有上百个，对性能影响很大。

接下来的变化是解决多索引字段查询的问题。我们将 主键字段和属性字段分开存储，储存在不同的列族下，多索引查询只需要取出列族1下的数据，再去最小集合的列族2里取得想要的值。储存如图四：

图四

为什么是不同列族，而不是一个列族下的两个列？

列族数据库数据文件是按照列族分的。在取数据时，都会把一个列族的所有列数据都取出来，事实上我们并不需要把记录明细取出来，所以把这部分数据放到了另一个列族下。

接下来是对列族2扩展，列族2储存更多的列，用来做各种刷选、计算处理。如下图：

图五

后来我感觉这玩样越来越像搜索了。。。

索引设计

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。本小节将向读者介绍一些索引的设计原则。

1.选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2.为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3.为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4.限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5.尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

6.尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7.删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。

SQL 优化

（一）深入浅出理解索引结构

实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：

其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。

我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页&#x