每日学习笔记(14)_MySQL

1）今天在读《MySQL技术内幕InnoDB存储引擎》，作者在第四章讲到MySQL的分区时提到了一个问题，“对数据表分区真的会提高数据库的查询性能吗？”。

对于OLAP的应用，分区的确可以很好地提高查询的性能，因为OLAP应用中大多数查询需要频繁地扫描一张很大的表。假设表中数据有1亿行，其中有一个时间戳属性列。你的查询需要从这张表中获取一年的数据。如果按数据戳进行分区，则只需要扫描相应的分区就可以了。

对于OLTP 的应用，分区则应该非常小心。在这种应用下，不可能会获取一张大表中10%的数据，大部分都是通过索引返回几条记录就可以了。而根据B+树索引的原理可知，对于一张大表，一般的B+树需要2~3次得磁盘IO。因此B+树可以很好地完成操作，不需要分区的帮助，而且设计不好的分区反而会带来严重的性能问题。

假设一张表有1000万行数据，如果对主键做10个HASH的分区，那么每个分区就只有100万行数据。看上去对主键进行查询的速度是应该会变快了，例如Select * from table where PK=@pk。但是你有没有考虑过这样一个问题：100万行和1000万行的数据本身构成的B+树层次都是一样的，可能都是2层？那么上述走主键分区的索引并不会带来性能的提高。好吧，即时1000万行的B+树高度是3，100万行的B +树高度是2，那么上述方法可以避免1次IO，从而提高查询的效率。恩，这没有问题，但是这张表只有主键索引，而没有其他的列需要查询吗？如果有如下的语句：select * from table where key=@key，此时对于key的查询就需要扫描所有的10个分区，即使每个分区的查询开销为2次IO,那么也需要20次IO。而对于原来的单表设计，对于Key的查询还是2~3次IO.

因此，对于使用InnoDB存储引擎作为OLTP应用的表，在使用分区时应该十分小心，设计时要确认数据的访问模式，否则分区不仅不会带来查询速度的提高，反而可能会使你的应用执行速度变慢。

 2）对于其他的一些数据库，比如SQL Server，它的表类型有一种不是索引组织表，称为堆表。这种表的特性决定了其上的索引都是非聚集的，但是堆表没有主键，所以这时书签是一个行标识符，可以用如“文件号：页号：槽号”的格式来定位实际的行。也许看上去，堆表的非聚集索引不需要再通过主键对聚集索引进行查找，那不是速度会更快吗？也许吧，在某些只读的情况下，书签为行标识符方式的非聚集索引可能会比书签为主键方式的非聚集索引快。但是考虑在OLTP应用的情况下，表可能还需要发生插入，更新，删除等DML操作。当进行这类操作时，书签为行标识符方式的非聚集索引可能需要不断更新行标识符所指向数据页的位置，此时开销可能就会大于书签为主键方式的非聚集索引了。

那为什么在 SQL Server上还要使用索引来组织表呢？堆表的书签性使得非聚集查找可疑比主键书签方式更快，并且非聚集可能在一张表中存在多个，我们需要对多个非聚集索引的查找。而且对于非聚集索引的离散读取，索引组织表上的非聚集索引会比堆表上的聚集索引慢一些。

的确，某些情况下，使用堆表的确会比索引组织表更快，但这大部分都是由于存在于OLAP的应用。其次就是前面提到的表中数据是否需要更新，并且更新是否会影响到物理地址的变更。此外另一个不能忽视的是对于排序和范围查找，索引组织表可以通过B+树的中间节点就找到要查找的所有页，然后进行读取，而堆表的特性决定了这是不可能实现的。最后，非聚集索引的离散读，的确是存在上述情况，但是一般可以通过预读技术来避免多次的离散读操作。

因此，具体是建立堆表还是索引组织表，这取决于你的应用，不存在哪个更优的情况。

作者：洞庭散人

出处：http://phinecos.cnblogs.com/　　　　

本博客遵从Creative Commons Attribution 3.0 License，若用于非商业目的，您可以自由转载，但请保留原作者信息和文章链接URL。