全部产品
分布式关系型数据库 DRDS

SQL 优化基本概念

更新时间:2017-08-14 14:42:15   分享:   

DRDS 是一个高效、稳定的分布式关系数据库服务,处理的是分布式关系运算。DRDS 对 SQL 的优化方法与单机关系数据库(例如 MySQL)有所不同,侧重考虑分布式环境中的网络 IO 开销,会尽量将 SQL 中的运算下推到底层各个分库(例如 RDS/MySQL)执行,从而减少网络 IO 开销、提升 SQL 执行效率。

DRDS 提供了一些指令来获取 SQL 的执行信息、辅助 SQL 的优化,例如获取 SQL 执行计划的 EXPLAIN 系列指令、获取 SQL 执行过程和开销的 TRACE 指令等。本文档介绍 DRDS 中 SQL 优化相关的基本概念和常用指令。

执行计划

为了访问数据而产生的一组有序的操作步骤集合,被称为 SQL 执行计划(简称执行计划)。在 DRDS 中,执行计划分为两个层次:DRDS 层的执行计划与 RDS/MySQL 层的执行计划。对执行计划的分析是进行 SQL 优化的有效方法,可以了解 DRDS 或 RDS/MySQL 是否对 SQL 语句生成了最优化的执行计划,是否有优化的空间等,从而为 SQL 优化提供重要的参考信息。

在 SQL 语句执行期间,DRDS 优化器会根据 SQL 语句和相关表的基本信息,判断该 SQL 语句应该在哪些分库上执行,决定在分库上执行的具体 SQL 语句形式,采用何种执行策略、数据合并与计算策略等。这个过程会尽可能达到优化 SQL 语句执行的目的,并产生 DRDS 层的执行计划。而 RDS/MySQL 层的执行计划就是原生的 MySQL 执行计划。

DRDS 提供了一组 EXPLAIN 指令来查看不同层面或不同详尽程度的执行计划。

表 1 是 DRDS 中 EXPLAIN 指令的简要说明,详细信息请参考 DRDS 控制指令

控制指令 说明 示例
EXPLAIN { SQL } 查看 DRDS 层 SQL 语句的概要执行计划,包括执行的分库、物理语句和整体参数。 EXPLAIN SELECT * FROM test
EXPLAIN DETAIL { SQL } 查看 DRDS 层 SQL 语句的详细执行计划,包括执行语句类型、并发度、返回字段信息、物理表和库分组等。 EXPLAIN DETAIL SELECT * FROM test
EXPLAIN EXECUTE { SQL } 查看底层 RDS/MySQL 的执行计划,等同于 MySQL 的 EXPLAIN 语句。 EXPLAIN EXECUTE SELECT * FROM test
表1 EXPLAIN 指令

DRDS 层执行计划

DRDS 层执行计划的返回结果中,字段含义如表2所示:

字段 说明
GROUP_NAME DRDS 分库的名字,可以根据后缀识别出是哪个分库,其值与 SHOW NODE 指令的结果一致。
SQL 在该分库上执行的 SQL 语句。
PARAMS 当 DRDS 使用 Prepare 协议与 MySQL 通信时,SQL 语句的参数列表。
表2 执行计划的字段含义

其中 SQL 字段的内容有两种形式:

1、 如果 SQL 语句不包含以下部分,则以 SQL 语句的形式显示执行计划:

  • 涉及多个分库的聚合函数;
  • 涉及多个分片的分布式 Join;
  • 复杂子查询。

