I read that SharePoint document library contents are stored in content databases, i.e. they are not folders on the hard disk. What happens then when we go to a document library and click Open with Explorer? Does SharePoint somehow serve the folders?
2 Answers
When you click in Open With Explorer your computer will open a window with all binaries (.js, .css, .html, ...) and/or files that you have in your Sharepoint Environment into that folder
For example: If you go to Site Contents > Site Assets > "Open with Explorer" you'll see all files in that folder. In this case, Sharepoint uses your Windows Explorer, and you can manipulate it if you want.
NOTE: This feature just works when you click with Internet Explorer, if you try to click with Google Chrome, you can't open the Windows Explorer
Open with Explorer will rely on WebClient Service:

This is a map network folder for sharing, and besides open with Explorer, we can also create a map drive like below to achieve the same function:

Access documents in SharePoint as easily as in a network folder