面试题：MySQL索引优化中的索引下推技术及优化策略

未使用ICP时：在没有ICP的情况下，MySQL从存储引擎获取数据行，然后将数据行返回给MySQL Server层进行过滤条件的判断。例如，表employees有索引idx_name_age（name, age），查询SELECT * FROM employees WHERE name LIKE 'J%' AND age > 30;，存储引擎根据索引idx_name_age找到满足name LIKE 'J%'的索引记录，然后根据索引记录回表获取整行数据，将这些数据行传递给Server层，Server层再判断age > 30这个条件，过滤出符合要求的行。
使用ICP时：当启用ICP时，如果部分过滤条件可以使用索引中的列，MySQL Server会将这部分过滤条件下推到存储引擎层。存储引擎在通过索引获取数据时，就可以利用这些下推的过滤条件对索引记录进行过滤，只有满足下推过滤条件的索引记录才会被回表获取整行数据。还是上述查询，MySQL Server会将age > 30这个条件下推到存储引擎，存储引擎在扫描idx_name_age索引时，对于满足name LIKE 'J%'的索引记录，先判断age > 30，只有满足这个条件的索引记录才会回表获取整行数据，减少了回表的次数。

索引列包含部分过滤条件：当查询的过滤条件部分可以由索引列覆盖时，ICP能发挥较大作用。例如上述employees表的查询，如果name和age列都在索引中，且有对这两列的过滤条件，ICP就可以减少回表操作。
范围查询结合其他条件：在范围查询（如>、<、BETWEEN等）与其他条件组合的场景下，ICP效果显著。比如SELECT * FROM products WHERE category = 'electronics' AND price > 100;，如果有(category, price)的联合索引，ICP可以在索引扫描时就过滤掉不符合price > 100的记录，减少回表。

假设有一个orders表，包含order_id、customer_id、order_date、total_amount等列。我们经常会进行这样的查询：

SELECT * FROM orders 
WHERE customer_id = 123 
  AND order_date BETWEEN '2023 - 01 - 01' AND '2023 - 12 - 31' 
  AND total_amount > 1000;

联合索引：创建联合索引idx_customer_date_amount(customer_id, order_date, total_amount)。这样对于上述查询，MySQL可以使用该联合索引，并且可以将order_date BETWEEN '2023 - 01 - 01' AND '2023 - 12 - 31'和total_amount > 1000这两个条件下推到存储引擎层。存储引擎在扫描索引时，先根据customer_id = 123定位到相关索引记录，然后利用下推的条件过滤不符合日期范围和金额条件的记录，减少回表次数。
前缀索引：如果customer_id是一个很长的字符串类型，为了减少索引空间占用，可以考虑使用前缀索引。例如创建前缀索引idx_customer_id_prefix(customer_id(10))。但要注意前缀索引的选择性，确保前缀长度能够区分大部分数据。在上述查询中，如果使用前缀索引，ICP同样可以将日期和金额的过滤条件下推，不过可能由于前缀索引选择性的问题，效果可能不如完整的联合索引，但在空间占用和查询性能之间可以做一个平衡。

星途面试题库