lookup vindex

创建vttablet

分别在116、117、118节点上创建vttablet

主机	116	117	118
vttablet	200	201	202
vttablet	300	301	302

for i in 200 201 202;do
sh scripts/vttablet-up.sh user i vt_user1 3313 -80
done

vtctlclient \
-server 10.216.91.117:15999 \
-log_dir /data/service_data/vitess/vtdataroot/tmp/ \
-alsologtostderr \
InitShardMaster -force user/-80 zone1-200

for i in 300 301 302;do
sh scripts/vttablet-up.sh user i vt_user2 3314 80-
done

vtctlclient \
-server 10.216.91.117:15999 \
-log_dir /data/service_data/vitess/vtdataroot/tmp/ \
-alsologtostderr \
InitShardMaster -force user/80- zone1-300

创建vschema:

vim vschema_user_sharded.json

{
  "sharded": true,
  "vindexes": {
    "hash": {
      "type": "hash"
    },
    "unicode_loose_md5": {
      "type": "unicode_loose_md5"
    },
    "keyspace_idx": {
      "type": "numeric"
    }
  },
  "tables": {
    "user": {
      "column_vindexes": [
        {
          "column": "user_id",
          "name": "hash"
        }
      ],
      "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"
        }
      ],
      "auto_increment": {
        "column": "music_id",
        "sequence": "music_seq"
      }
    },
    "music_extra": {
      "column_vindexes": [
        {
          "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"
        }
      ]
    }
  }
}

vim vschema_user_seq.json

{
  "sharded": false,
  "tables": {
    "user_seq": {
      "type": "sequence"
    },
    "music_seq": {
      "type": "sequence"
    },
    "name_keyspace_idx": {}
  }
}

vtctlclient \
-server 10.216.91.117:15999 \
-log_dir /data/service_data/vitess/vtdataroot/tmp/ \
-alsologtostderr \
ApplyVSchema -vschema_file vschema_user_sharded.json user

commerce库中创建以下表：

vim vschema_user_seq.json

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)
);

vtctlclient \
-server 10.216.91.117:15999 \
-log_dir /data/service_data/vitess/vtdataroot/tmp/ \
-alsologtostderr \
ApplyVSchema -vschema_file vschema_user_seq.json commerce

user库中创建以下表：

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)
);

vtctlclient \
-server 10.216.91.117:15999 \
-log_dir /data/service_data/vitess/vtdataroot/tmp/ \
-alsologtostderr \
ApplySchema -sql-file create_user_schema.sql user

创建lookup vindex

vim lookupvindex_user.json

{
  "sharded": true,
  "vindexes": {
    "name_keyspace_idx": {
      "type": "consistent_lookup_unique",
      "params": {
        "table": "commerce.name_keyspace_idx",
        "from": "name",
        "to": "keyspace_id"
      },
      "owner": "user"
    }
  },
  "tables": {
    "user": {
      "column_vindexes": [
        {
          "column": "name",
          "name": "name_keyspace_idx"
        }
      ]
    }
  }
}

vim lookupvindex_music.json

{
  "sharded": true,
  "vindexes": {
    "music_keyspace_idx": {
      "type": "consistent_lookup_unique",
      "params": {
        "table": "user.music_keyspace_idx",
        "from": "music_id",
        "to": "keyspace_id"
      },
      "owner": "music"
    }
  },
  "tables": {
    "music": {
      "column_vindexes": [
        {
          "column": "music_id",
          "name": "music_keyspace_idx"
        }
      ]
    }
  }
}

创建VReplication streams

vtctlclient  \
-server 10.216.91.117:15999 \
-log_dir /data/service_data/vitess/vtdataroot/tmp/ \
-alsologtostderr \
CreateLookupVindex -cell=zone1 -tablet_types=RDONLY user "$(cat lookupvindex_user.json)"

vtctlclient  \
-server 10.216.91.117:15999 \
-log_dir /data/service_data/vitess/vtdataroot/tmp/ \
-alsologtostderr \
CreateLookupVindex -cell=zone1 -tablet_types=RDONLY user "$(cat lookupvindex_music.json)"

登陆vitess:

mysql -h10.216.91.116 -P15306