MySQL slow query optimization

An index failure

a) The query statement using the LIKE keyword.
In the query statement that uses the LIKE keyword to query, if the first character of the matching string is "%", the index
Won't work. Only "%" is not indexed in the first position will work.
SELECT * FROM users WHERE username LIKE'old%'-use index
SELECT * FROM users WHERE username LIKE'%lu'-do not use index
b) The index column participates in the calculation
If the column in the condition is calculated, the index of the column will not be used
SELECT * FROM users WHERE userage=20;-- use index
SELECT * FROM users WHERE userage=10+20;-- use index
SELECT * FROM users WHERE userage+10=30;-- will not use the index
c) The index column uses a function
If a function is used on a column in the condition, the index of that column will not be used.
SELECT * FROM users WHERE username=concat('old','lu'); – use index
SELECT * FROM users WHERE concat(username,'lu')='oldlu';-will not use the index
d) Try to avoid OR operations
SELECT * FROM users WHERE username='oldlu' or userage=20 or usersex='male'
-If there is or in the condition, all fields required to be used must be indexed, otherwise the index is not used
Unless each column is indexed, OR is not recommended. In multi-column OR, you can consider replacing it with UNION.
SELECT * FROM users WHERE username='oldlu' UNION
SELECT * FROM users WHERE userage=20 UNION
SELECT * FROM users WHERE usersex='male

Two decomposition associated with the query

It is necessary to break a large query into multiple small queries.
Many high-performance applications will decompose the associated query, that is, a single-table query can be performed on each table.
Then correlate the query results in the application, which will be more efficient in many scenarios
SELECT * FROM users u
JOIN orders o ON u.userid = o.user_id
JOIN item i ON o.orderid = i.order_id
WHERE u.userid = 200;
Decomposed into:
SELECT * FROM users WHERE userid = 200;
SELECT * FROM order WHERE user_id = 200;
SELECT * FROM item WHERE order_id in (12321,32412,32321);

Three optimization LIMIT page

Operations that require paging in the system are usually implemented using limit plus offset, and at the same time adding appropriate
order by clause. If there is a corresponding index, the efficiency is usually good.
If the offset is very large, for example, it may be a query such as limit 1000000,20, which is required by mysql
To query 1000020 records and only return the last 20 records, the first 1000000 records will be discarded. This cost
Very high.
Use subqueries to optimize paging queries with large data volumes
select * from users where username ='oldlu' limit 1000000,100;
select * from users where username ='oldlu' and userid >= (select userid from users where
username ='oldlu' LIMIT 1000000,1) LIMIT 100;
Use id restriction to optimize paging query of large data volume
To use this method, you need to first assume that the id of the data table is continuously increasing.
The number of records can be calculated to query the range of id, you can use id between and to query.
Insert picture description here