我在PostgreSQL数据库中有下表
ID | first_name | last_name | class | position |
--------------------------------------------------------
1 | Teemo | Shroomer | Specialist | Top |
2 | Cecil | Heimerdinger | Specialist | Mid |
3 | Annie | Hastur | Mage | Mid |
4 | Fiora | Laurent | Slayer | Top |
5 | Garen | Crownguard | Fighter | Top |
6 | Malcolm | Graves | Specialist | ADC |
7 | Irelia | Lito | Figher | Top |
8 | Janna | Windforce | Controller | Support |
并且在3个不同的存储过程中有3个WHERE子句.
WHERE first_name = 'Annie'
WHERE first_name = 'Annie' AND class = 'Mage'
WHERE first_name = 'Janna' AND class = 'Controller' AND position = 'Support'
我为我的表创建了索引,如下所示
CREATE INDEX first_name_index ON users (first_name);
CREATE INDEX first_name_class_index ON users (first_name, class);
我对创建的索引有几个问题.
-
因为我已经有了FIRST_NAME+CLASS复合索引.我真的只需要First_Name字段的单一索引吗?如果我理解正确的话,那么仅使用FIRST_NAME的搜索可以受益于复合索引‘FIRST_NAME_CLASS_INDEX’
-
如果我使用第三个WHERE子句(有3个字段),我使用‘FIRST_NAME_CLASS_INDEX’会得到什么好处吗?索引是否会首先在内部使用复合索引(FIRST_NAME+CLASS)执行搜索,然后从获得的列表中搜索‘Position’.对吗?