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.
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), and the column attributes to be inserted must be specified explicitly to prevent data errors caused by changes in the table structure.
Pay attention to the following common reasons for invalid indexes in SQL statements:
Implicit type conversion; for example, if the type of index a is VARCHAR and the SQL statement is
where a = 1, then VARCHAR is changed to INT.
Math calculations and functions are performed on the index columns; for example, date column is formatted using a function.
Columns on which a join operation is performed have different character sets.
Multiple columns have different sorting orders; for example, the index is (a,b), but the SQL statement is
order by a b desclike.
When fuzzy queries are performed, some indexes can be queried for characters in the format of
xxx%; however, in other cases, indexes will not be used.
NOT, !=, NOT IN, etc. are used in queries.
Ensure query on demand and reject
select * to avoid the following problems:
innodb_buffer_pool_sizewhich may reduce the query hit rate.
Avoid using large transactions. It is recommended to split a large transaction into multiple small ones to avoid source-replica delay.
Commit transactions in the business code in a timely manner to avoid unnecessary lock waits.
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.
Use LIMIT for paging optimization. The operation "LIMIT 80000, 10" is to filter out 80,010 records and then return the last 10 ones. This may cause a high load on the database. It is recommended to locate the first record before paging, such as
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.
- 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.
- 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.