如何维护好 TiDB 的三颗仙丹——索引、SQL 和 IO

作者： tidb 狂热爱好者原文来源：https://tidb.net/blog/4c1d1fd6

文章内容：

TiDB 是一个高度兼容 MySQL5.7 协议的分布式数据库，它具有水平扩展、高可用、强一致性等特点，适用于各种场景下的海量数据处理。但是，要想让 TiDB 发挥出最佳的性能，也需要对其进行合理的维护和优化。本文将介绍如何利用索引、SQL 和 IO 三个方面来提升 TiDB 的查询效率和稳定性。

7.0 之前的 tidb 因为开发的限制并没有很好的手段控制慢 sql。很容易一个业务慢 sql 上来就直接把生产库打死了。这个时候需要我们尽量好的设计好监控。对数据库负责。

1. 每日巡检热力图发现黄点就叫开发整改。比如下图很明显的顺序写热点

2. 写自动化脚本巡检生产系统去查询哪些没有走索引并总体耗时最多的 sql

SELECT FLOOR(UNIX_TIMESTAMP(MIN(summary_begin_time))) AS agg_begin_time, FLOOR(UNIX_TIMESTAMP(MAX(summary_end_time))) AS agg_end_time, ANY_VALUE(digest_text) AS agg_digest_text, ANY_VALUE(digest) AS agg_digest, SUM(exec_count) AS agg_exec_count, SUM(sum_latency) AS agg_sum_latency, MAX(max_latency) AS agg_max_latency, MIN(min_latency) AS agg_min_latency, CAST( SUM(exec_count * avg_latency) / SUM(exec_count) AS SIGNED ) AS agg_avg_latency, CAST( SUM(exec_count * avg_mem) / SUM(exec_count) AS SIGNED ) AS agg_avg_mem, MAX(max_mem) AS agg_max_mem, ANY_VALUE(schema_name) AS agg_schema_name, ANY_VALUE(plan_digest) AS agg_plan_digest,query_sample_text,index_names FROM `INFORMATION_SCHEMA`.`CLUSTER_STATEMENTS_SUMMARY_HISTORY` where index_names is null and query_sample_text >” GROUP BY schema_name, digest ORDER BY agg_sum_latency DESC limit 10;

这个 sql 能查出最慢无索引 sql

3. 需要找出系统中单个 sql 很快但执行次数过多总计最耗时间的 sql。让开发放缓存。

SELECT FLOOR(UNIX_TIMESTAMP(MIN(summary_begin_time))) AS agg_begin_time, FLOOR(UNIX_TIMESTAMP(MAX(summary_end_time))) AS agg_end_time, ANY_VALUE(digest_text) AS agg_digest_text, ANY_VALUE(digest) AS agg_digest, SUM(exec_count) AS agg_exec_count, SUM(sum_latency) AS agg_sum_latency, MAX(max_latency) AS agg_max_latency, MIN(min_latency) AS agg_min_latency, CAST( SUM(exec_count * avg_latency) / SUM(exec_count) AS SIGNED ) AS agg_avg_latency, CAST( SUM(exec_count * avg_mem) / SUM(exec_count) AS SIGNED ) AS agg_avg_mem, MAX(max_mem) AS agg_max_mem, ANY_VALUE(schema_name) AS agg_schema_name, ANY_VALUE(plan_digest) AS agg_plan_digest,query_sample_text,index_names FROM `INFORMATION_SCHEMA`.`CLUSTER_STATEMENTS_SUMMARY_HISTORY` GROUP BY schema_name, digest ORDER BY agg_sum_latency DESC limit 10;

4. 每日找出前 1 个小时的慢 sql 让开发整改一次不用多给最慢的一个 sql 就行多了人家也改不完

select query_time,query from information_schema.CLUSTER_SLOW_QUERY where is_internal = false and Time > date_add(now(),interval -1 hour) and user<>‘root’ and query_time > 7 order by query_time desc limit 1;

索引

