Analyzing a SQL statement using VEXPLAIN

Introduction

To see which queries are run on your behalf on the MySQL instances when you execute a query on vtgate, you can use vexplain [ALL|PLAN|QUERIES].

QUERIES Type

The QUERIES format returns an output similar to what the command line application vtexplain returns - a list of the queries that have been run on MySQL, and against which shards they were issued.

How it works

Unlike normal EXPLAIN queries, VEXPLAIN QUERIES actually runs your query, and logs the interactions with the tablets. After running your query using this extra logging, the result you get is a table with all the interactions listed.

How to read the output

The output has four columns:

  • The first column, # groups queries that were sent in a single call together.
  • Keyspace - which keyspace was this query sent to.
  • Shard - for sharded keyspaces, this column will show which shard a query is sent to.
  • Query - the actual query used.

Example 1:

  1. mysql> vexplain queries select * from user where id = 4;
  2. +------+----------+-------+-----------------------------------------------------------+
  3. | #    | keyspace | shard | query                                                     |
  4. +------+----------+-------+-----------------------------------------------------------+
  5. |    0 | ks       | c0-   | select id, lookup, lookup_unique from `user` where id = 4 |
  6. +------+----------+-------+-----------------------------------------------------------+
  7. 1 row in set (0.00 sec)

Here we have a query where the planner can immediately see which shard to send the query to.

Example 2:

  1. mysql> vexplain queries select * from user where lookup = 'apa';
  2. +------+----------+-------+-------------------------------------------------------------------+
  3. | #    | keyspace | shard | query                                                             |
  4. +------+----------+-------+-------------------------------------------------------------------+
  5. |    0 | ks       | -40   | select lookup, keyspace_id from lookup where lookup in ('apa')    |
  6. |    1 | ks       | c0-   | select id, lookup, lookup_unique from `user` where lookup = 'apa' |
  7. |    2 | ks       | 40-80 | select id, lookup, lookup_unique from `user` where lookup = 'apa' |
  8. +------+----------+-------+-------------------------------------------------------------------+
  9. 3 rows in set (0.02 sec)

This is a query where the planner has to do a vindex lookup to find which shard the data might live on.

PLAN Type

The PLAN format returns the vtgate plan for the given query. It does so without actually running any queries - it just plans the given query and presents the plan.

How to read the output

The output contains a scalar output having a JSON description of the plan that vtgate will use for the query.

Example:

  1. mysql> vexplain plan select * from corder join commerce.product as prod on corder.sku = prod.sku;
  1. {
  2.   "OperatorType": "Join",
  3.   "Variant": "Join",
  4.   "JoinColumnIndexes": "L:1,L:2,L:3,L:4,R:0,R:1,R:2",
  5.   "JoinVars": {
  6.     "corder_sku": 0
  7.   },
  8.   "TableName": "corder_product",
  9.   "Inputs": [
  10.     {
  11.       "OperatorType": "Route",
  12.       "Variant": "Scatter",
  13.       "Keyspace": {
  14.         "Name": "customer",
  15.         "Sharded": true
  16.       },
  17.       "FieldQuery": "select corder.sku, corder.order_id as order_id, corder.customer_id as customer_id, corder.sku as sku, corder.price as price from corder where 1 != 1",
  18.       "Query": "select corder.sku, corder.order_id as order_id, corder.customer_id as customer_id, corder.sku as sku, corder.price as price from corder",
  19.       "Table": "corder"
  20.     },
  21.     {
  22.       "OperatorType": "Route",
  23.       "Variant": "Unsharded",
  24.       "Keyspace": {
  25.         "Name": "commerce",
  26.         "Sharded": false
  27.       },
  28.       "FieldQuery": "select prod.sku as sku, prod.description as description, prod.price as price from product as prod where 1 != 1",
  29.       "Query": "select prod.sku as sku, prod.description as description, prod.price as price from product as prod where prod.sku = :corder_sku",
  30.       "Table": "product"
  31.     }
  32.   ]
  33. }

In this example, we are executing a cross-keyspace join between two tables. The corder table living in the customer keyspace and product table living in the commerce keyspace.

ALL Type

The ALL format returns the vtgate plan along with the MySQL explain output for the executed queries.

How to read the output

The output contains a scalar output having a JSON description of the plan that vtgate will use for the query annotated with the explain output from mysql for these queries.

