MySQL-优化之 index merge(索引合并)

mysql> show index from role_goods;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| role_goods |          0 | PRIMARY  |            1 | id          | A         |       22816 |     NULL | NULL   |      | BTREE      |         |               |
| role_goods |          1 | roleId   |            1 | roleId      | A         |        1521 |     NULL | NULL   | YES  | BTREE      |         |               |
| role_goods |          1 | goodsId  |            1 | goodsId     | A         |        1521 |     NULL | NULL   | YES  | BTREE      |         |               |
| role_goods |          1 | roleId_2 |            1 | roleId      | A         |        1901 |     NULL | NULL   | YES  | BTREE      |         |               |
| role_goods |          1 | roleId_2 |            2 | status      | A         |        4563 |     NULL | NULL   | YES  | BTREE      |         |               |
| role_goods |          1 | roleId_2 |            3 | number      | A         |       22816 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

上面有一个复合索引：roleId_2(roleId,status,number)，如果条件是： where roleId=xxx and number=xxx，那么此时只会使用到最左前缀roleId，而不会使用到 number 来进行过滤。因为它们中间存在一个字段 status 没有出现在where条件中。实验如下所示：

mysql> explain select * from role_goods where roleId=100000001 and status=1 and number=1 limit 1;
+----+-------------+------------+------+-----------------+----------+---------+-------------------+------+-------+
| id | select_type | table      | type | possible_keys   | key      | key_len | ref               | rows | Extra |
+----+-------------+------------+------+-----------------+----------+---------+-------------------+------+-------+
|  1 | SIMPLE      | role_goods | ref  | roleId,roleId_2 | roleId_2 | 23      | const,const,const |   13 | NULL  |
+----+-------------+------------+------+-----------------+----------+---------+-------------------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from role_goods where roleId=100000001 and status=1 limit 1;
+----+-------------+------------+------+-----------------+----------+---------+-------------+------+-------+
| id | select_type | table      | type | possible_keys   | key      | key_len | ref         | rows | Extra |
+----+-------------+------------+------+-----------------+----------+---------+-------------+------+-------+
|  1 | SIMPLE      | role_goods | ref  | roleId,roleId_2 | roleId_2 | 14      | const,const |   13 | NULL  |
+----+-------------+------------+------+-----------------+----------+---------+-------------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from role_goods where roleId=100000001 and number=1 limit 1;
+----+-------------+------------+------+-----------------+--------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys   | key    | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+-----------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | role_goods | ref  | roleId,roleId_2 | roleId | 9       | const |   14 | Using where |
+----+-------------+------------+------+-----------------+--------+---------+-------+------+-------------+
1 row in set (0.01 sec)
mysql> explain select * from role_goods ignore index(roleId) where roleId=100000001 and number=1 limit 1;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | role_goods | ref  | roleId_2      | roleId_2 | 9       | const |   14 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)

可以看到 key_len 的变化：

显然最后一个查询仅仅使用到符合索引中的 roleId, 没有使用到 number. number使用在了 index conditon(也就是索引的push down技术)

注意最左前缀，并不是是指：一定要按照各个字段出现在where中的顺序来建立复合索引的。比如

where status=2 and roleId=xxx and number = xxx

该条件建立符合索引，我们并不需要按照status,roleId，number它们出现的顺序来建立索引：

alter table role_goods add index sin(status,roleId,number)

这是对最左前缀极大的误解。因为 where status=2 and roleId=xxx and number = xxx 和 where roleId=xxx and number = xxx and status=2它们是等价的。复合索引，哪个字段放在最前面，需要根据哪个字段经常出现在where条件中，哪个字段的选择性最好来判断的。

进一步可以参考的文章：

http://www.orczhou.com/index.php/2013/01/mysql-source-code-query-optimization-index-merge/

http://www.cnblogs.com/nocode/archive/2013/01/28/2880654.html

MySQL-优化之 index merge(索引合并)

标签：需要   进一步   algorithm   amp   --   class   cond   any   combine