2018-06-16 16:02:17

index merge

theory

index merge is that mysql sometimes(depends on some) could use seperate index on where condition at storage level, then can scan them simultaneously and them merge the result

  • where clause could affect optimizer whether to use index merge method, especially when intersect on index merge method
  • whether to use the method, I think the cardinatity of a index is very important, the higher the cardinatity the lower the posibility to use the method, because the purpose index merge is to reduce reduncdant rows retrieve, if optimizer think it can not satify this requirement, why use it?
  • the access method merges index scans from a single table; it does not merge scans across multiple tables.
  • type, key, key_len, rows will dispaly different in EXPLAIN when optimizer decide to use this method
  • it's more easier to judge a or opration whether the optimizer decide to use this method than a and opration

some example explain the theory

mysql> show index from lowcardinaliy;
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| lowcardinaliy |          0 | PRIMARY    |            1 | id          | A         |     4505178 |     NULL | NULL   |      | BTREE      |         |               |
| lowcardinaliy |          1 | idx_uid    |            1 | uid         | A         |        2609 |     NULL | NULL   |      | BTREE      |         |               |
| lowcardinaliy |          1 | idx_char25 |            1 | char25      | A         |        2609 |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> explain select * from lowcardinaliy where uid in (2142140633,1829213) or char25='zfqQXGkVLYnNTsHihoFgRwPa';
+----+-------------+---------------+------------+-------------+--------------------+--------------------+---------+------+-------+----------+---------------------------------------------------+
| id | select_type | table         | partitions | type        | possible_keys      | key                | key_len | ref  | rows  | filtered | Extra                                             |
+----+-------------+---------------+------------+-------------+--------------------+--------------------+---------+------+-------+----------+---------------------------------------------------+
|  1 | SIMPLE      | lowcardinaliy | NULL       | index_merge | idx_uid,idx_char25 | idx_uid,idx_char25 | 4,27    | NULL | 18747 |   100.00 | Using sort_union(idx_uid,idx_char25); Using where |
+----+-------------+---------------+------------+-------------+--------------------+--------------------+---------+------+-------+----------+---------------------------------------------------+

mysql> explain select * from lowcardinaliy where uid=2142140633 or char25='hello';
+----+-------------+---------------+------------+-------------+--------------------+--------------------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table         | partitions | type        | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+---------------+------------+-------------+--------------------+--------------------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | lowcardinaliy | NULL       | index_merge | idx_uid,idx_char25 | idx_uid,idx_char25 | 4,27    | NULL | 4073 |   100.00 | Using union(idx_uid,idx_char25); Using where |
+----+-------------+---------------+------------+-------------+--------------------+--------------------+---------+------+------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from lowcardinaliy where uid=2142140633 and char25='hello';
+----+-------------+---------------+------------+------+--------------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys      | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+--------------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | lowcardinaliy | NULL       | ref  | idx_uid,idx_char25 | idx_char25 | 27      | const |    1 |     5.00 | Using where |
+----+-------------+---------------+------------+------+--------------------+------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from lowcardinaliy where uid=2142140633 and char25  like 'h%';
+----+-------------+---------------+------------+------+--------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys      | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+--------------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | lowcardinaliy | NULL       | ref  | idx_uid,idx_char25 | idx_uid | 4       | const | 4322 |     8.94 | Using where |
+----+-------------+---------------+------------+------+--------------------+---------+---------+-------+------+----------+-------------+

mysql> explain select * from lowcardinaliy where uid=2142140633 and char25='zfqQXGkVLYnNTsHihoFgRwPa';
+----+-------------+---------------+------------+-------------+--------------------+--------------------+---------+------+------+----------+--------------------------------------------------+
| id | select_type | table         | partitions | type        | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                                            |
+----+-------------+---------------+------------+-------------+--------------------+--------------------+---------+------+------+----------+--------------------------------------------------+
|  1 | SIMPLE      | lowcardinaliy | NULL       | index_merge | idx_uid,idx_char25 | idx_uid,idx_char25 | 4,27    | NULL |    8 |   100.00 | Using intersect(idx_uid,idx_char25); Using where |
+----+-------------+---------------+------------+-------------+--------------------+--------------------+---------+------+------+----------+--------------------------------------------------+

mysql> explain select * from lowcardinaliy where uid in (2142140633,1829213) and char25='zfqQXGkVLYnNTsHihoFgRwPa';
+----+-------------+---------------+------------+------+--------------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys      | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+--------------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | lowcardinaliy | NULL       | ref  | idx_uid,idx_char25 | idx_char25 | 27      | const | 8862 |     0.20 | Using where |
+----+-------------+---------------+------------+------+--------------------+------------+---------+-------+------+----------+-------------+


mysql> explain select * from lowcardinaliy where uid=2142140633 and char25 like 'zfqQXGkVLYnNTsHihoFgRw%';
+----+-------------+---------------+------------+------+--------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys      | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+--------------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | lowcardinaliy | NULL       | ref  | idx_uid,idx_char25 | idx_uid | 4       | const | 4970 |     0.18 | Using where |
+----+-------------+---------------+------------+------+--------------------+---------+---------+-------+------+----------+-------------+

Permanent link of this article:http://nulls.cc/post/index_merge_in_mysql

-- EOF --