AnalyticDB for MySQL provides the schema optimization feature to offer optimization suggestions on how to reduce costs and improve efficiency for the use of clusters. These suggestions are provided based on algorithm analysis of continuously collected query performance metrics and relevant information such as tables and indexes. This feature greatly simplifies manual optimization.
Background information
As the number of data analysis services grows, the quantity and complexity of database queries to be processed also increase. Schema design and optimization can significantly reduce database costs and improve query performance. To perform schema design and optimization, you must take note of the following information:
Database engine architecture
The database engine architecture of computing and storage resources can help you perform data modeling and design suitable table schemas based on data distribution and business scenarios.
Differences in SQL statement characteristics
SQL statements suitable for ad hoc queries are quite different, such as the number of tables to be joined, the number of fields in the GROUP BY clause, and the join and filter conditions.
Differences in data characteristics
Data distribution and query characteristics vary based on business characteristics. In this case, if the initial data modeling methods and SQL statements remain unchanged, SQL engines cannot deliver the optimal performance. The performance of SQL statements may degrade based on the change of data characteristics or business models.
In such situations, AnalyticDB for MySQL provides the efficient and intelligent schema optimization feature to offer optimization suggestions on how to reduce costs and improve efficiency for the use of clusters.
Usage notes
Schema optimization can be used for versions later than AnalyticDB for MySQL V3.1.4.
Schema optimization suggestions are obtained from analysis of historical user data and query characteristics. If user data and query characteristics are stable, the relevant suggestions can remain valid. If user data and query characteristics are changed significantly, the suggestions may also significantly become less valuable as a reference. We recommend that you determine whether to adopt the suggestions based on your business characteristics.
Overview
Schema optimization provides suggestions of two optimization types, which are hot and cold data optimization and index optimization.
Hot and cold data optimization
Suggestions on hot and cold data optimization are to migrate tables that are not in use for a long time to cold storage disks, which reduces table storage costs. Suggestions of this optimization type are applicable to tables. For more information about how to migrate tables to cold storage disks, see Separation of hot and cold data storage.
After tables are migrated to cold storage disks, it takes longer to query the tables.
Index optimization
Suggestions on index optimization are to delete data indexes that are not in use for a long time, which reduces index storage costs. For information about how to filter data by index, see Filter conditions without pushdown.
After a data index is deleted, it takes longer to filter a table by using the data referenced in the index.
Procedure
- Log on to the AnalyticDB for MySQL console.
- In the upper-left corner of the page, select a region.
- In the left-side navigation pane, click Clusters.
- On the Data Warehouse Edition (V3.0) tab, find the cluster that you want to manage and click the Cluster ID.
In the left-side navigation pane, click Diagnostics and Optimization.
Click the Schema Optimization tab.
On the Available Optimization Suggestions tab, turn on Enable to enable schema optimization. Skip this step if this feature is already enabled for the cluster.
View available and applied optimization suggestions on the Available Optimization Suggestions and Applied Optimization Suggestions tabs.
On the Available Optimization Suggestions tab, view parameter information in the suggestion list and perform operations.
Parameter
Description
Suggestion ID
The ID of the optimization suggestion.
SQL
The SQL statement that specifies the detailed information of the table to modify.
Optimization Type
The type of optimization. Valid values: Hot and Cold Data Optimization and Index Optimization.
Optimization Suggestion
The specific optimization suggestion given for the optimization type.
Expected Optimization Benefits
The expected benefits obtained after the optimization suggestion is applied.
NoteThe expected optimization benefits are estimated values measured based on historical data and for reference only.
Actions
If the optimization type is Hot and Cold Data Optimization or Index Optimization, you can click Apply to apply the optimization suggestion.
NoteIf you agree to adopt the optimization suggestion, click Apply. Then, an ALTER statement is executed on the cluster and the suggestion is displayed on the Applied Optimization Suggestions tab.
The Apply operation has the same effect as executing the ALTER statement on the client. This operation cannot be revoked. Proceed with caution.
On the Applied Optimization Suggestions tab, select a time range and click Search to search for suggestions applied within the specified time range.
NoteThe application of an optimization suggestion with the execution of its SQL statement can be complete only after BUILD operations automatically triggered on tables are complete. Before BUILD operations are triggered, the suggestion is in the Running state. After BUILD operations are triggered, the state of the suggestion changes to Completed.