Detailed analysis of matters needing attention in MySQL

Detailed analysis of matters needing attention in MySQL

1. When making fuzzy queries (% is placed Position)

Differential treatment: If % is on the far left side of the query keyword, the index cannot be used at this time, but if it is not on the far left side, the index can be used.

#　select * from news where name like “%zhangsan”;无法使用（全表扫描）

##2.# select * from news where name like “z%”; you can use

3. The four arithmetic operations and function processing cannot be performed on the query keywords

# select * from news where id+1 = 1;
#  select * from people where length(name) = 5;

4. Use of common functions

now()
from_unixtime()：三月份每天最佳商品的总数、五月份每天注册的人数
rand(): 抽奖
# select * from user order by rand() limit 3;
benchmark()
# select benchmark(count, express): 执行count次express表达式

##Use cases of MySQL index

1. Paging effect under big data: Generally, when doing massive data processing, paging needs to be optimized

2. Generally, when paging, it is handled like this

##3. Mainly because MySQL is performing the limit operation At this time, the operation is like this

First take out the offset+number data, then discard the offset data and return the number data.

Generally, in order to prevent MySQL from occurring this kind of behavior, you can use:

# select * from news where id > 1000 limit 10;

4. Through the above comparison test, it is found that in the second type of paging processing, the primary key ID index can also be used, which can obviously speed up the process. So paging can be handled this way in the future.

The above is the detailed content of Detailed analysis of matters needing attention in MySQL. For more information, please follow other related articles on the PHP Chinese website!