Purpose
Standardize the management and maintenance of TDSQL-C for MySQL to avoid unavailability and other issues caused by improper operations.
Guide database developers in writing SQL statements reasonably to make best use of performance of TDSQL-C for MySQL.
Permission Management Specifications
Considering the stability and security of TDSQL-C for MySQL, SUPER, SHUTDOWN, and FILE permissions are restricted. Therefore, the following error may occur when executing SET statements on TDSQL-C for MySQL:
Solution: If you need to modify related parameters using SET, this can be done through the Parameter Settings feature on the cluster management page of the console.
Grant permissions on demand. It is sufficient to grant general applications only with the DML permissions (SELECT, UPDATE, INSERT, and DELETE).
Grant permissions to users of general applications at the database level, following the principle of least privilege.
Allow authorized users to access TDSQL-C for MySQL only from specific IP addresses or IP ranges. This can be achieved by configuring security groups in the console as instructed there. To set a security group for public network access, be sure to allow all the egress IP addresses involved.
Use different accounts for management and development.
Operation Specifications
Notes
Do not use weak passwords for enhanced database instance security.
For private network connections and login, ensure that the client-side CVM and TDSQL-C for MySQL instances are on machines with the same account, in the same region, and on the same VPC.
To parse binlog logs downloaded from the console locally, ensure the client's MySQL version matches the TDSQL-C for MySQL cluster version. Otherwise, parsing may result in garbled text. We recommend that you use mysqlbinlog v3.4 or later.
Enclose the URL with quotation marks when downloading cold backup files to a CVM instance over the private network in the console; otherwise, a 404 error will occur.
Suggestions
Avoid performing online DDL operations during peak business hours. For applicable tools, refer to: pt-online-schema-change.
Avoid performing batch operations during peak hours. Do such operations in off-peak hours instead.
Avoid running an instance for multiple businesses to minimize the risk of mutual interference between businesses due to high coupling.
We recommend that you disable transaction auto-commit and adopt the habit of using begin; first in online operations to reduce the risk of data loss from faulty operations. In case of faulty operations, you can use the rollback feature of TDSQL-C for MySQL. For tables not involving cross-database and cross-table logic, rapid or instant rollback can be used for faster data restoration. The default name for newly generated tables upon rollback is original table name_bak.
Estimate the resources required in advance and optimize the instances for promotional campaigns of your business. In case of a great demand for resources, contact your service manager in a timely manner.
Database and Table Design Specifications
Notes
TDSQL-C for MySQL v5.7 and later do not support MyISAM or MEMORY engines. If the MEMORY engine is required, we recommend that you use TencentDB for Redis or Memcached. If databases are migrated to TDSQL-C for MySQL via DTS, the MyISAM engine will be automatically converted to InnoDB.
Create at least one index on the auto-increment column or create a composite index whose first column is the auto-increment column.
row_format must be non-fixed.
Every table must have a primary key. Even if no suitable column can serve as the primary key, it is still necessary to add an arbitrary column as the primary key. According to the First Normal Form standard of MySQL, InnoDB secondary index leaf nodes will store a copy of the primary key value. We recommend that you use an auto-incrementing short column as the primary key to reduce the disk space occupied by indexes and improve efficiency. In scenarios where binlog_format is set to row, deleting data in batches without a primary key can cause severe primary-secondary latency.
Define fields as NOT NULL and set default values. NULL fields will cause unavailability of indexes, thus bringing problems to SQL development. NULL calculation can only be implemented based on IS NULL and IS NOT NULL.
Suggestions
Plan database resource usage based on business scenario analysis and estimated data access metrics (including database read/write QPS, TPS, storage and space). You can also configure monitoring for TDSQL-C for MySQL instances through the console in the Tencent Cloud Observability Platform (TCOP) interface.
Put the tables for the same type of businesses into one database when building databases and try not to mix them. Do not perform cross-database correlation operations in programs, as doing so will affect subsequent quick rollbacks.
Always use the utf8mb4 character set to reduce the risk of garbled characters. Certain complex Chinese characters and emojis can be displayed correctly only using utf8mb4. Changing the character set only affects tables created after the change. Therefore, we recommend that you select utf8mb4 when purchasing a new TDSQL-C for MySQL instance.
Use the DECIMAL type to store decimal values. The FLOAT and DOUBLE types have insufficient precision, especially for businesses involving money where the DECIMAL type must be used.
Do not use the TEXT or BLOB type to store a large quantity of text, binary data, images, files, and other contents in a database; instead, store such data as local disk files and only store their index information in the database.
Avoid using foreign keys. We recommend that you implement the foreign key logic at the application layer. Foreign key and cascade update are not suitable for high-concurrence scenarios, because they may reduce the insertion performance and lead to deadlock in case of high concurrence.
Reduce the coupling between business logic and data storage, mainly use databases for data storage, and aim to implement business logic at the application layer as much as possible, minimizing the use of stored procedures, triggers, functions, events, views, and other advanced features due to their poor portability and scalability. If such objects exist in an instance, we recommend that you not set definer by default to avoid migration failures caused by inconsistencies between the migration account and definer.
Do not use partitioned tables if your business is not expected to reach a significant magnitude in the short term. Partitioned tables are primarily used for archive management, especially in the express industry and e-commerce industry for order tables. Partitioned tables do not enhance performance, unless over 80% of queries in your business involve the partitioning field.
Purchase read-only instances to implement read/write separation for business scenarios with a high read load and low requirement for consistency (where a data latency within seconds is acceptable).
Index Design Specifications
Notes
Do not create indexes on columns that are updated very frequently and have low discrimination. Record updates will modify the B+ tree, and indexing fields with frequent updates can significantly reduce the database performance.
When creating a composite index, place the column with the highest discrimination on the far left. For example, in select xxx where a = x and b = x;, if a and b are used together to create a composite index and a has higher discrimination, then create idx_ab(a,b). If you have a mix of Non-Equal and Equal conditions, the column with the Equal condition must be placed first. For instance, in where a xxx and b = xxx, b must be placed in the foremost position of the index, even if a has higher discrimination, as the index cannot reach a.
Suggestions
Use no more than five indexes in a single table and no more than five fields in a single index. Too many indexes may affect the filtering effect, occupy much more capacity, and consume more resources for management.
Create indexes on the columns that are used for SQL filtering most frequently with a high cardinality value. It is meaningless to create indexes on a column not involved in SQL filtering. The higher the uniqueness of a field, the higher the cardinality value, and the better the index filtering result. Generally, an index column with a cardinality below 10% is considered an inefficient index, such as the gender field.
When indexing a VARCHAR field, we recommend that you specify the index length, rather than indexing the entire column. Generally, VARCHAR columns are long, and specifying a certain index length is often sufficient for discrimination. It is not necessary to index the entire column, which can be cumbersome and increase the cost of index maintenance. The index discrimination can be evaluated using count(distinct left(column name, index length))/count(*).
Avoid using redundant indexes. If both index (a,b) and index (a) exist, (a) is considered a redundant index. If the query filtering is based on column a, the index (a,b) is sufficient.
Reasonably utilize covering indexes to reduce the I/O overhead. In InnoDB, leaf nodes of secondary indexes only save their own key values and primary key values. If an SQL query does not target an index column or primary key, the search through this index will first find the corresponding primary key and then locate the required column based on that primary key, leading to back table. This process incurs additional I/O overhead. Covering indexes can be employed to solve this issue. For example, select a,b from xxx where a = xxx, if a is not the primary key, then a composite index can be created for the columns a and b, thereby avoiding back table.
SQL Statement Compiling Specifications
Notes
Do not use LIMIT for UPDATE and DELETE operations, because LIMIT is random and may cause data errors; instead, you must use WHERE for such operations for exact match.
Do not use INSERT INTO t_xxx VALUES(xxx). You must explicitly specify the column attributes to be inserted to prevent data errors due to changes in the table structure.
The following are common causes of invalid indexes in SQL statements:
Implicit type conversion, such as when the type of index a is VARCHAR and the SQL statement is written as where a = 1; where VARCHAR is converted to INT.
Math calculations and functions are performed on the index columns; for example, the date column is formatted using a function.
The character set of join columns is not unified.
Inconsistencies in multi-column sorting order, such as when the index is (a,b), but the SQL statement is ordered by a b desclike.
When fuzzy search is used, indexes can be queried for character types in the format of xxx%; other scenarios will not use indexes.
Negative direction queries are used (such as not, !=, and not in,).
Suggestions
Ensure query on demand and reject select * to avoid the following issues:
The covering index does not work and the problem of TABLE ACCESS BY INDEX ROWID occurs, which leads to extra I/O overhead.
Additional memory load occurs. A large amount of cold data is imported to innodb_buffer_pool_size, which may reduce the query hit rate.
There are extra overheads in network transfer.
Avoid using large transactions. We recommend that you split a large transaction into multiple small ones to avoid primary-secondary latency.
Unnecessary lock waits may occur. To prevent this issue, commit transactions in the business code timely.
Minimize the use of join operations for multiple tables and do not perform join operations on big tables. When a join operation is performed on two tables, the smaller one must be used as the driving table, the columns to be joined must have the same character set, and all of them must have been indexed.
Optimize pagination with LIMIT, operations like LIMIT 80000,10 extract 80010 records and return the last 10, creating significant database stress. We recommend that you determine the starting record position before paginating, for example, SELECT * FROM test WHERE id >= (SELECT sql_no_cache id FROM test order by id LIMIT 80000,1) LIMIT 10;.
Avoid using an SQL statement with multi-level nested subqueries. The query optimizer prior to MySQL v5.5 can convert IN to EXISTS and does not go through the indexes. In this case, a large external table may result in poor performance.
Note:
It is difficult to completely avoid the aforementioned issues. The solution is to set the aforementioned conditions as secondary filtering conditions for indexes rather than as primary filtering conditions.
A large number of full table scans have been detected through monitoring. You can set the log_queries_not_using_indexes parameter in the console and later download the slow log file for analysis. However, keep it enabled for only a short duration to avoid a surge in slow logs.
Perform the required SQL audit before a business is released. In routine operation and maintenance work, download slow query logs regularly for targeted optimization.