Resources.Load is just syntactic sugar around a bunch of things

Unity does a bunch of stuff in the background so that you, as a developer, don't have to think about what it is that Unity is actually doing. And this is (most likely) a good thing.

Nominally speaking, though, the assets in the Resources folder that Resources.Load allows you to access are packed up and shipped inside the Unity.dll that gets exported when you build your project (side note: the exe is almost meaningless, it contains just enough code to load the .dlls out of the YourProjectName_Data folder).

How it does this likely varies from object type to object type. I recently posted an answer with some code that shows how to extract a Texture2D out of a dll without using Resources.Load (there is a Resources.Load call, but that's so a developer using the dll that code is compiled into can override the packaged texture if they desire). Unfortunately the asker of that question wanted to load things that were not textures, and there's no convenient byte[] to GameObject converter the way there is for textures.

However, I don't know if this is how Unity does it. You can see how it might be done, though. Unity also probably has some internal helper functions to organize the streamed bytes into a GameObject prefab or whatever else it is you're trying to request. What these do and how they do it are black boxes from our perspective.

What I do know or suspect:

• All /Resource folder locations are combined and treated as the same location, regardless of where you locate them in your /Assets directory.
• All items in a /Resources folder will get included in the dll (and the files are contained the dll inside the _data folder)
• Calling Resources.Load() for some asset does cache the asset. If you make the same load call again in the future, Unity recognizes it and can hand up the built asset much more quickly.
  • Unclear if disk IO is involved or if it's streaming bytes from ram.
  • My own caching system around Resources.Load was neither faster nor slower than Resources.Load (insignificant difference when using vs. not using my own Dictionary<String,Texture2d>).
  • However there may be cache limits and Unity might unload things that haven't been in the scene for a while, etc. Or maybe it never unload the assets, leading to possible memory-leak type issues. I haven't tested either way.
• If you have a loaded asset active in the scene, it doesn't matter what the underlying Resources.Load does: your active object will be taking up memory anyway

All that said, Asset Bundles clean things up and include memory management public methods for unloading assets you know you don't need again for a while and only the portion of the bundle actually being referenced needs to be loaded into memory at any given time. Links are maintained between the prototypical asset and the in-scene asset so that no duplication occurs (except in situations where the developer tells the asset bundle to unload and severs that link intentionally).