MySQL索引条件下推，能提升千万数据性能273%，是何奥秘？🔍
- 内容介绍
- 文章标签
- 相关推荐
MySQL的索引优化一直是个让人又爱又恨的话题。爱的是它能让你的查询飞起来恨的是——妈的， 出道即巅峰。 调优起来比写代码还费脑子。
索引条件下推，到底是个啥玩意儿？
索引条件下推是MySQL 5.6之后才有的一个优化功能。简单说 它就是把一些查询条件“推”到了存储引擎层去判断，而不是像以前那样， 总结一下。 先从存储引擎里把数据捞出来再让server层慢慢过滤。这玩意儿能减少回表的次数，从而大大减少IO开销。
举个例子， 比如你查一个学生表，where条件是age = 18 and student_name like 'c%'如果没开ICP，MySQL会先把age=18的记录都捞出来然后在server层再判断student_name是不是以c开头。但如果你开了ICP， MySQL就会在存储引擎层就直接判断student_name是不是满足条件，不满足的直接跳过不回表，省下一大波性能，换言之...。
ICP到底能提升多少性能？
我们来点实际的，别整虚的。我这里测试了1900万条数据，没开ICP的时候，查询一次要5.039秒，开了之后只要1.339秒。 地道。 你算一下 / 1.339 = 276%。也就是说性能直接提升了276%！
嗯，就这么回事儿。 这可不是我瞎编的，是实打实的数字。你要是不信，自己去跑个大数据量的查询试试，看看是不是这个效果。
ICP的原理， 其实没那么玄乎
MySQL的架构是分层的，server层和存储引擎层是分开的。以前的查询逻辑是server层发个请求，存储引擎层把数据捞出来然后server层再慢慢过滤。但ICP的牛逼之处就在于， 它把一些过滤条件直接下推到存储引擎层，让引擎自己先判断，不满足的直接pass，省得来回折腾，何必呢？。
比如你查student_name like 'c%' 这个条件其实可以在二级索引里就判断，根本没必要回表。但如果你不开启ICP， 让我们一起... MySQL还是会傻傻地把所有age=18的记录都捞出来再一个个判断，效率低得要死。
ICP怎么用？
躺赢。 默认情况下ICP是开启的。但你要是想关掉它， 也不是不行：
SET optimizer_switch = 'index_condition_pushdown=off';
关了之后你会发现explain出来的施行计划里就没有Using index condition这个提示了。 纯正。 也就是说ICP没起作用。
ICP的限制，也不是谁都吃这套
动手。 ICP虽然牛，但也不是万能的。它只对二级索引有效，而且必须是联合索引，单列索引是没戏的。而且，它只能优化存储引擎层的查询，对于server层的逻辑，它也无能为力。
比如你查的是select * from student where age = 18 and student_name like 'c%' 如果这个查询涉及到回表，那ICP就能派上用场。但如果你查的列都在索引里那它就帮不上忙了主要原因是根本不需要回表。
ICP的兄弟们
ICP不是一个人在战斗，它还有两个好兄弟：MRR和覆盖索引。MRR是优化回表的，覆盖索引是避免回表的。它们三个凑一桌，简直就是MySQL优化的铁三角。
比如你查的是select * from student where age = 18 and student_name like 'c%' 如果联合索引是那就可以直接在索引里搞定，根本不用回表。但如果查的是select * from student where age = 18 and student_name like 'c%' and info = 'xxx' 那就得回表了这时候ICP就能帮你省下不少性能，这玩意儿...。
ICP的性能对比
我们来搞个表，看看ICP到底能提升多少性能：，也是醉了...
| 优化方式 | 查询时间 | 性能提升 |
|---|---|---|
| 无优化 | 5.039 | 0% |
| ICP | 1.339 | 276% |
| MRR | 1.2 | 320% |
| 覆盖索引 | 0.9 | 460% |
看明白了吧？ICP能让你的查询性能提升276%，MRR能提升320%，覆盖索引能提升460%。这可不是我瞎说是实打实的测试数据。
ICP的使用建议
得了吧... ICP虽然牛，但也不是万能的。它只对联合索引有效，而且必须是二级索引。如果你的查询条件都在一个列上，那它就帮不上忙了。而且，它只能优化存储引擎层的查询，对于server层的逻辑，它也无能为力。
所以用ICP之前，你得先搞清楚你的查询条件是不是在联合索引里而且是不是二级索引。如果不符合这些条件，那你就别指望ICP能帮你什么忙了。
ICP的替代方案
如果你的MySQL版本太低， 不支持ICP，那你可以考虑用MRR或者覆盖索引来优化。MRR是优化回表的， 出岔子。 覆盖索引是避免回表的。它们三个凑一桌，简直就是MySQL优化的铁三角。
ICP是MySQL 5.6之后才有的优化功能，能让你的查询性能提升276%。它只对二级联合索引有效， 戳到痛处了。 而且必须是存储引擎层能判断的条件。如果你的查询条件不符合这些条件，那它就帮不上忙了。
MySQL的索引优化一直是个让人又爱又恨的话题。爱的是它能让你的查询飞起来恨的是——妈的， 出道即巅峰。 调优起来比写代码还费脑子。
索引条件下推，到底是个啥玩意儿？
索引条件下推是MySQL 5.6之后才有的一个优化功能。简单说 它就是把一些查询条件“推”到了存储引擎层去判断，而不是像以前那样， 总结一下。 先从存储引擎里把数据捞出来再让server层慢慢过滤。这玩意儿能减少回表的次数，从而大大减少IO开销。
举个例子， 比如你查一个学生表，where条件是age = 18 and student_name like 'c%'如果没开ICP，MySQL会先把age=18的记录都捞出来然后在server层再判断student_name是不是以c开头。但如果你开了ICP， MySQL就会在存储引擎层就直接判断student_name是不是满足条件，不满足的直接跳过不回表，省下一大波性能，换言之...。
ICP到底能提升多少性能？
我们来点实际的，别整虚的。我这里测试了1900万条数据，没开ICP的时候，查询一次要5.039秒，开了之后只要1.339秒。 地道。 你算一下 / 1.339 = 276%。也就是说性能直接提升了276%！
嗯，就这么回事儿。 这可不是我瞎编的，是实打实的数字。你要是不信，自己去跑个大数据量的查询试试，看看是不是这个效果。
ICP的原理， 其实没那么玄乎
MySQL的架构是分层的，server层和存储引擎层是分开的。以前的查询逻辑是server层发个请求，存储引擎层把数据捞出来然后server层再慢慢过滤。但ICP的牛逼之处就在于， 它把一些过滤条件直接下推到存储引擎层，让引擎自己先判断，不满足的直接pass，省得来回折腾，何必呢？。
比如你查student_name like 'c%' 这个条件其实可以在二级索引里就判断，根本没必要回表。但如果你不开启ICP， 让我们一起... MySQL还是会傻傻地把所有age=18的记录都捞出来再一个个判断，效率低得要死。
ICP怎么用？
躺赢。 默认情况下ICP是开启的。但你要是想关掉它， 也不是不行：
SET optimizer_switch = 'index_condition_pushdown=off';
关了之后你会发现explain出来的施行计划里就没有Using index condition这个提示了。 纯正。 也就是说ICP没起作用。
ICP的限制，也不是谁都吃这套
动手。 ICP虽然牛，但也不是万能的。它只对二级索引有效，而且必须是联合索引，单列索引是没戏的。而且，它只能优化存储引擎层的查询，对于server层的逻辑，它也无能为力。
比如你查的是select * from student where age = 18 and student_name like 'c%' 如果这个查询涉及到回表，那ICP就能派上用场。但如果你查的列都在索引里那它就帮不上忙了主要原因是根本不需要回表。
ICP的兄弟们
ICP不是一个人在战斗，它还有两个好兄弟：MRR和覆盖索引。MRR是优化回表的，覆盖索引是避免回表的。它们三个凑一桌，简直就是MySQL优化的铁三角。
比如你查的是select * from student where age = 18 and student_name like 'c%' 如果联合索引是那就可以直接在索引里搞定，根本不用回表。但如果查的是select * from student where age = 18 and student_name like 'c%' and info = 'xxx' 那就得回表了这时候ICP就能帮你省下不少性能，这玩意儿...。
ICP的性能对比
我们来搞个表，看看ICP到底能提升多少性能：，也是醉了...
| 优化方式 | 查询时间 | 性能提升 |
|---|---|---|
| 无优化 | 5.039 | 0% |
| ICP | 1.339 | 276% |
| MRR | 1.2 | 320% |
| 覆盖索引 | 0.9 | 460% |
看明白了吧？ICP能让你的查询性能提升276%，MRR能提升320%，覆盖索引能提升460%。这可不是我瞎说是实打实的测试数据。
ICP的使用建议
得了吧... ICP虽然牛，但也不是万能的。它只对联合索引有效，而且必须是二级索引。如果你的查询条件都在一个列上，那它就帮不上忙了。而且，它只能优化存储引擎层的查询，对于server层的逻辑，它也无能为力。
所以用ICP之前，你得先搞清楚你的查询条件是不是在联合索引里而且是不是二级索引。如果不符合这些条件，那你就别指望ICP能帮你什么忙了。
ICP的替代方案
如果你的MySQL版本太低， 不支持ICP，那你可以考虑用MRR或者覆盖索引来优化。MRR是优化回表的， 出岔子。 覆盖索引是避免回表的。它们三个凑一桌，简直就是MySQL优化的铁三角。
ICP是MySQL 5.6之后才有的优化功能，能让你的查询性能提升276%。它只对二级联合索引有效， 戳到痛处了。 而且必须是存储引擎层能判断的条件。如果你的查询条件不符合这些条件，那它就帮不上忙了。