数据库多列组合索引(Clustered index)

在定义表的时候,经常遇到需要组合两列或跟多列为唯一索引的场景(concatenated index, also called multi-column, composite or combined index),比如在SmartERP系统中,一篇文章可以属于多个类目,通过表 e_article_category_detail 来关联,表定义为:

[id] [int] IDENTITY(1,1) NOT NULL,
 [article_id] [bigint] NOT NULL,
 [category_id] [int] NOT NULL,
 [primary_category] [bit] NOT NULL

其中字段 article_id 链接表 e_article,字段 category_id 链接表 e_article_category,id字段自动编号便于修改、删除操作,因为文章和类目的组合具有唯一性,所以需要创建两列的唯一索引,思路不外乎两种:1、创建 article_id + category_id 的两列主键的聚集索引,id列的普通唯一索引;2、id列主键聚集索引,创建两列的普通唯一索引。阅读下面的参考文章,http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys,我们知道两列索引的字段前后位置有差别,这两种思路的差别会有多大呢?在MS SQL Server 2014中,我们分别执行三个语句:

SELECT * FROM e_article_category_detail where article_id = 1
SELECT * FROM e_article_category_detail where category_id = 1
SELECT * FROM e_article_category_detail where article_id = 1 AND category_id = 1

第一种情况:id 字段为主键(如果没有主键,前两条语句就会以表格扫描的方式去匹配,效率非常低),article_id + category_id 唯一索引:

image.png

(在MS SQL Server 2008R2中执行计划有差异)

image.png

image.png

第二种情况:article_id + category_id 组合主键,id 唯一索引:

image.png

image.png

image.png

结论:在新版的数据库中,索引字段的位置已经没那么重要,但是第一个始终字段占优势(所以在链接表时首先选择第一个字段)。聚集索引(Clustered index)因为保存行数据物理存取顺序(所以也只能定义一个),其优势始终存在,哪怕是检索其他索引数据,所以数据库不管需不需要都要定义一个聚集索引。