例如:

  1. mysql> EXPLAIN SELECT * FROM test;
  2. +----------------------------------------------+--------------------------------------------+--------+
  3. | GROUP_NAME | SQL | PARAMS |
  4. +----------------------------------------------+--------------------------------------------+--------+
  5. | TESTDB_1478746391548CDTCTESTDB_OXGJ_0000_RDS | select `test`.`c1`,`test`.`c2` from `test` | {} |
  6. | TESTDB_1478746391548CDTCTESTDB_OXGJ_0001_RDS | select `test`.`c1`,`test`.`c2` from `test` | {} |
  7. | TESTDB_1478746391548CDTCTESTDB_OXGJ_0002_RDS | select `test`.`c1`,`test`.`c2` from `test` | {} |
  8. | TESTDB_1478746391548CDTCTESTDB_OXGJ_0003_RDS | select `test`.`c1`,`test`.`c2` from `test` | {} |
  9. | TESTDB_1478746391548CDTCTESTDB_OXGJ_0004_RDS | select `test`.`c1`,`test`.`c2` from `test` | {} |
  10. | TESTDB_1478746391548CDTCTESTDB_OXGJ_0005_RDS | select `test`.`c1`,`test`.`c2` from `test` | {} |
  11. | TESTDB_1478746391548CDTCTESTDB_OXGJ_0006_RDS | select `test`.`c1`,`test`.`c2` from `test` | {} |
  12. | TESTDB_1478746391548CDTCTESTDB_OXGJ_0007_RDS | select `test`.`c1`,`test`.`c2` from `test` | {} |
  13. +----------------------------------------------+--------------------------------------------+--------+
  14. 8 rows in set (0.04 sec)

GROUP_NAME 字段中显示的 GROUP 名称可以在 SHOW NODE 的结果中找到:

  1. mysql> SHOW NODE;
  2. +----+----------------------------------------------+-------------------+------------------+---------------------+--------------------+
  3. | ID | NAME | MASTER_READ_COUNT | SLAVE_READ_COUNT | MASTER_READ_PERCENT | SLAVE_READ_PERCENT |
  4. +----+----------------------------------------------+-------------------+------------------+---------------------+--------------------+
  5. | 0 | TESTDB_1478746391548CDTCTESTDB_OXGJ_0000_RDS | 69 | 0 | 100% | 0% |
  6. | 1 | TESTDB_1478746391548CDTCTESTDB_OXGJ_0001_RDS | 45 | 0 | 100% | 0% |
  7. | 2 | TESTDB_1478746391548CDTCTESTDB_OXGJ_0002_RDS | 30 | 0 | 100% | 0% |
  8. | 3 | TESTDB_1478746391548CDTCTESTDB_OXGJ_0003_RDS | 29 | 0 | 100% | 0% |
  9. | 4 | TESTDB_1478746391548CDTCTESTDB_OXGJ_0004_RDS | 11 | 0 | 100% | 0% |
  10. | 5 | TESTDB_1478746391548CDTCTESTDB_OXGJ_0005_RDS | 1 | 0 | 100% | 0% |
  11. | 6 | TESTDB_1478746391548CDTCTESTDB_OXGJ_0006_RDS | 8 | 0 | 100% | 0% |
  12. | 7 | TESTDB_1478746391548CDTCTESTDB_OXGJ_0007_RDS | 50 | 0 | 100% | 0% |
  13. +----+----------------------------------------------+-------------------+------------------+---------------------+--------------------+
  14. 8 rows in set (0.10 sec)

2、无法使用 SQL 语句表示的执行计划,DRDS 使用自定义格式的执行计划来表示。

