All Products
Search
Document Center

Scan all or part of table shards in all or part of database shards

Last Updated: Aug 17, 2020

In addition to routing an SQL statement to one or more database shards for execution, DRDS provides SCAN HINT to allow you to scan all or part of database shards and table shards. With 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.

SCAN HINT allows you to specify the following SQL execution manners:

  1. Execute an SQL statement in all table shards in all database shards.
  2. Execute an SQL statement in all table shards in a specified database shard.
  3. Execute an SQL statement in the specified table shard in a specified database shard by calculating the name of the table shard based on conditions.
  4. Execute an SQL statement in the specified table shard in a specified database shard by explicitly specifying the name of the table shard.

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

Notes:

  • 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 DRDS hint may become invalid, because the DRDS hint is used in the form of a MySQL comment and the MySQL Command-Line Client deletes all comments before sending the SQL statement to the server for execution. 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 a specified database shard by calculating the name of the table shard 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 tables 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 database shard.
  11. # Route an SQL statement to the specified table shard in a specified database shard by explicitly specifying the name of the table shard.
  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 database shard.
  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 you use this syntax, you must specify conditions for each table, for example, t1.id = 2 and t2.id = 2.
  23. condition_string:
  24. where_condition

Examples

  • Execute an SQL statement in all table shards in all database shards.
  1. SELECT /*+TDDL:scan()*/ COUNT(1) FROM t1

After you execute this SQL statement, 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 an SQL statement in all table shards in a specified database shard.
  1. SELECT /*+TDDL:scan(node='0,1,2')*/ COUNT(1) FROM t1

After you execute this SQL statement, 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 conditions.
  1. SELECT /*+TDDL:scan('t1', condition='t1.id = 2')*/ COUNT(1) FROM t1

After you execute this SQL statement, 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 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 you execute this SQL statement, 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.Note: Before you use this custom hint, you must ensure that the logical tables t1 and t2 are partitioned 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 an SQL statement in the specified table shard in a specified database shard by explicitly specifying the name of the table shard.
  1. SELECT /*+TDDL:scan('t1', real_table=("t1_00", "t1_01"))*/ COUNT(1) FROM t1

After you execute this SQL statement, the SQL statement is routed to the table shards t1_00 and 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 table shards:
  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 you execute this SQL statement, 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.