Example:

  1. mysql> vexplain all select * from corder join commerce.product as prod on corder.sku = prod.sku;
  1. {
  2.   "OperatorType": "Join",
  3.   "Variant": "Join",
  4.   "JoinColumnIndexes": "L:1,L:2,L:3,L:4,R:0,R:1,R:2",
  5.   "JoinVars": {
  6.     "corder_sku": 0
  7.   },
  8.   "TableName": "corder_product",
  9.   "Inputs": [
  10.     {
  11.       "OperatorType": "Route",
  12.       "Variant": "Scatter",
  13.       "Keyspace": {
  14.         "Name": "customer",
  15.         "Sharded": true
  16.       },
  17.       "FieldQuery": "select corder.sku, corder.order_id as order_id, corder.customer_id as customer_id, corder.sku as sku, corder.price as price from corder where 1 != 1",
  18.       "Query": "select corder.sku, corder.order_id as order_id, corder.customer_id as customer_id, corder.sku as sku, corder.price as price from corder",
  19.       "Table": "corder",
  20.       "mysql_explain_json": {
  21.         "query_block": {
  22.           "select_id": 1,
  23.           "cost_info": {
  24.             "query_cost": "0.65"
  25.           },
  26.           "table": {
  27.             "table_name": "corder",
  28.             "access_type": "ALL",
  29.             "rows_examined_per_scan": 4,
  30.             "rows_produced_per_join": 4,
  31.             "filtered": "100.00",
  32.             "cost_info": {
  33.               "read_cost": "0.25",
  34.               "eval_cost": "0.40",
  35.               "prefix_cost": "0.65",
  36.               "data_read_per_join": "640"
  37.             },
  38.             "used_columns": [
  39.               "order_id",
  40.               "customer_id",
  41.               "sku",
  42.               "price"
  43.             ]
  44.           }
  45.         }
  46.       }
  47.     },
  48.     {
  49.       "OperatorType": "Route",
  50.       "Variant": "Unsharded",
  51.       "Keyspace": {
  52.         "Name": "commerce",
  53.         "Sharded": false
  54.       },
  55.       "FieldQuery": "select prod.sku as sku, prod.description as description, prod.price as price from product as prod where 1 != 1",
  56.       "Query": "select prod.sku as sku, prod.description as description, prod.price as price from product as prod where prod.sku = :corder_sku",
  57.       "Table": "product",
  58.       "mysql_explain_json": {
  59.         "query_block": {
  60.           "select_id": 1,
  61.           "cost_info": {
  62.             "query_cost": "1.00"
  63.           },
  64.           "table": {
  65.             "table_name": "prod",
  66.             "access_type": "const",
  67.             "possible_keys": [
  68.               "PRIMARY"
  69.             ],
  70.             "key": "PRIMARY",
  71.             "used_key_parts": [
  72.               "sku"
  73.             ],
  74.             "key_length": "130",
  75.             "ref": [
  76.               "const"
  77.             ],
  78.             "rows_examined_per_scan": 1,
  79.             "rows_produced_per_join": 1,
  80.             "filtered": "100.00",
  81.             "cost_info": {
  82.               "read_cost": "0.00",
  83.               "eval_cost": "0.10",
  84.               "prefix_cost": "0.00",
  85.               "data_read_per_join": "272"
  86.             },
  87.             "used_columns": [
  88.               "sku",
  89.               "description",
  90.               "price"
  91.             ]
  92.           }
  93.         }
  94.       }
  95.     }
  96.   ]
  97. }

This example uses the same query as the previous ones. For all the Route operators, we are annotating them with the MySQL explain output for the query that the route is executing.

Safety for DML

The normal behaviour for VEXPLAIN is to not actually run the query for DMLs — it usually only plans the query and presents the produced plan for the PLAN type. Since vexplain ALL|QUERIES really runs your queries, you need to add a query directive to show that you are aware that your DML will actually run.

Example:

  1. mysql> vexplain queries insert into customer(email) values('abc@xyz.com');
  2. ERROR 1105 (HY000): VT09008: vexplain queries/all will actually run queries

This is the error you will get is you do not add the comment directive to your VEXPLAIN statement.

Example:

  1. mysql> vexplain /*vt+ EXECUTE_DML_QUERIES */ queries insert into customer(email) values('abc@xyz.com');
  2. +------+----------+-------+-----------------------------------------------------------------------+
  3. | #    | keyspace | shard | query                                                                 |
  4. +------+----------+-------+-----------------------------------------------------------------------+
  5. |    0 | customer | 80-   | insert into customer(email, customer_id) values ('abc@xyz.com', 1001) |
  6. +------+----------+-------+-----------------------------------------------------------------------+
  7. 1 row in set (0.00 sec)

Here we can see how vtgate will insert rows to the main table, but also to the two lookup vindexes declared for this table.

Note - MySQL client by default strips out the comments from the queries before it sends to the server. So you’ll need to run the client with -c flag to allow passing in comments.