Vindex

vindex

Vindex可以将字段值映射为keyspace ids。Vitess中的每个分片都覆盖一定范围的keyspace ID值，因此可以使用此映射来识别每一行属于哪个分片。

如何选择vindex

• where条件中频繁使用的列
• 唯一性 (映射函数)
• 用于join关联的列
• 基数较高的列

vindex的优势

Vindex的优势源于其灵活性:

• 一张表可以有多个vindex.
• Vindexes可以是非唯一的，允许一个列产生多个kespace id值。
• Vindexes可以是一个简单的函数或者基于lookup表。
• Vindex可以在多张表中进行共享。
• 可以创建使用自定义的Vindexes。

Primary Vindex

Primary Vindex类似于表中的主键。每张分片表中必须含有一个Primary Vindex。primary vindex必须是唯一的：也就是说给定一个输入值，只能生成一个keyspace id。Primary Vindex决定了插入的行属于哪一个分片。通常可以将Primary Vindex称为分片键。

Primary Vindex必须是唯一的，但不一定是主键或唯一键，允许不同行映射为同一个keyspace id，Vindex唯一性约束仅确保同一个分片中的所有行具有相同的keyspace id边界。

• 分片表必须有一个Primary Vindex
• Primary Vindex必须是唯一的，但不一定是主键或唯一键，允许不同行映射为同一个keyspace id
• Primary Vindex列称为分片键
• Primary Vindex不仅定义了分片键还决定了分片策略

Secondary Vindex

Secondary Vindexes可以为where条件中没有使用primary vindex提供选择优化。Secondary Vindexes返回一个或多个keyspace id，可以确保查询落在确定的分片上，如果没有Secondary Vindexes，查询语句将会分散到所有分片上。

secondary vindex列上需要有相应的辅助索引。

Lookup Vindex

使用lookup vindex需要创建一张表，该表包含两个字段，第一个字段为主表的vindex字段，第二个字段字符类型为binary或者VARBINARY需要存储keyspace id。主表中插入数据时相应的也会在lookup表中插入数据。

表结构示例：

create table user_seq(
id int, 
next_id bigint, 
cache bigint, 
primary key(id)
) comment 'vitess_sequence';
insert into user_seq(id, next_id, cache) values(0, 1, 3);

create table music_seq(
id int, 
next_id bigint, 
cache bigint, 
primary key(id)
) comment 'vitess_sequence';
insert into music_seq(id, next_id, cache) values(0, 1, 2);

create table name_keyspace_idx(
name varchar(128), 
keyspace_id binary(8), 
primary key(name, keyspace_id)
);

create table user(
user_id bigint, 
name varchar(128), 
primary key(user_id)
);

create table user_extra(
user_id bigint, 
extra varchar(128), 
primary key(user_id)
);

create table music(
user_id bigint, 
music_id bigint, 
primary key(user_id, music_id)
);

create table music_extra(
music_id bigint, 
keyspace_id bigint unsigned, 
primary key(music_id)
);

create table name_info(
name varchar(128), 
info varchar(128), 
primary key(name)
);

create table music_keyspace_idx(
music_id bigint not null auto_increment,
keyspace_id binary(8), 
primary key(music_id)
);

vschema示例：

{
  "sharded": true,
  "vindexes": {
    "hash": {
      "type": "hash"
    },
    "unicode_loose_md5": {
      "type": "unicode_loose_md5"
    },
    "name_keyspace_idx": {
      "type": "lookup",
      "params": {
        "table": "name_keyspace_idx",
        "from": "name",
        "to": "keyspace_id"
      },
      "owner": "user"
    },
    "music_keyspace_idx": {
      "type": "lookup_unique",
      "params": {
        "table": "music_keyspace_idx",
        "from": "music_id",
        "to": "keyspace_id"
      },
      "owner": "music"
    },
    "keyspace_idx": {
      "type": "numeric"
    }
  },
  "tables": {
    "user": {
      "column_vindexes": [
        {
          "column": "user_id",
          "name": "hash"
        },
        {
          "column": "name",
          "name": "name_keyspace_idx"
        }
      ],
      "auto_increment": {
        "column": "user_id",
        "sequence": "user_seq"
      }
    },
    "user_extra": {
      "column_vindexes": [
        {
          "column": "user_id",
          "name": "hash"
        }
      ]
    },
    "music": {
      "column_vindexes": [
        {
          "column": "user_id",
          "name": "hash"
        },
        {
          "column": "music_id",
          "name": "music_keyspace_idx"
        }
      ],
      "auto_increment": {
        "column": "music_id",
        "sequence": "music_seq"
      }
    },
    "music_extra": {
      "column_vindexes": [
        {
          "column": "music_id",
          "name": "music_keyspace_idx"
        },
        {
          "column": "keyspace_id",
          "name": "keyspace_idx"
        }
      ]
    },
    "name_info": {
      "column_vindexes": [
        {
          "column": "name",
          "name": "unicode_loose_md5"
        }
      ]
    },
    "music_keyspace_idx": {
      "column_vindexes": [
        {
          "column": "music_id",
          "name": "hash"
        }
      ]
    }
  }
}

keyspace id

When Vitess shards your database, it assigns each row an identifier called a keyspace ID. We then break up the domain of all keyspace ID values into ranges and assign ranges to each shard. Therefore, if a row’s keyspace ID falls in a certain range, it will be assigned a specific shard. This is cool because we can reshard the data completely without changing the keyspace ID at all… or your application knowing about it. The less your application has to know, the better.