加了索引,mysql查询就一定会用吗?

本文探讨了mysql中加了索引却不被使用的情况,包括查询条件涉及函数、隐式类型转换、特殊修饰符如%和OR、以及索引优化器的选择。索引优化器根据区分度估算最佳执行方案,基数小可能导致不走索引。通过force index可强制使用特定索引。了解这些原理有助于避免查询性能问题。

 

小白白跑去鹅厂面试,面试官提出了一个很实际的问题: mysql增加索引,那些情况会失效呢?谈一下实际工作中遇到的情况。我们的小白白又抛出了白氏秘籍:用不用索引,找DBA小姐姐!啊?这是你面试哈,还是DBA小姐姐面试呀。(更多信息,请关注微信公众号: 白白家族)


一 概述

日常处理mysql问题中,往往通过增加索引来提高查询速度,但在有些情况下,执行过程中并没有按照我们的预期结果执行,也就是说,即使字段加了索引,但现实也没有使用到,到底是什么地方出了差错,以下我们将一探究竟。


二 实验表结构声明:

我们将对以下表结构进行实际案例分析

CREATETABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `age` int(11) DEFAULT 0,  `name` varchar(16) DEFAULT "",  key idx_age (`age`),  key idx_name (`name`),  PRIMARY KEY (`id`)) ENGINE=InnoDB;

三 Mysql不走索引归类以及详细解析

  根据实验表做具体case分析,归纳为以下几点:

1. 查询条件在索引列上使用函数操作,或者运算的情况

例如以下case是不走索引的:

 

explain select * from student where abs(age) =18;explain select * from student where age + 1=18;

2. 查询条件字符串和数字之间的隐式转换

例如:name与age分别做字符串/数字(88)的隐式转换;

以下case走索引情况:

explain select * from student where name =’88’; explain select * from student where age='88'; explain select * from student where age =88;

以下case不走索引情况:

explain select * from student where name=88;

 

3. 特殊修饰符 %%, Or 将不走索引

explain select * from student where name like'%name%' ;explain select * from student where name ='name' or age = 18;

4. 索引优化器选择最优的索引

这一点最重要,索引到底用不用,不是列加了索引就一定会用,而是根据索引优化器来决定。

    索引优化器的存在,就是找到一个索引扫描行数最少的方案去执行语句。那么扫描行数怎么来判断的?是逐行统计数据表的数据吗?其实并不是,而是根据统计信息来估算的值。这个统计信息就是我们常说的索引的“区分度”。

    显然,一个索引上不同的值越多,这个索引的区分度就越好。我们把一个索引上不同的值的个数,称之为“索引基数”。也就是说,基数越大,索引的区分度就越好,执行查询的行数就越少。如何查看索引基数呢?使用 show index from 表名,cardinality字段显示的就是索引的基数。

 

扩展:MySQL 是怎样得到索引基数的呢?不感兴趣的小伙伴可以飘过啦。

索引基数 = 采样统计*页数。采样统计就是避免把整张表取出来一行行统计做精准计算,以免消耗系统性能。在采样统计时,InnoDB默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。统计信息不是固定不变的,他会随着数据表的变化而变化。当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。

 

索引优化器实例一:

   经常听人说,执行<>语句时,不走索引,今天我们将看一看实际执行情况,还是那句话,到底走不走,我们说了不算,还是索引优化器说了算:

 看截图 ,就会发现 <> 其实是走了索引。

    为什么会出现这样的情景呢?因为 student表中10W多条数据的值全都是'name',索引基数太小,所以在执行<>'1name'查询时,实际上要查询条数为10多W条,如果走了name字段索引,其实和全表查询没什么区别,况且,执行name字段索引,最终还是要转换为主键索引(二级索引查询都会转换为主键查询),所以索引优化器的优化结果是不走name索引。然而在执行<>'name'查询时,优化器优化结果是走name索引,因为,<>'name'的查询行数很小,大部分条数name字段的值都是'name'。

 

索引优化器实例二:

   同理,前缀like匹配是走索引,但是,以下却展示了不一样的结果:

    为什么会这样呢?请小伙伴们参考实例一仔细思考一下。
 

如何指定优化器执行固定的索引:

   索引优化器的存在,我们就没办法指定强制走我们指定的索引?

   答案就是通过 force index强制来实现,执行语句和分析结果如下图所示


 

四 总结以及实际应用

    实际应用中,应该牢记上述索引优化的原则,比如在实际工作中,由于索引优化器选错索引,导致数据查询缓慢,阻塞线上业务,而当时的解决办法,就是上述文章的分析过程,以及采用force 强制索引才解决的,前车之鉴,希望广大读者避免踩坑。

 更多信息请关注 微信公众号 :  白白家族
 或扫描白白家族公众号二维码:

 

