For a read-only analysis instance to achieve good SQL execution performance, you need to collect basic statistics on tables. After statistics collection is complete, the system can more accurately recommend optimal execution plans for the SQL statements executed by users.
Statistics Overview
Statistics are information about the data characteristics and distribution of database objects. A database can use statistics to calculate more accurate plan costs and optimize the selection of execution plans. The types of statistics include information such as table row counts, the Number of Distinct Values (NDV) per column, and column histograms. Collecting statistics involves scanning and obtaining database objects, and the collected statistics are stored in the system's data dictionary. Additionally, the system maintains a local cache of statistics to improve the optimizer's efficiency in accessing them.
When collecting and using statistics, note the following two points:
The accuracy of statistics is crucial for the optimizer's decision-making. Therefore, regularly updating and maintaining statistics is key to sustaining database performance.
Collecting statistics may have some impact on database performance because it involves scanning and analyzing database objects. Therefore, when collecting statistics, you need to balance performance with the accuracy of the statistics.
In summary, statistics play a crucial role in database management systems. They provide key information about database objects, assist the optimizer in making wiser decisions, and thereby enhance query performance and improve the overall efficiency of the database system.
Note:
The random sampling feature can perform random sampling at the sampling rate specified by the TableScan operator. It also supports block-level sampling and can collect statistics based on a configured ratio. This significantly reduces the overhead caused by full table scans during statistics collection.
Querying Table Statistics Information
Query table statistics using the SHOW TABLE STATS statement:
SHOW TABLE STATS [ShowLikeOrWhere];
Description of the query result field is as follows:
|
Db_name | Database name. |
Table_name | The table name. |
Partition_name | Partition name. |
Update_time | Time when statistics are collected or updated. |
Row_count | Total number of rows in the table. |
Sample_size | Number of rows sampled when basic statistics are collected. |
Locked | Whether the statistics of the table are locked. |
For example, to query the statistics of table t1 in the test database:
SHOW TABLE STATS WHERE Db_name='test' AND Table_name='t1';
Note:
In addition to SHOW TABLE STATS, the read-only analysis instance also supports the following statistics query commands:
SHOW COLUMN STATS: View column-level statistics.
SHOW INDEX STATS: View index statistics.
SHOW HISTOGRAM STATS: View histogram statistics.
SHOW STATS HEALTHY: View the health of statistics.
SHOW ANALYZE STATUS: View the status of ongoing statistics collection tasks.
SHOW TABLE MODIFIED: View table modification details (including the number of rows for Insert, Update, and Delete operations).
Statistics Collection
The read-only analysis instance supports two methods for collecting statistics: automatic collection and manual collection.
Automatic Collection
The kernel of the read-only analysis instance supports the capability to dynamically sample and automatically collect statistics when data is loaded into columnar storage. Users can generate statistics for corresponding database tables without manual collection.
Attention:
Statistics collected automatically by default do not include histogram information for tables. To collect histogram information for a table, use the manual collection method.
Manual Collection
To manually obtain the latest statistics for a table, log in to the read-only analysis instance using a MySQL client and execute the following statement to collect statistics for the specified table:
ANALYZE TABLE <table_name> [ALL COLUMNS | COLUMNS IdentList] [WITH <num> BUCKETS] [WITH <rate> SAMPLERATE];
Parameter description:
|
ALL COLUMNS | Specifies that all columns collect statistics according to the default behavior. For example, collects histograms for all columns. |
COLUMNS IdentList | Specifies collecting statistics for specified columns. For example, COLUMNS c1,c2,c3. |
WITH <num> BUCKETS | Specifies the number of buckets for equal-height histogram or TOPN collection, with a value range of [1, 1024]. Uses the default value of 256 when not specified. |
WITH <rate> SAMPLERATE | Specifies the sampling rate, in percentage, with a value range of [0.000001, 100]. Determined automatically by the system based on data volume when not specified. |
Note:
When the ALL COLUMNS and COLUMNS clauses are not specified, the default collection policy is used, which collects basic statistics for columns and does not include histogram information.
In addition to the clauses mentioned above, it also supports extended clauses such as INDEX, PARTITION, and UPDATE HISTOGRAM ON.
Use Case
Collect statistics for the t1 table in the test database using the default policy:
Collect histogram statistics (with 256 buckets) for all columns of the t1 table in the test database:
ANALYZE TABLE t1 ALL COLUMNS WITH 256 BUCKETS;
Collect histogram statistics (with 256 buckets) for the c1 and c2 columns of the t1 table in the test database:
ANALYZE TABLE t1 COLUMNS c1,c2 WITH 256 BUCKETS;
Database-Level Statistics Collection
When there are many tables, you can collect statistics for the entire database. The meaning of the collection policy is the same as at the table level. The collection principle is to complete the collection for all tables under the database as much as possible, and a failure in collecting a single table does not affect the collection of other tables.
ANALYZE DATABASE DBNameList [ALL COLUMNS | COLUMNS IdentList] [WITH <num> BUCKETS] [WITH <rate> SAMPLERATE];
For example, to collect statistics for all tables in the test database:
Statistics Management
The read-only analysis instance also provides the following statistics management commands for fine-grained control over the statistics lifecycle.
Deleting Statistics
Delete statistics for a specified table using the DROP STATS statement:
DROP STATS TableNameList;
For example, to delete statistics for the t1 table in the test database:
Locking and Unlocking Statistics
Lock statistics for a specified table using LOCK STATS. Once locked, the statistics for that table will not be overwritten by automatic collection tasks:
LOCK STATS TableNameList;
Unlock statistics for a specified table using UNLOCK STATS:
UNLOCK STATS TableNameList;
For example, to lock statistics for the t1 table in the test database: