文档首页

租户端数据库 TDSQL MYSQL 版（TDSQL）开发指南效用声明

效用声明

最近更新时间: 2024-10-17 17:10:00

DESCRIBE 语句

DESCRIBE 用于获取表结构信息：

示例：

mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

EXPLAIN 语句

语法

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement

explain_type: {
    FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
  | TREE
}

explainable_stmt: {
    SELECT statement
  | TABLE statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

注意：

查看执行计划，SQL不会真正执行

在只读的DB上，无法查看写SQL的执行计划

示例：

DROP TABLE if exists employees;
CREATE TABLE employees (
    id INT key NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired date,
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
shardkey=id;

MySQL [test]> explain select id,fname,lname from employees where id=20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: no matching row in const table
         info: set_1624363251_3, explain select id,fname,lname from `test`.`employees` where (id = 20) 
1 row in set (0.00 sec)

执行计划中各字段含义：

id：执行行顺序，按1,2,3,4…进行排序。在所有组中，id值越大，优先级越高，越先执行。id如果相同，可以认为是一组，从上往下顺序执行
select_type：select的类型。
table：输出记录的表，对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集
partitions：符合的分区
type：显示的是访问类型，访问类型表示以何种方式去访问数据，例如全表扫描
possible_keys：优化器可能使用到的索引
key：优化器实际选择的索引
key_len：表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：优化器预估的记录数量，根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数
filtered：该 filtered 列指示将按表条件过滤的表行的估计百分比。最大值为100，这意味着不会对行进行过滤。值从100开始减少表示过滤量增加
Extra：额外的显示选项
info：网关下推，记录了实际发往的set名称和sql信息，info这个一列信息是分布式实例执行计划特有的

网关下推示例

测试表准备

--创建测试表
drop table if exists t1;
create table t1(a int key, b int) shardkey=a;
drop table if exists t2;
create table t2(a int key, b int) shardkey=a;

--集群的结构，包含2个set

select 查询的下推

指定了shardkey的单表查询。根据shardkey的哈希值计算出目标set，然后将查询直接下推给目标set执行。

-- info字段展示了发送的目标set，以及下推的查询
MySQL [test]> explain select * From t1 where a=1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: no matching row in const table
         info: set_1624363222_1, explain select * from `test`.`t1` where (a = 1) 
1 row in set (0.00 sec)

未指定shardkey的单表查询。将查询广播给所有目标set执行。

-- 广播给两个set执行，因此返回了两条记录，其中info字段展示了发送的目标set，以及下推的查询
MySQL [test]> explain select * From t1 where b=1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p0,p1,p2,p3,p4,p5,p6,p7
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
         info: set_1624363222_1, explain select * from `test`.`t1` where (b = 1)
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p8,p9,p10,p11,p12,p13,p14,p15
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
         info: set_1624363251_3, explain select * from `test`.`t1` where (b = 1)
2 rows in set (0.01 sec)

多表连接查询。当shardkey相等时，将查询直接下推给db执行。

-- 广播给两个set执行，因此返回了两条记录，其中info字段展示了发送的目标set，以及下推的查询
-- shardkey相等，但shardkey未指定明确的值，因此广播给所有set执行。
MySQL [test]> explain select * from t1, t2 where t1.a=t2.a;

-- shardkey相等，且shardkey指定了明确的值，因此shardkey的哈希值转给目标set执行。
MySQL [test]> explain select * from t1, t2 where t1.a=t2.a and t1.a=1;

-- shardkey相等，且shardkey指定了多个明确的值，因此shardkey的哈希值转给多个目标set执行。
MySQL [test]> explain select * from t1, t2 where t1.a=t2.a and t1.a in (1,2,3);

-- shardkey相等，且shardkey指定了多个明确的值，但当前网关在计算shardkey的值时会忽略'or'谓词，因此将广播给所有set执行。
MySQL [test]> explain select * from t1, t2 where t1.a=t2.a and (t1.a=1 or t1.a=2);

常用聚合函数，包括sum、count、avg、max以及min的下推。

-- 网关将查询广播给所有set，并对set返回的聚合结果进行累加
MySQL [test]> explain select count(1) from t1;

-- 网关将avg转换为sum、count，并广播给所有set执行，再根据set返回的sum、count值计算出全局的avg
MySQL [test]> explain select avg(a) from t1;

-- 多表连接时，表的shardkey相等，网关将查询广播给所有set执行，并对set返回的聚合结果进行累加
MySQL [test]> explain select sum(t1.a) from t1, t2 where t1.a=t2.a;

-- 多表连接时，表的shardkey相等，且shardkey指定了明确的值，网关将查询转发给目标set执行
MySQL [test]> explain select sum(t1.a) from t1, t2 where t1.a=t2.a and t1.a=1;

-- 网关将查询广播给所有set执行，再对set返回的结果进行归并排序，计算出每个分组的全局sum值
MySQL [test]> explain select sum(a) from t1 group by b\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p8,p9,p10,p11,p12,p13,p14,p15
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using temporary; Using filesort
         info: set_1624363251_3, explain select sum(a),b, COLLATION(b) from `test`.`t1` group by b order by b
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p0,p1,p2,p3,p4,p5,p6,p7
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using temporary; Using filesort
         info: set_1624363222_1, explain select sum(a),b, COLLATION(b) from `test`.`t1` group by b order by b
2 rows in set (0.00 sec)

distinct的下推。

-- 将distinct下推给set执行，同时额外追加order by操作。网关对set返回的有序元组进行归并排序和去重，从而得到全局去重的结果。
MySQL [test]> explain select distinct b from t1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p8,p9,p10,p11,p12,p13,p14,p15
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using temporary; Using filesort
         info: set_1624363251_3, explain select distinct b from `test`.`t1` order by b
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: p0,p1,p2,p3,p4,p5,p6,p7
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using temporary; Using filesort
         info: set_1624363222_1, explain select distinct b from `test`.`t1` order by b
2 rows in set (0.00 sec)

子查询的下推。

-- 通过等值传递，能够推断出父查询和子查询中表的shardkey相等时，则网关将查询下推给db执行。
-- 注意：由于实现方式的不同，部分查询的explain的结果为json的形式。其中DBQuery字段描述了下推到db执行的查询。
-- IN子查询
MySQL [test]> explain select * from t1 where t1.a in (select a from t2)\G;
*************************** 1. row ***************************
trace: [
   {
      "ProxyDeduplicate " : "false",
      "Query" : "set_1624363222_1 set_1624363251_3  , Select `t1`.`a`, `t1`.`b` from `test`.`t1` where (`test`.`t1`.`a`) in (select `test`.`t2`.`a` from `test`.`t2`)",
      "QueryMode" : "Hash"
   }
]

-- EXISTS 子查询
MySQL [test]> explain select * from t1 where exists (select * From t2 where t1.a=t2.a)\G;
*************************** 1. row ***************************
trace: [
   {
      "ProxyDeduplicate " : "false",
      "Query" : "set_1624363222_1 set_1624363251_3  , Select `t1`.`a`, `t1`.`b` from `test`.`t1` where exists(select 1 from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))",
      "QueryMode" : "Hash"
   }
]

-- 通过等值传递，能够推断出父查询和子查询中表的shardkey相等时，则网关将查询下推给db执行。
MySQL [test]> explain select * from t1 where t1.a in (select b from t2 where t2.a=t2.b)\G;
*************************** 1. row ***************************
trace: [
   {
      "ProxyDeduplicate " : "false",
      "Query" : "set_1624363222_1 set_1624363251_3  , Select `t1`.`a`, `t1`.`b` from `test`.`t1` where (`test`.`t1`.`a`) in (select `test`.`t2`.`b` from `test`.`t2` where (`test`.`t2`.`a` = `test`.`t2`.`b`))",
      "QueryMode" : "Hash"
   }
]

distinct聚合函数的下推，例如count(distinct 表达式)、sum(distinct 表达式)等。

-- 不存在分组(group by)和排序(order by)操作时，网关只下推distinct查询给所有set执行.
-- 网关对set返回的结果再次去重，从而计算count(distinct b)的值。
MySQL [test]> explain select count(distinct b) from t1 \G
*************************** 1. row ***************************
trace: [
   {
      "AggFunc " : "count(distinct `test`.`t1`.`b`)",
      "ProxyDeduplicate " : "false",
      "Query" : "set_1624363222_1 set_1624363251_3  , Select DISTINCT `t1`.`b` from `test`.`t1` where 1",
      "QueryMode" : "Hash"
   }
]

-- 当存在分组(group by)操作时，网关下推distinct操作，并在下推的查询中额外添加order by语句。
-- 网关对set返回的有序元组按照'分组列'进行归并排序，并计算每个分组的聚合函数count(distinct b)的值。
MySQL [test]> explain select count(distinct b) from t1 group by a\G
*************************** 1. row ***************************
trace: [
   {
      "AggFunc " : "count(distinct `test`.`t1`.`b`)",
      "DBGroupColumns " : "`test`.`t1`.`a`",
      "DBSortedColumns " : "`test`.`t1`.`a`",
      "ProxyDeduplicate " : "false",
      "Query" : "set_1624363222_1 set_1624363251_3  , Select DISTINCT `t1`.`a`, `t1`.`b`, `test`.`t1`.`a` from `test`.`t1` where 1 order by 3",
      "QueryMode" : "Hash"
   }
]

-- 当同时存在分组(group by)以及排序(order by)操作时，网关按照前面的例子先计算出分组聚合操作的结果，再利用临时表对分组聚合的结果进行排序。
-- 其中PorxyTmptable字段展示了创建的临时表；ProxyQuery展示了需要在临时表上执行的查询。
MySQL [test]> explain select a, count(distinct b) as cnt from t1 group by a order by cnt \G
*************************** 1. row ***************************
trace: [
   {
      "AggFunc " : "count(distinct `test`.`t1`.`b`)",
      "DBGroupColumns " : "`test`.`t1`.`a`",
      "DBSortedColumns " : "`test`.`t1`.`a`",
      "ProxyDeduplicate " : "false",
      "ProxyQuery" : "SELECT f0 ,f1  FROM proxy_tmpdb.tmptbl  ORDER BY f1  ",
      "ProxySortedColumns " : "count(distinct `test`.`t1`.`b`)",
      "ProxyTmptable" : "CREATE TEMPORARY TABLE proxy_tmpdb.tmptbl (f0 int(11),f1  bigint)",
      "Query" : "set_1624363222_1 set_1624363251_3  , Select DISTINCT `t1`.`a`, `t1`.`b`, `test`.`t1`.`a` from `test`.`t1` where 1 order by 3",
      "QueryMode" : "Hash"
   }
]

Delete/update的下推

指定了shardkey值的单表查询。

-- 网关根据shardkey的值计算出目标set，并将查询直接下推给目标set。
-- 注意：info字段展示了目标set以及下推的查询语句
MySQL [test]> explain delete from t1 where a=1\G
*************************** 1. row ***************************
           id: 1
  select_type: DELETE
        table: t1
   partitions: p1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
         info: set_1624363222_1, explain delete from `test`.`t1` where (a = 1)

MySQL [test]> explain update t1 set b=1 where a=1\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: t1
   partitions: p1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
         info: set_1624363222_1, explain update `test`.`t1` SET b=1 where (a = 1)

没有指定shardkey值的单表查询。

-- 将查询广播给所有set。
-- 注意：info字段展示了目标set以及下推的查询语句
MySQL [test]> explain delete from t1 where 1\G
*************************** 1. row ***************************
           id: 1
  select_type: DELETE
        table: t1
   partitions: p0,p1,p2,p3,p4,p5,p6,p7
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
         info: set_1624363222_1, explain delete  from `test`.`t1` where 1
*************************** 2. row ***************************
           id: 1
  select_type: DELETE
        table: t1
   partitions: p8,p9,p10,p11,p12,p13,p14,p15
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
         info: set_1624363251_3, explain delete from `test`.`t1` where 1

MySQL [test]> explain update t1 set b=1 where 1\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: t1
   partitions: p0,p1,p2,p3,p4,p5,p6,p7
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
         info: set_1624363222_1, explain update `test`.`t1` SET b=1 where 1
*************************** 2. row ***************************
           id: 1
  select_type: UPDATE
        table: t1
   partitions: p8,p9,p10,p11,p12,p13,p14,p15
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
         info: set_1624363251_3, explain update `test`.`t1` SET b=1 where 1

多表更新操作，且表的shardkey相等。

-- 多表更新操作，且shardkey相等时，网关将查询直接下推给后端set执行。
-- 如果shardkey为一个明确的值，则根据shardkey的值计算出目标set；否则，将查询广播给所有set执行。
MySQL [test]> explain update t1, t2 set t1.b=t2.b where t1.a=t2.a and t1.a=202\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: no matching row in const table
         info: set_1624363222_1, explain update `test`.`t1` join `test`.`t2` SET t1.b=t2.b where ((t1.a = t2.a) and (t1.a = 202))

多表更新操作，且表的shardkey不相等，或者包含子查询。网关将构建与更新操作对应的select查询，计算出被更新行的主键、被更新列的新值，再构建相应的update语句发送给set执行。因此，其下推策略同select查询

-- 对于如下查询，网关将构建与之对应的select查询：
--      select * from t1, t2 where t1.a=1 and t2.a=21 and t1.b != t2.b group by t1.a;
-- 然后再根据执行的结果，为t1中需要被更新的每个元组构建如下查询给set执行：
--      update t1 set t1.b=... where t1.a= ...
MySQL [test]> explain update t1, t2 set t1.b=t2.b where t1.a=1 and t2.a=21\G
*************************** 1. row ***************************
trace: [
   {
      "optype" : "TableRename",
      "t1" : "T2e(a,b)",
      "t2" : "T6(a,b)",
      "timecost" : "0.031000"
   },
   {
      "0.OpType         " : "Load table",
      "1.TableName      " : "T6",
      "2.PushedDownCond " : "( /*filter*/((`test`.`t2`.`a`=21)))",
      "3.NumOfRows      " : "0",
      "4.AddedCond      " : "`test`.`t2`.`a` is null",
      "Query" : "set_1624363251_3  , select `a`,`b` from `test`.`t2` t2 where ( /*filter*/((`test`.`t2`.`a`=21))) limit 1000",
      "QueryMode" : "Hash",
      "timecost" : "0.640000"
   },
   {
      "0.OpType         " : "Load table",
      "1.TableName      " : "T2e",
      "2.PushedDownCond " : "(0)",
      "3.NumOfRows      " : "0",
      "4.AddedCond      " : "`test`.`t1`.`a` is null",
      "Query" : "AllSets , select `a`,`b` from `test`.`t1` t1 where (0) limit 1000",
      "QueryMode" : "All",
      "timecost" : "0.544000"
   },
   {
      "Query" : " select `test`.`t1`.`a`,`test`.`t1`.`b`,`test`.`t2`.`b` from `test`.`T2e` `t1` join `test`.`T6` `t2` where 0 group by `test`.`t1`.`a` for update of `test`.`t1`  ",
      "timecost" : "0.001000"
   }
]

USE 语句

语法如下：

use db_name

示例：

MySQL [test]> USE db1; 
MySQL [test]> SELECT COUNT(*) FROM mytable;           
MySQL [test]> USE db2; SELECT COUNT(*) FROM mytable;