mysql聯(lián)合索引的選擇性解析

本篇內容介紹了“聯(lián)合索引的選擇性解析”的有關(guān)知識，在實(shí)際案例的操作過(guò)程中，不少人都會(huì )遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學(xué)有所成！

通過(guò)下面的實(shí)驗來(lái)探討下聯(lián)合索引的使用選擇性：

版本：percona 5.6.27

create table test(

a int,

b int,

c int,

name varchar(32),

PRIMARY key(a),

key index_a_b_c(a,b,c)) ENGINE=INNODB

insert into test values(1,1,1,3,'leo');

insert into test values(2,1,2,1,'mike' );

insert into test values(3,1,3,1,'exo' );

insert into test values(4,1,2,3,'jhon' );

insert into test values(5,1,1,3,'lucy' );

insert into test values(6,2,2,3,'leo' );

insert into test values(7,3,1,2,'dv' );

insert into test values(8,2,1,3,'men' );

一：where條件對聯(lián)合索引的選擇性

mysql> explain select * from test where a=2;

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+

| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 5       | const |    2 | NULL  |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+

mysql> explain select * from test where a=2 and b=1;

+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------+

| id | select_type | table | type | possible_keys | key         | key_len | ref         | rows | Extra |

+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 10      | const,const |    1 | NULL  |

+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------+

mysql> explain select * from test where a=2 and b=2 and c=3;

+----+-------------+-------+------+---------------+-------------+---------+-------------------+------+-------+

| id | select_type | table | type | possible_keys | key         | key_len | ref               | rows | Extra |

+----+-------------+-------+------+---------------+-------------+---------+-------------------+------+-------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 15      | const,const,const |    1 | NULL  |

+----+-------------+-------+------+---------------+-------------+---------+-------------------+------+-------+

這三個(gè)是正常的使用方法,都走了索引

mysql> explain select * from test where a=2 and c=3;

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+

| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                 |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 5       | const |    2 | Using index condition |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+

1 row in set (0.00 sec)

如果把b漏掉，同樣走了索引

mysql> explain select * from test where b=2 and c=3;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

mysql> explain select * from test where c=3;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

如果把a漏掉，則不會(huì )走索引

結論：必須有聯(lián)合索引的第一個(gè)字段作為wehre條件

二：聯(lián)合索引排序選擇性

聯(lián)合索引的排序會(huì )按照（a,b,c）的順序進(jìn)行排序

測試數據在聯(lián)合索引的排序會(huì )是(1,1,3), (1,2,1), (1,2,3), (1,3,1), (2,1,3), (2,2,3), (3,1,2)順序存儲

mysql> explain select * from test where a=2 order by b;

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra       |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 5       | const |    2 | Using where |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+

mysql> explain select * from test where a=2 order by c;

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------------+

| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                       |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 5       | const |    2 | Using where; Using filesort |

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------------+

可以看出第二個(gè)Using filesort使用了臨時(shí)表排序了，效率低。從聯(lián)合索引的排序就可以知道當指定a的值的時(shí)候，這些值會(huì )按b的值排序，不是按c的值排序，故order by b不用再filesort排序，反之order by b需要重新排序。

所以select * from test where a=2 and b=2 order by c;不會(huì ) filesort排序

mysql> explain select * from test where a=2 and b=2 order by c;

+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------------+

| id | select_type | table | type | possible_keys | key         | key_len | ref         | rows | Extra       |

+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------------+

|  1 | SIMPLE      | test  | ref  | index_a_b_c   | index_a_b_c | 10      | const,const |    1 | Using where |

+----+-------------+-------+------+---------------+-------------+---------+-------------+------+-------------+

結論：當針對聯(lián)合索引中的某個(gè)字段進(jìn)行排序的時(shí)候，最優(yōu)的方法是有聯(lián)合索引排序字段之前的字段過(guò)濾條件