例如:

  1. mysql> EXPLAIN DETAIL SELECT COUNT(*) FROM test;
  2. +------------------------------------------------+----------------------------------+--------+
  3. | GROUP_NAME | SQL | PARAMS |
  4. +------------------------------------------------+----------------------------------+--------+
  5. | TEST_DB_1478746391548CDTCTEST_DB_OXGJ_0000_RDS | Merge as test
  6. queryConcurrency:GROUP_CONCURRENT
  7. columns:[count(*)]
  8. executeOn: TEST_DB_1478746391548CDTCTEST_DB_OXGJ_0000_RDS
  9. Query from test as test
  10. queryConcurrency:SEQUENTIAL
  11. columns:[count(*)]
  12. tableName:test
  13. executeOn: TEST_DB_1478746391548CDTCTEST_DB_OXGJ_0000_RDS
  14. Query from test as test
  15. queryConcurrency:SEQUENTIAL
  16. columns:[count(*)]
  17. tableName:test
  18. executeOn: TEST_DB_1478746391548CDTCTEST_DB_OXGJ_0001_RDS
  19. ... ...
  20. Query from test as test
  21. queryConcurrency:SEQUENTIAL
  22. columns:[count(*)]
  23. tableName:test
  24. executeOn: TEST_DB_1478746391548CDTCTEST_DB_OXGJ_0007_RDS
  25. | NULL |
  26. +------------------------------------------------+----------------------------------+--------+
  27. 1 row in set (0.00 sec)

其中,SQL 字段内容中的 executeOn 表示下推的 SQL 语句在哪个分库上执行,分库执行后返回的结果最终由 DRDS 进行合并。

RDS/MySQL 层执行计划

RDS/MySQL 层执行计划的结果与原生 MySQL 执行计划一致,请参考 MySQL 官方文档

一个 DRDS 逻辑表可能由多个分布在不同分库上的分片所组成,所以查看 RDS/MySQL 层执行计划也有多种方法。

1、查看一个 RDS/MySQL 分片的执行计划。

如果查询条件中带有拆分键,则直接使用 EXPLAIN EXECUTE 指令来查看对应分片上的执行计划。例如:

  1. mysql> EXPLAIN EXECUTE SELECT * FROM test WHERE c1 = 1;
  2. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  4. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
  5. | 1 | SIMPLE | test | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
  6. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
  7. 1 row in set (0.04 sec)

注意:如果 SQL 语句出现了跨分片的情况(例如 SQL 语句的条件中没有带拆分键),则 EXPLAIN EXECUTE 会随机返回一个 RDS/MySQL 分片上的执行计划。

如果要查看一条 SQL 语句在指定分片上的执行计划,可以使用 Hint 的方式来实现。例如:

  1. mysql> /!TDDL:node='TESTDB_1478746391548CDTCTESTDB_OXGJ_0000_RDS'*/EXPLAIN SELECT * FROM test;
  2. +----+-------------+-------+------+---------------+-------+---------+-------+------+-------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  4. +----+-------------+-------+------+---------------+-------+---------+-------+------+-------+
  5. | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
  6. +----+-------------+-------+------+---------------+-------+---------+-------+------+-------+
  7. 1 row in set (0.04 sec)

2、查看所有 RDS/MySQL 分片的执行计划:

如果确实需要查看 SQL 语句在所有分片上的执行计划,可以利用 SCAN Hint 来实现:

  1. mysql> /!TDDL:scan='test'*/EXPLAIN SELECT * FROM test;
  2. +----+-------------+-------+------+---------------+-------+---------+-------+------+-------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  4. +----+-------------+-------+------+---------------+-------+---------+-------+------+-------+
  5. | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
  6. | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 3 | NULL |
  7. +----+-------------+-------+------+---------------+-------+---------+-------+------+-------+
  8. 2 rows in set (0.08 sec)

注意:

  1. 使用 Hint 方式时,除了分库分表情况下的表名替换,DRDS 不会对 SQL 语句做其它处理,会直接将逻辑 SQL 语句发送到 RDS/MySQL 上执行,结果也不会做任何处理。

  2. 通过 EXPLAIN 获取的执行计划是静态分析产生的,并没有真正在数据库中执行。

TRACE 指令

DRDS 中的 TRACE 指令可以跟踪 SQL 的执行过程和各个阶段的执行开销,与执行计划相结合,更有助于对 SQL 进行优化。

TRACE 指令包含两条相关的指令:TRACE 和 SHOW TRACE,需要在一起配合使用。详细的用法信息请参考 DRDS 控制指令

本文导读目录
本文导读目录
以上内容是否对您有帮助?