{"id":21198,"date":"2024-09-30T04:04:54","date_gmt":"2024-09-30T11:04:54","guid":{"rendered":"https:\/\/www.pingcap.com\/article\/mastering-indexing-in-tidb-for-optimal-query-performance\/"},"modified":"2024-12-11T20:08:28","modified_gmt":"2024-12-12T04:08:28","slug":"mastering-indexing-in-tidb-for-optimal-query-performance","status":"publish","type":"article","link":"https:\/\/www.pingcap.com\/ko\/article\/mastering-indexing-in-tidb-for-optimal-query-performance\/","title":{"rendered":"Mastering Indexing in TiDB for Optimal Query Performance"},"content":{"rendered":"

<\/span>Understanding the Importance of Indexing in TiDB<\/span><\/h2>\n

The Role of Indexing in Databases<\/h3>\n

Indexing is a fundamental aspect of database management systems, contributing significantly to query performance and data retrieval efficiency. In a sense, indexing acts like a roadmap, allowing the database engine to swiftly locate the requested data without scanning the entire dataset.<\/p>\n

Concretely, indexes in databases can be likened to the index in a book. Just as the index of a book makes it easy to locate a specific topic or chapter, a database index enables rapid location of rows within tables based on key column values. This translates to faster data retrieval operations, culminating in accelerated query execution and, consequently, improving the overall user experience.<\/p>\n

In the context of \ud2f0DB<\/a>, an open-source NewSQL database that combines the best features of traditional RDBMS and NoSQL systems, indexing not only accelerates query performance but also supports complex transactional and analytical workloads. For comprehensive documentation on best practices in TiDB, please refer to TiDB Best Practices<\/a>.<\/p>\n

Key Features of Indexing in TiDB<\/h3>\n

TiDB supports several types of indexing mechanisms, each designed to serve specific use cases efficiently. Some of the prominent indexing features include:<\/p>\n

    \n
  1. Secondary Indexes:<\/strong> TiDB provides full support for secondary indexes, which are particularly useful in optimizing queries that do not solely rely on the primary key. Secondary indexes in TiDB function similarly to those in traditional databases but with added distributed capabilities.<\/li>\n
  2. Composite Indexes:<\/strong> TiDB allows the creation of composite indexes, which encompass multiple columns. This feature is invaluable in scenarios where queries involve conditions on multiple columns, thus dramatically reducing the number of rows that need to be scanned.<\/li>\n
  3. Covering Indexes:<\/strong> By incorporating all query columns within an index, covering indexes eliminate the need for additional data lookups, reducing I\/O operations and speeding up query performance.<\/li>\n<\/ol>\n

    For more detailed information on creating and using indexes effectively, visit Best Practices for Indexing<\/a>.<\/p>\n

    Common Challenges in Indexing<\/h3>\n

    While indexing undeniably enhances database performance, it is not devoid of challenges. These challenges, if not addressed, can negate the benefits of indexing and may even deteriorate performance:<\/p>\n

      \n
    1. Write Overhead:<\/strong> Each insert, update, or delete operation on a table with indexes incurs additional overhead due to the need to maintain these indexes. Consequently, excessive indexing can slow down write operations.<\/li>\n
    2. Storage Costs:<\/strong> Indexes consume additional storage space, which, in turn, can elevate storage costs. This is particularly notable for databases with numerous or large indexes.<\/li>\n
    3. Maintenance Complexity:<\/strong> Managing and maintaining indexes, particularly in a distributed database like TiDB, can be complex. Indexes must be routinely monitored to ensure their effectiveness and relevance, necessitating a meticulous maintenance strategy.<\/li>\n<\/ol>\n

      An optimal indexing strategy balances between accelerating query performance and minimizing the imposed overhead. For more insights into optimizing indexing strategies, refer to TiDB’s documentation on index best practices<\/a>.<\/p>\n

      <\/span>Advanced Indexing Techniques in TiDB<\/span><\/h2>\n

      Composite Indexes: Creating and Optimizing<\/h3>\n

      Composite indexes are crucial when dealing with queries that involve conditions on multiple columns. Creating a composite index in TiDB involves defining a multi-column index in the table schema. Here is an example:<\/p>\n

      \n
      CREATE<\/span> INDEX<\/span> idx_composite<\/span> ON<\/span> employees<\/span> (<\/span>department_id<\/span>,<\/span> salary<\/span>);<\/span>\n<\/code><\/pre>\n<\/div>\n
      In this example, a composite index is created on the department_id<\/code> \uadf8\ub9ac\uace0 salary<\/code> columns of the employees<\/code> table. This index proves advantageous for queries such as:<\/p>\n
      \n
      SELECT<\/span> *<\/span> FROM<\/span> employees<\/span> WHERE<\/span> department_id<\/span> =<\/span> 5<\/span> AND<\/span> salary<\/span> ><\/span> 50000<\/span>;<\/span>\n<\/code><\/pre>\n<\/div>\n
      Covering Indexes: Reducing I\/O Operations<\/h3>\n
      Covering indexes encompass all columns needed by the query, thus mitigating the need for additional data retrieval operations. This helps in markedly improving query performance by reducing I\/O operations.<\/p>\n
      Consider the following example of a covering index:<\/p>\n
      \n
      CREATE<\/span> INDEX<\/span> idx_covering<\/span> ON<\/span> sales<\/span> (<\/span>product_id<\/span>,<\/span> order_date<\/span>,<\/span> revenue<\/span>);<\/span>\n<\/code><\/pre>\n<\/div>\n
      This index covers queries like:<\/p>\n
      \n
      SELECT<\/span> product_id<\/span>,<\/span> order_date<\/span>,<\/span> revenue<\/span> FROM<\/span> sales<\/span> WHERE<\/span> product_id<\/span> =<\/span> 1234<\/span>;<\/span>\n<\/code><\/pre>\n<\/div>\n
      Full-Text Indexes: Enhancing Search Capabilities<\/h3>\n
      Full-text indexing is instrumental in enabling efficient text search capabilities, especially in use cases involving vast amounts of textual data. Though not traditionally available in TiDB, workarounds can be employed to simulate full-text search capabilities.<\/p>\n
      For instance, you can leverage third-party search engines like Elasticsearch in tandem with TiDB. By integrating Elasticsearch, you can index and search text efficiently while still storing structured data in TiDB. Here is a conceptual example:<\/p>\n
      \n
      # Python snippet for Elasticsearch with TiDB<\/span>\nfrom<\/span> elasticsearch<\/span> import<\/span> Elasticsearch<\/span>\n\nes<\/span> =<\/span> Elasticsearch<\/span>()<\/span>\n\n# Index a document<\/span>\nes<\/span>.<\/span>index<\/span>(<\/span>index<\/span>=<\/span>'documents'<\/span>,<\/span> id<\/span>=<\/span>1<\/span>,<\/span> body<\/span>=<\/span>{<\/span>\n    'title'<\/span>:<\/span> 'Introduction to TiDB'<\/span>,<\/span>\n    'content'<\/span>:<\/span> 'TiDB is an open-source NewSQL database that supports Hybrid Transactional and Analytical Processing workloads.'<\/span>\n})<\/span>\n\n# Search the indexed document<\/span>\nresults<\/span> =<\/span> es<\/span>.<\/span>search<\/span>(<\/span>index<\/span>=<\/span>'documents'<\/span>,<\/span> body<\/span>=<\/span>{<\/span>\n  'query'<\/span>:<\/span> {<\/span>\n    'match'<\/span>:<\/span> {<\/span>\n      'content'<\/span>:<\/span> 'NewSQL database'<\/span>\n    }<\/span>\n  }<\/span>\n})<\/span>\nprint<\/span>(<\/span>results<\/span>)<\/span>\n<\/code><\/pre>\n<\/div>\n
      This Python snippet showcases how to utilize Elasticsearch for text indexing while TiDB manages structured data. For scenarios requiring integrated text search, utilizing add-on solutions like Elasticsearch is recommended.<\/p>\n
      Index-Only Scans: Improving Query Efficiency<\/h3>\n
      Index-only scans occur when the index itself suffices to fulfill the query requirements, thereby obviating the need to access the table rows. This greatly minimizes I\/O operations and enhances query efficiency.<\/p>\n
      For instance, consider an index created as follows:<\/p>\n
      \n
      CREATE<\/span> INDEX<\/span> idx_only<\/span> ON<\/span> sales<\/span> (<\/span>customer_id<\/span>,<\/span> order_total<\/span>);<\/span>\n<\/code><\/pre>\n<\/div>\n
      For queries like:<\/p>\n
      \n
      SELECT<\/span> customer_id<\/span>,<\/span> order_total<\/span> FROM<\/span> sales<\/span> WHERE<\/span> customer_id<\/span> =<\/span> 789<\/span>;<\/span>\n<\/code><\/pre>\n<\/div>\n
      <\/span>Practical Applications and Case Studies<\/span><\/h2>\n
      Real-World Examples of Index Optimization in TiDB<\/h3>\n
      The practical advantages of indexing are best comprehended through real-world applications. Here are a few illustrative examples:<\/p>\n
        \n
      1. E-commerce Platform:<\/strong> An online retailer leveraged composite and covering indexes to accelerate customer order queries, which significantly reduced page load times during high-traffic shopping seasons.<\/li>\n
      2. Financial Institutions:<\/strong> A banking system employed secondary and composite indexes to swiftly process transactional queries, resulting in improved customer service and faster transaction verification.<\/li>\n<\/ol>\n
        These examples underline how judicious indexing strategies can dramatically enhance system performance and user satisfaction.<\/p>\n
        Performance Benchmarks: Indexed vs. Non-indexed Queries<\/h3>\n
        Benchmarking demonstrates the stark performance improvements brought about by indexing. Consider the following SQL queries on a sales<\/code> table with and without indexes:<\/p>\n
      Since all the needed data is contained within the index, TiDB can execute the query solely by scanning the index, greatly improving performance. For further exploration of index-only scans, check out TiDB Best Practices<\/a>.<\/p>\n
      Since all columns required by the query are present in the index, the database can retrieve the data solely from the index, bypassing the need to access the table’s main storage. This practice drastically reduces query latency and accelerates response times. Learn more about covering indexes and how to implement them effectively from the TiDB documentation<\/a>.<\/p>\n
      To optimize composite indexes, it is crucial to adhere to the left-prefix principle, which dictates that queries should filter on the initial columns of the composite index for the index to be effectively utilized. For more practical uses and best practices, refer to Best Practices for Indexing<\/a>.<\/p>\n