filein TencentDB for MySQL. The following error may occur when you run the
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.
#1227-Access denied;you need(at least one of)the SUPER privilege (s) for this operation
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_formatmust be non-fixed.
binbin_formatis 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.
varcharfield, it is recommended to specify an index length but not to index the entire column. This is because the
varcharcolumn 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_sizewhich 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_indexesparameter 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.