SQL查询优化：两类关联查询应如何选择索引列？

1. 三表连接查询索引选择疑问 #

查询语句回顾 #

SELECT BranchName, No_Of_Copies 
FROM BOOK as B, BOOK_COPIES as BC, LIBRARY_BRANCH as LB
WHERE B.BookId = BC.BookId and BC.BranchId=LB.BranchId and title ="The Lost Tribe";

为什么不需要额外给BOOK.BookId、BOOK_COPIES.BookId建索引 #

关系型数据库默认会给主键列自动创建唯一索引，本题默认规则下：

• BOOK.BookId是BOOK表的主键，本身自带索引，不需要重复创建
• LIBRARY_BRANCH.BranchId是LIBRARY_BRANCH表的主键，同样自带索引
• BOOK_COPIES表一般会将(BookId, BranchId)设为联合主键，BookId作为联合索引的最左前缀，本身就支持关联查询时的匹配，不需要单独建索引

参考答案选中的三个索引的作用 #

• BOOK.title：作为查询里筛选性最强的过滤条件，非主键列默认无索引，建索引后可以快速定位到The Lost Tribe对应的少量BookId，大幅减少后续连接的扫描行数
• BOOK_COPIES.BranchId：拿到匹配的BookId后，可以通过该索引快速匹配到对应分馆ID，不需要全表扫描BOOK_COPIES
• 部分教材默认非主键外键无默认索引，因此额外标注LIBRARY_BRANCH.BranchId需要建索引，保证两表关联时的匹配效率

2. 分组聚合查询索引优化判断 #

查询语句回顾 #

SELECT B.cardNo, Name, Address, COUNT(BookId,BranchId) 
FROM BORROWER as B, BOOK_LOANS as BL
WHERE (BL.CardNo=B.CardNo)
GROUP BY B.CardNo, Name, Address
HAVING COUNT(BL.BookId, BranchId)>5;

索引效果判断 #

你选择的这几个列建索引可以优化查询性能，但要注意索引结构，联合索引的优化效果远好于单独建多个单列索引：

• 如果给BOOK_LOANS建(CardNo, BookId, BranchId)的联合索引：CardNo作为关联条件的索引前缀可以快速匹配BORROWER表，同时BookId、BranchId都在索引中，COUNT统计时不需要回表，直接走覆盖索引即可完成统计
• 如果给BORROWER建(CardNo, Name, Address)的联合索引：CardNo作为关联匹配的索引前缀可以快速关联，同时分组用的Name、Address都在索引中，分组操作不需要回表取数据，也走覆盖索引，性能提升非常明显
• 如果仅给四个列单独建单列索引，只能用到CardNo的索引完成关联，后续分组、统计还是需要回表扫数据，优化效果非常有限

内容的提问来源于stack exchange，提问作者Nguyễn Hữu Hưng