Exporting the Top SQL Template List

Function

This API is used to export the Top SQL template list after Top SQL is enabled. This function is available only for paid instances. The maximum query interval is one hour.

URI

GET /v3/{project_id}/instances/{instance_id}/top-sql-templates

**Table 1** URI parameters
Parameter	Mandatory	Type	Description
project_id	Yes	String	Explanation: Project ID of a tenant in a region To obtain this value, see Obtaining a Project ID. Constraints: N/A Values: The value can contain 32 characters. Only letters and digits are allowed. Default value: N/A
instance_id	Yes	String	Explanation: Unique ID of an instance Constraints: N/A Values: The value can contain 32 characters. Only letters and digits are allowed. Default value: N/A

**Table 2** Query parameters
Parameter	Mandatory	Type	Description
start_at	Yes	Long	Explanation: Start time Constraints: Unix timestamp, in milliseconds Values: [0, 2^31-1]. The actual value depends on the query result. Default value: N/A
end_at	Yes	Long	Explanation: End time Constraints: Unix timestamp, in milliseconds Values: [0, 2^31-1]. The actual value depends on the query result. Default value: N/A
datastore_type	Yes	String	Explanation: Database type Constraints: N/A Values: MySQL TaurusDB Default value: N/A
node_id	No	String	Explanation: Node ID Constraints: N/A Values: The value can contain 32 characters. Only letters and digits are allowed. Default value: N/A
sort	No	String	Explanation: Sorting field Constraints: N/A Values: executeNum: execution times totalCost: total duration avgCost: average duration totalScan: total rows scanned avgScan: average rows scanned Default value: N/A
asc	No	Boolean	Explanation: Sorting order Constraints: N/A Values: true: ascending false: descending Default value: false
offset	No	Integer	Explanation: Index offset. The query starts from the next piece of data indexed by this parameter. If this parameter is set to 1 and limit is set to 10, only the second to eleventh records are displayed. Constraints: N/A Values: [0, 2^31-1] Default value: 0: The query starts from the first data record.
limit	No	Integer	Explanation: Number of records to be queried. If this parameter is set to 10, a maximum of 10 records can be displayed. Constraints: N/A Values: [1, 100] Default value: 20

Request Parameters

None

Response Parameters

Status code: 200

**Table 3** Response body parameters
Parameter	Type	Description
top_sql_templates	Array of TopSqlTemplate objects	Explanation: SQL templates. Values: N/A
total_count	Integer	Explanation: Total number of SQL templates Values: [0, 2^31-1]. The actual value depends on the query result.

**Table 4** TopSqlTemplate
Parameter	Type	Description
sql_template	String	Explanation: SQL template ID Values: N/A
sql_sample_string	String	Explanation: SQL sample Values: N/A
sql_type	String	Explanation: SQL type Values: N/A
db_name	String	Explanation: Database name Values: N/A
execute_num	Long	Explanation: Total executions Values: N/A
total_cost	Double	Explanation: Total duration (ms) Values: N/A
avg_cost	Double	Explanation: Average duration (ms) Values: N/A
avg_rows_sent	Double	Explanation: Average rows returned Values: N/A
avg_rows_affected	Double	Explanation: Average rows affected Values: N/A
avg_lock_time	Double	Explanation: Average lock wait time (ms) Values: N/A
total_rows_examined	Double	Explanation: Total rows scanned Values: N/A
avg_rows_examined	Double	Explanation: Average rows scanned Values: N/A
total_cost_ratio	String	Explanation: Percentage of the total duration Values: N/A
total_examined_ratio	String	Explanation: Percentage of scanned rows Values: N/A
execute_num_ratio	String	Explanation: Percentage of execution times Values: N/A

Example Request

Exporting the Top SQL template list

GET https://das.cn-north-1.myhuaweicloud.com/v3/054c630ff780d4cc2f40c00d7f6fb21d/instances/6243b3fcf2f948578d46ed4c52fb54eein01/top-sql-templates?start_at=1611975464337&end_at=1611979064337&node_id=fec05693c76c4f389561051db430324cno01&sort=avgCost&asc=true&datastore_type=MySQL&offset=0&limit=10

Example Response

Status code: 200

Successful request

{
  "top_sql_templates" : [ {
    "db_name" : "db_01",
    "execute_num" : 30,
    "avg_rows_examined" : 0,
    "total_cost_ratio" : "100.00%",
    "avg_lock_time" : 0,
    "sql_template" : "SELECT sleep(?)",
    "avg_rows_affected" : 0,
    "avg_rows_sent" : 1,
    "avg_cost" : 5000.266666666666,
    "execute_num_ratio" : "23.81%",
    "total_examined_ratio" : "-",
    "sql_type" : "select",
    "total_cost" : 150008,
    "total_rows_examined" : 0,
    "sql_sample_string" : "select sleep(5)"
  } ],
  "total_count" : 1
}