现在我们有个查询语句:select * from pyment where staff_id = 2 and customer_id = 584;
是应该创建一个(staff_id,customer_id)索引还是应该颠倒一下顺序。
此时,有i一个方法,我们可以看下staff_id和customer_id的全局选择性:
mysql> select count(distinct staff_id)/count(*) as staff_if_selectivity,
->count(distanct customer_id)/count(*) as customer_id_selectivity,
->count(*)
******************************************************************************
staff_id_selectivity:0.0001
customer_id_selectivity:0.0373
cout(*):16049
customer_id的选择性更高,所以答案是将其作为索引列的第一列:
mysql>alert table payment add key(customer_id,staff_id);
.具体的场景中,我们还需要根据,到底那一列使用频率较高。让复合索引同时提供单列索引的效果。要结合实际情况去做选择。