tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TDSQL-C for MySQL
    Documentation
    Download PDF

    Collecting Statistics

    Last updated: 2024-12-17 16:38:40
    Download PDF
      For the read-only analysis engine, you should collect basic statistics of table objects to achieve good SQL execution results. The system can more precisely recommend a reasonable SQL execution plan for you only after the table statistics are collected.

      Statistics Overview

      The statistics is the data characteristics and distribution information about database objects. The database can use the statistics to calculate a more accurate planned cost and optimize the plan selection. The types of statistics include the number of rows in the table, the number of distinct values (NDV) in the column, and the column histogram information. Collecting the statistics involves scanning and obtaining the database objects and storing the collected statistics in the data dictionary of the system. Additionally, the system maintains a local cache of the statistics to enhance the access efficiency of the optimizer. While collecting and using statistics, you should pay attention to the following two points:
      The accuracy of statistics is vital for decision making of the optimizer. Therefore, regularly updating and maintaining the statistics is key to maintaining the database performance.
      The collection of statistics may have a certain impact on the database performance, for it involves scanning and analyzing database objects. Therefore, you should weigh the performance and the statistics accuracy when collecting statistics.
      In summary, the statistics play a crucial role in the database management system. They provide critical information about database objects and assist the optimizer in making more advisable decisions, so as to enhance the query performance and the overall efficiency of the database system.

      Statistics Collection

      Log in to the read-only analysis engine instance through the MySQL client and execute the following statement to collect statistics of the specified table object:
      ANALYZE TABLE <table name>;

      Table Statistics Query

      To view the statistics collection status, you can use the following command:
      SHOW STATS_META [where db_name=<DBNAME>];
      The WHERE clause supports LIKE queries or other expression filters. You can view the statistics of objects in the corresponding db_name as needed.
      The fields of the query results are described as follows:
      Column Name
      Description
      db_name
      The name of the database.
      table_name
      The name of the table.
      partition_name
      The name of the partition.
      update_time
      Update time of the collection.
      modify_count
      Number of the rows modified in the table.
      row_count
      Total number of rows in the table.
      The following is an example of querying the statistics collection status of the t1 table in the test database:
      SHOW STATS_META WHERE db_name='test' AND table_name='t1';
      
      
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy