Solution: you can modify parameters on the Database Management > Parameter Settings tab in the console rather than run SET statements.
#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.
binlog_formatis row, deleting data in batches without the primary key can cause serious source-replica delay.
select xxx where a = x and b = x;, if a and b are used together to create a composite index and a has a higher selectivity, 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, in
where a xxx and b = xxx, b must be placed on the far left even if a has a higher selectivity, because a will not be used in the query.
count(distinct left(column name, index length))/count(\*)to check index selectivity.
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.
where a = 1, then VARCHAR is changed to INT.
order by a b desclike.
xxx%; however, in other cases, indexes will not be used.
select *to avoid the following problems:
innodb_buffer_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 are monitored, 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 logs regularly for targeted optimization.