【MySQL】性能优化之 index merge (1)

一 序言介绍 MySQL 5.0 版本之前，每条个表在查询时 只能使用一个索引，有些不知道此功能限制的开发总是在一个表上创建很多单独列的索引，以便当where条件中含有这些列是能够走上索引。但是这样并不是一个好方法，或者是“好心办坏事”，索引能够提供查询速度，但是也能给日常维护和IUD 操作带来维护成本。 MySQL 5.0 和之后的版本推出了一个新特性---索引合并优化（Index merge optimization），它让MySQL可以在查询中对一个表使用多个索引,对它们同时扫描，并且合并结果。 二 使用场景 Index merge算法有 3 种变体：例子给出最基本常见的方式： 2.1 对 OR 取并集  In this form, where the index has exactly N parts (that is, all index parts are covered): 1 key_part1=const1 AND key_part2=const2 ... AND key_partN=constN 2 Any range condition over a primary key of an InnoDB table. 3 A condition for which the Index Merge method intersection algorithm is applicable. root@odbsyunying 02:34:41 explain  select count(*) as cnt from `order` o  WHERE  o.order_status = 2  or  o.buyer_id=1979459339672858 \G    *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: o          type: index_merge possible_keys: buyer_id,order_status           key: order_status,buyer_id       key_len: 1,9           ref: NULL          rows: 8346         Extra: Using union(order_status,buyer_id); Using where 1 row in set (0.00 sec) 当 where 条件中 含有对两个索引列的 or 交集操作时 ，执行计划会采用 union merge 算法。 2.2 对 AND 取交集： ”In this form, where the index has exactly N parts (that is, all index parts are covered): key_part1=const1 AND key_part2=const2 ... AND key_partN=constN Any range condition over a primary key of an InnoDB table.“ root@odbsyunying 02:33:59 explain  select count(*) as cnt from `order` o  WHERE  o.order_status = 2  and o.buyer_id=1979459339672858 \G                        *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: o          type: index_merge possible_keys: buyer_id,order_status           key: buyer_id,order_status       key_len: 9,1           ref: NULL          rows: 1         Extra: Using intersect(buyer_id,order_status); Using where; Using index 1 row in set (0.00 sec) 当where条件中含有索引列的and操作时，执行计划会采用intersect 并集操作。 2. 3 对 AND 和 OR 的组合取并集。 root@odbsyunying 02:42:19 explain  select count(*) as cnt from `order` o  WHERE  o.order_status 2  or  o.buyer_id=1979459339672858 \G     *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: o          type: index_merge possible_keys: buyer_id,order_status           key: order_status,buyer_id       key_len: 1,9           ref: NULL          rows: 4585         Extra: Using sort_union(order_status,buyer_id); Using where 1 row in set (0.00 sec) The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows. 三 Index merge的 限制 MySQL在5.6.7之前，使用index merge有一个重要的前提条件：没有range可以使用。这个限制降低了MySQL index merge可以使用的场景。理想状态是同时评估成本后然后做出选择。因为这个限制，就有了下面这个已知的bad case ： SELECT * FROM t1 WHERE (goodkey1 10 OR goodkey2 20) AND badkey 优化器可以选择使用goodkey1和goodkey2做index merge，也可以使用badkey做range。因为上面的原则，无论goodkey1和goodkey2的选择度如何，MySQL都只会考虑range，而不会使用index merge的访问方式。这是一个悲剧...（5.6.7版本针对此有修复)
mysql index cond push down 索引下推 mysql 索引下推在8.0的代码中进行了重构，目前的逻辑比较清晰。本文对该代码进行相关的分析。本文介绍的代码为mysql-8.0.21版本。
[MySQL] 联合索引与using index condition [MySQL] 联合索引与using index condition1.测试联合索引的最左原则的时候, 发现了5.6版本后的新特性Index Condition Pushdown 2.含义就是存储引擎层根据索引尽可能的过滤数据,然后在返回给服务器层根据where其他条件进行过滤
MySQL Index Extensions Index Extensions含义 MySQL5.6开始 InnoDB可以通过主键自动扩展二级索引的功能称为Index Extensions，即二级索引除了存储本列索引的key值外，还存储着主键列key值。
MySQL · myrocks · clustered index特性 Cluster index介绍 最近在RDS MyRocks中，我们引入了一个重要功能，二级聚集索引(secondary clustering index). 我们知道innodb和rocksdb引擎的主键就是clustered index。