super
, shutdown
, and file
in TencentDB for MySQL. The following error may occur when you run the set
statement: #1227-Access denied;you need(at least one of)the SUPER privilege (s) for this operation
Solution: if you need to modify relevant parameters using "set", go to Database Management > Parameter Settings on the instance management page. If the parameters to be modified are not listed there, you can submit a ticket for application, and we will evaluate and make the modifications for you so as to ensure instance stability.pt-online-sche-machange
.begin;
for online operations, which can help minimize the risk of data loss caused by faulty operations. In case of a faulty operation, you can use the rollback feature of TencentDB for MySQL for data restoration (rollback to any point in time in the last 5 days is supported). For tables without cross-database and cross-table logic, you can use quick or instant rollback for even faster data restoration. The new table after rollback is named "original table name_bak".row_format
must be non-fixed.binbin_format
is row, deleting data in batches without the primary key can cause serious master-slave delay.select xxx where a = x and b = x;
, if a and b are used together to create a composite index and a has higher differentiation, then the composite index should be created as idx_ab(a,b)
. If None-Equal To and Equal To conditions are used at the same time, the column with the Equal To condition must be put first; for example, for where a xxx and b = xxx
, b must be placed at the forefront even if a has a higher differentiation, because index a will not be used in the query.varchar
field, it is recommended to specify an index length but not to index the entire column. This is because the varchar
column is often long and specifying the index length can provide sufficient differentiation. Indexing the entire column will increase the maintenance costs. You can use count (distinct left (column name, index length))/count (\*)
to check index differentiation.select a,b from xxx where a = xxx
, if a is not the primary key, a composite index can be created on a and b columns to prevent the problem.INSERT INTO t_xxx VALUES(xxx)
, and the column attributes to be inserted must be specified explicitly to prevent data errors caused by changes in the table structure.order by a b desclike
.xxx%
; however, in other cases, indexes will not be used.select *
to avoid the following problems: innodb_buufer_pool_size
which may reduce the query hit rate. SELECT * FROM test WHERE id = ( SELECT sql_no_cache id FROM test order by id LIMIT 80000,1 ) LIMIT 10 ;
.
- It is difficult to totally avoid the above problems. The solution is not to use the aforementioned conditions as the primary filtering conditions; instead, set them as the conditions secondary to the primary filtering conditions for indexes.
- If a large number of full table scans is found in the monitor, set the
log_queries_not_using_indexes
parameter in the console and download the slow logs for analysis later. Do not keep it enabled for too long so as to avoid a surge of slow logs.- Perform the required SQL audit before a business goes live. In daily OPS work, download slow query logs regularly for targeted optimization.
Was this page helpful?