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

文章内容：

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发挥出最佳的效果，满足各种场景下的海量数据处理需求。当然，这些只是一些基本的原则和方法，并不涵盖所有的情况和细节，用户还需要根据自己的实际业务需求和数据特点来进行更深入和细致的分析和优化。