mysql optimization

mysql 5.6

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

1

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

  1. 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.
  2. 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.
2

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.
Case sql
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.

  1. Traverse the clustered index value corresponding to the index between age 20-35
  2. Query base table data according to the clustered index corresponding to the age index
  3. 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

  1. Traverse all data between age 20-35
  2. 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
  3. Query base table data based on clustered index
  4. 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.

like'%keyword'

  1. Function index
  2. Joint index combined with index pushdown optimization
  3. Add fixed suffix field

Function Index Usage

Case data

idname
1superman
2spiderman
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;
TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalityIndex_typeExpression
gallant_user0PRIMARY1idA2BTREE
gallant_user1idx_like_index1A1BTREE( namelike _utf8mb3'%man')