## Module 4: Monitoring Index Usage Statistics Proper index design is a crucial prerequisite to maintaining database performance. TiDB v8.0.0 introduces the `information_schema.tidb_index_usage` table and the `sys.schema_unused_indexes` view to provide usage statistics of indexes. This feature helps you assess the efficiency of indexes in the database and optimize the index design. The following supplemental information from the [TiDB SQL Tuning](https://www.pingcap.com/education/course-tidb-sql-tuning/) training is a prerequisite knowledge for working with this module. Show it here for your convenience. ![70%](https://lab-static.pingcap.com/quick-demo/SelCarCost.png) ### Step 1. Verify the indexes efficiency by querying tidb_index_usage + Get the `book_title_uk` index usage baseline by querying `tidb_index_usage` table. Note that statistics are all zeros, because the `EXPLAIN` commands in the previous module did not actually execute the queries. ```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) ``` + Gather the CBO statistics. ```sql ANALYZE TABLE bookshop.books; ``` ```output Query OK, 0 rows affected, 8 warnings (0.69 sec) ``` + Execute a query that might use the index `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) ``` + Check the usage statistics again. Note the `query_total`, `rows_access_total`, and `percentage_access_1_10` will increase. ```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) ``` + Execute another query that does not likely to use the index `book_title_uk`. In this case, `type` is the predicate and `title` is omitted. While the `book_title_uk` index's leading column is `title`, so the CBO won't favor an `book_title_uk` index access path. ```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) ``` + However, for the previous query based on the result set only, we don't know the query was executed via the index access path or the full table scan access path. Let's get the answer from `tidb_index_usage` table. You will find that no counters were incremented, which means the index was not used for the previous query. ```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) ``` + Confirm that the `type = 'Science & Technology'` predicate cannot trigger the index usage. ```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) ``` + At this point, you may wonder that another reason for CBO choosing `TableFullScan` is because our data set are too small (only 100 rows in the table). You are correct because when the data set is not large enough there will be no obvious performance difference choosing between `IndexLookUp` and `TableFullScan`. But the execution plan does reflect CBO's decision. Let's do another experiment to test CBO's decision. + Create a new global unique index, this time it's on the columns combination `(type, title)`, `type` is the leading column. ```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) ``` + Execute the `type = 'Science & Technology'` predicate query again. This time we have two indexes, `book_type_uk` and `book_title_uk`, which access method will the CBO choose? `book_title_uk` is not likely, the choice will be made between the `book_type_uk` and the `TableFullScan`. As we already know, the rows in the result set account for 16% of the total rows. The query selectivity may not be optimal enough for the CBO to favor an index access path. ```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) ``` + Consult the `tidb_index_usage` table again. You will find that no counters were incremented for `book_type_uk`, especially for the `percentage_access_10_20` counter, it's 0. Which means the index was not used for the previous query. However, if there are a lot of rows in `books` table, and the selectivity keeps as 16%, CBO might choose index access path for the same query. ```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) ``` + As a general rule, in TiDB, a selectivity lower than 10% is considered the sweet spot for an index access path, but it is not an absolute rule for every edge case. ### Step 2. Drop unused indexes based on indexes usage statistics + The only purpose of a useless index is to slow down DML operations (INSERT, DELETE, UPDATE, etc.) and some DDL operations. Dropping it is the correct course of action. + Since the `tidb_index_usage` table can tell which index is begin used or not, TiDB provides another interesting in-memory table named `sys.schema_unused_indexes` to tell DBAs and DevOps engineers those useless indexes, and it will be cleared upon cluster restart. It is necessary to ensure that at least one TiDB node has been running for a sufficient amount of time before relying on `sys.schema_unused_indexes`. + Find the unused indexes on `books` table so far. ```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) ``` + Make the unused index invisible to CBO. CBO then won't use such index in the execution plan. In real-world scenarios, you should monitor the system for any potential performance regression. ```sql ALTER TABLE bookshop.books ALTER INDEX book_type_uk INVISIBLE; ``` ```output Query OK, 0 rows affected (0.51 sec) ``` + After completing certain business cycles, if no performance regression is observed related to the invisible index, it should be dropped. ```sql ALTER TABLE bookshop.books DROP INDEX book_type_uk; ``` ```output Query OK, 0 rows affected (0.51 sec) ``` + Now that you understand how to get assistance from the index usage statistics to help you making operation decisions. Please proceed to the next module.