mysql 5.6
MRR(Multi-Range Read Optimization)
MRR优化前范围查询逻辑如下,会产生大量的随机访问,查询性能存在优化空间
MRR优化启用后会对普通索引中的二级路由进行一个排序,将大量随机访问转换为顺序访问,流程如下
- 对于MyISAM存储引擎,在去磁盘获取完整数据之前,会先按照rowid排好序,再去顺序的读取磁盘。
- 对于Innodb存储引擎,则会按照聚簇索引键值排好序,再顺序的读取聚簇索引。
ICP(Index Condition Pushdown Optimization)
索引条件下推优化,很形象的命名,索引检索的伴随的其他条件会下推至检索动作
案例sql
联合索引:(age,name)
select * from gallant_user where age between 20 and 35 and name like '%白';
没有索引下推优化前mysql底层逻辑,like语句基于最左匹配不会起作用,会遍历所有age满足的数据
- 遍历age 20-35之间索引对应的聚簇索引值
- 根据age索引对应的聚簇索引查询基表数据
- 过滤结果集,将name为“老白”的数据返回
启用索引下推优化后mysql底层逻辑,like语句会下推至存储引擎进行过滤
- 遍历age 20-35之间所有数据
- 过滤掉不满足name条件的数据,剩余联合索引中满足age条件,且满足name like语句条件的索引对应的聚簇索引值
- 根据聚簇索引查询基表数据
- 返回数据
上面的案例可以得出索引下推技术通过减少mysql底层回表的次数来提升检索性能
like ‘%关键字’
- 函数索引
- 联合索引结合索引下推优化
- 新增固定的后缀字段
函数索引用法
案例数据
id | name |
---|---|
1 | superman |
2 | spiderman |
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;
Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Index_type | Expression |
---|---|---|---|---|---|---|---|---|
gallant_user | 0 | PRIMARY | 1 | id | A | 2 | BTREE | |
gallant_user | 1 | idx_like_index | 1 | A | 1 | BTREE | (name like _utf8mb3’%man’) |