TiDB Index Optimization

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB 索引优化

| username: liuhuanHappyStudy

[Overview] Problem Overview
In MySQL, there are some system tables or views that can query redundant indexes, duplicate indexes, and unused indexes, and then perform index optimization.
What methods are available in TiDB to check for redundant indexes, duplicate indexes, and unused indexes?

[TiDB Version]
Release Version: v5.0.1
Edition: Community
Git Commit Hash: 1145e347d3469d8e89f88dce86f6926ca44b3cd8
Git Branch: heads/refs/tags/v5.0.1
UTC Build Time: 2021-04-23 05:51:17
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

If the question is related to performance optimization or troubleshooting, please download the script and run it. Please select all and copy-paste the terminal output.

2
| username: ddhe9527 | Original post link

The mysql.schema_index_usage table, refer to the following link:

Additionally, you can also perform aggregate analysis on the information_schema.statements_summary table. The indexes used by SQL are in the INDEX_NAMES field, and the number of times SQL is executed is in the EXEC_COUNT field.

3
| username: cs58_dba | Original post link

In fact, it feels equally important to analyze TiDB Dashboard - Slow Queries or TiDB Dashboard - SQL Statements and optimize slow SQL.

4
| username: Kongdom | Original post link

I agree with the previous comment, regularly analyze and optimize the top 10 SQL statements in the dashboard.

5
| username: Mark | Original post link

Is it a MySQL pt tool? This is similar, you can make a request to the official, it is still very useful.

6
| username: cs58_dba | Original post link

Alternatively, integrating the feature into TiDB Dashboard would also work.

7
| username: ealam_小羽 | Original post link
  1. You can check the posts about product requirements to see if there are any tools.
  2. However, index optimization is generally more about optimizing based on slow queries from the TiDB Dashboard.
    TiDB Dashboard Introduction:
    Image
    Image849×569 132 KB

    TiDB Dashboard 介绍 | PingCAP 文档中心
    TiDB Dashboard Access:
    Image
    Image810×558 151 KB

    访问 TiDB Dashboard | PingCAP 文档中心
8
| username: liuhuanHappyStudy | Original post link

The statistical function of the mysql.schema_index_usage table has not been implemented yet, right? There is no data in TiDB v5.0.1. Some information can still be aggregated in the information_schema.statements_summary table. Thank you very much.

9
| username: system | Original post link

This topic will be automatically closed 60 days after the last reply. No new replies are allowed.