MySQL ICP优化：减少IO操作与使用场景详解

【mysql】关于ICP、MRR和BKA等特性 ICP（Index Condition Pushdown，索引条件推下）是MySQL 5.6及以后版本的一项优化技术，它旨在提高查询性能。在MySQL早期版本中，存储引擎会在索引中定位数据后将结果传递给服务器层，服务器再进行WHERE条件的过滤。引入ICP后，如果WHERE条件可以利用索引，MySQL会将过滤操作移至存储引擎层执行，减少从基础表（如InnoDB的辅助索引）的读取次数，从而降低I/O操作。启用ICP可以通过设置optimizer_switch参数为"index_condition_pushdown=on"。ICP主要适用于辅助索引，并且在MySQL 5.6中仅限于MyISAM、InnoDB和NDBcluster存储引擎，5.7.3及以上版本支持分区表的ICP。 在使用场景中，比如对一个带有多个条件的查询（如`SELECT * FROM people WHERE a = '12345' AND b LIKE '%xx%' AND c LIKE '%yy%'`），如果不使用ICP，服务器将首先通过二级索引找到a为'12345'的行，然后在服务器层过滤其他条件。而开启ICP后，`b LIKE '%xx%' AND c LIKE '%yy%'`的条件将在索引内处理，进一步减少数据传输。 ICP的特点包括： 1. 支持范围、ref、eq_ref和ref_or_null类型的访问数据方法。 2. 对于InnoDB的聚集索引，由于完整记录已经加载到InnoDB Buffer，ICP并不能降低I/O操作，因为数据已经在内存中。 3. 当SQL使用覆盖索引但只检索部分数据时，ICP的效果受限，因为没有足够的数据筛选。 另一种重要的优化技术是Multi-Range Read (MRR)，虽然题目部分并未详细阐述，但通常MRR是指InnoDB存储引擎的一种特性，它允许一次从磁盘读取多个连续的行，提高了数据扫描的效率。这在涉及范围查询或预读取操作时特别有效，减少了单独请求所需的时间。 总结起来，ICP和MRR都是MySQL中提升查询性能的关键工具，通过合理利用这些特性，可以显著改善查询响应时间并降低系统资源消耗。然而，它们的应用需根据具体场景和数据结构来决定是否启用，以及如何配置以达到最佳效果。同时，对于不同的存储引擎，如InnoDB，可能有特定的限制和优化策略。