在MySQL数据库中，创建复合索引的时候，不知道在创建过程中哪个列在前面，哪个列该在后面，用以下方式即可：
select count(distinct first_name)/count(*) as first_name_selectivity,
count(distinct last_name)/count(*) as last_name_selectivity,
count(*)
from actor\G
mysql> select count(distinct first_name)/count(*) as first_name_selectivity,
-> count(distinct last_name)/count(*) as last_name_selectivity,
-> count(*)
-> from actor\G
*************************** 1. row ***************************
first_name_selectivity: 0.6400
last_name_selectivity: 0.6050
count(*): 200
1 row in set (0.01 sec)
first_name_selectivity: 0.6400，因此 first_name 列的选择性更高，所以答案将其作为索引列的第一列：
mysql> alter table yoon add key (first_name,last_name);
select count(distinct first_name)/count(*) as first_name_selectivity,
count(distinct last_name)/count(*) as last_name_selectivity,
count(*)
from actor\G
mysql> select count(distinct first_name)/count(*) as first_name_selectivity,
-> count(distinct last_name)/count(*) as last_name_selectivity,
-> count(*)
-> from actor\G
*************************** 1. row ***************************
first_name_selectivity: 0.6400
last_name_selectivity: 0.6050
count(*): 200
1 row in set (0.01 sec)
first_name_selectivity: 0.6400，因此 first_name 列的选择性更高，所以答案将其作为索引列的第一列：
mysql> alter table yoon add key (first_name,last_name);
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28939273/viewspace-1266975/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
swap分区添加
请登录后发表评论
登录
全部评论
