## 模块 4: 监控索引使用情况统计 适当的索引设计是维护数据库性能的关键前提。TiDB v8.0.0 引入了 `information_schema.tidb_index_usage` 表和 `sys.schema_unused_indexes` 视图，以提供索引的使用情况统计信息。此功能有助于您评估数据库中索引的效率并优化索引设计。 以下来自 [TiDB SQL Tuning](https://www.pingcap.com/education/course-tidb-sql-tuning/) 培训的补充信息是使用此模块的前提知识。为方便起见，在此显示。 ![70%](https://lab-static.pingcap.com/quick-demo/SelCarCost.png) ### 步骤 1. 通过查询 `tidb_index_usage` 验证索引效率 + 通过查询 `tidb_index_usage` 表获取 `book_title_uk` 索引使用基线。请注意，统计信息全为零，因为前一个模块中的 `EXPLAIN` 命令实际上并未执行查询。 ```sql SELECT table_schema, table_name, index_name, query_total, rows_access_total, last_access_time, percentage_access_0, percentage_access_0_1, percentage_access_1_10, percentage_access_10_20, percentage_access_20_50, percentage_access_50_100, percentage_access_100 FROM information_schema.tidb_index_usage WHERE index_name='book_title_uk'\G ``` ```output *************************** 1. row *************************** table_schema: bookshop table_name: books index_name: book_title_uk query_total: 0 rows_access_total: 0 last_access_time: NULL percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 0 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 1 row in set (0.01 sec) ``` + 收集 CBO 统计信息。 ```sql ANALYZE TABLE bookshop.books; ``` ```output Query OK, 0 rows affected, 8 warnings (0.69 sec) ``` + 执行可能使用 `book_title_uk` 索引的查询。 ```sql SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology' AND title = 'Play That Funky Music'; ``` ```output +-------+--------+ | stock | price | +-------+--------+ | 147 | 185.76 | +-------+--------+ 1 row in set (0.00 sec) ``` + 再次检查使用统计信息。请注意 `query_total`、`rows_access_total` 和 `percentage_access_1_10` 将增加。 ```sql SELECT table_schema, table_name, index_name, query_total, rows_access_total, last_access_time, percentage_access_0, percentage_access_0_1, percentage_access_1_10, percentage_access_10_20, percentage_access_20_50, percentage_access_50_100, percentage_access_100 FROM information_schema.tidb_index_usage WHERE index_name='book_title_uk'\G ``` ```output *************************** 1. row *************************** table_schema: bookshop table_name: books index_name: book_title_uk query_total: 1 rows_access_total: 1 last_access_time: 2025-03-09 05:33:47 percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 1 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 1 row in set (0.01 sec) ``` + 执行另一个不太可能使用 `book_title_uk` 索引的查询。在这种情况下，`type` 是谓词，而 `title` 被省略。由于 `book_title_uk` 索引的前导列是 `title`，因此 CBO 不会选择 `book_title_uk` 索引访问路径。 ```sql SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology'; ``` ```output +------------+--------+ | stock | price | +------------+--------+ | 2147483647 | 101.02 | | 30 | 195.32 | | 141 | 13.20 | | 138 | 94.34 | | 38 | 138.97 | | 66 | 13.00 | | 147 | 185.76 | | 6 | 109.95 | | 20 | 75.24 | | 990 | 197.15 | | 4981 | 133.59 | | 9888998 | 106.51 | | 288 | 197.48 | | 30000 | 56.56 | | 5555555 | 104.12 | | 6 | 187.77 | +------------+--------+ 16 rows in set (0.02 sec) ``` + 然而，仅根据结果集，我们无法知道前一个查询是通过索引访问路径执行的还是通过全表扫描访问路径执行的。让我们从 `tidb_index_usage` 表中获取答案。您会发现没有计数器增加，这意味着前一个查询未使用索引。 ```sql SELECT table_schema, table_name, index_name, query_total, rows_access_total, last_access_time, percentage_access_0, percentage_access_0_1, percentage_access_1_10, percentage_access_10_20, percentage_access_20_50, percentage_access_50_100, percentage_access_100 FROM information_schema.tidb_index_usage WHERE index_name='book_title_uk'\G ``` ```output *************************** 1. row *************************** table_schema: bookshop table_name: books index_name: book_title_uk query_total: 1 rows_access_total: 1 last_access_time: 2025-03-09 05:33:47 percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 1 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 1 row in set (0.01 sec) ``` + 确认 `type = 'Science & Technology'` 谓词无法触发索引使用。 ```sql EXPLAIN ANALYZE SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology'; ``` ```output +---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+ | TableReader_11 | 16.00 | 16 | root | partition:all | time:1.5ms, loops:2, RU:4.149196, cop_task: {num: 8, max: 1.33ms, min: 955.8µs, avg: 1.17ms, p95: 1.33ms, max_proc_keys: 13, p95_proc_keys: 13, tot_proc: 662.4µs, tot_wait: 4.26ms, copr_cache_hit_ratio: 0.00, build_task_duration: 23.7µs, max_distsql_concurrency: 8}, rpc_info:{Cop:{num_rpc:8, total_time:9.31ms}} | data:Projection_5 | 994 Bytes | N/A | | └─Projection_5 | 16.00 | 16 | cop[tikv] | | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:8, tasks:8}, scan_detail: {total_process_keys: 100, total_process_keys_size: 8414, total_keys: 108, get_snapshot_time: 4.02ms, rocksdb: {key_skipped_count: 100, block: {}}}, time_detail: {total_process_time: 662.4µs, total_wait_time: 4.26ms, tikv_wall_time: 6.22ms} | bookshop.books.stock, bookshop.books.price | N/A | N/A | | └─Selection_10 | 16.00 | 16 | cop[tikv] | | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:8, tasks:8} | eq(bookshop.books.type, "Science & Technology") | N/A | N/A | | └─TableFullScan_9 | 100.00 | 100 | cop[tikv] | table:books | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:8, tasks:8} | keep order:false | N/A | N/A | +---------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+ 4 rows in set (0.00 sec) ``` + 此时，您可能会想，CBO 选择 `TableFullScan` 的另一个原因是因为我们的数据集太小（表中只有 100 行）。您是对的，因为当数据集不够大时，选择 `IndexLookUp` 和 `TableFullScan` 之间不会有明显的性能差异。但执行计划确实反映了 CBO 的决策。让我们做另一个实验来测试 CBO 的决策。 + 创建一个新的全局唯一索引，这次是在列组合 `(type, title)` 上，`type` 是前导列。 ```sql CREATE UNIQUE INDEX book_type_uk ON bookshop.books (type, title) GLOBAL; ANALYZE TABLE bookshop.books; ``` ```output Query OK, 0 rows affected (6.02 sec) Query OK, 0 rows affected, 8 warnings (0.82 sec) ``` + 再次执行 `type = 'Science & Technology'` 谓词查询。这次我们有两个索引，`book_type_uk` 和 `book_title_uk`，CBO 会选择哪种访问方法？`book_title_uk` 不太可能，选择将在 `book_type_uk` 和 `TableFullScan` 之间做出。正如我们已经知道的，结果集中的行占总行数的 16%。查询选择性可能不足以使 CBO 偏向索引访问路径。 ```sql SELECT stock, price FROM bookshop.books WHERE type = 'Science & Technology'; ``` ```output +------------+--------+ | stock | price | +------------+--------+ | 30000 | 56.56 | | 5555555 | 104.12 | | 6 | 187.77 | | 2147483647 | 101.02 | | 30 | 195.32 | | 141 | 13.20 | | 138 | 94.34 | | 38 | 138.97 | | 990 | 197.15 | | 4981 | 133.59 | | 9888998 | 106.51 | | 66 | 13.00 | | 147 | 185.76 | | 6 | 109.95 | | 20 | 75.24 | | 288 | 197.48 | +------------+--------+ 16 rows in set (0.00 sec) ``` + 再次查询 `tidb_index_usage` 表。您会发现 `book_type_uk` 没有计数器增加，特别是 `percentage_access_10_20` 计数器为 0，这意味着前一个查询未使用索引。然而，如果 `books` 表中有很多行，并且选择性保持为 16%，CBO 可能会为相同的查询选择索引访问路径。 ```sql SELECT table_schema, table_name, index_name, query_total, rows_access_total, last_access_time, percentage_access_0, percentage_access_0_1, percentage_access_1_10, percentage_access_10_20, percentage_access_20_50, percentage_access_50_100, percentage_access_100 FROM information_schema.tidb_index_usage WHERE index_name in ('book_title_uk','book_type_uk')\G ``` ```output *************************** 1. row *************************** table_schema: bookshop table_name: books index_name: book_title_uk query_total: 1 rows_access_total: 1 last_access_time: 2025-03-09 05:33:47 percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 1 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 *************************** 2. row *************************** table_schema: bookshop table_name: books index_name: book_type_uk query_total: 0 rows_access_total: 0 last_access_time: NULL percentage_access_0: 0 percentage_access_0_1: 0 percentage_access_1_10: 0 percentage_access_10_20: 0 percentage_access_20_50: 0 percentage_access_50_100: 0 percentage_access_100: 0 2 rows in set (0.01 sec) ``` + 在 TiDB 中，一般规则是选择性低于 10% 被认为是索引访问路径的最佳选择，但这并不是每个边缘案例的绝对规则。 ### 步骤 2. 根据索引使用情况统计删除未使用的索引 + 无用索引的唯一目的是减慢 DML 操作（INSERT、DELETE、UPDATE 等）和某些 DDL 操作。删除它是正确的做法。 + 由于 `tidb_index_usage` 表可以告诉哪些索引正在使用或未使用，TiDB 提供了另一个有趣的内存表 `sys.schema_unused_indexes`，它可以告诉 DBA 和 DevOps 工程师那些无用的索引，并且在集群重启时会被清除。在依赖 `sys.schema_unused_indexes` 之前，必须确保至少有一个 TiDB 节点已运行足够长的时间。 + 查找 `books` 表上到目前为止未使用的索引。 ```sql SELECT * FROM sys.schema_unused_indexes WHERE object_schema='bookshop' AND object_name='books'; ``` ```output +---------------+-------------+--------------+ | object_schema | object_name | index_name | +---------------+-------------+--------------+ | bookshop | books | book_type_uk | +---------------+-------------+--------------+ 1 row in set (0.01 sec) ``` + 使未使用的索引对 CBO 不可见。然后 CBO 不会在执行计划中使用此类索引。在实际场景中，您应该监控系统是否存在任何潜在的性能回归。 ```sql ALTER TABLE bookshop.books ALTER INDEX book_type_uk INVISIBLE; ``` ```output Query OK, 0 rows affected (0.51 sec) ``` + 完成某些业务周期后，如果未观察到与不可见索引相关的性能回归，则应将其删除。 ```sql ALTER TABLE bookshop.books DROP INDEX book_type_uk; ``` ```output Query OK, 0 rows affected (0.51 sec) ``` + 现在您了解了如何从索引使用统计信息中获得帮助，以帮助您做出操作决策。请继续下一个模块。