All Products
Search
Document Center

Scan all or some of database shards and table shards

Last Updated: Aug 12, 2020

In addition to routing an SQL statement to one or more database shards for execution, DRDS provides the SCAN hint to allow you to scan all or some of database shards and table shards. With the SCAN hint, you can route an SQL statement to each database shard at a time. For example, you can view all the table shards in a specified database shard, or view the data volume of each physical table of a specified logical table.

With the SCAN hint, you can specify the following SQL execution manners:

  • Execute an SQL statement in all table shards in all database shards.
  • Execute an SQL statement in all table shards in a specified database shard.
  • Execute an SQL statement in the specified table shard in the specified database shard by calculating the name of the physical table based on conditions.
  • Execute an SQL statement in the specified table shard in the specified database shard by explicitly specifying the name of the physical table.

The SCAN hint can be used in data manipulation language (DML) statements, data definition language (DDL) statements, and some data access language (DAL) statements.

This topic is applicable to Distributed Relational Database Service (DRDS) 5.3 and later versions.

Limits

  • DRDS hints can be in the formats of /+TDDL:hint_command/ and /! +TDDL:hint_command*/.

  • In the MySQL Command-Line Client, if you need to execute an SQL statement that contains a DRDS hint in the format of /*+TDDL:hint_command*/, add the -c parameter to the logon command. Otherwise, the client may delete the DRDS hint, which is used in the form of a MySQL comment, before sending the SQL statement to the server for execution. In this case, the DRDS hint is invalidated. For more information, see MySQL Client Options.

Syntax

  1. # SCAN HINT
  2. # Route an SQL statement to all table shards in all database shards.
  3. SCAN()
  4. # Route an SQL statement to all table shards in a specified database shard.
  5. SCAN(NODE="node_list") # Specify the database shard.
  6. # Route an SQL statement to the specified table shard in the specified database shard by calculating the name of the physical table based on conditions.
  7. SCAN(
  8. [TABLE=]"table_name_list" # Specify the name of the logical table.
  9. , CONDITION="condition_string" # Calculate the names of physical databases based on the content of TABLE and CONDITION.
  10. [, NODE="node_list"] ) # Filter the results obtained based on the content of CONDITION, to retain only the results of the specified physical database.
  11. # Route an SQL statement to the specified table shard in the specified database shard by explicitly specifying the name of the physical table.
  12. SCAN(
  13. [TABLE=]"table_name_list" # Specify the name of the logical table.
  14. , REAL_TABLE=("table_name_list") # Specify the name of the physical table. The same physical table names are applied to all physical databases.
  15. [, NODE="node_list"] ) # Filter the results obtained based on the content of CONDITION, to retain only the results of the specified physical database.
  16. # Specify physical table names or logical table names.
  17. table_name_list:
  18. table_name [, table_name]...
  19. # Specify physical databases by using GROUP_KEY and GROUP_INDEX, which can be obtained by executing the `SHOW NODE` statement.
  20. node_list:
  21. {group_key | group_index} [, {group_key | group_index}]...
  22. # Execute an SQL WHERE statement. When using this syntax, you must specify conditions for each table, for example, t1.id = 2 and t2.id = 2.
  23. condition_string:
  24. where_condition

Since DRDS 5.4.1, a 4-character random string is added to the name of a physical table, which is also called a table shard. You must run the SHOW TOPOLOGY command to obtain the topology of the corresponding logical table and the actual physical table name.

Examples

  • Execute the following SQL statement in all table shards in all database shards.

    1. SELECT /*+TDDL:scan()*/ COUNT(1) FROM t1

    After this statement is executed, the SQL statement is routed to all the physical tables corresponding to the logical table t1, and the result sets are merged and returned.

  • Execute the following SQL statement in all table shards in the specified database shard.

    1. SELECT /*+TDDL:scan(node='0,1,2')*/ COUNT(1) FROM t1

    After this statement is executed, all physical tables corresponding to the logical table t1 in database shards 0000, 0001, and 0002 are calculated, the SQL statement is routed to the physical tables, and the result sets are merged and returned.

  • Execute the following SQL statement in specified table shards based on the conditions:

    1. SELECT /*+TDDL:scan('t1', condition='t1.id = 2')*/ COUNT(1) FROM t1

    After this statement is executed, all physical tables that correspond to the logical table t1 and meet the conditions are calculated, the SQL statement is routed to the physical tables, and the result sets are merged and returned.

  • Execute the following SQL JOIN statement in the specified table shards based on the conditions:

    1. SELECT /*+TDDL:scan('t1, t2', condition='t1.id = 2 and t2.id = 2')*/ * FROM t1 a JOIN t2 b ON a.id = b.id WHERE b.name = "test"

    After the statement is executed, all physical tables that correspond to the logical tables t1 and t2 and meet the conditions are calculated, the SQL statement is routed to the physical tables, and the result sets are merged and returned.

    Before you use this custom hint, you must ensure that the logical tables t1 and t2 are split into the same number of database shards and the same number of table shards. Otherwise, the database shards calculated by the DRDS instance based on the conditions are different, and an error will be returned.

  • Execute the following SQL statement in the specified table shards in the specified database shards by explicitly specifying the names of the physical tables.

    1. SELECT /*+TDDL:scan('t1', real_table=("t1_00", "t1_01"))*/ COUNT(1) FROM t1

    After this statement is executed, the SQL statement is routed to the table shards t1_00``t1_01 in all database shards, and the result sets are merged and returned.

  • Execute the following SQL JOIN statement in the specified table shards in the specified database shards by explicitly specifying the names of the physical tables.

    1. SELECT /*+TDDL:scan('t1, t2', real_table=("t1_00,t2_00", "t1_01,t2_01"))*/ * FROM t1 a JOIN t2 b ON a.id = b.id WHERE b.name = "test";

    After this statement is executed, the SQL statement is routed to the table shards t1_00 t2_00 t1_01 t2_01 in all database shards, and the result sets are merged and returned.