看这篇就够了！MySQL 索引知识点超全总结( 四 ) _MySQL

select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';

在 MySQL 5.5 执行 explain，extra 字段的值显示没有使用索引

文章插图

执行查询花费时间为 0.12s

文章插图

在 MySQL 5.7 执行 explain，extra 字段的值显示使用了索引下推

文章插图

执行查询花费时间为 0.02s

文章插图

索引下推

explain 结果中的 extra 字段值包含 using index condition，则说明使用了索引下推。索引下推功能是从 5.6 版本开始支持的。在 5.6 版本之前，i_first_name 索引是没有使用上的，需要每次去主键索引表取完整的记录值进行比较。从 5.6 版本开始，由于索引 i_first_name 的存在，可以直接取索引的 first_name 值进行过滤，这样不符合"first_name like 'Hi%'"条件的记录就不再需要回表操作。
MRR 优化MySQL 5.6 版本开始支持 Multi-Range Read(MRR)优化，MRR 优化的目的是为减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，对于 IO-bound 类型的 SQL 查询语句可带来性能极大提升。我们先看下对比测试，以下测试语句在同一个 MySQL 实例下执行，执行前均进行 mysql 服务重启，以保证缓存此没被预热。

关闭 MRR

SET @@optimizer_switch='mrr=off';select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';执行耗时为 0.90s

文章插图

开启 MRR

SET @@optimizer_switch='mrr=on,mrr_cost_based=off'; select * from employees where hire_date > '1990-01-14' and first_name like 'Hi%';

文章插图

分析

从测试结果可以发现在 mrr 从关闭到开启，耗时从 0.90s 减少到 0.03s，查询速率达到 30 倍的提升。
常见的索引失效场景在 MySQL 表中建立了索引，SQL 查询语句就会一定使用到索引么？不一定，存在着索引失效的场景。我们给 employees 表增一个组合索引，后续例子均基于此表进行分析、测试。

alter table employees add index i_b_f_l(birth_date, first_name, last_name)alter table employees add index i_h(hire_date);

文章插图

失效场景

范围查询（>,<,<>）

explain select * from employees where hire_date > '1989-06-02';

文章插图

查询条件类型不一致

alter table employees add index i_first_name (first_name);explain select * from employees where first_name = 1;

文章插图

查询条件使用了函数

explain select * from employees where CHAR_LENGTH(hire_date) = 10;

文章插图

模糊查询

explain select * from employees where hire_datelike'%1995';

文章插图

不使用组合索引的首个字段当条件

explain select * from employees where last_name = 'Kalloufi' and first_name = 'Saniya';

文章插图

为什么会失效？

顺序读比离散读性能要好
范围查询一定会导致索引失效么？并不会！稍微更改下查询条件看下 explain 的对比结果，可以看到新语句用到索引下推，说明索引并未失效。为什么？在不使用覆盖索引的情况下，优化器只有在数据量小的时候才会选择使用非聚集索引。受制于传统的机械磁盘特性，通过聚集索引顺序读数据行的性能会比通过非聚集索引离散读数据行要好。所以，优化器在即使有非聚集索引、但是访问数据量可能达到送记录数的 20%时会选择聚集索引。当然也可以用 Force index 强制使用索引。
- 上一页
- 1
- 2
- 3
- 4
- 5
- 6
- 下一页
推荐阅读

看这篇就够了！MySQL 索引知识点超全总结( 四 )

推荐阅读

心理学|心理学：为什么你总是无法做到自律，源于内在缺乏3大认知系统

今年实习offer比较一个是阿里妈妈北京算法岗一个是腾讯TEG基础研究也是算法岗深圳

劈腿怎么练最快又不疼

蒋勤勤“东京电影节”美麻了，黑白长裙难掩曼妙身姿，47岁依旧倾国倾城

女性排毒养颜吃什么好女性排毒养颜美白食谱

青年|一亿年薪？知情人爆料：Uzi几乎确定复出，疯抢之下开天价9位数

互联网腾讯否认微信测试语音消息进度调节

鱼香茄子的家常做法下厨房,鱼香茄子和油焖茄子-

潇湘大视野|【意·暖心】助力意大利！中国在行动

行业互联网原熊猫资本合伙人毛圣博确定加入奇绩创坛并担任合伙人

腐竹怎样快速泡发

女生每个月排几个卵子？

玉的鉴别方法有哪些玉的鉴别和术语

许昌美食攻略|鸡鲜肚脆，养生局安排！，人均20+正宗广州猪肚鸡！文火煲汤

燃军事■乌克兰突袭被美赞扬，俄：损失巨大，俄军反炮兵雷达被毁

传媒|引力传媒：控股股东拟减持不超3.99%股份

旧爱|八月下旬，红娘做媒，痴情等待，拥抱良缘，三星座与旧爱相偎相依

第一财经|天山生物被起诉，账面资金难承6500万债务担保款项丨热公司

恋爱7年，关晓彤首聊和鹿晗的相处模式，感叹：我们生活挺简单的

UZI|《剑与远征》火爆海外，UZI也来代言，口碑与热度双收的手游佳作