tl; dr:
这篇文章讲述了分布式数据库上的网站如何减少在线服务潜伏期从1.11 s降低到417.7 ms,然后减少到123.6 ms 。我们发现,在整个优化过程中,可以应用一些关于MySQL的教训。但是,当我们优化分布式数据库时,我们需要考虑更多。
OSS Insight网站实时显示GitHub事件的数据更改。它由TiDB Cloud驱动,弹性量表和实时分析的MySQL兼容分布式SQL数据库。
最近,为了节省成本,我们尝试使用较低规格的机器,而不会影响查询效率和用户体验。但是我们的网站和查询响应减慢了。
我们如何在分布式数据库上解决这些问题?我们可以使用我们在mySQL上学习的方法吗?
分析SQL执行计划
为了识别慢速SQL语句,我们使用TIDB Cloud的诊断页面按平均延迟来对SQL查询进行排序。
例如,在API服务器收到请求后,它执行以下SQL语句以获取vscode repository中的问题数:
SELECT
COUNT(DISTINCT number)
FROM github_events
WHERE
repo_id = 41881900 -- vscode
AND type = 'IssuesEvent';
但是,如果开源存储库很大,则此查询可能需要几秒钟或更长时间才能执行。
使用EXPLAIN ANALYZE
来解决查询性能问题
在MySQL中,当我们解决查询性能问题时,我们通常使用EXPLAIN ANALYZE <sql>
语句查看SQL语句的执行计划。我们可以使用执行计划来找到问题。 tidb的作品。
我们执行了EXPLAIN
语句:
EXPLAIN ANALYZE SELECT
COUNT(DISTINCT number)
FROM github_events
WHERE
repo_id = 41881900 -- vscode
AND type = 'IssuesEvent';
结果表明查询需要1.11秒才能执行。
您可以看到TIDB的koude0语句执行结果与MySQL完全不同。 TIDB的执行计划使我们对如何执行此SQL语句有了更清楚的了解。
执行计划显示:
- 此SQL语句分为几个子任务。有些在
root
节点上,另一些则在koude5节点上。 - 查询从
partition:issue_event partition
表中获取的数据。 - 此查询通过索引
index_github_events_on_repo_id(repo_id)
进行了范围扫描。这让查询缩小数据扫描。 此过程仅采用 59 ms。这是多个并发任务的执行时间的总和。 - 除了
IndexRangeScan
外,查询还使用了TableRowIDScan
。 此扫描进行 4.69 S ,多个并发子任务的执行时间总和。
从上面的执行时间中,我们确定查询性能瓶颈在TableRowIDScan
步骤中。
我们重新播放了EXPLAIN ANALYZE
语句,发现查询第二次更快。为什么?
为什么TableRowIDScan
花了这么长时间?
要找到TableRowIDScan
花了这么长时间的原因,我们需要对TIDB的基础存储的基本知识。
在tidb中,表的数据条目和索引存储在键值对中的tikv节点上。
- 对于索引,键是索引值和
row_id
(对于非簇索引)或主键(对于群集索引)的组合。row_id
或主键指示数据存储的位置。 - 对于数据输入,键是表ID和
row_id
或主键的组合。值部分是这一行数据的组合。
此图显示了如何在执行计划中执行IndexLookup
:
在上面的查询中,TIDB使用查询条件repo_id=41881900
来滤除与次级索引index_github_events_on_repo_id
中存储库相关的所有行号row_id
。查询需要数字column
数据,但是辅助索引不提供。因此,tidb必须执行IndexLookup
才能根据获得的row_id
(TableRowIDScan
步骤)在表中找到相应的行。
行可能散布在不同的数据块中,并存储在硬盘上。这会导致TIDB执行大量的I/O操作来读取来自不同数据块甚至不同的机器节点的数据。
为什么EXPLAIN ANALYZE
第二次更快?
在EXPLAIN ANALZYE
的执行结果中,我们看到与TableRowIDScan
步骤对应的“执行信息”列包含此信息:
block: {cache_hit_count: 2755559, read_count: 179510, read_byte: 4.07 GB}
我们认为这与tikv有关。 TIKV从磁盘中读取大量数据块。因为从 disk 中读取的数据块在第一次执行中被缓存在内存中,因此可以直接从内存而不是直接读取275万个数据块从硬盘中检索。这使得TableRowIDScan
的步骤更快,并且查询总体上更快。
但是,我们认为用户查询是随机的。例如,用户可能会从vscode
存储库查找数据,然后转到kubernetes
存储库。 TIKV的内存无法缓存所有驱动器中的所有数据块。因此,这并不能解决我们的问题,但它提醒我们,当我们分析SQL执行效率时,我们需要排除缓存效应。
使用覆盖索引避免执行TableRowIDScan
我们可以避免在IndexLookup
中执行TableRowIDScan
吗?
在MySQL中,覆盖索引可防止索引过滤后的索引查找。我们想将其应用于OSS Insight。在我们的TIDB数据库中,我们尝试创建一个复合索引以实现索引覆盖。
当我们创建具有多个列的复合索引时,我们需要注意列顺序。我们的目标是允许尽可能多的查询使用复合索引,以帮助这些查询尽快缩小数据扫描的范围,并在查询中提供尽可能多的字段。当我们创建一个复合索引时,我们遵循此顺序:
- 具有很高差异化的列,可以用作
WHERE
语句的等效条件,例如repo_id
- 没有高分化但可以用作
WHERE
语句的等效条件,例如type
和action
- 可以用作
WHERE
语句的范围查询条件的列,例如created_at
- 冗余列未用作滤波器条件,但在查询中使用,例如
number
和push_size
我们使用CREATE IDNEX
语句在数据库中创建复合索引:
CREATE INDEX index_github_events_on_repo_id_type_number ON github_events(repo_id, type, number);
当我们创建索引并再次运行SQL语句时,查询速度明显更快。我们通过EXPLAIN ANALYZE
查看了执行计划,发现执行计划变得更简单。 IndexLookup
和TableRowIDScan
步骤消失了。 查询仅处理417.7 ms 。
因此,我们知道我们的查询可以通过在新索引上执行IndexRangeScan
来获取所需的所有数据。该复合索引包括number
字段,因此TIDB无需执行IndexLookup
即可从表中获取数据。这减少了许多I/O操作。
推动计算以进一步减少查询延迟
对于需要获得270,000行数据的查询,417.7 ms的执行时间很短。但是我们可以改善时间吗?
我们认为这取决于将计算和存储层分开的TIDB架构。这与mysql不同。
在tidb中:
-
tidb-server
节点计算数据。它对应于执行计划中的根。 -
tikv-server
节点存储数据。它对应于执行计划中的cop[tikv]
。
通常,SQL语句分为多个步骤,以通过计算和存储节点的合作执行。
当我们在本文中执行SQL语句时,TIDB从tikv-server
获得了github_events
表的数据,并在tidb-server
上执行了计数函数的汇总计算。
SELECT
COUNT(DISTINCT number)
FROM github_events
WHERE
repo_id = 41881900 -- vscode
AND type = 'IssuesEvent';
执行计划表明,当TIDB执行IndexReader
时,tidb-server
需要通过网络读取tikv-server
的270,000行数据。这很耗时。
我们如何避免如此大的网络传输?尽管查询需要获得大量数据,但最终计算结果仅为数字。我们可以在tikv-server
上完成COUNT
的聚合计算,并将结果仅返回到tidb-server
?
tidb通过tikv-server
上的coprocessor实现了这一想法。此优化过程称为计算下降。
执行计划表明我们的SQL查询没有这样做。为什么?我们检查了TIDB文档,并了解到:
通常,带有
DISTINCT
选项的聚合功能在单线程执行模型中在TIDB层中执行。
这意味着我们的SQL语句无法使用计算下降。
SELECT
COUNT(DISTINCT number)
FROM github_events
WHERE
repo_id = 41881900 -- vscode
AND type = 'IssuesEvent';
因此,我们删除了DISTINCT
关键字。
对于github_events
表,一个问题仅生成一个使用IssuesEvent
type和opened
操作的事件。我们可以通过添加action = 'opened'
的状况来获得唯一问题的总数。这样,我们不需要使用DISTINCT
关键字进行重复数据解复。
SELECT
COUNT(number)
FROM github_events
WHERE
repo_id = 41881900 -- vscode
AND type = 'IssuesEvent'
AND action = 'opened';
我们创建的复合索引缺少action
列。这导致查询索引覆盖范围失败。因此,我们创建了一个新的复合索引:
CREATE INDEX index_github_events_on_repo_id_type_action_number ON github_events(repo_id, type, action, number);
创建索引后,我们通过EXPLAIN ANALYZE
语句检查了修改后的SQL语句的执行计划。我们发现:
- 因为我们添加了一个新的过滤器
action='opened'
,所以扫描的行数已从270,000减少到140,000。 -
tikv-server
执行了StreamAgg
操作员,这是COUNT
函数的汇总计算。这表明该计算已被推到TIKV协处理器以执行。 -
tidb-server
仅需要通过网络从tikv-server
获取两行数据。这大大减少了传输的数据量。 - 查询仅花费123.6毫秒。
+-------------------------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_28 | 1.00 | 1 | root | | time:123.6ms, loops:2 | funcs:count(Column#43)->Column#34 | 388 Bytes | N/A |
| └─IndexReader_29 | 1.00 | 2 | root | partition:issues_event | time:123.6ms, loops:2, cop_task: {num: 2, max: 123.5ms, min: 1.5ms, avg: 62.5ms, p95: 123.5ms, max_proc_keys: 131360, p95_proc_keys: 131360, tot_proc: 115ms, tot_wait: 1ms, rpc_num: 2, rpc_time: 125ms, copr_cache_hit_ratio: 0.50, distsql_concurrency: 15} | index:StreamAgg_11 | 590 Bytes | N/A |
| └─StreamAgg_11 | 1.00 | 2 | cop[tikv] | | tikv_task:{proc max:116ms, min:8ms, avg: 62ms, p80:116ms, p95:116ms, iters:139, tasks:2}, scan_detail: {total_process_keys: 131360, total_process_keys_size: 23603556, total_keys: 131564, get_snapshot_time: 1ms, rocksdb: {delete_skipped_count: 320, key_skipped_count: 131883, block: {cache_hit_count: 307, read_count: 1, read_byte: 63.9 KB, read_time: 60.2µs}}} | funcs:count(gharchive_dev.github_events.number)->Column#43 | N/A | N/A |
| └─IndexRangeScan_15 | 7.00 | 141179 | cop[tikv] | table:github_events, index:index_ge_on_repo_id_type_action_created_at_number(repo_id, type, action, created_at, number) | tikv_task:{proc max:116ms, min:8ms, avg: 62ms, p80:116ms, p95:116ms, iters:139, tasks:2} | range:[41881900 "IssuesEvent" "opened",41881900 "IssuesEvent" "opened"], keep order:false | N/A | N/A |
+-------------------------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+-----------+------+
将我们学到的知识应用于其他查询
通过我们的分析和优化,查询延迟显着降低:
1.11Sâ417.7ms 123.6 ms
我们将我们学到的知识应用于其他查询,并在github_events
表中创建了以下复合索引:
index_ge_on_repo_id_type_action_pr_merged_created_at_add_del
index_ge_on_repo_id_type_action_created_at_number_pdsize_psize
index_ge_on_repo_id_type_action_created_at_actor_login
index_ge_on_creator_id_type_action_merged_created_at_add_del
index_ge_on_actor_id_type_action_created_at_repo_id_commits
这些复合索引涵盖了OSS Insight网站上的存储库分析和个人分析页面中的20多个分析查询。这提高了我们网站的整体加载速度。
我们在MySQL上学习的一些课程可以在整个优化过程中应用。,但是当我们优化A 分布式数据库中的查询性能时,我们需要考虑更多。我们还建议您在TIDB文档中阅读Performance Tuning。这将为您提供更专业,更全面的性能优化指南。