数据库索引—三星索引

三星索引概念

对于一个查询而言，一个三星索引，可能是其最好的索引。

满足的条件如下：

• 索引将相关的记录放到一起则获得一星 （比重27%）

• 如果索引中的数据顺序和查找中的排列顺序一致则获得二星（排序星） （比重27%）

• 如果索引中的列包含了查询中需要的全部列则获得三星（宽索引星） （比重50%）

这三颗星，哪颗最重要？第三颗星。因为将一个列排除在索引之外可能会导致很多磁盘随机读（回表操作）。第一和第二颗星重要性差不多，可以理解为第三颗星比重是50%，第一颗星为27%，第二颗星为23%，所以在大部分的情况下，会先考虑第一颗星，但会根据业务情况调整这两颗星的优先度。

一星：

一星的意思就是：如果一个查询相关的索引行是相邻的或者至少相距足够靠近的话，必须扫描的索引片宽度就会缩至最短，也就是说，让索引片尽量变窄，也就是我们所说的索引的扫描范围越小越好。

二星（排序星） ：

在满足一星的情况下，当查询需要排序，group by、 order by，如果查询所需的顺序与索引是一致的（索引本身是有序的），是不是就可以不用再另外排序了，一般来说排序可是影响性能的关键因素。

三星（宽索引星） ：

在满足了二星的情况下，如果索引中所包含了这个查询所需的所有列（包括 where 子句和 select 子句中所需的列，也就是覆盖索引），这样一来，查询就不再需要回表了，减少了查询的步骤和IO请求次数，性能几乎可以提升一倍。

设计三星索引实战

现在有表，SQL如下

CREATE TABLE customer (
    cno INT,
    lname VARCHAR (10),
    fname VARCHAR (10),
    sex INT,
    weight INT,
    city VARCHAR (10)
);
​
CREATE INDEX idx_cust ON customer (city, lname, fname, cno);

对于下面的SQL而言，这是个三星索引

select cno,fname from customer where lname=’xx’ and city =’yy’ order by fname;

来评估下：

第一颗星：所有等值谓词的列，是组合索引的开头的列，可以把索引片缩得很窄，符合。

根据之前讲过的联合索引，我们是知道条件已经把搜索范围搜到很窄了

第二颗星：order by的fname字段在组合索引中且是索引自动排序好的，符合。

第三颗星：select中的cno字段、fname字段在组合索引中存在，符合。

现在有表，SQL如下：

CREATE TABLE `test` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `user_name` VARCHAR (100) DEFAULT NULL,
    `sex` INT (11) DEFAULT NULL,
    `age` INT (11) DEFAULT NULL,
    `c_date` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
​
) ENGINE = INNODB AUTO_INCREMENT = 12 DEFAULT CHARSET = utf8;

SQL语句如下：

select user_name,sex,age from test where user_name like 'test%'  and sex =1 ORDER BY age

如果我们建立索引(user_name,sex,age)：

第三颗星，满足

第一颗星，满足

第二颗星，不满足，user_name 采用了范围匹配，sex 是过滤列，此时age 列无法保证有序的。

上述我们看到，此时索引(user_name,sex,age)并不能满足三星索引中的第二颗星（排序）。

于是我们改改，建立索引(sex, age，user_name)：

第一颗星，不满足，只可以匹配到sex，sex选择性很差，意味着是一个宽索引片(同时因为age也会导致排序选择的碎片问题)

第二颗星，满足，等值sex 的情况下，age是有序的，

第三颗星，满足，select查询的列都在索引列中，

对于索引(sex,age，user_name)我们可以看到，此时无法满足第一颗星，窄索引片的需求。

以上2个索引，都是无法同时满足三星索引设计中的三个需求的，我们只能尽力满足2个。而在多数情况下，能够满足2颗星，已经能缩小很大的查询范围了，具体最终要保留那一颗星（排序星 or 窄索引片星），这个就需要看查询者自己的着重点了，无法给出标准答案。