MRR (Multi-Range Read Optimization)
The range query logic before MRR optimization is as follows, which will generate a large number of random accesses, and there is room for optimization in query performance
After the MRR optimization is enabled, the secondary routes in the common index will be sorted, and a large number of random accesses will be converted into sequential access. The process is as follows
- For the MyISAM storage engine, before going to the disk to get the complete data, it will sort the order by rowid, and then read the disk in order.
- For the Innodb storage engine, the clustered index will be sorted according to the key value of the clustered index, and then the clustered index will be read sequentially.
ICP(Index Condition Pushdown Optimization)
Push down optimization of index conditions, very vivid naming, and other conditions accompanying the index search will be pushed down to the search action.
joint index: (age, name)
select * from gallant_user where age between 20 and 35 and name like '%白';
Without index push down the underlying logic of MySQL before optimization, the like statement will not work based on the leftmost match, and it will traverse all the data that age satisfies.
- Traverse the clustered index value corresponding to the index between age 20-35
- Query base table data according to the clustered index corresponding to the age index
- Filter the result set and return the data whose name is "Lao Bai"
After enabling index push down optimization, the underlying logic of mysql, like statements will be pushed down to the storage engine for filtering
- Traverse all data between age 20-35
- Filter out the data that does not meet the name condition, and the clustered index value corresponding to the index that meets the age condition in the remaining joint index and meets the name like statement condition
- Query base table data based on clustered index
- Return data
The above case shows that the index push down technology improves retrieval performance by reducing the number of times that MySQL bottoms back to the table.
- Function index
- Joint index combined with index pushdown optimization
- Add fixed suffix field
Function Index Usage
SELECT id, name FROM gallant_user where name like '%man'; create index idx_like_index on gallant.gallant_user ((name like '%man')); // 查看函数索引 show index from gallant_user;