索引是数据库中用于加速数据检索的数据结构，它可以让数据库系统不必扫描全表，而是直接定位到满足条件的记录，从而大大减少查询时间和资源消耗。TiDB 支持主键索引、唯一索引、普通索引和前缀索引等多种类型的索引，用户可以根据实际业务需求来创建合适的索引。

但是，并不是所有的字段都适合建立索引，也不是索引越多越好。过多或不合理的索引会增加数据维护的开销，降低数据更新和插入的速度，占用更多的存储空间，甚至导致查询优化器选择错误的执行计划。因此，在创建索引时，需要遵循以下原则：

• 选择区分度高的字段作为索引列，即该字段的不同值占总记录数的比例较高，这样可以过滤掉更多无关的记录。

• 尽量选择较短的字段作为索引列，或者使用前缀索引来减少索引长度，这样可以节省存储空间和 IO 开销。

• 尽量避免在频繁更新或插入的字段上建立索引，因为每次数据变动都会导致索引重建，影响性能。

• 尽量避免在查询条件中使用函数或表达式来处理索引列，因为这样会使得索引失效，无法利用索引进行查询优化。

• 尽量避免在查询条件中使用范围查询或模糊查询（如 LIKE ‘%xxx%’）来处理多列联合索引的前缀列，因为这样会使得联合索引失效，无法利用后续列的索引信息。

除了创建合理的索引外，还需要定期更新和维护索引的统计信息。统计信息是查询优化器选择执行计划时所依赖的重要依据，它包括表中记录数、各个列的基数、各个列值的分布情况等。如果统计信息不准确或过时，会导致查询优化器选择错误或低效的执行计划，从而影响查询性能。TiDB 提供了 ANALYZE TABLE 命令来收集表和索引的统计信息，并将其存储在 mysql.stats_meta 和 mysql.stats_histograms 系统表中。用户可以通过 SHOW STATS_HEALTHY 命令来查看表和索引的健康度（health），健康度越低表示统计信息越不准确，需要及时更新。用户可以通过 ANALYZE TABLE 命令来手动更新统计信息，也可以通过设置 tidb_auto_analyze_ratio 和 tidb_auto_analyze_start_time 等参数来开启自动更新统计信息的功能。

SQL

SQL 是用户与 TiDB 进行交互的主要方式，用户通过编写 SQL 语句来实现数据的查询、更新、删除等操作。SQL 语句的编写质量直接影响到 TiDB 的查询效率和稳定性，因此，需要遵循以下原则来编写高效的 SQL 语句：

• 尽量使用预编译语句或参数化语句，而不是拼接字符串来生成动态 SQL 语句，这样可以避免 SQL 注入攻击，提高 SQL 执行效率，减少 SQL 解析开销。

• 尽量避免使用 SELECT * 来查询所有列，而是只查询需要的列，这样可以减少网络传输和内存占用，提高查询速度。

• 尽量避免使用子查询，而是使用 JOIN 或 EXISTS 等关联查询方式，这样可以减少查询层次，简化查询逻辑，提高查询效率。

• 尽量避免使用 UNION ALL 来合并多个子查询的结果，而是使用 JOIN 或 EXISTS 等关联查询方式，这样可以减少数据冗余，提高查询效率。

• 尽量避免使用 ORDER BY、GROUP BY、DISTINCT 等排序和去重操作，或者在使用时尽量利用索引来进行排序和去重，这样可以减少排序和去重的开销，提高查询效率。

• 尽量避免使用 HAVING 子句来过滤分组后的结果，而是在 WHERE 子句中进行过滤，这样可以减少分组后的数据量，提高查询效率。

• 尽量避免使用 LIMIT 子句来分页查询数据，而是使用索引列和条件过滤来实现分页功能，这样可以减少扫描的数据量，提高查询效率。

除了编写高效的 SQL 语句外，还需要定期分析和优化 SQL 语句的执行计划。执行计划是 TiDB 对 SQL 语句进行逻辑优化和物理优化后生成的可执行的操作序列，它包括了各个操作的类型、顺序、参数、代价等信息。用户可以通过 EXPLAIN 和 EXPLAIN ANALYZE 命令来查看 SQL 语句的执行计划，并根据执行计划中的信息来判断 SQL 语句是否存在性能问题。例如：

