[tcaplus DB knowledge base] Introduction to tcaplus DB local index

Index description

Tcallusdb supports two types of indexes: local indexes and global indexes.

• Local index: an index based on the tcallusdb primary key field. It is created with the table when it is being created.
• Global index: an index based on the first level fields (including primary key fields and non primary key fields) of the tcallusdb table.

Through local index and global index, users can easily use the index for data query. Advantages:

• Based on local index query, users can query indexes through some primary key fields
• Based on the global index, users can query in various forms through any level of fields, such as range, ambiguity, aggregation, paging and so on.

Use restrictions

Local index usage restrictions

• Once a local index is created, it cannot be modified, deleted or added during use. It will be deleted as the table is deleted.
• Local indexes only support exact matching, that is, when using local index fields as query criteria, they can only be matched to specific values, and fuzzy and range matching are not supported.

Global index usage restrictions

• Global indexes only support table level fields. For those nested fields and array list type fields, global indexes are not supported.
• Global indexes only support Generic type tables, not List type tables.
• Global indexes are currently supported in tcaplus_client tool, C++ SDK(TDR protocol table & PB protocol table), JDBC and GO API.
• Some global indexes are not supported by SQL. Please refer to the description of SQL not supported at the end of this article

Local index

characteristic

• The local index is a real-time index. When inserting or deleting data, the index data will be updated at the same time;
• The field of the local index must be included in the primary key field, and the field must also include the fragment field. Therefore, when querying, it will only fall on one data fragment for query;
• Local indexes only support equivalent queries;
• A table can establish multiple local indexes, and all fields of a local index must be included in the query;
• At present, only the generic table supports local indexes;

establish

The local index is stated in the table definition when the table is created. Once the table is created, the local index cannot be added, modified or deleted. When the table is deleted, the local index will be deleted together;

tdr table defines the local index

Examples are as follows:

<?xml version="1.0" encoding="GBK" standalone="yes" ?>
 
<metalib name="tcaplus_tb" tagsetversion="1" version="1">
 
<struct name="PLAYERONLINECNT" version="1" primarykey="TimeStamp,GameSvrID,GameAppID" splittablekey="TimeStamp">
    <entry name="TimeStamp"           type="uint32"     desc="In minutes" />
    <entry name="GameSvrID"         type="string"     size="64" />
    <entry name="GameAppID"         type="string"     size="64" desc="gameapp id" />
    <entry name="OnlineCntIOS"      type="uint32"     defaultvalue="0" desc="ios Number of people online" />
    <entry name="OnlineCntAndroid"  type="uint32"     defaultvalue="0" desc="android Number of people online" />
    <entry name="BinaryLen"         type="smalluint" defaultvalue="1" desc="Data source data length; when the length is 0, the source check is ignored"/>
    <entry name="binary"            type="tinyint"     desc="Binary" count= "1000" refer="BinaryLen" />
    <entry name="binary2"           type="tinyint"     desc="Binary 2" count= "1000" refer="BinaryLen" />
    <entry name="strstr"            type="string"     size="64" desc="character string"/>
    <index name="index_1"  column="TimeStamp"/>
    <index name="index_2"  column="TimeStamp, GameSvrID"/>
    <index name="index_3"  column="TimeStamp, GameAppID"/>
</struct>
 
</metalib>

Among them, the items of index are defined local indexes. Three local indexes (index_1, index_2, index_3) are defined in the above table. The name of the index can be any string. column is the field contained in the index, and multiple fields are separated by commas. Indexing constraints:

• The local index must contain fragmentation factors. As shown above, the TimeStamp field must be included in each primary key index;
• All fields in the local index must belong to the primary key field. Therefore, indexing GameSvrID and GameAppID is not allowed;
• It is also not allowed to establish local indexes on other non primary key fields.

pb table definition local index

Examples are as follows:

syntax = "proto3";                      // Specify the version of the protocol buffers language
 
 
import "tcaplusservice.optionv1.proto"; // Use the public definitions of TcaplusDB by importing them. 
 
message game_players {  // Define a TcaplusDB table with message
 
        // Specify the primary keys with the option tcaplusservice.tcaplus_primary_key
        // The primary key of a TcaplusDB table has a limit of 4 fields
    option(tcaplusservice.tcaplus_primary_key) = "player_id, player_name, player_email";
 
    // Specify the primary key indexes with the option tcaplusservice.tcaplus_index
    option(tcaplusservice.tcaplus_index) = "index_1(player_id, player_name)";
    option(tcaplusservice.tcaplus_index) = "index_2(player_id, player_email)";
 
 
    // Value Types supported by TcaplusDB
    // int32, int64, uint32, uint64, sint32, sint64, bool, fixed64, sfixed64, double, fixed32, sfixed32, float, string, bytes
    // Nested Types Message
 
    // primary key fields
    int64 player_id = 1; 
    string player_name = 2;
    string player_email = 3;
 
 
    // Ordinary fields
    int32 game_server_id = 4;
    repeated string login_timestamp = 5;
    repeated string logout_timestamp = 6;
    bool is_online = 7;
 
    payment pay = 8;
}
 
 
message payment {
     
        int64 pay_id = 1;
        uint64 amount = 2;
    int64 method = 3;
 
}

Where, * * Option (tcaplus service. Tcaplus_index) * * is the definition method of local index. Index_1 in index_1(player_id, player_name) is the index name, and player_id and player_name are the index fields;

query

The local index only supports equivalent query, that is, when using the local index query, all the fields defined in the local index need to be given values. For example, if the local index is defined and the fields are key1 and key2, the values of key1 and key2 must be given when using the index for query, and the query can be carried out in the way of key1=XXX and key2=XXX;

In tcaplus, the corresponding request is a GetByPartKey request. Only this request is queried using the local index;

During local index query, a lot of data may be returned. At this time, tcaplus will return packets. If the packet receiving speed on the service side is lower than that of tcaplus returning response packets, tcaplus may lose packets because the network cache is full. It is generally recommended to use limit and offset to request multiple times when using local index query Data, especially when the amount of data is large;

matters needing attention

Assuming that the fields contained in the local index are key1 and key2, if the number of records with key1=XXX and key2=XXX is very large, performance problems are easy to occur when querying the local index with this condition, which should be avoided as far as possible. Of course, tcaplus does not limit the number of records at present.

Tcallusdb is a distributed NoSQL database produced by Tencent. The storage and scheduling code is completely self-developed. It has the characteristics of cache + landing integration architecture, PB level storage, millisecond delay, lossless horizontal expansion and complex data structure. At the same time, it has the characteristics of rich ecology, convenient migration, extremely low operation and maintenance cost and five nine high availability. Its customers cover games, the Internet and politics Services, finance, manufacturing and the Internet of things.