<think>嗯,用户问的是MySQL查询语句命中索引是否就一定快。这个问题看起来简单,但其实需要仔细分析。首先,我得回想一下索引的基本原理,以及在实际应用中可能遇到的各种情况。 索引的作用主要是快数据检索速度,通过减少全表扫描的需要。通常来说,命中索引确实能提高查询效率,但用户的问题是在所有情况下都成立吗?显然不是,所以需要找出例外情况。 首先想到的是索引的类型。比如,覆盖索引可以直接从索引中获取数据,不需要回表,这样效率很高。但如果查询需要回表,特别是当需要回表的行数很多时,可能会导致性能下降。比如,当查询返回大量数据时,使用索引可能反而更慢,因为每次回表都是一次磁盘I/O操作,累积起来反而比全表扫描更耗时。 然后,索引的选择性也是一个关键因素。如果索引列的值重复率很高,比如性别字段,这样的索引选择性低,即使命中了索引数据库可能仍然需要扫描大量的行,效率提升有限。这时候优化器可能会选择全表扫描而不是使用索引。 还有索引碎片的问题。如果表中的数据经常增删改,导致索引碎片化,这时候即使命中索引查询效率也会降低,因为物理存储不连续,增了磁盘I/O的次数。 另外,联合索引的字段顺序也会影响效率。如果查询条件没有按照最左前缀原则使用联合索引,可能导致索引失效或者只能部分使用,这样效果可能不如预期。 还有数据量的问题。当数据量非常小时,全表扫描可能更快,因为使用索引需要额外的查找开销。优化器在这种情况下可能会选择不使用索引。 此外,查询的复杂性也是一个因素。如果查询涉及到多个条件,其中某些条件命中了索引,但其他条件需要额外的过滤,这时候整体效率可能仍然不高。 比如,用户可能有一个包含age和name的联合索引,但查询使用了age范围查询,而name是等值查询。这种情况下,索引使用可能不如预期高效,因为范围查询会导致后续的索引列无法有效利用。 还有,隐式类型转换或者函数操作可能导致索引失效,比如在WHERE子句中对索引使用函数,这会让MySQL无法使用索引,从而降低效率。不过用户的问题是关于命中索引的情况,所以这种情况可能不算在内,但需要确认是否属于命中索引的范围。 再想,如果索引列的数据分布不均匀,比如某些值特别集中,这时候即使使用索引,也可能需要扫描大量数据页,效率不高。比如,某个状态字段有90%的值都是“完成”,这时候查询状态为“完成”的记录,即使有索引,可能也不如全表扫描快。 此外,多表连接时,即使每个表都使用索引,但如果连接顺序不当或者连接条件复杂,也可能导致性能问题。比如,嵌套循环连接中,驱动表的选择不当,可能导致大量磁盘I/O。 还有,索引的维护成本。虽然查询时命中索引可能快,但写操作(INSERT、UPDATE、DELETE)时需要维护索引,可能会影响整体性能,但这属于另一个层面的问题,用户的问题可能更关注查询时的性能。 总结下来,需要从索引类型、数据量、索引选择性、索引碎片、查询条件、数据分布、优化器选择等多个方面来分析。然后把这些点组织成用户容易理解的回答,用例子说明每种情况,帮助用户理解为什么有时候即使命中索引查询也不一定快。</think>MySQL查询命中索引不一定保证查询速度快,需结合以下场景综合分析: 1. **覆盖索引与非覆盖索引** - ✅ 覆盖索引场景:`SELECT id FROM users WHERE age=25`(age有索引索引直接存储id和age,无需回表,速度极快 - ❌ 非覆盖索引场景:`SELECT * FROM users WHERE age=25`(age有索引) 需根据索引查主键后回表查完整数据,当命中10万行时产生10万次磁盘IO 2. **索引选择性** - 高选择性场景:`SELECT * FROM orders WHERE order_id='123'`(唯一索引) 精准定位1条记录,效率极高 - 低选择性场景:`SELECT * FROM users WHERE gender='F'`(gender有索引) 当表中80%都是女性时,索引扫描可能比全表扫描更慢 3. **数据分布特征** - 极端案例:`SELECT * FROM logs WHERE status=1`(status有索引) 当status=1占99%数据时,优化器可能选择全表扫描 4. **索引碎片影响** 某表经历大量DELETE操作后,索引碎片率达40%: ```sql ALTER TABLE orders FORCE INDEX(idx_created_at); SELECT * FROM orders WHERE created_at > '2024-01-01'; ``` 实际查询时间反而比全表扫描慢2倍 5. **联合索引使用** 存在联合索引`(province,city,district)`: - 高效场景:`WHERE province='浙江' AND city='杭州'` - 低效场景:`WHERE city='杭州'`(无法使用最左前缀) 6. **优化器误判** 当表统计信息过期时: ```sql ANALYZE TABLE users; -- 更新统计信息前 SELECT * FROM users WHERE age BETWEEN 20 AND 25; ``` 实际有50万数据符合,但统计信息显示预估1万行,导致错误选择索引 7. **范围查询陷阱** ```sql SELECT * FROM products WHERE price > 100 ORDER BY create_time DESC LIMIT 20; ``` 存在`(price)`索引但无`(price,create_time)`联合索引时,需排序操作可能更慢 **优化建议:** 1. 定期执行`ANALYZE TABLE`更新统计信息 2. 对范围查询+排序场景建立复合索引 3. 使用`FORCE INDEX`前先通过`EXPLAIN`验证 4. 对低选择性字段谨慎建立索引 5. 定期执行`OPTIMIZE TABLE`整理索引碎片 实际案例:某电商订单表`(order_status)`索引查询反而变慢,最终解决方案是: ```sql ALTER TABLE orders ADD INDEX idx_status_created(status,created_at), DROP INDEX idx_status; ``` 通过组合查询字段和排序字段建立联合索引,使查询耗时从1.2s降至0.02s。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值