• 是否有全表扫描（TableFullScan）或全索引扫描（IndexFullScan）的操作，如果有，说明没有利用索引进行过滤，需要检查索引是否存在或有效。

• 是否有排序（Sort）或去重（HashAgg）的操作，如果有，说明没有利用索引进行排序或去重，需要检查是否有合适的索引或是否可以优化 SQL 语句。

• 是否有哈希连接（HashJoin）或嵌套循环连接（IndexJoin）的操作，如果有，说明没有利用索引进行连接优化，需要检查连接条件是否有索引或是否可以优化 SQL 语句。

• 是否有表读取（TableReader）或索引读取（IndexReader）的操作，如果有，说明没有利用 TiKV Coprocessor 进行下推计算，需要检查是否开启了下推开关或是否可以优化 SQL 语句。

IO

IO 是指 TiDB 与 TiKV 之间进行数据读写时所涉及的磁盘和网络输入输出。IO 性能直接影响 TiDB 的查询效率和稳定性，因此，需要监控和优化 IO 性能，避免出现 IO 瓶颈。TiDB 提供了 TiDB Dashboard 和 Grafana 两个工具来监控 TiDB 和 TiKV 的各项指标，包括 CPU、内存、磁盘、网络等资源的使用情况。用户可以通过这些工具来分析 IO 性能的状况，并根据分析结果来进行优化。例如：

• 如果发现 TiDB 或 TiKV 的 CPU 使用率过高，说明 CPU 资源不足，可能会导致查询延迟或超时，需要增加 CPU 核数或扩容 TiDB 或 TiKV 节点。

• 如果发现 TiDB 或 TiKV 的内存使用率过高，说明内存资源不足，可能会导致查询失败或 OOM（Out of Memory），需要增加内存大小或扩容 TiDB 或 TiKV 节点。

• 如果发现 TiDB 或 TiKV 的磁盘使用率过高，说明磁盘空间不足，可能会导致数据写入失败或磁盘损坏，需要增加磁盘空间或扩容 TiDB 或 TiKV 节点。

• 如果发现 TiDB 或 TiKV 的磁盘读写速度过低，说明磁盘性能不佳，可能会导致查询慢或卡顿，需要使用更高性能的磁盘或优化磁盘配置。

• 如果发现 TiDB 或 TiKV 的网络带宽使用率过高，说明网络资源不足，可能会导致查询超时或失败，需要增加网络带宽或优化网络配置。

之前群内网友用读写只有 100m 的网络 ssd 搭建 tikv。他用了 17 台机器搭建系统。既浪费了公司的资金也无法发挥 tidb 的性能。

这么慢的机器去做数据分析还不如用这些钱买一台本地 ssd 盘跑 tidb 17 个小矮人的真实案例。

如果你的磁盘性能实在不行。你可以从表结构入手，想尽办法从表结构上去节约 io

1. 用分区表只查询需要的内容。电力公司一月的电力分钟级别采集信息有 1200t。把数据按天按划分一天有 40t。有 10 个城市按城市划分一个城市 4tb，按业务按日期做 tidb 分区的话。就能在比较慢的磁盘上查询了。

2. 紧扣表结构拒绝不合理的表结构。一个开发大爷用 blob 存 16m json 数据。可以用审核工具拒绝这些表结构上线。varchar 不过 255 坚决拒绝 text 和 blob。varchar 可以用二进制 varbinary 保存二进制省空间。

3. 做定时任务去删除热点表的数据，维持最小的大小。比如电表的账单信息，京东的订单都只保留最近一个月。历史信息放历史库查询。这样热点表就小了

总结

本文介绍了如何利用索引、SQL 和 IO 三个方面来维护和优化 TiDB 的性能和稳定性。通过创建合理的索引、编写高效的 SQL 语句、监控和优化 IO 性能，可以让 TiDB 发挥出最佳的效果，满足各种场景下的海量数据处理需求。当然，这些只是一些基本的原则和方法，并不涵盖所有的情况和细节，用户还需要根据自己的实际业务需求和数据特点来进行更深入和细致的分析和优化。