On recent OS X versions (Lion at the very least), the problem isn't that /Library and ~/Library aren't indexed by default; they are. Trouble is that if I search for a file named, say "com.apple.RemoteDesktop.plist", Finder appears to run a query akin to

    kMDItemDisplayName == "com.apple.RemoteDesktop.plist"
    && kMDItemSupportFileType != MDSystemFile

which returns nothing on my system, rather than simply

    kMDItemDisplayName == "com.apple.RemoteDesktop.plist"    

which returns three results, one each in subdirectories of /System/Library, /Library, and ~/Library.

There's an option in the "full" Spotlight search window that lets you control this for any given search, under "Other..." in the drop-down you get when you click "+" to add criteria. It's called, logically enough, "System files".

Incidentally, the mdfind command line tool doesn't have this behavior by default.