空间换时间，超大数据表的查询效率优化。

不念不忘少年蓝@ 2022-05-13 13:42 155阅读 0赞

原文出处：[http://www.cnblogs.com/wesley/archive/2012/04/23/2466982.html][http_www.cnblogs.com_wesley_archive_2012_04_23_2466982.html]

在开发论坛程序的时候，我借鉴了目前一些论坛的数据规模，10年的积累大概在2000万~5000万左右，因此决定，最低承载力设计要求至少是 9 位数。于是在开发完第一阶段的功能以后，开始折磨硬盘进行性能测试。  
在论坛程序中，体积最大、同时也是效率要求最高的两张表，便是话题 Topics 和帖子 Posts，因此本文将针对这2个实例，介绍分享我的实际经验。  
  
本文没有给出任何代码示范， 内容可能比较理论化抽象化， 但希望能够通俗易懂。

首先从查询需求入手，分析我们的目标。  
  
对话题表 Topics，最频繁的查询需求有以下几种筛选、排序，或者它们的搭配：

1.  通过 Id 查询单个话题
2.  按论坛版面 ForumId 筛选
3.  按照发贴者筛选
4.  按照特定过滤条件筛选，如置顶、精华、逻辑删除等
5.  按照最后回复倒序
6.  按照话题创建时间倒序

对于帖子表 Posts，需求可能略微简单一些：

1.  通过 Id 查询单个帖子
2.  按照话题 TopicId 筛选
3.  按照发帖人筛选
4.  按照特定过滤条件筛选，如垃圾信息、逻辑删除等
5.  按照发贴时间排序

主键  
  
确定数据行的唯一性，我们毋庸置疑的在这两张表各自的 Id 列上建立了主键。  
  
索引  
  
优化查询效率，首先想到的是索引，索引又分为聚簇索引和非聚簇索引，这个大家都懂的。(关于这些的基本知识介绍已经非常多见，这里只做简单介绍，了解相关基本知识有劳另找文章阅读。)  
聚簇索引即表数据行的物理排序，可以获得最佳的筛选和排序效率，SQL Server 设计器在设置主键时会默认将主键设置为聚簇索引，实际上对不少情况而言，这是浪费了宝贵的资源。非聚簇索引可以建多个，可以有效的提高查询对应列的查询效率。索引是好东西，但是不能滥用，因为索引的储存是磁盘开销，索引的更新也是维护开销。没错，网上大量文章都这么说的，当然也是正确的。  
  
而在撰写本文之前，我没有看到一篇文章提及聚簇索引和非聚簇索引在查询时配合关系，我可不认为这不值得一提，也恰恰是本文理论的重点。

1.  任何情况都肯定会用到聚簇索引，即使是整表从头到尾扫一边，那也是按照这个顺序扫一遍
2.  但不是任何情况都会用到你建立的非聚簇索引，因为分析器不会傻到先扫完一遍非聚簇索引，再去扫一遍聚簇索引，这么解释是不是很白？
3.  基于第1条和第2条，我们很容易得出一个理解，非聚簇索引建立在物理排序(即可认为是聚簇索引，有的话)的基础上，通过查看 SQL Server 查询分析器的实际执行计划，也证明了这一点，目的有2个：
    
     *  减少对聚簇索引的扫描范围，或者说对聚簇索引进行直接定位
     *  预先对数据进行逻辑排序，提高查询时的排序性能 (对用于排序的索引，正序和倒序的设置是必须符合实际查询情况的，否则查询优化器将弃用索引)
4.  那么一次查询会不会使用多个非聚簇索引呢？不会！ 你可以使用 with index 语句来强制使用2个或以上的非聚簇索引，但是效率更差。

通过以上分析，至少我自己的脑子跳出了关于索引的一大堆概念理念，变得开阔明朗了，不知道您看懂没  0.0

在 Topics 表上，将主键 Id 设置了唯一聚簇索引，另外增加了一个3个字段的复合唯一索引 ForumId(asc) + LastRepliedTime(desc) + Id(desc) 和一个单列索引 UserId。  
在 Posts 表上，主键仍然是 Id， 但不作为聚簇索引，我们把聚簇索引加在 TopicId 上，且不唯一。  
这样的索引设计，已经我们满足了大部分查询需求，但对于特定过滤条件的筛选，索引无能为力。我们总不能在想查询的字段上都加上索引吧，何况它们都是个布尔字段。  
  
**优化方法一： 标识字段另存小表**  
  
前面我们说了非聚簇索引的两个目的，而非聚簇索引对某些查询场景表示无能为力，那我们只好自己想办法解决了。我们通过以下的实际案例来解释。  
  
论坛有一个置顶贴的功能，就是把某几个话题固定摆在最上面嘛。最简单、最直接的实现途径有2个：一是查询按照置顶标识排序、二是把置顶贴查询出来然后缓存起 来。第一种办法我们立马可以放弃了，总不能为此专门给这个只有几个枚举值甚至是 bool 类型的字段加个索引吧。那么我们就将它单独查询出来然后再缓存。然后，又一个简单、直接的查询被想出来了，在 where 条件中加一个判断条件。但是请记得我们正在查一个数据量庞大的表，即便面对500万这样的数据量，SQL Server 就会耗费几个G的内存加上数分钟时间，简单的说，崩溃了。  
面对这样的需求，我们可以用一个更加直接、却不太容易被第一想到的方式：找另外一个地方（表） 吧这些标记为置顶的话题 Id 单独存起来。把置顶话题的 Id 单独存一张小表，然后通过 inner 联接查询或者 in 语句去查询 Topics 这个大表，无论哪种都是飞速的，因为通过主键聚簇索引的范围查找是 Seek 直接定位，是数据库中最快的查询计划。  
类似地，像精华贴等这种数量不庞大的结果集，我们可以使用同样的办法。最终仍然是按照原理去设计的：缩小对聚簇索引的扫描范围。  
  
**优化方法二： 大表切成小表**  
  
通过上述优化，对于一个500万数据量的数据库已经是小菜一碟了。但是记得我们的设计目标是9位数，100000000，要仔细数才能数清楚0的个数的那种。即便排序索引做得再优秀，面对一个庞大的结果集进行排序，翻到第1000页， 差不多30000行记录往后仍然是很耗时间的，也就是为什么大家翻到到一个巨大数目的页码时，被强制缩小到了一个限定范围的页数，不然就得崩溃了。  
  
其他文章就介绍了一种“切”法：分表，物理切。  
将不需要的数据归档，存储在另外的归档表里，让被频繁查询的主表维持在一个可接受的大小范围内。这种做法已经在诸多成熟程序中实现，当然是可行可靠的。但是带来了2个比较不爽的问题，一要定时搬迁数据，二破坏了逻辑结构关系，让笔者我这种完美癖很难接受。  
  
因此**我设计了另一种“切”的方式，逻辑切**。  
  
回顾我们前面反复在使用的一条规则，减少对聚簇索引的扫描范围。如果我能提前知道哪些数据是不可能出现在结果集当中的，那不就可以提前排除了吗？那问题就变成，我怎么知道哪些数据不可能出现在结果集中呢？  
  
建立一张冗余表： DailyStatistics ：  
Date - 主键，存储不包含时间部分的日期值  
TopicCount - 当天的话题数量  
PostCount - 当天的帖子数量  
TopicIdStone - 当天最后一个话题的 Id  
PostIdStone - 当天最后一个帖子的 Id  
  
实际上是在业务逻辑层面对数据量做好了统计从而实现查询优化。它由触发器来维护，当对 Topics 和 Posts 表进行插入或删除时，触发器自动对这个统计表进行计算维护，不需要在我们的程序中进行额外的逻辑维护。  
说到这里，我想大家已经能够明白接下来它能够干什么了。日期是我们判断话题归档的标识条件，通过这张统计表，我们把日期换算成了 Id 范围，我们只需要加一句 Id between .. and ... ， 再一次通过 Id 聚簇索引来缩小扫描范围。

这张表的维护开支，只有简单的触发逻辑实现插入更新，并且是自动维护的，与维护一个巨大索引的开支相比简直就是赚翻了；而这个表的数据量，10年不过3653条，毛毛雨嘛！

[http_www.cnblogs.com_wesley_archive_2012_04_23_2466982.html]: http://www.cnblogs.com/wesley/archive/2012/04/23/2466982.html